Conventions Make for Easy Automation

I talk a lot about implementing conventions, which usually results in easy automation. By establishing a standard framework, you can manage complex environments with minimal manual effort.

Below are examples of shell scripts you can run from any host to manage an Oracle Data Guard environment. You should be able to use the same framework and extrapolate from it to meet your specific needs.

Automating Data Guard Management through Conventions

Consistency is the precursor to automation. When your SIDs, unique names, and script paths follow a predictable pattern, managing a primary and standby pair becomes a matter of simple execution rather than complex navigation.

Monitoring Data Guard Status Across Hosts

The following examples show how a single script, dg_show.sh, can be used to pull status information regardless of which host you are currently logged into.

Show Data Guard status from host1:

[oracle@ol7-121-dg1 ~]$ echo $ORACLE_SID  hawk  [oracle@ol7-121-dg1 ~]$ /sf_working/dg_show.sh  DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production  DGMGRL> connect /  Connected as SYSDG.  DGMGRL> show configuration  Configuration - dg_hawk    Members:      hawka - Primary database      hawkb - Physical standby database  Configuration Status: SUCCESS 

Show Data Guard status from host2:

[oracle@ol7-121-dg2 ~]$ echo $ORACLE_SID  hawk  [oracle@ol7-121-dg2 ~]$ /sf_working/dg_show.sh  DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production  DGMGRL> connect /  Connected as SYSDG.  DGMGRL> show database hawkb  Database - hawkb    Role: PHYSICAL STANDBY    Intended State: APPLY-ON    Transport Lag: 0 seconds    Apply Lag: 0 seconds  Database Status: SUCCESS 

Managing Redo Transport with Shell Scripts

Beyond just monitoring, we can automate state changes. The dg_transport_on_off.sh script allows us to toggle redo transport services efficiently.

Stopping and Starting Transport

In this demo, we use the script to turn off transport on the primary and attempt to manage it from the standby (resulting in a predictable ORA error when the state is invalid for the operation).

Stop Transport from host1:

[oracle@ol7-121-dg1 ~]$ /sf_working/dg_transport_on_off.sh hawka off  DGMGRL> connect /  Connected as SYSDG.  DGMGRL> edit database hawka set state=TRANSPORT-off;  Succeeded.  DGMGRL> show database hawka  Database - hawka    Role: PRIMARY    Intended State: TRANSPORT-OFF  Database Status: SUCCESS 

Attempt to Start Transport from host2:

[oracle@ol7-121-dg2 ~]$ /sf_working/dg_transport_on_off.sh hawka on  DGMGRL> connect /  Connected as SYSDG.  DGMGRL> edit database hawkb set state=TRANSPORT-on;  Error: ORA-16516: current state is invalid for the attempted operation  Failed. 

Script Implementation: dg_show.sh and dg_transport_on_off.sh

Here are the underlying bash scripts used in the demonstrations above. These utilize the DGMGRL command-line interface and heredocs for automation.

dg_show.sh

#!/bin/bash -e  check_dg()  {   dgmgrl -echo << EOF   connect /   show configuration   show database ${ORACLE_SID}a   show database ${ORACLE_SID}b   exit  EOF  }  check_dg  exit 

dg_transport_on_off.sh

#!/bin/bash -e  DN=`dirname $0`  BN=`basename $0`  ST=${2:?"---> USAGE: $DN/$BN ORACLE_UNQNAME <ON|OFF>"}  check_dg()  {   dgmgrl -echo << EOF   connect /   show configuration   edit database ${ORACLE_UNQNAME} set state=TRANSPORT-${ST};   show database ${ORACLE_UNQNAME}   exit  EOF  }  check_dg  exit 

Conclusions? None necessary :=)