实践redo log 恢复
1.在OS上删除非current redo log# lltotal 153780-rw-r----- 1 oracle dba 52429312 Feb 11 10:30 redo01b.log-rw-r----- 1 oracle dba 52429312 Feb 11 11:28 redo02b.log-rw-r----- 1 oracle dba 52429312 Feb 11 10:30 redo03b.log# rm /oracle/data/redob/redo01b.logrm: remove regular file `/oracle/data/redob/redo01b.log'? yes# lltotal 102520-rw-r----- 1 oracle dba 52429312 Feb 11 11:28 redo02b.log-rw-r----- 1 oracle dba 52429312 Feb 11 10:30 redo03b.log
删除后，查询：
SQL> select * from v$logfile; GROUP# STATUSTYPE MEMBER IS_---------- ------- ------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --- 3 ONLINE/oracle/data/redoa/redo03a.log NO 3 ONLINE/oracle/data/redob/redo03b.log NO 2 ONLINE/oracle/data/redoa/redo02a.log NO 2 ONLINE/oracle/data/redob/redo02b.log NO 1 ONLINE/oracle/data/redoa/redo01a.log NO 1 ONLINE/oracle/data/redob/redo01b.log NO6 rows selected.
没有结果，switch一下：
SQL> alter system switch logfile;
alert log出现：
Thread 1 advanced to log sequence 24Current log# 3 seq# 24 mem# 0: /oracle/data/redoa/redo03a.logCurrent log# 3 seq# 24 mem# 1: /oracle/data/redob/redo03b.logThu Feb 11 11:30:08 2010Errors in file /oracle/admin/williamdb/bdump/williamdb_arc0_24908.trc:ORA-00313: open failed for members of log group 1 of thread 1ORA-00312: online log 1 thread 1: '/oracle/data/redob/redo01b.log'ORA-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3
无法switch
重启一下DB：
SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startupORACLE instance started.Total System Global Area285212672 bytesFixed Size 1261348 bytesVariable Size 159383772 bytesDatabase Buffers 117440512 bytesRedo Buffers 7127040 bytesDatabase mounted.Database opened.
没有报错，再看看alert log：
Thu Feb 11 11:33:37 2010Errors in file /oracle/admin/williamdb/bdump/williamdb_lgwr_27256.trc:ORA-00313: open failed for members of log group 1 of thread 1ORA-00312: online log 1 thread 1: '/oracle/data/redob/redo01b.log'ORA-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3
报错了。
问题：是否自动跳过有问题的group？
再查询：
SQL> select * from v$logfile; GROUP# STATUSTYPE MEMBER IS_---------- ------- ------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --- 3 ONLINE/oracle/data/redoa/redo03a.log NO 3 ONLINE/oracle/data/redob/redo03b.log NO 2 ONLINE/oracle/data/redoa/redo02a.log NO 2 ONLINE/oracle/data/redob/redo02b.log NO 1 ONLINE/oracle/data/redoa/redo01a.log NO 1 INVALID ONLINE/oracle/data/redob/redo01b.log NO6 rows selected.
INVALID了，开始恢复：
SQL> alter database clear logfile group 1;Database altered.SQL> SELECT * FROM v$logfile; GROUP# STATUSTYPE MEMBER IS_---------- ------- ------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --- 3 ONLINE/oracle/data/redoa/redo03a.log NO 3 ONLINE/oracle/data/redob/redo03b.log NO 2 ONLINE/oracle/data/redoa/redo02a.log NO 2 ONLINE/oracle/data/redob/redo02b.log NO 1 ONLINE/oracle/data/redoa/redo01a.log NO 1 ONLINE/oracle/data/redob/redo01b.log NO6 rows selected.SQL> select * from v$log; GROUP# THREAD#SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 0 52428800 2 YES UNUSED 981183 11-FEB-10 2 1 22 52428800 2 YES INACTIVE 975410 11-FEB-10 3 1 24 52428800 2 NOCURRENT 981210 11-FEB-10
再到OS的目录看一下：
# lltotal 153780-rw-r----- 1 oracle dba 52429312 Feb 11 11:44 redo01b.log-rw-r----- 1 oracle dba 52429312 Feb 11 11:33 redo02b.log-rw-r----- 1 oracle dba 52429312 Feb 11 11:44 redo03b.log#
看到redo log自动生成回来了，group的状态变成unused。
switch一下：
Thread 1 advanced to log sequence 25Current log# 1 seq# 25 mem# 0: /oracle/data/redoa/redo01a.logCurrent log# 1 seq# 25 mem# 1: /oracle/data/redob/redo01b.log
成功恢复！
2.current
删除group 1的两个member
# lltotal 153780-rw-r----- 1 oracle dba 52429312 Feb 11 16:59 redo01a.log-rw-r----- 1 oracle dba 52429312 Feb 11 11:33 redo02a.log-rw-r----- 1 oracle dba 52429312 Feb 11 11:48 redo03a.log# rm -f redo01a.log # lltotal 102520-rw-r----- 1 oracle dba 52429312 Feb 11 17:01 redo02a.log-rw-r----- 1 oracle dba 52429312 Feb 11 17:01 redo03a.log# # # cd ..# cd redob/# lltotal 153780-rw-r----- 1 oracle dba 52429312 Feb 11 17:04 redo01b.log-rw-r----- 1 oracle dba 52429312 Feb 11 17:02 redo02b.log-rw-r----- 1 oracle dba 52429312 Feb 11 17:02 redo03b.log# mv redo01b.log redo01b.log.bak # lltotal 153780-rw-r----- 1 oracle dba 52429312 Feb 11 17:05 redo01b.log.bak-rw-r----- 1 oracle dba 52429312 Feb 11 17:02 redo02b.log-rw-r----- 1 oracle dba 52429312 Feb 11 17:02 redo03b.log
然后switch一下，alert log显示：
Thu Feb 11 17:00:20 2010Errors in file /oracle/admin/williamdb/bdump/williamdb_arc0_27278.trc:ORA-00313: open failed for members of log group 1 of thread 1ORA-00312: online log 1 thread 1: '/oracle/data/redoa/redo01a.log'ORA-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3Thu Feb 11 17:00:20 2010Errors in file /oracle/admin/williamdb/bdump/williamdb_arc0_27278.trc:ORA-00313: open failed for members of log group 1 of thread 1ORA-00312: online log 1 thread 1: '/oracle/data/redoa/redo01a.log'ORA-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3
查询一下v$logfile状态，什么都没有看出来
SQL> select * from v$logfile; GROUP# STATUSTYPE MEMBER IS_---------- ------- ------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --- 3 ONLINE/oracle/data/redoa/redo03a.log NO 3 ONLINE/oracle/data/redob/redo03b.log NO 2 ONLINE/oracle/data/redoa/redo02a.log NO 2 ONLINE/oracle/data/redob/redo02b.log NO 1 ONLINE/oracle/data/redoa/redo01a.log NO 1 ONLINE/oracle/data/redob/redo01b.log NO6 rows selected.
重启一下DB：
SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startupORACLE instance started.Total System Global Area285212672 bytesFixed Size 1261348 bytesVariable Size 167772380 bytesDatabase Buffers 109051904 bytesRedo Buffers 7127040 bytesDatabase mounted.ORA-00313: open failed for members of log group 1 of thread 1ORA-00312: online log 1 thread 1: '/oracle/data/redoa/redo01a.log'ORA-00312: online log 1 thread 1: '/oracle/data/redob/redo01b.log'SQL> select status from v$instance;STATUS------------MOUNTEDSQL> alter database open;alter database open*ERROR at line 1:ORA-00313: open failed for members of log group 1 of thread 1ORA-00312: online log 1 thread 1: '/oracle/data/redoa/redo01a.log'ORA-00312: online log 1 thread 1: '/oracle/data/redob/redo01b.log'
打开不了database，好了，开始恢复：
SQL> recover database until cancel;Media recovery complete.SQL> alter database open resetlogs;Database altered.SQL> select status from v$instance;STATUS------------OPEN
顺利恢复了，看看alert log情况：
Thu Feb 11 17:16:01 2010Setting recovery target incarnation to 3Thu Feb 11 17:16:01 2010Assigning activation ID 3048540245 (0xb5b50855)Thread 1 advanced to log sequence 2Thread 1 opened at log sequence 2Current log# 2 seq# 2 mem# 0: /oracle/data/redoa/redo02a.logCurrent log# 2 seq# 2 mem# 1: /oracle/data/redob/redo02b.logSuccessful open of redo thread 1Thu Feb 11 17:16:02 2010MTTR advisory is disabled because FAST_START_MTTR_TARGET is not setThu Feb 11 17:16:02 2010SMON: enabling cache recoveryThu Feb 11 17:16:02 2010Successfully onlined Undo Tablespace 1.Dictionary check beginningDictionary check completeThu Feb 11 17:16:02 2010SMON: enabling tx recoveryThu Feb 11 17:16:02 2010Database Characterset is WE8ISO8859P1replication_dependency_tracking turned off (no async multimaster replication found)Starting background process QMNCQMNC started with pid=20, OS id=6516Thu Feb 11 17:16:03 2010LOGSTDBY: Validating controlfile with logical metadataThu Feb 11 17:16:03 2010LOGSTDBY: Validation completeThu Feb 11 17:16:03 2010db_recovery_file_dest_size of 100 MB is 0.00% used. This is auser-specified limit on the amount of space that will be used by thisdatabase for recovery-related files, and does not reflect the amount ofspace available in the underlying filesystem or ASM diskgroup.Thu Feb 11 17:16:05 2010Starting control autobackupControl autobackup written to DISK device handle '/oracle/product/10.2.0/dbs/oraclebackupc-3047744130-20100211-00'Completed: alter database open resetlogs
OS上的情况
# lltotal 205040-rw-r----- 1 oracle dba 52429312 Feb 11 17:16 redo01b.log-rw-r----- 1 oracle dba 52429312 Feb 11 17:05 redo01b.log.bak-rw-r----- 1 oracle dba 52429312 Feb 11 17:16 redo02b.log-rw-r----- 1 oracle dba 52429312 Feb 11 17:16 redo03b.log#
没有问题，文件也自动重建了。
但是有个奇怪的现象：
SQL> select * from v$log; GROUP# THREAD#SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 1 52428800 2 YES INACTIVE 1001509 11-FEB-10 2 1 2 52428800 2 NOCURRENT 1001510 11-FEB-10 3 1 0 52428800 2 YES UNUSED 0SQL> select * from v$logfile; GROUP# STATUSTYPE MEMBER IS_---------- ------- ------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --- 3 ONLINE/oracle/data/redoa/redo03a.log NO 3 ONLINE/oracle/data/redob/redo03b.log NO 2 ONLINE/oracle/data/redoa/redo02a.log NO 2 ONLINE/oracle/data/redob/redo02b.log NO 1 ONLINE/oracle/data/redoa/redo01a.log NO 1 ONLINE/oracle/data/redob/redo01b.log NO6 rows selected.
怎么会是group 3 unused呢？
提醒：alter database open resetlogs;后一定要重新做全备
有时间测试一下current redo log丢失对数据丢失的影响
页:
[1]