誤刪重做日誌檔案組導致啟動資料庫報錯ORA-03113

Hoegh發表於2015-07-09
資料庫
        聽不止一個人說起，學技術就要不斷地折騰、搞破壞，比如說備份恢復，你就可以嘗試刪檔案，不管是日誌檔案、臨時檔案、資料檔案還是system檔案。刪了之後，重啟資料庫肯定報錯，有的甚至當時資料庫就掛掉，這樣你就可以學著恢復，一破一立之間，很多常規的備份恢復手段也就算是領教了。我今天就嘗試著把虛擬機器上的一個重做日誌檔案組刪除。

1.環境準備

我們在Oracle11g中進行測試，資料庫處於非歸檔狀態。

點選(此處)摺疊或開啟

  1. SQL>

  3. SQL> select * from v$version;

  5.  

  7. BANNER

  9. --------------------------------------------------------------------------------

  11. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

  13. PL/SQL Release 11.2.0.3.0 - Production

  15. CORE 11.2.0.3.0 Production

  17. TNS for Linux: Version 11.2.0.3.0 - Production

  19. NLSRTL Version 11.2.0.3.0 - Production

  21.  

  23. SQL>

  25. SQL> archive log list;

  27. Database log mode No Archive Mode

  29. Automatic archival Disabled

  31. Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch

  33. Oldest online log sequence 9

  35. Current log sequence 11

  37. SQL>

2.刪除一個重做日誌檔案組，重啟資料庫報錯

首先，透過查詢v$log檢視來獲取資料庫重做日誌檔案組的狀態。

點選(此處)摺疊或開啟

  1. SQL> select GROUP#,MEMBERS,ARCHIVED,STATUS from v$log;

  3.  

  5.     GROUP# MEMBERS ARC STATUS

  7. ---------- ---------- --- ----------------

  9.          1 1 NO INACTIVE

  11.          2 1 NO CURRENT

  13.          3 1 NO INACTIVE

  15.  

  17. SQL>
然後，透過ls命令檢視資料檔案，刪除第一個重做日誌檔案組（該檔案組只有一個日誌成員）。

點選(此處)摺疊或開啟

  1. [oracle@ hoegh HOEGH]$ ls

  3. control01.ctl redo01.log sysaux01.dbf undotbs01.dbf

  5. control02.ctl redo02.log system01.dbf users01.dbf

  7. example01.dbf redo03.log temp01.dbf

  9. [oracle@hoegh HOEGH]$

  11. [oracle@hoegh HOEGH]$

  13. [oracle@hoegh HOEGH]$ rm redo01.log

  15. [oracle@hoegh HOEGH]$ ls

  17. control01.ctl control02.ctl example01.dbf redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
重啟資料庫，資料庫報錯。

點選(此處)摺疊或開啟

  1. SQL>

  3. SQL> shu immediate

  5. Database closed.

  7. Database dismounted.

  9. ORACLE instance shut down.

  11. SQL> startup

  13. ORACLE instance started.

  15.  

  17. Total System Global Area 941600768 bytes

  19. Fixed Size 1348860 bytes

  21. Variable Size 515902212 bytes

  23. Database Buffers 419430400 bytes

  25. Redo Buffers 4919296 bytes

  27. Database mounted.

  29. ORA-03113: end-of-file on communication channel

  31. Process ID: 5196

  33. Session ID: 125 Serial number: 5

  35.  

  37.  

  39. SQL>

  41. SQL> select status from v$instance;

  43. ERROR:

  45. ORA-03114: not connected to ORACLE

  47.  

  49.  

  51. SQL>


3.檢視報警日誌檔案，定位問題

ORA-03113報錯是一個非常經典的報錯，報錯原因多種多樣，從報錯資訊中並看不出是什麼原因導致的報錯，我們可以到報警日誌檔案中檢視有價值的線索。

點選(此處)摺疊或開啟

  1. [oracle@enmoedu1 trace]$ tail -40 alert_HOEGH.log

  3. Wed Jul 08 21:59:30 2015

  5. MMON started with pid=15, OS id=5443

  7. Wed Jul 08 21:59:30 2015

  9. MMNL started with pid=16, OS id=5445

  11. starting up 1 dispatcher(s) for network address \'(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))\'...

  13. starting up 1 shared server(s) ...

  15. ORACLE_BASE from environment = /u01/app/oracle

  17. Wed Jul 08 21:59:39 2015

  19. alter database mount

  21. Wed Jul 08 21:59:43 2015

  23. Successful mount of redo thread 1, with mount id 2105928075

  25. Database mounted in Exclusive Mode

  27. Lost write protection disabled

  29. Completed: alter database mount

  31. Wed Jul 08 22:11:45 2015

  33. Time drift detected. Please check VKTM trace file for more details.

  35. Wed Jul 08 22:11:59 2015

  37. alter database open

  39. Wed Jul 08 22:11:59 2015

  41. Errors in file /u01/app/oracle/diag/rdbms/HOEGH/HOEGH/trace/HOEGH_lgwr_5435.trc:

  43. ORA-00313: open failed for members of log group 1 of thread 1

  45. ORA-00312: online log 1 thread 1: \'/u01/app/oracle/oradata/HOEGH/redo01.log\'

  47. ORA-27037: unable to obtain file status

  49. Linux Error: 2: No such file or directory

  51. Additional information: 3

  53. Errors in file /u01/app/oracle/diag/rdbms/HOEGH/HOEGH/trace/HOEGH_lgwr_5435.trc:

  55. ORA-00313: open failed for members of log group 1 of thread 1

  57. ORA-00312: online log 1 thread 1: \'/u01/app/oracle/oradata/HOEGH/redo01.log\'

  59. ORA-27037: unable to obtain file status

  61. Linux Error: 2: No such file or directory

  63. Additional information: 3

  65. Errors in file /u01/app/oracle/diag/rdbms/HOEGH/HOEGH/trace/HOEGH_ora_5451.trc:

  67. ORA-00313: open failed for members of log group 1 of thread

  69. ORA-00312: online log 1 thread 1: \'/u01/app/oracle/oradata/HOEGH/redo01.log\'

  71. USER (ospid: 5451): terminating the instance due to error 313

  73. Wed Jul 08 22:12:00 2015

  75. System state dump requested by (instance=1, osid=5451), summary=[abnormal instance termination].

  77. System State dumped to trace file /u01/app/oracle/diag/rdbms/HOEGH/HOEGH/trace/HOEGH_diag_5425.trc

  79. Dumping diagnostic data in directory=[cdmp_20150708221200], requested by (instance=1, osid=5451), summary=[abnormal instance termination].

  81. Instance terminated by USER, pid = 5451
