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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Linux

From novice to tech pro — start learning today.