In the following post we’ll see how to recover tablespace to a point in time (TSPITR), some of the young DBAs would be thinking why do we need to do TSPITR its an incomplete recovery and why would we ever want to do so. To those I would like to introduce them to a fraternity known as Developer (refereed as an application guy), If you are a DBA these guys job is to make sure that each and every penny that Organisation is giving you is paid off. There are n number of ways in which they do that (Did I hear the F- word…), well you’ll come across a scenario where a developer thought that they were in a test environment and issued commands to delete data from several tables in one tablespace whereas It turns out that they were in the production environment ( It might seem Stupid but I don’t know a single DBA who hasn’t faced such a situation).

So this post is to describe how to use TSPITR to restore the tablespace in question to the point-in-time just before the DELETE ( Or any other erroneous DML/DDL ) was issued.

For ease of discussion lets name the Tablespace for which we have to perform TSPITR as USERS.

STEP 1:- Determine and Resolve Dependencies between the objects in the tablespace of interest (USERS) and objects related to these via referential integrity constraint.

To get these dependencies Oracle has given a view called as TS_PITR_CHECK, which can be used as:-

• SELECT * FROM sys.ts_pitr_check WHERE (ts1_name = ‘USERS‘ AND ts2_name != ‘USERS‘)  OR (ts1_name != ‘USERS‘ AND ts2_name = ‘USERS‘);

So if there are any dependencies the above query would give you all the details that are required, and accordingly you can disable the constraints and make a note of it. If we don’t disable the constraints, TSPITR would not be successful.

STEP 2:- Determine Which Objects Will Not Be Recovered

We are doing a TSPITR to a point-in-time when that erroneous SQL was executed, so what about objects which were created after that time, here comes the introduction to a new fraternity which are known as Database Administrators, no matter how poorly the SQl are written, no matter what edition the database is, no matter what infra does the database run, DBAs have to ensure magical performance and almost recovery of everything… EVERYTHING… (Did I hear the Awwww expression…) so henceforth the STEP number 2.

For this special case Oracle has  TS_PITR_OBJECTS_TO_BE_DROPPED view, which can be used as:-

• SELECT owner, name, tablespace_name FROM ts_pitr_objects_to_be_dropped WHERE tablespace_name =’USERS’ AND creation_time > to_date(‘<Time of TSPITR>’,’dd-mon-rrrr hh24:mi:ss’);

Above query would give you all the objects that were created after the point-in-time till where you’ll restore the tablespace (<Time of TSPITR>).

Once you get the objects you can use datapump to export the objects in concern, for example:-

• expdp <username/password> tables=<Comma separated list of table name> directory=DATA_PUMP_DIR dumpfile=TSPITR_after_objects.dmp logfile=TSPITR_after_objects.log

You might have to amend the expdp statement above, directory information you’ll get via DBA_DIRECTORIES view, and if using OS authentication and want to connect as sysdba use (\”/ as sysdba\”) in UNIX.

Step 3: Create an Auxiliary Destination

Several Steps are performed internally for which RMAN needs an auxiliary destination, so we need approximately equivalent space as the size of the datafiles in the tablespace to be recovered. So once we ensure there is proper space, on that filesystem we create an Auxiliary destination.

mkdir <Path where you have sufficient space>/auxx

STEP 4:- Recover the tablespace.

Now RMAN is ready to recover your tablespace you can now use until time (Until Time Clause) or until SCN (Until SCN Clause) or until sequence (Until Sequence Clause) clause to recover your tablespace.

• RMAN> connect target /
• RMAN> recover tablespace users until time “to_date(’12-aug-2015 00:00:00′,’dd-mon-rrrr hh24:mi:ss’)” auxiliary destination ‘<Path where you have sufficient space>/auxx’;

Now the most important things comes up, THE OUTPUT, from the output you can see that there is a hell lot of tasks that RMAN does for you, I have listed the tasks below in case you haven’t personally seen the beauty:-

• Creates an auxiliary instance, starts it, and connects to it
• Takes offline tablespaces that are involved with TSPITR
• Restores the backup control file that corresponds to the target restore time
• Restores datafiles to a destination specified by your AUXILIARY DESTINATION
• Recovers restored datafiles in an auxiliary location
• Opens an auxiliary database with the open resetlogs command
• Exports the auxiliary data dictionary metadata about recovered tablespaces and shuts down the auxiliary database
• Issues a switch command on the target database to update the control file to point at the recovered auxiliary datafiles
• Imports objects associated with recovered tablespaces into the target database
• Deletes the auxiliary datafiles

See, isn’t it a beauty..!! All this tasks are being performed while a DBA is just sitting and explaining WHAT/WHEN/HOW/WHERE to Senior Managers and Stakeholders, which I think is more difficult than the task done by RMAN in the background, if it were to me I’ll ask the RMAN to switch the tasks 🙂 …

I always wondered if RMAN is doing all this stuff why not export import the tables too so that we can also recover tables from RMAN, which was not possible in 11g, however studying the code helps me figure out the glitch. The great news is this has been fixed in 12c and YES you can even recover a table via RMAN in 12c.

STEP 5:- Importing the objects which were determined during STEP 2.

You guys thought its over right 🙂 … Well it isn’t, neither with the explanation to the Senior Management nor with the TSPITR.

• impdp <username/password> tables=<Comma separated list of table name> directory=DATA_PUMP_DIR dumpfile=TSPITR_after_objects.dmp logfile=TSPITR_after_objects_imp.log

So above command would be used to import the tables that were exported in STEP 5, so that even by TSPITR no objects that were created after the erroneous query are missed.

STEP 6:- Back Up the Tablespace and Alter It Online

Gotcha..!! why are you guys in so much of a hurry, its not a SQL Server or Sybase or DB2, its Oracle and you are a Oracle DBA we come with a guarantee, that we ensure every mistake done by the first fraternity (Developers) is fixed 😉

So backup your database because Oracle ensures everything except a Developer ( I am pretty sure this time I heard the F- word) and his cute little ANNOYING practices.

• RMAN> backup tablespace <Tablespace Name on which we did TSPITR>;
• RMAN> sql ‘alter tablespace <Tablespace Name on which we did TSPITR> online’;

Now its done… No… Oh Wait… Those Senior Management on the Bridge Call, well I wish I have a STEP and a GUN for the same…

For better understanding please go through the TSPITR output, it logs every step done by RMAN during TSPITR.

TSPITR Complete Output

I hope this post would be helpful in scenarios when you know the recovery process but as there is lot of stress on DBAs during recovery scenarios even googling at that time for the syntax is an overhead, hence the idea is to make a repository that cover all possibile recovery scenarios step by step with syntax of commands at a single destination. I would try to cover all the scenarios in my subsequent posts. Please comment if any of the curious readers are interested to know the internals of the recovery process like which process does that, what information is in redo that is applied to the datafiles to recover them to point in time and etc.

PLEASE COMMENT IF YOU FACE ANY ERRORS WHEN PERFORMING THIS RECOVERY SO THAT WE CAN WORK ON THOSE AND ENHANCE THIS REPOSITORY FOR OTHER READERS.