Guarantee Restore Point Primary-Standby Environment

Guarantee Restore Point Primary-Standby Environment:

When we have a Data Guard environment (Primary RAC and Standby Standalone Databases) and we want to make use of restore points.

We need to follow below steps.

Always create a restore point on the standby database first then in Primary.

The main reason for this is because the SCN of the standby must be less than the SCN of the primary in case you need to flashback your database.

If we didn’t follow the step 1 then we need to rebuild the standby.

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

$
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;

2.) Connect to Standby Database Instances to create Restore point, database must be open in Mount mode, if it's in ADG and open in the Read-Only mode you need to open the database in Mount mode, create a restore point and bring back to Read-Only mode.

STDBY.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 name for a65
col scn for 9999999999999999999999
col time for a40
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;

create restore point "&restore_point_name" guarantee flashback database;

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

3.) Wait for a few moments.

4.) Connect to any 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 on heading on;
col name for a65
col scn for 9999999999999999999999
col time for a40
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;

create restore point "&restore_point_name" guarantee flashback database;

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

5.) 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;