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!
