Oracle丢失重做日志的几种场景恢复

来源:转载

实验环境:RHEL6.4 + Oracle 11.2.0.4

一、丢失重做日志组中成员

1.1 故障模拟 1.2 处理方法 1.3 实际处理过程

二、丢失重做日志组

2.1 丢失INACTIVE重做日志组 2.2 丢失ACTIVE重做日志组 2.3 丢失CURRENT重做日志组

Reference

环境准备 SQL> set linesize 160 SQL> col member for a80SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_---------- ------- ------- -------------------------------------------------------------------------------- ---3ONLINE /u01/oradata/ORADB/onlinelog/o1_mf_3_c3g92rg3_.log NO3ONLINE /u01/app/oracle/fast_recovery_area/ORADB/onlinelog/o1_mf_3_c3g92rmf_.logYES2ONLINE /u01/oradata/ORADB/onlinelog/o1_mf_2_c3g92qkl_.log NO2ONLINE /u01/app/oracle/fast_recovery_area/ORADB/onlinelog/o1_mf_2_c3g92qoq_.logYES1ONLINE /u01/oradata/ORADB/onlinelog/o1_mf_1_c3g92prr_.log NO1ONLINE /u01/app/oracle/fast_recovery_area/ORADB/onlinelog/o1_mf_1_c3g92px4_.logYES6 rows selected.SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------ ------------ ------------1 1 4 52428800 512 2 NO CURRENT 1917689 03-NOV-15 2.8147E+142 1 2 52428800 512 2 YES INACTIVE 1861002 02-NOV-151888519 03-NOV-153 1 3 52428800 512 2 YES INACTIVE 1888519 03-NOV-151917689 03-NOV-15 一、丢失重做日志组中成员 1.1 故障模拟

模拟第2组日志组一个成员丢失。

SQL> startup forceORACLE instance started.Total System Global Area 304807936 bytesFixed Size2252744 bytesVariable Size 205520952 bytesDatabase Buffers 92274688 bytesRedo Buffers 4759552 bytesDatabase mounted.Database opened.SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_---------- ------- ------- -------------------------------------------------------------------------------- ---3ONLINE /u01/oradata/ORADB/onlinelog/o1_mf_3_c3g92rg3_.log NO3ONLINE /u01/app/oracle/fast_recovery_area/ORADB/onlinelog/o1_mf_3_c3g92rmf_.logYES2 INVALID ONLINE /u01/oradata/ORADB/onlinelog/o1_mf_2_c3g92qkl_.log NO2ONLINE /u01/app/oracle/fast_recovery_area/ORADB/onlinelog/o1_mf_2_c3g92qoq_.logYES1ONLINE /u01/oradata/ORADB/onlinelog/o1_mf_1_c3g92prr_.log NO1ONLINE /u01/app/oracle/fast_recovery_area/ORADB/onlinelog/o1_mf_1_c3g92px4_.logYES6 rows selected. 1.2 处理方法

删除无效成员,然后在组中添加新成员。

SQL> alter database drop logfile member '/u01/oradata/ORADB/onlinelog/o1_mf_2_c3g92qkl_.log';SQL> alter database add logfile member '/u01/oradata/ORADB/onlinelog/redo02a.log' to group 2; 1.3 实际处理过程 SQL> alter database drop logfile member '/u01/oradata/ORADB/onlinelog/o1_mf_2_c3g92qkl_.log';alter database drop logfile member '/u01/oradata/ORADB/onlinelog/o1_mf_2_c3g92qkl_.log'*ERROR at line 1:ORA-01609: log 2 is the current log for thread 1 - cannot drop membersORA-00312: online log 2 thread 1: '/u01/oradata/ORADB/onlinelog/o1_mf_2_c3g92qkl_.log'ORA-00312: online log 2 thread 1: '/u01/app/oracle/fast_recovery_area/ORADB/onlinelog/o1_mf_2_c3g92qoq_.log'SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------ ------------ ------------1 1 7 52428800 512 2 YES INACTIVE 1920749 03-NOV-151940772 03-NOV-152 1 8 52428800 512 2 NO CURRENT 1940772 03-NOV-15 2.8147E+143 1 6 52428800 512 2 YES INACTIVE 1920739 03-NOV-151920749 03-NOV-15SQL> alter system switch logfile;System altered.SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------ ------------ ------------1 1 7 52428800 512 2 YES INACTIVE 1920749 03-NOV-151940772 03-NOV-152 1 8 52428800 512 2 YES ACTIVE 1940772 03-NOV-151941407 03-NOV-153 1 9 52428800 512 2 NO CURRENT 1941407 03-NOV-15 2.8147E+14SQL> alter database drop logfile member '/u01/oradata/ORADB/onlinelog/o1_mf_2_c3g92qkl_.log';Database altered.SQL> alter database add logfile member '/u01/oradata/ORADB/onlinelog/o1_mf_2_c3g92qkl_.log' to group 2;alter database add logfile member '/u01/oradata/ORADB/onlinelog/o1_mf_2_c3g92qkl_.log' to group 2*ERROR at line 1:ORA-01276: Cannot add file /u01/oradata/ORADB/onlinelog/o1_mf_2_c3g92qkl_.log. File has an Oracle Managed Files file name.SQL> alter database add logfile member '/u01/oradata/ORADB/onlinelog/redo02a.log' to group 2;Database altered.SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_---------- ------- ------- -------------------------------------------------------------------------------- ---3ONLINE /u01/oradata/ORADB/onlinelog/o1_mf_3_c3g92rg3_.log NO3ONLINE /u01/app/oracle/fast_recovery_area/ORADB/onlinelog/o1_mf_3_c3g92rmf_.logYES2 INVALID ONLINE /u01/oradata/ORADB/onlinelog/redo02a.log NO2ONLINE /u01/app/oracle/fast_recovery_area/ORADB/onlinelog/o1_mf_2_c3g92qoq_.logYES1ONLINE /u01/oradata/ORADB/onlinelog/o1_mf_1_c3g92prr_.log NO1ONLINE /u01/app/oracle/fast_recovery_area/ORADB/onlinelog/o1_mf_1_c3g92px4_.logYES6 rows selected.SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------ ------------ ------------1 1 7 52428800 512 2 YES INACTIVE 1920749 03-NOV-151940772 03-NOV-152 1 8 52428800 512 2 YES ACTIVE 1940772 03-NOV-151941407 03-NOV-153 1 9 52428800 512 2 NO CURRENT 1941407 03-NOV-15 2.8147E+14SQL> alter system switch logfile;System altered.SQL> alter system switch logfile;System altered.SQL> alter system switch logfile;System altered.SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_---------- ------- ------- -------------------------------------------------------------------------------- ---3ONLINE /u01/oradata/ORADB/onlinelog/o1_mf_3_c3g92rg3_.log NO3ONLINE /u01/app/oracle/fast_recovery_area/ORADB/onlinelog/o1_mf_3_c3g92rmf_.logYES2ONLINE /u01/oradata/ORADB/onlinelog/redo02a.log NO2ONLINE /u01/app/oracle/fast_recovery_area/ORADB/onlinelog/o1_mf_2_c3g92qoq_.logYES1ONLINE /u01/oradata/ORADB/onlinelog/o1_mf_1_c3g92prr_.log NO1ONLINE /u01/app/oracle/fast_recovery_area/ORADB/onlinelog/o1_mf_1_c3g92px4_.logYES6 rows selected. 二、丢失重做日志组 2.1 丢失INACTIVE重做日志组 2.1.1 清除归档的INACTIVE重做日志组 SQL> alter database clear logfile group 2;Database altered.SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------ ------------ ------------ 1 1 4 52428800 512 2 YES INACTIVE 1962650 03-NOV-15 1962653 03-NOV-15 2 1 0 52428800 512 3 YES UNUSED 1962653 03-NOV-15 1962685 03-NOV-15 3 1 6 52428800 512 2 NO CURRENT 1962685 03-NOV-15 2.8147E+14

可以看到,clear后的日志组状态为UNUSED。

2.1.2 清除未归档的INACTIVE重做日志组 #清除未归档的INACTIVE重做日志组,不会丢失任何已提交事物,但清除后必须完全备份,从而确保可以执行完整恢复。SQL> alter database clear logfile unarchived group 2;#需要考虑脱机文件是否需要已经清除的日志文件组才能重新联机。如果需要,那么只能删除包含脱机文件的表空间。SQL> alter database clear logfile unarchived group 2 unrecoverable datafile;#控制文件备份到跟踪文件目录中,默认存放是udump目录下SQL> alter database backup controlfile to trace; 2.2 丢失ACTIVE重做日志组

这个时候尝试生成检查点,

SQL> alter system checkpoint; 2.2.1 第一种情况:命令成功执行

命令成功执行,会将所有已经提交的更改写入磁盘的数据文件中。就跟INACTIVE重做日志组处理流程一致了。

2.2.2 第二种情况:命令执行出现故障

命令执行出现故障,就只能执行不完整恢复。

2.3 丢失CURRENT重做日志组

数据库mount模式下执行不完整恢复,最后使用RESETLOGS打开数据库。

SQL> alter database open resetlogs; Reference OCP 认证考试指南 (1Z0-053)[M]. 清华大学出版社, 2010.



分享给朋友:
您可能感兴趣的文章:
随机阅读: