Deploying Oracle active Data Guard 12c on Windows servers Part II (Administration)

6.Data Guard Administration 

In the Part II We will perform the following tasks in this lab to manage and validate the data guard configuration (followed in order). if you wonder  how we got here check the Part I :  Implementation

6.1 Data guard Broker Configuration 


» What is a data guard Broker ? (‘DGMGRL’)

Oracle Data Guard Broker is a tool that automates and centralizes the creation, management and monitoring of the Data Guard configuration. All operations can be done via Oracle Enterprise Manager that uses the broker or via command  lines (DGMGRL) .
The actions that are automated and simplified by the broker are as follows :

  • Create and activate Data Guard configurations (up to 253 members)
  • Administration and management of the Data Guard configuration for all sites in the configuration
  • Simplifies switchovers and failovers
  • Monitor redo transport and the redo application; capture diagnostic information; quickly detect problems with centralized monitoring, test and report events.

» Broker  Prerequisite 

  • Same Oracle version on both Primary and standby databases.
  • A parameter file (SPFILE) must be used.
  • OraNet network settings must be configured on primary and standby database.
  • The value of the initialization parameter “DG_BROKER_START” = TRUE.
  • The service name must be registered statically on the listener for each instance.

» Configuring the Broker

a) ORA Net configuration:  

  • First  run the set the local listener on the standby DB using following commands:
---- Standby server 
SQL> Alter system set local_listener='(address=(host= 192.168.78.52) (port=1522)(protocol=tcp))';
SQL> alter system register;
  • Add the following lines in each of the listeners: global_name format should be ‘db_unique_name_DGMGRL.db_domain
---- Primary server 

(SID_DESC =(GLOBAL_DBNAME = MTLDB_DGMGRL.evilcorp) –- format ‘db_unique_name_DGMGRL.db_domain’
(ORACLE_HOME =C:APPORACLEproduct12.1.0.2db_1)(SID_NAME = MTLDB)))
              
---- Standby server

(SID_DESC =(GLOBAL_DBNAME = MTLSTD_DGMGRL.evilcorp)
(ORACLE_HOME = C:APPORACLEproduct12.1.0.2db_1)(SID_NAME = MTLSTD)))

        Note: This step is only necessary when oracle restart is not enabled

  b)  Create the configuration & add the databases

  • Execute the following in the DGMGRL window
C: dgmgrl sys/racattack@Montreal                                           

DGMGRL> CREATE CONFIGURATION DG12 as primary database is MTLDB connect identifier is Montreal; 
 //created

DGMGRL> add database MTLSTD as connect identifier is Boisbriand maintained as physical;  
//added

c)  Enable the configuration

DGMGRL> enable configuration; 
you can check the log out here  
#tail -f 'C:apporaclediagrdbmsMTLSTDMTLSTDtracealert_MTLSTD.log'

DGMGRL> show configuration 
Configuration - dg12
Protection Mode: MaxPerformance
Databases:
mtldb  - Primary database
mtlstd - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
ENABLED

d) Confirm the value of the broker’s StaticConnectIdentifier parameter matches each databases’  static listener IP/port

DGMGRL> EDIT instance mtldb set property StaticConnectIdentifier=
'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.51)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=MTLDB.evilcorp)(INSTANCE_NAME=mtldb)(SERVER=DEDICATED)))';

DGMGRL> EDIT instance mtlstd set property StaticConnectIdentifier=
'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.52)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=MTLSTD.evilcorp)(INSTANCE_NAME=mtlstd)(SERVER=DEDICATED)))';

6.2 Redo transport and Protection modes                                         Go to Top

                      

»  Redo transport management

  a)  Data Guard Broker Mode

  • The ASYNC mode is started by default when the Broker configuration is enabled . However the ”LogXptMode”  Broker property enables you to set the redo transport service.
    Run the following to keep the change consistent on both standby and primary DB over role transitions  :
-- change the Transport mode from ASYNC to SYNC 
DGMGRL> edit database MTLDB set property logxptmode='SYNC';
DGMGRL> edit database MTLSTD set property logxptmode='SYNC';

    b)  SQL mode

  • Even without data guard broker you can still enable the Redo Transport and application (Managed Recovery process), using the following SQL commands (SQL mode):
-- Start the MRP process in the background  
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
-- Stop the MRP
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;   
  • Notes:
    • The DISCONNECT option allows you to run the command in the background.
    • Standby redo logs are necessary for a Real-time Apply of redo sources,
    • USING CURRENT LOGFILE” option is no longer needed in 12c.
    • Warning : never use this method while the Broker is configured.Doing so will cause data guard inconsistency.

    c)  Check Redo Apply

  • To check that the Redo data is correctly applied on the standby DB (MTLSTD) consult the alert log or one of the v$
    views (v$archive_log, v$history_log,v$log) :

--- Standby Database alert.log
#tail -f 'C:apporaclediagrdbmsMTLSTDMTLSTDtracealert_MTLSTD.log'
Media Recovery Waiting for thread 1 sequence 677 (in transit)
Fri Apr 15 05:30:41 2016
Recovery of Online Redo Log: Thread 1 Group 5 Seq 677 Reading mem 0
Mem# 0: +REDO/MTLDB/ONLINELOG/group_5.260.908907615

SQL> Select MAX (SEQUENCE#), APPLIED FROM V$ARCHIVED_LOG GROUP BY APPLIED; MAX(SEQUENCE#) APPLIED -------------- ---------     676        IN-MEMORY     669        N     675        YES SQL> SELECT * FROM V$ARCHIVE_GAP; no rows selected

» Change the protection mode                                                                                                                Go to Top

  

a)  Data Guard Broker Mode

The default value for each of these parameters is as follows

  • Protection mode: MAXIMUM PERFORMANCE
  • Mode de transport (Logxptmode): ASYNC (asynchrone)

To change the protection mode, i.e to MAXIMUM AVAILABILITY (synchronous), the following Broker commands are executed:

DGMGRL> EDIT DATABASE MTLDB SET PROPERTY 'LogXptMode'='SYNC';                
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
DGMGRL> show configuration
Configuration - dg12  
Protection Mode: MaxAvailability

a)  SQL mode

Execute the following SQL statement while choosing one of the 3 values on the primary database after a restart in mount mode:

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {AVAILABILITY | PERFORMANCE | PROTECTION};

» Startup modes of a standby database

    There are several modes for a standby DB :

    • MOUNT : Default mode, allows continuous application of the redo logs upon reception.
    • READ-ONLY: Allows Read-Only access to the standby DB while continuing redo data reception from the primary database. Redo application is interrupted until the Database is restarted (mounted back).
    • SNAPSHOT (READ-WRITE): Allows to read and write on standby DB, but all changes will be lost when the database is restarted (mounted back).

   Note : Modes other than MOUNT are part of the features requiring an ACTIVE DATAGUARD license (on top of the Enterprise E license)

6.2 Manage role transitions                                                                 Go to Top

6.2.1 Switchover

fig25

Fig10 : Switchover scenario

» Data Guard Broker Method


a) Test the validity of the switchover before execution: 

DGMGRL> validate database mtlstd;
Database Role:     Physical standby database
Primary Database:  mtldb
Ready for Switchover:  Yes
Ready for Failover:    Yes (Primary Running)
Flashback Database Status:
mtldb:   Off
mtlstd:  Off
Current Log File Groups Configuration:
Thread#  Online Redo Log Groups Standby    Redo Log Groups Status  
             (mtldb)            (mtlstd)
   1             3                 2        Insufficient SRLs               

Future Log File Groups Configuration:
Thread#  Online Redo Log Groups  Standby    Redo Log Groups Status          
          (mtlstd)               (mtldb)
   1           3                     2      Insufficient SRLs

-------  Standby alert log:
Mon Dec 29 21:44:17 2014
SWITCHOVER VERIFY BEGIN
SWITCHOVER VERIFY WARNING: no standby database is defined in LOG_ARCHIVE_DEST_n
to protect this database if it is converted to a primary database
SWITCHOVER VERIFY COMPLETE

c) execute the switchover : 

DGMGRL> switchover to mtlstd Performing switchover NOW, please wait... Operation requires a connection to instance "mtlstd" on database "mtlstd" Connecting to instance "mtlstd"... Connected as SYSDG. New primary database "mtlstd" is opening... Oracle Clusterware is restarting database "mtldb" ... Switchover succeeded, new primary is "mtlstd" DGMGRL> show configuration Configuration - dg12 Protection Mode: MaxPerformance Members: mtlstd - Primary database mtldb  - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS   (status updated 38 seconds ago) --- alert log from primary database: Active, synchronized Physical Standby switchover target has been identified Preventing updates and queries at the Primary Generating and shipping final logs to target standby Switchover End-Of-Redo Log thread 1 sequence 682 has been fixed Switchover: Primary highest seen SCN set to 0x0.0x6dce3f ARCH: End-Of-Redo Branch archival of thread 1 sequence 682 ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2 ARCH: Archiving is disabled due to current logfile archival Primary will check for some target standby to have received all redo Waiting for target standby to apply all redo Archivelog for thread 1 sequence 682 required for standby recovery Switchover: Primary controlfile converted to standby controlfile succesfully Switchover: Complete - Database shutdown required Sending request(convert to primary database) to switchover target mtlstd Switchover complete. Database shutdown required Completed: ALTER DATABASE SWITCHOVER TO 'mtlstd'

» SQL Method

This method is based only on SQL commands to initiate role switching ( Primary/Standby).

Warning : never use this method while the Broker is configured.Doing so will cause data guard inconsistency.

a) Check the status of the switchover on each of the databases (Primary/Standby)

--- Primary Database
PRIMARY_SQL> select switchover_status from v$database;           
SWITCHOVER_STATUS 
----------------- 
TO STANDBY     

--- Primary database  
STANDBY_SQL> select switchover_status from v$database;
SWITCHOVER_STATUS 
----------------- 
NOT ALLOWED           

b) Test the validity of the switchover before execution

--- Primary Database
PRIMARY_SQL> ALTER DATABASE SWITCHOVER TO MTLSTD VERIFY;                
Database altered.   ---- ceci confirme que l’opération est valide.     

