Oracle一次縮小表空間的處理過程

lhrbest發表於2016-09-06
Oracle


Oracle一次縮小表空間的處理過程

1  BLOG文件結構圖

wps7D5D.tmp 

 

 

2  前言部分

2.1  導讀和注意事項

各位技術愛好者，看完本文後，你可以掌握如下的技能，也可以學到一些其它你所不知道的知識，~O(∩_∩)O~：

① 收縮表空間的幾種辦法

② 表空間大小查詢

③ AIX下查詢磁碟空間大小的shell指令碼

④ 刪除資料檔案的正確方法

⑤ ORA-03262處理

⑥ 縮小資料檔案

⑦ su - grid asmcmd lsdg的使用

⑧ 其他常用命令

  Tips：

① 本文在ITpub（http://blog.itpub.net/26736162）、部落格園(http://www.cnblogs.com/lhrbest)和微信公眾號（xiaomaimiaolhr）有同步更新

② 文章中用到的所有程式碼，相關軟體，相關資料請前往小麥苗的雲盤下載（http://blog.itpub.net/26736162/viewspace-1624453/）

③ 若文章程式碼格式有錯亂，推薦使用搜狗、360或QQ瀏覽器，也可以下載pdf格式的文件來檢視，pdf文件下載地址：http://blog.itpub.net/26736162/viewspace-1624453/，另外itpub格式顯示有問題，可以去部落格園地址閱讀

④ 本篇BLOG中命令的輸出部分需要特別關注的地方我都用灰色背景和粉紅色字型來表示，比如下邊的例子中，thread 1的最大歸檔日誌號為33，thread 2的最大歸檔日誌號為43是需要特別關注的地方；而命令一般使用黃色背景和紅色字型標注；對程式碼或程式碼輸出部分的注釋一般採用藍色字型表示。

  List of Archived Logs in backup set 11

  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time

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

  1    32      1621589    2015-05-29 11:09:52 1625242    2015-05-29 11:15:48

  1    33      1625242    2015-05-29 11:15:48 1625293    2015-05-29 11:15:58

  2    42      1613951    2015-05-29 10:41:18 1625245    2015-05-29 11:15:49

  2    43      1625245    2015-05-29 11:15:49 1625253    2015-05-29 11:15:53

 

[ZHLHRDB1:root]:/>lsvg -o

T_XDESK_APP1_vg

rootvg

[ZHLHRDB1:root]:/>

00:27:22 SQL> alter tablespace idxtbs read write;

 

====》2097152*512/1024/1024/1024=1G 

 

本文如有錯誤或不完善的地方請大家多多指正，ITPUB留言或QQ皆可，您的批評指正是我寫作的最大動力。

 

3  環境介紹

 

專案

source db

db 型別

RAC

db version

11.2.0.3.0

db 儲存

ASM

OS版本及kernel版本

AIX 64位 7.1.0.0

 

4  處理過程

一個同事過來說，表空間不夠了，讓我幫忙看看，好吧，首先看一下表空間的大小，SQL語句如下：

WITH wt1 AS

 (SELECT ts.TABLESPACE_NAME,

         df.all_bytes,

         decode(df.TYPE,

                'D',

                nvl(fs.FREESIZ, 0),

                'T',

                df.all_bytes - nvl(fs.FREESIZ, 0)) FREESIZ,

         df.MAXSIZ,

         ts.BLOCK_SIZE,

         ts.LOGGING,

         ts.FORCE_LOGGING,

         ts.CONTENTS,

         ts.EXTENT_MANAGEMENT,

         ts.SEGMENT_SPACE_MANAGEMENT,

         ts.RETENTION,

         ts.DEF_TAB_COMPRESSION,

         df.ts_df_count

  FROM   dba_tablespaces ts, 

         (SELECT 'D' TYPE,

                 TABLESPACE_NAME,

                 COUNT(*) ts_df_count,

                 SUM(BYTES) all_bytes,

                 SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES)) MAXSIZ

          FROM   dba_data_files d

          GROUP  BY TABLESPACE_NAME

          UNION ALL 

          SELECT 'T',

                 TABLESPACE_NAME,

                 COUNT(*) ts_df_count,

                 SUM(BYTES) all_bytes,

                 SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES)) 

          FROM   dba_temp_files d

          GROUP  BY TABLESPACE_NAME) df, 

         (SELECT TABLESPACE_NAME,

                 SUM(BYTES) FREESIZ

          FROM   dba_free_space

          GROUP  BY TABLESPACE_NAME

          UNION ALL

          SELECT tablespace_name,

                 SUM(d.BLOCK_SIZE * a.BLOCKS) bytes

          FROM   gv$sort_usage   a,

                 dba_tablespaces d

          WHERE  a.tablespace = d.tablespace_name

          GROUP  BY tablespace_name) fs

  WHERE  ts.TABLESPACE_NAME = df.TABLESPACE_NAME

  AND    ts.TABLESPACE_NAME = fs.TABLESPACE_NAME(+))

