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

查看redolog的状态

$ sqlplus / as sysdba
SQL> 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 1
ORA-00289: suggestion :
/u01/oracle/fast_recovery_area/HLS/archivelog/2015_01_07/o1_mf_1_248_%u_.arc
ORA-00280: change 70615250 for thread 1 is in sequence #248


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
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/oracle/oradata/HLS/system01.dbf'


ORA-01112: media recovery not started


SQL> 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 error
ORA-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 forced
ORA-00600: internal error code, arguments: [2662], [0], [70615257], [0],
[70887210], [12583040], [], [], [], [], [], []
Process ID: 21448
Session 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 sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jan 7 14:21:39 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size          2260088 bytes
Variable Size         436208520 bytes
Database Buffers      624951296 bytes
Redo Buffers            5517312 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [70675277], [0],
[70887210], [12583040], [], [], [], [], [], []
Process ID: 21704
Session ID: 1 Serial number: 5


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@hls dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jan 7 14:22:23 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size          2260088 bytes
Variable Size         436208520 bytes
Database Buffers      624951296 bytes
Redo Buffers            5517312 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [70695282], [0],
[70887210], [12583040], [], [], [], [], [], []
Process ID: 21755
Session ID: 1 Serial number: 5


SQL> exit

将数据导出

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

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

USERNAME
------------------------------
HLS_TEXT
HLS_0628
HLS_0805
CF_TEST
HLS_DEMO
HLS_0729
HLS_TEST
HLS_DEV

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

$ sqlplus / as sysdba
SQL> 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 sysdba
SQL> 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

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

需要注意的事项

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

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