如果损坏的是current redo log (select group#,sequence#,archived,status from v$log;)
有两种情况:
A、 数据库是正常关闭的。当前日志组的损坏就可以直接用alter database clear unarchived logfile group n来重建。
启动数据库到mount状态
SQL> select group#,sequence#,archived,status from v$log;
GROUP# SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
1 0 YES UNUSED
3 3 NO CURRENT
2 2 YES INACTIVE
SQL> alter database clear unarchived logfile group 3;
Database altered.
SQL> alter database open;
Database altered.
B、 如果数据库是非正常关闭(shutdown abort),current redo log 损坏,最好的恢复办法就是通过不完全恢复,可以保证数据库的一致性,但会丢失数据。
SQL> select group#,sequence#,archived,status from v$log;
GROUP# SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
1 32 NO CURRENT
3 31 YES INACTIVE
2 30 YES INACTIVE
执行clear logfile group 1会报如下错误。
SQL> alter database clear unarchived logfile group 1;
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance orcl (thread 1)
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01.log'
此时需要进行不完全恢复
RMAN> restore database;
Starting restore at 02-DEC-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/full_ORCL_20121202_12_1.bak
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/full_ORCL_20121202_12_1.bak tag=TAG20121202T141019
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 02-DEC-12
SQL> recover database until cancel;
ORA-00279: change 274326 generated at 12/02/2012 14:10:20 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/oradata/arch/1_22_800911110.dbf
ORA-00280: change 274326 for thread 1 is in sequence #22
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
Auto
…………
ORA-00279: change 274396 generated at 12/02/2012 14:11:18 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/oradata/arch/1_32_800911110.dbf
ORA-00280: change 274396 for thread 1 is in sequence #32
ORA-00278: log file '/u01/app/oracle/oradata/arch/1_31_800911110.dbf' no longer
needed for this recovery
ORA-00308: cannot open archived log
'/u01/app/oracle/oradata/arch/1_32_800911110.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> alter database open resetlogs;
Database altered.
恢复到当前日志的SEQUENCE#为32,不完全恢复结束。这种办法恢复的数据库会丢失当前联机日志中的事务数据。恢复成功之后,记得再做一次数据库的全备份。