Info Trandata from SQLPLUS

January 7, 2024
Categories: GoldenGate

Info Trandata from SQLPLUS Automation

Info Trandata equivalent information from SQLPLUS is extremely useful for checking if supplemental logging is activated on schema objects. If you are a seasoned GoldenGate professional, you know that activating supplemental logging at both the database and object level is a critical requirement to capture all the transactions in to the trail files.

Imagine a scenario where you need to verify supplemental logging on hundreds of tables after running the ADD TRANDATA or ADD SCHEMATRANDATA from the GoldenGate console or using Adminclient. Manually running ‘Info Trandata’ for each table through GoldenGate can be a tedious and time-consuming process. A practical solution? Crafting a simple script that performs this check quick & fast from your shell, saving you both time and effort.

ARCHITECTURE

Ways to check supplemental Logging

Info Trandata from CLI of Admin Client or GGSCI
Eg : Info Trandata <schema>.<TableName>
Trandata Information from GoldenGate UI console.
SQLPLUS method using the sql query.
select * from table(logmnr$always_suplog_columns( SCHEMA, TABLE ));

Why need for the script ?

Supplemental logging in Oracle databases adds extra logging information to the redo log files, which is essential for Oracle GoldenGate. Manually checking the supplemental logging status for each table can be tedious, especially in large databases. This is where the script comes into play, simplifying and automating this process.

Script Overview

This shell script operates by connecting to an Oracle database, fetching a list of table names from the specified schema (in this case, ‘SOE’), and then checking whether supplemental logging is enabled or disabled for each table. It categorizes the tables accordingly and provides a count for each category.
Key Features:

Dynamic SID Input: The script accepts the Oracle SID (or PDB name) as a command-line argument, allowing flexibility and ease of use across different databases.
Customizable Query: The SQL query within the script is tailor-made to fetch tables from the ‘FUSION’ schema but can be easily modified to suit other schemas.
Supplemental Logging Check: It employs Oracle’s LOGMNR$ALWAYS_SUPLOG_COLUMNS to determine the supplemental logging status of each table.
User-Friendly Output: The script neatly displays the tables with supplemental logging enabled and disabled, along with the respective counts.

				
					
#!/bin/bash

# Check if a SID is passed as an argument
if [ $# -eq 0 ]; then
    echo "No Oracle PDB Name or ORACLE SID provided. Usage: $0 PDBNAME/ORACLE_SID"
    exit 1
fi

# Use the first argument as the Oracle SID
DB_SID=$1
export ORACLE_PDB_SID=$DB_SID

# SQL query to get all table names from the SOE schema
SQL_QUERY="SELECT table_name FROM all_tables WHERE owner = 'SOE'"

# Function to check supplemental logging for a table
check_supplemental_logging() {
    local table_name=$1
    local check_query="SELECT 1 FROM TABLE(LOGMNR\$ALWAYS_SUPLOG_COLUMNS('FUSION', '${table_name}')) WHERE ROWNUM = 1"
    
    local result=$(sqlplus -s / as sysdba <<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
${check_query};
EXIT;
EOF
    )
    echo $result
}

# Fetch table names
echo "Fetching table names..."
table_names=$(sqlplus -s / as sysdba <<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
${SQL_QUERY};
EXIT;
EOF
)

# Verify if table_names is empty
if [ -z "$table_names" ]; then
    echo "No table names were fetched. Exiting."
    exit 1
fi

# Iterate over table names and check supplemental logging status
echo "Checking supplemental logging status..."
enabled_tables=()
disabled_tables=()

for table_name in $table_names; do
    # Trim whitespace
    table_name=$(echo $table_name | xargs)

    # Skip empty lines
    [ -z "$table_name" ] && continue

    # Check supplemental logging
    result=$(check_supplemental_logging $table_name)
    if [[ -n $result ]]; then
        enabled_tables+=("$table_name")
    else
        disabled_tables+=("$table_name")
    fi
done

# Display results and counts
echo "-------------------------------------------------------------------"
echo "SUPPLEMENTAL LOGGING ENABLED TABLES (Count: ${#enabled_tables[@]}):"
echo "-------------------------------------------------------------------"
printf '%s\n' "${enabled_tables[@]}"

echo "-------------------------------------------------------------------"
echo "SUPPLEMENTAL LOGGING DISABLED TABLES (Count: ${#disabled_tables[@]}):"
echo "-------------------------------------------------------------------"
printf '%s\n' "${disabled_tables[@]}"

Usage

To run the script, simply pass the Oracle SID/PDB_NAME as an argument:

./supplemental_logging_check.sh ORACLE_SID / PDBNAME

				
					Fetching table names...
Checking supplemental logging status...
-------------------------------------------------------------------
SUPPLEMENTAL LOGGING ENABLED TABLES (Count: 11):
-------------------------------------------------------------------
ORDERS
INVENTORIES
PRODUCT_DESCRIPTIONS
PRODUCT_INFORMATION
WAREHOUSES
ORDERENTRY_METADATA
LOGON
CARD_DETAILS
ORDER_ITEMS
ADDRESSES
CUSTOMERS

-------------------------------------------------------------------
SUPPLEMENTAL LOGGING DISABLED TABLES (Count: 0):
-------------------------------------------------------------------

SUMMARY:This script will be very useful in checking processes like checking supplemental logging status more efficient and error-free. It’s a handy tool and can be easily integrated into regular health checks.

For more information about documentation please check here.
Hope you found this article useful.

Disclaimer: The views expressed on this document are my own and do not necessarily reflect the views of Oracle.