怎么解決Oracle報錯ORA

133次閱讀

共計 5910 個字符，預計需要花費 15 分鐘才能閱讀完成。

這篇文章主要介紹“怎么解決 Oracle 報錯 ORA-01194、ORA-01110 問題”，在日常操作中，相信很多人在怎么解決 Oracle 報錯 ORA-01194、ORA-01110 問題問題上存在疑惑，丸趣 TV 小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”怎么解決 Oracle 報錯 ORA-01194、ORA-01110 問題”的疑惑有所幫助！接下來，請跟著丸趣 TV 小編一起來學習吧！

使用_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

到此，關于“怎么解決 Oracle 報錯 ORA-01194、ORA-01110 問題”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注丸趣 TV 網站，丸趣 TV 小編會繼續努力為大家帶來更多實用的文章！

正文完