資料庫open報錯ORA-01555問題

管理的測試庫出問題了，無法open，我們先來看看是什麼問題：

Recovery of Online Redo Log: Thread 1 Group 4 Seq 4 Reading mem 0    Mem# 0: /onlinelog/shr/redo04.log  Completed redo application of 0.00MB  Completed crash recovery at   Thread 1: logseq 4, block 3, scn 7755957   0 data blocks read, 0 data blocks written, 0 redo k-bytes read  Thread 1 advanced to log sequence 5 (thread open)  Thread 1 opened at log sequence 5    Current log# 5 seq# 5 mem# 0: /onlinelog/shr/redo05.log  Successful open of redo thread 1  MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set  Thu Jun 19 13:31:35 2014  SMON: enabling cache recovery  ORA-01555 caused by SQL statement below (SQL ID: 4krwuz0ctqxdt, SCN: 0x0000.007658ba):  select ctime, mtime, stime from obj$ where obj# = :1  Errors in file /oracle/diag/rdbms/shr/shr/trace/shr_ora_5262.trc:  ORA-00704: bootstrap process failure  ORA-00704: bootstrap process failure  ORA-00604: error occurred at recursive SQL level 1  ORA-01555: snapshot too old: rollback segment number 6 with name "_SYSSMU6_1263032392$" too small  Errors in file /oracle/diag/rdbms/shr/shr/trace/shr_ora_5262.trc:  ORA-00704: bootstrap process failure  ORA-00704: bootstrap process failure  ORA-00604: error occurred at recursive SQL level 1  ORA-01555: snapshot too old: rollback segment number 6 with name "_SYSSMU6_1263032392$" too small  Error 704 happened during db open, shutting down database  USER (ospid: 5262): terminating the instance due to error 704  Instance terminated by USER, pid = 5262  ORA-1092 signalled during: ALTER DATABASE OPEN...  opiodr aborting process unknown ospid (5262) as a result of ORA-1092  Thu Jun 19 13:31:37 2014  ORA-1092 : opitsk aborting process

從上面的錯誤來看，該資料庫之所以open失敗,是由於Oracle在bootstrap階段執行遞迴SQL時出現ora-01555錯誤,
這樣bootstrap過程無法繼續下去，也就導致資料庫無法open。我們可以看到報錯的SQL語句如下：
select ctime, mtime, stime from obj$ where obj# = :1

這是很熟悉的一個SQL，通過10046 trace跟蹤Oracle open的過程你會發現該SQL。

針對該錯誤，或許有人以為是復原段的問題，實際上並不是，這種情況下推進下SCN 就可以很順利的把資料庫open。

但是這裡有個問題：該兄弟的資料庫是Oracle 11.2.0.4，已經不支援傳統的10015 event的方式了。

下面我們通過oradebug 來解決該問題：

SQL> conn /as sysdba  Connected to an idle instance.  SQL> startup mount  ORACLE instance started.         Total System Global Area 4275781632 bytes  Fixed Size                  2260088 bytes  Variable Size             989856648 bytes  Database Buffers         3271557120 bytes  Redo Buffers               12107776 bytes  Database mounted.  SQL>  SQL> oradebug poke 0x06001AE70 4 0x859AFA  ORA-00074: no process has been specified  SQL> oradebug setmypid  Statement processed.  SQL>  oradebug DUMPvar SGA kcsgscn_  kcslf kcsgscn_ [06001AE70, 06001AEA0) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 6001AB50 00000000  SQL>  oradebug poke 0x06001AE70 4 0x859AFA  BEFORE: [06001AE70, 06001AE74) = 00000000  AFTER:  [06001AE70, 06001AE74) = 00859AFA  SQL> alter database open;         Database altered.         SQL>

這裡簡單解釋一下，4 為長度，0x859AFA是16進位，我在原來的v$datafile_header.checkpoint_change#的基礎之上
加上上1000000得到該值。