Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 3 March 2013

Apply TSPITR to recover Drop Tablespace with DBPITR and TTS Method

After Dropping Tablespace by mistake we need to restore that tablespace. For this purpose we can use any of the listed solution. Check your alert.log to find he exact drop time.
1. Database Point in Time Recovery (DBPITR)
2. Tablespace Point in Time Recovery (TSPITR)
3. Transportable Tablespace (TTS) Method.
4. Use DUPLICATE database & Convectional export/ import Method.
1. DBPITR Concepts: 
DBPITR enables you to recover a database to some time in the past. For Example, if a logical error occurred today at 7:30 AM, DBPITR would enable you to restore the entire database to the state it was in 07:29 AM there by removing the effect of the error but also remove all other valid updates that occurred since 07:29 AM. The entire database will be rolled back in time in order to recover the tablespace which may be extremely undesirable.
Note: It is highly recommended that you backup your controlfile and your online redolog files before invoking DBPITR, so that you can perform complete recovery to the current point in time incase if DBPITR does not leave the database in desired state. DBPITR can also be done faster with flashback techniques. Check our separate post for this operation:
startup mount;
SET UNTIL TIME "TO_DATE ('01-04-12 07:29:00', 'DD-MM-YY HH24:MI:SS')";
restore database;
recover database;
alter database open resetlogs;
The set until time clause in side the RMAN run block is the time in the past, to which your database will be recovered. RMAN uses this time to determine from which backup to restore the datafiles, as backup must have been created prior to the intended restore time. All datafiles are then restored from the selected backup after which they are recovered up to the “until time” using archive logs and possibly online redologs.
2. TSPITR Concepts: 
TSPITR enables you to recover that particular tablespace to some time in the past, leaving the remainder of database at the current time. This is some how better solution especially when the multiple application is running with database. For More info: Recovery of Dropped Tablespace (TSPITR) Method
SQL> shutdown abort;
startup nomount;
SET UNTIL TIME "TO_DATE ('01-04-12 04:10:00', 'DD-MM-YY HH24:MI:SS')";
restore controlfile;
alter database mount;
restore database;
recover database;
alter database open resetlogs;
To perform DBPITR step during the TSPITR, RMAN requires the database to be in archivelog mode and existing backup of database that was created before the point in time recovery, and all the archive logs and online logs created from the time of the backup until the point in time recovery. Also the controlfile recovery (from autobackup) must matches with the database physical schema at the point in time when recovery ends. 
3. TTS Concept: 
TTS enable to transport of large amount of data from one oracle database to another database instead of creating DDL and DML statements for each row and object in the tablespaces. This is done my copying the datafiles from a set of one or more tablespace from source to the target database, together with some metadata export using exp or expdp and imported using imp or impdp, to the target. This is very fast or feasible solution as compare to any other solution such as “create table as select” “using database links” or “convectional import/export.
For TTS tablespace (s) must be self contained, means that segment inside those tablespace must not depend on objects stored in other tablespace that are not part of the transport tablespace set. 1. There are some other restrictions to perform TTS on particular set of tablespace. You can check it as follows:
SQL> exec sys.dbms_tts.transport_set_check(’hrms’)
SQL> select * from sys.transport_set_violations;
2. Export the transportable tablespace as a dumfile
SQL>alter tablespace hrms read only;
3. Copy dump file (hrms.dmp) as well as datafile associated with dump file (hrms tablespace) plus any related external data files held externally such as bfile or lobs for table in the tablespace to the target system.
3. Import transportable tablespace through dumpfile.
SQL> drop tablespace humres including contents;
C:\> imp TRANSPORT_TABLESPACE=y TABLESPACES=hrms DATAFILES=’D:\oracle\oradata\sadhan\hrms01.dbf’ FILE=hrms.dmp
Multiple block size TTS support: Oracle 9i introduce the case when source and target database have different standard block sizes, this is done using the system modifiable DB_nk_CACHE_SIZE parameters in the target database.
Cross platform TTS support: Oracle 10g introduces the case when source and target database are on different platforms and where “Endian-ness” of the platform might different.
4. Use DUPLICATE & Conventional Import/Export Concept: 
To recover from a drop and truncated table, a duplicated database (copy of primary) will be restored and recover to point in time, so the table can be exported. Once the table export is complete, the table can be imported into the primary database. This can be performed online, but we will need to factor in the disk space requirements to create a clone of the database from which the tablespace has been dropped.Recovery of DROP or TRUNCATE table using Duplicate DB Method.
Scenario with Example:
Our Oracle 9i database running with two application payroll software and Financial Software on Windows Environment. All the HRMS application Tables resides in SDH_HRMS_DBF Tablespace and Finance Application tables resides in SDH_FIN_DBF. Corresponding to the two applications, the database users are HRMS AND ORAFIN.
Now we will go through some logical error in HRMS application and try to Apply TSPITR method to recover the loss with DBPITR and TTS.
SQL> select to_char(sysdate,'yyyy-mm-dd:hh24:mi:ss') "SYSDATE" From dual;
     Set CU_CODE='WRONG_UPDATE' where SEQCOD = 65;