SELECT (SELECT A.TS#

        FROM   V$TABLESPACE A

        WHERE  A.NAME = UPPER(t.TABLESPACE_NAME)) TS#,

       t.TABLESPACE_NAME TS_Name,

       round(t.all_bytes / 1024 / 1024) ts_size_M,

       round(t.freesiz / 1024 / 1024) Free_Size_M,

       round((t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M,

       round((t.all_bytes - t.FREESIZ) * 100 / t.all_bytes, 3) Used_per,

       round(MAXSIZ / 1024 / 1024/1024, 3) MAX_Size_g,

       round(decode(MAXSIZ, 0, to_number(NULL), (t.all_bytes - FREESIZ)) * 100 /

             MAXSIZ,

             3) USED_per_MAX,

       round(t.BLOCK_SIZE) BLOCK_SIZE,

       t.LOGGING,

       t.ts_df_count

FROM   wt1 t

UNION ALL

SELECT to_number('') TS#,

       'ALL TS:' TS_Name,

       round(SUM(t.all_bytes) / 1024 / 1024, 3) ts_size_M,

       round(SUM(t.freesiz) / 1024 / 1024) Free_Size_m,

       round(SUM(t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M,

       round(SUM(t.all_bytes - t.FREESIZ) * 100 / SUM(t.all_bytes), 3) Used_per,

       round(SUM(MAXSIZ) / 1024 / 1024/1024) MAX_Size,

       to_number('') "USED,% of MAX Size",

       to_number('') BLOCK_SIZE,

       '' LOGGING,

       to_number('') ts_df_count

FROM   wt1 t

order by TS#

;

wps7D7D.tmp 

TPCCIND表空間佔用了99%了，剩下378M，不夠用了，好吧，看看系統還有剩餘的空間沒有:

wps7D7E.tmp 

這個截圖的shell指令碼如下，可以在AIX環境下檢視磁碟的使用情況，作者曾花了接近2天的時間寫的（主要是不熟悉AWK，汗顏，，，）：

[ZFLHRADB1:root]:/>more disk*

if [ 1 = 1 ] ;then

  sum=0;asmnum=0

  awk  'BEGIN {printf "------------------------------------------------------------------------------------------------------------------------------\n"; printf "%-43s %-18s %-14s %-8s %-15s %-14s\n","|                 disk              ","|        PVID      ","|  no_reserve ","| size(G)","|    disktype     ","|  disk_storage  |"; printf "------------------------------------------------------------------------------------------------------------------------------\n";}'

  for diskname in `lspv | grep disk | awk '{print $1}'`;do

    mydiskname=`ls -l /dev/rdiskname|grep?w/dev/rdiskname|grep?w/dev/rdiskname| cut -c 1-12,17-38,59-76`

    mydiskpvid=`lquerypv -H /dev/$diskname | cut -c 1-16`

    if  [ "${mydiskpvid}" = "" ];then mydiskpvid="0000000000000000" ;  fi 2>/dev/null

    mydiskreserve=`lsattr -El $diskname | grep -i reserve_policy | cut -c 17-30`

    mydisksize=`bootinfo -s diskname2>/dev/null‘;let"mydisksize1=diskname2>/dev/null‘;let"mydisksize1=mydisksize/1024" 2>/dev/null

    mydiskvg=`lspv | grep -w diskname | awk '{print3}'`

    mydiskasmgroup=`lquerypv -h /dev/rdiskname|head -n 7|tail -n 1|awk -F ' ' '{printNF}'|sed -e 's/\.//g' -e 's/\|//g' | awk '{ if (1!="")printf"+"1!="")printf"+"1 ; else print "NULL"}'`

    mydiskflag=`lquerypv -h /dev/r$diskname 2>/dev/null|grep -i orcldisk|wc -l`

    if  [ mydisksize?lt1000];thenmydisktype="HeadDisk";elif[mydisksize?lt1000];thenmydisktype="HeadDisk";elif[{mydisksize} -gt 1000 -a mydiskflag?gt0];thenmydisktype="ASM:"mydiskflag?gt0];thenmydisktype="ASM:"mydiskasmgroup; elif  [ mydisksize?gt1000?amydisksize?gt1000?a{mydiskflag} -eq 0 -a mydiskvg!="None"];thenmydisktype=mydiskvg!="None"];thenmydisktype=mydiskvg ; else  mydisktype="Not_Used"; fi 2>/dev/null

    mydiskpath=`lspath -l diskname2>/dev/null|head?1|awk′print$NF′|sed"s/.diskname2>/dev/null|head?1|awk′print$NF′|sed"s/.//"`

    mydiskstring=`odmget -q attribute="unique_id" CuAt|egrep "name|value"|paste - -|tr  '\t' ' '|grep -w ${diskname}|sed 's/\"//g'`

    mydiskstorage=`echo {mydiskstring} 2> /dev/null|awk '{ if(NF ~ /EMC/) {print "EMC"} else if (NF /NETAPP/)print"NETAPP"elseif(NF /NETAPP/)print"NETAPP"elseif(NF ~ /HITACHI/) {print "HDS"}}'`

    mydiskdepth=`lsattr -El {diskname}|grep queue_depth|awk '{print2}'`

    mydiskstorage1=mydiskstorage","mydiskstorage","mydiskpath","$mydiskdepth

    [ mydisksize1?gt1?amydisksize1?gt1?a{mydiskflag} -gt 0 ] && { (( sum=sum+mydisksize1));((asmnum=mydisksize1));((asmnum=asmnum+1 )) ;}

    echo  "mydiskname""mydiskname""mydiskpvid"  "mydiskreserve""mydiskreserve""{mydisksize1%.*}"  "mydisktype""mydisktype""mydiskstorage1" | awk '{printf "| %-10s %-6s %-8s %-14s | %-17s | %-12s | %-8s|  %-15s | %-14s |\n",1,1,2,3,3,4,5,5,6,7,7,8,$9}'

  done   

  awk  'BEGIN {printf "------------------------------------------------------------------------------------------------------------------------------\n";}'

  echo  "ASMDISK_TOTAL:asmnum""TOTALSIZE(GB):asmnum""TOTALSIZE(GB):sum" |awk '{printf "| %-20s %-101s |\n", 1,1,2}'

  awk  'BEGIN {printf "------------------------------------------------------------------------------------------------------------------------------\n";}'

fi

if [ 1 = 1 ] ;then 
  sum=0;asmnum=0 
  awk  'BEGIN {printf "------------------------------------------------------------------------------------------------------------------------------\n"; printf "%-43s %-18s %-14s %-8s %-15s %-14s\n","|                 disk              ","|        PVID      ","|  no_reserve ","| size(G)","|    disktype     ","|  disk_storage  |"; printf "------------------------------------------------------------------------------------------------------------------------------\n";}' 
  for diskname in `lspv | grep disk | awk '{print 1}'`;do           mydiskname=`ls -l /dev/rdiskname |grep -w /dev/rdiskname|cut?c1?12,17?38,59?76‘mydiskpvid=‘lquerypv?H/dev/diskname|cut?c1?12,17?38,59?76‘mydiskpvid=‘lquerypv?H/dev/diskname | cut -c 1-16` 
    if  [ "mydiskpvid"=""];thenmydiskpvid="0000000000000000";fi2>/dev/nullmydiskreserve=‘lsattr?Elmydiskpvid"=""];thenmydiskpvid="0000000000000000";fi2>/dev/nullmydiskreserve=‘lsattr?Eldiskname | grep -i reserve_policy | cut -c 17-30` 
    mydisksize=`bootinfo -s diskname2>/dev/null‘;let"mydisksize1=diskname2>/dev/null‘;let"mydisksize1=mydisksize/1024" 2>/dev/null 
    mydiskvg=`lspv | grep -w diskname|awk′print$3′‘mydiskasmgroup=‘lquerypv?h/dev/rdiskname|awk′print$3′‘mydiskasmgroup=‘lquerypv?h/dev/rdiskname|head -n 7|tail -n 1|awk -F ' ' '{print NF}'|sed -e 's/\.//g' -e 's/\|//g' | awk '{ if ($1 != "") printf "+"$1 ; else print "NULL"}'`           mydiskflag=`lquerypv -h /dev/rdiskname 2>/dev/null|grep -i orcldisk|wc -l` 
    if  [ mydisksize?lt1000];thenmydisktype="HeadDisk";elif[mydisksize?lt1000];thenmydisktype="HeadDisk";elif[{mydisksize} -gt 1000 -a mydiskflag?gt0];thenmydisktype="ASM:"mydiskflag?gt0];thenmydisktype="ASM:"mydiskasmgroup; elif  [ mydisksize?gt1000?amydisksize?gt1000?a{mydiskflag} -eq 0 -a mydiskvg!="None"];thenmydisktype=mydiskvg!="None"];thenmydisktype=mydiskvg ; else  mydisktype="Not_Used"; fi 2>/dev/null 
    mydiskpath=`lspath -l diskname2>/dev/null|head?1|awk′print$NF′|sed"s/.diskname2>/dev/null|head?1|awk′print$NF′|sed"s/.//"` 
    mydiskstring=`odmget -q attribute="unique_id" CuAt|egrep "name|value"|paste - -|tr  '\t' ' '|grep -w diskname|sed′s/\"//g′‘mydiskstorage=‘echodiskname|sed′s/\"//g′‘mydiskstorage=‘echo{mydiskstring} 2> /dev/null|awk '{ if(NF /EMC/)print"EMC"elseif(NF /EMC/)print"EMC"elseif(NF ~ /NETAPP/) {print "NETAPP"} else if(NF ~ /HITACHI/) {print "HDS"}}'`           mydiskdepth=`lsattr -El{diskname}|grep queue_depth|awk '{print 2}'`           mydiskstorage1=mydiskstorage","mydiskpath","mydiskpath","mydiskdepth 
    [ mydisksize1?gt1?amydisksize1?gt1?a{mydiskflag} -gt 0 ] && { (( sum=sum+mydisksize1));((asmnum=mydisksize1));((asmnum=asmnum+1 )) ;} 
    echo  "mydiskname""mydiskname""mydiskpvid"  "mydiskreserve""mydiskreserve""{mydisksize1%.*}"  "mydisktype""mydisktype""mydiskstorage1" | awk '{printf "| %-10s %-6s %-8s %-14s | %-17s | %-12s | %-8s|  %-15s | %-14s |\n",1,1,2,3,3,4,5,5,6,7,7,8,9}'         done       awk  'BEGIN {printf "------------------------------------------------------------------------------------------------------------------------------\n";}'         echo  "ASMDISK_TOTAL:asmnum" "TOTAL_SIZE(GB):sum" |awk '{printf "| %-20s %-101s |\n",1,$2}' 
  awk  'BEGIN {printf "------------------------------------------------------------------------------------------------------------------------------\n";}' 
fi

 

從截圖可以看出分配給資料庫的一共8塊磁碟，看看資料庫中有多少：

SELECT * FROM v$asm_disk;

wps7D7F.tmp 

看了8塊磁碟分配完了，而系統剩餘空間還有769M：wps7D80.tmp

那就只能隔東牆補西牆，看到系統裡TPCCHIS表空間剩下270G，經開發確認，可以縮小一些空間：

wps7D81.tmp 

 

而縮小表空間有3種辦法：

1、ALTER TABLESPACE test SHRINK SPACE KEEP 20M; --主要針對臨時表空間

2、刪除資料檔案

3、縮小資料檔案的大小

 

我們試試第一種：ALTER TABLESPACE TPCCHIS SHRINK SPACE KEEP 100G;

ORA-12916: cannot shrink permanent or dictionary managed tablespace

 

wps7D92.tmp 

報錯了，永久表空間或是字典管理的不能SHRINK，只能針對temp表空間進行SHRINK SPACE。

 

試試第二種辦法：刪除資料檔案

 

SELECT * FROM dba_data_files d WHERE d.tablespace_name='TPCCHIS';

wps7D93.tmp 

SELECT D.HEADER_FILE, COUNT(1)

  FROM DBA_SEGMENTS D

 WHERE D.TABLESPACE_NAME = 'TPCCHIS'

 GROUP BY D.HEADER_FILE; 

wps7D94.tmp 

所以，表都在23號檔案上，其它檔案應該可以刪掉的。

ALTER TABLESPACE TPCCHIS DROP DATAFILE 13;

結果報錯了：ORA-03262: the file is non-empty

[ZFLHRADB1:oracle]:/oracle>oerr ora 03262

03262, 00000, "the file is non-empty"

// *Cause:  Trying to drop a non-empty datafile

// *Action: Cannot drop a non empty datafile

 

wps7D95.tmp 

參考mos文章： 
Unable to Drop a Datafile From the Tablespace Using Alter Tablespace Command (文件 ID 1050261.1)

違反下列任何一個條件，該datafile均不能被drop

1）必須為空，否則會報：ORA-03262: the file is non-empty。值得注意的是，non-empty的含義是有extent被分配給了table，而不是該table中有無rows，此時若是使用drop table xxx是不行的，必須使用 drop table xxx purge;或者在已經使用了drop table xxx的情況下，再使用purge table “xxx表在回收站中的名稱”來purge該表，否則空間還是不釋放，datafile依然drop不掉。

2）不能是所屬表空間的第一個file

  以上兩者可以通過drop tablespace來達到目的。

3）不能在read-only表空間中。

4）不能被offline，否則會報：ORA-03264: cannot drop offline datafile of locally managed tablespace

 針對該報錯，解決方法為：

[oracle@rhel6 u01]$ oerr ora 3264 

03264, 00000, "cannot drop offline datafile of locally managed tablespace" 

// *Cause:  Trying to drop offline datafile in lmts 

// *Action: Try to drop file afetr making it online 

5） Cannot be a datafile that is part of the system tablespace, even if it is not the first datafile of the system tablespace --該條來源於How to Drop a Datafile From a Tablespace (文件 ID 111316.1)

試了試其它檔案還是不能刪除，好吧，這個必須把表移動到其它表空間後才可以，算了，比較麻煩。

試試第3種辦法：縮小資料檔案的大小

 

SELECT 'alter database datafile ' || D.FILE_ID || ' resize 1G;'

  FROM DBA_DATA_FILES D

 WHERE D.TABLESPACE_NAME = 'TPCCHIS';

wps7D96.tmp 

按照開發的要求，縮減100G即可，所以，我們執行3個即可，每個檔案大小為30g，

alter database datafile 23 resize 1G;

alter database datafile 25 resize 1G;

alter database datafile 27 resize 1G;

執行完成之後，表空間大小：

wps7DA6.tmp 

空間多出100G：

wps7DA7.tmp 

 

我們可以給表空間TPCCIND加空間了：

 ALTER TABLESPACE TPCCIND ADD DATAFILE   '+DATA1' SIZE 30G;

 ALTER TABLESPACE TPCCIND ADD DATAFILE   '+DATA1' SIZE 30G;

 ALTER TABLESPACE TPCCIND ADD DATAFILE   '+DATA1' SIZE 30G;

增加完成後檢視錶空間大小：

wps7DA8.tmp 

好吧，可以了。

  About Me

..........................................................................................................................................................................................................                        

●  本文作者：小麥苗，只專注於資料庫的技術，更注重技術的運用

● 本文在itpub（http://blog.itpub.net/26736162）、部落格園(http://www.cnblogs.com/lhrbest)和個人微信公眾號（xiaomaimiaolhr）上有同步更新，推薦pdf檔案閱讀

● QQ群：230161599 微信群：私聊

● 本文itpub地址：http://blog.itpub.net/26736162/viewspace-2124606/ 部落格園地址：http://www.cnblogs.com/lhrbest/articles/5846969.html

● 本文pdf版：http://yunpan.cn/cdEQedhCs2kFz （提取碼：ed9b）

● 小麥苗分享的其它資料：http://blog.itpub.net/26736162/viewspace-1624453/

●  聯絡我請加QQ好友(642808185)，註明新增緣由

● 於 2016-09-06 09:00~2016-09-06 20:00 在中行完成

● 【版權所有，文章允許轉載，但須以連結方式註明源地址，否則追究法律責任】

..........................................................................................................................................................................................................

長按識別二維碼或微信客戶端掃描下邊的二維碼來關注小麥苗的微信公眾號：xiaomaimiaolhr,學習最實用的資料庫技術。

wps7DA9.tmp

 

 

 

 


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

相關文章