• Arun Kumar

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?

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


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 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 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


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;
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.


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.

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.

Recent Posts

See All

MySQL Installation on Linux

This article demonstrates MySQL installation on Oracle Enterprise Linux 6.5 version. Installation of MySQL Make sure you are able to connect internet via virtual machine. Try to ping google.com and pr

Add ISO image storage repository on XenServer

If you have used VMware ESXI, its very easy to upload ISO files to the data store and create VMs. With XenServer, you must create your own ISO repository to upload ISO files. Create ISO directory Acce

Automate RMAN Backups using Shell Scripts

In a real environment, off course you will not manually trigger all the Oracle database backups. You need an automated mechanism to trigger RMAN backups. In this article we will look at RMAN backup au

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis