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

LindaC
LindaC used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
johnsoneSenior Oracle DBA

Commented:
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).

Author

Commented:
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.

Author

Commented:
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
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

johnsoneSenior Oracle DBA

Commented:
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).

Author

Commented:
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?
Senior Oracle DBA
Commented:
Yes, you can do that.  That is what you are doing with your create controlfile statement.

There is no real need to drop the other tablespaces.  Just leave their data files with the MISSSING tag.  As long as you are not referencing objects in those tablespaces, then you should be fine.

Author

Commented:
Thank you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial