How restoring individual datafile effects the constraints and logical consistency ???

Hi all,

Last week when I was in a discussion with one of my friends, who is an Oracle Database consultant, a question turned up regarding Oracle’s feature of restoring individual datafiles.

Oracle documentation says, we can restore individual datafiles if that particular file is corrupt or not available. Queries can be issued against, transactions can be performed on all objects in a tablespace except the ones present in the file being restored. This helps us in reducing the downtime.

Of-course this is a great feature and contributes towards the Oracle’s vision of Maximum Availability. But the question that’s turned up in our discussion is,

what if we have 2 tables in two different datafiles, they have referential integrity constraints defined on them and one of the files is being restored from a backup?

To be clear I have a table student in datafile 7 and dept in datafile 8. The structure of the tables is

DEPT

ID             NOT NULL          NUMBER                      PK

NAME                                     VARCHAR2(10)

STUDENT

ID                                           NUMBER
NAME                                  VARCHAR2(10)
DID                                        NUMBER(10)                 FK

I’m planning to restore datafile 7 from old backup as i lost that file. I couldn’t access the object DEPT as the datafile in which DEPT is present is being restored. But I can access the table STUDENT as that is in a different datafile (A different Tablespace probably).

So, if i try to insert the data which is not complying the PK-FK releation, while the restore operation is going on, what Oracle’ll do?

  • Won’t it allow?
  • Will it allow but validate the data after the restore
  • Will it disable the constraints and allow the transactions but error out if we/Oracle try to enable the constraints again?

So, I thought of testing this myself and simulated the operation. See the exercise below

I’m creating a tablespace RMANTST with a datafile

SQL> create tablespace RMANTST datafile ‘/XXX/rmantst1.dbf’ size 10M;

Tablespace created.

Now create a table DEPT in RMANTST.

SQL> create table dept (id number,name varchar2(10), constraint dept_pk primary key (id)) tablespace RMANTST;

Table created.

My intention is to create another table in a different datafile of the same tablespace. so I made the current datafile offline and added a new datafile to this tablespace. After that I’ve created a table in the tablespace RMANTST so my new table will go into the other datafile.

SQL> alter database datafile ‘/XXX/rmantst1.dbf’ offline;

Database altered.

SQL> alter tablespace RMANTST add datafile ‘/XXX/rmantst2.dbf’ size 10M;

Tablespace altered.

SQL> create table student (id number,name varchar2(10),did number(10), constraint STUD_fk FOREIGN KEY (did) references dept(id)) tablespace RMANTST;

Table created.

SQL> alter database datafile ‘/XXX/rmantst1.dbf’ online;

Database altered.

SQL> select a.FILE_NAME,b.SEGMENT_NAME from dba_data_files a,dba_extents b where a.file_id=b.file_id and b.segment_name=’DEPT';

FILE_NAME
——————————————————————————–
SEGMENT_NAME
——————————————————————————–
/XXX/rmantst1.dbf

DEPT
SQL> select a.FILE_NAME,b.SEGMENT_NAME from dba_data_files a,dba_extents b where a.file_id=b.file_id and b.segment_name=’STUDENT';

FILE_NAME
——————————————————————————–
SEGMENT_NAME
——————————————————————————–
/XXX/rmantst2.dbf

STUDENT

Now I’ve inserted some sample data

SQL> insert into dept values (10,’maths’);

1 row created.

SQL> insert into dept values (20,’physics’);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dept;

ID NAME
———- ———-
10 maths
20 physics

SQL> insert into student values (110,’tom’,10);

1 row created.

SQL> insert into student values (120,’jonathan’,20);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from student;

ID NAME DID
———- ———- ———-
110 tom 10
120 jonathan 20

I’ve taken the backup of the datafile 7 (rmantst1)

 rman target /

Recovery Manager: Release 11.2.0.3.0 – Production on Wed Aug 29 06:44:00 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: CSM2 (DBID=105970236)

RMAN> backup datafile 7 format ‘/XXX/bkp_rmantst1′;

