RMAN Point-In-Time-Recovery PITR

RMAN PITR allows you to specify a SCN number or a recovery time till when you want to recovery database. The PITR is used when you want to restore database to a time just before the crash happened.

• Take DB FULL Backup

• Create New User & Table

• Simulate Failure

• Start RMAN PITR

Take DB FULL Backup

Connect to the target DB and take DB full backup

backup database plus archivelog;

Once backup is completed, check backup tag via below command

list backup of database summary;

In our case, the backup tag is TAG20170115T105254

Create New User & Table

We will first find the current system data and time and later create a new user. We assume that database got corrupted while creating the new user and we will recover the database to the system data and time just before the new user creation

SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;

2017-01-15 10:54:53

SQL> create user ngr identified by ngr;
SQL> grant connect, resource, create session to ngr;
SQL> conn ngr/ngr
SQL> create table test(serial number(2),name varchar2(5));
SQL> insert into test values(1,'one');
SQL> insert into test values(2,'Two');
SQL> insert into test values(3,'Three');
SQL> insert into test values(4,'Four');
SQL> commit;

Simulate Failure

Delete all the datafiles from server. Connect as sysdba and

SQL> select name from v$datafile;

rm -rf <DF locations>

Start RMAN PITR

Kill the DB instance, if running. You can do shut abort or kill pmon at OS level

Start the DB instance and take it to Mount stage

Connect to RMAN and issue below command

run
{
RESTORE DATABASE from tag TAG20170115T105254;
RECOVER DATABASE UNTIL TIME "TO_DATE('2017-01-15 10:54:53', 'YYYY-MM-DD HH24:MI:SS')";
sql 'ALTER DATABASE OPEN RESETLOGS';
}