`
itspace
  • 浏览: 956695 次
  • 性别: Icon_minigender_1
  • 来自: 杭州
社区版块
存档分类
最新评论

记一次惨痛的数据库恢复

阅读更多
客户在出账期间,查出生产库部分表格数据有误,于是要求取回出账之前的数据。即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这活越做越胆小了。
1
4
分享到:
评论

相关推荐

    记一次MySQL数据库恢复(附方案).zip

    记一次MySQL数据库恢复(附方案).zip记一次MySQL数据库恢复(附方案).zip记一次MySQL数据库恢复(附方案).zip记一次MySQL数据库恢复(附方案).zip记一次MySQL数据库恢复(附方案).zip记一次MySQL数据库恢复(附...

    SQL数据库恢复软件

    简单数据库记录恢复软件,由于一次偶然,数据库一条记录被删掉了,然后用此软件恢复了!

    Rman数据库恢复实例

    使用Rman数据库备份与恢复的实例,详细记录了一次完整的备份与恢复

    数据库灾难性恢复(数据库技术;灾难性;恢复;数据备份)

    当想要进行增量备份时(包含上一次完全备份或增量备份之后的所有更改),应使用FLUSH LOGS回滚二进制日志。然后,你需要将从最后的完全或增量备份的某个时刻到最后某个点的所有二进制日志复制到备份位置。这些二进制...

    Oracle 数据库备份与恢复(RMAN介绍一)

    Oracle 数据库备份与恢复(RMAN介绍一)

    删除Access数据库中重复的记录

    在Access中一般只能查找10行大概20条重复记录,这样程序就可以任意指定表及多字段检索重复记录,并把重复的记录移除到一个临时表中,并不是进行真正删除,你可以进行恢复。但是执行第二次查找时则自动删除上次查找的...

    记一次oracle rman增量备份恢复计划

    1.1 备份时间 凌晨2点(02:00:00) 1.2 备份操作 注: 增量备份模式为cumulative(累积模式) 1. 周日:0级增量备份 ... 保留策略为保存14天,也就是能恢复到14天内的任何时间点 并且实现本地备份+异地备份

    数据库系统概论第四版答案

    数据的存取单位即一次可以存取数据的大小也很灵活,可以小到某一个数据项(如一个学生 的姓名),大到一组记录(成千上万个学生记录)。而在文件系统中,数据的存取单位只有一 个:记录,如一个学生的完整记录。 ( 2...

    数据库系统概论-SQL-CH10-作业解答.doc

    第十章 数据库恢复技术 1、试述事务的概念及事务的四个特性。恢复技术能保证事务的哪些特性? 答:事务是用户定义的一个数据库操作序列,这些操作要么全做要么全不做,事务是一 个不可分割的工作单元。 事务具有4个...

    达梦数据库_SQL语言手册

    修改操作的对象也可以是元组的集合,相对于面向记录的数据库语言一次只能操作一条记录来 语言的使用简化了用户的处理,提高了应用程序的运行效率 语言简洁,方便易学 语言功能强大,格式规范,表达简洁,接近英语的语法...

    数据库备份可能出错的十种情况

    而且只有 全备份,不能将数据库恢复至某个时间点。所以,我们需要全备份+日志备份。比如每天一个全备份,每隔1小时或若干分钟一个日志备份。说到差异备份,因为微软的差异备份记录的是上一次全备份以来发生的变化,...

    基于Python实现的数据库快速备份与恢复软件源码+项目说明.zip

    一款MySql数据库备份(快照保存)与恢复软件。功能上与dump类似,但是提供了相对有好的交互界面,能够有效地管理导出的sql文件。 # 使用场景 开发阶段、测试阶段,尤其适合单人开发的小项目。 例如开发完毕需要...

    VB删除Access数据库中重复记录(已测试,编译通过)

    在Access中一般只能查找10行大概20条重复记录,这样程序就可以任意指定表及多字段检索重复记录,并把重复的记录移除到一个临时表中,并不是进行真正删除,你可以进行恢复。但是执行第二次查找时则自动删除上次查找的...

    SQL数据库修复软件

    SQL数据库损坏 SQL数据库修复软件 SQL数据库0字节 SQL系统表损坏 SQL误删除恢复 SQL 误删除表恢复 我们的修复率在100%,如果您已经尝试多家公司,请不妨再交给我们做,我们已经多次救援过被判‘死刑’的数据库。...

    浪潮ERP账套备份恢复工具

    解决方法:第一次恢复出现提示时记下来缺少的表空间,如022。然后用WinRAR解压缩DBGhost备份文件,提取.Bak裸设备文件。采用DBGhost1.7或更高版本恢复,恢复时选择裸设备文件,出现要求输入数据库用户编号提示时输入...

    5.4空间数据库管理系统.pdf

    后的恢复方面缺少基本功能 全关系型空间数据库管理 5.4.2 全关系型空间数据库管理基本思想 1 图形数据和属性数据都采用关系数据管理系统管理, 即使用同一DBMS管理 扩展结构管理模式 在标准的关系数据库上增加空间...

    数据库专家格雷讲述DBMS技术手段与方法

    建立系统运行日志,记载各事务的始点、终点以及在事务中被更新过的页面的改前和 改后状况,以便在系统出现故障使数据库遭到破坏时,能根据定期或不定期为数据库所作的备份加上日志中的信息将数据库恢复到系统故障前...

    Oracle数据库的安装及备份的练习.docx

    重要:进行以下操作之前,要确保数据库是归档模式、控制文件的自动备份选项打开,并且执行一次包括归档的完全数据库备份。 控制文件的恢复 控制文件是Oracle重要的物理文件,它记录了数据库的名称、每个数据文件的...

    数据库系统的特点.pdf

    不仅数据是 结构化,而且数据的存取单位即一次可以存取数据的大小也很灵活,可以小 到一某个数据项(如一个学生的名字) ,大到一组记录(成千上万个学生的记 录) 。而在文件系统中,数据的存取单位只有一个:记录...

Global site tag (gtag.js) - Google Analytics