掉電引起的ORA-1172錯(cuò)誤的解決方法

這期內(nèi)容當(dāng)中小編將會給大家?guī)碛嘘P(guān)掉電引起的ORA-1172錯(cuò)誤的解決方法，文章內(nèi)容豐富且以專業(yè)的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

由于UPS故障，導(dǎo)致機(jī)房連續(xù)多次掉電，問題解決后，發(fā)現(xiàn)一臺本地測試數(shù)據(jù)庫打開時(shí)報(bào)錯(cuò)，ORA-1172、ORA-1151錯(cuò)誤。

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

下面嘗試用EVENTS方式打開數(shù)據(jù)庫，不過由于出現(xiàn)ORA-600 2662錯(cuò)誤的機(jī)制與上面一篇文章不同，因此這里不需要設(shè)置隱含參數(shù)_allow_resetlogs_corruption。不過由于當(dāng)前的版本是10g，因此需要設(shè)置隱含參數(shù)_allow_error_simulation為true，這時(shí)使用EVENTS調(diào)整SCN的前提。

SQL> conn / as sysdba
Connected to an idle instance.
SQL> create pfile='/home/oracle/inittest08.ora' from spfile;

File created.

編輯這個(gè)初始化參數(shù)文件，添加_ALLOW_ERROR_SIMULATION=true到這個(gè)文件中，并將數(shù)據(jù)庫啟動到mount狀態(tài)：

SQL> startup pfile=/home/oracle/inittest08.ora mount
ORACLE instance started.

Total System Global Area 2483027968 bytes
Fixed Size                  2074760 bytes
Variable Size            1090520952 bytes
Database Buffers         1375731712 bytes
Redo Buffers               14700544 bytes
Database mounted.

通過EVENTS調(diào)整SCN：

SQL> alter session set events '10015 trace name adjust_scn level 1';   

Session altered.

下面嘗試打開數(shù)據(jù)庫：

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

嘗試打開仍然報(bào)錯(cuò)，再次檢查alert文件：

Errors in file /opt/ora10g/admin/test08/udump/test08_ora_6525.trc:
ORA-00600: internal error code, arguments: [2256], [0], [1073741824], [1], [2238656971], [], [], []
Thu Jun  5 16:32:01 2008
Errors in file /opt/ora10g/admin/test08/udump/test08_ora_6525.trc:
ORA-00600: internal error code, arguments: [2662], [1], [2238656973], [1], [2238756337], [8388637], [], []
Thu Jun  5 16:32:04 2008
Errors in file /opt/ora10g/admin/test08/udump/test08_ora_6525.trc:
ORA-00600: internal error code, arguments: [2662], [1], [2238656973], [1], [2238756337], [8388637], [], []
Thu Jun  5 16:32:04 2008
Error 600 happened during db open, shutting down database
USER: terminating instance due to error 600
Instance terminated by USER, pid = 6525
ORA-1092 signalled during: alter database open...

除了剛才的ORA-600 2662錯(cuò)誤外，又新增了600的2256錯(cuò)誤。根據(jù)METALINK文檔Doc ID:  Note:30681.1的描述，這時(shí)需要對ADJUST_SCN的LEVEL進(jìn)行調(diào)整：

將參數(shù)c的值乘以4再根據(jù)SCN的范圍確定LEVEL的值，根據(jù)需要將LEVEL設(shè)置為7，然后嘗試嘗試打開數(shù)據(jù)庫：

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup pfile=/home/oracle/inittest08.ora mount
ORACLE instance started.

Total System Global Area 2483027968 bytes
Fixed Size                  2074760 bytes
Variable Size            1090520952 bytes
Database Buffers         1375731712 bytes
Redo Buffers               14700544 bytes
Database mounted.
SQL> alter session set events '10015 trace name adjust_scn level 7';

Session altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error

錯(cuò)誤信息這次發(fā)生了變化，檢查alert文件：

Errors in file /opt/ora10g/admin/test08/udump/test08_ora_6700.trc:
ORA-00600: internal error code, arguments: [4194], [30], [29], [], [], [], [], []
Thu Jun  5 16:43:30 2008
Errors in file /opt/ora10g/admin/test08/bdump/test08_smon_6686.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 32529)
ORA-01110: data file 1: '/data/oradata/test08/system01.dbf'
Thu Jun  5 16:43:31 2008
Errors in file /opt/ora10g/admin/test08/bdump/test08_smon_6686.trc:
ORA-00600: internal error code, arguments: [4194], [109], [98], [], [], [], [], []
Thu Jun  5 16:43:33 2008
Doing block recovery for file 2 block 133
Block recovery from logseq 531, block 60 to scn 7516192829
Thu Jun  5 16:43:33 2008
Recovery of Online Redo Log: Thread 1 Group 2 Seq 531 Reading mem 0
  Mem# 0: /data/oradata/test08/redo02.log
Block recovery stopped at EOT rba 531.62.16
Block recovery completed at rba 531.62.16, scn 1.3221225531
Doing block recovery for file 2 block 5
Block recovery from logseq 531, block 60 to scn 7516192826
Thu Jun  5 16:43:33 2008
Recovery of Online Redo Log: Thread 1 Group 2 Seq 531 Reading mem 0
  Mem# 0: /data/oradata/test08/redo02.log
