当前位置：首页 -> 基础 -> 数据库编程

Oracle联机日志恢复案例(一)

2014-11-24 09:39:20 · 作者: · 浏览: 2
标签: Oracle 联机 日志 恢复 案例
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#
-------------