Tablespace Point-in-time 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 :

in TBPITR, we take the tablespace back in time or before the wrong transactions are issued.

 

 

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.

 

Lab Activity:=============

 

 

CREATE TEST TABLESPACE.

 

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

 

 

create a new emp table.

 

create table testdemo (id int, name varchar2(200));

insert into testdemo(id , name) VALUES(1,’test’);

 

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

 

RMAN> backup database plus archivelog format '/u01/backup/oemdb_%U';

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

SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';

 

Session altered.

 

SQL> select sysdate from dual;

 

SYSDATE

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

24/06/2024 10:44:19

 

drop tablespace TBPITR including contents and datafiles;

 

CONNECT TO RMAN AND START RECOVERY:

 

 

run{

recover tablespace TBPITR until time "to_date('22-jun-24 11:01:53','dd-mon-rr hh24:mi:ss')"

auxiliary destination '/u01/backup/auxdest';

}

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;