当前位置: 首页 > ORA > 正文

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

2,启动报错

www.htz.pw > startup force

ORACLE instance started.

 

Total System Global Area  237998080 bytes

Fixed Size                  2227216 bytes

Variable Size             197133296 bytes

Database Buffers           33554432 bytes

Redo Buffers                5083136 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: 5599

Session ID: 1 Serial number: 5

 

 

alert下面报这个错误

这里报递归的SQL出现错误,原因表不存在。下面alert中的日志内容

 

Completed redo scan

 read 90 KB redo, 65 data blocks need recovery

Started redo application at

 Thread 1: logseq 89, block 76381

Recovery of Online Redo Log: Thread 1 Group 2 Seq 89 Reading mem 0

  Mem# 0: /oracle/app/oracle/oradata/orcl1123/redo02.log

Completed redo application of 0.06MB

Completed crash recovery at

 Thread 1: logseq 89, block 76561, scn 3062096

 65 data blocks read, 65 data blocks written, 90 redo k-bytes read

Thread 1 advanced to log sequence 90 (thread open)

Thread 1 opened at log sequence 90

  Current log# 3 seq# 90 mem# 0: /oracle/app/oracle/oradata/orcl1123/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 /oracle/app/oracle/diag/rdbms/orcl1123/orcl1123/trace/orcl1123_ora_5599.trc:

ORA-00704: bootstrap process failure

ORA-00604: error occurred at recursive SQL level 1

ORA-00942: table or view does not exist

Errors in file /oracle/app/oracle/diag/rdbms/orcl1123/orcl1123/trace/orcl1123_ora_5599.trc:

ORA-00704: bootstrap process failure

ORA-00604: error occurred at recursive SQL level 1

ORA-00942: table or view does not exist

Error 704 happened during db open, shutting down database

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

Instance terminated by USER, pid = 5599

ORA-1092 signalled during: ALTER DATABASE OPEN…

opiodr aborting process unknown ospid (5599) as a result of ORA-1092

Tue Jun 10 20:41:24 2014

ORA-1092 : opitsk aborting process

Tue Jun 10 20:41:39 2014

Starting ORACLE instance (normal)

 

下面通过10046的方法来实现具体在那条语句报错

[oracle@www.htz.pw sql]$sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jun 10 20:41:37 2014

 

Copyright (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 bytes

Fixed Size                  2227216 bytes

Variable Size             197133296 bytes

Database Buffers           33554432 bytes

Redo Buffers                5083136 bytes

Database mounted.

www.htz.pw > oradebug setmypid

Statement 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.trc

www.htz.pw > 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: 5691

Session ID: 1 Serial number: 5

 

 

这里可以看到是表access$表不存在

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

 

*** 2014-06-10 20:43:47.784

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

EXEC #182936776088:c=152978,e=465517,p=58,cr=764,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1402404227824845

ERROR #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 on

  access$(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 NUMBER

 ORDER#                                    NOT NULL NUMBER

 COLUMNS                                            RAW(126)

 TYPES                                     NOT NULL NUMBER

 

create 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 on

  2    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 bytes

Fixed Size                  2227216 bytes

Variable Size             197133296 bytes

Database Buffers           33554432 bytes

Redo Buffers                5083136 bytes

Database mounted.

Database opened.

 

本文固定链接: http://www.htz.pw/2014/06/12/access%e8%a1%a8%e5%9c%a8%e6%95%b0%e6%8d%ae%e5%ba%93%e4%b8%a2%e5%a4%b1%e7%9a%84%e6%81%a2%e5%a4%8d.html | 认真就输

该日志由 huangtingzhong 于2014年06月12日发表在 ORA 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: access$表在数据库丢失的恢复 | 认真就输
关键字: , , ,