Avatar of LindaC
Flag for Puerto Rico asked on

Script attached to recover a database to another database named TEST - with dropping tablespaces

Hi experts.

I need to recover a database to another database that will be named TEST, dropping tablespaces I won't be restoring .
I will recreate the controlfile in the new database named Test and will be dropping the tablespace just before the alter database resetlogs.
The script is attached.

The question is if the "drop tablespaces statements just before the alter database resetlogs are ok or what do I need to do.
Attached is the script.
Oracle Database

Avatar of undefined
Last Comment

8/22/2022 - Mon

I don't believe you can do the drops until the database is open.  Since you are recreating the control file, the files for those tablespaces should show up with MISSING names.  Once the database is open, you should be able to drop them.  You may need to OFFLINE DROP the associated data files first (NOTE:  If you need to do this, query DBA_DATA_FILES for the names as they will not be the original names anymore, they should say MISSING).

Version 9.2.
What I'am confused is that I read several documents that mentions that I cannot offline drop, the first datafile of the tablespace.

I have uploaded the modified script.

I added a startup mount, then the offline drops , the drop tablespaces, and then the alter database open resetlogs.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes

Those are not the correct file names.  You cannot generate the commands until after you have recreated the control file.  The reason is that the names are in the control file and when you recreated the control file you omitted those names.  They will show up in DBA_DATA_FILES with a name that I believe defaults to the $ORACLE_HOME/dbs directory and the name starts with MISSING and then has a number after it (typically the datafile number).

It is not better to recreate the controlfile with only the va_data, system and undo datafiles, and skip all others? Can I do that?

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

Thank you.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.