前天有套库发现表空间分了很大但是使用的非常少，想收回多余浪费的空间放回ASM，删了两个表空间应该可以释放1T左右的空间，但是ASM DISKGROUP的free space时，并没有增长， 也就是删除的空间没有释放， 开始以为遇到了BUG， 其实原因很简单， 只是在这简单记录这个问题的提个醒。
SQL> select name,free_mb from v$asm_diskgroup; NAME FREE_MB ------------------------------ ---------- ARCHDG 1487878 DATADG 282768 OCRDG 3726 SQL> @tbs_del Please enter Name of Tablespace: idx312 Please enter Name of Tablespace will move to: idx999 no rows selected 'MOVE to another tablespace scripts:' no rows selected *********** To check default user using 'idx312' no rows selected *********** TO release dbfiles to OS FILE_NAME MB ------------------------------ ---------- +DATADG/anbob/rfk_16g_100 16383 +DATADG/anbob/rfk_32g_016 32767 +DATADG/anbob/rfk_32g_054 32767 +DATADG/anbob/rfk_32g_055 32767 +DATADG/anbob/rfk_32g_056 32767 +DATADG/anbob/rfk_32g_057 32767 +DATADG/anbob/rfk_32g_058 32767 +DATADG/anbob/rfk_32g_077 32767 +DATADG/anbob/rfk_16g_101 16383 +DATADG/anbob/rfk_32g_094 32767 +DATADG/anbob/rfk_32g_103 32767 +DATADG/anbob/rfk_16g_106 16383 +DATADG/anbob/rfk_32g_083 33270 +DATADG/anbob/rfk_32g_136 30710 +DATADG/anbob/rfk_16g_126 16630 +DATADG/anbob/rfk_32g_158 33279 +DATADG/anbob/rfk_32g_162 33279 +DATADG/anbob/rfk_32g_179 32767 +DATADG/anbob/rfk_16g_178 16639 +DATADG/anbob/rfk_16g_186 16639 +DATADG/anbob/rfk_16g_187 16639 +DATADG/stdanbob/datafile/idx312 30000 .694.855175641 22 rows selected. SQL> drop tablespace idx312; Tablespace dropped. SQL> create tablespace idx312 datafile '+datadg' size 1g autoextend on; Tablespace created. SQL> @df TABLESPACE_NAME TotalMB UsedMB FreeMB % Used Ext Used ------------------------------ ---------- ---------- ---------- ------ --- ---------------------- ... IDX312 1024 1 1023 1% YES |# | IDX313 270080 1438 268642 1% NO |# | IDX314 197113 1318 195795 1% NO |# | ... SQL> select name,free_mb from v$asm_diskgroup; NAME FREE_MB ------------------------------ ---------- ARCHDG 1487878 DATADG 312770 <<============ OCRDG 3726
Note:
你可能会注意该ASM DISKGROUP 增长了约30G， 离我们删除的几百G的空间还差的远， 等了几分钟后无果， 有时有可能因为buffer原因不会释放那么快， 回操作系统命令行sync 几次同样， 其实如果你注意了我上面的文件名称也猜到了问题原因。OMF! 这也是为什么drop tablespace 后为ASM DISKGROUP只增长了其中一个OMF datafile的大小，其它非OMF管理的datafile没有真正从ASM Diskgroup中删除, 如果drop tablespace 时带including contents and datafiles选项， datafile都会级连删除，但是又太危险，如果表空间里有对象我们没有发现这样表空间里的对象也会被强置删除。
解决方法是再手动删除datafile就可以，如下:
grid@kdfk1:/home/grid> asmcmd -p
ASMCMD [+] > ls
ARCHDG/
DATADG/
OCRDG/
ASMCMD [+] > cd DATADG
ASMCMD [+DATADG] > ls
STDSVP/
kdfkclu/
anbob/
ASMCMD [+DATADG] > cd anbob
ASMCMD [+DATADG/anbob] > lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 4096 1048576 1580928 1528060 0 1528060 0 N ARCHDG/
MOUNTED EXTERN N 512 4096 1048576 11856960 311744 0 311744 0 N DATADG/
MOUNTED EXTERN N 512 4096 1048576 4124 3726 0 3726 0 Y OCRDG/
ASMCMD [+DATADG/anbob] > lsop
Group_Name Dsk_Num State Power
ASMCMD [+DATADG/anbob] > ls -l rfk_16g_100
Type Redund Striped Time Sys Name
N rfk_16g_100 => +DATADG/STDSVP/DATAFILE/IDX312.445.839927477
grid@kdfk1:/home/grid> asmcmd lsof|grep rfk_16g_100
Note:
==========================================
if the file in database will be show :
grid@kdfk1:/home/grid> asmcmd lsof|grep rfk_32g_182
stdanbob anbob1 +datadg/anbob/rfk_32g_182
==================================================
ASMCMD [+datadg/anbob] > rm rfk_16g_100 SQL> select name,free_mb from v$asm_diskgroup; NAME FREE_MB ------------------------------ ---------- ARCHDG 1515838 DATADG 328129 <<< had increase 16G OCRDG 3726 ... 重复 SQL> select name,free_mb from v$asm_diskgroup; NAME FREE_MB ------------------------------ ---------- ARCHDG 1483177 DATADG 1185531 <<< OCRDG 3726
找回了已删除的1T的空间
== tbs_del.sql ==
undefine tbsname
col file_name for a30
prompt
column uservar new_value tbsname_new noprint
select 'USERS' uservar from dual;
accept tbsname prompt 'Please enter Name of Tablespace: '
accept tbsname_new prompt 'Please enter Name of Tablespace will move to: '
select owner,segment_name,segment_type from dba_segments where tablespace_name=upper('&&tbsname');
prompt 'MOVE to another tablespace scripts:'
select decode(segment_type,'INDEX','alter index ','TABLE','alter table ','INDEX PARTITION','alter index ')||owner||'.'||segment_name
|| decode(segment_type,'INDEX',' rebuild ','TABLE',' move ','INDEX PARTITION',' rebuild partition '||partition_name)|| ' tablespace &&tbsname_new;' scripts
from dba_segments where tablespace_name=upper('&&tbsname');
prompt ***********
prompt To check default user using '&&tbsname'
select 'alter user '||username||' default tablespace users; ' from dba_users where default_tablespace=upper('&&tbsname');
prompt ***********
prompt TO release dbfiles to OS
select file_name,bytes/1024/1024 mb from dba_data_files where tablespace_name=upper('&&tbsname');
(Doc ID 552082.1) 下面的脚本是列出在ASM 中但是没有在数据库中的脚本，Note: 但是有包含了spfile , controlfile, datafile not open in DB, controlfile， 所以手动清理时要注意
set pagesize 0
set linesize 200
col full_alias_path format a80
/*+ ----------------------------------------------------------------
Query will return all the files stored on ASM but not currenlty
opened by any database client of the diskgroups
ordered by group number, file type
---------------------------------------------------------------*/
select * from (
/*+ -----------------------------------------------------------------
1st branch returns all the files stored on ASM
-----------------------------------------------------------------*/
select x.gnum,x.filnum,x.full_alias_path,f.ftype from (
SELECT gnum,filnum,concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path
FROM (SELECT g.name gname, a.parent_index pindex, a.name aname,
a.reference_index rindex,a.group_number gnum,a.file_number filnum
FROM v$asm_alias a, v$asm_diskgroup g
WHERE a.group_number = g.group_number)
START WITH (mod(pindex, power(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex) x,
(select group_number gnum,file_number filnum, type ftype from v$asm_file order by group_number,file_number) f
where x.filnum != 4294967295
and x.gnum=f.gnum and x.filnum=f.filnum
MINUS
/*+ --------------------------------------------------------------
2nd branch returns all the files stored on ASM
and currently opened by any database client of the diskgroups
-----------------------------------------------------------------*/
select x.gnum,x.filnum,x.full_alias_path,f.ftype
from ( select id1 gnum,id2 filnum from v$lock where type='FA' and (lmode=4 or lmode=2)) l,
(
SELECT gnum,filnum,concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path
FROM (SELECT g.name gname, a.parent_index pindex, a.name aname,
a.reference_index rindex,a.group_number gnum,a.file_number filnum
FROM v$asm_alias a, v$asm_diskgroup g
WHERE a.group_number = g.group_number)
START WITH (mod(pindex, power(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex
) x,
(select group_number gnum,file_number filnum, type ftype from v$asm_file order by group_number,file_number) f
where x.filnum != 4294967295 and
x.gnum=l.gnum
and x.filnum=l.filnum
and x.gnum=f.gnum and x.filnum=f.filnum) q
order by q.gnum,q.ftype
;
---- enjoy it
今天是2016年的第一天， 祝各位元旦快乐！