how to I identify datafiles in ASM that aren't a part of the database and remove them

Commands were issued to drop tablespaces. However, the including contents etc was not a part of the statement. It was intentionally done to prevent dropping datafiles that contain data. Now the datafiles need to be removed. This doesn't appear to be a common issue. Does anyone know how I can identify and remove the files? We are using ASM. I can see 2 files that are associated with dropped tablespaces. However, understandably I can't see the datafiles by querying the database. It is unclear how I can query within asm to find the datafiles. Is there a script out there that can somehow query and get a list of files with no associated tablespace and remove them?
sikyalaSenior Database AdministratorAsked:
Who is Participating?
 
MikeOM_DBACommented:
1) Extract file names using a script similar to this:
#!/usr/bin/ksh
# -- Set asm environment
ORAENV_ASK=NO
sid='+ASM'
db=ORCL
. /usr/local/bin/oraenv "$sid"
# -- list asm files
asmcmd <<!EOASM >asm_files.txt
ls +DATA/$db/DATAFILE
exit
!EOASM
# -- Set db environment
sid='orcldb1'
. /usr/local/bin/oraenv "$sid"
# -- list all database files:
sqlplus -s /nolog <<!EOSQL >db_files.txt
conn / as sysdba
select name from v\$datafile;
exit;
!EOSQL

Open in new window

2) Load the resulting files into excel spreadsheet, normalize and compare file names.
3) Good luck!
:p
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.