OS : Solaris 5.10
DB Oracle 11g 11.2.0.4
DB size 2TB
Recently i got into a situation there I have dropped the wrong tablespace on database. I needed to do the damage control.
options i though of
1) RMAN tablespace point in time recovery - out of question as i dropped the tablespace and the current controlfile doesn;t know the tablespace name. (
2) shut down the PROD DB and restore the old control file(which will have the dropped tablespace detail), mount DB and restore the dropped tablespace and roll farwad the db and open normally (resetlogs not required ) - out of question as logically not correct to shut the entire DB for just one tablespace recovery, when partial cloning can fix the issue.
3) Do the partial cloning (SYSTEM, SYSAUX, UNDO and the dropped tablespace)
a) on different server - I could have choosen this option but would have delayed the whole process of recovery and I would have needed the netbackup setting on the server to receive the PROD backup for cloning.
b) on the same server - which I chose and got into trouble.
Followed the below steps to perform the cloning on the same server.
1) created the pfile with below consideration.
DB_UNIQUE_NAME= " different than source database"
COMPATIBLE= "should be same as source database"
CONTROL_FILES= " path should be different then source database"
DB_NAME= " same as source database"
INSTANCE_NAME = "different than source database"
LOG_ARCHIVE_DEST_1= "different than source database"
2) Startup nomount with above created pfile.
3) connect to catalog database and restore the controlfile (before the point where drop tablespace happened) from RMAN backup.
4) restore tablespace (SYSTEM, SYSAUX, UNDO and the dropped tablespace) below is the script, partial DB restored successfully. Note : Here i am only doing set newname for tablespaces getting restored.
run
{
set newname for datafile 1 to '/test/ac/system01.dbf';
set newname for datafile 2 to '/test/ac/sysaux01.dbf';
set newname for datafile 3 to '/test/ac/undotbs01.dbf';
set newname for datafile 39 to '/test/ac/userss01.dbf';
set until time "to_date('2015-12-30:12:00:00','YYYY-MM-DD:hh24:mi:ss')";
restore tablespace system,sysaux,undotbs1,USERSS;
switch datafile all;
}
5) Recover database with below command. and rename the tempfile and redo logs to new name to avoid any issue and opened the DB with resetlogs option.
recover database skip tablespace
'USERS',
'TBS_000_TS01_ARCH',
'TBS_000_TS01_DATA',
'TBS_000_TS01_HIST',
'TBS_000_TS01_INDX',
'TBS_000_TS02_DATA',
.
.
.
.
500+ tablespaces to be skipped
.
.
'TEST';
the result of this command is in log: It takes the skipped tablespace datafile offline (it only made the change in the clone database controlfile)
recovery command log:
Starting recover at 30-DEC-15
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
using channel ORA_DISK_1
channel ORA_SBT_TAPE_1: starting incremental datafile backup set restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00003: /test/ac/undotbs01.dbf
.
.
.
.
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00003: /test/ac/undotbs01.dbf
channel ORA_SBT_TAPE_1: reading from backup piece doqq3c70_1_1
channel ORA_SBT_TAPE_1: piece handle=doqq3c70_1_1 tag=TAG20151230T052343
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:15
Executing: alter database datafile 4 offline
Executing: alter database datafile 5 offline
Executing: alter database datafile 6 offline
Executing: alter database datafile 7 offline
.
.
.
Executing: alter database datafile 36 offline
Executing: alter database datafile 37 offline
.
.
.
Executing: alter database datafile 537 offline
starting media recovery
archived log for thread 1 with sequence 4114 is already on disk as file /oradata/INSTANCE_ID/arch/INSTANCE_ID_846148712_1_4114.arc
channel ORA_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=4113
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:15
archived log file name=/test/INSTANCE_ID_846148712_1_4113.arc thread=1 sequence=4113
archived log file name=/oradata/INSTANCE_ID/arch/INSTANCE_ID_846148712_1_4114.arc thread=1 sequence=4114
unable to find archived log
archived log thread=1 sequence=4115
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/30/2015 17:40:55
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 4115 and starting SCN of 10100891182
RMAN> exit
6) until the 5th command PROD database is running fine. I did the expdp and impdp of the tablespace content .. it worked
7) When I tried to drop the clone database using below, this has actually dropped all the datafiles (except those part of clone database) of PROD database
SQL>alter database mount restrict exclusive;
SQL> drop database
Question
1) I was always under assumption that datafiles of one database (standalone) are locked by the instance and other instance( CLONE in this case) should not lock the datafiles of PROD and drop the datafiles. any input on why this behavior?
2) When i did the "recover database skip tablespace " the log shows that it made the datafile offline but actually it only made the change to the CLONE database controlfile and the PROD database was intact. if this is true then why drop database has dropped the datafiles of PROD database?
Please let me know if I need to provide the more background.
Regards,
Anurag