Automating DBA Tasks with Oracle GoldenGate - Part 2

Automating DBA Tasks with Oracle GoldenGate

In this post, we will explore how to automate DBA jobs with Goldengate. If you're reading this directly, I recommend checking out Part 1 first to understand how it works. If you've already read Part 1, feel free to continue from here! Or you are already familiar with goldengate you are good enough to continue.

Steps to Automate User Management in Goldengate

  • Configure Goldengate parameters and place the required shell and SQL scripts.
  • Add the necessary input into GG_USERMGMT_TB, such as the username, database name, and granted roles.
  • Verify that the user has been successfully created in the database.

    • This setup is sufficient to bring your bright idea to life using your environment. Here, the E_E01_1 extract acts as the master process to automate DBA job user management operations.
    I have two databases, orclpdb01 and orclpdb02, with the master extract process configured and running on orclpdb01. The E_E01_1 process functions as a watchdog, monitoring the GG_USERMGMT_TB table. This table requires three mandatory inputs: DB_NAME, USER_NAME, and ROLE.
    Once you add this information to GG_USERMGMT_TB and commit the transaction, the extract process captures the changes and triggers the execution of the user_mgmt.sh script. The script retrieves the values for DB_NAME, USER_NAME, and ROLE from the table to perform the necessary user management actions.

    Parameter Descriptions:

  • DB_NAME → Target database
  • USER_NAME → Database account to be created on the target database
  • ROLE → Level of access assigned to the database user

    • Literally, GoldenGate triggers the user management script as shown below:
    sh user_mgmt.sh ORCLPDB02 skumar DBA

    This script connects to ORCLPDB01 and creates the user skumar with DBA privileges on the specified target database.

    Demo:

    
Insert data into GG_USRMGMT_TB
insert into GG_USERMGMT_TB (DB_NAME,USER_NAME,ROLE)
VALUES ('ORCLPDB02','SKUMAR','DBA');
Commit;

    Once inserted above statment goldengate will call user_mgmt.sh as per below parameter configuration setup

    
TABLE GGADMIN.GG_USRMGMT_TB 
EVENTACTIONS(
SHELL (
'/home/oracle/scripts/user_mgmt.sh $DB_NAME $USER_NAME $ROLE', 
VAR $DB_NAME = DB_NAME,
VAR $USER_NAME = USER_NAME, 
VAR $ROLE = @IF ( @COLTEST(ROLE, MISSING, INVALID), 'CONNECT',ROLE )
)
);

    Execution log:

    
---------------------------------------------------------  
Verify whether SKUMAR User is present or on in ORCLPDB02
---------------------------------------------------------
SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 ORCLPDB02                      READ WRITE NO
SQL> select username from dba_users where username='SKUMAR';

no rows selected

SQL>

As result user not exsit in database. Now we are good to create on database. 
------------------------------------------------------------------------------------------------------
--Create SKUMAR user by insert in user management in ggadmin schema on master databsae
------------------------------------------------------------------------------------------------------
SQL> select name from v$pdbs;

NAME
--------------------------------------------------------------------------------
ORCLPDB01

SQL> insert into GG_USRMGMT_TB (db_name,user_name,role) values ('ORCLPDB02','SKUMAR','DBA');

1 row created.

SQL> commit;

Commit complete.

SQL>
----------------------------------------------------------------------------
Open E_E01_1.rpt file to verify whether funcation called as executed or not
----------------------------------------------------------------------------

2025-03-16 19:37:45  INFO    OGG-06507  
    MAP (TABLE) resolved (entry ORCLPDB01.GGADMIN.GG_USRMGMT_TB): TABLE "ORCLPDB01"."GGADMIN"."GG_USRMGMT_TB" EVENTACTIONS(
SHELL ('/home/oracle/scripts/user_mgmt.sh $DB_NAME $USER_NAME $ROLE', 
VAR $DB_NAME = DB_NAME, VAR $USER_NAME = USER_NAME, VAR $ROLE = @IF ( @COLTEST(ROLE, MISSING, INVALID), 'CONNECT', ROLE )), IGNORE).
      
2025-03-16 19:37:45  INFO    OGG-06509  Using the following key columns for source table ORCLPDB01.GGADMIN.GG_USRMGMT_TB: DB_NAME, USER_NAME.
2025-03-16 19:37:45  INFO    OGG-01286  
    Executing shell command '/home/oracle/scripts/user_mgmt.sh ORCLPDB02 SKUMAR DBA' due to SHELL event for source table ORCLPDB01.GGADMIN.GG_USRMGMT_TB at redo sequence 216, RBA 96238096.
    
