• Oracle Database
• 2013.11.07
• 渡部 亮太

論理破損は厄介だ – 表と索引の不整合

現在、来週の特濃JPOUG | Japan Oracle User Groupの準備をしつつ、いろいろ検証しております。その中で、改めて論理破損の厄介さを感じております。

ごにょごにょして表と索引が不整合となっている状況を作ります。

SQL> SELECT i,s1 FROM tbl_index_mismatch;

         I S1
---------- ----------
         1 AAAAAAAAAA
           AAAAAAAAAA
           AAAAAAAAAA
           AAAAAAAAAA
           AAAAAAAAAA
           AAAAAAAAAA
           AAAAAAAAAA
           AAAAAAAAAA
           AAAAAAAAAA
           AAAAAAAAAA

         I S1
---------- ----------
         2 BBBBBBBBBB
           BBBBBBBBBB
           BBBBBBBBBB
           BBBBBBBBBB
           BBBBBBBBBB
           BBBBBBBBBB
           BBBBBBBBBB
           BBBBBBBBBB
           BBBBBBBBBB
           BBBBBBBBBB

SQL> SELECT /*+ index(tbl_index_mismatch idx_index_mismatch) */ i,s1 FROM tbl_index_mismatch WHERE i = 3;

         I S1
---------- ----------
         3 BBBBBBBBBB
           BBBBBBBBBB
           BBBBBBBBBB
           BBBBBBBBBB
           BBBBBBBBBB
           BBBBBBBBBB
           BBBBBBBBBB
           BBBBBBBBBB
           BBBBBBBBBB
           BBBBBBBBBB

通常の運用で定期的に実行される可能性がある作業で、破損チェックを実行可能なのはRMAN backupぐらいかなと。

RMAN> BACKUP DATAFILE 7;

Starting backup at 05-NOV-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=45 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/u01/app/oracle/oradata/c101p/tbs3.dbf
channel ORA_DISK_1: starting piece 1 at 05-NOV-13
channel ORA_DISK_1: finished piece 1 at 05-NOV-13
piece handle=/u01/app/oracle/product/12.1.0.1/ee_1/dbs/09oo7nem_1_1 tag=TAG20131105T210630 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-NOV-13

ここで検出されないのは想定内。では、CHECK LOGICALでは・・・

RMAN> BACKUP CHECK LOGICAL DATAFILE 7;

Starting backup at 05-NOV-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/u01/app/oracle/oradata/c101p/tbs3.dbf
channel ORA_DISK_1: starting piece 1 at 05-NOV-13
channel ORA_DISK_1: finished piece 1 at 05-NOV-13
piece handle=/u01/app/oracle/product/12.1.0.1/ee_1/dbs/0aoo7nes_1_1 tag=TAG20131105T210636 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-NOV-13

あれれ、成功しちゃった・・・。じゃ、念のためVALIDATE系のコマンドも実行してみます。

RMAN> BACKUP CHECK LOGICAL VALIDATE  DATAFILE 7;

Starting backup at 05-NOV-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/u01/app/oracle/oradata/c101p/tbs3.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7    OK     0              1141         1280            629681
  File Name: /u01/app/oracle/oradata/c101p/tbs3.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              5
  Index      0              1
  Other      0              133

Finished backup at 05-NOV-13

RMAN> validate datafile 7;

Starting validate at 05-NOV-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=53 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00007 name=/u01/app/oracle/oradata/c101p/tbs3.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7    OK     0              1141         1280            629681
  File Name: /u01/app/oracle/oradata/c101p/tbs3.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              5
  Index      0              1
  Other      0              133

Finished validate at 05-NOV-13

VALIDATE系のコマンドでもチェックできない。

ではしょうがない。負荷の高いコマンドですが、 ANALYZE TABLE VALIDATE STRUCTURE CASCADE を実行してみます。

SQL> set timing on
SQL> analyze table tbl_index_mismatch validate structure cascade;
analyze table tbl_index_mismatch validate structure cascade
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file

Elapsed: 00:00:44.76

流石にこのコマンドであれば、破損を検出できます。しかし、高々2行のテーブルの破損チェックに 44秒かかるとはなかなか厳しい・・・

(おそらく11.2から導入された)FASTオプションを指定すると、処理時間が大幅に短縮されるが、破損が検出できない・・・

SQL> analyze table tbl_index_mismatch validate structure cascade FAST;

Table analyzed.

Elapsed: 00:00:00.00
SQL>

なかなか苦しい・・・