25 rows updated.
SQL> commit;
Commit complete.
Now consider the situation after this mistake occurred in HRMS application, after some times oracle financial application users perform some operation
SQL> select * from orafin.DEPT_MASTER
     Where JDADPT = 501;
----- ---------- ------   -----------   -------
1     FRESH       501      PRODUCE       7
SQL> update orafin.DEPT_MASTER
     set DIVNAME = ‘PROBLEM’ where JDADPT = 501;
1 rows updated.
SQL> commit;
Commit complete.
SQL> select * from orafin.DEPT_MASTER
     Where JDADPT = 501;
----- ---------- ------   -----------   -------
1     PROBLEM     501      PRODUCE       7
Now in this situation considering the solution the DBPITR would cause the loss of the update of oracle financial application or SDH_FIN_DBF tablespace. Performing TSPITR would only affect the HRMS application.
RMAN> recover tablespace SDH_HRMS_DBF until time
Auxiliary destination 'G:\ORA_DBF_EXTENDED';
After the successful completion of above recovery procedure, you can see the HRMS ‘wrong_update’ is corrected but the update of ORAFIN application has not been impacted.
SQL> select orafin.DEPT_MASTER
     Where JDADPT = 501;
----- ---------- ------   -----------   -------
1     FRESH       501      PRODUCE       7
Note: Taking Controlfile autobackup goods that guarantees that a controlfile may be recovered that matches the database physical schema at the point in time when recovery end. This example was performed using oracle 10g database, the database was OPEN during the recovery process usable by all the applications except the HRMS.
1.        RMAN started an automatic in NOMOUNT state. This instance is randomly named and exists only during the TSPITR.
2.        RMAN restored a controlfile for that instance from autobackup and MOUNTED the auxiliary database with it. This controlfile contains the original name of datafiles, which are mapped to the auxiliary destination ‘G:\ORA_DBF_EXTENDED’ with the set newname clause.
3.        RMAN restored the datafile for auxiliary database, skipping unnecessary datafiles. In order to do a TSPITR for the HRMS tablespace, only the datafile for this tablespace and the datafile for mandatory tablespace SYSTEM, SYSAUX AND UNDO are needed. The datafile of the HRMS tablespace get restored to the original location (not to the auxiliary destination) there by eliminating the need to copy it. A tempfile for temp tablespace also created automatically. All others tablespace are skipped, and time to restore and recover them are saved in order to speed up the TSPITR.
4.        RMAN performed DBPITR with auxiliary database.
5.        RMAN performed a TTS with the datafile and metadata of HRMS tablespace, where the source database was the auxiliary database on the automatic instance and the target was the original database.
6.        RMAN removed the auxiliary database with its files and terminated the automatic instance.
Tablespace Point in Time Recovery is easy for DBA to perform using RMAN and provides benefits especially when many applications have been consolidated into the same database. Internally RMAN creates the auxiliary database from a backup of source database, and then perform a combination of DBPITR and TTS from the auxiliary database to the source database for the specified tablespace. In fact TSPITR transports a set of one or more tablespace from an earlier point in time, back into their original database, which is the current point in time.
Check the below link for related operation: 


Post a Comment