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.
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.
8 thoughts on “Performing Tablespace Point-in-Time Recovery”
Nice Post Nitish,I have some query like
1.Is backup required for TSPITR?
2.If data deleted from table associated with single datafile having tablespace size 2TB ,is it necessary to create 2tb of auxillary space?
3.For TSPITR ,archive mode required to enable.
Thanks for stopping by, Glad you liked the post.
Regardsing TSPITR, If you carefully notice the output of TSPITR you’ll get all your answers and to many more questions, for exact output please check the TSPITR Complete Output link above in the post, following are the Steps occurred during a TSPITR:-
1) Creates an auxiliary instance, starts it, and connects to it
2) Takes offline tablespaces that are involved with TSPITR
3) Restores the backup control file that corresponds to the target restore time
4) Restores datafiles to a destination specified by your AUXILIARY DESTINATION
5) Recovers restored datafiles in an auxiliary location
6) Opens an auxiliary database with the open resetlogs command
7) Exports the auxiliary data dictionary metadata about recovered tablespaces and shuts down the auxiliary database
8) Issues a switch command on the target database to update the control file to point at the recovered auxiliary datafiles
9) Imports objects associated with recovered tablespaces into the target database
10) Deletes the auxiliary datafiles
Now as you can see the 4th step answers your 1st question, the 5th Step answers your 3rd question.
1) Yes Backup is required for TSPITR.
2) Not only the tablespace in concern but SYSTEM, SYSAUX & UNDO tablespaces are also recovered, so even more than 2TB would be required for TSPITR in the scenario you described. Again for better understanding WHY please check the complete output, I have attached in my post.
Even in 12c where table level recovery is possible it follows the same steps, and just includes the table export import in the last step.
3) Yes for recovering the Tablespace to the point where you want (before the un-intentioned drop) archivelogs are required to be applied to the restored datafiles.
Let me know if sounds confusing to you, could describe the steps involved individually.
Wonderful writeup! Read couple of articles of yours and have become a fan! The good thing about your posts are it’s written in a simple and uncomplicated way with log files to support your theory. Very interesting, Keep writing – Rajaram
LikeLiked by 1 person
Thanks, Glad you liked it.
I want suggestion to Recovery of table in following Scenario
1) RMAN BACKUP IS AVAILABLE UNTIL TIME 4 AM in morning .
2) A TABLE WAS CREATED AT MORNING TIME AND DROPPED again in some .time
3) recycle bin has been Disabled or the table has been purged from recycle bin
what are other option available for DBA to recover table ?
Loved the stuff and enjoyed reading…had the same expressions (awwwww) while going through!!! 😉
Wow , superb explanation
For say table dropped or records deleted at 9:05 AM ET.
We perform TSPITR until 9:00 AM but what about the transaction of other tables because all objects in tablespace will be rollback until 9:00 AM.