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!
Did run into an issue where it doesn’t seem to know that aliased files are actually in use. Example:
SQL> @list_orphan_dbfiles.sql
rm +DATAC1/MYDB/CONTROLFILE/CURRENT.754.1037369121
rm +RECOC1/MYDB/CONTROLFILE/CURRENT.18522.1037369123
ASMCMD [+] > ls -l */MYDB/CONTROLFILE
Type Redund Striped Time Sys Name
+DATAC1/MYDB/CONTROLFILE/:
CONTROLFILE HIGH FINE JAN 26 12:00:00 Y current.754.1037369121
CONTROLFILE HIGH FINE JAN 26 12:00:00 N current01.ctl => +DATAC1/MYDB/CONTROLFILE/current.754.1037369121
+RECOC1/MYDB/CONTROLFILE/:
CONTROLFILE HIGH FINE JAN 26 12:00:00 Y current.18522.1037369123
CONTROLFILE HIGH FINE JAN 26 12:00:00 N current02.ctl => +RECOC1/MYDB/CONTROLFILE/current.18522.1037369123
ASMCMD [+] >
Needless to say, removing the controlfiles listed in the procedure’s output would be disastrous. Is there any way to fix this so it recognizes alias pointers?
Great work!!!
Could you please hint me with some views (if they exists) to expand on your script to include PDBs?z
Thanks!
Output Like:
GNAME CDB_NAME PDB_NAME FILE_TYPE MB GB #FILES
Found a bug in the script. alias_index isn’t always = 0 for the root parent_index.
added rank() to get root parent_index.
(SELECT parent_index
FROM (SELECT parent_index,rank() over (order by alias_index) rank_no
FROM v$asm_alias
WHERE group_number =
(SELECT group_number
FROM v$asm_diskgroup
WHERE name =
c.Diskgroup)
)
WHERE rank_no=1) a
Thanks! Nice fix.
How to get sql for file_name and space from v$asm_files at database level to calculate space saving?