Flashback Database to Restore Point in Primary-Standby Environment

Flashback Database to Restore Point in Primary-Standby Environment:

When we have a Data Guard environment (Primary RAC and Standby Standalone Databases) and we want Flashback database to restore point.

We need to follow below steps.

a) Always flashback the primary database first. Then follow below step for Standby.

b) On the primary, connect using RMAN and list the incarnations.

c) On the standby, cancel the recovery and flashback the standby to restore point created earlier.

d) On the standby, connect using RMAN and list the incarnations.

e) If there is a mismatch in incarnation between primary and standby then need to reset the incarnation of standby and start recovery (Details not available in this DOC).

1)   Disable Log Transport Services to the Standby Database for the Destination serving the Standby Database on all Primary Instances:

$
dgmgrl sys/<Password>@<PRIM/STDBY>

DGMGRL> 
show database 'PRIM';
edit database 'PRIM' set state = 'TRANSPORT-OFF';
show database 'PRIM';

2)   Stop Managed Recovery on the Standby Database using Data Guard Broker or SQL*PLUS:

$
dgmgrl sys/<Password>@<PRIM/STDBY>

DGMGRL>
show database 'STDBY';
edit database 'STDBY' set state = 'APPLY-OFF';
show database 'STDBY';
  OR
STDBY.SYS.SQL>
select inst_id, process, status, thread#, sequence#, block#, blocks from gv$managed_standby order by inst_id;

alter database recover managed standby database cancel;

select inst_id, process, status, thread#, sequence#, block#, blocks from gv$managed_standby order by inst_id;

3)   Shutdown all the Primary Database Instances:

PRIM.SYS.SQL>

set serveroutput on long 2000000 longchunksize 20000 pagesize 50000 linesize 1000;
set trimspool on time on timi on feedback on verify on echo off heading on;
col db_unique_name for a15
col open_mode for a10

select a.inst_id,a.name db_name, a.db_unique_name, b.instance_name,b.instance_role,a.database_role,a.open_mode, a.protection_level, a.fs_failover_status,a.dataguard_broker,a.log_mode, a.force_logging, a.flashback_on from gv$database a, gv$instance b
where a.inst_id=b.inst_id order by a.inst_id;

$
srvctl stop database -d PRIM -o immediate
 OR
PRIM.SYS.SQL>
shutdown immediate

4)   Startup mount one Primary Database Instance:

$
srvctl start instance -d PRIM -i PRIM1 -o mount

 OR
PRIM.SYS.SQL>
startup mount

PRIM.SYS.SQL>

set serveroutput on long 2000000 longchunksize 20000 pagesize 50000 linesize 1000;

set trimspool on time on timi on feedback on verify on echo off heading on;

col db_unique_name for a15

col open_mode for a10

select a.inst_id,a.name db_name, a.db_unique_name, b.instance_name,b.instance_role,a.database_role,a.open_mode, a.protection_level, a.fs_failover_status,a.dataguard_broker,a.log_mode, a.force_logging, a.flashback_on from gv$database a, gv$instance b
where a.inst_id=b.inst_id order by a.inst_id;

5)   Flashback the Primary Database to the desired Restore Point:

PRIM.SYS.SQL>
set serveroutput on long 2000000 longchunksize 20000 pagesize 50000 linesize 1000 
set trimspool on time on timi on feedback on verify off echo off heading on;
col name for a65
col scn for 9999999999999999999999
col time for a40
col guarantee_flashback_database for a30

select name, scn, time, guarantee_flashback_database, storage_size, database_incarnation# from v$restore_point order by time,name;

flashback database to restore point "&restore_point_name";

When trying  flashback database to restore point failed with below error, The flashback rollback database by using flashback logs, then it recovers to desire point in time using archived log files.

flashback database to restore point "&restore_point_name"

 *

 ERROR at line 1:

 ORA-38754: FLASHBACK DATABASE not started; required redo log is not available

 ORA-38762: redo logs needed for SCN 12993198112859 to SCN 12993198112866

 ORA-38761: redo log sequence 52 in thread 1, incarnation 27 could not be accessed

If using SQLPlus for Flashback database, then you need to have the required archive logs in the archive destination at the time of flashback.

Alternate, use RMAN. Issue the same command from the RMAN will restore required Archived Logfiles from Backup.

RMAN>
connect target sys/<Password>@PRIM
connect catalog <Catalog_User>/<Password>@CATALOG_DB
list restore point all;
run
{
flashback database to restore point "&restore_point_name";
}

6)   Open the Instance with RESETLOGS:

PRIM.SYS.SQL>
alter database open resetlogs;

$
srvctl stop instance -d PRIM -i PRIM1 -o immediate

srvctl start database -d PRIM

PRIM.SYS.SQL>

set serveroutput on long 2000000 longchunksize 20000 pagesize 50000 linesize 1000;
set trimspool on time on timi on feedback on verify on echo off heading on;
col db_unique_name for a15
col open_mode for a10

select a.inst_id,a.name db_name, a.db_unique_name, b.instance_name,b.instance_role,a.database_role,a.open_mode, a.protection_level, a.fs_failover_status,a.dataguard_broker,a.log_mode, a.force_logging, a.flashback_on from gv$database a, gv$instance b
where a.inst_id=b.inst_id order by a.inst_id;

7)   Flashback the Standby Database to the desired Restore Point:

STDBY.SYS.SQL>

set serveroutput on long 2000000 longchunksize 20000 pagesize 50000 linesize 1000 
set trimspool on time on timi on feedback on verify off echo off heading on;
col name for a65
col scn for 9999999999999999999999
col time for a40
col host_name for a15
col guarantee_flashback_database for a30
col db_unique_name for a15
col open_mode for a10

select a.inst_id,a.name db_name, a.db_unique_name, b.instance_name,b.instance_role,a.database_role,a.open_mode, a.protection_level, a.fs_failover_status,a.dataguard_broker,a.log_mode, a.force_logging, a.flashback_on from gv$database a, gv$instance b
where a.inst_id=b.inst_id order by a.inst_id;

select name, scn, time, guarantee_flashback_database, storage_size, database_incarnation# from v$restore_point order by TIME,NAME;

flashback database to restore point "&restore_point_name";

select a.inst_id,a.name db_name, a.db_unique_name, b.instance_name,b.instance_role,a.database_role,a.open_mode, a.protection_level, a.fs_failover_status,a.dataguard_broker,a.log_mode, a.force_logging, a.flashback_on from gv$database a, gv$instance b
where a.inst_id=b.inst_id order by a.inst_id;

8)   Enable Log Transport Services to the Standby Database for the Destination serving the Standby Database on all Primary Instances:

$
dgmgrl sys/<Password>@<PRIM/STDBY>

DGMGRL>
show database 'PRIM';
edit database 'PRIM' set state = 'TRANSPORT-ON';

show database 'PRIM';

STDBY.SYS.SQL>
select arch.thread# "thread", arch.sequence# "last sequence received", appl.sequence# "last sequence applied", (arch.sequence# - appl.sequence#) "difference" from (select thread#, sequence# from gv$archived_log 
where (thread#, first_time ) in (select thread#, max(first_time) from gv$archived_log group by thread#)) arch, (select thread#, sequence# from gv$log_history 
where (thread#, first_time ) in (select thread#, max(first_time) from gv$log_history group by thread#)) appl  where arch.thread# = appl.thread# order by 1;

9)   Perform a Log Switch on all Primary Database Instances to check log shipment:

PRIM1.SYS.SQL>
alter system switch logfile;

alter system switch logfile;

alter system switch logfile;

alter system switch logfile;

alter system switch logfile;

PRIM2.SYS.SQL>
alter system switch logfile;

alter system switch logfile;

alter system switch logfile;

alter system switch logfile;

alter system switch logfile;

10) Once Log Transport Services are running again you can enable Managed Recovery on the Standby Database using Data Guard Broker or SQL*PLUS:

$
dgmgrl sys/<Password>@<PRIM/STDBY>

DGMGRL>
show database 'STDBY';
edit database 'STDBY' set state = 'APPLY-ON';
show database 'STDBY';

  OR
STDBY.SYS.SQL>
select inst_id, process, status, thread#, sequence#, block#, blocks from gv$managed_standby order by inst_id;

alter database recover  managed standby database using current logfile disconnect from session; 

select inst_id, process, status, thread#, sequence#, block#, blocks from gv$managed_standby order by inst_id;

select arch.thread# "thread", arch.sequence# "last sequence received", appl.sequence# "last sequence applied", (arch.sequence# - appl.sequence#) "difference" from (select thread#, sequence# from gv$archived_log 
where (thread#, first_time ) in (select thread#, max(first_time) from gv$archived_log group by thread#)) arch, (select thread#, sequence# from gv$log_history 
where (thread#, first_time ) in (select thread#, max(first_time) from gv$log_history group by thread#)) appl  where arch.thread# = appl.thread# order by 1;