oracle 链接丢失,Oracle 11g丢失access$恢复方法

最近接触到两个案例都是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$表丢失以前记录是否对系统产生严重影响还未知,希望知道的朋友告知下