首页 » ORACLE 9i-23ai » Why do drop tablespace completes the space is not available as free yet in ASM Diskgroup?（删除tablesapce后, ASM空闲空间没变）

Why do drop tablespace completes the space is not available as free yet in ASM Diskgroup?（删除tablesapce后, ASM空闲空间没变）

  • 2015/11/23
  • ORACLE 9i-23ai
  • 391 views
  • Why do drop tablespace completes the space is not available as free yet in ASM Diskgroup?（删除tablesapce后, ASM空闲空间没变）已关闭评论

前天有套库发现表空间分了很大但是使用的非常少，想收回多余浪费的空间放回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年的第一天， 祝各位元旦快乐！

Related Posts:

打赏

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：