溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點(diǎn)擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》

怎么解決Oracle報(bào)錯(cuò)ORA-01194、ORA-01110問題

發(fā)布時(shí)間：2021-11-08 17:07:50 來源：億速云 閱讀：3800 作者：iii 欄目：關(guān)系型數(shù)據(jù)庫

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

使用_allow_resetlogs_corruption打開無歸檔日志rman備份庫，運(yùn)維DBA反映服務(wù)器宕機(jī)后，開啟數(shù)據(jù)庫報(bào)錯(cuò)ORA-01194 ORA-01110，分析原因?yàn)镺racle SCN不一致導(dǎo)致數(shù)據(jù)庫無法啟動(dòng)，使用_allow_resetlogs_corruption打開數(shù)據(jù)庫

1.rman還原恢復(fù)操作

--還原數(shù)據(jù)庫
RMAN> restore database;
--恢復(fù)數(shù)據(jù)庫
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
這里報(bào)日志缺少，實(shí)際上是備份的數(shù)據(jù)庫文件后，沒有備份歸檔日志，歸檔日志全部丟失

進(jìn)行不完全恢復(fù)

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.查看相關(guān)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.--發(fā)現(xiàn)數(shù)據(jù)文件scn和控制文件不一致，重建控制文件，然后查詢相關(guān)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.
--此時(shí)所有scn均一致

3.嘗試打開數(shù)據(jù)庫

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.使用隱含參數(shù)打開數(shù)據(jù)庫

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

到此，關(guān)于“怎么解決Oracle報(bào)錯(cuò)ORA-01194、ORA-01110問題”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請繼續(xù)關(guān)注億速云網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)砀鄬?shí)用的文章！

向AI問一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手