Automating DBA Tasks with Oracle GoldenGate - Part 1

Automating DBA Tasks with Oracle GoldenGate

Oracle GoldenGate is widely recognized as one of the best tools for real-time data replication. It allows seamless database migrations, schema or table replication across homogeneous and heterogeneous environments, and enhances database performance by offloading reporting workloads. Additionally, it ensures high availability by enables Bi-directional or cascading database replication configurations. Beyond these well-known benefits, GoldenGate can also be leveraged for automating routine DBA tasks, reducing manual intervention and improving efficiency by Streamline Your DBA Tasks with EVENTACTIONS. Let's explore how you can leverage its EVENTACTIONS parameter to automate routine DBA tasks, reduce manual effort, and optimize workflows.

In this post, I will demonstrate how GoldenGate can assist in automating key DBA operations, such as:

  1. Starting or stopping GoldenGate processes – Automate the management of replication processes to ensure smooth operations.
  2. Creating users – Use GoldenGate to automate user creation and privileges, streamlining database administration.

Stay tuned as we explore these automation possibilities in detail!

Unlocking Automation in Oracle GoldenGate:

How GoldenGate Works: A Quick Refresher

  • Extract: Coordinates with the log mining server to capture GoldenGate-enabled table transactions. Converts changes into canonical format and writes to trail files.
  • Replicat: Reads trail files and applies changes to target databases.

What is EVENTACTIONS?

An optional parameter (used in TABLE/MAP statements) that triggers predefined actions when specific transactions are detected. Acts as a "smart switch" for:

  • Starting/stopping GoldenGate processes
  • Executing DDL/DML statements
  • Invoking custom shell scripts

While this core functionality is well-known, GoldenGate’s true potential shines when you layer on filters, transformations, and automation using parameters like EVENTACTIONS.

Here's how I've used EVENTACTIONS with shell scripting to simplify operations:

  1. Automating GoldenGate Process Maintenance
    • Scenario: START/STOP goldengate process automatically.
    • Implementation:
      • Configure EVENTACTIONS to trigger a shell script when a specific error or transaction is detected.
      • The script checks the process status and restarts it if needed.
  2. Self-Service User Management
    • Scenario: Allow teams to request database user creation or password resets without DBA intervention.
    • Implementation:
      • Create a control table where operations like CREATE USER or ALTER USER are logged.
      • Use EVENTACTIONS to execute a script that reads inputs from the table and performs the action.

This approach can be extended to tasks like AWR/ADDM report generation, space management, or audit log cleanup – all triggered by GoldenGate!

Practical Implementations

1. Automated Process Maintenance

Scenario: Auto START/STOP GoldenGate processes
Implementation: Configure EVENTACTIONS to trigger status checks and process restarts

2. Self-Service User Management

Scenario: Enable user creation/password resets without DBA involvement
Implementation: Control table + EVENTACTIONS-triggered scripts

Sample Configuration

IGNOREDELETES
IGNOREINSERTS
TABLE GGADMIN.GG_PRCS_TRG_TB,
  EVENTACTIONS(
    SHELL ('/home/oracle/scripts/gg_proc_mgr.sh $PROC $OPS',
    VAR $PROC = PROCESS_NAME,
    VAR $OPS = OPERATION
  )
);
GETDELETES
GETINSERTS

Execution Example

sh /home/oracle/scripts/gg_proc_mgr.sh P_E01_1 STOP

Why IGNOREDELETES/IGNOREINSERTS?

IGNOREDELETES/INSERTS: These directives ensure GoldenGate ignores INSERT and DELETE operations on GG_PRCS_TRG_TB. Only UPDATEs trigger the EVENTACTIONS script.

Purpose: This design ensures automation is triggered only when modifying an existing process’s status (e.g., stopping/starting), not when adding/removing entries.

Master/Controller : E_E01_1 (Master process. This extract process capture data and execute tasks)

Database :

  • ORCLPDB01
  • ORCLPDB01
    • Goldengate Process :
  • E_B01_1
  • E_B01_2
  • P_B01_1
  • P_B01_2
  • P_E01_1
  • R_B01_1
  • R_B01_2
  • R_E01_1

    • In our example, we will test automated operations exclusively within this environment.
    Here, E_E01_1 serves as the master process, through which we will create users and manage the start/stop operations of GoldenGate processes.

    Goldengate operations will be controlled from the GG_PRCS_TRG_TB table. If an update operation changes from "start" to "stop," it triggers the execution of "/home/oracle/scripts/user_mgmt.sh", which stops the corresponding GG process.

    Modifying the process status from "start" to "stop" in the trigger table.

    UPDATE GG_PRCS_TRG_TB set OPERATION = 'STOP' where PROCESS_NAME='R_B01_2';
    commit;

    Once the change is committed, the Extract process captures it and executes "/home/oracle/scripts/user_mgmt.sh". Goldengate passes the values for the input arguments process_name and operation. 

    
IGNOREDELETES
IGNOREINSERTS
TABLE GGADMIN.GG_PRCS_TRG_TB,
  EVENTACTIONS(
    SHELL ('/home/oracle/scripts/gg_proc_mgr.sh $PROC $OPS',
    VAR $PROC = PROCESS_NAME,
    VAR $OPS = OPERATION
  )
);
GETDELETES
GETINSERTS

    Execution log:

    Before request to stop the process status

    
GGSCI (dgsrv1) 8> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     E_B01_1     00:00:02      00:00:00
EXTRACT     RUNNING     E_B01_2     00:00:00      00:00:04
EXTRACT     RUNNING     E_E01_1     00:00:00      00:00:14
EXTRACT     RUNNING     P_B01_1     00:00:00      00:00:06
EXTRACT     RUNNING     P_B01_2     00:00:00      00:00:06
EXTRACT     RUNNING     P_E01_1     00:00:00      00:00:06
REPLICAT    RUNNING     R_B01_1     00:00:00      00:00:00
REPLICAT    RUNNING     R_B01_2     00:00:00      00:00:13
REPLICAT    RUNNING     R_E01_1     00:00:00      00:00:02

    Change the process start to stop

    
SQL>  UPDATE GG_PRCS_TRG_TB set OPERATION = 'STOP' where PROCESS_NAME='R_B01_2';

1 row updated.

SQL> commit;

Commit complete.

SQL>

    Here we go

    Check the detail in report file, Look at text green in color which say what goldengate process is did when update find in database

    
2025-03-16 08:06:43  INFO    OGG-06507  
    MAP (TABLE) resolved (entry ORCLPDB01.GGADMIN.GG_PRCS_TRG_TB): TABLE "ORCLPDB01"."GGADMIN"."GG_PRCS_TRG_TB", 
EVENTACTIONS(SHELL ('/home/oracle/scripts/gg_proc_mgr.sh $PROC $OPS', VAR $PROC = PROCESS_NAME, VAR $OPS = OPERATION)). 
    
2025-03-16 08:06:43  WARNING OGG-06439  No unique key is defined for table GG_PRCS_TRG_TB. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2025-03-16 08:06:43  INFO    OGG-06509  Using the following key columns for source table ORCLPDB01.GGADMIN.GG_PRCS_TRG_TB: PROCESS_NAME, OPERATION, REQUESTED_TS.
2025-03-16 10:03:01  INFO    OGG-01286  
    Executing shell command '/home/oracle/scripts/gg_proc_mgr.sh R_B01_2 STOP'
     due to SHELL event for source table ORCLPDB01.GGADMIN.GG_PRCS_TRG_TB at redo sequence 214, RBA 172855364.
    
2025-03-16 10:03:01  INFO    OGG-05301  Shell command output: 'Current Status of R_B01_2: RUNNING'.
2025-03-16 10:03:01  INFO    OGG-05301  Shell command output: 'Process R_B01_2 is running. Stopping now...'.
2025-03-16 10:03:01  INFO    OGG-05301  Shell command output: ''.
2025-03-16 10:03:01  INFO    OGG-05301  Shell command output: 'Oracle GoldenGate Command Interpreter for Oracle'.
2025-03-16 10:03:01  INFO    OGG-05301  Shell command output: 'Version 19.24.0.0.240716 OGGCORE_19.24.0.0.0OGGRU_PLATFORMS_240706.1245_FBO'.
2025-03-16 10:03:01  INFO    OGG-05301  Shell command output: 'Linux, x64, 64bit (optimized), Oracle 19c  on Jul  6 2024 16:30:34'.
2025-03-16 10:03:01  INFO    OGG-05301  Shell command output: 'Operating system character set identified as UTF-8.'.
2025-03-16 10:03:01  INFO    OGG-05301  Shell command output: ''.
2025-03-16 10:03:01  INFO    OGG-05301  Shell command output: 'Copyright (C) 1995, 2024, Oracle and/or its affiliates. All rights reserved.'.
2025-03-16 10:03:01  INFO    OGG-05301  Shell command output: ''.
2025-03-16 10:03:01  INFO    OGG-01971  The previous message, 'INFO OGG-05301', repeated 2 times.
2025-03-16 10:03:01  INFO    OGG-05301  Shell command output: 'GGSCI (dgsrv1) 1>'.
2025-03-16 10:03:01  INFO    OGG-05301  Shell command output: 'Sending STOP request to REPLICAT R_B01_2 ...'.
2025-03-16 10:03:02  INFO    OGG-05301  Shell command output: 'Request processed.'.
2025-03-16 10:03:02  INFO    OGG-05301  Shell command output: ''.
2025-03-16 10:03:12  INFO    OGG-01971  The previous message, 'INFO OGG-05301', repeated 1 times.
2025-03-16 10:03:12  INFO    OGG-05301  Shell command output: 'GGSCI (dgsrv1) 2> Process R_B01_2 is now STOPPED.'.
2025-03-16 10:03:12  INFO    OGG-05301  Shell command output: 'Final status of R_B01_2: STOPPED'.
2025-03-16 10:03:12  INFO    OGG-01287  
    Successfully executed shell command '/home/oracle/scripts/gg_proc_mgr.sh R_B01_2 STOP'.

    Current goldengate process status

    
