Performing Database point-in-time recovery (DBPITR) - SCN-Based Recovery

In this post I want to explain how to do SCN-Based recovery to a particular database SCN. In this scenario we want to recover database to specify SCN. For example we’ll create a table and drop it .

And then we recover dropped table .

DBPITR is sometimes called incomplete recovery because it does not use all of the available redo or completely recover all changes to your database.

SQL> Create table  TEST_SCN

    AS

    SELECT * FROM HR.EMPLOYEES;

Table created.

SQL> SELECT COUNT(*) FROM TEST_SCN;

 COUNT(*)

----------

      3424

SQL> SELECT GROUP# ,SEQUENCE# ,STATUS , ARCHIVED , FIRST_CHANGE# FROM V$LOG;

    GROUP#  SEQUENCE#   STATUS           ARC    FIRST_CHANGE#

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

         1          0                        UNUSED            YES       0

         2          0                        UNUSED            YES       0

         3          1                        CURRENT          NO         678956

SQL> ALTER SYSTEM SWITCH LOGFILE;

SQL> SELECT GROUP# ,SEQUENCE# ,STATUS , ARCHIVED , FIRST_CHANGE#

    FROM V$LOG;

    GROUP#  SEQUENCE# STATUS           ARC          FIRST_CHANGE#

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

         1          2 ACTIVE                                YES            689996

         2          3 CURRENT                             NO             689998

         3          1 ACTIVE                                YES            678956

Copy SCN  689998  because we need it for recovery.

RMAN> BACKUP DATABASE PLUS ARCHIVELOG ALL;

SQL> DROP TABLE HR.TEST_SCN PURGE;

 SQL> SHUT IMMEDIATE;

SQL> STARTUP MOUNT;

REMEMBER WE HAVE COPIED THE CURRENT SCN NUMBER

RMAN> RESTORE DATABASE UNTIL SCN 689998;

RMAN> RECOVER DATABASE UNTIL SCN 689998;

RMAN> ALTER DATABASE OPEN RESETLOGS;

SQL> SELECT COUNT(*) FROM TEST_SCN;

  COUNT(*)

----------

      3424

Also If you have set the initialization parameter log_checkpoints_to_alert to TRUE, then every time a log switch occurs, Oracle will write the SCN of each checkpoint to your target database alert.log file. This feature can be handy when trying to determine historical checkpoint SCN activity.