Hi all,
So I got asked by a client to perform a checking on ASM for orphan files, as they have some frequent create/drop database on this environment, as being a development env.
Also, lots of databases shared the same data diskgroup, so I had to work this out for all databases and also for possible inexistent databases.
Some basic approaches I raised:
1) Locating uncatalloged files in ASM per database.
– Source: https://oraganism.wordpress.com/2012/09/09/orphaned-files-in-asm/
– This approach assumes the files on ASM uncatalogged to any database are the Orphaned ones. Which is a fair assumption.
– But I understand that files can be catalloged and unmonted, which would brake this approach.
2) Listing files in ASM but not in database (v$datafile, v$datafile_copy, v$controlfile, v$tempfile, v$logfile) by database.
– Source: https://oracledba.blogspot.com/2018/11/orphaned-files-in-asm.html
– This seems a fair assumption. Would need to be ran from each database.
– There is not guarantee if this is working properly or not.
– Not clear also if PDB files are included.
– There is another similar one: https://dbaliveblog.wordpress.com/asm-orphaned-file-identification-script/
– Also this one: https://anjo.pt/wp/keyword-oracle/2013/02/26/find-orphan-asm-files/
3) MOS: Query That Can Be Used to Find Orphaned Datafiles on a 12c ASM Instance (Doc ID 2228573.1)
– From MOS, seems the most recommended approach.
– Attention point: PDB$SEED may not be shown as per: PDB$SEED Datafiles Not Appear In CDB_DATA_FILES (Doc ID 1940806.1)
— On 12.1.0.2, recommended to use “EXCLUDE_SEED_CDB_VIEW”. To check if it can be done on session level.
I downloaded and ran referred script on MOS Script to report the list of files stored in ASM and CURRENTLY NOT OPENED (Doc ID 552082.1) on the environment.
But the results didn’t sound correct.
After a while, I ended up building my own script based on all mentioned approaches and it worked very fine.
After approved I dropped all the listed files, freed a several TBs of space and no database affected. So I’d assume it as correct and would really recommend it for you.
So what did I used:
SQL to Check ASM Space per Database:
set pages 350 timing on col gname form a10 col dbname form a10 col file_type form a16 break on gname skip 2 on dbname skip 1 compute sum label total_db of gb on dbname compute sum label total_diskg of gb on gname SELECT gname, dbname, file_type, round(SUM(space)/1024/1024) mb, round(SUM(space)/1024/1024/1024) gb, COUNT(*) "#FILES" FROM ( SELECT gname, regexp_substr(full_alias_path, '[[:alnum:]_]*',1,4) dbname, file_type, space, aname, system_created, alias_directory FROM ( SELECT concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path, system_created, alias_directory, file_type, space, level, gname, aname FROM ( SELECT b.name gname, a.parent_index pindex, a.name aname, a.reference_index rindex , a.system_created, a.alias_directory, c.type file_type, c.space FROM v$asm_alias a, v$asm_diskgroup b, v$asm_file c WHERE a.group_number = b.group_number AND a.group_number = c.group_number(+) AND a.file_number = c.file_number(+) AND a.file_incarnation = c.incarnation(+) ) START WITH (mod(pindex, power(2, 24))) = 0 AND rindex IN ( SELECT a.reference_index FROM v$asm_alias a, v$asm_diskgroup b WHERE a.group_number = b.group_number AND ( mod(a.parent_index, power(2, 24))) = 0 ) CONNECT BY prior rindex = pindex ) WHERE NOT file_type IS NULL and system_created = 'Y' ) GROUP BY gname, dbname, file_type ORDER BY gname, dbname, file_type /
Expected Output:
SQL> @asm_sizebydb GNAME DBNAME FILE_TYPE MB GB #FILES ---------- ---------- ---------------- ---------- ---------- ---------- DATAC1 DATABSE1 CONTROLFILE 2316 2 1 DATAFILE 7620756 7442 49 DATAGUARDCONFIG 16 0 2 ONLINELOG 82536 81 14 PARAMETERFILE 8 0 1 ********** ---------- total_db 7525 DATABSE2 CONTROLFILE 492 0 1 DATAFILE 3081604 3009 47 ONLINELOG 416 0 4 PARAMETERFILE 16 0 2 PASSWORD 0 0 2 TEMPFILE 83372 81 3 ********** ---------- total_db 3090 DATABSE3 CONTROLFILE 588 1 1 DATAFILE 1430712 1397 8 DATAGUARDCONFIG 16 0 2 ONLINELOG 147816 144 18 PARAMETERFILE 8 0 1 ********** ---------- total_db 1542 [...] ********** ---------- total_disk 76868
SQL To list Orphan files per Database:
SET VERIFY OFF SET LINESIZE 200 SET SERVEROUTPUT ON SET PAGESIZE 50000 DECLARE cmd CLOB; 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) aa , (SELECT parent_index FROM (SELECT parent_index FROM v$asm_alias WHERE group_number = (SELECT group_number FROM v$asm_diskgroup WHERE name = c.Diskgroup) 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 = c.Diskgroup))) b WHERE aa.file_number = b.file_number(+) AND aa.alias_directory = 'N' 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; /
Expected Output:
rm +DATA/XPTODB/CONTROLFILE/CURRENT.4928.955985765 rm +DATA/XPTODB/CONTROLFILE/CURRENT.4934.955986589 rm +DATA/XPTODB/CONTROLFILE/CURRENT.4962.955998825 rm +DATA/XPTODB/CONTROLFILE/CURRENT.5063.956480113 rm +DATA/XPTODB/CONTROLFILE/CURRENT.6374.955984145 rm +DATA/XPTODB/CONTROLFILE/CURRENT.7547.955968953 rm +DATA/XPTODB/DATAFILE/TBSEXEMPLE.4936.955985803 rm +DATA/XPTODB/DATAFILE/TBSEXEMPLE.4966.955998847 rm +DATA/XPTODB/DATAFILE/TBSEXEMPLE.7540.955968995 rm +DATA/XPTODB/DATAFILE/TBSEXEMPLE.7574.955984177 rm +DATA/XPTODB/DATAFILE/TBSEXEMPLE.4937.955985803 rm +DATA/XPTODB/DATAFILE/TBSEXEMPLE.4967.955998847 rm +DATA/XPTODB/DATAFILE/TBSEXEMPLE.7542.955968995 rm +DATA/XPTODB/DATAFILE/TBSEXEMPLE.7558.955984177 rm +DATA/XPTODB/DATAFILE/SYSAUX.4935.955986599 rm +DATA/XPTODB/DATAFILE/SYSAUX.4963.955998847 rm +DATA/XPTODB/DATAFILE/SYSAUX.6286.955984161 rm +DATA/XPTODB/DATAFILE/SYSAUX.7544.955968963 rm +DATA/XPTODB/DATAFILE/SYSTEM.4930.955986599 rm +DATA/XPTODB/DATAFILE/SYSTEM.4964.955998847 rm +DATA/XPTODB/DATAFILE/SYSTEM.7536.955968965
To run this for all databases on server (RAC Databases):
export ORAENV_ASK=NO for DBSID in `ps -ef | grep ora_pmon | grep -v grep | awk -F_ '{ print $3}'` do echo "######" ${DBSID} export ORACLE_SID=${DBSID} . oraenv sqlplus / as sysdba @script.sql exit done
Hope it helps you!