DBADEEDS

Actions Expresses Priorities

Poor’s Man Oracle Databases Comparison Using Shell Scripts

Published by

DBADEEDS

on

Imagine this scenario: You’re a DBA at a small firm, migrating data between Oracle instances. Last week, a subtle schema drift caused a production outage—mismatched constraints led to invalid objects piling up. You vow never again. That’s where this script shines. It’s like having a vigilant watchdog that scans for differences in objects, users, privileges, and more, then emails you a neat CSV report. No licenses required, just Bash and Oracle basics. In this post, we’ll dive into the script’s story, its usefulness, and how you can adapt it for your own database battles. Managing multiple Oracle databases across environments. One day, after a routine patch, queries start failing mysteriously. Turns out, a developer added a unique constraint in dev but forgot to propagate it to prod. Manual checks? Tedious! It creates a temporary database link from the local DB to the remote one, runs a series of SQL queries to spot discrepancies, and compiles everything into a timestamped CSV. The report highlights everything from object count mismatches to privilege differences, ensuring nothing slips through.

Why “poor man’s”? Because it relies on free, built-in Oracle features like DBA views and SQL*Plus, wrapped in Bash. No GoldenGate, no OEM—just pure ingenuity.

The Full Script: compare_validate.sh

For completeness, here’s the entire script. You can copy-paste it directly, but remember to customize the configuration variables (like passwords and TNS aliases) before running.

#!/bin/bash
 
# Check if an argument (ORACLE_SID) was provided
if [ "$#" -ne 1 ]; then
    echo "Usage: $0 <LOCAL_ORACLE_SID>"
    echo "Example: $0 DEEDSDEV"
    exit 1
fi
 
# --- 1. Environment Configuration ---
 
# Set Oracle environment variables (adjust paths as needed)
export ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
export ORACLE_SID=$1 # Use the command line argument here
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
 
echo "Attempting to connect to local SID: $ORACLE_SID"
echo "Using ORACLE_HOME: $ORACLE_HOME"
 
# Check if sqlplus is available
if ! command -v sqlplus &> /dev/null
then
    echo "ERROR: sqlplus command not found. Please verify ORACLE_HOME and PATH settings."
    exit 1
fi
 
# --- 2. Script Configuration Variables ---
LOCAL_DB_USER=“DBA”
LOCAL_DB_PASS="" # !! REPLACE THIS WITH YOUR PASSWORD !!
REMOTE_DB_TNS_ALIAS="DEEDS1" # The TNS entry name for the remote DB
REMOTE_DB_LINK_NAME="DBA_DEEDS1" # The name for the dblink to be created and dropped
 
EMAIL_RECIPIENT=Sandeepreddy@dbadeeds.com
EMAIL_SUBJECT="Oracle Database Comparison Report"
REPORT_FILE="db_validation_report_$(date +%Y%m%d%H%M%S).csv"
 
# --- 3. Functions ---
 
function run_sql() {
    local sql_query="$1"
    # Connects to the local database using sqlplus and runs the query
    sqlplus -S "$LOCAL_DB_USER/$LOCAL_DB_PASS@$ORACLE_SID" <<EOF
SET ECHO OFF
SET FEEDBACK OFF
WHENEVER SQLERROR EXIT FAILURE
$sql_query
EOF
    if [ $? -ne 0 ]; then
        echo "SQL Error occurred during operation."
        exit 1
    fi
}
 
function run_sql_to_csv() {
    local section_header="$1"
    local sql_query="$2"
    local output_file="$3"
 
    echo "Running SQL query for $section_header..."
 
    # Append the header to the report file
    echo -e "\n--- $section_header ---" >> "$output_file"
 
    sqlplus -S "$LOCAL_DB_USER/$LOCAL_DB_PASS"@$ORACLE_SID <<EOF >> "$output_file"
SET MARKUP CSV ON QUOTE ON
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING ON
SET LINESIZE 32767
SET PAGESIZE 0
WHENEVER SQLERROR EXIT FAILURE
 
$sql_query
EOF
    if [ $? -ne 0 ]; then
        echo "Error running SQL query for '$section_header'. Exiting script."
        # Drop the link on failure before exiting
        drop_dblink
        exit 1
    fi
    echo "Query '$section_header' completed successfully."
}
 