Starting backup at 29-AUG-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=3 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/XXX/rmantst1.dbf
channel ORA_DISK_1: starting piece 1 at 29-AUG-12
channel ORA_DISK_1: finished piece 1 at 29-AUG-12
piece handle=/XXX/bkp_rmantst1 tag=TAG20120829T064454 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 29-AUG-12

RMAN> exit
Recovery Manager complete.

Now i’m putting the datafile 7 into offline mode as I want to restore it

SQL> alter database datafile ‘/XXX/rmantst1.dbf’ offline;

Database altered.

Checking the constraints once to make sure they’re enabled.

SQL> select CONSTRAINT_NAME,status from dba_constraints where CONSTRAINT_NAME like ‘%STUD%';

CONSTRAINT_NAME STATUS
—————————— ——–
STUD_FK ENABLED

SQL> select CONSTRAINT_NAME,status from dba_constraints where CONSTRAINT_NAME like ‘%DEPT%';

CONSTRAINT_NAME STATUS
—————————— ——–
DEPT_PK ENABLED

Now I’m trying to insert some data which violates the referential integrity

SQL> insert into student values (130,’tanel’,50);
insert into student values (130,’tanel’,50)
*
ERROR at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: ‘/XXX/rmantst1.dbf’

and if i try to restore without bringing that datafile into offline mode, RMAN obviously throws “ORA-19573: cannot obtain exclusive enqueue for datafile 7″ error.

Here we don’t have a record with dept id 50 in the Department table (PK). When I try to insert a row, Oracle complained that file 7 can’t be read. So Oracle tries to validate the data before inserting/updating and reporting an error if the data required for validation (data from PK table) is not available. So there’s no question of inserting the violating data. This way Oracle guarantees the logical consistence among the tables.

Observe one more typical scenarios here

I’m inserting one more row into the DEPT(Pk table)

SQL> insert into dept values (50,’computers’);

1 row created.

SQL> commit;

Commit complete.

and a row into the FK table

SQL> insert into student values (130 ,’tanel’,50);

1 row created.

SQL> commit;

Commit complete.

What if I want to restore data from backup taken earlier which doesn’t contain this new entry (50,’computers’). How does Oracle behaves in that case

SQL> !strings /XXX/bkp_rmantst1
}|{z
CSM2
TAG20120829T064454
physics,
maths
CSM2
=/tg
RMANTST

See, the backup doesn’t have “computers”

Now I’m restoring the datafile

RMAN> restore datafile 7;

Starting restore at 29-AUG-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=52 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 00007 to /XXX/rmantst1.dbf
channel ORA_DISK_1: reading from backup piece /XXX/bkp_rmantst1
channel ORA_DISK_1: piece handle=/XXX/bkp_rmantst1 tag=TAG20120829T064454
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 29-AUG-12

Now if you see the restored datafile

nearside*[itl2t53r]> strings /XXX/rmantst1.dbf
}|{z
CSM2
=/tg
RMANTST
physics,
maths

See it too doesn’t contain “computers”; that’s obvious as the datafile is restored from the backup which doesn’t contain “computers”.

But how Oracle deal with this case???

Oracle is quite intelligent, It won’t allow you to make the datafile online until you recover the datafile

SQL> alter database datafile ‘/XXX/rmantst1.dbf’ online;
alter database datafile ‘/XXX/rmantst1.dbf’ online
*
ERROR at line 1:
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: ‘/XXX/rmantst1.dbf’

Once you’re done with the recovery, you can bring it back online.

SQL> recover datafile 7;
Media recovery complete.
SQL> !strings /XXX/rmantst1.dbf
}|{z
CSM2
=/tg
RMANTST
3 computers,
physics,
maths

 

Please find the part-2 here http://querycsm.wordpress.com/2012/09/03/can-we-do-point-in-time-recovery-of-datafiles-or-tablespaces/

 

CSM

One Response to How restoring individual datafile effects the constraints and logical consistency ???

  1. Pingback: Can we do Point In Time Recovery of Datafiles or Tablespaces « 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

Follow

Get every new post delivered to your Inbox.

Join 331 other followers

%d bloggers like this: