Setting up an Observer

Setting up an Observer

Observer is an automatic data guard broker, it can be installed with either the Oracle Client Administrator software or the full Oracle Database software.
It is preferable that the observer would run on a different host than the primary and standby databases, using the same network as any end-user client or application.
The host should be located In a third, independent location or at least isolated as much as possible from the standby database.
Make the observer highly available by configuring auto restart on the same server or on an alternate host.
These are the steps for setting up the observer:

Enable flashback on Primary & Standby:
Primary:

sqlplus> alter database flashback on ;

Standby:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
alter database flashback on ;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Update db_lost_write_protect on Primary & Standby:

SQL> alter system set db_lost_write_protect='TYPICAL' scope=both;

Adjust recovery area & flashback on Primary & Standby:

alter system set db_recovery_file_dest_size=40G scope=both;
alter system set db_flashback_retention_target=180 scope=both;

Adjust FastStartFailoverTarget between the Primary & the standby

DGMGRL> edit database prmy_db set property FastStartFailoverTarget='stndby_db';
DGMGRL> edit database stndby_db set property FastStartFailoverTarget='prmy_db';

Enable FAST_START FAILOVER in the broker

DGMGRL> ENABLE FAST_START FAILOVER;
DGMGRL> SHOW FAST_START FAILOVER;
DGMGRL> show configuration verbose

Adjust any required parameter of the observer

DGMGRL> show configuration verbose
.
.
.
  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    TraceLevel                      = 'SUPPORT'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'
    ExternalDestination1            = ''
    ExternalDestination2            = ''
    PrimaryLostWriteAction          = 'CONTINUE'
    ConfigurationWideServiceName    = 'prmy_db_CFG'
DGMGRL> show fast_start failover;
Fast-Start Failover: DISABLED
  Threshold:          30 seconds
  Target:             (none)
  Observer:           (none)
  Lag Limit:          30 seconds
  Shutdown Primary:   TRUE
  Auto-reinstate:     TRUE
  Observer Reconnect: (none)
  Observer Override:  FALSE
Configurable Failover Conditions
  Health Conditions:
    Corrupted Controlfile          YES
    Corrupted Dictionary           YES
    Inaccessible Logfile            NO
    Stuck Archiver                  NO
    Datafile Write Errors          YES
  Oracle Error Conditions:
    (none)

Start the observer:

$ dgmgrl sys/P@$$w0rd@prmy_db
DGMGRL> start observer;

Simulating that prmy_db is no longer available, by shutting down prmy_db database, the failover target should be changed to stndby_db

$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Nov 10 22:12:30 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> shut abort
ORACLE instance shut down.

We can see at the observer log, the failover process:

[W000 11/10 22:12:37.00] Primary database cannot be reached.
[W000 11/10 22:12:37.01] Fast-Start Failover threshold has not exceeded. Retry for the next 30 seconds
[W000 11/10 22:12:38.01] Try to connect to the primary.
[W000 11/10 22:12:40.36] Primary database cannot be reached.
[W000 11/10 22:12:41.36] Try to connect to the primary.
[W000 11/10 22:13:07.15] Primary database cannot be reached.
[W000 11/10 22:13:07.15] Fast-Start Failover threshold has expired.
[W000 11/10 22:13:07.15] Try to connect to the standby.
[W000 11/10 22:13:07.15] Making a last connection attempt to primary database before proceeding with Fast-Start Failover.
[W000 11/10 22:13:07.15] Check if the standby is ready for failover.
[S002 11/10 22:13:07.16] Fast-Start Failover started...
22:13:07.16  Friday, November 10, 2017
Initiating Fast-Start Failover to database "stndby_db"...
[S002 11/10 22:13:07.16] Initiating Fast-start Failover.
Performing failover NOW, please wait...
Failover succeeded, new primary is "stndby_db"
22:13:59.05  Friday, November 10, 2017
[S002 11/10 22:13:59.05] Fast-Start Failover finished...
[W000 11/10 22:13:59.05] Failover succeeded. Restart pinging.
[W000 11/10 22:13:59.10] Primary database has changed to stndby_db.

When starting back the old primary: prmy_db, the observer automatically reinstate it and changing its role to be a fully functional standby

[W000 11/10 22:14:22.87] Try to connect to the new standby prmy_db.
[W000 11/10 22:14:24.87] Connection to the new standby restored!
[W000 11/10 22:14:56.94] Try to connect to the primary stndby_db.
[W000 11/10 22:14:58.95] Connection to the primary restored!
[W000 11/10 22:14:59.95] Wait for new primary to be ready to reinstate.
[W000 11/10 22:15:00.95] New primary is now ready to reinstate.
[W000 11/10 22:15:00.95] Issuing REINSTATE command.
22:15:00.95  Friday, November 10, 2017
Initiating reinstatement for database "prmy_db"...
Reinstating database "prmy_db", please wait...
[W000 11/10 22:15:18.99] The standby prmy_db is ready to be a FSFO target
Reinstatement of database "prmy_db" succeeded
22:15:51.90  Friday, November 10, 2017
[W000 11/10 22:15:52.06] Successfully reinstated database prmy_db.
[W000 11/10 22:15:54.07] The reinstatement of standby prmy_db was just done

Stopping  observer :
Stop the observer process from a different session:

$ dgmgrl sys/P@$$w0rd@prmy_db
DGMGRL> stop observer

Start the observer in the background:
See my next post "Starting the Oracle Data Guard Broker OBSERVER in the BACKGROUND"