function send_email() {
    local recipient="$1"
    local subject="$2"
    local attachment="$3"
    local body="Please find the attached database comparison report."
    echo "$body" | mailx -s "$subject" -a "$attachment" "$recipient"
}
 
function create_dblink() {
    echo "Attempting to create database link $REMOTE_DB_LINK_NAME..."
    run_sql "CREATE DATABASE LINK $REMOTE_DB_LINK_NAME USING '$REMOTE_DB_TNS_ALIAS';"
    echo "Database link created successfully."
}
 
function drop_dblink() {
    echo "Attempting to drop database link $REMOTE_DB_LINK_NAME..."
    # Check if the link exists before trying to drop it (optional safety check)
    run_sql "DROP DATABASE LINK $REMOTE_DB_LINK_NAME;"
    echo "Database link dropped."
}
 
# --- 4. Main Execution ---
 
trap drop_dblink EXIT # Ensure link is dropped even if script fails unexpectedly
 
# Ensure the report file is clear or newly created
> "$REPORT_FILE"
echo "Starting database comparison script..."
 
# Create the database link
create_dblink
 
# Verify connectivity immediately
echo "Verifying database link connectivity..."
run_sql "SELECT * FROM dual@$REMOTE_DB_LINK_NAME;"
echo "Connectivity check successful."
 
# Run all comparison queries
# Note: The SQL queries below have been updated to use the $REMOTE_DB_LINK_NAME variable
 
run_sql_to_csv "Object Count Differences" "
WITH local_counts AS (
SELECT owner, object_type, COUNT(*) AS local_count
FROM dba_objects
WHERE owner IN (SELECT username FROM dba_users WHERE oracle_maintained = 'N')
GROUP BY owner, object_type
),
remote_counts AS (
SELECT owner, object_type, COUNT(*) AS remote_count
FROM dba_objects@$REMOTE_DB_LINK_NAME
WHERE owner IN (SELECT username FROM dba_users@$REMOTE_DB_LINK_NAME WHERE oracle_maintained = 'N')
GROUP BY owner, object_type
)
SELECT NVL(lc.owner, rc.owner) AS schema_name, NVL(lc.object_type, rc.object_type) AS object_type,
       NVL(lc.local_count, 0) AS local_count, NVL(rc.remote_count, 0) AS remote_count,
       NVL(lc.local_count, 0) - NVL(rc.remote_count, 0) AS diff
FROM local_counts lc FULL OUTER JOIN remote_counts rc
ON lc.owner = rc.owner AND lc.object_type = rc.object_type
WHERE NVL(lc.local_count, 0) <> NVL(rc.remote_count, 0)
ORDER BY schema_name, object_type;" "$REPORT_FILE"
 
run_sql_to_csv "Invalid Object Status Comparison" "
SELECT
    Object_Name,
    Object_Type,
    Owner_Name,
    Invalid_Status_Comparison
