ITPub博客

首页 > 数据库 > Oracle > redo log恢复

redo log恢复

原创 Oracle 作者:pwz1688 时间:2014-04-09 21:56:16 0 删除 编辑
一、部分日志成员丢失(不论是不是正常关机)
--查看系统日志信息
SQL> select * from v$log;
    GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1 1 21 104857600 2 NO INACTIVE 492936 08-APR-14
         2 1 19 104857600 2 NO INACTIVE 492928 08-APR-14
         3 1 20 104857600 2 NO INACTIVE 492930 08-APR-14
         5 1 23 104857600 2 NO CURRENT 493273 08-APR-14
SQL> select * from v$logfile;
    GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
         1 ONLINE /u01/app/oracle/oradata/PROD/disk1/redo01.log NO
         1 ONLINE /u01/app/oracle/oradata/PROD/disk2/redo01.log NO
         3 ONLINE /u01/app/oracle/oradata/PROD/disk1/redo03.log NO
         3 ONLINE /u01/app/oracle/oradata/PROD/disk2/redo03.log NO
         2 ONLINE /u01/app/oracle/oradata/PROD/disk1/redo02.log NO
         2 ONLINE /u01/app/oracle/oradata/PROD/disk2/redo02.log NO
         5 ONLINE /u01/app/oracle/oradata/PROD/disk1/redo05.log NO
         5 ONLINE /u01/app/oracle/oradata/PROD/disk2/redo05.log NO
8 rows selected.
--联机状态,删除当前日志组某成员,关库再启库
[oracle@gc1 disk1]$ cd /u01/app/oracle/oradata/PROD/disk1
[oracle@gc1 disk1]$ ls
control01.ctl redo01.log redo02.log redo03.log redo04.log redo05.log
[oracle@gc1 disk1]$ rm redo05.log
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 1219760 bytes
Variable Size 121635664 bytes
Database Buffers 293601280 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
由此可见,删除当前日志组成员后,可正常启库。但告警日志信息中会有错误提示,如下:
ALTER DATABASE OPEN
Wed Apr 9 00:28:06 2014
Errors in file /u01/app/oracle/oradata/PROD/bdump/prod_lgwr_10654.trc:
ORA-00313: open failed for members of log group 5 of thread 1
ORA-00312: online log 5 thread 1: '/u01/app/oracle/oradata/PROD/disk1/redo05.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Wed Apr 9 00:28:06 2014
Errors in file /u01/app/oracle/oradata/PROD/bdump/prod_lgwr_10654.trc:
ORA-00321: log 5 of thread 1, cannot update log file header
ORA-00312: online log 5 thread 1: '/u01/app/oracle/oradata/PROD/disk1/redo05.log'
Wed Apr 9 00:28:06 2014
Errors in file /u01/app/oracle/oradata/PROD/bdump/prod_lgwr_10654.trc:
ORA-00313: open failed for members of log group 5 of thread 1
Wed Apr 9 00:28:06 2014
Thread 1 opened at log sequence 23
  Current log# 5 seq# 23 mem# 1: /u01/app/oracle/oradata/PROD/disk2/redo05.log
Successful open of redo thread 1
--恢复日志(先删除旧成员,再新增一个成员)
SQL> alter database drop logfile member '/u01/app/oracle/oradata/PROD/disk1/redo05.log';
alter database drop logfile member '/u01/app/oracle/oradata/PROD/disk1/redo05.log'
*
ERROR at line 1:
ORA-01609: log 5 is the current log for thread 1 - cannot drop members
ORA-00312: online log 5 thread 1: '/u01/app/oracle/oradata/PROD/disk1/redo05.log'
ORA-00312: online log 5 thread 1: '/u01/app/oracle/oradata/PROD/disk2/redo05.log'
注意:因为group 5为当前日志组,所以无法直接删除其成员,需执行日志切换
--生成全局检查点,执行日志切换
SQL> alter system checkpoint;
System altered.

SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
    GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1 1 21 104857600 2 NO INACTIVE 492936 08-APR-14
         2 1 24 104857600 2 NO CURRENT 494611 09-APR-14
         3 1 20 104857600 2 NO INACTIVE 492930 08-APR-14
         5 1 23 104857600 2 NO ACTIVE 493273 08-APR-14
