Flashback Oracle Rac Database with Dataguard -- Including Pluggable Container / Pdb

 

##########   Creating  Restore Point  ############

1)  Stop Redo Transport and Redo Apply 

A)  When we Dont have Broker Configuration  

alter system set log_archive_dest_state_n='defer'  sid='*' ;   ( On primary ) 

alter database recover managed standby database cancel  ;   ( On Standby ) 

B)  When we chave Broker Configuration  

edit database Chicago  set state='TRANSPORT-OFF'; 

EDIT database Boston  set state='apply-off' ; 

2) Create Restore  Point (in Standby First   ) 

create  restore point GRP_DG GUARANTEE FLASHBACK DATABASE ;   ( On Standby ) 

create  restore point GRP_PR  GUARANTEE FLASHBACK DATABASE ;   ( On Primary ) 

3) Enable Log Transport and Apply 

A) When we Dont have Broker Configuration  

alter system set log_archive_dest_state_n='enable'  sid='*' ;   ( On primary ) 

alter database recover managed standby databsae  using current logfile  disconnect ;  ( on  Standby ) 

B)  When we chave Broker Configuration 

edit database Chicago  set state='TRANSPORT-On'; 

EDIT database Boston  set state='apply-on' ; 

##########  Flashback Database   ############

1)  Stop Redo Transport and Redo Apply 

A)  When we Dont have Broker Configuration  

alter system set log_archive_dest_state_n='defer'  sid='*'  ;   ( On primary ) 

alter database recover managed standby database cancel  ;   ( On Standby ) 

B)  When we chave Broker Configuration  

edit database Chicago  set state='TRANSPORT-OFF'; 

EDIT database Boston  set state='apply-off' ; 

2) Flashbackup  Primary Database to restore Point 

srvctl stop database -d DB_NAME 

srvctl start instance -d DB_NAME -i instance_name -o mount 

flashback database to restore point GRP_PR ; 

alter database open resetlogs ; 

srvctl stop instance -d DB_NAME -i instance_name  

srvctl start database -d DB_NAME 

2) Flashbackup  Satndby Database to restore Point 

srvctl stop database -db  DB_NAME -stopoption immediate 

srvctl start instance -d DB_NAME -i instance_name -o mount 

flashback database to restore point GRP_DG ; 

srvctl stop instance -db DB_NAME -i instance_name  

srvctl start database -db DB_NAME -startoption  mount 

3) Enable Log Transport and Apply 

A) When we Dont have Broker Configuration  

alter system set log_archive_dest_state_n='enable'  SID='*';   ( On primary ) 

alter database recover managed standby databsae  using current logfile  disconnect ;  ( on  Standby ) 

B)  When we chave Broker Configuration 

edit database Chicago  set state='TRANSPORT-On'; 

EDIT database Boston  set state='apply-on' ; 

4)  Restart standby database on  Database pass through  resetlogs  and there is no lag 

########## Drop Restore Point   ############

1)  Stop Redo Transport and Redo Apply 

A)  When we Dont have Broker Configuration  

alter system set log_archive_dest_state_n='defer'  SID='*';   ( On primary ) 

alter database recover managed standby database cancel  ;   ( On Standby ) 

B)  When we chave Broker Configuration  

edit database Chicago  set state='TRANSPORT-OFF'; 

EDIT database Boston  set state='apply-off' ; 

2) Drop Restore  Point  

Drop  restore point GRP_PR  ;   ( On Primary ) 

DROP  restore point GRP_dg   ;   ( On Standby ) 

3) Enable Log Transport and Apply 

A) When we Dont have Broker Configuration  

alter system set log_archive_dest_state_n='enable'  SID='*';   ( On primary ) 

alter database recover managed standby databsae  using current logfile  disconnect ;  ( on  Standby ) 

B)  When we chave Broker Configuration 

edit database Chicago  set state='TRANSPORT-On'; 

EDIT database Boston  set state='apply-on' ; 

########## Flashback  Pluggable Database ############

Please ensure  to place standby database in  mount state  before we perform flashback  of pluggable database .   Once flashback of pluggable database is performed  on primary site  no  action is needed on standby site . 

You may see Mrp getting terminated saying flashback is not on and we may have to   enable flashback temporarily on standby 

There are options for creating restore points at the PDB level. If you connect to the PDB you can issue the commands as normal.

CONN / AS SYSDBA

ALTER SESSION SET CONTAINER=pdb1;

-- Normal restore point.

CREATE RESTORE POINT pdb1_before_changes;

DROP RESTORE POINT pdb1_before_changes;

-- Guaranteed restore point.

CREATE RESTORE POINT pdb1_before_changes GUARANTEE FLASHBACK DATABASE;

DROP RESTORE POINT pdb1_before_changes;

Flashback : If PDB uses local undo:

ALTER PLUGGABLE DATABASE pdb1 CLOSE;

FLASHBACK PLUGGABLE DATABASE pdb1 TO RESTORE POINT pdb1_before_changes;

ALTER PLUGGABLE DATABASE pdb1 OPEN RESETLOGS;

What if LOCAL_UNDO is not enabled?

SQL> select * from database_properties where property_name='LOCAL_UNDO_ENABLED';

PROPERTY_NAME PROPE DESCRIPTION

----------------------- ----- ---------------------------------------------

LOCAL_UNDO_ENABLED TRUE true if local undo is enabled

SQL> SELECT C.CON_ID, P.NAME , C.TABLESPACE_NAME, C.STATUS FROM CDB_TABLESPACES C,V$PDBS P WHERE  C.TABLESPACE_NAME LIKE 'UNDO%' AND  C.CON_ID=P.CON_ID ORDER BY C.CON_ID;

Switching to Local Undo Mode

shutdown immediate;

startup upgrade;

alter database local undo on;

shutdown immediate;

startup;

In oracle 12.2 if local_undo is not enabled, then we can use AUXILIARY DESTINATION parameter , which will create an auxiliary instance with container database’s system,sysaux and undo tablespace, and then restore the PDB using rollback transactions. However this flahback will take a long time and will consume additional space on the auxiliary destination.

steps FOR SHARED UNDO:

SQL> alter pluggable database PDB1 close;

SQL>flashback pluggable database PDB1  to restore point STAGE1 auxiliary destination '/oradata/aux_inst';

SQL> alter pluggable database PDB1  open resetlogs;

. If PDB uses shared undo and restore point created when PDB was closed (i.e. clean restore point)

SQL> alter pluggable database PDB1 close;

SQL> flashback pluggable database PDB1 to clean restore point TEST1;

SQL> alter pluggable database PDB1 open resetlogs;

Prior to Oracle 19.9, setting the UNDO_RETENTION parameter in the root container meant that value was set for all pluggable databases also. From Oracle 19.9 onward this is not the case. Now there are two ways to set the parameter in the root container.

 

-- Just the root container.

alter system set undo_retention=3000;

-- The root container and all PDBs.

alter system set undo_retention=3000 container=all;

With Local Undo enabled, How To Set Specific Undo Tablespace For Each PDB Instance On RAC Node (Doc ID 2673826.1)

Oracle 19c New Feature Automatic Flashback of Standby Database

Please  ensure to place  standby database in mount state  before we perform flashback on standby site .

One of the new features in Oracle 19c is that either when a flashback or point-in-time recovery is performed on the primary database in an Oracle Data Guard configuration, the same operation is also performed on the standby database as well.

Oracle 19c feature is that when we create a Restore Point on the primary database, it will automatically create a restore point as well on the standby database.

These restore points are called Replicated Restore Points and have the restore point name suffixed with a “_PRIMARY”.

Flashback is performed to the restore point created earlier and we then open the database with the RESETLOGS option.

The standby database is placed in MOUNT mode and we will see that the MRP process on the standby database will start and perform the automatic flashback operation on the standby database as well.

When we see the message “Flashback Media Recovery Complete” in the standby database alert log, we can now open the standby database.

 ############ Views :  ############ 

show parameters flash

show parameter db_recovery_file_dest

select flashback_ON from v$database;

select * from v$flashback_database_log;

SELECT * FROM v$flashback_database_stat;

SELECT scn, database_incarnation#, guarantee_flashback_database, storage_size, time, name

FROM gv$restore_point;

select name from v$recovery_file_dest;

select space_limit,space_used,space_reclaimable,number_of_files from   v$recovery_file_dest;

select * from v$flash_recovery_area_usage;

SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;

SELECT 

NAME, 

TO_CHAR(SPACE_LIMIT, '999,999,999,999') AS SPACE_LIMIT, 

TO_CHAR(SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE, 

'999,999,999,999') AS SPACE_AVAILABLE, 

ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1) 

AS PERCENT_FULL 

FROM V$RECOVERY_FILE_DEST;

########## Reference   ############

Metalink Doc   2338328.1 

Flashback of PDB failed with ORA-39867: Clean PDB Restore Point is On An Orphan Incarnation Of The PDB (Doc ID 2716855.1)