Wednesday, August 6, 2014

How to open a Physical Standby as Primary in an (Active) Data Guard Configuration

Here are the steps to open a Physical Standby as a new independent Database by removing (Active) Data Guard & Data Guard Broker (if exists) Configuration


1. Check the status of the DG COnfiguration

Log on to primary and standby and run in SQLPLUS:

On Primary :

SQL> SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE
-------------------- -------------------- ----------------
READ WRITE           MAXIMUM PERFORMANCE  PRIMARY


On Standby :

SQL> SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE
-------------------- -------------------- ----------------
READ WRITE           MAXIMUM PERFORMANCE  PRIMARY


Protection mode should be MAXIMUM PERFORMANCE as above.

If not you have to set it on Primary 

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

If you use DG Broker , use DGMGRL login to primary DB and set it as follows:

$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@myprimary
Password:
Connected.

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPERFORMANCE;

Now you will have to remove the DG Broker configuration:

DGMGRL> show configuration verbose;

Configuration - DG_FSF

  Protection Mode: MaxPerformance
  Databases:
    myprimary - Primary database
    mystandby - (*) Physical standby database

  (*) Fast-Start Failover target

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'

Fast-Start Failover: ENABLED

  Threshold:        30 seconds
  Target:           eoletbou2
  Observer:         s-eacea-rh-mgmtdbo01-p
  Lag Limit:        30 seconds
  Shutdown Primary: TRUE
  Auto-reinstate:   TRUE

Configuration Status:
SUCCESS

DGMGRL>

Stop Observer
DGMGRL> stop observer;

Disable Fast Start Failover (Active Dataguard)
DGMGRL> DISABLE FAST_START FAILOVER;
Disabled.

Remove the DG configuration
DGMGRL> REMOVE CONFIGURATION;
Removed configuration

DGMGRL>exit;


2. On the primary and standby database set dg_broker_start=false:


SQL> alter system set dg_broker_start=false scope=both;

System altered.

SQL> show parameter dg_broker

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /oraslow/app/oracle/product/11
                                                 .2.0/dbhome_2/dbs/dr1myprimary.dat
dg_broker_config_file2               string      /oraslow/app/oracle/product/11
                                                 .2.0/dbhome_2/dbs/dr2myprimary
                                                 .dat
dg_broker_start                      boolean     FALSE


3. On both system rename the metadata files shown by the previous command

SQL> ! mv /oraslow/app/oracle/product/11.2.0/dbhome_2/dbs/dr1myprimary.dat.dat /oraslow/app/oracle/product/11.2.0/dbhome_2/dbs/dr1myprimary.back

SQL> ! mv /oraslow/app/oracle/product/11.2.0/dbhome_2/dbs/dr2myprimary.dat /oraslow/app/oracle/product/11.2.0/dbhome_2/dbs/dr2myprimary.back

4. Check the values for the following prameters in both primary and stanby and unset/reset if needed.

ATTENTION : Some of them have been unset when previously removed the DG Broker configuration

show parameter LOG_ARCHIVE_CONFIG 
show parameter DB_FILE_NAME_CONVERT
show parameter LOG_FILE_NAME_CONVERT
show parameter LOG_ARCHIVE_DEST_n pointing to the Standby Database and valid for STANDBY_LOGFILES
show parameter LOG_ARCHIVE_DEST_STATE_n
show parameter DG_BROKER_START
show parameter DG_BROKER_CONFIG_FILE1
show parameter DG_BROKER_CONFIG_FILE2
show parameter STANDBY_ARCHIVE_DEST
show parameter STANDBY_FILE_MANAGEMENT
show parameter FAL_SERVER
show parameter FAL_CLIENT

5. Drop Standby Redologs from the Primary Database

SQL> SELECT GROUP# FROM V$STANDBY_LOG;

SQL>  SELECT GROUP# FROM V$STANDBY_LOG;

    GROUP#
----------
         4
         5
         6
         7

SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 4;

Database altered.

SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 5;

Database altered.

SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 6;

Database altered.

SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 7;

Database altered.

6. Shutdown standby and open it in mount state in order to disable managed recovery and activate it (Make Primary)

SQL> shutdown immediate;
SQL> startup mount;

-- Check the mode and the changed parameters

SQL> SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE
-------------------- -------------------- ----------------
MOUNTED              MAXIMUM PERFORMANCE  PHYSICAL STANDBY


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

Database altered.

SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;

Database altered.


SQL> SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE
-------------------- -------------------- ----------------
MOUNTED              MAXIMUM PERFORMANCE  PRIMARY

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE
-------------------- -------------------- ----------------
READ WRITE           MAXIMUM PERFORMANCE  PRIMARY

7. Former Physical Standby is now and independent Primary DB 



REFERENCES

https://blogs.oracle.com/AlejandroVargas/resource/How-to-open-the-standby-when-the-primary-is-lost.pdf

Oracle Support Documents : 733794.1 , 975614.1, 261336.1