--删除成员
SQL> alter database drop logfile member '/u01/app/oracle/oradata/PROD/disk1/redo05.log';
Database altered.
SQL> select * from v$logfile;
    GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
         1 ONLINE /u01/app/oracle/oradata/PROD/disk1/redo01.log NO
         1 ONLINE /u01/app/oracle/oradata/PROD/disk2/redo01.log NO
         3 ONLINE /u01/app/oracle/oradata/PROD/disk1/redo03.log NO
         3 ONLINE /u01/app/oracle/oradata/PROD/disk2/redo03.log NO
         2 ONLINE /u01/app/oracle/oradata/PROD/disk1/redo02.log NO
         2 ONLINE /u01/app/oracle/oradata/PROD/disk2/redo02.log NO
         5 ONLINE /u01/app/oracle/oradata/PROD/disk2/redo05.log NO
7 rows selected.
--新增成员
SQL> alter database add logfile member '/u01/app/oracle/oradata/PROD/disk1/redo05.log' to group 5;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
    GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1 1 21 104857600 2 NO INACTIVE 492936 08-APR-14
         2 1 24 104857600 2 NO ACTIVE 494611 09-APR-14
         3 1 25 104857600 2 NO CURRENT 494645 09-APR-14
         5 1 23 104857600 2 NO ACTIVE 493273 08-APR-14

--执行日志切换,将当前日志组切换回group 5,并查看告警日志信息是否报错
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
    GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1 1 26 104857600 2 NO CURRENT 494650 09-APR-14
         2 1 24 104857600 2 NO ACTIVE 494611 09-APR-14
         3 1 25 104857600 2 NO ACTIVE 494645 09-APR-14
         5 1 23 104857600 2 NO ACTIVE 493273 08-APR-14
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
    GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1 1 26 104857600 2 NO ACTIVE 494650 09-APR-14
         2 1 24 104857600 2 NO ACTIVE 494611 09-APR-14
         3 1 25 104857600 2 NO ACTIVE 494645 09-APR-14
         5 1 27 104857600 2 NO CURRENT 494656 09-APR-14
--当前日志组回到group 5,此时告警日志恢复正常,如下所示:
alter database drop logfile member '/u01/app/oracle/oradata/PROD/disk1/redo05.log'
Wed Apr 9 00:33:37 2014
ORA-1609 signalled during: alter database drop logfile member '/u01/app/oracle/oradata/PROD/disk1/redo05.log'...
Wed Apr 9 00:34:36 2014
Thread 1 advanced to log sequence 24
  Current log# 2 seq# 24 mem# 0: /u01/app/oracle/oradata/PROD/disk1/redo02.log
  Current log# 2 seq# 24 mem# 1: /u01/app/oracle/oradata/PROD/disk2/redo02.log
Wed Apr 9 00:34:58 2014
alter database drop logfile member '/u01/app/oracle/oradata/PROD/disk1/redo05.log'
Wed Apr 9 00:34:58 2014
Completed: alter database drop logfile member '/u01/app/oracle/oradata/PROD/disk1/redo05.log'
Wed Apr 9 00:35:48 2014
alter database add logfile member '/u01/app/oracle/oradata/PROD/disk1/redo05.log' to group 5
Wed Apr 9 00:36:05 2014
Completed: alter database add logfile member '/u01/app/oracle/oradata/PROD/disk1/redo05.log' to group 5
Wed Apr 9 00:36:23 2014
Thread 1 advanced to log sequence 25
  Current log# 3 seq# 25 mem# 0: /u01/app/oracle/oradata/PROD/disk1/redo03.log
  Current log# 3 seq# 25 mem# 1: /u01/app/oracle/oradata/PROD/disk2/redo03.log
Wed Apr 9 00:36:36 2014
Thread 1 advanced to log sequence 26
  Current log# 1 seq# 26 mem# 0: /u01/app/oracle/oradata/PROD/disk1/redo01.log
  Current log# 1 seq# 26 mem# 1: /u01/app/oracle/oradata/PROD/disk2/redo01.log
Thread 1 cannot allocate new log, sequence 27
Checkpoint not complete
  Current log# 1 seq# 26 mem# 0: /u01/app/oracle/oradata/PROD/disk1/redo01.log
  Current log# 1 seq# 26 mem# 1: /u01/app/oracle/oradata/PROD/disk2/redo01.log
Wed Apr 9 00:36:53 2014
Thread 1 advanced to log sequence 27
  Current log# 5 seq# 27 mem# 0: /u01/app/oracle/oradata/PROD/disk2/redo05.log
  Current log# 5 seq# 27 mem# 1: /u01/app/oracle/oradata/PROD/disk1/redo05.log
--查看日志文件信息
[oracle@gc1 disk1]$ ls
control01.ctl redo01.log redo02.log redo03.log redo04.log redo05.log
由此可见,日志文件恢复成功
总结:删除单个成员,不受影响(不论是不是当前日志组),系统只看日志组在不在,不关心是否成员都存在。
修复方式:先采用alter database drop logfile member '丢失文件' 删除日志文件,再执行
alter database add logfile member  '新增日志文件' 做新增日志成员操作即可。
---------------------------------------------------------------------------------------------------------
二、非当前日志组所有成员丢失
--查看当前日志信息
SQL> col member for a50;
SQL> select * from v$log;
    GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1 1 26 104857600 2 NO INACTIVE 494650 09-APR-14
         2 1 24 104857600 2 NO INACTIVE 494611 09-APR-14
         3 1 25 104857600 2 NO INACTIVE 494645 09-APR-14
         5 1 27 104857600 2 NO CURRENT 494656 09-APR-14
SQL> select * from v$logfile;
    GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
         1 ONLINE /u01/app/oracle/oradata/PROD/disk1/redo01.log NO
         1 ONLINE /u01/app/oracle/oradata/PROD/disk2/redo01.log NO
         3 ONLINE /u01/app/oracle/oradata/PROD/disk1/redo03.log NO
         3 ONLINE /u01/app/oracle/oradata/PROD/disk2/redo03.log NO
         2 ONLINE /u01/app/oracle/oradata/PROD/disk1/redo02.log NO
         2 ONLINE /u01/app/oracle/oradata/PROD/disk2/redo02.log NO
         5 ONLINE /u01/app/oracle/oradata/PROD/disk1/redo05.log NO
         5 ONLINE /u01/app/oracle/oradata/PROD/disk2/redo05.log NO
8 rows selected.
--删除非当前日志组2的所有成员
[oracle@gc1 disk1]$ rm /u01/app/oracle/oradata/PROD/disk1/redo02.log
[oracle@gc1 disk1]$ rm /u01/app/oracle/oradata/PROD/disk2/redo02.log
--正常关库并启库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 1219760 bytes
Variable Size 121635664 bytes
Database Buffers 293601280 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/PROD/disk1/redo02.log'
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/PROD/disk2/redo02.log'

SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
PROD MOUNTED
SQL> select * from v$log;
    GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1 1 26 104857600 2 NO INACTIVE 494650 09-APR-14
         5 1 27 104857600 2 NO CURRENT 494656 09-APR-14
         3 1 25 104857600 2 NO INACTIVE 494645 09-APR-14
         2 1 24 104857600 2 NO INACTIVE 494611 09-APR-14
注意:启库失败,因是shutdown immediate正常关库,会生成全局检查点,脏块都写完,因此可见除当前日志组外,其它日志组状态都为inactive,所以丢失的日志组2可根据需要进行drop或clear方式进行恢复,下面采用clear的方式进行修改
--清空丢失的日志组(类似于新增日志组成员)
SQL> alter database clear logfile group 2;
Database altered.
注意:clear之后,group 2的状态应该为unused,类似于新增组成员。
SQL> alter database open;
Database altered.
SQL> select * from v$log;
    GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1 1 26 104857600 2 NO INACTIVE 494650 09-APR-14
         2 1 28 104857600 2 NO CURRENT 496855 09-APR-14
         3 1 25 104857600 2 NO INACTIVE 494645 09-APR-14
         5 1 27 104857600 2 NO INACTIVE 494656 09-APR-14
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
    GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1 1 26 104857600 2 NO INACTIVE 494650 09-APR-14
         2 1 28 104857600 2 NO ACTIVE 496855 09-APR-14
         3 1 29 104857600 2 NO CURRENT 497425 09-APR-14
         5 1 27 104857600 2 NO INACTIVE 494656 09-APR-14
--查看日志文件信息
[oracle@gc1 disk1]$ ls
control01.ctl redo01.log redo02.log redo03.log redo04.log redo05.log
[oracle@gc1 disk1]$ cd ../
[oracle@gc1 PROD]$ cd disk2
[oracle@gc1 disk2]$ ls
control01.ctl redo01.log redo02.log redo03.log redo04.log redo05.log
由此可见,clear group 2之后,丢失的组2成员都新增成功了。
总结:删除非当前日志组所有成员,正常关库后启库会报错,但因是非当前日志组且正常关库(生成了全局检查点,所有日志都已提交),可采用 alter database clear/drop logfile group '丢失日志组号' 的方式进行修复。
---------------------------------------------------------------------------------------------------------
三、当前日志组丢失(正常关库)
--查看当前日志信息
SQL> select * from v$log;
    GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1 1 26 104857600 2 NO INACTIVE 494650 09-APR-14
         2 1 28 104857600 2 NO ACTIVE 496855 09-APR-14
         3 1 29 104857600 2 NO CURRENT 497425 09-APR-14
         5 1 27 104857600 2 NO INACTIVE 494656 09-APR-14
SQL> select * from v$logfile;
    GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
         1 ONLINE /u01/app/oracle/oradata/PROD/disk1/redo01.log NO
         1 ONLINE /u01/app/oracle/oradata/PROD/disk2/redo01.log NO
         3 ONLINE /u01/app/oracle/oradata/PROD/disk1/redo03.log NO
         3 ONLINE /u01/app/oracle/oradata/PROD/disk2/redo03.log NO
         2 ONLINE /u01/app/oracle/oradata/PROD/disk1/redo02.log NO
         2 ONLINE /u01/app/oracle/oradata/PROD/disk2/redo02.log NO
         5 ONLINE /u01/app/oracle/oradata/PROD/disk1/redo05.log NO
         5 ONLINE /u01/app/oracle/oradata/PROD/disk2/redo05.log NO
8 rows selected.
--删除当前日志组所有成员
[oracle@gc1 disk2]$ rm /u01/app/oracle/oradata/PROD/disk1/redo03.log
[oracle@gc1 disk2]$ rm /u01/app/oracle/oradata/PROD/disk2/redo03.log 
--正常关库并启库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 1219760 bytes
Variable Size 121635664 bytes
Database Buffers 293601280 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/PROD/disk1/redo03.log'
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/PROD/disk2/redo03.log'
SQL> select * from v$log;
    GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1 1 26 104857600 2 NO INACTIVE 494650 09-APR-14
         5 1 27 104857600 2 NO INACTIVE 494656 09-APR-14
         3 1 29 104857600 2 NO CURRENT 497425 09-APR-14
         2 1 28 104857600 2 NO INACTIVE 496855 09-APR-14
注意:启库失败,同理应是正常关库,脏块都写完了,下面采用上一种方式即drop 或clear来修复日志文件。
--清空或删除丢失的当前日志组
SQL> alter database clear logfile group 3;
alter database clear logfile group 3
*
ERROR at line 1:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/PROD/disk2/redo03.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/PROD/disk1/redo03.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance PROD (thread 1) - cannot drop
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/PROD/disk1/redo03.log'
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/PROD/disk2/redo03.log'
可见无法clear或drop当前日志组,只能采用不完全恢复的方式修改(实际因正常关库,日志都已写完提交了)。(11g是可以clear和drop当前日志组的)
--recover database不完全修复
SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.

SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
PROD OPEN
总结:删除当前日志组成员并正常关库,启库会报错,且无法通过clear或drop当前日志组的方式进行修复,但可使用recover database until cancel的方式进行不完全修复,但启库时必须使用resetlogs或noresetlogs的方式才可。
---------------------------------------------------------------------------------------------------------
四、当前日志组丢失(非正常关库)
--查看日志信息
SQL> select * from v$log;
    GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1 1 2 104857600 2 NO CURRENT 497642 09-APR-14
         2 1 0 104857600 2 YES UNUSED 0
         3 1 1 104857600 2 NO INACTIVE 497641 09-APR-14
         5 1 0 104857600 2 YES UNUSED 0
SQL> select * from v$logfile;
    GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
         1 ONLINE /u01/app/oracle/oradata/PROD/disk1/redo01.log NO
         1 ONLINE /u01/app/oracle/oradata/PROD/disk2/redo01.log NO
         3 ONLINE /u01/app/oracle/oradata/PROD/disk1/redo03.log NO
         3 ONLINE /u01/app/oracle/oradata/PROD/disk2/redo03.log NO
         2 ONLINE /u01/app/oracle/oradata/PROD/disk1/redo02.log NO
         2 ONLINE /u01/app/oracle/oradata/PROD/disk2/redo02.log NO
         5 ONLINE /u01/app/oracle/oradata/PROD/disk2/redo05.log NO
         5 ONLINE /u01/app/oracle/oradata/PROD/disk1/redo05.log NO
