2013/05/15

Published 5月 15, 2013 by

Oracle Rebuild Index



1. 文件目的


提供Oracle Rebuild操作說明,能依照此文件進行各項工作的處理與確認。




2. Oracle Version


查看Oracle版本,以下的指令適用在10.2.0.4版本:

[oracle@c5rptdb1 OPatch]$ ./opatch lsinventory

Invoking OPatch 10.2.0 .4.2



Oracle Interim Patch Installer version 10.2.0 .4.2

Copyright (c) 2007, Oracle Corporation.  All rights reserved.





Oracle Home       : /orasys/product/10.2

Central Inventory : /orasys/product/oraInventory

   from           : /etc/oraInst.loc

OPatch version    : 10.2.0 .4.2

OUI version       : 10.2.0 .4.0

OUI location      : /orasys/product/10.2/oui

Log file location : /orasys/product/10.2/cfgtoollogs/opatch/opatch 2013-05-15 _08-35-51AM.log



Lsinventory Output file location : /orasys/product/10.2/cfgtoollogs/opatch/lsinv/lsinventory 2013-05-15 _08-35-51AM.txt



--------------------------------------------------------------------------------

Installed Top-level Products (2):



Oracle Database 10g                                                  10.2.0 .1.0

Oracle Database 10g Release 2 Patch Set 3                            10.2.0 .4.0

There are 2 products installed in this Oracle Home.





There are no Interim patches installed in this Oracle Home.





--------------------------------------------------------------------------------



OPatch succeeded.



 



3. 確認tablespace的大小


透過tode,可以知道STGC5_IDX這一個快沒有空間了,已使用79%,剩下26G空間。



由這一個欄位資訊,可以得知:

MAX SIZE每一個dbf31G,目前使用Used每一個是24G,剩下(24+24+25+25)/(31*4)=79%空間,可用空間26G(31*4-24+24+25+25=26)





4. Index Rebuild/File Add語法及注意事項




rebuild index online的指令範例語法如下:



(1)在原來的Tablespace rebuild

ALTER INDEX index_name REBUILD ONLINE;



(2)rebuild 到另一個Tablespace

ALTER INDEX  index_name REBUILD TABLESPACE tablespace_name ONLINE;



(3)add file Tablespace



注意事項:

一、須注意避開DB忙碌時間即可 ex. 晨午報產生時間

二、注意最大的index_name需大於剩餘空間

三、Rebuild到另一個tablespace,還需要在rebuild回原來的tablespace,所以要做兩次,但效果比較好

四、Archive log空間會變大,注意事否空間足夠

五、Tablespace Temp空間也會變大,也要注事是否足夠空間





5. Index Rebuild ()操作


一、查詢前十大的index_name

SELECT * FROM (SELECT OWNER, SEGMENT_NAME INDEX_NAME, BYTES/1024/1024 MB from dba_segments  where tablespace_name='STGC5_IDX' order by BYTES DESC) WHERE  rownum<10;







註:最大為12.5G,小於目前最大26G,可以做Rebuild



產生rebuild語法:

ALTER INDEX STGC5.GROUPHIST_0E8E REBUILD TABLESPACE STGC5_IDX ONLINE;



要打十個太累,自動產生語法:

SELECT * FROM (SELECT 'ALTER INDEX ' || OWNER || '.' || SEGMENT_NAME || ' REBUILD TABLESPACE ' || TABLESPACE_NAME || ' ONLINE;'  from dba_segments  where tablespace_name='STGC5_IDX' order by BYTES DESC) WHERE rownum < 10;









寫成一個sql,自動全部執行:



[oracle@c5rptdb1 script]$ cat index_rebuild_stgc5.sql

alter session set NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS";

--設定時間格式

set linesize 110

--設定顯示寬度

set timing on

--啟動計時

spool index_20130514.log

--產生log file

select sysdate from dual;

--顯示現在時間

PROMPT rebuild 1/10...

--列印目前執行項目

ALTER INDEX STGC5.PKFWAMTCOMPTRACKHISTORY REBUILD TABLESPACE STGC5_IDX ONLINE;

PROMPT rebuild 2/10...

ALTER INDEX STGC5.GROUP_HIST_ 7F 1A REBUILD TABLESPACE STGC5_IDX ONLINE;

PROMPT rebuild 3/10...

ALTER INDEX STGC5.TXNTIMECOMMENT_ 7F 1A REBUILD TABLESPACE STGC5_IDX ONLINE;