FROM
    (
    SELECT
        COALESCE(L.OBJECT_NAME, R.OBJECT_NAME) AS Object_Name,
        COALESCE(L.OBJECT_TYPE, R.OBJECT_TYPE) AS Object_Type,
        COALESCE(L.OWNER, R.OWNER) AS Owner_Name,
        CASE
            WHEN L.OBJECT_NAME IS NOT NULL AND R.OBJECT_NAME IS NOT NULL THEN 'Invalid in Both DBs'
            WHEN L.OBJECT_NAME IS NOT NULL AND R.OBJECT_NAME IS NULL THEN 'Invalid Only in Local DB'
            WHEN R.OBJECT_NAME IS NOT NULL AND L.OBJECT_NAME IS NULL THEN 'Invalid Only in Remote DB'
        END AS Invalid_Status_Comparison
    FROM
        -- Local database invalid objects (filtered in inline view)
        (SELECT OBJECT_NAME, OBJECT_TYPE, OWNER FROM DBA_OBJECTS
         WHERE STATUS = 'INVALID'
         AND OWNER NOT IN ('SYS', 'SYSTEM', 'SCOTT', 'HR', 'SH', 'OE', 'PM', 'IX', 'DIP', 'ORACLE_OCM', 'OUTLN', 'ANONYMOUS', 'APEX_PUBLIC_USER', 'APPQOSSYS', 'CTXSYS', 'DBSNMP', 'FLOWS_FILES', 'MDDATA', 'MDSYS', 'OLAPSYS', 'OWBSYS', 'ORDDATA', 'ORDPLUGINS', 'ORDSYS', 'SI_INFORMTN_SCHEMA', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'WMSYS', 'XDB', 'XS\$IMDP')) L
    FULL OUTER JOIN
        -- Remote database invalid objects (filtered in inline view)
        (SELECT OBJECT_NAME, OBJECT_TYPE, OWNER FROM DBA_OBJECTS@$REMOTE_DB_LINK_NAME
         WHERE STATUS = 'INVALID'
         AND OWNER NOT IN ('SYS', 'SYSTEM', 'SCOTT', 'HR', 'SH', 'OE', 'PM', 'IX', 'DIP', 'ORACLE_OCM', 'OUTLN', 'ANONYMOUS', 'APEX_PUBLIC_USER', 'APPQOSSYS', 'CTXSYS', 'DBSNMP', 'FLOWS_FILES', 'MDDATA', 'MDSYS', 'OLAPSYS', 'OWBSYS', 'ORDDATA', 'ORDPLUGINS', 'ORDSYS', 'SI_INFORMTN_SCHEMA', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'WMSYS', 'XDB', 'XS\$IMDP')) R
    ON L.OBJECT_NAME = R.OBJECT_NAME
    AND L.OBJECT_TYPE = R.OBJECT_TYPE
    AND L.OWNER = R.OWNER
    ) combined_results -- Alias the inner query so we can filter the calculated column
WHERE
    INVALID_STATUS_COMPARISON != 'Invalid in Both DBs'
ORDER BY
    Owner_Name,
    Object_Name,
    Object_Type;" "$REPORT_FILE"
 
run_sql_to_csv "Object Name Comparison: Local Only vs Remote Only" "
SELECT 'LOCAL_ONLY' AS source, o.owner, o.object_type, o.object_name
FROM dba_objects o
WHERE o.owner IN (SELECT username FROM dba_users WHERE oracle_maintained = 'N')
AND o.object_name NOT LIKE 'SYS\_%' ESCAPE '\' -- Corrected condition with ESCAPE
AND NOT EXISTS (
    SELECT 1 FROM dba_objects@$REMOTE_DB_LINK_NAME r
    WHERE r.owner = o.owner AND r.object_type = o.object_type AND r.object_name = o.object_name
)
UNION ALL
SELECT 'REMOTE_ONLY' AS source, r.owner, r.object_type, r.object_name
FROM dba_objects@$REMOTE_DB_LINK_NAME r
WHERE r.owner IN (SELECT username FROM dba_users@$REMOTE_DB_LINK_NAME WHERE oracle_maintained = 'N')
AND r.object_name NOT LIKE 'SYS\_%' ESCAPE '\' -- Corrected condition with ESCAPE
AND NOT EXISTS (
    SELECT 1 FROM dba_objects l
    WHERE l.owner = r.owner AND l.object_type = r.object_type AND l.object_name = r.object_name
)
ORDER BY source, owner, object_type, object_name;" "$REPORT_FILE"
 
run_sql_to_csv "Public Synonyms Count Comparison" "
SELECT * FROM (
SELECT 'LOCAL' AS source, COUNT(*) AS synonym_count
FROM dba_synonyms
WHERE owner = 'PUBLIC' AND table_owner IN (SELECT username FROM dba_users WHERE oracle_maintained != 'Y')
UNION ALL
SELECT 'REMOTE' AS source, COUNT(*) AS synonym_count
FROM dba_synonyms@$REMOTE_DB_LINK_NAME
WHERE owner = 'PUBLIC' AND table_owner IN (SELECT username FROM dba_users@$REMOTE_DB_LINK_NAME WHERE oracle_maintained != 'Y')
) ORDER BY source;" "$REPORT_FILE"
 
 
run_sql_to_csv "User Comparison (Non-maintained only)" "
SELECT NVL(l.username, r.username) AS username,
       CASE WHEN l.username IS NULL THEN 'REMOTE_ONLY'
            WHEN r.username IS NULL THEN 'LOCAL_ONLY'
            ELSE 'BOTH' END AS location_status,
       NVL(l.created, r.created) AS created_date_source
FROM dba_users l
FULL OUTER JOIN dba_users@$REMOTE_DB_LINK_NAME r
ON l.username = r.username
WHERE NVL(l.oracle_maintained, 'N') = 'N' OR NVL(r.oracle_maintained, 'N') = 'N'
AND l.username IS NULL OR r.username IS NULL
ORDER BY location_status, username;" "$REPORT_FILE"
 
 
run_sql_to_csv "Role Comparison (Granted to Users)" "
WITH local_roles AS (
    SELECT grantee, granted_role, admin_option, delegate_option
    FROM dba_role_privs
    WHERE grantee IN (SELECT username FROM dba_users WHERE oracle_maintained = 'N')
),
remote_roles AS (
    SELECT grantee, granted_role, admin_option, delegate_option
    FROM dba_role_privs@$REMOTE_DB_LINK_NAME
    WHERE grantee IN (SELECT username FROM dba_users@$REMOTE_DB_LINK_NAME WHERE oracle_maintained = 'N')
)
SELECT NVL(lr.grantee, rr.grantee) AS grantee, NVL(lr.granted_role, rr.granted_role) AS granted_role,
       CASE WHEN lr.grantee IS NULL THEN 'REMOTE_ONLY' WHEN rr.grantee IS NULL THEN 'LOCAL_ONLY' ELSE 'BOTH' END AS location_status
FROM local_roles lr FULL OUTER JOIN remote_roles rr
ON lr.grantee = rr.grantee AND lr.granted_role = rr.granted_role
WHERE lr.grantee IS NULL OR rr.grantee IS NULL
ORDER BY grantee, granted_role;" "$REPORT_FILE"
 
 
run_sql_to_csv "System Privilege Comparison" "
WITH local_sys AS (
    SELECT grantee, privilege, admin_option
    FROM dba_sys_privs
    WHERE grantee IN (SELECT username FROM dba_users WHERE oracle_maintained = 'N')
),
remote_sys AS (
    SELECT grantee, privilege, admin_option
    FROM dba_sys_privs@$REMOTE_DB_LINK_NAME
    WHERE grantee IN (SELECT username FROM dba_users@$REMOTE_DB_LINK_NAME WHERE oracle_maintained = 'N')
)
SELECT NVL(ls.grantee, rs.grantee) AS grantee, NVL(ls.privilege, rs.privilege) AS privilege,
       CASE WHEN ls.grantee IS NULL THEN 'REMOTE_ONLY' WHEN rs.grantee IS NULL THEN 'LOCAL_ONLY' ELSE 'BOTH' END AS location_status
FROM local_sys ls FULL OUTER JOIN remote_sys rs
ON ls.grantee = rs.grantee AND ls.privilege = rs.privilege
WHERE ls.grantee IS NULL OR rs.grantee IS NULL
ORDER BY grantee, privilege;" "$REPORT_FILE"
 
 
run_sql_to_csv "Constraint Comparison (PK/UK)" "
WITH local_cons AS (
    SELECT owner, table_name, constraint_name, constraint_type, status
    FROM dba_constraints
    WHERE owner IN (SELECT username FROM dba_users WHERE oracle_maintained = 'N') AND constraint_type IN ('P', 'U')
),
remote_cons AS (
    SELECT owner, table_name, constraint_name, constraint_type, status
    FROM dba_constraints@$REMOTE_DB_LINK_NAME
    WHERE owner IN (SELECT username FROM dba_users@$REMOTE_DB_LINK_NAME WHERE oracle_maintained = 'N') AND constraint_type IN ('P', 'U')
)
SELECT NVL(lc.owner, rc.owner) AS owner, NVL(lc.table_name, rc.table_name) AS table_name,
       NVL(lc.constraint_name, rc.constraint_name) AS constraint_name,
       NVL(lc.constraint_type, rc.constraint_type) AS constraint_type,
       CASE WHEN lc.owner IS NULL THEN 'REMOTE_ONLY' WHEN rc.owner IS NULL THEN 'LOCAL_ONLY' ELSE 'BOTH' END AS location_status,
       lc.status AS local_status, rc.status AS remote_status
FROM local_cons lc FULL OUTER JOIN remote_cons rc
ON lc.owner = rc.owner AND lc.constraint_name = rc.constraint_name AND lc.table_name = rc.table_name
WHERE lc.owner IS NULL OR rc.owner IS NULL OR lc.status <> rc.status
ORDER BY owner, table_name, constraint_name;" "$REPORT_FILE"
 
echo "All queries completed. Report generated: $REPORT_FILE"
 
# Email the report (the trap will handle dropping the dblink after the script exits)
send_email "$EMAIL_RECIPIENT" "$EMAIL_SUBJECT" "$REPORT_FILE"
 
echo "Script finished."

Breaking Down the Script: How It Works

The script is a self-contained Bash powerhouse. Here’s a high-level overview:

1. Setup and Configuration

  • Environment Variables: Sets ORACLE_HOME, ORACLE_SID (passed as an argument), PATH, and LD_LIBRARY_PATH. This ensures SQL*Plus can connect.
  • Config Vars: Define local user/pass, remote TNS alias, DB link name, email details, and report file. Pro Tip: Replace the empty password with a secure one, or better yet, use Oracle Wallet for credentials.
  • Checks: Verifies sqlplus availability and argument count.

2. Key Functions

  • run_sql: Executes SQL queries on the local DB via SQL*Plus, exiting on errors.
  • run_sql_to_csv: Runs queries and appends results to the CSV report in markup format (CSV with quotes).
  • send_email: Uses mailx to email the report.
  • create_dblink / drop_dblink: Creates a temporary DB link to the remote DB and drops it on exit (via trap for cleanup).

3. Main Execution Flow

  • Clears/creates the report file.
  • Creates the DB link and verifies connectivity with a simple SELECT * FROM dual@remote.
  • Runs a battery of comparison queries, each appended to the report:
  • Object Count Differences: Compares counts of objects (tables, views, etc.) per schema, highlighting diffs.
  • Invalid Object Status: Spots invalid objects unique to local/remote or both.
  • Object Name Comparison: Lists objects only in local or remote.
  • Public Synonyms Count: Compares counts of public synonyms.
  • User Comparison: Identifies users unique to each DB (non-Oracle-maintained).
  • Role Comparison: Checks roles granted to users, flagging mismatches.
  • System Privilege Comparison: Similar for system privileges.
  • Constraint Comparison (PK/UK): Verifies primary/unique constraints, including status differences.
  • Emails the report and cleans up.

The queries use FULL OUTER JOINs for comprehensive diffs, filtering out system schemas to focus on user data.

In an era of cloud migrations and hybrid setups (as of late 2025, with Oracle 23c gaining traction), database consistency is paramount.

Potential pitfalls? It assumes TNS connectivity and proper privileges. For large DBs, queries might be slow—optimize with indexes if needed. Also, handle passwords securely to avoid exposure.

How to Use It

  1. Save as compare_validate.sh and make executable: chmod +x compare_validate.sh.
  2. Update configs: ORACLE_HOME, user/pass, TNS alias (ensure tnsnames.ora has the remote entry).
  3. Run: ./compare_validate.sh YOUR_LOCAL_SID (e.g., ./compare_validate.sh DEEDSDEV).
  4. Check your email for the report. Review diffs and act!

Wrapping Up: Empower Your DBA Toolkit

This “poor man’s” script proves that simplicity often trumps complexity. In Sandeep’s story, it turned reactive firefighting into proactive peace of mind. Whether you’re validating a migration, auditing security, or just ensuring sync between envs, give it a spin. It’s a testament to Bash’s power in the Oracle ecosystem.

Got improvements or war stories? Share in the comments! Happy scripting. 🚀

Leave a comment

Previous Post

Recent posts

Quote of the week

“I asked my database for a joke, but it returned a NULL value. Guess it doesn’t have a sense of humor.”

~ Gpt

DBADEEDS

About

A passionate baseball fan blog celebrating America’s favorite pastime.

Topics

Follow Us

Blog at WordPress.com.