2025-03-16 19:37:45  INFO    OGG-05301  Shell command output: ''.
2025-03-16 19:37:45  INFO    OGG-05301  Shell command output: 'SQL*Plus: Release 19.0.0.0.0 - Production on Sun Mar 16 19:37:45 2025'.
2025-03-16 19:37:45  INFO    OGG-05301  Shell command output: 'Version 19.25.0.0.0'.
2025-03-16 19:37:45  INFO    OGG-05301  Shell command output: ''.
2025-03-16 19:37:45  INFO    OGG-05301  Shell command output: 'Copyright (c) 1982, 2024, Oracle.  All rights reserved.'.
2025-03-16 19:37:45  INFO    OGG-05301  Shell command output: ''.
2025-03-16 19:37:45  INFO    OGG-05301  Shell command output: 'Last Successful login time: Sun Mar 16 2025 00:33:16 +05:30'.
2025-03-16 19:37:45  INFO    OGG-05301  Shell command output: ''.
2025-03-16 19:37:45  INFO    OGG-05301  Shell command output: 'Connected to:'.
2025-03-16 19:37:45  INFO    OGG-05301  Shell command output: 'Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production'.
2025-03-16 19:37:45  INFO    OGG-05301  Shell command output: 'Version 19.25.0.0.0'.
2025-03-16 19:37:45  INFO    OGG-05301  Shell command output: ''.
2025-03-16 19:37:45  INFO    OGG-05301  Shell command output: 'SQL> old   1: create user &user_name identified by oracle default tablespace users profile default'.
2025-03-16 19:37:45  INFO    OGG-05301  Shell command output: 'new   1: create user SKUMAR identified by oracle default tablespace users profile default'.
2025-03-16 19:37:46  INFO    OGG-05301  Shell command output: ''.
2025-03-16 19:37:46  INFO    OGG-05301  Shell command output: 'User created.'.
2025-03-16 19:37:46  INFO    OGG-05301  Shell command output: ''.
2025-03-16 19:37:46  INFO    OGG-05301  Shell command output: 'old   1: grant connect,&grant_role to &user_name'.
2025-03-16 19:37:46  INFO    OGG-05301  Shell command output: 'new   1: grant connect,DBA to SKUMAR'.
2025-03-16 19:37:46  INFO    OGG-05301  Shell command output: ''.
2025-03-16 19:37:46  INFO    OGG-05301  Shell command output: 'Grant succeeded.'.
2025-03-16 19:37:46  INFO    OGG-05301  Shell command output: ''.
2025-03-16 19:37:46  INFO    OGG-05301  Shell command output: 'SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production'.
2025-03-16 19:37:46  INFO    OGG-05301  Shell command output: 'Version 19.25.0.0.0'.
2025-03-16 19:37:46  INFO    OGG-01287  
    Successfully executed shell command '/home/oracle/scripts/user_mgmt.sh ORCLPDB02 SKUMAR DBA'.
    

----------------------------------------------------------------------------
Verify whether user is create on database. Connect to ORCLPDB02 (Target DB)
----------------------------------------------------------------------------
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 ORCLPDB02                      READ WRITE NO
SQL> select username,created from dba_users where username='SKUMAR';

USERNAME                       CREATED
------------------------------ -------------------------
SKUMAR                         16-MAR-25

    Now, I can see that the user SKUMAR has been created in the database through the GoldenGate process. In this setup, no DBA directly connected to the database to create users. Instead, GoldenGate automated the process. Similarly, we can use GoldenGate to automate routine DBA tasks, making database management more efficient and seamless.

    


    user_mgmt.sh
    

#!/usr/bin/bash
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/19.0.0.0/db_1
export TNS_ADMIN=$ORACLE_HOME/network/admin
export PATH=$PATH:$HOME/.local/bin:$HOME/bin:${PATH}:/usr/bin:/bin:/usr/local/bin:${PATH}:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/oracm/lib:/lib:/usr/lib:/usr/local/lib
export SQLPATH=/home/oracle/scripts

DB_NAME=$1
USER_NAME=$2
GRANT_ROLE=$3
sqlplus c##dbops/oracle@${DB_NAME} << EOF
@create_user.sql $USER_NAME $GRANT_ROLE
EOF

DB_USER_NAME=$(sqlplus -s c##dbops/oracle@"${DB_NAME}" << EOF
set head off feedback off timing off verify off
@get_username.sql $USER_NAME
exit;
EOF
)
DB_USER_NAME=$(echo "$DB_USER_NAME"| tr -d 'n'|sed 's/[[:space:]*]//g')

if [[ "$USER_NAME" == "$DB_USER_NAME" ]]; then
    echo "$USER_NAME created successfully on $DB_NAME"
fi


    create_user.sql
    
set define '&'
define user_name=&1
define grant_role=&2

create user &user_name identified by oracle default tablespace users profile default;
grant connect,&grant_role to '&user_name';


    get_username.sql
    

set define '&'
define user_name=&1
set head off feedback off timing off veri off
select username from dba_users where username='&user_name';

    Note: The provided shell script, SQL script, and Goldengate parameter configuration are designed to work for inserting operations into GG_USERMGMT_TB. If you need to perform actions like password resets, user deletion, or privilege revocation, you will need to customize them based on your system’s design. Looking for a complete solution based on demand? Let me know, and I'll update this post!

    Comments

    Post a Comment

    Popular posts from this blog

    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: Starting or stopping GoldenGate processes – Automate the m...
    Read more

    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