11.1.0.7版本也会出现access$表丢失导致数据库无法启动

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题: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)
/

以前类似文章:Oracle 异常恢复案例汇总

此条目发表在 Oracle备份恢复 分类目录,贴了 , , , , 标签。将固定链接加入收藏夹。

11.1.0.7版本也会出现access$表丢失导致数据库无法启动》有 1 条评论

  1. 惜分飞 说:

    Unavailable Bootstrap Object ACCESS$ Causes ORA-704 ORA-604 ORA-942 When Opening Database (Doc ID 1383179.1)

    APPLIES TO:
    
    Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
    Information in this document applies to any platform.
    checked for relevance on 08-Oct-2014[This section is not visible to customers.]
    SYMPTOMS
    
    When attempting to start the database using sqlplus as sysdba, the following errors are raised:
    
    ORA-00704: bootstrap process failure
    ORA-00604: error occurred at recursive SQL level 1
    ORA-00942: table or view does not exist
    
    The errors are shown in the sqlplus output but are also available in the alert.log
    
    Sql trace on startup shows:
    
    PARSE ERROR #4573972496:len=56 dep=1 uid=0 oct=3 lid=0 tim=5805202237641 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
    CAUSE
    
    The object access$ is not available.
    
    Access$ is a bootstrap object which is necessary to start the DB.
    
    
    SOLUTION
    
    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.
    
    
    REFERENCES
    
    BUG:12733463 - ORA-704, ORA-604 AND ORA-942 ON TABLE ACCESS$ DURING STARTUP
    NOTE:18494.1 - OERR: ORA 704 "bootstrap process failure"