掉電引起的ORA-1172錯誤的解決方法

這期內容當中小編將會給大家帶來有關掉電引起的ORA-1172錯誤的解決方法，文章內容豐富且以專業的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

由于UPS故障，導致機房連續多次掉電，問題解決后，發現一臺本地測試數據庫打開時報錯，ORA-1172、ORA-1151錯誤。

嘗試打開數據庫。

下面嘗試用EVENTS方式打開數據庫，不過由于出現ORA-600 2662錯誤的機制與上面一篇文章不同，因此這里不需要設置隱含參數_allow_resetlogs_corruption。不過由于當前的版本是10g，因此需要設置隱含參數_allow_error_simulation為true，這時使用EVENTS調整SCN的前提。

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

File created.

編輯這個初始化參數文件，添加_ALLOW_ERROR_SIMULATION=true到這個文件中，并將數據庫啟動到mount狀態：

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調整SCN：

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

Session altered.

下面嘗試打開數據庫：

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

嘗試打開仍然報錯，再次檢查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錯誤外，又新增了600的2256錯誤。根據METALINK文檔Doc ID:  Note:30681.1的描述，這時需要對ADJUST_SCN的LEVEL進行調整：

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

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

錯誤信息這次發生了變化，檢查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

這次又出現了ORA-600的4194和4193錯誤，根據錯誤信息的看來是Oracle進行恢復的過程中出現了問題。查詢METALINK，發現是REDO中的回滾記錄和UNDO中的不一致造成的。嘗試使用隱含參數_CORRUPTED_ROLLBACK_SEGMENTS來打開數據庫。在剛才的建立的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&)

嘗試啟動數據庫：

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.

重要數據庫成功的打開，由于使用了初始化參數_CORRUPTED_ROLLBACK_SEGMENTS，可能導致數據庫出現不一致的狀態，因此很可能已經造成了數據的丟失，不過好在是測試數據庫。下面只需要將非系統的SCHEMA導出，建立一個干凈的數據庫，然后導入既可。

此時其實并沒有解決掉REDO和UNDO中記錄沖突的問題，如果關閉數據庫，嘗試不適應隱含參數打開，會碰到下面的錯誤：

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錯誤的解決方法了，如果剛好有類似的疑惑，不妨參照上述分析進行理解。如果想知道更多相關知識，歡迎關注億速云行業資訊頻道。