TABLESPACE POINT IN TIME RECOVERY

           I have a table in my production database which is being updated every week. By mistake one of the developers dropped that table yesterday(Monday) morning around 10 am. So, my manager asked me to get the table back into the database as that table is very important for some transactions. Now I have two options to get the table back into the database.

a.        Duplicate the database in the stage server using the Sunday night full backup and recover it until the     morning 10am using the archive logs and export the table and import the table in the production database.

b.      Tablespace point in time recovery on the production database itself.

If there is enough space in the stage server to duplicate the database and if the database is small,we can choose the option a.If the database is large it takes more time as it needs to duplicate all the tablespaces. But I did not have enough time and space in the stage server to choose the option a.

If there is enough space in the production server and if you have given some maintenance window to recover the tablespace, we can choose the option b. I have enough space in the production server and the tablespace was a small one. So, I opted the b option. 

Here is the process I followed:

Target database:DBACLASS

Auxiliary database: AUX

Version:10.2.0

1.       Create the pfile for an auxiliary instance from the target database in which we want to do the tablespace point in time recovery.

SQL>create pfile from spfile;

2.       Modify the pfile for the auxiliary database.

InitAUX.ora

----------------

large_pool_size=150M

shared_pool_size=150M

compatible='10.2.0.1.0'

control_files='/u02/oradata/AUX/control01.ctl','/u02/oradata/AUX/control02.ctl','/u02/oradata/AUX/control03.ctl'

db_block_size=8192

db_name='DBACLASS'

remote_login_passwordfile='EXCLUSIVE'

#db_unique_name='DBACLASS'

DB_FILE_NAME_CONVERT= ('/u02/oradata/DBACLASS/DBACLASS/','/u02/oradata/AUX/')

LOG_FILE_NAME_CONVERT= ('/u02/oradata/DBACLASS/DBACLASS/','/u02/oradata/AUX/')

LOG_ARCHIVE_DEST_1='LOCATION=/u02/oradata/AUX/arch'

LOG_ARCHIVE_FORMAT=%r_%t_%s.arc

service_names='AUX'

LOCK_NAME_SPACE ='DBACLASS'

db_name:

The database name of the auxiliary database should be same as that of the target database.

LOCK_NAME_SPACE:

When the primary database and auxiliary database (cloned database) is in the same server with the same name, then LOCK_NAME_SPACE parameter has to be added in the auxiliary parameter file. Because Oracle check for something unique to differ among the databases when the two databases have same name. Distributed lock manager generates the lock names by using this parameter.

  DB_FILE_NAME_CONVERT:

This parameter is used to create the database files in the specified location for clone database.

3.       Export the oracle_sid for the auxiliary database.

$ORACLE_SID=AUX

4.       Create the password file for the auxiliary database in $ORACLE_HOME/dbs.

$$ORACLE_HOME/dbs /orapwd file=orapwAUX password=abacus entries=5

5.       Configure the listener and tnsnames for the AUX database.

Listener: 

SID_LIST_AUX =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (SID_NAME = AUX)

      (ORACLE_HOME = /opt/app/oracle/product/10.2.0)

      (PROGRAM = extproc)

    )

  )

AUX =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.210)(PORT = 1521))

    )

  )

Tnsnames:

AUX =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.210)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = AUX)

      (UR = A)

    )

  ) 

6.       Create table TABLE1 in the users tablespace in the Primary database (DBACLASS).

SQL>create table TABLE1(num number, tname varchar2(20));

Table created.

7.       Insert some rows in the table TABLE1.

SQL>select count(*) from TABLE1;

Count(*)

---------

6 

8.       Connect to the rman and take the backup of the database.(dg2.abacus.com)

$rman catalog rmn/rmn target sys/abacus@DBACLASS 

Rman>backup database;

9.       Now drop the table TABLE1 in the users tablespace(DBACLASS).

SYS>set time on;

09:47:41 SQL> drop table table1; 

Now if you want the table TABLE1 to be restored into the database. TABLESPACE point in time recovery has to be done until 9:47.

10.    Now start up the AUX database in nomount state.

SQL>startup nomount;

11.   Now connect to the rman catalog and run the tspitr.rmn( RMAN server)

tspitr.rmn

------------

     CONNECT AUXILIARY sys/****@aux

CONNECT TARGET sys/****@dbaclass

CONNECT CATALOG rmn/***@acdbrmn

 run{

  allocate auxiliary channel c1 device type disk;

  recover tablespace users until time "TO_DATE('2011-DEC-22:21:47:45','yyyy-mon-dd:hh24:mi:ss')";

  }

$rman catalog rmn/rmn target sys/abacus@DBACLASS auxiliary sys/abacus@AUX 

Rman>@tspitr.rmn 

12.   After that check the tablespace USERS  is in offline mode. Make it as online and check for the table TABLE1 in the primary database(DBACLASS). 

SQL>alter tablespace users online;

SQL>select count(*) from table1;

Mechanism that follows by TSPITR: 

1.       Makes the tablespace offline in the target database which is to recovered.

2.       Restores the control file from the rman database backup before the target time specified in the recover until time statement for clone database.

3.       Restores the db files for the auxiliary database to the specified location in the DB_FILE_NAME_CONVERT.

4.       Then it recover the database files until the specified time in the tablespace recovery statement and the database opened with reset logs option.

5.       Exports the meta data of the  tablespace which has to be recovered and the auxiliary database is shut down.

Eg:

'exp userid =\"sys/abacus@aux as sysdba\" point_in_time_recover=y tablespaces=USERS file=tspitr_a.dmp';

6.       Imports the metadata into the target database from the auxiliary database. And removes the auxiliary database files.

7.       Makes the recovered tablespace offline. 

Objects to check after the TSPITR: 

 When the tablespace is recovered upto a certain time t, then the objects those are created after the time t will be lost. So, we need to take the backup of those tables before the TSPITR.

Check the objects that created after time t by using the below query:   

select

OWNER,NAME, CREATION_TIME, TABLESPACE_NAME

from TS_PITR_OBJECTS_TO_BE_DROPPED

 where tablespace_name=’****’ and  creation_time >= to_date(********);

           Issues may face:

--------------------- 

If $ORACLE_HOME/dbs/lk$ORACLE_SID is existed, then remove it.  If it is not removed an error occurs while opening the auxiliary database that don’t open database in exclusive mode.

               

Note:

------- 

The AUX database is cloned into the directory specified in the DB_FILE_NAME_CONVERT. After the TSPITR is completed, automatically rman deletes the db files,redo logs, controfiles , temp files of the AUX database(auxiliary).