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;

Start up and shut down the Oracle Database Instance

 

1. Background SQL>  shutdown

An Oracle database system consists of an Oracle database and an Oracle instance. An Oracle instance (also known as a database instance) contains the set of Oracle Database background processes that operate on the stored data and the shared allocated memory that those processes use to do their work. In this tutorial you learn how to manage your Oracle Database instance.

 

                               

1

Start up and shut down the Oracle Database Instance

You may need to shut down the database instance to perform certain administrative tasks. To shut down and restart the database instance, perform the following steps:

2.      Open a terminal window as the oracle user. Execute the oraenv command to set the environment variables.

3.      Log in to SQL*Plus as the SYSDBA user

4.      Issue the SHUTDOWN command to close the database and shut down the instance.

5. SQL>  shutdown

6. Issue the STARTUP command to start the instance and open the database.

SQL> startup