Oracle联机日志恢复案例

案例一：在多路化的日志成员中，单个成员丢失

这种情况很好解决，只需将没损坏的联机日志拷贝到那个坏的日志去就行。

案例二：非current的联机日志所有成员丢失

模拟环境：1.查看联机日志状态信息sys@SIQIAN11>select group#,sequence#,archived,status from v$log; 1 4 NO CURRENT 2 2 YES INACTIVE 3 3 YES INACTIVEsys@SIQIAN11>select member from v$logfile;/u01/oradata/siqian11g/redo01.log/u01/oradata/siqian11g/redo03.log/u01/oradata/siqian11g/redo02.log2.关闭数据库并删除非当前联机日志shutdown immediate[Oracle@siqian siqian11g]$ rm -f /u01/oradata/siqian11g/redo02.log3.启动数据库并查看出错信息ORA-00313: open failed for members of log group 2 of thread 1ORA-00312: online log 2 thread 1: "/u01/oradata/siqian11g/redo02.log"4.清理第二组日志sys@SIQIAN11>alter database clear logfile group 2;5.打开数据库alter database open;

案例三：current的联机日志所有成员丢失

模拟环境：1.查看联机日志状态信息sys@SIQIAN11>select group#,sequence#,archived,status from v$log; GROUP# SEQUENCE# ARC STATUS-------------------- --- ---------------- 1 7 NO CURRENT 2 5 YES INACTIVE 3 6 YES ACTIVE2.关闭数据库并删除所有第一组的联机日志shutdown immediate[oracle@siqian siqian11g]$ rm -f redo01.log3.启动数据库并查看错误信息ORA-00313: open failed for members of log group 1 of thread 1ORA-00312: online log 1 thread 1: "/u01/oradata/siqian11g/redo01.log"4.尝试用上面的方法清理第一组日志sys@SIQIAN11>alter database clear logfile group 1;alter database clear logfile group 1*ERROR at line 1:ORA-00350: log 1 of instance siqian11g （thread 1） needs to be archivedORA-00312: online log 1 thread 1: "/u01/oradata/siqian11g/redo01.log"5.不完全恢复数据库sys@SIQIAN11>recover database until cancel;Media recovery complete.6.以resetlogs方式打开数据库sys@SIQIAN11>alter database open resetlogs;7.冷备

案例四：数据文件和当前日志组全部丢失

模拟环境：1.查看当前日志信息sys@SIQIAN11>select group#,sequence#,archived,status from v$log; GROUP# SEQUENCE# ARC STATUS-------------------- --- ---------------- 1 7 YES ACTIVE 2 8 YES ACTIVE 3 9 NO CURRENT2.用test用户登录并向t01表加若干数据test@SIQIAN11>select count（*） from t01; COUNT（*）---------- 10test@SIQIAN11>begin 2 for i in 11..15 3 loop 4 insert into t01 values（i）; 5 end loop; 6 end; 7 /PL/SQL procedure successfully completed.commit;test@SIQIAN11>select count（*） from t01; COUNT（*）---------- 153.关闭数据库并删除数据文件与当前日志文件shutdown immediate[oracle@siqian siqian11g]$ rm -f redo03.log[oracle@siqian siqian11g]$ rm -f *.dbf4.启动数据库并查看出错信息startupORA-01157: cannot identify/lock data file 1 - see DBWR trace fileORA-01110: data file 1: "/u01/oradata/siqian11g/system01.dbf"sys@SIQIAN11>select file#,error from v$recover_file; FILE# ERROR--------------------------------------------------------------------------- 1 FILE NOT FOUND 2 FILE NOT FOUND 3 FILE NOT FOUND 4 FILE NOT FOUND 5 FILE NOT FOUND 6 FILE NOT FOUND 7 FILE NOT FOUND 8 FILE NOT FOUND 8 rows selected.5.还原数据文件[oracle@siqian siqian11g]$ cp /backup/cold/ *.dbf /u01/oradata/siqian11g/6.查看控制文件和数据文件中的SCN信息sys@SIQIAN11>select file#,checkpoint_change# from v$datafile; FILE# CHECKPOINT_CHANGE#---------------------------- 1 2267395 2 2267395 3 2267395 4 2267395 5 2267395 6 2267395 7 2267395 8 22673958 rows selected.sys@SIQIAN11>select file#,checkpoint_change# from v$datafile_header; FILE# CHECKPOINT_CHANGE#---------------------------- 1 2266285 2 2266285 3 2266285 4 2266285 5 2266285 6 2266285 7 2266285 8 2266285 8 rows selected.发现控制文件中的SCN号比较新，因为数据文件是从冷备那边拷贝来的。7.尝试恢复数据库sys@SIQIAN11>recover database;ORA-00283: recovery session canceled due to errorsORA-00313: open failed for members of log group 3 of thread 1ORA-00312: online log 3 thread 1: "/u01/oradata/siqian11g/redo03.log"ORA-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3刚才把当前的日志删除了，所以找不到日志来恢复8.用until cancel方式来恢复数据库sys@SIQIAN11>recover database until cancel;ORA-00279: change 2266772 generated at 06/30/2013 10:14:43 needed for thread 1ORA-00289: suggestion : /backup/arch/arch_1_819452646_9.logORA-00280: change 2266772 for thread 1 is in sequence #9Specify log: {<RET>=suggested | filename | AUTO | CANCEL}ORA-00308: cannot open archived log "/backup/arch/arch_1_819452646_9.log"ORA-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3查看当前日志信息sys@SIQIAN11>select group#,sequence#,archived,status from v$log; GROUP# SEQUENCE# ARC STATUS-------------------- --- ---------------- 1 7 YES INACTIVE 3 9 NO CURRENT 2 8 YES INACTIVE9.通过基于cancel的不完全恢复来恢复数据库sys@SIQIAN11>recover database until cancel;ORA-00279: change 2266772 generated at 06/30/2013 10:14:43 needed for thread 1ORA-00289: suggestion : /backup/arch/arch_1_819452646_9.logORA-00280: change 2266772 for thread 1 is in sequence #9Specify log: {<RET>=suggested | filename | AUTO | CANCEL}cancelMedia recovery cancelled.10.以resetlogs方式打开数据库sys@SIQIAN11>alter database open resetlogs;11.验证test@SIQIAN11>select count（*） from t01; COUNT（*）---------- 10可见只能恢复到上一次归档的模样。12.冷备更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12Oracle控制文件恢复案例Oracle触发器中增删改查本表相关资讯 Oracle联机日志

Oracle当前联机日志组损坏的处理（08/19/2013 09:41:18）

Oracle联机日志文件丢失或损坏的处（01/09/2013 08:47:40）

本文评论查看全部评论（0）

评论声