move核心对象表 dependency$ 后数据库无启动问题解决
浏览数：22 /
时间：2015年06月12日
move table:
SQL> startup
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 2083336 bytes
Variable Size 88081912 bytes
Database Buffers 184549376 bytes
Redo Buffers 6303744 bytes
Database mounted.
Database opened.
SQL> alter table dependency$ move;
Table altered.
restart oracle:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 2083336 bytes
Variable Size 88081912 bytes
Database Buffers 184549376 bytes
Redo Buffers 6303744 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
alert日志：
Sun Jan 18 23:39:59 2015
Errors in file /oracle/app/product/10.2.0/db_1/admin/orcl/udump/orcl_ora_27363.trc:
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index ‘SYS.I_DEPENDENCY1‘ or partition of such index is in unusable state
Sun Jan 18 23:39:59 2015
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 27363
ORA-1092 signalled during: alter database open...
chown oracle.oinstall /oracle/app/product/10.2.0/db_1/bin/oracle
chmod 6751 /oracle/app/product/10.2.0/db_1/bin/oracle
startup mount;
alter session set events ‘10046 trace name context forever,level 12‘;
oradebug setmypid
oradebug tracefile_name
alter database open;
/oracle/app/product/10.2.0/db_1/admin/orcl/udump/orcl_ora_27422.trc
10046 trace:
PARSING IN CURSOR #2 len=84 dep=2 uid=0 oct=3 lid=0 tim=1388333382888260 hv=2686874206 ad=‘70696c18‘
select o.name, u.name from obj$ o, user$ u where o.obj# = :1 and o.owner# = u.user#
END OF STMT
PARSE #2:c=1000,e=1173,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=1388333382888249
BINDS #2:
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=2b4e28797e60 bln=22 avl=03 flg=05
value=122
EXEC #2:c=3000,e=2453,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=1388333382890953
WAIT #2: nam=‘db file sequential read‘ ela= 55 file#=1 block#=98 blocks=1 obj#=-1 tim=1388333382891225
WAIT #2: nam=‘db file sequential read‘ ela= 169 file#=1 block#=90 blocks=1 obj#=-1 tim=1388333382891502
FETCH #2:c=0,e=563,p=2,cr=5,cu=0,mis=0,r=1,dep=2,og=4,tim=1388333382891624
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op=‘NESTED LOOPS (cr=5 pr=2 pw=0 time=573 us)‘
STAT #2 id=2 cnt=1 pid=1 pos=1 obj=18 op=‘TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=69 us)‘
STAT #2 id=3 cnt=1 pid=2 pos=1 obj=36 op=‘INDEX UNIQUE SCAN I_OBJ1 (cr=2 pr=0 pw=0 time=41 us)‘
STAT #2 id=4 cnt=1 pid=1 pos=2 obj=22 op=‘TABLE ACCESS CLUSTER USER$ (cr=2 pr=2 pw=0 time=492 us)‘
STAT #2 id=5 cnt=1 pid=4 pos=1 obj=11 op=‘INDEX UNIQUE SCAN I_USER# (cr=1 pr=1 pw=0 time=174 us)‘
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index ‘SYS.I_DEPENDENCY1‘ or partition of such index is in unusable state
EXEC #1:c=551916,e=2086867,p=68,cr=722,cu=0,mis=0,r=0,dep=0,og=1,tim=1388333383882751
ERROR #1:err=1092 tim=443696155
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(property,0),subname,d_attrs from dependency$ d, obj$ o
where d_obj#=:1 and p_obj#=obj#(+) order by order#
备份oracle二进制文件，以便修复后还原。
使用ue找到select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(property,0),subname,d_attrs from dependency$ d, obj$ o
where d_obj#=:1 and p_obj#=obj#(+) order by order#的位置，删除“order by order#”，并增加d_obj#+0和p_obj#+0。
[oracle@bogon ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jan 19 00:53:03 2015
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 2083336 bytes
Variable Size 88081912 bytes
Database Buffers 184549376 bytes
Redo Buffers 6303744 bytes
Database mounted.
SQL> alter session set events ‘10046 trace name context forever,level 12‘;
Session altered.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/oracle/app/product/10.2.0/db_1/admin/orcl/udump/orcl_ora_28745.trc
SQL> alter database open;
Database altered.
在数据库open过程中，alert日志有如下报错。
Database Characterset is AL32UTF8
Mon Jan 19 00:53:45 2015
Errors in file /oracle/app/product/10.2.0/db_1/admin/orcl/bdump/orcl_smon_28730.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index ‘SYS.I_DEPENDENCY2‘ or partition of such index is in unusable state
Mon Jan 19 00:53:45 2015
Errors in file /oracle/app/product/10.2.0/db_1/admin/orcl/bdump/orcl_smon_28730.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index ‘SYS.I_DEPENDENCY2‘ or partition of such index is in unusable state
Mon Jan 19 00:53:45 2015
Errors in file /oracle/app/product/10.2.0/db_1/admin/orcl/bdump/orcl_smon_28730.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index ‘SYS.I_DEPENDENCY2‘ or partition of such index is in unusable state
Opening with internal Resource Manager plan
where NUMA PG = 1, CPUs = 4
Mon Jan 19 00:53:46 2015
Errors in file /oracle/app/product/10.2.0/db_1/admin/orcl/bdump/orcl_smon_28730.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index ‘SYS.I_DEPENDENCY2‘ or partition of such index is in unusable state
replication_dependency_tracking turned off (no async multimaster replication found)
Mon Jan 19 00:53:47 2015
Errors in file /oracle/app/product/10.2.0/db_1/admin/orcl/bdump/orcl_smon_28730.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index ‘SYS.I_DEPENDENCY2‘ or partition of such index is in unusable state
Starting background process QMNC
QMNC started with pid=19, OS id=28758
Mon Jan 19 00:53:51 2015
Errors in file /oracle/app/product/10.2.0/db_1/admin/orcl/bdump/orcl_smon_28730.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index ‘SYS.I_DEPENDENCY2‘ or partition of such index is in unusable state
Mon Jan 19 00:53:57 2015
Completed: alter database open
--重建索引
SQL> alter index SYS.I_DEPENDENCY1 rebuild;
Index altered.
SQL> alter index SYS.I_DEPENDENCY2 rebuild;
Index altered.
alert日志中不再报错，恢复oracle 二进制文件后，正常启动数据库。
SQL> startup
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 2083336 bytes
Variable Size 88081912 bytes
Database Buffers 184549376 bytes
Redo Buffers 6303744 bytes
Database mounted.
Database opened.
SQL> alter table dependency$ move;
Table altered.
restart oracle:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 2083336 bytes
Variable Size 88081912 bytes
Database Buffers 184549376 bytes
Redo Buffers 6303744 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
alert日志：
Sun Jan 18 23:39:59 2015
Errors in file /oracle/app/product/10.2.0/db_1/admin/orcl/udump/orcl_ora_27363.trc:
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index ‘SYS.I_DEPENDENCY1‘ or partition of such index is in unusable state
Sun Jan 18 23:39:59 2015
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 27363
ORA-1092 signalled during: alter database open...
chown oracle.oinstall /oracle/app/product/10.2.0/db_1/bin/oracle
chmod 6751 /oracle/app/product/10.2.0/db_1/bin/oracle
startup mount;
alter session set events ‘10046 trace name context forever,level 12‘;
oradebug setmypid
oradebug tracefile_name
alter database open;
/oracle/app/product/10.2.0/db_1/admin/orcl/udump/orcl_ora_27422.trc
10046 trace:
PARSING IN CURSOR #2 len=84 dep=2 uid=0 oct=3 lid=0 tim=1388333382888260 hv=2686874206 ad=‘70696c18‘
select o.name, u.name from obj$ o, user$ u where o.obj# = :1 and o.owner# = u.user#
END OF STMT
PARSE #2:c=1000,e=1173,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=1388333382888249
BINDS #2:
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=2b4e28797e60 bln=22 avl=03 flg=05
value=122
EXEC #2:c=3000,e=2453,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=1388333382890953
WAIT #2: nam=‘db file sequential read‘ ela= 55 file#=1 block#=98 blocks=1 obj#=-1 tim=1388333382891225
WAIT #2: nam=‘db file sequential read‘ ela= 169 file#=1 block#=90 blocks=1 obj#=-1 tim=1388333382891502
FETCH #2:c=0,e=563,p=2,cr=5,cu=0,mis=0,r=1,dep=2,og=4,tim=1388333382891624
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op=‘NESTED LOOPS (cr=5 pr=2 pw=0 time=573 us)‘
STAT #2 id=2 cnt=1 pid=1 pos=1 obj=18 op=‘TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=69 us)‘
STAT #2 id=3 cnt=1 pid=2 pos=1 obj=36 op=‘INDEX UNIQUE SCAN I_OBJ1 (cr=2 pr=0 pw=0 time=41 us)‘
STAT #2 id=4 cnt=1 pid=1 pos=2 obj=22 op=‘TABLE ACCESS CLUSTER USER$ (cr=2 pr=2 pw=0 time=492 us)‘
STAT #2 id=5 cnt=1 pid=4 pos=1 obj=11 op=‘INDEX UNIQUE SCAN I_USER# (cr=1 pr=1 pw=0 time=174 us)‘
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index ‘SYS.I_DEPENDENCY1‘ or partition of such index is in unusable state
EXEC #1:c=551916,e=2086867,p=68,cr=722,cu=0,mis=0,r=0,dep=0,og=1,tim=1388333383882751
ERROR #1:err=1092 tim=443696155
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(property,0),subname,d_attrs from dependency$ d, obj$ o
where d_obj#=:1 and p_obj#=obj#(+) order by order#
备份oracle二进制文件，以便修复后还原。
使用ue找到select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(property,0),subname,d_attrs from dependency$ d, obj$ o
where d_obj#=:1 and p_obj#=obj#(+) order by order#的位置，删除“order by order#”，并增加d_obj#+0和p_obj#+0。
[oracle@bogon ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jan 19 00:53:03 2015
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 2083336 bytes
Variable Size 88081912 bytes
Database Buffers 184549376 bytes
Redo Buffers 6303744 bytes
Database mounted.
SQL> alter session set events ‘10046 trace name context forever,level 12‘;
Session altered.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/oracle/app/product/10.2.0/db_1/admin/orcl/udump/orcl_ora_28745.trc
SQL> alter database open;
Database altered.
在数据库open过程中，alert日志有如下报错。
Database Characterset is AL32UTF8
Mon Jan 19 00:53:45 2015
Errors in file /oracle/app/product/10.2.0/db_1/admin/orcl/bdump/orcl_smon_28730.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index ‘SYS.I_DEPENDENCY2‘ or partition of such index is in unusable state
Mon Jan 19 00:53:45 2015
Errors in file /oracle/app/product/10.2.0/db_1/admin/orcl/bdump/orcl_smon_28730.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index ‘SYS.I_DEPENDENCY2‘ or partition of such index is in unusable state
Mon Jan 19 00:53:45 2015
Errors in file /oracle/app/product/10.2.0/db_1/admin/orcl/bdump/orcl_smon_28730.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index ‘SYS.I_DEPENDENCY2‘ or partition of such index is in unusable state
Opening with internal Resource Manager plan
where NUMA PG = 1, CPUs = 4
Mon Jan 19 00:53:46 2015
Errors in file /oracle/app/product/10.2.0/db_1/admin/orcl/bdump/orcl_smon_28730.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index ‘SYS.I_DEPENDENCY2‘ or partition of such index is in unusable state
replication_dependency_tracking turned off (no async multimaster replication found)
Mon Jan 19 00:53:47 2015
Errors in file /oracle/app/product/10.2.0/db_1/admin/orcl/bdump/orcl_smon_28730.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index ‘SYS.I_DEPENDENCY2‘ or partition of such index is in unusable state
Starting background process QMNC
QMNC started with pid=19, OS id=28758
Mon Jan 19 00:53:51 2015
Errors in file /oracle/app/product/10.2.0/db_1/admin/orcl/bdump/orcl_smon_28730.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index ‘SYS.I_DEPENDENCY2‘ or partition of such index is in unusable state
Mon Jan 19 00:53:57 2015
Completed: alter database open
--重建索引
SQL> alter index SYS.I_DEPENDENCY1 rebuild;
Index altered.
SQL> alter index SYS.I_DEPENDENCY2 rebuild;
Index altered.
alert日志中不再报错，恢复oracle 二进制文件后，正常启动数据库。
-----------------------------------------------------------------------------------------------------------------
本站注明原创和翻译的均为原创文章，文章允许转载，但必须以链接方式注明源地址，
否则追究法律责任!文章中难免有疏漏欢迎网友批评指正。
QQ: 173386747
Email: [email protected]
Blog: http://blog.csdn.net/card_2005
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
