Partial DB Recovery To ReClaim User Objects
There are cases where users drops the objects and later they realize, what they have done. In case, you dont have flashback enabled or recycle bin turned on, you are in for some messy work. In such cases, one has to restore the whole database and then export and import back the desired objects. This works fine when you are dealing with small DBs however, if the DB is running in TBs, this brings whole new set of problems due to its sheer size.
Though, there is a way where you can do the partial restore of DB without restoring the whole database which helps you to reduce the time, efforts and resources.
One can follow the bellow mentioned process to achieve it should it needed.
Use Case - There a package called as TEST in sysman schema, which got dropped. We need to recover this using partial recovery. The object residing in SYSAUX table space. Hence we will ignore the TEMP & USERS table spaces.
On SOURCE DATABASE -
SQL> select a.FILE#, b.NAME from v$datafile a , v$tablespace b where a.TS#=b.TS#;
FILE# NAME
---------- ------------------------------
1 SYSTEM
2 SYSAUX
3 UNDOTBS1
4 TEMP 5 USERS
One has to start the Auxiliary instance on target node, with pfile copied from DB to be restored. In this case, I will rename the db instance as AUX.
[oracle@urac1 BKP_TST]$ . oraenv
ORACLE_SID = [AUX] ?
ORACLE_HOME = [/home/oracle] ?
[oracle@urac1 BKP_TST]$ sqlplus / as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 402656720 bytes
Database Buffers 1191182336 bytes
Redo Buffers 7344128 bytes
## NOW ONE NEEDS TO RESTORE CONTROLFILE FROM BACKUP PIECE. FOLLOWING BACKUP PIECE CONTAINS MY CONTRILFILE.
restore controlfile from '/gold_backups/BKP_TST/09_03pi266o_1_1.bkp'
RMAN> restore controlfile from '/gold_backups/BKP_TST/09_03pi266o_1_1.bkp' ;
Starting restore at 09-SEP-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+DATA/aux/control01.ctl
Finished restore at 09-SEP-14
RMAN> sql "alter database mount clone database";
sql statement: alter database mount clone database
released channel: ORA_DISK_1
## NOW WE NEED TO CHANGE THE NAME OF THE DATAFILES BEFORE RESTORE.
## THIS IS USEFUL IF WE ARE USING THE SAME DISK GROUP AS SOURCE DATABASE.
## IN THIS TEST CASE, I AM CREATING AUXILIARY DATABASE ON SAME HOST WITH DIFF UNIQUE NAME.
## DB_UNIQUE_NAME=AUX
RUN
{
set newname for datafile 1 to '+DATA/AUX/system01.dbf';
set newname for datafile 2 to '+DATA/AUX/sysaux01.dbf';
set newname for datafile 3 to '+DATA/AUX/undotbs01.dbf';
restore tablespace system,sysaux, undotbs1;
switch datafile all;
sql "alter database datafile 1,2,3 online";
-- recover database skip forever tablespace TEMP,USERS;
--SINCE WE ARE RESTORING BACKUP ON DIFFERENT HOST, THE REDO LOG FILE DOES NOT EXISTS.
-- sql "alter database rename file ''/fs01/oradata/primary/REDO01.LOG'' to ''/fs01/oradata/tspitr/REDO01.LOG''";
-- sql "alter database rename file ''/fs01/oradata/primary/REDO02.LOG'' to ''/fs01/oradata/tspitr/REDO02.LOG''";
-- sql "alter database rename file ''/fs01/oradata/primary/REDO03.LOG'' to ''/fs01/oradata/tspitr/REDO03.LOG''";
}
RMAN> RUN
{
set newname for datafile 1 to '+DATA/AUX/system01.dbf';
set newname for datafile 2 to '+DATA/AUX/sysaux01.dbf';
set newname for datafile 3 to '+DATA/AUX/undotbs01.dbf';
restore tablespace system,sysaux, undotbs1;
switch datafile all;
sql "alter database datafile 1,2,3 online";
}
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 09-SEP-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/AUX/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to +DATA/AUX/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to +DATA/AUX/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /gold_backups/BKP_TST/09_02pi2669_1_1.bkp
channel ORA_DISK_1: piece handle=/gold_backups/BKP_TST/09_02pi2669_1_1.bkp tag=FULL_BACKUP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 09-SEP-14
datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=857808883 file name=+DATA/aux/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=5 STAMP=857808883 file name=+DATA/aux/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=857808883 file name=+DATA/aux/undotbs01.dbf
sql statement: alter database datafile 1,2,3 online
## RECOVER THE DATABASE TILL SPECIFIC TIME IF YOU KNOW IT OR JUST DO THE RECOVERY
RMAN> run
{
-- set until time "to_date( '09-09-2014 09:18', 'DD-MM-RRRR HH24:MI')";
recover database skip forever tablespace TEMP,USERS;
}
Starting recover at 09-SEP-14
using channel ORA_DISK_1
Executing: alter database datafile 4 offline drop
starting media recovery
RMAN-08187: WARNING: media recovery until SCN 1001013 complete
Finished recover at 09-SEP-14
## TRYING TO RECOVER DATABASE USING CANCEL BASED RECOVERY
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1002979 generated at 09/09/2014 09:19:51 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0.3/db_1/dbs/arch1_9_857810115.dbf
ORA-00280: change 1002979 for thread 1 is in sequence #9
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open RESETLOGS;
alter database open RESETLOGS
*
ERROR at line 1:
ORA-00395: online logs for the clone database must be renamed
ORA-00312: online log 3 thread 1: '+DATA/testdb/redo03.log'
## NOW SINCE ITS COMPLAINING ABOUT THE NAME OF THE LOGFILE, ONE HAS TO RENAME IT. RENAME ALL THE THREE LOGFILES.
## THIS HAPPENED DUE TO THE FACT THAT I AM RESTORING THE DATABASE ON SAME DISK GROUP. ONE HAS TO BE CAREFUL HERE
## EVEN IF YOU RENAME THE LOGFILES, IT WONT HURT AS YOU ARE JUST UPDATING ENTRIES IN CONTROLFILE.
## REDO LOGS WILL BE RECREATED WHEN YOU OPEN THE DB IN RESETLOGS MODE
alter database rename file '+DATA/testdb/redo03.log' to '+DATA/AUX/redo03.log'
SQL> alter database rename file '+DATA/testdb/redo03.log' to '+DATA/AUX/redo03.log'
Database altered.
SQL> alter database rename file '+DATA/testdb/redo01.log' to '+DATA/AUX/redo01.log;
Database altered.
SQL> alter database rename file '+DATA/testdb/redo02.log' to '+DATA/AUX/redo02.log';
Database altered.
SQL> alter database open RESETLOGS;
Database altered.
SQL> conn sysman/sysman
Connected.
SQL> select text from dba_source where name='TEST';
TEXT
--------------------------------------------------------------------------------
PACKAGE test AS
TYPE TimeRec IS RECORD (
minutes SMALLINT,
hours SMALLINT);
TYPE TransRec IS RECORD (
category VARCHAR2(10),
account INT,
amount REAL,
time_of TimeRec);
minimum_balance CONSTANT REAL := 10.00;
number_processed INT;
insufficient_funds EXCEPTION;
END trans_data;
So as you can see, you can now see a package of interest which we were trying to restore. Hope this helps.
Useful post -- “Amazing write-up!”
ReplyDelete