Find orphan ASM files 5


These days I’ve been doing the migration of some BI databases into a private cloud, about 7 TB of data in total. We made sure that before starting there was enough disk space.

However, in the middle of the largest “duplicate from active database”, it stops. When trying to restart, it complaints that disk group space was exhausted.

After some investigation, I found out that for a previous DB, which made 1.8 TB in datafiles, it was using above 3 TB in ASM. A closer investigation and I could see several copies of some datafiles. I believe it come from the fact that there were network problems, duplicates that were repeated with some interval between, rolling forward with RMAN incremental backup, switch to copy… a whole mess. 🙂

Then come the problem:

– how to make a easy way to detect all orphan files on ASM?

The first solution that come to mind was to spool the list of data files and, from asmcmd, output all the files for the problematic DB with the command:

asmcmd ls dgdata/O01XXXXX/datafile > /tmp/f_asm.txt

Then put both files as external tables into a database and query them. But there were several steps and was hard work.

The second solution was to use v$asm_file and v$asm_alias together with queries to v$datafile, v$logfile and v$controlfile. This from the database instance, even if the documentation states that:
“In a database instance, V$ASM_FILE displays no rows.”
(http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_1029.htm#REFRN30172)

For this solution I’ve based on a query from http://aychin.wordpress.com/2011/07/26/dfdg-script-to-display-asm-disk-usage-information-and-files/

And the solution is:

DEFINE ASMGROUP="DGDATA"
 
set linesize 200
set pagesize 50000
col reference_index noprint
col type format a15
col files format a80

WITH v_asmgroup AS (SELECT group_number FROM v$asm_diskgroup WHERE name='&ASMGROUP'),
     v_parentindex AS (SELECT parent_index 
                    FROM v$asm_alias 
              WHERE group_number = (SELECT group_number FROM v_asmgroup) 
                AND alias_index=0),
  v_asmfiles AS (SELECT file_number, type 
              FROM v$asm_file 
           WHERE group_number = (SELECT group_number FROM v_asmgroup)),
 v_dbname AS (SELECT '/'||upper(db_unique_name)||'/' dbname from v$database)
SELECT 'rm '|| files files FROM -- this line show the delete command
(
  SELECT '+&ASMGROUP'||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) aa,
             (SELECT parent_index FROM v_parentindex) a,
             (SELECT file_number, type FROM v_asmfiles) b
  WHERE aa.file_number=b.file_number(+)
    AND aa.alias_directory='N'
   -- missing PARAMETERFILE, DATAGUARDCONFIG
   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 v_dbname)
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 '+&ASMGROUP%'
    UNION ALL
 SELECT upper(member) files, 'ONLINELOG' type FROM v$logfile WHERE member like '+&ASMGROUP%'
)
);

FILES
--------------------------------------------------------------------------------
rm +DGDATA/O01XXXXX/CONTROLFILE/CURRENT.800.807811333

So then I just need to run from asmcmd:
ASMCMD> rm +DGDATA/O01XXXXX/CONTROLFILE/CURRENT.800.807811333

Because WITH clause does not work on MOUNTED databases (like a standby) – SQL Statement Using ‘WITH’ Clause Fails with ORA-01219 [ID 1166073.1] , here the version without the WITH clause:

DEFINE ASMGROUP="DGDATA"
 
set linesize 200
set pagesize 50000
col reference_index noprint
col type format a15
col files format a80

SELECT 'rm '|| files files FROM -- this line show the delete command
(
  SELECT '+&ASMGROUP'||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) aa,
             (SELECT parent_index FROM (SELECT parent_index 
                    FROM v$asm_alias 
              WHERE group_number = (SELECT group_number FROM v$asm_diskgroup WHERE name='&ASMGROUP') 
                AND alias_index=0)) 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='&ASMGROUP'))) b
  WHERE aa.file_number=b.file_number(+)
    AND aa.alias_directory='N'
   -- missing PARAMETERFILE, DATAGUARDCONFIG
   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 '+&ASMGROUP%'
    UNION ALL
 SELECT upper(member) files, 'ONLINELOG' type FROM v$logfile WHERE member like '+&ASMGROUP%'
)
);

Leave a comment

Your email address will not be published. Required fields are marked *

5 thoughts on “Find orphan ASM files

  • Venu Gopal

    Very nice script and useful.
    But I can recommend to user “ALTER DISKGROUP DROP FILE ‘filename’;” to cleanup asm orphan files instead rm command. It’s just a suggestion.

  • Ann

    This script is really helpful. Can you help me with a SQL to get the total size (space used ) by these orphan files.
    So i would know how much space i would be saving if i drop these orphan files.

  • Jean-Louis CIRET

    Many thanks Anjo !!!! I was fighting with V$ASM_ALIAS to do this :

    my version with alter diskgroup drop file to drop file directly from sqlplus and not asmcmd :

    DEFINE ASMGROUP=”DATA_DG”

    set linesize 300
    set pagesize 50000
    col reference_index noprint
    col type format a15
    col files format a80

    WITH v_asmgroup AS (SELECT group_number FROM v$asm_diskgroup WHERE name=’&ASMGROUP’),
    v_parentindex AS (SELECT parent_index
    FROM v$asm_alias
    WHERE group_number = (SELECT group_number FROM v_asmgroup)
    AND alias_index=0),
    v_asmfiles AS (SELECT file_number, type
    FROM v$asm_file
    WHERE group_number = (SELECT group_number FROM v_asmgroup)),
    v_dbname AS (SELECT ‘/’||upper(db_unique_name)||’/’ dbname from v$database)
    SELECT ‘alter diskgroup &ASMGROUP DROP FILE ”’|| files || ”’;’ FROM — this line show the delete command
    (
    SELECT ‘+&ASMGROUP’||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) aa,
    (SELECT parent_index FROM v_parentindex) a,
    (SELECT file_number, type FROM v_asmfiles) b
    WHERE aa.file_number=b.file_number(+)
    AND aa.alias_directory=’N’
    — missing PARAMETERFILE, DATAGUARDCONFIG
    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 v_dbname)
    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 ‘+&ASMGROUP%’
    UNION ALL
    SELECT upper(member) files, ‘ONLINELOG’ type FROM v$logfile WHERE member like ‘+&ASMGROUP%’
    )
    );

  • Ciret

    Be carefull with this script because if you use aliases the script can list files that are actually used by the database