Avatar of LindaC
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.
control-drop-tablespaces.txt
Oracle Database

Avatar of undefined
Last Comment
LindaC

8/22/2022 - Mon
johnsone

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).
LindaC

ASKER
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.
LindaC

ASKER
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.
control-drop-tablespaces.txt
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
johnsone

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).
LindaC

ASKER
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?
ASKER CERTIFIED SOLUTION
johnsone

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
or
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
LindaC

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