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
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
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).
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.
What I'am confused is that I read several documents that mentions that I cannot offline drop, the first datafile of the tablespace.
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
I added a startup mount, then the offline drops , the drop tablespaces, and then the alter database open resetlogs.
control-drop-tablespaces.txt
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).
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you.