Oracle 11.2でI_DEPENDENCY1損害対応

ORACLEデータベース によくあるエラ の解決策

プロのOracle Databaseの復旧サービスを提供
携帯番号: +86 13764045638 メール:[email protected]

 

ここで、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:


この文を7回実行して、七回の実行計画も以上のとおり。
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$に対して、全テーブルスキャンを実行する。


Posted

in

by

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *