标签归档：access$

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

发表于 2015 年 1 月 27 日由惜分飞

有网友咨询数据库启动报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备份恢复 | 标签为 access$, ORA-00604, ORA-00942, ORA-01092, Oracle 恢复 | 一条评论

Oracle 11g丢失access$恢复方法

发表于 2012 年 6 月 26 日由惜分飞

最近接触到两个案例都是11g数据库因为异常关闭导致access$表丢失,使得数据库不能正常open.为什么这个表会丢失还未找到原因.我这里提供一种在upgrade模式下解决给问题方法.
数据库版本

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "xifenfei" from dual;

xifenfei
--------------------------------------
2012-06-22 05:28:57

数据库启动报ORA-00704

SQL> startup
ORACLE instance started.

Total System Global Area  523108352 bytes
Fixed Size                  1346052 bytes
Variable Size             448792060 bytes
Database Buffers           67108864 bytes
Redo Buffers                5861376 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: 1782
Session ID: 125 Serial number: 5

找出ORA-00704报错原因

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

Total System Global Area  523108352 bytes
Fixed Size                  1346052 bytes
Variable Size             448792060 bytes
Database Buffers           67108864 bytes
Redo Buffers                5861376 bytes
Database mounted.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
Statement processed.
SQL> oradebug TRACEFILE_NAME
/u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_2010.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: 2010
Session ID: 125 Serial number: 5

查看trace文件发现

PARSE ERROR #3063868604:len=56 dep=1 uid=0 oct=3 lid=0 tim=1340312320595472 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

*** 2012-06-22 04:58:40.596
USER (ospid: 2010): terminating the instance due to error 704

启动数据库至upgrade模式

SQL> startup  upgrade
ORACLE instance started.

Total System Global Area  523108352 bytes
Fixed Size                  1346052 bytes
Variable Size             448792060 bytes
Database Buffers           67108864 bytes
Redo Buffers                5861376 bytes
Database mounted.
Database opened.

创建access$表和index

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.
--创建语句可以在?\RDBMS\ADMIN\dcore.bsq中找到

重启数据库

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  523108352 bytes
Fixed Size                  1346052 bytes
Variable Size             448792060 bytes
Database Buffers           67108864 bytes
Redo Buffers                5861376 bytes
Database mounted.
Database opened.

access$表作用(感谢vmcd同学提供)
When a database object is first referenced in a PL/SQL program, the PL/SQL engine checks the ACCESS$ table (owned by SYS) to see if the executor of the program has authority on that database object.
对于access$表丢失以前记录是否对系统产生严重影响还未知,希望知道的朋友告知下

发表在 Oracle备份恢复 | 标签为 access$ | 2 条评论

17813235971

QQ 咨询

标签归档：access$

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

Oracle 11g丢失access$恢复方法

文章分类

最近发表

标签归档：access$

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

Oracle 11g丢失access$恢复方法

标签云

文章分类

最近发表