其中，黃色標註部分為關鍵資訊，我們知道“/u01/app/oracle/oradata/HOEGH/redo01.log”這個檔案找不到了。

4.啟動資料庫到mount狀態，重建重做日誌檔案組

從報警日誌可以看出，第一組重做日誌檔案組丟了，我們可以透過sql語句“alter database  clear logfile group 1;”重建日誌檔案組；確認日誌檔案建立成功後，將資料庫切換到open狀態。

點選(此處)摺疊或開啟

  1. SQL> startup nomount

  3. ORACLE instance started.

  5.  

  7. Total System Global Area 941600768 bytes

  9. Fixed Size 1348860 bytes

  11. Variable Size 515902212 bytes

  13. Database Buffers 419430400 bytes

  15. Redo Buffers 4919296 bytes

  17. SQL> alter database mount;

  19.  

  21. Database altered.

  23.  

  25. SQL> select group#,sequence#,archived,status from v$log;

  27.  

  29.     GROUP# SEQUENCE# ARC STATUS

  31. ---------- ---------- --- ----------------

  33.          1 7 NO INACTIVE

  35.          3 6 NO INACTIVE

  37.          2 8 NO CURRENT

  39.  

  41. SQL>

  43. SQL>

  45. SQL>

  47. SQL>

  49. SQL> alter database clear logfile group 1;

  51.  

  53. Database altered.

  55.  

  57. SQL> select group#,sequence#,archived,status from v$log;

  59.  

  61.     GROUP# SEQUENCE# ARC STATUS

  63. ---------- ---------- --- ----------------

  65.          1 0 NO UNUSED

  67.          3 6 NO INACTIVE

  69.          2 8 NO CURRENT

  71.  

  73.  

  75. 啟動資料庫到open狀態

  77. SQL>

  79. SQL> alter database open;

  81.  

  83. Database altered.

  85.  

  87. SQL>

  89. SQL> select group#,sequence#,archived,status from v$log;

  91.  

  93.     GROUP# SEQUENCE# ARC STATUS

  95. ---------- ---------- --- ----------------

  97.          1 0 NO UNUSED

  99.          2 8 NO CURRENT

  101.          3 6 NO INACTIVE
此時我們再次檢視檔案列表，結果如下。

點選(此處)摺疊或開啟

  1. [oracle@hoegh HOEGH]$ ls

  3. control01.ctl control02.ctl example01.dbf redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf

  5. [oracle@hoegh HOEGH]$

5.手動切換重做日誌檔案組

為了確保新建的日誌檔案組可用，我們可以手動切換日誌檔案組，改變新建日誌檔案組的狀態（由UNUSED改為其他）。

點選(此處)摺疊或開啟

  1. SQL>

  3. SQL> alter system switch logfile;

  5.  

  7. System altered.

  9.  

  11. SQL> select group#,sequence#,archived,status from v$log;

  13.  

  15.     GROUP# SEQUENCE# ARC STATUS

  17. ---------- ---------- --- ----------------

  19.          1 9 NO CURRENT

  21.          2 8 NO ACTIVE

  23.          3 6 NO INACTIVE

  25.  

  27. SQL> alter system switch logfile;

  29.  

  31. System altered.

  33.  

  35. SQL> select group#,sequence#,archived,status from v$log;

  37.  

  39.     GROUP# SEQUENCE# ARC STATUS

  41. ---------- ---------- --- ----------------

  43.          1 9 NO ACTIVE

  45.          2 8 NO ACTIVE

  47.          3 10 NO CURRENT

  49.  

  51. SQL> alter system switch logfile;

  53.  

  55. System altered.

  57.  

  59. SQL> select group#,sequence#,archived,status from v$log;

  61.  

  63.     GROUP# SEQUENCE# ARC STATUS

  65. ---------- ---------- --- ----------------

  67.          1 9 NO INACTIVE

  69.          2 11 NO CURRENT

  71.          3 10 NO INACTIVE

  73.  

  75. SQL>
其中，
current:表示該日誌組為當前日誌組,oracle正在使用該日誌組;
active:當current redo組發生日誌切換時,狀態會改變為active,在這個狀態下,如果為歸檔模式,archive程式會歸檔active日誌組;如果發生資料庫crash,該日誌組也是例項恢復必需的日誌組;
inactive:當active日誌組歸檔完畢並且oracle判斷不需要進行例項恢復時,會將其狀態修改為inactive,等待下一輪的使用;所以當日志組為inactive的時候,如果資料庫為歸檔模式.那麼日誌肯定是歸檔完成了。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30162081/viewspace-1727538/，如需轉載，請註明出處，否則將追究法律責任。

相關文章