Tuesday, February 12, 2019

Deploying Oracle active Data Guard 12c on Windows servers Part III (FAST START FAILOVER)

7. FAST START FAILOVER

7.1 CONFIGURATION FAST START FAILOVER

Fast start failover allows Data Guard to automatically failover to a previously selected standby database without human intervention. Data Guard monitors the status of the configuration continuously and triggers a failover if necessary.This architecture makes the fast-start failover ideal for high availability and disaster recovery.

» Prerequisites to enable Fast-start Failover:

  • Active Broker.
  • Flashback Database configured on both databases (primary et standby).
  • The right protection mode configured.
  • Standby Redo log files created on both sides.
  • Identical Redo transport on both databases (directions).
  • Set the appropriate value to the FastStartFailoverLagLimit parameter (redo offset limit in seconds, between the standby and primary DB).

» Configure the Broker for the FSFO 


A) Enable flashback and set failover targets

 • FLASHBACK activation on both DBs
-----------------------------------                    
SQL> ALTER DATABASE FLASHBACK ON;                                     
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=60 scope=both; ----(1Hour)
DGMGRL> EDIT DATABASE mtldb SET PROPERTY FastStartFailoverTarget= MTLSTD;
DGMGRL> EDIT DATABASE mtlstd SET PROPERTY FastStartFailoverTarget= MTLDB ;
DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverPmyShutdown= FALSE;
DGMGRL> ENABLE FAST_START FAILOVER;

B) Install and start the Observer on a preferred third-party server

Dgmgrl sys/Montreal                                            
DGMGRL> START OBSERVER;
DGMGRL> SHOW FAST_START FAILOVER;
Fast-Start Failover: ENABLED
Threshold:          30 seconds
Target:             mtlstd
Observer:           ora-host-01
Lag Limit:          30 seconds
Shutdown Primary:   FALSE
Auto-reinstate:     TRUE
Observer Reconnect: 30 seconds
Observer Override:  FALSE
Configurable Failover Conditions
Health Conditions:
Corrupted Controlfile        YES
Corrupted Dictionary         YES
Inaccessible Logfile          NO
Stuck Archiver                NO
Datafile Offline             YES
Oracle Error Conditions:(none)

This step implies that the third-party server must have a Client Administrator software of the same version and that the aliases of the two databases be integrated into the tnsnames.ora.

7.2 CONFIGURATION OF THE FAILOVER CLIENT                 

Applications must also be able to quickly terminate their connections from the database that crashed and reconnect immediately to the new primary database (after failover).

An effective failover client on Data Guard is based on 3 components:

    • » Fast database failover
    • » Fast start of database services on the new primary database
    • » Fast notification of clients and reconnection to the new primary database

A) Enable Oracle restart and add the a service in primary mode on both databases

   --- Attach the standby database to Oracle restart  
# srvctl add database -d MTLSTD -n MTLDB -o C:\APP\ORACLE\PRODUCT\12.1.0.2\DB_1 -r PHYSICAL_STANDBY -s MOUNT
# srvctl modify database -d MTLSTD -spfile C:\APP\ORACLE\PRODUCT\12.1.0.2\DB_1\DATABASE\SPFILEMTLSTD.ORA
-domain evilcorp

--- Add dynamic service to both databases
# srvctl add service -d mtldb  -s virtuo -l PRIMARY -e SELECT -m BASIC -w 1 -z 180
# srvctl add service -d mtlstd -s virtuo -l PRIMARY -e SELECT -m BASIC -w 1 -z 180
(-s) Dynamic service on witch sessions are linked
(-z) failover_retries
(-w) failover_delay
(-r/-l) Role
(-e) Session failover type
(-n) db_name (shared by both DBs)
(-d) db_unique_name

B) Enable the service on the primary database for the first time

$ srvctl start service -d mtldb -s virtuo

C) Checking the configuration

  • Primary

PS C:\Windows\system32> srvctl config database -d mtldb
Database unique name: MTLDB
Database name: MTLDB
Oracle home: C:\APP\ORACLE\product\12.1.0.2\db_1
Oracle user: nt authority\system
Spfile: +DATA/MTLDB/PARAMETERFILE/spfile.257.907772105
Password file:
Domain: evilcorp
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA,REDO,FRA
Services: virtuo
Database instance: MTLDB
  • Standby

