记一次惨痛的数据库恢复

客户在出账期间，查出生产库部分表格数据有误，于是要求取回出账之前的数据。即2010年7月27日的17点的数据。该数据库容量已经达到了近10个T，幸好该库有一dataguard库，并在dataguard打开了闪回。

引用

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

其闪回时间设置为

引用

SQL> show parameter flash

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     5760

由于生产库平时归档量较大，备库存储效率不够理想，就打开延迟归档日志应用，即延迟应用2天归档，故障发生时间为2010年7月30日15点，但备库的日志应用只到28日的15点，要取回客户的数据，也就意味着需要闪回1天多的时间，在备库日志应用正常的情况下，关闭备库的mrp进程，进一步检查flashback log。

引用

SQL> select FIRST_CHANGE# ,FIRST_TIME from V$FLASHBACK_DATABASE_LOGfile order by FIRST_TIME;

         FIRST_CHANGE# FIRST_TIME
---------------------- -------------------
                     0 2010-07-15 02:41:59
                     0 2010-07-15 11:51:48
                     0 2010-07-16 06:50:50
                     0 2010-07-16 10:14:03
                     0 2010-07-16 11:01:43
                     0 2010-07-16 11:57:07
                     0 2010-07-16 13:04:13
                     0 2010-07-16 14:10:09
                     0 2010-07-16 14:56:22
                     0 2010-07-16 15:47:51
                     0 2010-07-16 16:37:26

         FIRST_CHANGE# FIRST_TIME
---------------------- -------------------
                     0 2010-07-16 17:24:53
                     0 2010-07-16 18:16:42
                     0 2010-07-16 19:31:22
                     0 2010-07-16 21:02:24
                     0 2010-07-16 23:10:37
                     0 2010-07-17 01:10:19
                     0 2010-07-17 02:21:35
                     0 2010-07-17 03:20:35
                     0 2010-07-17 04:30:13
                     0 2010-07-17 05:36:43
                     0 2010-07-17 07:20:57

         FIRST_CHANGE# FIRST_TIME
---------------------- -------------------
                     0 2010-07-17 08:19:34
                     0 2010-07-17 09:14:24
                     0 2010-07-17 10:13:18
                     0 2010-07-17 11:21:50
                     0 2010-07-17 12:38:33
                     0 2010-07-17 13:51:33
                     0 2010-07-17 15:12:51
                     0 2010-07-17 16:21:00
                     0 2010-07-17 17:41:32
                     0 2010-07-17 19:03:46
                     0 2010-07-17 20:38:06

         FIRST_CHANGE# FIRST_TIME
---------------------- -------------------
                     0 2010-07-17 22:59:20
                     0 2010-07-18 00:49:40
                     0 2010-07-18 00:51:04
                     0 2010-07-18 00:51:04
                     0 2010-07-18 00:52:37
                     0 2010-07-18 00:54:15
                     0 2010-07-18 00:59:15
                     0 2010-07-18 01:09:26
                     0 2010-07-18 01:16:37
                     0 2010-07-18 01:42:25
                     0 2010-07-18 02:54:45

         FIRST_CHANGE# FIRST_TIME
---------------------- -------------------
                     0 2010-07-18 03:50:33
                     0 2010-07-18 04:33:41
                     0 2010-07-18 06:12:03
                     0 2010-07-18 07:15:40
                     0 2010-07-18 08:05:00
                     0 2010-07-18 08:38:35
                     0 2010-07-18 09:34:11
                     0 2010-07-18 10:41:01
                     0 2010-07-18 11:59:57
                     0 2010-07-18 13:07:17
                     0 2010-07-18 14:42:52

         FIRST_CHANGE# FIRST_TIME
---------------------- -------------------
                     0 2010-07-18 16:23:06
                     0 2010-07-18 17:40:24
                     0 2010-07-18 19:09:57
                     0 2010-07-18 20:45:33
                     0 2010-07-18 22:57:31
                     0 2010-07-19 00:54:52
                     0 2010-07-19 02:04:23
                     0 2010-07-19 03:03:12
                     0 2010-07-19 04:02:11
                     0 2010-07-19 05:50:31
                     0 2010-07-19 07:22:30

         FIRST_CHANGE# FIRST_TIME
---------------------- -------------------
                     0 2010-07-19 08:08:24
                     0 2010-07-19 08:59:28
                     0 2010-07-19 09:47:24
                     0 2010-07-19 10:09:21
                     0 2010-07-19 10:27:47
                     0 2010-07-19 10:45:56
                     0 2010-07-19 11:10:11
                     0 2010-07-19 11:35:25
                     0 2010-07-19 12:00:28
                     0 2010-07-19 12:30:45
                     0 2010-07-19 13:05:15

         FIRST_CHANGE# FIRST_TIME
---------------------- -------------------
                     0 2010-07-19 13:23:37
                     0 2010-07-19 14:25:52
                     0 2010-07-19 15:34:41
                     0 2010-07-19 16:44:28
                     0 2010-07-19 18:07:21
                     0 2010-07-19 19:45:53
                     0 2010-07-19 23:22:06
                     0 2010-07-20 02:05:14
                     0 2010-07-20 06:50:58
                     0 2010-07-20 07:46:11
                     0 2010-07-20 11:18:25

         FIRST_CHANGE# FIRST_TIME
---------------------- -------------------
                     0 2010-07-20 15:57:07
                     0 2010-07-20 16:47:25
                     0 2010-07-20 17:47:57
                     0 2010-07-20 18:19:41
                     0 2010-07-20 19:05:38
                     0 2010-07-20 20:35:27
                     0 2010-07-20 23:09:03
                     0 2010-07-21 00:26:14
                     0 2010-07-21 01:06:37
                     0 2010-07-21 01:42:02
                     0 2010-07-21 02:21:43

         FIRST_CHANGE# FIRST_TIME
---------------------- -------------------
                     0 2010-07-21 02:26:56
                     0 2010-07-21 02:37:34
                     0 2010-07-21 02:52:47
                     0 2010-07-21 03:00:07
                     0 2010-07-21 03:15:13
                     0 2010-07-21 03:40:41
                     0 2010-07-21 03:51:32
                     0 2010-07-21 03:59:35
                     0 2010-07-21 04:13:34
                     0 2010-07-21 04:51:14
                     0 2010-07-21 05:15:02

         FIRST_CHANGE# FIRST_TIME
---------------------- -------------------
                     0 2010-07-21 06:08:48
                     0 2010-07-21 06:54:01
                     0 2010-07-21 07:03:09
                     0 2010-07-21 07:09:15
                     0 2010-07-21 07:17:15
                     0 2010-07-21 08:03:57
                     0 2010-07-21 09:03:41
                     0 2010-07-21 10:25:26
                     0 2010-07-21 11:52:55
                     0 2010-07-21 13:27:49
                     0 2010-07-21 15:25:20

         FIRST_CHANGE# FIRST_TIME
---------------------- -------------------
                     0 2010-07-21 17:35:20
                     0 2010-07-21 21:32:37
                     0 2010-07-22 00:51:01
                     0 2010-07-22 04:32:24
                     0 2010-07-22 07:09:33
                     0 2010-07-22 10:05:35
                     0 2010-07-22 15:35:00
                     0 2010-07-22 22:56:11
                     0 2010-07-23 03:34:15
                     0 2010-07-23 07:13:27
                     0 2010-07-23 11:53:19

         FIRST_CHANGE# FIRST_TIME
---------------------- -------------------
                     0 2010-07-23 14:58:55
                     0 2010-07-23 15:09:18
                     0 2010-07-23 15:29:15
                     0 2010-07-23 17:16:23
                     0 2010-07-23 23:40:05
                     0 2010-07-24 02:46:05
                     0 2010-07-24 06:45:17
                     0 2010-07-24 09:20:40
                     0 2010-07-24 16:52:27
                     0 2010-07-24 22:47:17
                     0 2010-07-25 02:29:13

         FIRST_CHANGE# FIRST_TIME
---------------------- -------------------
                     0 2010-07-25 06:47:12
                     0 2010-07-25 10:59:36
                     0 2010-07-25 22:45:04
                     0 2010-07-26 04:16:08
                     0 2010-07-26 07:35:35
                     0 2010-07-26 16:22:51
                     0 2010-07-26 22:36:40
                     0 2010-07-26 23:36:34
                     0 2010-07-27 04:59:44
                     0 2010-07-27 07:05:37
                     0 2010-07-27 11:24:08

         FIRST_CHANGE# FIRST_TIME
---------------------- -------------------
                     0 2010-07-27 16:46:57
                     0 2010-07-27 22:21:58
                     0 2010-07-28 05:32:22
                     0 2010-07-28 06:59:53
                     0 2010-07-28 11:32:01

159 rows selected.

检查闪回日志总大小，发现已经有1个多T

引用

SQL> select sum(BYTES)/1024/1024/1024 from v$flashback_database_logfile;

SUM(BYTES)/1024/1024/1024
-------------------------
               1348.55301

检查操作系统空间，和数据字典结果一致。

引用

$ du -sg 
1348.55

检查最早能闪回的时间点

引用

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select OLDEST_FLASHBACK_TIME from v$flashback_database_log;

OLDEST_FLASHBACK_TI
-------------------
2010-07-16 13:07:54

由于闪回时需要再次应用归档日志，需要确保27号17点之后的归档日志必须存在，发现其最早的归档处在7月25日18点，心想闪回应该不成问题。

引用

backup:/Tbackup/archlve/crmdb#ls -trl
total 2001469776
-rw-r-----   1 ora10g   887      1007934976 Jul 25 18:10 1_58127_679169948.dbf
-rw-r-----   1 ora10g   887        38256128 Jul 25 18:17 2_50883_679169948.dbf
-rw-r-----   1 ora10g   887       931522560 Jul 25 18:25 1_58128_679169948.dbf
-rw-r-----   1 ora10g   887        39721984 Jul 25 18:32 2_50884_679169948.dbf
-rw-r-----   1 ora10g   887      1017833984 Jul 25 18:40 1_58129_679169948.dbf
-rw-r-----   1 ora10g   887        39507456 Jul 25 18:47 2_50885_679169948.dbf
-rw-r-----   1 ora10g   887       970186752 Jul 25 18:55 1_58130_679169948.dbf
-rw-r-----   1 ora10g   887        39321600 Jul 25 19:02 2_50886_679169948.dbf
-rw-r-----   1 ora10g   887       832380928 Jul 25 19:10 1_58131_679169948.dbf
-rw-r-----   1 ora10g   887        38038528 Jul 25 19:17 2_50887_679169948.dbf
-rw-r-----   1 ora10g   887       858141696 Jul 25 19:24 1_58132_679169948.dbf
-rw-r-----   1 ora10g   887        39401984 Jul 25 19:32 2_50888_679169948.dbf
-rw-r-----   1 ora10g   887       837659648 Jul 25 19:39 1_58133_679169948.dbf
-rw-r-----   1 ora10g   887        39500800 Jul 25 19:47 2_50889_679169948.dbf
-rw-r-----   1 ora10g   887       796241920 Jul 25 19:54 1_58134_679169948.dbf
-rw-r-----   1 ora10g   887        39579136 Jul 25 20:02 2_50890_679169948.dbf
-rw-r-----   1 ora10g   887       866682880 Jul 25 20:09 1_58135_679169948.dbf
-rw-r-----   1 ora10g   887        38248448 Jul 25 20:17 2_50891_679169948.dbf
-rw-r-----   1 ora10g   887       689133568 Jul 25 20:24 1_58136_679169948.dbf
-rw-r-----   1 ora10g   887        39594496 Jul 25 20:32 2_50892_679169948.dbf
。。。

考虑到存储效率不行，闪回1天需要大量时间，需要写成简单脚本，放在后台执行

引用

$nohup sh flashbackcrm.sh > flashbackcrm.log &
$cat flashbackcrm.sh
. $HOME/.profile
export ORACLE_SID=crmdb
sqlplus  "drb/***@dbra_crmdb as sysdba" <<EOF
flashback standby database to scn 11171934027949;
EOF

谁知报错，情况不妙

引用

$
[1] +  Done                    nohup sh flashbackcrm.sh > flashbackcrm.log &
SQL> flashback standby database to scn 11171934027949
*
ERROR at line 1:
ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
ORA-38761: redo log sequence 58026 in thread 1, incarnation 1 could not be
accessed

发现需要应用58026号日志。检查58026号日志时间戳，发现竟是24日的日志，但是已经被删除了！

引用

SQL> select thread#,sequence#,to_char(first_change#),to_char(first_time,'yymmddhh24miss')
from v$archived_log where sequence#=58026 and thread#=1;
         1      58026 11171213774238                           100724221333

我需要闪回至27号，怎么需要应用24号的日志呢？迷惑了！！！难道检查数据文件头处于不一致状态？可惜不是。

引用

SQL> select file#,to_char(checkpoint_change#) from v$datafile_header where checkpoint_change#!=11172172099694;

no rows selected

由于处于vpn中，不能上网查询具体原因，查阅手头资料，Oracle给出这么一解释，也是解释的不够透彻

引用

The amount of time it takes to Flashback a database is proportional to how far back you need to revert the database, rather than the time it would take to restore and recover the whole database, which could be much longer. The before images in the Flashback logs are only used to restore the database to a point in the past, and forward recovery is used to bring the database to a consistent state at some time in the past. Oracle returns datafiles to the previous point-in-time, but not auxiliary files, such as initialization parameter files.


To restore a database to its state at some past target time using Flashback Database, each block is restored to its contents as of the flashback logging time most immediately prior to the desired target time, and then changes from the redo logs are applied to fill in changes between the time captured by the flashback logs and the target time. Redo logs must be available for the entire time period spanned by the flashback logs, whether on tape or on disk. In practice, however, redo logs are often kept much longer than flashback logs, so this requirement is not a real limitation.

于是进一步尝试闪回一小时前数据库状态，我想应该不会出问题，反正需要闪回的，所想时间也不会太长。结果正是这一想法，导致出现了问题。
有了这一想法之后，就立即开干。查询出一小时之间的scn，郁闷的是还是出现错误。所需要的归档还是24日的，这次更加迷惑了。

引用

SQL> flashback standby database to scn 11172162849123;
flashback standby database to scn 11172162849123
*
ERROR at line 1:
ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
ORA-38761: redo log sequence 58016 in thread 1, incarnation 1 could not be
accessed


SQL> select first_time from v$archived_log where thread#=1 and sequence#=58016;

FIRST_TIME
-------------------
2010-07-24 19:44:22

经验告诉我们，可能是遇到bug了。于是减少闪回空间，删除部分闪回日志，触发Oracle更新数据字典。
SQL> alter system set db_recovery_file_dest_size=1350g scope=memory;

System altered.
后台alert日志显示

引用

ALTER SYSTEM SET db_recovery_file_dest_size='1350G' SCOPE=MEMORY;
Fri Jul 30 17:12:54 2010
Deleted Oracle managed file /Tbackup/flash2/crmdb/DBRA_CRMDB/flashback/o1_mf_5zz7wfnr_.flb
Deleted Oracle managed file /Tbackup/flash2/crmdb/DBRA_CRMDB/flashback/o1_mf_609zfmyy_.flb
Deleted Oracle managed file /Tbackup/flash2/crmdb/DBRA_CRMDB/flashback/o1_mf_5zzcxtx2_.flb
Deleted Oracle managed file /Tbackup/flash2/crmdb/DBRA_CRMDB/flashback/o1_mf_60b6sylm_.flb

可惜令人伤心的是，错误依旧！搞不懂Oracle在想什么了。

引用

SQL>  flashback standby database to scn 11172162849123;
flashback standby database to scn 11172162849123
*
ERROR at line 1:
ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
ORA-38761: redo log sequence 58016 in thread 1, incarnation 1 could not be
accessed

我们也不顾Oracle闪回的原理，于是几个同事上网搜了一把，网上号称此错误的解决办法就是用rman闪回。我们想既然用rman闪回就用rman闪回吧。立即用rman闪回，也不顾得将脚本放在后台执行了，这又是另一大错误！

引用

RMAN> flashback database to scn 11172162849123;

Starting flashback at 30-JUL-10
Starting implicit crosscheck backup at 30-JUL-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=274 devtype=DISK
Finished implicit crosscheck backup at 30-JUL-10

Starting implicit crosscheck copy at 30-JUL-10
using channel ORA_DISK_1
Crosschecked 1116 objects
Finished implicit crosscheck copy at 30-JUL-10

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1


starting media recovery

这里等待N久时间，这就是不放在后台执行的结果！只能将本本开着，一边担心vpn会不会断掉，又担心晚上会不会断电，后来担心本本会不会休眠，于是将本本自动休眠关闭powercfg -h off，操心的事情可不少啊
这时alert日志显示，Flashback Restore Start字段显示，估计是在restore闪回日志了。

引用

datafilecopy header validation failure for file /Tbackup/crmdb/oradata/rcrm3_lv065_4G
datafilecopy header validation failure for file /Tbackup/crmdb/oradata/rcrm3_lv063_4G
datafilecopy header validation failure for file /Tbackup/crmdb/oradata/rcrm3_lv063_4G
datafilecopy header validation failure for file /Tbackup/crmdb/oradata/rcrm3_lv056_4G
datafilecopy header validation failure for file /Tbackup/crmdb/oradata/rcrm3_lv056_4G
datafilecopy header validation failure for file /Tbackup/crmdb/oradata/rcrm3_lv059_4G
datafilecopy header validation failure for file /Tbackup/crmdb/oradata/rcrm3_lv059_4G
datafilecopy header validation failure for file /Tbackup/crmdb/oradata/rcrm3_lv039_4G
datafilecopy header validation failure for file /Tbackup/crmdb/oradata/rcrm3_lv039_4G
datafilecopy header validation failure for file /Tbackup/crmdb/oradata/rcrm3_lv040_4G
datafilecopy header validation failure for file /Tbackup/crmdb/oradata/rcrm3_lv040_4G
Fri Jul 30 17:36:40 2010
alter database recover datafile list clear
Fri Jul 30 17:36:41 2010
Completed: alter database recover datafile list clear
Fri Jul 30 17:39:05 2010
Flashback Restore Start

大概等待了1个多小时，似乎还没进度，观察v$session_longops，，没有任何参考价值。只能观察iostat，显示磁盘在忙，至少说明rman进程还活着，还在restore闪回日志。除此之外，没有任何参考价值，这时只能和同事抱怨抱怨Oracle rman debug做的真烂，让人死等。这时突然想到数据文件头是不是也会相应改变。结果一查，让人吓出一身冷汗。

引用