PROMPT rebuild 4/10...

ALTER INDEX STGC5.IDANDTIMESTAMP_ 7F 1A REBUILD TABLESPACE STGC5_IDX ONLINE;

PROMPT rebuild 5/10...

ALTER INDEX STGC5.LOTOBJTXNTIME_0E94 REBUILD TABLESPACE STGC5_IDX ONLINE;

PROMPT rebuild 6/10...

ALTER INDEX STGC5.PKFWWIPHISTORY_N 2M REBUILD TABLESPACE STGC5_IDX ONLINE;

PROMPT rebuild 7/10...

ALTER INDEX STGC5.WIPTXN_0E8E REBUILD TABLESPACE STGC5_IDX ONLINE;

PROMPT rebuild 8/10...

ALTER INDEX STGC5.PKFWWIPTRANSACTION REBUILD TABLESPACE STGC5_IDX ONLINE;

PROMPT rebuild 9/10...

ALTER INDEX STGC5.PKFWWIPHISTORY REBUILD TABLESPACE STGC5_IDX ONLINE;

PROMPT rebuild 10/10...

ALTER INDEX STGC5.GROUPHIST_0E8E REBUILD TABLESPACE STGC5_IDX ONLINE;

select sysdate from dual;

--顯示結束完的時間

spool off;

--關閉

exit;

--離開



執行方式:

[oracle@c5rptdb1 script]$ sqlplus / as sysdba



SQL*Plus: Release 10.2.0 .4.0 - Production on Wed May 15 10:29:30 2013



Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.





Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0 .4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options



SQL> @index_rebuild_stgc5.sql



註:Index Rebuild完之後,再確認一下空間是否有釋放出來,若沒有,代表這一段時間很少在做delete的動作,大部份都是add資料,所以釋放的空間不大。



6. Index Rebuild ()操作


選擇Add tablespace file









填入Tablespace Name






增加DataFiles







SQL語法:

CREATE TABLESPACE STGC5_IDX_TMP DATAFILE

  '/rptdata1/c5prpt/STGC5_IDX_TMP_01.dbf' SIZE 1024M AUTOEXTEND ON NEXT 100M MAXSIZE 31744M

LOGGING

PERMANENT

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT MANUAL

FLASHBACK ON;



開始搬移資料到新的TABLESPACE,一樣選擇前十大的index_name ==>STGC5_IDX會清出空間

ALTER INDEX  STGC5.PKFWAMTCOMPTRACKHISTORY REBUILD TABLESPACE STGC5_IDX_TMP ONLINE;



做完之後再搬移到舊的TABLESPACE ==>  tablespace STGC5_IDX_temp 會變成空的

ALTER INDEX STGC5.PKFWAMTCOMPTRACKHISTORY rebuild TABLESPACE STGC5_IDX online;



再把 tablespace STGC5_IDX_temp 刪除掉

Drop tablespace STGC5_IDX_TMP including contents;



7. Add File in Tablespace




一樣是在Tablespace的位置下,新增file






照上面看到的資訊增加File

DataFile:預設OS使用的空間,增加1g的空間

Auto Extend:下次若空間滿了,增加allocate的空間,100mb

Maximum:最大的空間佔多大,使用31G( 32G會出錯)






Sql語法:

ALTER TABLESPACE STGC5_IDX ADD DATAFILE '/rptdata1/c5prpt/STGC5_IDX_05.dbf' SIZE 1024M AUTOEXTEND ON  NEXT 100M MAXSIZE 31744M;





PS.
確認Index是否正常:
詳見以下的文章:Drop Partition & MView Rsync & Index確認
http://shinchuan1.blogspot.tw/2013/11/drop-partition-mview-rsync-index.html

8. 修改備份軟體的Catalog data


因增加了Datafilerman的資料不知道要備份此檔案,需要透過RMAN的方式,連結到backup Server,做resync catalog的動作。



[oracle@c5rptdb1 script]$ rman  target  / catalog rman/rman@C5RMAN



Recovery Manager: Release 10.2.0 .4.0 - Production on Wed May 15 10:00:51 2013



Copyright (c) 1982, 2007, Oracle.  All rights reserved.



connected to target database: C5PRPT (DBID=2364754468)

connected to recovery catalog database

RMAN> resync catalog;



starting full resync of recovery catalog

full resync complete



RMAN> exit