access$表异常丢失的判断和修复

打开数据库报错
SQL> startup 
ORACLE instance started.




Total System Global Area 1252663296 bytes
Fixed Size                  2252824 bytes
Variable Size             402657256 bytes
Database Buffers          838860800 bytes
Redo Buffers                8892416 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
Process ID: 2045
Session ID: 1 Serial number: 5


百度了下ORA-704错误很多，需要详细报错。


SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.




Total System Global Area 1252663296 bytes
Fixed Size                  2252824 bytes
Variable Size             402657256 bytes
Database Buffers          838860800 bytes
Redo Buffers                8892416 bytes
Database mounted.


启用10046事件跟踪
SQL> oradebug setmypid
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 12
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/ogg1/ogg1/trace/ogg1_ora_2103.trc
启动数据库
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
Process ID: 2103
Session ID: 1 Serial number: 5


查看trace文件 /u01/app/oracle/diag/rdbms/ogg1/ogg1/trace/ogg1_ora_2103.trc
EXEC #139711298749464:c=0,e=210,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=2853959010,tim=1487024958433924
WAIT #139711298749464: nam='db file sequential read' ela= 16 file#=1 block#=46462 blocks=1 obj#=37 tim=1487024958434002
WAIT #139711298749464: nam='db file sequential read' ela= 14 file#=1 block#=57115 blocks=1 obj#=37 tim=1487024958434067
FETCH #139711298749464:c=0,e=150,p=2,cr=3,cu=0,mis=0,r=0,dep=2,og=4,plh=2853959010,tim=1487024958434097
CLOSE #139711298749464:c=0,e=3,dep=2,type=3,tim=1487024958434209
=====================
PARSE ERROR #139711273628304:len=56 dep=1 uid=0 oct=3 lid=0 tim=1487024958434335 err=942
select order#,columns,types from access$ where d_obj#=:1
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist


表access$丢了
重建表及索引


启动到upgrade模式
SQL> startup upgrade 
ORACLE instance started.




Total System Global Area 1252663296 bytes
Fixed Size                  2252824 bytes
Variable Size             402657256 bytes
Database Buffers          838860800 bytes
Redo Buffers                8892416 bytes
Database mounted.
Database opened.
重建access$表和索引
SQL> create table access$
  2      ( d_obj#        number not null,
  3       order#        number not null,
  4        columns       raw(126),
  5        types         number not null)
  6       storage (initial 10k next 100k maxextents unlimited pctincrease 0)
  7     /


Table created.


SQL> create index i_access1 on
  2        access$(d_obj#, order#)
  3        storage (initial 10k next 100k maxextents unlimited pctincrease 0)
  4      /


Index created.


重启数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.




Total System Global Area 1252663296 bytes
Fixed Size                  2252824 bytes
Variable Size             402657256 bytes
Database Buffers          838860800 bytes
Redo Buffers                8892416 bytes
Database mounted.
Database opened.
SQL>