This is a very annoying task when you have to purge old and unused files from ASM. Of course you can try to run the script provided by Oracle support in the Doc.Id 552082.1 , but the query is deprecated for post 12c grid infra. There is another note for post 12c ( 2228573.1 ) but here we will try another method.
In our databases we could have some files with OMF and some files with classical naming convention. So the first step is to list all files in disk groups, but when an asm alias ( due to the classical naming convention ) is present, display only this alias, not the target file.
Some tips used in this script :
- file_number 4294967295 is a special number that we have to exclude
- alias_index < 50 are useds for DB root asm directories
Reduce the list with all files used by the database ( from v$datafile, v$tempfile, v$controlfile, …) and the result is a file list that you potentially could purge.
Of course this doesn’t cover all file type (spfiles, password files, broker files, …) but this is a good starting point and could help you to free some space on your ASM disk groups.
Disclamer : Before apply the result of the script, double check if all files that you will delete are not used by any databases.
The entire risk arising out of the use of the script remains with you.
Use at your own risk . You are warned !
SET VERIFY OFF
SET LINESIZE 200
SET SERVEROUTPUT ON
SET PAGESIZE 50000
SET TRIMSPOOL ON
BEGIN
FOR c IN (SELECT name Diskgroup
FROM V$ASM_DISKGROUP)
LOOP
FOR l
IN (SELECT 'rm ' || files files
FROM
(SELECT '+' || c.Diskgroup || files files, TYPE
FROM ( SELECT UPPER
(
SYS_CONNECT_BY_PATH (aa.name, '/')
)
files
, aa.reference_index
, b.TYPE
FROM (SELECT file_number
, alias_directory
, name
, reference_index
, parent_index
FROM v$asm_alias
where ALIAS_INDEX NOT IN (
select ALIAS_INDEX from v$asm_alias
where file_number IN (
select file_number from v$asm_alias
where SYSTEM_CREATED = 'N'
and group_number =
(SELECT group_number
FROM v$asm_diskgroup
WHERE name = c.Diskgroup
)
)
and SYSTEM_CREATED = 'Y'
and group_number =
(SELECT group_number
FROM v$asm_diskgroup
WHERE name = c.Diskgroup
)
) and group_number =
(SELECT group_number
FROM v$asm_diskgroup
WHERE name = c.Diskgroup
)
) aa
, (SELECT parent_index
FROM (SELECT distinct parent_index
FROM v$asm_alias
WHERE group_number =
(SELECT group_number
FROM v$asm_diskgroup
WHERE name =
c.Diskgroup)
AND alias_index < 50)) a
, (SELECT file_number, TYPE
FROM (SELECT file_number, TYPE
FROM v$asm_file
WHERE group_number =
(SELECT group_number
FROM v$asm_diskgroup
WHERE name =
c.Diskgroup)))
b
WHERE aa.file_number = b.file_number(+)
AND aa.alias_directory = 'N'
AND aa.file_number != 4294967295
AND b.TYPE IN
('DATAFILE'
, 'ONLINELOG'
, 'CONTROLFILE'
, 'TEMPFILE')
START WITH aa.PARENT_INDEX = a.parent_index
CONNECT BY PRIOR aa.reference_index =
aa.parent_index)
WHERE SUBSTR
(
files
, INSTR (files, '/', 1, 1)
, INSTR (files, '/', 1, 2)
- INSTR (files, '/', 1, 1)
+ 1
) =
(SELECT dbname
FROM (SELECT '/'
|| UPPER (db_unique_name)
|| '/'
dbname
FROM v$database))
MINUS
(SELECT UPPER (name) files, 'DATAFILE' TYPE
FROM v$datafile
UNION ALL
SELECT UPPER (name) files, 'TEMPFILE' TYPE
FROM v$tempfile
UNION ALL
SELECT UPPER (name) files, 'CONTROLFILE' TYPE
FROM v$controlfile
WHERE name LIKE '+' || c.Diskgroup || '%'
UNION ALL
SELECT UPPER (name), 'CONTROLFILE' TYPE
FROM v$datafile_copy
WHERE deleted = 'NO'
UNION ALL
SELECT UPPER (MEMBER) files, 'ONLINELOG' TYPE
FROM v$logfile
WHERE MEMBER LIKE '+' || c.Diskgroup || '%')))
LOOP
DBMS_OUTPUT.put_line (l.files);
END LOOP;
END LOOP;
END;
/
This script is design to be run from the client database (not from the asm instance). Here is a result :
SQL*Plus: Release 12.1.0.2.0 Production on Fri May 31 15:44:41 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options SYS@SETRADEV # @asm_orphaned_files.sql rm +DATAC02/SETRADEV/DATAFILE/SETRA.317.966277287 rm +DATAC02/SETRADEV/DATAFILE/SETRA.409.966954235 rm +DATAC02/SETRADEV/DATAFILE/SETRA.422.968237887 rm +DATAC02/SETRADEV/DATAFILE/SETRA.436.981892603 rm +DATAC02/SETRADEV/DATAFILE/SETRA.442.981907497 rm +DATAC02/SETRADEV/DATAFILE/SYSAUX.389.966247761 rm +DATAC02/SETRADEV/DATAFILE/SYSAUX.399.966952889 rm +DATAC02/SETRADEV/DATAFILE/SYSAUX.420.968237827 rm +DATAC02/SETRADEV/DATAFILE/SYSAUX.434.981892565 rm +DATAC02/SETRADEV/DATAFILE/SYSAUX.440.981907449 rm +DATAC02/SETRADEV/DATAFILE/SYSTEM.386.966247749 rm +DATAC02/SETRADEV/DATAFILE/SYSTEM.408.966953793 rm +DATAC02/SETRADEV/DATAFILE/SYSTEM.421.968237871 rm +DATAC02/SETRADEV/DATAFILE/SYSTEM.435.981892587 rm +DATAC02/SETRADEV/DATAFILE/SYSTEM.441.981907489 rm +DATAC02/SETRADEV/DATAFILE/UNDOTBS1.387.966247751 rm +DATAC02/SETRADEV/DATAFILE/UNDOTBS1.404.966963411 rm +DATAC02/SETRADEV/DATAFILE/UNDOTBS1.410.968237591 rm +DATAC02/SETRADEV/DATAFILE/UNDOTBS1.430.981907283 rm +DATAC02/SETRADEV/DATAFILE/UNDOTBS1.433.981892369 rm +DATAC02/SETRADEV/DATAFILE/USERS.390.966247767 rm +DATAC02/SETRADEV/DATAFILE/USERS.411.966963413 rm +DATAC02/SETRADEV/DATAFILE/USERS.423.968237891 rm +DATAC02/SETRADEV/DATAFILE/USERS.437.981892603 rm +DATAC02/SETRADEV/DATAFILE/USERS.443.981907499 rm +DATAC02/SETRADEV/ONLINELOG/GROUP_1.297.966977863 rm +DATAC02/SETRADEV/ONLINELOG/GROUP_1.391.966247949 rm +DATAC02/SETRADEV/ONLINELOG/GROUP_2.392.966247951 rm +DATAC02/SETRADEV/ONLINELOG/GROUP_2.413.966977867 rm +DATAC02/SETRADEV/ONLINELOG/GROUP_3.393.966247953 rm +DATAC02/SETRADEV/ONLINELOG/GROUP_3.414.966977869 rm +DATAC02/SETRADEV/ONLINELOG/GROUP_4.394.966247957 rm +DATAC02/SETRADEV/ONLINELOG/GROUP_4.415.966977873 rm +DATAC02/SETRADEV/SETRAREF01.DBF rm +DATAC02/SETRADEV/SETRAREF02.DBF rm +DATAC02/SETRADEV/SETRAREF03.DBF rm +DATAC02/SETRADEV/TEMPFILE/TEMP.395.966247963 rm +DATAC02/SETRADEV/TEMPFILE/TEMP.416.966977879 rm +DATAC02/SETRADEV/TEMPFILE/TEMP.428.968238205 rm +DATAC02/SETRADEV/TEMPFILE/TEMP.438.981892677 rm +DATAC02/SETRADEV/TEMPFILE/TEMP.444.981907569 rm +RECOC02/SETRADEV/ONLINELOG/GROUP_1.395.966247949 rm +RECOC02/SETRADEV/ONLINELOG/GROUP_1.675.966977865 rm +RECOC02/SETRADEV/ONLINELOG/GROUP_2.396.966247953 rm +RECOC02/SETRADEV/ONLINELOG/GROUP_2.676.966977867 rm +RECOC02/SETRADEV/ONLINELOG/GROUP_3.397.966247955 rm +RECOC02/SETRADEV/ONLINELOG/GROUP_3.677.966977871 rm +RECOC02/SETRADEV/ONLINELOG/GROUP_4.398.966247959 rm +RECOC02/SETRADEV/ONLINELOG/GROUP_4.678.966977875 PL/SQL procedure successfully completed. SYS@SETRADEV # exit