Recover Standby Database until Specific SCN or Timestamp

Owned by Anton Els (Unlicensed)
Last updated: Jan 19, 2014 by Anna Macnaughtan (Unlicensed)
2 min read

One of the new features in Dbvisit Standby version 7 is the option to recover the standby database up until a specified SCN or TIMESTAMP.

This can be especially useful where you have a financial system with a standby database being used for reporting. Once the batch processing is complete on your primary database the batch process creates a text file on the standby server indicating the SCN or TIMESTAMP to which the standby should recovered. This can then allow the DBA to recover the standby database automatically up until this specified SCN or TIMESTAMP.

The standby database can then be opened read-only and used for reporting. This is useful if you would like to view data at a specific point in time.

 

Two dynamically created files can be used to save specific SCN or timestamp. Files full names are specified by global variables RECOVER_UNTIL_SCN_FILE and RECOVER_UNTILTIME_FILE:

RECOVER_UNTIL_TIME_FILE = $DBVISIT_BASE/standby/log/recover_until_time.txt by default
RECOVER_UNTIL_SCN_FILE = $DBVISIT_BASE/standby/log/recover_until_scn.txt by default

 

Below is an example of using the until time option (Expected format is YYYY-MM-DD:HH24:MI:SS). In this example we manually create a text file with a timestamp to which the standby should be recover to.

The standby will not recover past this time, until this file has been removed or updated to a later time.

 

Below is the contents of the recover_until_time.txt file as well as the output form a dbvisit run on the standby server when this file is in place:

oracle@dbvlin502[/usr/dbvisit/standby]: cat ./log/recover_until_time.txt
2013-08-22:23:38:32

oracle@dbvlin502[/usr/dbvisit/standby]: ./dbvisit proddb
=============================================================
Dbvisit Standby Database Technology (7.0.01.11254) (pid 7655)
dbvisit started on dbvlin502: Fri Aug 23 11:45:20 2013 ()
=============================================================

>>> Log file(s) for proddb from dbvlin501 will be applied to dbvlin502
    Taking into account RECOVER_UNTIL_TIME=2013-08-22:23:38:32 set in file
    recover_until_time.txt
201308231145 - Log seq 5440 thread 1 applied to standby database proddb.

>>> Dbvisit Archive Management Module (AMM)

    Config: number of archives to keep      = 0
    Config: number of days to keep archives = 7
    Config: diskspace full threshold        = 80%

Processing /u01/app/oracle/archive/proddb...
    Archive log dir: /u01/app/oracle/archive/proddb
    Total number of archive files   : 23
    Number of archive logs deleted = 0
    Current Disk percent full       : 22%

=============================================================
dbvisit ended on dbvlin502: Fri Aug 23 11:45:23 2013
=============================================================

 

The same process can be followed when using an SCN number.