online redo日志文件损坏恢复
online redo日志文件损坏恢复
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);
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
Linux下Oracle 11G XE 安装笔记
在CentOS 6.4下安装Oracle 11gR2(x64)
Oracle 11gR2 在VMWare虚拟机中安装步骤
Debian 下 安装 Oracle 11g XE R2
评论暂时关闭