记一次oracle数据库redolog全部丢失的恢复

查看redolog的状态

$ sqlplus / as sysdbaSQL> select GROUP#, STATUS from v$log;    GROUP# STATUS---------- ----------------     1 CLEARING     3 INACTIVE     2 CURRENT

在redolog都丢失的情况下，使用隐藏参数，尝试以open resetlogs的方式打开数据库。

SQL> alter system set "_allow_resetlogs_corruption"=TRUE scope=spfile;SQL> shutdown immediate;SQL> startup mount;SQL> recover database using backup controlfile until cancel;

会出现下面的提示：

ORA-00279: change 70615250 generated at 01/04/2015 08:13:02 needed for thread 1ORA-00289: suggestion :/u01/oracle/fast_recovery_area/HLS/archivelog/2015_01_07/o1_mf_1_248_%u_.arcORA-00280: change 70615250 for thread 1 is in sequence #248Specify log: {<RET>=suggested | filename | AUTO | CANCEL}cancel <-- 此处输入CANCELORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error belowORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: '/u01/oracle/oradata/HLS/system01.dbf'ORA-01112: media recovery not startedSQL> alter database open resetlogs; <-- recover执行完成之后，尝试以resetlogs模式打开数据库。

碰到ORA-600 2662错误

执行完毕上面的alter database open resetlogs之后。出现下面的错误：

alter database open resetlogs*ERROR at line 1:ORA-00603: ORACLE server session terminated by fatal errorORA-00600: internal error code, arguments: [2662], [0], [70615260], [0],[70887210], [12583040], [], [], [], [], [], []ORA-00600: internal error code, arguments: [2662], [0], [70615259], [0],[70887210], [12583040], [], [], [], [], [], []ORA-01092: ORACLE instance terminated. Disconnection forcedORA-00600: internal error code, arguments: [2662], [0], [70615257], [0],[70887210], [12583040], [], [], [], [], [], []Process ID: 21448Session ID: 1 Serial number: 5

查询metalink，ORA-600 [2662]的含义为"Block SCN is ahead of Current SCN" 。
此处BLOCK SCN为70887210, CURRENT SCN为70615257，可以看到，BLOCK SCN比CURRENT SCN大了一些。
根据metalink文章ORA-600 [2662] "Block SCN is ahead of Current SCN" (文档 ID 28929.1)的说法，在BLOCK SCN和CURRENT SCN差异不大的情况下，可以使用反复起停数据库的方式将数据库的CURRENT SCN向前推荐，试CURRENT SCN大于BLOCK SCN，然后就可以符合打开数据库的条件。

在反复几次执行下面的过程之后，数据库就可以正常打开了。

[oracle@hls dbs]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Wed Jan 7 14:21:39 2015Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to an idle instance.SQL> startupORACLE instance started.Total System Global Area 1068937216 bytesFixed Size          2260088 bytesVariable Size         436208520 bytesDatabase Buffers      624951296 bytesRedo Buffers            5517312 bytesDatabase mounted.ORA-01092: ORACLE instance terminated. Disconnection forcedORA-00600: internal error code, arguments: [2662], [0], [70675277], [0],[70887210], [12583040], [], [], [], [], [], []Process ID: 21704Session ID: 1 Serial number: 5SQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options[oracle@hls dbs]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Wed Jan 7 14:22:23 2015Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to an idle instance.SQL> startupORACLE instance started.Total System Global Area 1068937216 bytesFixed Size          2260088 bytesVariable Size         436208520 bytesDatabase Buffers      624951296 bytesRedo Buffers            5517312 bytesDatabase mounted.ORA-01092: ORACLE instance terminated. Disconnection forcedORA-00600: internal error code, arguments: [2662], [0], [70695282], [0],[70887210], [12583040], [], [], [], [], [], []Process ID: 21755Session ID: 1 Serial number: 5SQL> exit

将数据导出

上面是使用oracle的隐藏参数，忽略数据库很多不一致条件下将数据库打开的。数据库中可能还有很多不一致的地方，为避免数据库使用过程中出现问题，数据库打开之后需要将数据导出，然后重新导入到一个重新创建的数据库中。

数据库中，应用使用的SCHEMA为：

USERNAME------------------------------HLS_TEXTHLS_0628HLS_0805CF_TESTHLS_DEMOHLS_0729HLS_TESTHLS_DEV

执行下面操作将上面SCHEMA的数据导出：

$ sqlplus / as sysdbaSQL> create directory dmpdir as '/home/oracle/dmp';$ expdp \'/ as sysdba\' \schemas=HLS_TEXT,HLS_0628,HLS_0805,CF_TEST,HLS_DEMO,HLS_0729,HLS_TEST,HLS_DEV \directory=dmpdir \dumpfile=hls_20150107.dmp \logfile=hls_exp_20150107.log

导出执行完成之后，会生成hls_20150107.dmp文件

将导出的dmp文件导入到新创建的数据库中。

执行导入之前，需要先使用dbca工具重新创建一个名为HLS的数据库。

执行下面操作进行数据导入：

$ sqlplus / as sysdbaSQL> create tablespace HLSDEV datafile '/u01/oracle/oradata/HLS/hlsdev.dbf' size 1G autoextend on next 100m maxsize unlimited;SQL>create directory dmpdir as '/home/oracle/dmp';SQL> exit;$ impdp \'/ as sysdba\' \directory=dmpdir \dumpfile=hls_20150107.dmp \logfile=hls_imp_20150107.log

执行完上面操作之后，数据库就恢复完了。

需要注意的事项

使用隐藏参数强制打开数据库的方式，是在数据库出现异常损坏，且没有备份情况下的一种非常规恢复手段，只能作为一种尝试的手段，并不能保证每次最终都可以正常将数据库打开。

在数据库日常使用过程中，做好备份和谨慎操作是保护系统安全最有效的手段。