Oracle: Recover Database Using RMAN Until a Log Sequence Number

In this scenario we will recover database using RMAN until a log sequence number SCN.

First create a table called customers and count the number of rows.

SQL> select count(*) from customers;

  COUNT(*)
----------
         4

SQL> alter system switch logfile;

System altered.

Check whether database is in archivelog mod or not.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            D:\app\SantoshTiwari\oradata\arc
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence           2

Now delete table customers.

SQL> delete customers;

4 rows deleted.

SQL> commit;

Commit complete.


SQL> select count(*) from customers;

  COUNT(*)
----------
         0

Now check the sequence no to apply so that we can recover table as it was before deletion.

SQL> select sequence#,first_change#, to_char(first_time,'HH24:MI:SS') from v$log order by
3;

 SEQUENCE# FIRST_CHANGE# TO_CHAR(
---------- ------------- --------
         1       1025275 18:31:53
         2       1025651 18:34:56
         3        1025944 18:37:59

Shut down the database and start in mount state.


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  606806016 bytes
Fixed Size                  1376268 bytes
Variable Size             398462964 bytes
Database Buffers          201326592 bytes
Redo Buffers                5640192 bytes
Database mounted.


Recover using SQL prompt.

SQL> recover database UNTIL CHANGE 2;
Media recovery complete.

-----------------------------------------------
Recover using RMAN

RMAN> run {
2> set until sequence=2;
3> restore database;
4> recover database;
5>alter database open resetlogs;
6> }

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

SQL> select count(*) from customers;

  COUNT(*)

----------

         4