online redo日志文件损坏恢复
- WBOYOriginal
- 2016-06-07 16:40:481079browse
online redo日志文件对数据库是非常重要的，当current日志文件损坏，通常就意味着要丢失数据，但是也不是绝对的，可以通过一定的
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解决
--设置隐含参数
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: {
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: {
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