你好,游客 登录 注册 搜索
背景:
阅读新闻

Oracle联机日志恢复案例

[日期:2013-07-16] 来源:Linux社区  作者:panjc_job [字体: ]

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

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

案例二:非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;

    GROUPSEQUENCE# 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;

    GROUPSEQUENCE# 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;

    GROUPSEQUENCE# 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.冷备

更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

linux
相关资讯       Oracle联机日志 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款