ORACLEデータベース によくあるエラ の解決策
プロのOracle Databaseの復旧サービスを提供
携帯番号： +86 13764045638 メール：service@parnassusdata.com
ここで、11.2でi_dependency1が無効になった場合をシミュレーションする（テスト環境では11.2.0.3を使ってください）：
11.2で：データベースに依頼関係があるテーブルは何か探ってみよう：
SQL> select owner,object_id,object_name,object_type from dba_objects where object_name like '%DEPENDENCY%'; OWNER OBJECT_ID OBJECT_NAME OBJECT_TYPE --------------- ---------- ------------------------------ ------------------- SYS 104 DEPENDENCY$ TABLE SYS 106 I_DEPENDENCY1 INDEX SYS 107 I_DEPENDENCY2 INDEX SYS 1511 V_$OBJECT_DEPENDENCY VIEW SYS 2200 GV_$OBJECT_DEPENDENCY VIEW SYS 2837 GV_$SQL_FEATURE_DEPENDENCY VIEW SYS 2839 V_$SQL_FEATURE_DEPENDENCY VIEW SYS 2857 GV_$RESULT_CACHE_DEPENDENCY VIEW SYS 2859 V_$RESULT_CACHE_DEPENDENCY VIEW SYS 4841 PUBLIC_DEPENDENCY VIEW PUBLIC 1512 V$OBJECT_DEPENDENCY SYNONYM PUBLIC 2201 GV$OBJECT_DEPENDENCY SYNONYM PUBLIC 2838 GV$SQL_FEATURE_DEPENDENCY SYNONYM PUBLIC 2840 V$SQL_FEATURE_DEPENDENCY SYNONYM PUBLIC 2858 GV$RESULT_CACHE_DEPENDENCY SYNONYM PUBLIC 2860 V$RESULT_CACHE_DEPENDENCY SYNONYM PUBLIC 4842 PUBLIC_DEPENDENCY SYNONYM SYSMAN 14862 MGMT_INV_DEPENDENCY_RULE TABLE SYSMAN 15200 MGMT_METRIC_DEPENDENCY_DEF TABLE SYSMAN 15201 PK_MGMT_METRIC_DEPENDENCY_DEF INDEX SYSMAN 15202 MGMT_METRIC_DEPENDENCY TABLE SYSMAN 15203 PK_MGMT_METRIC_DEPENDENCY INDEX SYSMAN 15204 MGMT_METRIC_DEPENDENCY_DETAILS TABLE SYSMAN 15851 MGMT_METRIC_DEPENDENCY_IDX_01 INDEX 24 rows selected. SQL>
ここで、10gは18，11.2は24。
SQL> select owner,table_name,index_name,tablespace_name,status from dba_indexes where table_name='DEPENDENCY$'; OWNER TABLE_NAME INDEX_NAME TABLESPACE_NAME STATUS --------------- ------------------------------ ------------------------------ ------------------------------ -------- SYS DEPENDENCY$ I_DEPENDENCY2 SYSTEM VALID SYS DEPENDENCY$ I_DEPENDENCY1 SYSTEM VALID SQL>
10gと11gで、DEPENDENCY$テーブルに二つのインディクスが存在している、この二つのインディクスが壊れた場合に、データベースへの影響が異なっている。その意味は以下の通り：
create table dependency$ /* dependency table */
( d_obj# number not null, /* dependent object number */
d_timestamp date not null, /* dependent object specification timestamp */
order# number not null, /* order number */
p_obj# number not null, /* parent object number */
p_timestamp date not null, /* parent object specification timestamp */
d_owner# number, /* dependent owner number */
property number not null, /* 0x01 = HARD dependency */
/* 0x02 = REF dependency */
/* 0x04 = FINER GRAINED dependency */
d_attrs raw("M_CSIZ"), /* Finer grain attr. numbers if finer grained */
d_reason raw("M_CSIZ")) /* Reason mask of attrs causing invalidation */
storage (initial 10k next 100k maxextents unlimited pctincrease 0)
/
create unique index i_dependency1 on
dependency$(d_obj#, d_timestamp, order#)
storage (initial 10k next 100k maxextents unlimited pctincrease 0)
/
create index i_dependency2 on
dependency$(p_obj#, p_timestamp)
storage (initial 10k next 100k maxextents unlimited pctincrease 0)
/
このようなブロックに書き込まれている：
SQL> col segment_name for a30 SQL> select owner,segment_name,segment_type,extent_id,file_id,block_id from DBA_EXTENTS where segment_name like '%DEPENDENCY%'; OWNER SEGMENT_NAME SEGMENT_TYPE EXTENT_ID FILE_ID BLOCK_ID --------------- ------------------------------ ------------------ ---------- ---------- ---------- SYS I_DEPENDENCY2 INDEX 0 1 864 SYS I_DEPENDENCY2 INDEX 1 1 8584 SYS I_DEPENDENCY2 INDEX 2 1 8856 SYS I_DEPENDENCY2 INDEX 3 1 9000 SYS I_DEPENDENCY2 INDEX 4 1 10072 SYS I_DEPENDENCY2 INDEX 5 1 12792 SYS I_DEPENDENCY2 INDEX 6 1 16128 SYS I_DEPENDENCY2 INDEX 7 1 18752 SYS I_DEPENDENCY2 INDEX 8 1 19960 SYS I_DEPENDENCY2 INDEX 9 1 21328 SYS I_DEPENDENCY2 INDEX 10 1 21584 SYS I_DEPENDENCY2 INDEX 11 1 22288 SYS I_DEPENDENCY2 INDEX 12 1 22888 SYS I_DEPENDENCY2 INDEX 13 1 23408 SYS I_DEPENDENCY2 INDEX 14 1 25616 SYS I_DEPENDENCY2 INDEX 15 1 25672 SYS I_DEPENDENCY2 INDEX 16 1 28672 SYS I_DEPENDENCY1 INDEX 0 1 856 SYS I_DEPENDENCY1 INDEX 1 1 8720 SYS I_DEPENDENCY1 INDEX 2 1 8984 SYS I_DEPENDENCY1 INDEX 3 1 10712 SYS I_DEPENDENCY1 INDEX 4 1 14968 SYS I_DEPENDENCY1 INDEX 5 1 19248 SYS I_DEPENDENCY1 INDEX 6 1 20760 SYS I_DEPENDENCY1 INDEX 7 1 21312 SYS I_DEPENDENCY1 INDEX 8 1 21608 SYS I_DEPENDENCY1 INDEX 9 1 22344 SYS I_DEPENDENCY1 INDEX 10 1 23368 SYS I_DEPENDENCY1 INDEX 11 1 25608 SYS I_DEPENDENCY1 INDEX 12 1 25688 SYS I_DEPENDENCY1 INDEX 13 1 29192 SYS I_DEPENDENCY1 INDEX 14 1 32760 SYS I_DEPENDENCY1 INDEX 15 1 34240 SYS I_DEPENDENCY1 INDEX 16 1 37504 SYS DEPENDENCY$ TABLE 0 1 840 SYS DEPENDENCY$ TABLE 1 1 8624 SYS DEPENDENCY$ TABLE 2 1 8800 SYS DEPENDENCY$ TABLE 3 1 8968 SYS DEPENDENCY$ TABLE 4 1 9640 SYS DEPENDENCY$ TABLE 5 1 10080 SYS DEPENDENCY$ TABLE 6 1 12992 SYS DEPENDENCY$ TABLE 7 1 15544 SYS DEPENDENCY$ TABLE 8 1 18728 SYS DEPENDENCY$ TABLE 9 1 19848 SYS DEPENDENCY$ TABLE 10 1 20768 SYS DEPENDENCY$ TABLE 11 1 21296 SYS DEPENDENCY$ TABLE 12 1 21576 SYS DEPENDENCY$ TABLE 13 1 21832 SYS DEPENDENCY$ TABLE 14 1 22808 SYS DEPENDENCY$ TABLE 15 1 23344 SYS DEPENDENCY$ TABLE 16 1 24704 SYS DEPENDENCY$ TABLE 17 1 45440 SYSMAN PK_MGMT_METRIC_DEPENDENCY_DEF INDEX 0 2 14608 SYSMAN MGMT_METRIC_DEPENDENCY_DEF TABLE 0 2 14600 54 rows selected. SQL>
10gと11gのDEPENDENCY$、 I_DEPENDENCY1、I_DEPENDENCY2の意味は同じだが、処理法は異なるだけ。
今は11.2.0.3でi_dependency1失効をシミュレーションする。：
SYS@lunarp>alter index i_dependency1 unusable; Index altered. Elapsed: 00:00:00.42 SYS@lunarp>commit; Commit complete. Elapsed: 00:00:00.00 SYS@lunarp>
該当するalert.log情報は以下の通り:
Wed Mar 05 01:50:18 2014
Index SYS.I_DEPENDENCY1 or some [sub]partitions of the index have been marked unusable
SYS.I_DEPENDENCY1を無効とマークした。
データベースを起動してみよう：
SYS@lunarp>startup mount Welcome Lunar's oracle world! Love you , baby ! ORACLE instance started. Total System Global Area 626327552 bytes Fixed Size 2230952 bytes Variable Size 243271000 bytes Database Buffers 373293056 bytes Redo Buffers 7532544 bytes Database mounted. Welcome Lunar's oracle world! Love you , baby ! SYS@lunarp>oradebug setmypid Statement processed. SYS@lunarp>alter session set db_file_multiblocK_read_count=1; Session altered. Elapsed: 00:00:00.00 SYS@lunarp>alter session set tracefile_identifier='lunar'; Session altered. Elapsed: 00:00:00.01 SYS@lunarp>oradebug event 10046 trace name context forever,level 12; Statement processed. SYS@lunarp>oradebug tracefile_name /u01/app/oracle/diag/rdbms/lunarp/lunarp/trace/lunarp_ora_8257_lunar.trc SYS@lunarp>alter database open; ----------ここに注意してください、直にopenしてください Database altered. Elapsed: 00:00:08.91 SYS@lunarp>oradebug event 10046 trace name context off Statement processed. SYS@lunarp>oradebug close_trace Statement processed. SYS@lunarp>
そうだ！この結果だ、11.2から、I_DEPENDENCY1に似ているindex失効になるとデータベースが起動できなくなる。
10.2でopen upgradeモードが必要としている。
今、このインディクスをリカバリする：
SYS@lunarp>select owner,table_name,index_name,tablespace_name,status from dba_indexes where table_name='DEPENDENCY$'; OWNER TABLE_NAME INDEX_NAME TABLESPACE_NAME STATUS ------------------------------ ------------------------------ ------------------------------ ------------------------------ -------- SYS DEPENDENCY$ I_DEPENDENCY2 SYSTEM VALID SYS DEPENDENCY$ I_DEPENDENCY1 SYSTEM UNUSABLE Elapsed: 00:00:00.16 SYS@lunarp>alter index sys.I_DEPENDENCY1 rebuild online; Index altered. Elapsed: 00:00:02.76 SYS@lunarp>select owner,table_name,index_name,tablespace_name,status from dba_indexes where table_name='DEPENDENCY$'; OWNER TABLE_NAME INDEX_NAME TABLESPACE_NAME STATUS ------------------------------ ------------------------------ ------------------------------ ------------------------------ -------- SYS DEPENDENCY$ I_DEPENDENCY2 SYSTEM VALID SYS DEPENDENCY$ I_DEPENDENCY1 SYSTEM VALID Elapsed: 00:00:00.00 SYS@lunarp>
注意 ：11.2と10.2の間に、相違がある（どうでもいいですけど）：11.2にまた余計なバイト“type#”を取っている：
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(property,0),subname,type#,d_attrs
from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#
では、11.2がなぜ直に起動できるか探ってみよう？
まずは、 I_DEPENDENCY1 が健全の時に、データベース起動に関する操作：
PARSING IN CURSOR #140636885644848 len=185 dep=1 uid=0 oct=3 lid=0 tim=1394010624511477 hv=1850944673 ad='850425a8' sqlid='3ktacv9r56b51' select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(property,0),subname,type#,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order# END OF STMT PARSE #140636885644848:c=5999,e=20725,p=2,cr=41,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1394010624511472 BINDS #140636885644848: 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=7fe8939c71e8 bln=22 avl=03 flg=05 value=426 EXEC #140636885644848:c=1000,e=1100,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=4184428695,tim=1394010624512679 WAIT #140636885644848: nam='db file sequential read' ela= 4373 file#=1 block#=857 blocks=1 obj#=106 tim=1394010624517146 WAIT #140636885644848: nam='db file sequential read' ela= 299 file#=1 block#=858 blocks=1 obj#=106 tim=1394010624517612 FETCH #140636885644848:c=1000,e=4978,p=2,cr=2,cu=0,mis=0,r=0,dep=1,og=4,plh=4184428695,tim=1394010624517698 STAT #140636885644848 id=1 cnt=0 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=2 pr=2 pw=0 time=5006 us cost=0 size=0 card=0)' STAT #140636885644848 id=2 cnt=0 pid=1 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=2 pr=2 pw=0 time=4974 us)' STAT #140636885644848 id=3 cnt=0 pid=2 pos=1 obj=104 op='TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=2 pr=2 pw=0 time=4956 us)' STAT #140636885644848 id=4 cnt=0 pid=3 pos=1 obj=106 op='INDEX RANGE SCAN I_DEPENDENCY1 (cr=2 pr=2 pw=0 time=4936 us)' STAT #140636885644848 id=5 cnt=0 pid=2 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us)' STAT #140636885644848 id=6 cnt=0 pid=5 pos=1 obj=36 op='INDEX RANGE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us)' CLOSE #140636885644848:c=0,e=90,dep=1,type=0,tim=1394010624517803 BINDS #140636885393152:
この文を７回実行して、七回の実行計画も以上のとおり。
I_DEPENDENCY1はUNUSABLEの時に、その文が依然として、七回も実行したが、七回の実行計画も以下の通り： “TABLE ACCESS FULL DEPENDENCY$”。
これはoracle 11.2の2進数コードに判断の機能が増えたと意味している、I_DEPENDENCY1はUNUSABLEの時に自動的に実行計画を修正した：
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#,
nvl(property,0),subname,type#,d_attrs
from
dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 33 0.00 0.02 2 41 0 0
Execute 33 0.00 0.00 0 0 0 0
Fetch 127 0.11 0.55 285 8764 0 94
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 193 0.12 0.58 287 8805 0 94
Misses in library cache during parse: 2
Misses in library cache during execute: 2
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 7
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 2 6 SORT ORDER BY (cr=263 pr=37 pw=0 time=42164 us cost=0 size=0 card=0)
0 2 6 NESTED LOOPS OUTER (cr=263 pr=37 pw=0 time=41858 us)
0 2 6 TABLE ACCESS FULL DEPENDENCY$ (cr=259 pr=37 pw=0 time=38319 us)
0 2 6 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=4 pr=1 pw=0 time=3497 us)
0 2 6 INDEX RANGE SCAN I_OBJ1 (cr=3 pr=0 pw=0 time=995 us)(object id 36)
推測：11.2のコードに、ある判断が使えなくなった。データベースを起動するときに、DEPENDENCY$のようなテーブルのインディクスが無効になって、DEPENDENCY$に対して、全テーブルスキャンを実行する。
