Oracle联机日志恢复案例

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

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

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

模拟环境：

1.查看联机日志状态信息
sys@SIQIAN11>select group#,sequence#,archived,status from v$log;
         1          4 NO  CURRENT
         2          2 YES INACTIVE
         3          3 YES INACTIVE
sys@SIQIAN11>select member from v$logfile;
/u01/oradata/siqian11g/redo01.log
/u01/oradata/siqian11g/redo03.log
/u01/oradata/siqian11g/redo02.log
2.关闭数据库并删除非当前联机日志
shutdown immediate
[oracle@siqian siqian11g]$ rm -f /u01/oradata/siqian11g/redo02.log
3.启动数据库并查看出错信息
ORA-00313: open failed for members of log group 2 of thread 1
ORA-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 ACTIVE
2.关闭数据库并删除所有第一组的联机日志
shutdown immediate
[oracle@siqian siqian11g]$ rm -f redo01.log
3.启动数据库并查看错误信息
ORA-00313: open failed for members of log group 1 of thread 1
ORA-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 archived
ORA-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  CURRENT
2.用test用户登录并向t01表加若干数据
test@SIQIAN11>select count(*) from t01;
  COUNT(*)
----------
        10
test@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(*)
----------
        15
3.关闭数据库并删除数据文件与当前日志文件
shutdown immediate
[oracle@siqian siqian11g]$ rm -f redo03.log
[oracle@siqian siqian11g]$ rm -f *.dbf
4.启动数据库并查看出错信息
startup
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-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            2267395
8 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 errors
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/oradata/siqian11g/redo03.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional 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 1
ORA-00289: suggestion : /backup/arch/arch_1_819452646_9.log
ORA-00280: change 2266772 for thread 1 is in sequence #9
Specify 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 status
Linux Error: 2: No such file or directory
Additional 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 INACTIVE
9.通过基于cancel的不完全恢复来恢复数据库
sys@SIQIAN11>recover database until cancel;
ORA-00279: change 2266772 generated at 06/30/2013 10:14:43 needed for thread 1
ORA-00289: suggestion : /backup/arch/arch_1_819452646_9.log
ORA-00280: change 2266772 for thread 1 is in sequence #9
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
10.以resetlogs方式打开数据库
sys@SIQIAN11>alter database open resetlogs;
11.验证
test@SIQIAN11>select count(*) from t01;
  COUNT(*)
----------
        10
可见只能恢复到上一次归档的模样。
12.冷备