oracle跨表空间报错ORA00942,解决oracle报错ora-00704 ora-00604 ora-00942 启动不了数据库...

使用conn / as sysdba登录oracle，使用start mount出现此错误

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  4728872960 bytes

Fixed Size            1314156  bytes

Variable Size          163578516  bytes

Database Buffers      1019898880 bytes

Redo Buffers            6004736  bytes

Database mounted.

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: 2056

Session ID: 128 Serial number: 7

发现报的错和alert日志显示的一样。

这个错大概是system表空间里面的核心表被删除了，所以数据库启动不起来。这里，如果没有备份的话，这个就不能用常规的手段启动了。

首先去找到报ORA-00704的原因：

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  4728872960 bytes

Fixed Size            1314156  bytes

Variable Size          163578516  bytes

Database Buffers      1019898880 bytes

Redo Buffers            6004736  bytes

Database mounted.

SQL> alter session set sql_trace=true;

SQL> select value from v$diag_info where name='Default Trace File';

VALUE

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

/u01/app/oracle/diag/rdbms/iscba/iscba/trace/iscba_ora_22821.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: 2176

Session ID: 128 Serial number: 7

然后查看iscba_ora_22821.trc：

Trace file /u01/app/oracle/diag/rdbms/iscba/iscba/trace/iscba_ora_22821.trc

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORACLE_HOME = /u01/app/oracle/product/11.2/db_1

System name:    Linux

Node name:    h-badb-01

Release:    2.6.32-431.el6.x86_64

Version:    #1 SMP Sun Nov 10 22:19:54 EST 2013

Machine:    x86_64

VM name:    VMWare Version: 6

Instance name: iscba

Redo thread mounted by this instance: 1

Oracle process number: 17

Unix process pid: 22821, image: oracle@h-badb-01 (TNS V1-V3)

*** 2016-02-25 10:41:03.622

*** SESSION ID:(202.3) 2016-02-25 10:41:03.622

*** CLIENT ID:() 2016-02-25 10:41:03.622

*** SERVICE NAME:() 2016-02-25 10:41:03.622

*** MODULE NAME:(sqlplus@h-badb-01 (TNS V1-V3)) 2016-02-25 10:41:03.622

*** ACTION NAME:() 2016-02-25 10:41:03.622

。。。省略N多字。

发现最后又如下提示：

=====================

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

*** 2016-02-25 10:41:42.317

USER (ospid: 22821): terminating the instance due to error 704

*** 2016-02-25 10:41:43.413

EXEC #139799435394504:c=309952,e=2052517,p=57,cr=758,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1456368103413294

ERROR #139799435394504:err=1092 tim=1456368103413356

这个地方就能确定，是缺失 access$ 造成的数据库启动不起来。

为了解决问题，我们只有用非常规的手段来自己创建这个表，来进行恢复，步骤如下：

1.启动数据库到upgrade状态

SQL> startup  upgrade

ORACLE instance started.

Total System Global Area  4728872960 bytes

Fixed Size            1314156  bytes

Variable Size          163578516  bytes

Database Buffers      1019898880 bytes

Redo Buffers            6004736  bytes

Database mounted.

Database opened.

2.创建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.

注意：创建语句可以在$ORACLE_HOME/RDBMS/ADMIN/dcore.bsq中找到

3.重启数据库

QL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  4728872960 bytes

Fixed Size            1314156  bytes

Variable Size          163578516  bytes

Database Buffers      1019898880 bytes

Redo Buffers            6004736  bytes

Database mounted.

Database opened.

4.检查状态并用tnsname去连接

select status from v$instance;

sqlplus username/password@TNSNAMES