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