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 ############

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)

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

Metalink Doc   2338328.1