c) execute the switchover

--- Primary Database PRIMARY_SQL> ALTER DATABASE SWITCHOVER TO MTLSTD;                Database altered.   ---- ceci confirme que l’opération est valide. Alert log /nouvelle base secondaire (MTLDB) : Switchover complete. Database shutdown required Thu Apr 14 17:59:57 2016  Instance shutdown complete Alert log /nouvelle base primaire (MTLSTD): Switchover: Complete - Database mounted as primary    SWITCHOVER: completed request from primary database. 

d) Startup the  new primary and standby databases

  • New Primary database (MTLSTD):
--- start the new Primary Database
MTLSTD> alter database open;                                                
Database altered.   


  • New Standby DB (MTLDB) : 
--- Start the New standby Database
MTLDB> startup mount;           
Database mounted.  
     
---- Check SCN and DATE of the switchover 
MTLSTD> SELECT TRUNC(scn_to_timestamp(STANDBY_BECAME_PRIMARY_SCN)) swicth_date,
        STANDBY_BECAME_PRIMARY_SCN scn_switch FROM v$database;

SWICTH_DA SCN_SWITCH                                                               
--------- ----------                                                         
14-APR-16    7046772  


6.2.3 Failover                                                                                                                                             Go to Top


fig26

Fig11 : Failover scenario

» Data Guard Broker Method

This method relies on the commands available on the broker as well as on the FLASHBACK database option to simplify the recovery of the corrupt primary database (Reinstate)

a) Enable flashback on the primary DB MTLDB :

SQL> ALTER SYSTEM SET FLASHBACK ON scope=both;
SQL> ALTER SYSTEM SET db_flashback_retention_target=60 scope=both;

b) Check the status of the configuration on each of the databases (primary/Standby) :

DGMGRL> validate database mtlstd
Database Role:     Physical standby database
Primary Database:  mtldb
Ready for Switchover:  Yes
Ready for Failover:    Yes (Primary Running)
Flashback Database Status:
mtldb:   On

c) Simulate the failover by stopping the primary database abnormally:

SQL> SHUTDOWN ABORT;

d) Apply the failover :

dgmgrl  /@Boisbriand  DGMGRL> show configuration
Configuration - dg12
Protection Mode: MaxPerformance
Members:                                                                              
mtldb  - Primary database
Error: ORA-12519: TNS:no appropriate service handler found
mtlstd - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
ERROR   (status updated 0 seconds ago)

---------Failover
DGMGRL> failover to MTLSTD;
Performing failover NOW, please wait...
Failover succeeded, new primary is "mtlstd"

e) Recovery of the former primary database:

DGMGRL> show configuration
Configuration - dg12
Protection Mode: MaxPerformance
Members:
mtlstd - Primary database
mtldb  - Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS   (status updated 16 seconds ago)

----- Restart of the MTLDB database 
C: sqlplus /@Montreal 

sys@MTLDB.evilcorp> startup mount;
Database mounted.

------ Reinstate
dgmgrl  /@Boisbriand  
DGMGRL> Reinstate database MTLDB;                                              
Reinstating database "mtldb", please wait...                    
Reinstatement of database "mtldb" succeeded

DGMGRL> show configuration
Configuration - dg12
Protection Mode: MaxPerformance
Members:
mtlstd - Primary database
mtldb  - Physical standby database

f) Restore the initial Primary/Standby roles (Primary:mtldb /Standby:mtlstb):

DGMGRL> switchover to  MTLDB;
Performing switchover NOW, please wait...
Operation requires a connection to instance "mtldb" on database "mtldb"
Connecting to instance "mtldb"...
Connected as SYSDG.
New primary database "mtldb" is opening...
Oracle Clusterware is restarting database "mtlstd" ...succeeded
Switchover succeeded, new primary is "mtldb"

» SQL Method

This method is based only on SQL commands to initiate role failover (Primary/Standby) in case of failure (manual/automatic).
Warning : never use this method while the Broker is configured.Doing so will cause data guard inconsistency.

a) Check the switchover status on each of the databases(Primary/standby)

--- Primary database PRIMARY_SQL> select switchover_status from v$database;           SWITCHOVER_STATUS ----------------- TO STANDBY     --- Standby database   STANDBY_SQL> select switchover_status from v$database; SWITCHOVER_STATUS ----------------- NOT ALLOWED         

b) Test the validity of the switchover before execution

PRIMARY_DB> ALTER DATABASE SWITCHOVER TO MTLSTD VERIFY;               

c) Execute the failover

---- Minimum data loss (recommended) SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH; ---- OR with data loss SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;

d) Recovery of the former primary database

-- Verify the SCN at failover time SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE; STANDBY_BECAME_PRIMARY_SCN -------------------------- 7917322

  • The obtained SCN will serve to restore the lost primary database into a new standby database using one of these method
    • Via RMAN backups
    • Via FLASHBACK

 Go to Top


  • Now that you have gotten this far into this lab, it’s time to have a real fun.You read me right let’s get wild and get this broker rock !!Fast-Start Failover here we Go !!!!

                                                                                        >> Part III : Explore Features (Fast Start failover)