Tablespace Point-in-time Recovery

If you already know about database point in time recovery, the issue with the database point in time recovery (DBPITR) is that the database is not accessible to users.

• Why Tablespace PITR Recovery?

• How it works?

• RMAN TSPITR

• Lab Activity

• CREATE TEST TABLESPACE & USER FOR ACTIVITY

• DROP THE TABLESPACE AND START TBPIT PROCESS

• CONNECT TO RMAN AND START RECOVERY

Why Tablespace PITR Recovery?

Let us assume that you have a problem only with one user and the transaction that the user executed has impacted one table that reside under one tablespace or one data file. So rather than performing the entire database point in time recovery, We can perform single tablespace point in time recovery just before the transactions were issued. In this way, only the affected tablespace will not be available for the users. Rest all database will still be up and running.

How it works?

TBPITR is different from DBPITR

In DBPITR, the entire database is restored to back in time. In TBPITR, we take the tablespace back in time or before the wrong transactions are issued.

It’s not straight restore and recover

Once tablespace is dropped, you cannot restore at via RMAN as details of the tablespace are removed from the control file.

Let us say somebody dropped the entire tablespace and then you try to issue the command restore tablespace from RMAN. The command will not work because once you drop the tablespace, control files will update that this tablespace does not exist. This means even if you try to restore it from RMAN, the tablespace will not be restored.

TSPITR Recovery Methods

There are three methods which you can use to recover dropped tablespace:

1. Create DB clone with PITR before tablespace drop, export tablespace from clone DB and import into original database

2. Perform entire database Point In Time recovery. This lead to downtime + loss of data

3. Use automated RMAN TSPITR method

RMAN TSPITR

Its completely automated method. You just have to run the command TBPITR and everything will be taken care.

To perform RMAN TSPITR, you need following

• Timestamp or SCN when tablespace was dropped → You can get this information form alert log

• A location with same space as your database size

On the same server you need another Mount point where a database of the same size will be cloned. Once you run the TSPITR, it will Create a clone of the database in the location that you specify and then it will export the tablespace, import it into original database and then clean the cloned instance. And all the steps are automatically done by RMAN.

Note: Make sure the location that you choose for the auxiliary instance of the cloned instance should have the same space or required space as an prod DB.

Lab Activity

In this activity, we will be doing the tablespace point in time recovery. Let us start.

CREATE TEST TABLESPACE & USER FOR ACTIVITY

create tablespace TBPITR datafile '/u01/app/oracle/oradata/proddb/tbpitr.dbf' size 10M;

create user tbtest identified by tbtest;

alter user tbtest default tablespace TBPITR;

grant connect, resource to tbtest;
grant select on scott.emp to tbtest;

Now connect sqlplus with tbtest user and create a new emp table by selecting scott.emp table

SQL> conn tbtest/tbtest

SQL> create table emp as select * from scott.emp;

Take RMAN backup for the activity. I am taking database backup in a specific location

backup database plus archivelog format '/u02/rman_bkp/proddb/proddb_%U';

Check the database size and make sure you have a destination with same or more free space.

Query to check database size

col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/

DROP THE TABLESPACE AND START TBPIT PROCESS

Now we will drop the tablespace and then we will see that we can recover it via RMAN or not.

drop tablespace TBPITR including contents and datafiles;

If you connect to tbtest user and query emp table, you will get below error

SQL> conn tbtest/tbtest

SQL> select * from emp:
select * from emp
              *
ERROR at line 1:
ORA-00942: table or view does not exist

Let us try to restore the tablespace using RMAN. Connect to RMAN and issue below command

RMAN> restore tablespace TBPITR;
ERROR:
RMAN-20202: Tablespace not found in recovery catalog

Reason behind the above error is that you control file is already been updated that this tablespace does not belong. Even though you have the backup, still you will not be able to restore as it is cleared from your control file.

Open the alert log and check the time when the tablespace was dropped. 

In my activity, the timestamp is Mon Jan 16 11:01:542017

We will change this time as Mon Jan 16 11:01:53 2017, One second before the time when the tablespace is dropped. Now we will restore this tablespace till this time.

CONNECT TO RMAN AND START RECOVERY

Before you fire below command, make sure you have same or more space in auxiliary destination as your original database.

The auxiliary destination is the location where RMAN will automatically create the destination for the clone instance. Then export the tablespace from the cloned instance. It will put it back to the original location and then RMAN will clean this auxiliary destination also.

run{
recover tablespace TBPITR until time "to_date('16-jan-17 11:01:53','dd-mon-rr hh24:mi:ss')"
auxiliary destination '/u02/auxdest';
}

When you fire the above command, RMAN will automatically decide one dummy instance name and start that instance. Then it will restore all the data files. It will create a database. DB will be created as per the given time which is just before the tablespace was dropped. So the tablespace will be there inside the DB. Then it will export the tablespace and put it into the production database.

Once the DB clone is done. Then it will start the export of the tablespace. Once the export is done, it will start to import the tablespace into the production DB. Then it will delete the cloned files and instances.

Check the original database if the tablespace restore is done or not

select status,tablespace_name from dba_tablespaces;

Once tablespace restore is done, it will be in offline mode. Make it online

alter tablespace TBPITR online;

select status,tablespace_name from dba_tablespaces;

Now you can check whether you are able to query from the emp table as tbtest user. You will able to do it.