8 rows selected.
注意:上一种场景不完全恢复后,日志组2与5都变为unused了。
--删除当前日志组所有成员
[oracle@gc1 disk2]$ rm /u01/app/oracle/oradata/PROD/disk1/redo01.log
[oracle@gc1 disk2]$ rm /u01/app/oracle/oradata/PROD/disk2/redo01.log
--非正常关库,且启库
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 1219760 bytes
Variable Size 121635664 bytes
Database Buffers 293601280 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/PROD/disk2/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/PROD/disk1/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
启库失败。
--采用recover database进行不完全恢复
SQL> recover database until cancel;
ORA-00279: change 497643 generated at 04/09/2014 19:03:30 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/product/10.2.0/db_1/dbs/arch1_2_844455690.dbf
ORA-00280: change 497643 for thread 1 is in sequence #2
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/PROD/disk1/redo01.log
ORA-00308: cannot open archived log '/u01/app/oracle/oradata/PROD/disk1/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/PROD/disk1/redo03.log
ORA-00310: archived log contains sequence 1; sequence 2 required
ORA-00334: archived log: '/u01/app/oracle/oradata/PROD/disk1/redo03.log'
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/PROD/disk3/system01.dbf'
SQL> recover database until cancel;
ORA-00279: change 497643 generated at 04/09/2014 19:03:30 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/product/10.2.0/db_1/dbs/arch1_2_844455690.dbf
ORA-00280: change 497643 for thread 1 is in sequence #2
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/PROD/disk1/redo02.log
ORA-00339: archived log does not contain any redo
ORA-00334: archived log: '/u01/app/oracle/oradata/PROD/disk1/redo02.log'
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/PROD/disk3/system01.dbf'
SQL> recover database until cancel;
ORA-00279: change 497643 generated at 04/09/2014 19:03:30 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/product/10.2.0/db_1/dbs/arch1_2_844455690.dbf
ORA-00280: change 497643 for thread 1 is in sequence #2
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/PROD/disk2/redo05.log
ORA-00339: archived log does not contain any redo
ORA-00334: archived log: '/u01/app/oracle/oradata/PROD/disk2/redo05.log'
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/PROD/disk3/system01.dbf'
由此可见,使用recover database until cancel执行不完全恢复失败,此时若有备份,可执行恢复,但若没有备份的情况下,要如何开库呢?
分析可知,开库的前提条件是要三个scn号一致,此时无法开库,是因为shutdown abort时,未生成全局检查点,scn号不一致,当前redo日志又被删除了(smon无法通过日志执行instance恢复),因此无法open数据库,这时只有修改pfile参数文件,增加一个参数设置_allow_resetlogs_corruption=true,让数据库可以在scn号不一致的情况下开库。
--修改pfile初始化参数文件,增设_allow_resetlogs_corruption=true参数设置
[oracle@gc1 dbs]$ vi initPROD.ora
--关库再启库
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 1219760 bytes
Variable Size 121635664 bytes
Database Buffers 293601280 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
SQL> select instance_name,status from v$instance;
ERROR:
ORA-03114: not connected to ORACLE
SQL> shutdown abort;
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL>
SQL> quit;
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@gc1 ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Apr 9 22:40:20 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 1219760 bytes
Variable Size 121635664 bytes
Database Buffers 293601280 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> alter database open;
Database altered.
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
            517649
SQL> select file#,checkpoint_change# from v$datafile;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1 517649
         2 517649
         3 517649
         4 517649
SQL> select file#,checkpoint_change# from v$datafile_header;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1 517649
         2 517649
         3 517649
         4 517649
--重新改回pfile文件,去掉刚新增的参数设置,再关库启库
[oracle@gc1 dbs]$ vi initPROD.ora

--关库,启库并查看scn号
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 1219760 bytes
Variable Size 121635664 bytes
Database Buffers 293601280 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
            519602
SQL> select file#,checkpoint_change# from v$datafile;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1 519602
         2 519602
         3 519602
         4 519602
SQL> select file#,checkpoint_change# from v$datafile_header;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1 519602
         2 519602
         3 519602
         4 519602
到时为止,可见日志恢复成功
总结:当前日志组成员全丢失,且非常关库,通过使用recover database until cancel不完全恢复无效,若无相关日志文件备份,则只能在pfile初始化参数文件中设置_allow_resetlogs_corruption=true,让数据库在三个scn号非一致的情况下也能开库,最后使用open resetlogs方式打开。这种方式是不推荐的,只是被逼无赖的情况下极端的处理方式,最后就算恢复成功了,因数据库完整性已受到破坏,不推荐再使用此库,而是建议再建一个实例,将此库的有效数据提取出去。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21251711/viewspace-1139028/,如需转载,请注明出处,否则将追究法律责任。

上一篇: redo log详述
下一篇: 归档日志
请登录后发表评论 登录
全部评论

注册时间:2009-03-30

  • 博文量
    106
  • 访问量
    667918