Standby Recovery To A Particular SCN/Time

Posted: March 24, 2013 in StandBy
Tags: , ,

In this post, i will be recovering a standby to a particular SCN/Time to recover from a truncate table situation when we have a Physical standby implemented with some time delay between archive shipping and getting applied. I have a time delay of 120 Minutes.
Below are the steps i followed on PRIMARY database. Well, original blog from which i got these steps is of laurentschneider

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
A_OB                           TABLE

SQL>
SQL> create table A_AB as select * from dba_objects;

Table created.

SQL> select count(*) from A_AB;

  COUNT(*)
----------
     13798

SQL> commit;

Commit complete.

SQL>
SQL>
SQL> alter system switch logfile;

System altered.

SQL>
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
   21661731

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
23-MAR-13 10.12.56.196491 AM +01:00

SQL> truncate table A_AB;

Table truncated.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
   21661836

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
23-MAR-13 10.13.28.984307 AM +01:00

SQL> alter system switch logfile;

System altered.

SQL> select count(*) from A_AB;

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

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

I have created table A_AB at 23-MAR-13 10:12:56 with SCN 21661731 and truncated by 10:13:38 with SCN 21661836. SO, i will be applying archived logs at standby till 10:12:56. At standby database, i have followed the following steps:

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> ALTER DATABASE RECOVER automatic standby database until time '2013-03-23 10:12:56';

Database altered.

Following is the output from standby alert.log file

Managed Standby Recovery Canceled (PLMQDBS)
Completed: alter database recover managed standby database cancel
Sat Mar 23 10:19:31 2013
ALTER DATABASE RECOVER automatic standby database until time '2013-03-23 10:12:56'
Media Recovery Start
 started logmerger process
Sat Mar 23 10:19:31 2013
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 8 slaves
Media Recovery Log /u05/oradata/CORE/flash/PLMQDBST/archivelog/2013_03_23/o1_mf_1_3272_8ntv43gt_.arc
Media Recovery Log /u05/oradata/CORE/flash/PLMQDBST/archivelog/2013_03_23/o1_mf_1_3273_8ntv43ht_.arc
Sat Mar 23 10:19:44 2013
Media Recovery Log /u05/oradata/CORE/flash/PLMQDBST/archivelog/2013_03_23/o1_mf_1_3274_8ntv43kb_.arc
Media Recovery Log /u05/oradata/CORE/flash/PLMQDBST/archivelog/2013_03_23/o1_mf_1_3275_8ntv6d5h_.arc
Media Recovery Log /u05/oradata/CORE/flash/PLMQDBST/archivelog/2013_03_23/o1_mf_1_3276_8ntv6dyj_.arc
Media Recovery Log /u05/oradata/CORE/flash/PLMQDBST/archivelog/2013_03_23/o1_mf_1_3277_8ntvmv0g_.arc
Media Recovery Log /u05/oradata/CORE/flash/PLMQDBST/archivelog/2013_03_23/o1_mf_1_3278_8ntwg1fl_.arc
Sat Mar 23 10:19:48 2013
Incomplete Recovery applied until change 21661756 time 03/23/2013 10:12:56
Media Recovery Complete (PLMQDBS)
Completed: ALTER DATABASE RECOVER automatic standby database until time '2013-03-23 10:12:56'
Sat Mar 23 10:23:45 2013

Now, i just need to check the contents of table A_AB. I have converted standby into Snapshot standby and then back to physical standby.

SQL> select * from v$restore_point;

no rows selected

SQL> alter database convert to snapshot standby;

Database altered.

SQL> select * from v$restore_point;

       SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME                             RESTORE_POINT_TIME  PRE NAME
---------- --------------------- --- ------------ -------------------------------- ------------------- --- ------------------------------------
  21661755                     1 YES     15941632 23-MAR-13 10.23.45.000000000 AM  10:23:45            YES SNAPSHOT_STANDBY_REQUIRED_03/23/2013 

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 4175568896 bytes
Fixed Size                  2213384 bytes
Variable Size             805308920 bytes
Database Buffers         3355443200 bytes
Redo Buffers               12603392 bytes
Database mounted.
Database opened.
SQL> conn backup/backup
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
A_AB                           TABLE
A_OB                           TABLE

SQL> select count(*) from A_AB;

  COUNT(*)
----------
     13798

SQL> select count(*) from A_OB;

  COUNT(*)
----------
     13795

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 4175568896 bytes
Fixed Size                  2213384 bytes
Variable Size             805308920 bytes
Database Buffers         3355443200 bytes
Redo Buffers               12603392 bytes
Database mounted.
SQL> alter database convert to physical standby;

Database altered.

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 4175568896 bytes
Fixed Size                  2213384 bytes
Variable Size             805308920 bytes
Database Buffers         3355443200 bytes
Redo Buffers               12603392 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL>

Below are lines from alert.log file of standby database.

alter database convert to snapshot standby
Starting background process RVWR
Sat Mar 23 10:23:45 2013
RVWR started with pid=24, OS id=29360282
Allocated 15937344 bytes in shared pool for flashback generation buffer
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_03/23/2013 10:23:45
krsv_proc_kill: Killing 2 processes (all RFS)
Sat Mar 23 10:23:48 2013
Deleted Oracle managed file /u05/oradata/CORE/flash/PLMQDBST/archivelog/2013_03_23/o1_mf_1_3279_8ntwl8sd_.arc
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after incomplete recovery UNTIL CHANGE 21661756
Resetting resetlogs activation ID 165702244 (0x9e06a64)
Online log /u02/oradata/CORE/PLMQDBS/redo01a.log: Thread 1 Group 1 was previously cleared
Online log /u03/oradata/CORE/PLMQDBS/redo01b.log: Thread 1 Group 1 was previously cleared
Online log /u02/oradata/CORE/PLMQDBS/redo02a.log: Thread 1 Group 2 was previously cleared
Online log /u03/oradata/CORE/PLMQDBS/redo02b.log: Thread 1 Group 2 was previously cleared
Online log /u02/oradata/CORE/PLMQDBS/redo03a.log: Thread 1 Group 3 was previously cleared
Online log /u03/oradata/CORE/PLMQDBS/redo03b.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 21661754
Sat Mar 23 10:23:49 2013
Setting recovery target incarnation to 2
WARNING: STANDBY_FILE_MANAGEMENT initialization parameter is
not set to the value "AUTO".
This may cause recovery of the standby database to terminate
prior to applying all available redo data.
It may be necessary to use the ALTER DATABASE CREATE DATAFILE
command to add datafiles created on the primary database.
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: alter database convert to snapshot standby
About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s