【案例】Oracle報錯ORA-01194 ORA-01110 由於資料庫SCN不一致導致無法啟動

使用_allow_resetlogs_corruption開啟無歸檔日誌rman備份庫， 運維DBA反映伺服器當機後，開啟資料庫報錯ORA-01194 ORA-01110，分析原因為Oracle SCN不一致導致資料庫無法啟動，使用_allow_resetlogs_corruption開啟資料庫

1.rman還原恢復操作

--還原資料庫
RMAN> restore database;
--恢復資料庫
RMAN> recover database;
Starting recover at 2012-03-08 21:20:45
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=65 device type=DISK
starting media recovery
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/08/2012 21:20:47
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 2936 and starting SCN of 25991695 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 2935 and starting SCN of 25991652 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 2934 and starting SCN of 25991649 found to restore
……………………
RMAN-06025: no backup of archived log for thread 1 with sequence 2902 and starting SCN of 25991156 found to restore
這裡報日誌缺少，實際上是備份的資料庫檔案後，沒有備份歸檔日誌，歸檔日誌全部丟失

進行不完全恢復

SQL> recover database until cancel; ORA-00279: change 25991194 generated at 03/08/2012 20:33:58 needed for thread 1 ORA-00289: suggestion : /opt/oracle/oradata/archivelog/chf/1_2902_752334071.dbf ORA-00280: change 25991194 for thread 1 is in sequence #2902 Specify log: {

=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: '/opt/oracle/oradata/chf/system01.dbf' ORA-01112: media recovery not started SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1:  ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/opt/oracle/oradata/chf/system01.dbf'

2.檢視相關SCN

SQL> select file#,to_char(checkpoint_change#,'999999999999') from v$datafile;
     FILE# TO_CHAR(CHECK
---------- -------------
         1      25992214
         2      25992214
         3      25992214
         4      25992214
         5      25992214
         6      25992214
         7      25992214
         8      25992214
         9      25992214
        10      25992214
        11      25992214
     FILE# TO_CHAR(CHECK
---------- -------------
        13      25992214
        14      25992214
13 rows selected.
SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file;
     FILE# ONLINE_ TO_CHAR(CHANG
---------- ------- -------------
         1 ONLINE       25991194
         2 ONLINE       25991194
         3 ONLINE       25991194
         4 ONLINE       25991194
         5 ONLINE       25991194
         6 ONLINE       25991194
         7 ONLINE       25991194
         8 ONLINE       25991194
         9 ONLINE       25991194
        10 ONLINE       25991194
        11 ONLINE       25991194
     FILE# ONLINE_ TO_CHAR(CHANG
---------- ------- -------------
        13 ONLINE       25991194
        14 ONLINE       25991194
13 rows selected.
SQL> select file#,to_char(checkpoint_change#,'999999999999') from v$datafile_header;
     FILE# TO_CHAR(CHECK
---------- -------------
         1      25991194
         2      25991194
         3      25991194
         4      25991194
         5      25991194
         6      25991194
         7      25991194
         8      25991194
         9      25991194
        10      25991194
        11      25991194
     FILE# TO_CHAR(CHECK
---------- -------------
        13      25991194
        14      25991194
13 rows selected.--發現資料檔案scn和控制檔案不一致，重建控制檔案，然後查詢相關scnSQL> select file#,to_char(checkpoint_change#,'999999999999') from v$datafile;
     FILE# TO_CHAR(CHECK
---------- -------------
         1      25991194
         2      25991194
         3      25991194
         4      25991194
         5      25991194
         6      25991194
         7      25991194
         8      25991194
         9      25991194
        10      25991194
        11      25991194
     FILE# TO_CHAR(CHECK
---------- -------------
        13      25991194
        14      25991194
13 rows selected.
SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file;
     FILE# ONLINE_ TO_CHAR(CHANG
---------- ------- -------------
         1 ONLINE       25991194
         2 ONLINE       25991194
         3 ONLINE       25991194
         4 ONLINE       25991194
         5 ONLINE       25991194
         6 ONLINE       25991194
         7 ONLINE       25991194
         8 ONLINE       25991194
         9 ONLINE       25991194
        10 ONLINE       25991194
        11 ONLINE       25991194
     FILE# ONLINE_ TO_CHAR(CHANG
---------- ------- -------------
        13 ONLINE       25991194
        14 ONLINE       25991194
13 rows selected.
SQL> select file#,to_char(checkpoint_change#,'999999999999') from v$datafile_header;
     FILE# TO_CHAR(CHECK
---------- -------------
         1      25991194
         2      25991194
         3      25991194
         4      25991194
         5      25991194
         6      25991194
         7      25991194
         8      25991194
         9      25991194
        10      25991194
        11      25991194
     FILE# TO_CHAR(CHECK
---------- -------------
        13      25991194
        14      25991194
13 rows selected.
--此時所有scn均一致

3.嘗試開啟資料庫

SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/opt/oracle/oradata/chf/system01.dbf' SQL> recover database using backup controlfile until cancel; ORA-00279: change 25991194 generated at 03/08/2012 20:33:58 needed for thread 1 ORA-00289: suggestion : /opt/oracle/oradata/archivelog/chf/1_2902_752334071.dbf ORA-00280: change 25991194 for thread 1 is in sequence #2902 Specify log: {

=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: '/opt/oracle/oradata/chf/system01.dbf' ORA-01112: media recovery not started SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1:  ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/opt/oracle/oradata/chf/system01.dbf'

5.使用隱含引數開啟資料庫

SQL> create pfile='/tmp/pfile' from spfile;
File created.
-------/tmp/pfile中加上----------
_allow_resetlogs_corruption= TRUE
---------------------------------
SQL> startup mount pfile='/tmp/pfile' force
ORACLE instance started.
Total System Global Area  622149632 bytes
Fixed Size                  2230912 bytes
Variable Size             419431808 bytes
Database Buffers          192937984 bytes
Redo Buffers                7548928 bytes
Database mounted.
SQL> alter database open resetlogs;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE

總結
這次的試驗沒有多少實際意義，但是可以說明幾個問題：
1.所有的資料檔案的scn都一致，甚至和控制檔案的也一致，資料庫不一定可以open成功
(懷疑是資料檔案中的scn大於data header scn)
2.對於這樣的問題，如果使用bbed修改所有資料檔案header的scn不知道是否可以解決
3.如果rman只備份了資料檔案而沒有任何一個歸檔日誌，資料庫透過隱含引數還是可以open，搶救資料

--------------------------------------ORACLE-DBA----------------------------------------

最權威、專業的Oracle案例資源彙總之【案例】Oracle報錯ORA-01194 ORA-01110 由於資料庫SCN不一致導致無法啟動

本文由大師惜分飛原創分享，網址：