我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新 ，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。
故障处理：access$表在数据库丢失的恢复
下面是测试一把access$基表丢失的恢复方法
欢迎大家加入ORACLE超级群：17115662 免费解决各种ORACLE问题,以后BLOG将迁移到http://www.htz.pw
1，数据库版本
www.htz.pw > select * from v$version;BANNER——————————————————————————–Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit ProductionPL/SQL Release 11.2.0.3.0 – ProductionCORE 11.2.0.3.0 ProductionTNS for Linux: Version 11.2.0.3.0 – ProductionNLSRTL Version 11.2.0.3.0 – Production
2，启动报错
www.htz.pw > startup forceORACLE instance started.Total System Global Area 237998080 bytesFixed Size 2227216 bytesVariable Size 197133296 bytesDatabase Buffers 33554432 bytesRedo Buffers 5083136 bytesDatabase mounted.ORA-01092: ORACLE instance terminated. Disconnection forcedORA-00704: bootstrap process failureORA-00604: error occurred at recursive SQL level 1ORA-00942: table or view does not existProcess ID: 5599Session ID: 1 Serial number: 5
alert下面报这个错误
这里报递归的SQL出现错误，原因表不存在。下面alert中的日志内容
Completed redo scanread 90 KB redo, 65 data blocks need recoveryStarted redo application atThread 1: logseq 89, block 76381Recovery of Online Redo Log: Thread 1 Group 2 Seq 89 Reading mem 0Mem# 0: /oracle/app/oracle/oradata/orcl1123/redo02.logCompleted redo application of 0.06MBCompleted crash recovery atThread 1: logseq 89, block 76561, scn 306209665 data blocks read, 65 data blocks written, 90 redo k-bytes readThread 1 advanced to log sequence 90 (thread open)Thread 1 opened at log sequence 90Current log# 3 seq# 90 mem# 0: /oracle/app/oracle/oradata/orcl1123/redo03.logSuccessful open of redo thread 1MTTR advisory is disabled because FAST_START_MTTR_TARGET is not setSMON: enabling cache recoveryErrors in file /oracle/app/oracle/diag/rdbms/orcl1123/orcl1123/trace/orcl1123_ora_5599.trc:ORA-00704: bootstrap process failureORA-00604: error occurred at recursive SQL level 1ORA-00942: table or view does not existErrors in file /oracle/app/oracle/diag/rdbms/orcl1123/orcl1123/trace/orcl1123_ora_5599.trc:ORA-00704: bootstrap process failureORA-00604: error occurred at recursive SQL level 1ORA-00942: table or view does not existError 704 happened during db open, shutting down databaseUSER (ospid: 5599): terminating the instance due to error 704Instance terminated by USER, pid = 5599ORA-1092 signalled during: ALTER DATABASE OPEN…opiodr aborting process unknown ospid (5599) as a result of ORA-1092Tue Jun 10 20:41:24 2014ORA-1092 : opitsk aborting processTue Jun 10 20:41:39 2014Starting ORACLE instance (normal)
下面通过10046的方法来实现具体在那条语句报错
[oracle@www.htz.pw sql]$sqlplus / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Tue Jun 10 20:41:37 2014Copyright (c) 1982, 2011, Oracle. All rights reserved.Connected to an idle instance.www.htz.pw > startup mount;ORACLE instance started.Total System Global Area 237998080 bytesFixed Size 2227216 bytesVariable Size 197133296 bytesDatabase Buffers 33554432 bytesRedo Buffers 5083136 bytesDatabase mounted.www.htz.pw > oradebug setmypidStatement processed.www.htz.pw > oradebug event 10046 trace name context forever,level 12;Statement processed.www.htz.pw > oradebug tracefile_name;/oracle/app/oracle/diag/rdbms/orcl1123/orcl1123/trace/orcl1123_ora_5691.trcwww.htz.pw > alter database open;alter database open*ERROR at line 1:ORA-01092: ORACLE instance terminated. Disconnection forcedORA-00704: bootstrap process failureORA-00604: error occurred at recursive SQL level 1ORA-00942: table or view does not existProcess ID: 5691Session ID: 1 Serial number: 5这里可以看到是表access$表不存在
PARSE ERROR #182956478584:len=56 dep=1 uid=0 oct=3 lid=0 tim=1402404227784268 err=942select order#,columns,types from access$ where d_obj#=:1ORA-00704: bootstrap process failureORA-00604: error occurred at recursive SQL level 1ORA-00942: table or view does not existORA-00704: bootstrap process failureORA-00604: error occurred at recursive SQL level 1ORA-00942: table or view does not exist*** 2014-06-10 20:43:47.784USER (ospid: 5691): terminating the instance due to error 704EXEC #182936776088:c=152978,e=465517,p=58,cr=764,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1402404227824845ERROR #182936776088:err=1092 tim=1402404227824898
3，重建access$基表
建议的办法很简单，重启到数据库到upgrade模式，重建基表就可以了。
create table access$ /* access table */( d_obj# number not null, /* dependent object number */order# number not null, /* dependency order number */columns raw("M_BVCO"), /* list of cols for this entry */types number not null) /* access types */storage (initial 10k next 100k maxextents unlimited pctincrease 0)/create index i_access1 onaccess$(d_obj#, order#)storage (initial 10k next 100k maxextents unlimited pctincrease 0)/www.htz.pw > create table access$ /* access table */2 ( d_obj# number not null, /* dependent object number */3 order# number not null, /* dependency order number */4 columns raw("M_BVCO"), /* list of cols for this entry */5 types number not null) /* access types */6 storage (initial 10k next 100k maxextents unlimited pctincrease 0)7 /columns raw("M_BVCO"), /* list of cols for this entry */*ERROR at line 4:ORA-00910: specified length too long for its datatype
在其它相同的版本看到这个是126，但是不知道为什么从脚本弄出来的是一个字符串
SQL> desc access$;Name Null? Type—————————————– ——– —————————-D_OBJ# NOT NULL NUMBERORDER# NOT NULL NUMBERCOLUMNS RAW(126)TYPES NOT NULL NUMBERcreate table access$ /* access table */( d_obj# number not null, /* dependent object number */order# number not null, /* dependency order number */columns raw(126), /* list of cols for this entry */types number not null) /* access types */storage (initial 10k next 100k maxextents unlimited pctincrease 0)/www.htz.pw > create table access$ /* access table */2 ( d_obj# number not null, /* dependent object number */3 order# number not null, /* dependency order number */4 columns raw(126), /* list of cols for this entry */5 types number not null) /* access types */6 storage (initial 10k next 100k maxextents unlimited pctincrease 0)7 /Table created.www.htz.pw > create index i_access1 on2 access$(d_obj#, order#)3 storage (initial 10k next 100k maxextents unlimited pctincrease 0)4 www.htz.pw > /Index created.www.htz.pw > startup force;ORACLE instance started.Total System Global Area 237998080 bytesFixed Size 2227216 bytesVariable Size 197133296 bytesDatabase Buffers 33554432 bytesRedo Buffers 5083136 bytesDatabase mounted.Database opened.
------------------作者介绍-----------------------
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)
