oracle 11.1.0.7版本也會出現access$表丟失導致資料庫無法啟動

有網友諮詢資料庫啟動報ora-01092：ORACLE 執行個體終止。強制中斷連線，請求幫忙處理
資料庫版本

Trace file d:＼app＼administrator＼diag＼rdbms＼orcl＼orcl＼trace＼orcl_ora_5648.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows NT Version V6.1 Service Pack 1
CPU                 : 1 - type 8664, 1 Physical Cores
Process Affinity    : 0x0000000000000000
Memory (Avail/Total): Ph:7605M/10239M, Ph+PgF:11979M/20477M
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 18
Windows thread id: 5648, image: ORACLE.EXE (SHAD)

open資料庫報ORA-01092: ORACLE 執行個體終止。強制中斷連線

SQL> alter database open;
alter database open
*

第 1 行出現錯誤:
ORA-01092: ORACLE 執行個體終止。強制中斷連線

alert日誌

Thread 1 opened at log sequence 1008
  Current log# 3 seq# 1008 mem# 0: D:＼APP＼ADMINISTRATOR＼ORADATA＼ORCL＼REDO03.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
Errors in file d:＼app＼administrator＼diag＼rdbms＼orcl＼orcl＼trace＼orcl_ora_3964.trc:
ORA-00704: 引導程式進程失敗
ORA-00604: 遞迴 SQL 層級 1 出現錯誤
ORA-00942: 表或視圖不存在
Error 704 happened during db open, shutting down database
USER (ospid: 3964): terminating the instance due to error 704
Instance terminated by USER, pid = 3964
ORA-1092 signalled during: ALTER DATABASE OPEN...
ORA-1092 : opiodr aborting process unknown ospid (3384_3964)
做10046分析日誌
PARSE ERROR #1:len=56 dep=1 uid=0 oct=3 lid=0 tim=1796038335 err=942
select order#,columns,types from access$ where d_obj#=:1
 
*** 2015-01-27 21:24:50.794
----- Error Stack Dump -----

ORA-00604: 遞迴 SQL 層級 1 出現錯誤
ORA-00942: 表或視圖不存在
通過這裡可以知道資料庫在啟動的過程中由於無法訪問access$表從而出現ORA-00942錯誤,又是由於該sql是資料庫內部調用因為出現ORA-00604錯誤.
出現該錯誤的原因是由於:BUG:12733463 – ORA-704, ORA-604 AND ORA-942 ON TABLE ACCESS$ DURING STARTUP

官方提供方法

1. Shutdown (abort) the instance and clean up any OS structures used by the instance.
    Eg: Ensure there is no shared memory, semaphores etc.. left lying around
 
2. Retry the startup.
 
3. If the error persists try and recover the database or recover from a backup.

惜分飛處理方法

startup  upgrade
 
 create table access$
 ( d_obj#        number not null,
   order#        number not null,
   columns       raw(126),
   types         number not null)
   storage (initial 10k next 100k maxextents unlimited pctincrease 0)
/
 
create index i_access1 on
  access$(d_obj#, order#)
  storage (initial 10k next 100k maxextents unlimited pctincrease 0)
/