Block recovery completed at rba 531.62.16, scn 1.3221225531
Thu Jun  5 16:43:33 2008
Errors in file /opt/ora10g/admin/test08/bdump/test08_smon_6686.trc:
ORA-01595: error freeing extent (2) of rollback segment (1))
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4194], [109], [98], [], [], [], [], []
Thu Jun  5 16:43:34 2008
Doing block recovery for file 2 block 1102
Block recovery from logseq 531, block 58 to scn 7516192831
Thu Jun  5 16:43:34 2008
Recovery of Online Redo Log: Thread 1 Group 2 Seq 531 Reading mem 0
  Mem# 0: /data/oradata/test08/redo02.log
Block recovery stopped at EOT rba 531.62.16
Block recovery completed at rba 531.62.16, scn 1.3221225531
Doing block recovery for file 2 block 61
Block recovery from logseq 531, block 58 to scn 7516192825
Thu Jun  5 16:43:34 2008
Recovery of Online Redo Log: Thread 1 Group 2 Seq 531 Reading mem 0
  Mem# 0: /data/oradata/test08/redo02.log
Block recovery completed at rba 531.60.16, scn 1.3221225530
Thu Jun  5 16:43:34 2008
Errors in file /opt/ora10g/admin/test08/udump/test08_ora_6700.trc:
ORA-00600: internal error code, arguments: [4193], [4306], [4309], [], [], [], [], []
DEBUG: Replaying xcb 0xf0eba330, pmd 0xf3d4c360 for failed op 8
Doing block recovery for file 2 block 1085
No block recovery was needed

這次又出現(xiàn)了ORA-600的4194和4193錯(cuò)誤，根據(jù)錯(cuò)誤信息的看來是Oracle進(jìn)行恢復(fù)的過程中出現(xiàn)了問題。查詢METALINK，發(fā)現(xiàn)是REDO中的回滾記錄和UNDO中的不一致造成的。嘗試使用隱含參數(shù)_CORRUPTED_ROLLBACK_SEGMENTS來打開數(shù)據(jù)庫。在剛才的建立的inittest08.ora初始化文件中添加下面的信息：

undo_management='MANUAL'
_corrupted_rollback_segments=(_SYSSMU1&,_SYSSMU2&,_SYSSMU3&,_SYSSMU4&,_SYSSMU5&,_SYSSMU6&,_SYSSMU7&,_SYSSMU8&,_SYSSMU9&,_SYSSMU10&,_SYSSMU11&,_SYSSMU12&,_SYSSMU13&,_SYSSMU14&,_SYSSMU15&,_SYSSMU16&,_SYSSMU17&,_SYSSMU18&,_SYSSMU19&,_SYSSMU20&,_SYSSMU21&,_SYSSMU22&,_SYSSMU23&,_SYSSMU24&,_SYSSMU25&,_SYSSMU26&,_SYSSMU27&,_SYSSMU28&,_SYSSMU29&,_SYSSMU30&,_SYSSMU31&,_SYSSMU32&,_SYSSMU33&,_SYSSMU34&,_SYSSMU35&,_SYSSMU36&,_SYSSMU37&,_SYSSMU38&,_SYSSMU39&,_SYSSMU40&,_SYSSMU41&)

嘗試啟動數(shù)據(jù)庫：

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup pfile=/home/oracle/inittest08.ora
ORACLE instance started.

Total System Global Area 2483027968 bytes
Fixed Size                  2074760 bytes
Variable Size            1090520952 bytes
Database Buffers         1375731712 bytes
Redo Buffers               14700544 bytes
Database mounted.
Database opened.

重要數(shù)據(jù)庫成功的打開，由于使用了初始化參數(shù)_CORRUPTED_ROLLBACK_SEGMENTS，可能導(dǎo)致數(shù)據(jù)庫出現(xiàn)不一致的狀態(tài)，因此很可能已經(jīng)造成了數(shù)據(jù)的丟失，不過好在是測試數(shù)據(jù)庫。下面只需要將非系統(tǒng)的SCHEMA導(dǎo)出，建立一個(gè)干凈的數(shù)據(jù)庫，然后導(dǎo)入既可。

此時(shí)其實(shí)并沒有解決掉REDO和UNDO中記錄沖突的問題，如果關(guān)閉數(shù)據(jù)庫，嘗試不適應(yīng)隱含參數(shù)打開，會碰到下面的錯(cuò)誤：

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2483027968 bytes
Fixed Size                  2074760 bytes
Variable Size            1090520952 bytes
Database Buffers         1375731712 bytes
Redo Buffers               14700544 bytes
Database mounted.
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4194], [30], [29], [], [], [], [],
[]

SQL> shutdown immediate
ORA-03113: end-of-file on communication channel
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup pfile=/home/oracle/inittest08.ora
ORACLE instance started.

Total System Global Area 2483027968 bytes
Fixed Size                  2074760 bytes
Variable Size            1090520952 bytes
Database Buffers         1375731712 bytes
Redo Buffers               14700544 bytes
Database mounted.
Database opened.

上述就是小編為大家分享的掉電引起的ORA-1172錯(cuò)誤的解決方法了，如果剛好有類似的疑惑，不妨參照上述分析進(jìn)行理解。如果想知道更多相關(guān)知識，歡迎關(guān)注億速云行業(yè)資訊頻道。