Data Guard with Flashback

This arcticle shows How to setup and use flashback in DATA GUARD setup.

• Enable the flashback at primary database
  SQL> Shutdown immediate
  SQL> Startup mount
  SQL> Alter database flashback on;
  SQL> Alter database open;
  SQL> select flashback_on from v$database;
  
  Enable the flashback at standby database
  SQL> recover managed standby database cancel;
  SQL> Alter database flashback on;
  SQL> select flashback_on from v$database;
  SQL> alter database recover managed standby database disconnect from session;
  
  Need of Flashback of standby database or not ?
  
  Check the current SCN at standby database, if it is lower than the flashback SCN than standby database will be synchronized itself after Primary database flashback. But If it not then there is need to flashback the standby database with 5-10 number lower than flashback SCN to get synchronized.
  
  For example: Here current SCN at standby is 37054496 and primary database is flashback to SCN 37054499 which is higher so no needs to flashback the standby database.
  
  SQL> select name,to_char(current_scn),database_role from v$database;
  NAME TO_CHAR(CURRENT_SCN) DATABASE_ROLE
  --------- ---------------------------------------- ----------------
  DRGTRG1 37054496 PHYSICAL STANDBY
  
  
  
  SQL> select name,to_char(current_scn),database_role from v$database;
  NAME TO_CHAR(CURRENT_SCN) DATABASE_ROLE
  --------- ---------------------------------------- ----------------
  DRGTRG1 37054617 PRIMARY
  
  
  Flashback at Primary database
  
  SQL> shutdown immediate;
  Database closed.
  Database dismounted.
  ORACLE instance shut down.
  
  SQL> startup mount;
  ORACLE instance started.
  Total System Global Area 1191182336 bytes
  Fixed Size 1267140 bytes
  Variable Size 620759612 bytes
  Database Buffers 553648128 bytes
  Redo Buffers 15507456 bytes
  Database mounted.
  
  SQL> FLASHBACK DATABASE TO SCN 37054499;
  Flashback complete.
  
  SQL> alter database open resetlogs;
  Database altered.
  
  SQL> alter system switch logfile;
  System altered.
  
  Entries in the alert.log at Primary database
  
  ALTER DATABASE MOUNT
  Tue May 5 14:16:14 2009
  Setting recovery target incarnation to 2
  Tue May 5 14:16:14 2009
  Successful mount of redo thread 1, with mount id 795560426
  Tue May 5 14:16:14 2009
  Allocated 15937428 bytes in shared pool for flashback generation buffer
  Starting background process RVWR
  RVWR started with pid=16, OS id=14674
  Tue May 5 14:16:14 2009
  Database mounted in Exclusive Mode
  Completed: ALTER DATABASE MOUNT
  Tue May 5 14:18:11 2009
  FLASHBACK DATABASE TO SCN 37054499
  Tue May 5 14:18:11 2009
  Flashback Restore Start
  Flashback Restore Complete
  Flashback Media Recovery Start
  parallel recovery started with 3 processes
  Tue May 5 14:18:13 2009
  Recovery of Online Redo Log: Thread 1 Group 3 Seq 123 Reading mem 0
  Mem# 0: /oracle/oradata/drgtrg1/redo/redo03.log
  Tue May 5 14:18:14 2009
  Recovery of Online Redo Log: Thread 1 Group 1 Seq 124 Reading mem 0
  Mem# 0: /oracle/oradata/drgtrg1/redo/redo01.log
  Tue May 5 14:18:14 2009
  Recovery of Online Redo Log: Thread 1 Group 2 Seq 125 Reading mem 0
  Mem# 0: /oracle/oradata/drgtrg1/redo/redo02.log
  Tue May 5 14:18:14 2009
  Incomplete Recovery applied until change 37054539
  Flashback Media Recovery Complete
  Completed: FLASHBACK DATABASE TO SCN 37054499
  Tue May 5 14:18:31 2009
  alter database open resetlogs
  Tue May 5 14:18:32 2009
  RESETLOGS after incomplete recovery UNTIL CHANGE 37054539
  Resetting resetlogs activation ID 785436917 (0x2ed0d0f5)
  Tue May 5 14:19:40 2009
  Setting recovery target incarnation to 3
  Tue May 5 14:19:40 2009
  Assigning activation ID 795560426 (0x2f6b49ea)
  LGWR: STARTING ARCH PROCESSES
  ARC0 started with pid=20, OS id=14715
  Tue May 5 14:19:40 2009
  ARC0: Archival started
  ARC1: Archival started
  LGWR: STARTING ARCH PROCESSES COMPLETE
  ARC1 started with pid=21, OS id=14717
  LNS1 started with pid=22, OS id=14719
  Tue May 5 14:19:44 2009
  Thread 1 advanced to log sequence 2 (thread open)
  Thread 1 opened at log sequence 2
  Current log# 2 seq# 2 mem# 0: /oracle/oradata/drgtrg1/redo/redo02.log
  Successful open of redo thread 1
  Tue May 5 14:19:44 2009
  MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
  Tue May 5 14:19:44 2009
  ARC0: Becoming the 'no FAL' ARCH
  ARC0: Becoming the 'no SRL' ARCH
  Tue May 5 14:19:44 2009
  ARC1: Becoming the heartbeat ARCH
  Tue May 5 14:19:44 2009
  LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
  Tue May 5 14:19:44 2009
  ARC1: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
  Tue May 5 14:19:44 2009
  SMON: enabling cache recovery
  Tue May 5 14:19:44 2009
  Successfully onlined Undo Tablespace 1.
  Tue May 5 14:19:44 2009
  Expanded controlfile section 11 from 108 to 216 records
  Requested to grow by 108 records; added 4 blocks of records
  Tue May 5 14:19:45 2009
  Dictionary check beginning
  Dictionary check complete
  Tue May 5 14:19:47 2009
  SMON: enabling tx recovery
  Tue May 5 14:19:47 2009
  Database Characterset is AL32UTF8
  Opening with internal Resource Manager plan
  where NUMA PG = 1, CPUs = 4
  replication_dependency_tracking turned off (no async multimaster replication found)
  Starting background process QMNC
  QMNC started with pid=23, OS id=14721
  Tue May 5 14:19:49 2009
  LOGSTDBY: Validating controlfile with logical metadata
  Tue May 5 14:19:49 2009
  LOGSTDBY: Validation complete
  Tue May 5 14:19:50 2009
  db_recovery_file_dest_size of 10240 MB is 2.38% used. This is a
  user-specified limit on the amount of space that will be used by this
  database for recovery-related files, and does not reflect the amount of
  space available in the underlying filesystem or ASM diskgroup.
  Tue May 5 14:19:51 2009
  Completed: alter database open resetlogs
  Tue May 5 14:20:59 2009
  Thread 1 advanced to log sequence 3 (LGWR switch)
  Current log# 3 seq# 3 mem# 0: /oracle/oradata/drgtrg1/redo/redo03.log
  ++++++++++
  Entries in the alert.log at Standby database
  
  Media Recovery Log /oracle/oradata01/drgtrg2/stby_archive/1_124_675957688.arc
  Media Recovery Waiting for thread 1 sequence 125 (in transit)
  Tue May 5 14:15:23 2009
  RFS[2]: Possible network disconnect with primary database
  Aborting archivelog file creation: /oracle/oradata01/drgtrg2/stby_archive/1_125_675957688.arc
  If this a network disconnect, then this archivelog will be fetched again
  by GAP resolution mechanism.
  Redo Shipping Client Connected as PUBLIC
  -- Connected User is Valid
  RFS[3]: Assigned to RFS process 6846
  RFS[3]: Identified database type as 'physical standby'
  Tue May 5 14:19:44 2009
  Redo Shipping Client Connected as PUBLIC
  -- Connected User is Valid
  RFS[4]: Assigned to RFS process 6844
  RFS[4]: Identified database type as 'physical standby'
  Tue May 5 14:19:44 2009
  RFS[3]: New Archival REDO Branch: 686067511 Current: 675957688
  Tue May 5 14:19:44 2009
  Primary database is in MAXIMUM PERFORMANCE mode
  Primary database is in MAXIMUM PERFORMANCE mode
  Tue May 5 14:19:44 2009
  RFS[3]: Archived Log: '/oracle/oradata01/drgtrg2/stby_archive/1_1_686067511.arc'
  RFS[3]: Detected missing archivals for Branch(resetlogs_id): 675957688
  RFS[3]: Last archived SCN: 0:37054497 Last change SCN: 0:37054539
  RFS[3]: New Archival REDO Branch(resetlogs_id): 686067511 Prior: 675957688
  RFS[3]: Archival Activation ID: 0x2f6b49ea Current: 0x2ed0d0f5
  RFS[3]: Effect of primary database OPEN RESETLOGS
  RFS[3]: Managed Standby Recovery process is active
  New incarnation branch detected in ArchiveLog, filename /oracle/oradata01/drgtrg2/stby_archive/1_1_686067511.arc
  Inspection of file changed rdi from 2 to 3
  Setting recovery target incarnation to 3
  Tue May 5 14:19:44 2009
  RFS[3]: Incarnation entry added for Branch(resetlogs_id): 686067511 (drgtrg2)
  Tue May 5 14:19:44 2009
  Setting recovery target incarnation to 3
  Tue May 5 14:19:45 2009
  MRP0: Incarnation has changed! Retry recovery...
  Tue May 5 14:19:45 2009
  Errors in file /oracle/admin/drgtrg2/bdump/drgtrg2_mrp0_5627.trc:
  ORA-19906: recovery target incarnation changed during recovery
  Recovery interrupted!
  Tue May 5 14:19:48 2009
  Errors in file /oracle/admin/drgtrg2/bdump/drgtrg2_mrp0_5627.trc:
  ORA-19906: recovery target incarnation changed during recovery
  Tue May 5 14:20:08 2009
  Managed Standby Recovery not using Real Time Apply
  parallel recovery started with 3 processes
  Media Recovery start incarnation depth : 1, target inc# : 3, irscn : 37054539
  Tue May 5 14:20:08 2009
  Waiting for all non-current ORLs to be archived...
  Media Recovery Waiting for thread 1 sequence 125 branch(resetlogs_id) 675957688
  Fetching gap sequence in thread 1 branch(resetlogs_id) 675957688, gap seq 125-125
  Error 12514 received logging on to the standby
  FAL[client, MRP0]: Error 12514 connecting to (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=traindb2)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=drgtrg1_XPT)(SERVER=dedicated))) for fetching gap sequence
  Tue May 5 14:20:08 2009
  Errors in file /oracle/admin/drgtrg2/bdump/drgtrg2_mrp0_5627.trc:
  ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
  Tue May 5 14:20:38 2009
  Error 12514 received logging on to the standby
  FAL[client, MRP0]: Error 12514 connecting to (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=traindb2)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=drgtrg1_XPT)(SERVER=dedicated))) for fetching gap sequence
  Tue May 5 14:20:38 2009
  Errors in file /oracle/admin/drgtrg2/bdump/drgtrg2_mrp0_5627.trc:
  ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
  Tue May 5 14:20:40 2009
  RFS[3]: Archived Log: '/oracle/oradata01/drgtrg2/stby_archive/1_125_675957688.arc'
  Tue May 5 14:20:59 2009
  RFS[4]: Archived Log: '/oracle/oradata01/drgtrg2/stby_archive/1_2_686067511.arc'
  Primary database is in MAXIMUM PERFORMANCE mode
  Tue May 5 14:21:08 2009
  Media Recovery Log /oracle/oradata01/drgtrg2/stby_archive/1_125_675957688.arc
  Media Recovery Log /oracle/oradata01/drgtrg2/stby_archive/1_1_686067511.arc
  Media Recovery Log /oracle/oradata01/drgtrg2/stby_archive/1_2_686067511.arc
  Media Recovery Waiting for thread 1 sequence 3 (in transit)