資料檔案offline後unusable索引造成的問題

lhrbest發表於2016-07-17
索引



最近在做一個oracle入庫速度測試時，想到將最近一個小時的索引放到記憶體中（表是按小時分割槽）是不是會提升入庫的速度，索引的維護對io是一個不小的開銷；
不過這個方案如果要使用的話資料庫必須是 oracle 12c，因為在當前小時結束後，需要將相關索引移出記憶體，讓下一個小時的索引留在記憶體，這樣記憶體的使用情況基本是一個定量；
而在移動的過程中不能對業務有影響，這樣需要用到12c的新功能，線上移動資料檔案。

測試的結果是入庫速度有很明顯的提升，入庫速度是之前的幾倍，但這個不是本文的重點；本文的重點是在測試的過程中發現的一個小問題，不確定是不是bug（oracle 11.2.0.4也有這個問題），以下是過程。

1.準備工作

建立表空間、使用者等
  1. create tablespace dasong datafile '/oradata/oracle/dasong.dbf' size 100m;
    create tablespace dasong_idx2 datafile '/oradata/oracle/dasong_idx2.dbf' size 100m;
    create tablespace dasong_idx3 datafile '/oradata/oracle/dasong_idx3.dbf' size 100m;

    create user dasong identified by dasong
    default tablespace dasong
    temporary tablespace temp;

    grant dba to dasong;
    grant create session to dasong;
    grant resource to dasong;
    grant debug connect session to dasong;
    grant debug any procedure to dasong;
    grant select_catalog_role to dasong;

2.建立表、索引，並插入資料

  1. create table t_idx_offline_test
    (
      c1 number,
      c2 number,
      c3 number
    )
    partition by range(c1) interval(1000)
    (
      partition part_0 values less than(0)
    ) tablespace dasong;

    create index idx_test_c2 on t_idx_offline_test(c2) tablespace dasong_idx2 local;
    create index idx_test_c3 on t_idx_offline_test(c3) tablespace dasong_idx3 local;

    insert into t_idx_offline_test
    select rownum, rownum+1, rownum+2 from dual connect by rownum<10000;
    commit;

3.offline資料檔案

  1. alter database datafile '/oradata/oracle/dasong_idx2.dbf' offline for drop;
資料檔案offline不會影響索引分割槽的狀態，分割槽相關的段也是存在的，索引並沒有察覺到相關的資料檔案已離線
  1. select * from user_ind_partitions;


  1. select * from user_segments;


4.rebuild索引分割槽

  1. alter index idx_test_c2 rebuild partition sys_p872 tablespace dasong_idx3;
原索引所在資料檔案如果不可用，則rebuild失敗，因為rebuild會從原索引中讀資料，暫不知道怎麼樣繞過
  1. SQL Error: ORA-00376: file 10 cannot be read at this time
    ORA-01110: data file 10: '/oradata/oracle/dasong_idx2.dbf'
    00376. 00000 -  "file %s cannot be read at this time"
    *Cause:    attempting to read from a file that is not readable. Most likely
               the file is offline.
    *Action:   Check the state of the file. Bring it online

先將原來索引分割槽unusable（SYS_P872）
  1. alter index idx_test_c2 modify partition sys_p872 unusable;
索引分割槽SYS_P872狀態變成UNUSABLE


分割槽SYS_P872對應的段消失，多出了一個10.130（這個是原來的SYS_P872對應的段）


現在rebuild索引分割槽 到 其它表空間（dasong_idx3），可以成功
  1. alter index idx_test_c2 rebuild partition sys_p872 tablespace dasong_idx3;
rebuild成功可以看到索引分割槽狀態為usable，tablespace是dasong_idx3


對應的新段在dasong_idx3表空間中，原來的10.130並不沒有消失（即使將資料檔案online，重啟資料庫，10.130也不會消失）


5.temporary段處理

所有使用索引unusable的操作都會使索引原來的分割槽段變成temporary（unusable索引、刪除索引、exchange索引分割槽對應的資料分割槽 等）；
除非刪除表空間，其它常規手段都不會刪除temporary段（其實此時索引對應的段應該是已經沒有了，或是訪問不到的，因為資料檔案不可用，此時看到的只是後設資料）

  1. alter index idx_test_c2 unusable;
  2. alter index idx_test_c2 modify default attributes tablespace dasong_idx3;






  1. drop tablespace dasong_idx2 including contents and datafiles;
索引idx_test_c2有一個分割槽在dasong_idx3分割槽中，所以刪除表空間會報錯（rebuild之前應該能刪除掉表空間，但是including也會把索引刪除，這不是我想要的結果）
  1. SQL Error: ORA-14405: partitioned index contains partitions in a different tablespace
    14405. 00000 -  "partitioned index contains partitions in a different tablespace"
    *Cause:    An attempt was made to drop a tablespace which contains indexes
               whose partitions are not completely contained in this tablespace,
               and which are defined on the tables which are completely contained
               in this tablespace.
    *Action:   find indexes with partitions which span the tablespace being
               dropped and some other tablespace(s). Drop these indexes, or move
               the index partitions to a different tablespace, or find the tables
               on which the indexes are defined, and drop (or move) them.

即使 手動刪除seg$內容（不確定會不會造成其它問題），user_segments中不再有相關的temporary段，此時刪除表空間dasong_idx2還是會報上面的錯，說明資料字典沒有清理完全，還是有一部分跟dasong_idx2相關（資料庫還是認為dasong_idx2有索引資料）
  1. delete from seg$ where file#=10 and type#=3;
    commit;



6.刪除索引、刪除表空間

最後只能先刪除索引，再刪除表空間，才能清理完對應的資料字典資料。
  1. drop index idx_test_c2;
    drop tablespace dasong_idx2 including contents and datafiles;

暫時沒有想出其它的方法來解決這個問題，不過temporary沒有清理，也不會影響索引部分分割槽的rebuild（先unusable，再rebuild），只是看起來比較不舒服，如果可以接受這個的話，此方案還是可以考濾的，畢竟入庫速度有好幾倍的提升。


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2122112/，如需轉載，請註明出處，否則將追究法律責任。

相關文章