Orphaned Files in ASM

There may be a better way to do achieve this, but as I have found myself using it a number of times it seems worth sharing…

The problem of orphaned files can occur with or without ASM and I’m sure most readers have found themselves identifying database files that are present at a file system or ASM level, but the database (control file) knows nothing about them. You want to clean up the debris from previous activities, but you need to be confident that your programmatic or manual verification of the file being orphaned is correct… No one wants to delete a file that should not be removed!

Before going into the steps I use for this I need to stress that the approach relies on an assumption:

ASM directory “+<DISKGROUP_NAME>/<DB_UNIQUE_NAME>” contains no database files that should be retained and belong to a database with a different DB_UNIQUE_NAME.

… and the example code snippets are based on the following further assumptions/statements:

1. oratab has the instance name listed – Last time I checked DBCA does not take care of this for you in a RAC environment, but many companies add instance names. If you don’t then you’ll need to modify the approach to append the appropriate number to ORACLE_SID before the SQL*Plus & RMAN commands. One way to achieve this is covered in RAC/Exadata Shell Aliases, but to keep this simple I’ll leave that to those that need it. Note that you would also need to modify the approach used to run for multiple database that is given at the end of the post.
2. Parts of the code snippets assume you’re not running more than an eight node RAC database – Actually, the approach would work on a RAC database with more than 8 nodes as long as you didn’t pick a node with instance number(s) greater than 8 to run the script on.
3. It is not perfect/foolproof!

If you use Oracle Managed Files (OMF) for all databases that use the ASM storage then you’re very likely (but not guaranteed) to be safe to use this approach, however, I can imagine a number of ways to end up in a position where this is not going to be the case. For example, if a database has been renamed, but the database files have not been moved within ASM to match the new name. This is something I’ve previously covered in Rename Database When Using ASM.

Basically, you have been warned and are responsible for making sure this approach is safe in your environment.

To protect you (dear Googler who just wants a quick fix for your immediate problem) from making a mistake you do not appreciate you could be about to make it seems appropriate to provide the steps rather than my complete script so that you have a slightly higher barrier to deleting things you really don’t want to delete.

OK, so that is enough of my caution.

Orphaned Files for a Single Database

Getting a list of the orphaned files for database is as simple as the following (assuming the environment is already source using oraenv for the instance):

rman log=orphaned_files_${ORACLE_SID%[1-8]}_DATA.log << EOF
connect target /
catalog start with '+DATA/${ORACLE_SID%[1-8]}';
EOF

A couple of comments about this snippet:

1. The ${ORACLE_SID%[1-8]} strips the instance number off ORACLE_SID, which under most circumstances will give you the DB_NAME or DB_UNIQUE_NAME in a RAC environment, but this is not guaranteed to be the case as some people do odd things with naming standards.
2. If you’re not using this in a RAC environment and you have instance names that end with a digit, which seems fairly common, then replace all the ${ORACLE_SID%[1-8]} strings with ${ORACLE_SID} to avoid chopping the end off their names.
3. Diskgroup name is hard coded in this example, but it’s pretty trivial to come up with a way of obtaining the diskgroups and one approach is given later in this post.

Generating the File Deletion Script

Once the “orphaned_files_*” file has been created then we need to parse out the file names and turn them into ASMCMD rm commands:

awk -v db=${ORACLE_SID%[1-8]} '{if (($1" "$2 == "File Name:") && ($3 !~ /spfile/)) {print "asmcmd rm "$3}}' orphaned_files_${ORACLE_SID%[1-8]}_*.log > asmcmd_cleanup.sh

This will generate a file named asmcmd_cleanup.sh containing the commands to delete all the orphaned files that were found in +<DISKGROUP_NAME>/<DB_UNIQUE_NAME> for the database, excluding any file or path containing the string “spfile” (because many places create an alias for spfile). You may want to add further exclusions to this.

It would be very wise to verify the contents of asmcmd_cleanup.sh, but when you are happy that you have excluded what you want to exclude it just needs to be run as a user with appropriate privileges to execute asmcmd and with the appropriate environment set up. For example, if you have implemented GI and RDBMS role separation, e.g., oracle and grid users then you’ll need to run the script as grid.

So that’s the nuts and bolts of the approach. Hopefully you’ll find it useful. If you have a cleaner (or simply better) approach to identify the orphaned files then please let me know.

Finding the ASM Diskgroups for a Database

As mentioned earlier deriving the list of diskgroups can be achieved using:

diskgroups=$(sqlplus -s -L / as sysdba << EOF
set pages 0 feedback off
select listagg (name, ' ') within group (order by name) from v\$asm_diskgroup;
EOF
)

Generating File List for all Databases in /etc/oratab

… and if you have a number of databases you want to run this for in a single pass then you probably want to use something like the following in a script:

ORAENV_ASK=NO
for inst in $(awk -F":" '{if(($1 ~ /[1-8]$/) && ($1 !~ /+ASM/ )) {print $1}}' /etc/oratab); do
ORACLE_SID=${inst}
. oraenv
diskgroups=$(sqlplus -s -L / as sysdba << EOF
set pages 0 feedback off
select listagg (name, ' ') within group (order by name) from v\$asm_diskgroup;
EOF
)
for diskgroup in $diskgroups; do
rman log=orphaned_files_${inst%[1-8]}_${diskgroup}.log << EOF
connect target /
catalog start with '+${diskgroup}/${inst%[1-8]}';
EOF
done
done

You just need to combine the above with the awk command to generate the asmcmd rm commands and you’re got something that will run for all the databases on a given host and generate a script file that can be run from the GI/ASM environment (i.e., with asmcmd in your path) in order to clear up the files that the databases do not know about.

A final word of caution: Watch you don’t remove backupsets that have been kept for a reason, but the current control file doesn’t know about.

As ever, feedback most welcome, even if I’ve help you to delete something you didn’t want to remove and you’re mad with me 😦