掉電引起的ORA-1172錯誤解決過程（二）

由於UPS故障，導致機房連續多次掉電，問題解決後，發現一臺本地測試資料庫開啟時報錯，ORA-1172、ORA-1151錯誤。

掉電引起的ORA-1172錯誤解決過程（一）：http://yangtingkun.itpub.net/post/468/465223

嘗試開啟資料庫。

 

 

上一篇介紹了問題的產生和現象，下面嘗試用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.