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%'
)
);