SQL> select to_char(checkpoint_change#) from v$datafile;

TO_CHAR(CHECKPOINT_CHANGE#)
----------------------------------------
11172172099694
11172172099694
11172172099694
11172172099694
11172172099694
11172172099694
11172172099694
。。。
SQL> select to_char(checkpoint_change#) from v$datafile_header;

TO_CHAR(CHECKPOINT_CHANGE#)
----------------------------------------
11172172099694
11172172099694
11172172099694
11172172099694
11172172099694
11172172099694
11172172099694
。。。
SQL> select thread#,sequence#, to_char(first_time,'yyyymmddhh24:mi:ss') from v$archived_log where 11170104004584 between first_change# and next_change#;

   THREAD#  SEQUENCE# TO_CHAR(FIRST_TI
---------- ---------- ----------------
         2      50356 2010072015:32:58
         1      57469 2010072015:34:33

根据此提示，Oralce已经将数据文件头已经回到了7月20日，由于7月20至7月24日的归档已经被删除了，也就意味着flashback log restore完成之后，应用归档时会报归档不存在的错误！也就意味着本次数据库闪回的失败。同事也通过电话告之客户，事情的来龙去脉，客户说拿回20日的数据也问题不大，但是问题的关键是万一flashback log restore完成之后，数据库不能打开怎么办？这样只能通过dul挖掘数据，数据库不能打开，也就意味着数据库处于不一致状态中。dul挖取的数据一致性也需要打个问号。
于是赶紧和同事在公司实验环境中模拟该错误，并讨论Oracle闪回技术原理，其过程不表，打算以后专门写，了解了Oracle在处理普通的闪回和普通restore point的区别，普通的restore point和gurantee  restore point区别。于是只能先回家睡觉，第二天再来看闪回情况。处理到现在的时候已经是晚上21：30分了，于是在公司楼下真功夫吃了点快餐。
一大早就来公司，本来今天是周末，客户对闪回的数据也不是很急，但心里有事睡懒觉也不踏实，在来公司的路上只能祈祷电源不会断（公司大楼素有在周末断电的传统，不禁痛骂一下），vpn网络不断，电脑不休眠。来公司一看，所幸的是，flashback log已经restore完成，在检查archivelog时，果然报错了。

引用

archive log thread 2 sequence 51159 is already on disk as file /Tbackup/archlve/crmdb/2_51159_679169948.dbf
archive log thread 2 sequence 51160 is already on disk as file /Tbackup/archlve/crmdb/2_51160_679169948.dbf
archive log thread 2 sequence 51161 is already on disk as file /Tbackup/archlve/crmdb/2_51161_679169948.dbf
archive log thread 2 sequence 51162 is already on disk as file /Tbackup/archlve/crmdb/2_51162_679169948.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of flashback command at 07/31/2010 00:52:57
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 2 seq 50906 lowscn 11171446780858 found to restore
RMAN-06025: no backup of log thread 2 seq 50905 lowscn 11171445263896 found to restore
RMAN-06025: no backup of log thread 2 seq 50904 lowscn 11171443742483 found to restore
RMAN-06025: no backup of log thread 2 seq 50903 lowscn 11171442281587 found to restore
RMAN-06025: no backup of log thread 2 seq 50902 lowscn 11171440992339 found to restore
RMAN-06025: no backup of log thread 2 seq 50901 lowscn 11171439585968 found to restore
RMAN-06025: no backup of log thread 2 seq 50900 lowscn 11171438206163 found to restore
RMAN-06025: no backup of log thread 2 seq 50899 lowscn 11171436792461 found to restore
RMAN-06025: no backup of log thread 2 seq 50898 lowscn 11171435160073 found to restore
RMAN-06025: no backup of log thread 2 seq 50897 lowscn 11171433419802 found to restore
RMAN-06025: no backup of log thread 2 seq 50896 lowscn 11171431548122 found to restore
RMAN-06025: no backup of log thread 2 seq 50895 lowscn 11171429730564 found to restore
RMAN-06025: no backup of log thread 2 seq 50894 lowscn 11171427855737 found to restore
RMAN-06025: no backup of log thread 2 seq 50893 lowscn 11171425821083 found to restore
RMAN-06025: no backup of log thread 2 seq 50892 lowscn 11171423679028 found to restore
RMAN-06025: no backup of log thread 2 seq 50891 lowscn 11171421477258 found to restore
RMAN-06025: no backup of log thread 2 seq 50890 lowscn 11171419129448 found to restore
RMAN-06025: no backup of log thread 2 seq 50889 lowscn 11171416776143 found to restore
RMAN-06025: no backup of log thread 2 seq 50888 lowscn 11171414354332 found to restore
RMAN-06025: no backup of log thread 2 seq 50887 lowscn 11171411869317 found to restore
RMAN-06025: no backup of log thread 2 seq 50886 lowscn 11171409432159 found to restore
RMAN-06025: no backup of log thread 2 seq 50885 lowscn 11171407281980 found to restore
RMAN-06025: no backup of log thread 2 seq 50884 lowscn 11171405142922 found to restore
RMAN-06025: no backup of log thread 2 seq 50883 lowscn 11171402623636 found to restore
RMAN-06025: no backup of log thread 2 seq 50882 lowscn 11171399915758 found to restore
RMAN-06025: no backup of log thread 2 seq 50881 lowscn 11171397211239 found to restore
RMAN-06025: no backup of log thread 2 seq 50880 lowscn 11171394582891 found to restore
RMAN-06025: no backup of log thread 2 seq 50879 lowscn 11171391837167 found to restore
RMAN-06025: no backup of log thread 2 seq 50878 lowscn 11171389172741 found to restore
RMAN-06025: no backup of log thread 2 seq 50877 lowscn 11171386598009 found to restore
RMAN-06025: no backup of log thread 2 seq 50876 lowscn 11171384036096 found to restore
RMAN-06025: no backup of log thread 2 seq 50875 lowscn 11171381544642 found to restore
RMAN-06025: no backup of log thread 2 seq 50874 lowscn 11171379070275 found to restore
RMAN-06025: no backup of log thread 2 seq 50873 lowscn 11171376610876 found to restore
RMAN-06025: no backup of log thread 2 seq 50872 lowscn 11171374069138 found to restore
RMAN-06025: no backup of log thread 2 seq 50871 lowscn 11171371353219 found to restore
RMAN-06025: no backup of log thread 2 seq 50870 lowscn 11171368688008 found to restore
RMAN-06025: no backup of log thread 2 seq 50869 lowscn 11171365825514 found to restore
RMAN-06025: no backup of log thread 2 seq 50868 lowscn 11171362933951 found to restore
RMAN-06025: no backup of log thread 2 seq 50867 lowscn 11171359940334 found to restore
RMAN-06025: no backup of log thread 2 seq 50866 lowscn 11171357163312 found to restore
RMAN-06025: no backup of log thread 2 seq 50865 lowscn 11171354012803 found to restore
RMAN-06025: no backup of log thread 2 seq 50864 lowscn 11171350872691 found to restore
RMAN-06025: no backup of log thread 2 seq 50863 lowscn 11171347712449 found to restore
RMAN-06025: no backup of log thread 2 seq 50862 lowscn 11171344393505 found to restore
RMAN-06025: no backup of log thread 2 seq 50861 lowscn 11171341027514 found to restore
RMAN-06025: no backup of log thread 2 seq 50860 lowscn 11171337549632 found to restore
RMAN-06025: no backup of log thread 2 seq 50859 lowscn 11171334022902 found to restore
RMAN-06025: no backup of log thread 2 seq 50858 lowscn 11171330544052 found to restore
RMAN-06025: no backup of log thread 2 seq 50857 lowscn 11171327020781 found to restore
RMAN-06025: no backup of log thread 2 seq 50856 lowscn 11171323507159 found to restore
RMAN-06025: no backup of log thread 2 seq 50855 lowscn 11171320230859 found to restore
RMAN-06025: no backup of log thread 2 seq 50854 lowscn 11171317091202 found to restore
MAN-06025: no backup of log thread 2 se
RMAN> exit

不管他，再次检查控制文件和数据文件scn是不是处于一致，如果一致的话，数据库能打开的机会就大了

引用

SQL> select to_char(checkpoint_change#) from v$datafile;

TO_CHAR(CHECKPOINT_CHANGE#)
----------------------------------------
11172172099694
11172172099694
11172172099694
11172172099694
。。。
SQL> select to_char(checkpoint_change#) from v$datafile_header;

TO_CHAR(CHECKPOINT_CHANGE#)
----------------------------------------
11172172099694
11172172099694
11172172099694
11172172099694
11172172099694
11172172099694
。。。

怀着忐忑的心情，敲下了关键的命令，接下来又是漫长的等待，在备库上打开数据库又不需要恢复事务的，莫非周末过不好了？

引用

$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jul 31 08:47:34 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database open read only;
.....

查看后台alert日志，发现在创建tempfile，心里一阵舒坦，打开机会99%！

引用

Sat Jul 31 08:47:41 2010
alter database open read only
Sat Jul 31 08:47:51 2010
SMON: enabling cache recovery
Sat Jul 31 08:48:02 2010
Re-creating tempfile /Tbackup/crmdb/oradata/rcrm4_tmp19_4G
Re-creating tempfile /Tbackup/crmdb/oradata/rcrm3_tmp18_4G
Re-creating tempfile /Tbackup/crmdb/oradata/rcrm2_tmp17_4G
Re-creating tempfile /Tbackup/crmdb/oradata/rcrm1_tmp16_4G
Re-creating tempfile /Tbackup/crmdb/oradata/rcrm5_tmp15_4G
Re-creating tempfile /Tbackup/crmdb/oradata/rcrm4_tmp14_4G
Re-creating tempfile /Tbackup/crmdb/oradata/rcrm3_tmp13_4G
Re-creating tempfile /Tbackup/crmdb/oradata/rcrm2_tmp12_4G
Re-creating tempfile /Tbackup/crmdb/oradata/rcrm1_tmp11_4G
Re-creating tempfile /Tbackup/crmdb/oradata/rcrm5_tmp25_4G
Re-creating tempfile /Tbackup/crmdb/oradata/rcrm4_tmp24_4G
Re-creating tempfile /Tbackup/crmdb/oradata/rcrm3_tmp23_4G
Re-creating tempfile /Tbackup/crmdb/oradata/rcrm2_tmp22_4G
Re-creating tempfile /Tbackup/crmdb/oradata/rcrm1_tmp21_4G
Re-creating tempfile /Tbackup/crmdb/oradata/rcrm5_tmp10_4G
Re-creating tempfile /Tbackup/crmdb/oradata/rcrm4_tmp09_4G
Re-creating tempfile /Tbackup/crmdb/oradata/rcrm3_tmp08_4G
Re-creating tempfile /Tbackup/crmdb/oradata/rcrm2_tmp07_4G
Re-creating tempfile /Tbackup/crmdb/oradata/rcrm1_tmp06_4G
Re-creating tempfile /Tbackup/crmdb/oradata/rcrm5_tmp05_4G
Re-creating tempfile /Tbackup/crmdb/oradata/rcrm4_tmp04_4G
Re-creating tempfile /Tbackup/crmdb/oradata/rcrm3_tmp03_4G
Re-creating tempfile /Tbackup/crmdb/oradata/rcrm2_tmp02_4G
Re-creating tempfile /Tbackup/crmdb/oradata/rcrm1_tmp01_4G
Re-creating tempfile /
Tbackup/crmdb/oradata/rcrm5_tmp20_4G
Database Characterset is ZHS16GBK

经过10分钟的等待，数据库终于打开成功！

引用

SQL> alter database open read only;

Database altered.

alert日志也显示，看来周末可以过的舒坦了。

引用

Sat Jul 31 08:51:01 2010
WARNING: inbound connection timed out (ORA-3136)
Opening with internal Resource Manager plan
where NUMA PG = 1, CPUs = 32
replication_dependency_tracking turned off (no async multimaster replication found)
Physical standby database opened for read only access.
Completed: alter database open read only

总结：
1、对Oracle 10g闪回原理认识不够，经验主义害死人。
2、操作之前，没有在测试环境下，进行相关测试，还是经验主义害死人。
3、没有用脚本在后台执行，其实编写个这样的小脚本，比在前台直接敲打命令，费不了多少时间，结果导致很多繁琐事。
4、三思而后行，dba这活越做越胆小了。