Can we do Point In Time Recovery of Datafiles or Tablespaces

Hi guys,

This is an extension to the article I’ve posted last week: https://querycsm.wordpress.com/2012/08/29/how-restoring-individual-datafile-effects-the-constraints-and-logical-consistency/

I tried to restore individual datafile from some backup. But I tried to replicate a scenario:  My restore operation will take out a record from a table with primary key and that record to be removed will have dependent reference data in another table.

When I tried to restore the datafile, which contains the PK table, Oracle asked for recovery followed by the restore. When I recovered the file, I got the record (The PK one) which I tried to delete earlier into the respective table. So there’s no harm for the referential integrity constraints happened.

My intention of disturbing the PK-FK relation is not satisfied yet…

So, I tried doing a PITR (Point In Time Recovery) of the data file like below

SQL> select to_char(sysdate,’dd:mm:yyyy – HH:mi:ss’) from dual;

TO_CHAR(SYSDATE,’DD:M


03:09:2012 – 04:26:57

SQL> select * from dept_non_cons;

ID NAME


——————————
10 maths
20 physics
50 computers

Now I’ve inserted 2 rows

SQL> insert into dept_non_cons values (70,’Chemistry’);

1 row created.

SQL> insert into dept_non_cons values (60,’Databases’);

1 row created.

SQL> commit;

SQL> select * from dept_non_cons;

ID NAME


——————————
70 Chemistry
60 Databases
10 maths
20 physics
50 computers

SQL> select to_char(sysdate,’dd:mm:yyyy – HH:mi:ss’) from dual;

TO_CHAR(SYSDATE,’DD:M


03:09:2012 – 04:27:12

Now I’m taking this datafile offline and restoring from an old backup.

That backup contains these 3 records only

10 maths
20 physics
50 computers

And now I’m recovering like

RMAN> run
2> {
3> set until time “to_date(’09 03 2012 04:26:57‘,’mm dd yyyy hh24:mi:ss’)”;
4> recover datafile 9;
5> }

So, Technically we have only 3 records till 04:26:57

but after recovery, when I did this

SQL> alter database datafile 9 online;

Database altered.

SQL> select * from dept_non_cons;

ID NAME


——————————
70 Chemistry
60 Databases
10 maths
20 physics
50 computers

How come I got Chemistry and Databases here !!! ??? Strange right??

The answer is

Oracle won’t allow PITR in case of Datafiles.

If you wish / have to do an incomplete recovery, you always have to restore ALL the datafiles, recover them until desired point in time and then open the database using resetlogs to sync all the datafiles with controlfiles and redo logs.

So there’s no question of having missing logical consistency imposed by constraints.

What about PITR in case of tablespaces? According to Oracles’ documentation http://docs.oracle.com/cd/B19306_01/backup.102/b14191/rcmtspit.htm , We can do PITR of Tablespaces. So how Oracle handles this kind of problems there?

The answer for this is,

Oracle checks for this kind of anomalies before doing PITR of tablespaces. There’s a view called SYS.TS_PITR_CHECK which will record the anomalies for our reference.

In my case the view reported exactly what I required

SELECT *
FROM SYS.TS_PITR_CHECK
WHERE (
TS1_NAME IN (‘TST2’)
AND TS2_NAME NOT IN (‘TST2’)
);SQL> 2 3 4 5 6

own1 name1 subname1 obj1type ts1_na name2 subname2 obj2type own2 ts2_na cname reason
—— —– ——– ——– —— —– ——– ——– —— —— —– ————————-
SYS DEPT TABLE TST2 STUDE TABLE SYS RMANTS STUD_ constraint between tables

NT T FK not contained in recovery
set

SYS DEPT TABLE TST2 DEPT_ INDEX SYS RMANTS Table and Index enforcing
PK T primary key constraint
not in same tablespace

Oracle functions this way to make sure the logical consistency is maintained.

You’re welcome to ask questions/provide feedback.

have nice day.

CSM

Advertisements

One Response to Can we do Point In Time Recovery of Datafiles or Tablespaces

  1. Pingback: How restoring individual datafile effects the constraints and logical consistency ??? « queryCSM

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: