🚀 DB BOT: Real-Time Oracle & GoldenGate Monitoring in Slack

In today's fast-paced DevOps environment, quick access to database metrics is essential. This blog will walk you through creating a Slack bot that provides real-time monitoring of Oracle databases and Golden Gate replication. With simple slash commands, your team can check tablespace usage, Flash Recovery Area status, and Golden Gate replication health directly in Slack.

Project Overview

Our "DB Bot" offers these key capabilities:

• Monitor tablespace usage across multiple Oracle databases
• Check Flash Recovery Area (FRA) status on multiple databases
• View GoldenGate process status across different servers
• List GoldenGate credential stores
• Monitor replication lag in GoldenGate

Prerequisites

• Node.js v14+
• Python 3.6+
• Oracle client libraries (instantclient_21_19)
• Access to Oracle databases and GoldenGate servers
• A Slack workspace with permissions to add apps

 Project Structure

oracle-slack-bot/

├── oracle_slack_bot.js   # Main bot application

├── .env                  # Environment variables

├── config/

│   └── users.json        # User access control

├── Oracle/

│   ├── tablespace.py     # Oracle tablespace monitoring

│   └── fra.py            # Flash Recovery Area monitoring

└── GoldenGate/

    └── gg_status.py      # GoldenGate monitoring

 

Step 1: Setting Up the Slack App

1. Go to https://api.slack.com/apps
2. Click "Create New App" → "From scratch"
3. Name your app "DB Bot" and select your workspace
4. Under "OAuth & Permissions," add these scopes:
• chat:write
• commands
• users:read
• users:read.email
5. Install the app to your workspace
6. Note the "Bot User OAuth Token" and "Signing Secret"

 Step 2: Setting Up the Project

First, create the project structure:

# Create project directory

mkdir -p oracle-slack-bot/{Oracle,GoldenGate,config}

cd oracle-slack-bot

# Initialize npm project

npm init -y

# Install dependencies

npm install @slack/bolt dotenv winston

# Set up Python virtual environment

python -m venv venv

source venv/bin/activate

pip install cx_Oracle python-dotenv

Create a .env file:

vi .env

# Slack credentials

SLACK_BOT_TOKEN=xoxb-your-bot-token

SLACK_SIGNING_SECRET=your-signing-secret

# Original database variables (for backward compatibility)

ORACLE_USER=system

ORACLE_PASSWORD=your-password

ORACLE_DSN=testdb-ho-01.com:1521/devdb

# Multiple database configuration

DB1_NAME=testdb-ho-01

DB1_USER=system

DB1_PASSWORD=your-password

DB1_DSN=testdb-ho-01.com:1521/devdb

DB2_NAME=testdb-ho-04

DB2_USER=system

DB2_PASSWORD=your-password

DB2_DSN=testdb-ho-04.com:1521/devdb

# GoldenGate hosts

GG1_HOST=testdb-ho-03.com

GG2_HOST=testdb-ho-06.com

 

Create config/users.json:

{

  "admin": ["testingbot@gmail.com"],

  "oracle": ["testingbot@gmail.com", "dba2@gmail.com"]

}

Step 3: Creating the Oracle Monitoring Scripts

Tablespace Monitoring Script (Oracle/tablespace.py)

https://github.com/Saravananlokesh/Slackbot/blob/main/tablespace.py  

Flash Recovery Area Monitoring Script (Oracle/fra.py)

https://github.com/Saravananlokesh/Slackbot/blob/main/fra.py 

Step 4: Creating the GoldenGate Monitoring Script

#!/usr/bin/env python

# filepath: GoldenGate/gg_status.py

https://github.com/Saravananlokesh/Slackbot/blob/main/gg_status.py 

Step 5: Creating the Slack Bot Application

https://github.com/Saravananlokesh/Slackbot/blob/main/oracle_slack_bot.js 

Step 6: Registering Slash Commands in Slack

Go to your Slack app's settings page and register these slash commands:

1. /tablespace - Checks tablespace usage
2. /fra - Checks Flash Recovery Area
3. /gginfo - Shows GoldenGate process status
4. /ggcredstore - Lists GoldenGate credential stores
5. /gglag - Checks GoldenGate replication lag
6. /showmyemail - Shows user email and permissions

For each command, set the Request URL to https://your-domain.com/slack/events (or your ngrok URL during development).

Step 7: Running the Bot

Make scripts executable:

chmod +x Oracle/tablespace.py

chmod +x Oracle/fra.py

chmod +x GoldenGate/gg_status.py

 

Start the bot:

node oracle_slack_bot.js

For development, expose the bot using ngrok:

ngrok http 3000

Step 8: Using the Bot in Slack

Once your bot is running and properly configured, you can use these commands in Slack:

Oracle Database Commands:

• /tablespace - Check tablespace usage on the default database
• /tablespace db2 - Check tablespace usage on the second database
• /fra - Check Flash Recovery Area on the default database
• /fra db2 - Check Flash Recovery Area on the second database

GoldenGate Commands:

• /gginfo - Show GoldenGate process status on the first server
• /gginfo gg2 - Show GoldenGate process status on the second server
• /ggcredstore - List credential stores on the first server
• /ggcredstore gg2 - List credential stores on the second server
• /gglag gg_test - Check lag using the specified credential store on the first server
• /gglag gg_test gg2 - Check lag using the specified credential store on the second server

Troubleshooting Common Issues

Oracle Connection Issues

If you see ORA-12154: TNS:could not resolve the connect identifier specified:

• Verify the DSN format in your .env file
• Check network connectivity to the database
• Ensure Oracle client libraries are properly installed

GoldenGate SSH Issues

If you see sudo: sorry, you must have a tty to run sudo:

• The -tt flag in the SSH command forces TTY allocation, which should fix this
• If still having issues, check sudo privileges for the oracle user on the GoldenGate servers

Slack Command Errors

If commands show "dispatch_failed":

• Check that your bot is running
• Verify your Request URLs in the Slack app settings
• Make sure your ngrok URL is current (if using ngrok)

Security Considerations

1. Access Control: The bot uses a combination of Slack user emails and IDs to control who can access which commands
2. Credential Management: All credentials are stored in the .env file and not hardcoded in scripts
3. Least Privilege: Database and SSH users should have only the permissions needed for their functions
4. Audit Logging: All command executions are logged for audit purposes

This Slack bot provides a convenient interface for monitoring Oracle databases and Golden Gate replication. It allows DBAs and developers to quickly check system status without needing to log into servers directly. The modular design makes it easy to add more databases or monitoring capabilities in the future.

Key benefits include:

• Reduced need for direct server access
• Quick access to critical metrics
• Consistent formatting of monitoring information
• Fine-grained access control for different team members
• Support for multiple database environments