redo log恢复

最新推荐文章于 2024-10-11 17:04:33 发布
最新推荐文章于 2024-10-11 17:04:33 发布
阅读量134 收藏
点赞数
文章标签： 数据库 操作系统
一、部分日志成员丢失（不论是不是正常关机）
--查看系统日志信息
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
bb
--关库再启库
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
bb

--关库，启库并查看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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21251711/viewspace-1139028/

crbzijz188696507
关注
oracle redo01. log,redo01.log恢复问题
weixin_39725650的博客
04-04 707
背景： 在正常oracle11g正常open状态，我操作系统mv 了redo01.log然后rman不知道该怎么恢复。可是通过下面的脚本居然重建了redo01.log我不知道哪一步能重建的.看哪个日志可以知道呢？ps：操作过程如下：SQL> select member from v$logfile;MEMBER-----------------------------------------...
oracle redo log恢复,REDO LOG 丢失恢复方法小结
weixin_39839018的博客
04-06 911
一.在shutdown immediate情况下1.SQL> shutdown immediate数据库已经关闭。已经卸载数据库。ORACLE 例程已经关闭。2.删除所有联机日志文件3.SQL> startupORACLE 例程已经启动。Total System Global Area 135338868 bytesFixed Size 453...
Redo Log的恢复
csdn466460455的博客
02-12 871
完全恢复和不完全恢复：完全恢复一定不丢数据；不完全恢复可以丢数据 数据库对完全恢复的定义：控制文件知道数据库的恢复终点（on disk rba）；并且按照自己设定的路线恢复到了终点 数据库对不完全恢复的定义： ...
redo日志丢失的恢复
lyciswaiting的专栏
05-22 352
方法一：用命令清空日志组方法1 正常关闭数据库SQL> shutdown数据库已经关闭。已经卸载数据库。ORACLE 例程已经关闭。2 删除所有的重做日志文件3 启动数据库SQL> startupORACLE 例程已经启动。Total System Global Area  167772160 bytesFixed Size              
(转)oracle redolog损坏的处理办法
cov98559的博客
04-10 270
redolog损坏的处理办法 损坏非当前联机日志大家都清楚，联机日志分为当前联机日志和非当前联机日志，非当前联机日志的损坏是比较简单的，一般通过clear命令就可以解决问题。 1、启动数据库，遇到ORA-00...
MySQL的redo log、undo log、binlog
12-14
- 作用不同：redo log保证事务层面的持久性，binlog负责数据库级别的恢复。 - 内容不同：redo log为物理日志，记录物理变化；binlog为逻辑日志，记录SQL逻辑。 综上所述，MySQL的这三种日志在数据库的事务处理、...
详细讲解一下redolog恢复数据的过程
07-15
redolog恢复数据的过程主要分为以下几个步骤： 1. 检查数据库状态：首先要检查数据库的状态，确定数据库是否处于正常可用的状态。如果数据库无法正常启动，需要采取相应的修复措施，例如修复损坏的文件或恢复备份等...
数据库innodb使用redo log数据恢复原理
qq_24263755的博客
08-30 1285
数据库innodb使用redo log数据恢复原理
介绍Oracle redo log 恢复步骤的文档
07-25
### Oracle Redo Log恢复步骤详解 #### 一、引言 在Oracle数据库系统中，Redo Log机制是确保数据一致性和可靠性的关键组件之一。它记录了所有对数据库所做的更改，以便在出现故障时能够进行恢复操作。本文档将详细...
丢失所有redo-数据文件-控制文件的恢复
02-09
丢失所有redo-数据文件-控制文件的恢复
redo日志文件丢失分情况恢复
shineboy123456的博客
03-29 2842
redo 日志文件丢失分情况恢复
ORA-01033问题：定位redo日志进行不完全恢复启库实战案例
狗剩儿的记忆博客
09-09 450
再以上截图中，有个信息特别重要：就是ORA-00280中 change xxxxxx 和 sequence #815,这类型的故障,一般是由于缺少日志(比如数据库非归档模式,或者归档日志丢失),导致某些文件无法正常online。完成以上操作后，我们就可以进行open数据库，但是这里要注意的是我们在不完全恢复后，日志序列号中断，重新open数据库的话就需要重置日志序列。recover database的原理是数据库使用控制文件的scn作为恢复的终点，将数据文件block恢复到控制文件所记录的scn为止。
恢复误删除的redo log
Zhong Weicheng Study Notes
12-18 3084
恢复非当前的日志组 [oracle@zwc primarydb]$ rm -rf redo01* SYS@zwcdb:~>startup ORACLE instance started. Total System Global Area 285212672 bytes Fixed SIZE 1218992 bytes Variable SIZE
Oracle redo log 大小及组数的设置（在线修改日志组数目及组成员个数）
demonson的专栏
09-17 3488
1.查看当前系统现有的redo log状况（组数/大小/名称/状态） desc">SYS@ORCL>desc v$log  Name                                      Null?    Type  ----------------------------------------- -------- ---------------------------
Mysql工作原理——redo日志文件和恢复操作
mashaokang1314的博客
02-21 2192
redo日志刷盘时机 log Buffer空间不足时 log buffer 的大小是有限的，如果不停的往这个有限大小的log buffer里散入日志，很快就会被填满，如果当前写入log buffer的redo日志量已经占满了log buffer总容量的大约一半左右，就需要把这些日志刷新到磁盘上。 事务提交时 之所以使用redo日志主要是因为它占用的空间少，还是顺序写，在事务提交时可以不把修改过的Buffer Pool页面刷新到磁盘，但是为了保证持久性，必须要把这些页面对应的redo日志刷新到磁盘。 后台线
图解Redis 03 | List数据类型的原理及应用场景
大厂小码农的博客
10-10 797
List是一个简单的字符串列表，按照元素的插入顺序进行排序。您可以从头部或尾部添加元素到这个列表中。列表的最大长度为2^32 - 1，即支持多达40亿个元素。
MySQL数据库的详细学习步骤
最新发布
2401_87352036的博客
10-11 1224
MySQL数据库的详细学习步骤可以归纳为以下几个阶段，每个阶段都包含了特定的学习内容和目标。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值