GGSCI (dgsrv1) 9> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     E_B01_1     00:00:02      00:00:02
EXTRACT     RUNNING     E_B01_2     00:00:00      00:00:06
EXTRACT     RUNNING     E_E01_1     00:00:00      00:00:16
EXTRACT     RUNNING     P_B01_1     00:00:00      00:00:08
EXTRACT     RUNNING     P_B01_2     00:00:00      00:00:08
EXTRACT     RUNNING     P_E01_1     00:00:00      00:00:08
REPLICAT    RUNNING     R_B01_1     00:00:00      00:00:02
REPLICAT    STOPPED     R_B01_2     00:00:00      00:00:01  <<<<<<< Now process changed to RUNNING to STOPPED
REPLICAT    RUNNING     R_E01_1     00:00:00      00:00:04

    Source code

    gg_proc_mgr.sh

    
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/19.0.0.0/db_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/oracm/lib:/lib:/usr/lib:/usr/local/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export TNS_ADMIN=$ORACLE_HOME/network/admin:$ORACLE_HOME/network/jlib
export GGATE_HOME=/ggate/app/ggate/GG19c
export PATH=$PATH:$HOME/.local/bin:$HOME/bin:/usr/bin:/bin:/usr/local/bin:$GGATE_HOME
PRCS=$1  # Process Name (Extract or Replicat)
OPS=$2   # Operation Type (start/stop)
if [[ -z "$PRCS" || -z "$OPS" ]]; then
    echo "Usage: $0 <Process_Name > <start|stop >"
    exit 1
fi
cd "$GGATE_HOME" || { echo "Error: Unable to access $GGATE_HOME"; exit 1; }
get_status() {
    ./ggsci <<EOF | grep -i "$PRCS:" | awk -F": " '{print $NF}'
status $PRCS
EOF
}
gg_prcs_status=$(get_status)
echo "Current Status of $PRCS: $gg_prcs_status"
if [[ "$OPS" == "START" ]]; then
    if [[ "$gg_prcs_status" != "RUNNING" ]]; then
        echo "Process $PRCS is stopped. Starting now..."
        ./ggsci <<EOF
start $PRCS
EOF
    else
        echo "Process $PRCS is already running. No action needed."
        exit 0
    fi
elif [[ "$OPS" == "STOP" ]]; then
    if [[ "$gg_prcs_status" == "RUNNING" ]]; then
        echo "Process $PRCS is running. Stopping now..."
        ./ggsci <<EOF
stop $PRCS
EOF
    else
        echo "Process $PRCS is already stopped. No action needed."
        exit 0
    fi
else
    echo "Invalid operation: $OPS. Use 'start' or 'stop'."
    exit 1
fi
timer_count=0
while true; do
    sleep 10
    gg_prcs_status=$(get_status)
    if [[ ("$OPS" == "START" && "$gg_prcs_status" == "RUNNING") ||
          ("$OPS" == "STOP" && "$gg_prcs_status" == "STOPPED") ]]; then
        echo "Process $PRCS is now $gg_prcs_status."
        break
    fi
    ((timer_count++))
    if [[ "$timer_count" -gt 3 ]]; then
        echo "Timeout occurred! Please check the logs."
        exit 1
    fi
done
echo "Final status of $PRCS: $gg_prcs_status"
    Location: V4P2+M3, Madurai, Tamil Nadu 625015, India

    Comments

    Post a Comment

    Popular posts from this blog

    Goldengate initial load options - File to Replicat

    Image
    Goldengate initial load options - File to Replicat This method allows the Extract process to write to a file on the target system, which the Replicat applies using SQL INSERT statements.  The Extract process generates a file in a universal format for the Replicat to load. This approach fundamentally resembles the standard GoldenGate data replication method used for change data capture (CDC). During the initial load, you can enable CDC. In a live environment, you should use the SQLPREDICATE key to specify the SCN# to maintain data integrity. Please follow the below steps to enable goldengate initial load using File to Replicat method On source: Add initial load extract process. Create a parameter file. Verify task(in...
    Read more

    Reset RMAN configuration all set to default

    Oracle Recovery Manager (RMAN) is a robust utility designed for the backup, restoration, and recovery of Oracle databases. RMAN simplifies database management by offering comprehensive backup options, automatic recovery features, and seamless integration with Oracle Database. It supports full and incremental backups, point-in-time recovery, and database cloning. RMAN can be executed through command-line, integrated with Oracle Enterprise Manager, and offers advanced features like block media recovery, encryption, and compression, ensuring efficient and secure database management. One of the most tedious processes is resetting all RMAN configurations in a single command. If you want to clear configurations, use " CONFIGURE..CLEAR" . Sometimes, restoring a standby or duplicate database can fail due to configurations from the source database. Therefore, it's essential to reset all configurations beforehand using the fo...
    Read more