How to find and remove orphaned ASM files

How to find and remove orphaned ASM files

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

Partager cet article