Warning: session_start(): open(/tmp/sess_r44fkgafrhstl503ie7sov2rs2, O_RDWR) failed: No space left on device (28) in /razi/eraazi/darbari/mercy/index.php on line 15
EraAzi | Flashback primary database in dataguard environment Flashback primary database in dataguard environment
 

      Development wanted to do some changes in the dev database, which needs to be rolled back once testing is over.
So we have chosen to create flashback restore point before they do the changes and flashback the database, once testing is over.
Now there is a little twist. This is a data guard environment, i.e it has a standby database. Doing flashback on the primary database will break redo apply.
Below are the steps to flashback primary database in data guard environment safely.

1. Make sure both primary and standby database have flashback enabled.

-- PRIMARY DB

SQL> select name,database_role,flashback_on from v$database;

NAME      DATABASE_ROLE    FLASHBACK_ON
--------- ---------------- ------------------
PRIMDB    PRIMARY           YES

-- STANDBY DB

SQL> select name,database_role,flashback_on from v$database;

NAME      DATABASE_ROLE    FLASHBACK_ON
--------- ---------------- ------------------
STDBY      PHYSICAL STANDBY      YES

2. Create a restore point in the primary:[PRIMARY]

SQL> create restore  point BEFORE_TEST GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL>  select scn,NAME from v$restore_point;

       SCN NAME
---------- -----------------------
  14084750 BEFORE_TEST

Now we can confirm the dev team to do the changes in the primary database. And once dev team confirmed that their testing is over,
Follow below steps to flashback primary.

4. Shutdown Primary database [ PRIMARY]

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.

5. Mount primary database [ PRIMARY]

SQL> startup mount
ORACLE instance started.

Total System Global Area 1.1107E+10 bytes
Fixed Size                  7644464 bytes
Variable Size            9294584528 bytes
Database Buffers         1711276032 bytes
Redo Buffers               93011968 bytes
Database mounted.

6. Flashback primary database [ PRIMARY]

SQL> flashback database to restore point BEFORE_TEST;

Flashback complete.

7 . Open primary database in resetlogs: [ PRIMARY]

SQL> alter database open resetlogs;
Database altered.

8. Cancel the recovery on standby: [ STANDBY]

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

9. Flashback standby database [ STANDBY]
Get the SCN value from point 2 (the respective scn of the guarantee restore point)

SQL>FLASHBACK STANDBY DATABASE TO 14084750;

Flashback complete.

10. Start recovery [ STANDBY]

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered

NOTE:
If you are getting below error in standby, then we need to recreate the control file in standby

Sequence 22 does not yet exist in new incarnation, and it has been already
applied in the old.

SEE - How to recreate standby controlfile

DATAGUARD


Related Topics

How to use expdp to export data from physical standby database
How to recreate physical standby controlfile
Standby redologs in oracle dataguard
Useful DGMGRL commands in oracle dataguard
How to disable enable log shipping in standby using dgmgrl
How to enable active dataguard in physical standby database
Flashback primary database in dataguard environment
How to add a tempfile in primary database in dataguard
How to setup dataguard broker configuration (DG broker) in 12c

You May Also Like

How to generate AWR report in RAC
How to move spfile from file system to ASM in RAC
ORA-20101: TABLESPACE is not a ASSM (Automatic Segment Space Management) tablespace
Find user commits per minute in oracle database
SAMPLE parameter in EXPDP to export subset of data
Find pending distributed pending transactions in oracle
ORA-32774: more than one file was specified for bigfile tablespace
How to Read CSV files in R studio
How to get the execution plan for a SQL between two AWR snapshots
Apply database patch from OEM Cloud control

From This Website

oraversion utility in oracle 18c - New feature
Apply database proactive bundle patch in RAC using manual process
Useful flashback related commands
Steps To Set Up Master Slave Replication in MySQL
What is colored sql_id in dba_hist_colored_sql
PDB Lockdown Profiles in Oracle 12.2
ERROR: No checkpoint table specified for ADD REPLICAT
Important views in For Oracle DBA in daily usage
TNS-12542: TNS:address already in use
MySQL Replication and its Types