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

online redo日志文件损坏恢复

[日期:2015-01-11] 来源:Linux社区  作者:aaron8219 [字体: ]

online redo日志文件对数据库是非常重要的,当current日志文件损坏,通常就意味着要丢失数据,但是也不是绝对的,可以通过一定的手段对redo日志文件进行恢复,运气好的话,未提交数据还是不会丢失的。
 
[Oracle@zlm2 backup]$ sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 31 22:53:23 2014
 
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> conn zlm/zlm
Connected.
 
--创建测试表
SQL> create table test(tbid number(10));
 
Table created.
 
--插入数据
SQL> insert into test values(1);

1 row created.
 
注意,并未对此insert操作commit
 
SQL> select group#,sequence#,status,first_change# from v$log;
 
    GROUP#  SEQUENCE# STATUS           FIRST_CHANGE#
---------- ---------- ---------------- -------------
         1         55 ACTIVE                 1723785
         2         56 CURRENT                1723866
         3         54 INACTIVE               1723562
 
SQL> !
[oracle@zlm2 backup]$ cd /u01/app/oracle/oradata/zlm11g/
[oracle@zlm2 zlm11g]$ ll
total 2572744
-rwxrwxr-x 1 oracle oinstall   9748480 Dec 31 22:56 control01.ctl
-rw-r----- 1 oracle oinstall 362422272 Dec 31 22:49 example01.dbf
-rw-r----- 1 oracle oinstall  52429312 Dec 31 22:51 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Dec 31 22:56 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Dec 31 22:49 redo03.log
-rw-r----- 1 oracle oinstall 608182272 Dec 31 22:55 sysaux01.dbf
-rw-r----- 1 oracle oinstall 775954432 Dec 31 22:55 system01.dbf
-rw-r----- 1 oracle oinstall  20979712 Dec 31 22:05 temp01.dbf
-rw-r----- 1 oracle oinstall 178266112 Dec 31 22:55 undotbs01.dbf
-rw-r----- 1 oracle oinstall  13115392 Dec 31 22:49 users01.dbf
-rw-r----- 1 oracle oinstall 524296192 Dec 31 22:49 zlm01.dbf
 
--模拟在线破坏3个redo日志
[oracle@zlm2 zlm11g]$ echo > redo01.log
[oracle@zlm2 zlm11g]$ echo > redo02.log
[oracle@zlm2 zlm11g]$ echo > redo03.log
[oracle@zlm2 zlm11g]$ exit
exit
 
--关闭数据库
SQL> shutdown immediate
ORA-01031: insufficient privileges
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
ORA-03113: end-of-file on communication channel
Process ID: 4667
Session ID: 40 Serial number: 105
 
由于redo文件已经被破坏,一致性关闭数据库报错,报ora-03113错误
 
--重启数据库
SQL> startup
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@zlm2 backup]$ sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 31 22:57:34 2014
 
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 
Connected to an idle instance.
 
SQL> startup
ORACLE instance started.
 
Total System Global Area  835104768 bytes
Fixed Size                  2232960 bytes
Variable Size             494931328 bytes
Database Buffers          335544320 bytes
Redo Buffers                2396160 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/zlm11g/redo02.log'
ORA-27048: skgfifi: file header information is invalid
Additional information: 14
 
发现已无法自动启动到open状态,提示非法文件头信息,报ora-27048错误,以及ora-00313、ora-00312
由于启动数据库的时候会做一致性检查,redo的损坏使一致性检查无法通过,可以通过设置隐含参数_allow_resetlogs_corruption解决
 
--设置隐含参数
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
 
System altered.
 
--关闭并重启数据库
SQL> shutdown immediate
ORA-01109: database not open
 
 
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
 
Total System Global Area  835104768 bytes
Fixed Size                  2232960 bytes
Variable Size             494931328 bytes
Database Buffers          335544320 bytes
Redo Buffers                2396160 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/zlm11g/redo02.log'
ORA-27048: skgfifi: file header information is invalid
Additional information: 14
 
 
SQL> show parameter _allow_resetlogs_corruption
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_allow_resetlogs_corruption          boolean     TRUE
 
可以看到,刚才设置的隐含参数已经生效
 
--做一次基于cancel的不完全恢复
SQL> recover database until cancel;
ORA-00279: change 1723866 generated at 12/31/2014 22:51:41 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ZLM11G/archivelog/2014_12_31/o1_mf_1_56_%u_.a
rc
ORA-00280: change 1723866 for thread 1 is in sequence #56
 
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/u01/app/oracle/fast_recovery_area/ZLM11G/archivelog/2014_12_31/o1_mf_1_56_%u_.
arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
 
 
ORA-00308: cannot open archived log
'/u01/app/oracle/fast_recovery_area/ZLM11G/archivelog/2014_12_31/o1_mf_1_56_%u_.
arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
 
 
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/zlm11g/system01.dbf'
 
 
SQL> !
[oracle@zlm2 backup]$ cd /u01/app/oracle/fast_recovery_area/ZLM11G/archivelog/2014_12_31/
[oracle@zlm2 2014_12_31]$ ll
total 56076
-rw-r----- 1 oracle oinstall 33343488 Dec 31 20:59 o1_mf_1_50_bb7wtcln_.arc
-rw-r----- 1 oracle oinstall    30720 Dec 31 21:02 o1_mf_1_51_bb7wzzlk_.arc
-rw-r----- 1 oracle oinstall 23774208 Dec 31 22:38 o1_mf_1_52_bb82m1dp_.arc
-rw-r----- 1 oracle oinstall   172032 Dec 31 22:41 o1_mf_1_53_bb82rsqv_.arc
-rw-r----- 1 oracle oinstall     6656 Dec 31 22:49 o1_mf_1_54_bb837jy3_.arc
-rw-r----- 1 oracle oinstall    14848 Dec 31 22:51 o1_mf_1_55_bb83cxqy_.arc
 
最新的归档只有到55,第56号归档还没有生成,用auto最多只能恢复到55号归档
 
--再次执行recover database until cancel,这次输入cancel
SQL> recover database until cancel;
ORA-00279: change 1723866 generated at 12/31/2014 22:51:41 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ZLM11G/archivelog/2014_12_31/o1_mf_1_56_%u_.a
rc
ORA-00280: change 1723866 for thread 1 is in sequence #56
 
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/zlm11g/system01.dbf'
 
 
ORA-01112: media recovery not started
 
 
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
 
 
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [1723874], [0],
[1724203], [4194432], [], [], [], [], [], []
Process ID: 5139
Session ID: 1 Serial number: 5
 
用resetlogs打开数据库,报了一个600错误,先不用理会
 
SQL> select open_mode from v$database;
ERROR:
ORA-03114: not connected to ORACLE
 
此时提示未连接上数据库
 
SQL> conn / as sysdba
Connected to an idle instance.
SQL> select open_mode from v$database;
select open_mode from v$database
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
 
提示数据库实例未启动
 
SQL> startup mount
ORACLE instance started.
 
Total System Global Area  835104768 bytes
Fixed Size                  2232960 bytes
Variable Size             494931328 bytes
Database Buffers          335544320 bytes
Redo Buffers                2396160 bytes
Database mounted.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
 
 
SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.
 
 
SQL> alter database open;
 
Database altered.
 
 
SQL> select count(*) from zlm.test;
 
  COUNT(*)
----------
         1
 
SQL> select * from zlm.test;
 
      TBID
----------
         1
 
其实在设置了隐含参数后,用resetlogs打开数据库后,关闭再重启数据库就已经可以正常open数据库了,很幸运地,之前并未提交的数据也都没有丢失,最后不要忘记把隐含参数设置回来,并且做一次全备
 
SQL> alter system set "_allow_resetlogs_corruption"=false scope=spfile;
 
System altered.
 
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
 
Total System Global Area  835104768 bytes
Fixed Size                  2232960 bytes
Variable Size             494931328 bytes
Database Buffers          335544320 bytes
Redo Buffers                2396160 bytes
Database mounted.
Database opened.
SQL> show parameter _allow_resetlogs_corruption
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_allow_resetlogs_corruption          boolean     FALSE
SQL> 

 

CentOS 5.5 i386 上安装 Oracle 10G XE  http://www.linuxidc.com/Linux/2011-02/31928p2.htm

Linux下Oracle 11G XE 安装笔记  http://www.linuxidc.com/Linux/2012-08/66974.htm

在CentOS 6.4下安装Oracle 11gR2(x64) http://www.linuxidc.com/Linux/2014-02/97374.htm

Oracle 11gR2 在VMWare虚拟机中安装步骤 http://www.linuxidc.com/Linux/2013-09/89579p2.htm

Debian 下 安装 Oracle 11g XE R2 http://www.linuxidc.com/Linux/2014-03/98881.htm

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

本文永久更新链接地址http://www.linuxidc.com/Linux/2015-01/111583.htm

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

       

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