PS C:\Windows\system32> srvctl config database -d mtlstd
Database unique name: MTLSTD
Database name: MTLDB
Oracle home: c:\app\oracle\product\12.1.0.2\db_1
Oracle user: nt authority\system
Spfile: C:\APP\ORACLE\PRODUCT\12.1.0.2\DB_1\DATABASE\SPFILEMTLSTD.ORA
Password file:
Domain: evilcorp
Start options: mount
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Disk Groups: FRA,REDO,DATA
Services: virtuo
Database instance: MTLSTD

D) TNS entry to add to the client

PROD_DG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =montreal-01)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST =montreal-02)(PORT = 1521))
(CONNECT_DATA =(SERVER = DEDICATED)
(SERVICE_NAME = virtuo.evilcorp)))

7.3 FAST-START FAILOVER simulation via a crash of the primary DB


A) Check if the primary database to be failed over contains more than 30 minutes of flashback data

SQL> SELECT (SYSDATE - OLDEST_FLASHBACK_TIME)*24*60 AS HISTORY FROM V$FLASHBACK_DATABASE_LOG
                                               
HISTORY                                                                    
--------
818.2   -- Minutes
SQL> CREATE TABLE X AS SELECT * FROM ALL_OBJECTS;
SQL> select count(*) from x;
COUNT(*)
----------
92577

B) Stop the primary database (crash)

 SQL> shutdown abort;
----- Observer Log
DGMGRL>  
Initiating Fast-Start Failover to database "mtlstd"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "mtlstd"
14:08:25.32  Friday, April 22, 2016

Client sessions with a Client Failover configuration will be automatically redirected to the new primary database

  • Before failover

SQL> select db_unique_name,database_role,fs_failover_status,fs_failover_current_target
failover_target, fs_failover_observer_present observer  from v$database;

DB_UNIQUE_NAME DATABASE_ROLE FAILOVER_STATUS      FAILOVER_TARGET   OBSERVER
-------------- ------------- -------------------- ---------------- -----------
mtldb          PRIMARY       TARGET OVER LAG LIMIT    mtlstd          YES

  • After failover
DB_UNIQUE_NAME DATABASE_ROLE FAILOVER_STATUS      FAILOVER_TARGET   OBSERVER
-------------- ------------- -------------------- ---------------- -----------
mtlstd         PRIMARY       REINSTATE REQUIRED        mtldb           YES

C) Restart the failed Primary Database in Mount Mode

DGMGRL> show configuration
Configuration - dg12
Protection Mode: MaxPerformance
Members:
mtlstd - Primary database
Warning: ORA-16829: fast-start failover configuration is lagging
mtldb  - (*) Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated
Fast-Start Failover: ENABLED

• This step is used to resynchronize the failed primary DB [mtldb] with the new primary database [mtlstd] (after failover)

MTLDB> startup mount

D) Primary DB Alert Log

… Incomplete Recovery applied until change 9877792 time 04/22/2018 14:07:36
Flashback Media Recovery Complete
Completed: FLASHBACK DATABASE TO SCN 9877791
…alter database convert to physical standby
…/
Physical Standby Database mounted.
CONVERT TO PHYSICAL STANDBY: Complete - Database mounted as physical standby
Completed: alter database convert to physical standby

E) Observer Log

…  14:46:52.24  Friday, April 22, 2018
Initiating reinstatement for database "mtldb"...
Reinstating database "mtldb", please wait...
Reinstatement of database "mtldb" succeeded                             
14:47:21.90  Friday, April 22, 2018
DGMGRL> show configuration                                                       
Configuration - dg12                                                      
Protection Mode: MaxPerformance
Members:
mtlstd - Primary database
mtldb  - (*) Physical standby database
Fast-Start Failover: ENABLED
Configuration Status:
SUCCESS   (status updated 16 seconds ago)

F) Restore the original role distribution (MTLDB as primary - MTLSD as standby) via a switchover 

  • Configuration Status :
 DGMGRL> switchover to mtldb
Performing switchover NOW, please wait...
New primary database "mtldb" is opening...
Oracle Clusterware is restarting database "mtlstd" ..
Switchover succeeded, new primary is "mtldb"

DGMGRL> show configuration
Configuration - dg12
Protection Mode: MaxPerformance
Members:
mtldb - Primary database
mtlstd  - (*) Physical standby database
Configuration Status:                                                        
SUCCESS   (status updated 23 seconds ago)

Go to Top

 Part I :  Implementation 
Part II : Administration (switchover and failover) 

 

No comments:

Post a Comment