Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Rodger.

Asked: July 20, 2016 - 11:05 pm UTC

Last updated: July 25, 2016 - 8:41 am UTC

Version: Oracle Database (Enterprise) 11.2.0.4

Viewed 1000+ times

You Asked

We've been migrating several DBs from their old Oracle 10G platforms to our new Oracle SuperClusters (SC). The older DBs were flatfile based, but the new SC-based DBs are using ASM. Several of the migrations initially failed, and some of the older DBs were merged, and then dropped. Consequently, we have multiple (several K) files in ASM that belong to databases that no longer exist (or never existed). Is there a way to reliably determine, en masse, which of these files can be removed without impacting the 18 existing databases on the SuperCluster (cannot rely on paths in ASM to divide the files into useful and orphans)?

and Chris said...

Well you could query dba_data_files for each of your remaining databases. Then crosscheck this list against what's actually in ASM.

Or you could take the approach described here:

https://oraganism.wordpress.com/2012/09/09/orphaned-files-in-asm/

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Not all of the orphans are datafiles.

Rodger Schell, July 22, 2016 - 10:34 pm UTC

The problem with this approach is that not all of the orphaned files are datafiles, currently I see:

ARCHIVELOG
ASMPARAMETERFILE
AUTOBACKUP
BACKUPSET
CHANGETRACKING
CONTROLFILE
DATAFILE
DATAGUARDCONFIG
DUMPSET
OCRFILE
ONLINELOG
PARAMETERFILE
TEMPFILE

Admittedly, some of these can be determined by querying other tables, but I don't know a way of resolving several of these file types.
Connor McDonald
July 25, 2016 - 8:41 am UTC

Not all those directories contain things that are managed by the database! For example, DUMPSET (should) only contain data pump export/import files. Only you can state whether these are "orphans".

If you're using RMAN, you should be able to use this to see which backups, archivelogs, etc. are still registered.

There's various other *file views which may help:

v$controlfile
v$tempfile

But you're going to have to do some manual work figuring out what's needed and what's not. Unless you can rebuild the whole thing from scratch...