move核心對象表 dependency$ 後資料庫無啟動問題解決，movedependency

move核心對象表 dependency$ 後資料庫無啟動問題解決，movedependency
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 二進位檔案後，正常啟動資料庫。

-----------------------------------------------------------------------------------------------------------------
本站註明原創和翻譯的均為原創文章，文章允許轉載，但必須以連結方式註明源地址，
否則追究法律責任!文章中難免有疏漏歡迎網友批評指正。

QQ:       173386747

Email:    hailong.sun1982@gmail.com

Blog:     http://blog.csdn.net/card_2005