How To Remove An Empty ASM System Directory

Sometimes it can happen that the ASM contains empty system directories whichcannot be removed. According to Note 3751057.8(ALTER DISKGROUP DROP DIRECTORY FORCE removes system aliases) the system generated directories should be removed automatically as soon as all it's files are removed. However this does not happen sometimes and a manualdirectory removal will fail :

SQL>setpages2000
SQL>SELECTlevel,dir,sys,substr(lpad('',2*level,'')||CONCAT('+'||gname,
SYS_CONNECT_BY_PATH(aname,'/')),1,60)full_path
FROM(SELECTg.namegname,a.parent_indexpindex,a.nameaname,
a.reference_indexrindex,a.ALIAS_DIRECTORYdir,a.SYSTEM_CREATEDsys
FROMv$asm_aliasa,v$asm_diskgroupg
WHEREa.group_number=g.group_number)
STARTWITH(MOD(pindex,POWER(2,24)))=0
CONNECTBYPRIORrindex=pindex
ORDERBYrtrim(ltrim(full_path))desc,levelasc;

3NY+DATA/UDWH/DATAFILE/ADASTRA.376.624105133
2YY+DATA/UDWH/DATAFILE
3NY+DATA/UDWH/CONTROLFILE/Current.486.624103637
3NY+DATA/UDWH/CONTROLFILE/Current.479.624103637
3NY+DATA/UDWH/CONTROLFILE/Current.434.624103637
2YY+DATA/UDWH/CONTROLFILE
1YY+DATA/UDWH
1YY+DATA/TDWH
2NN+DATA/IDWH/spfileIDWH.ora
2NN+DATA/IDWH/control03.ctl
2NN+DATA/IDWH/control02.ctl
2NN+DATA/IDWH/control01.ctl

The TDWH directory from the above list is empty but cannot be removed :

ASMCMD>cdtdwh
ASMCMD>ls
ASMCMD>cd..
ASMCMD>rm-rftdwh
ORA-15032:notallalterationsperformed
ORA-15177:cannotoperateonsystemaliases(DBDERROR:OCIStmtExecute)

alterdiskgroupDATAdropdirectory'+DATA/TDWH'FORCE;
ORA-15032:notallalterationsperformed
ORA-15177:cannotoperateonsystemaliases(DBDERROR:OCIStmtExecute)

A solution to remove these directories is to recreate the ASM diskgroups but this is not something one would do very often especially when the ASM is used by production databases.

Solution

Either:
Create a new database with the DBCA having the same name as the old directory and subsequently drop the database with the DBCA.

Or:
Create a dummy tablespace having a datafile within the directory to be dropped, drop the tablespace and then remove the datafile using the ASMCMD tool. The old directory will be removed automatically.

There is a scenario when there are left behind directories after the database is dropped using RMAN's command "drop database including backups [noprompt];". When one falls into this there is no way out but to recreate the diskgroups. This problem is being investigated in Bug 6606689. To avoid this issue one should run the following two commands instead of "DROP DATABASE INCLUDING BACKUPS;":

RMAN> delete backupset;

RMAN> drop database;

From Oracle

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

Blog： http://blog.youkuaiyun.com/tianlesoftware

Email: dvd.dba@gmail.com

DBA1 群：62697716(满); DBA2 群：62697977(满) DBA3 群：62697850(满)

DBA 超级群：63306533(满); DBA4 群： 83829929 DBA5群： 142216823

聊天 群：40132017 聊天2群：69087192

--加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请