記一次oracle資料庫redolog全部丟失的恢復

花菜土豆粉發表於2015-01-07
Oracle資料庫

檢視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

執行完上面操作之後，資料庫就恢復完了。

需要注意的事項

使用隱藏引數強制開啟資料庫的方式，是在資料庫出現異常損壞，且沒有備份情況下的一種非常規恢復手段，只能作為一種嘗試的手段，並不能保證每次最終都可以正常將資料庫開啟。

在資料庫日常使用過程中，做好備份和謹慎操作是保護系統安全最有效的手段。

相關文章