1228

#!/bin/bash

# Define the path to oratab

ORATAB="/etc/oratab"

# Check if oratab exists

if [ ! -f $ORATAB ]; then

    echo "$ORATAB not found. Exiting."

    exit 1

fi

DB_STATUS_FILE="/tmp/db_status.log"

GGSCI_PATH="/usr/local/bin/ggsci"  # Modify this to your GoldenGate installation path

LAG_THRESHOLD=0  # Set your lag threshold in seconds (0 for no lag allowed)

# Arrays to hold summary information

DB_HEALTH_GREEN=()

DB_NOT_OK=()

WALLET_COUNT=0

WALLET_DB_LIST=()

GOLDENGATE_FOUND="No"

# Function to set Oracle environment for a specific database

set_oracle_env() {

    DB_NAME=$1

    ORACLE_HOME=$2

    export ORACLE_HOME=$ORACLE_HOME

    export ORACLE_SID=$DB_NAME

    export PATH=$ORACLE_HOME/bin:$PATH

    echo "Set environment for database: $DB_NAME with ORACLE_HOME: $ORACLE_HOME"

}

# Function to check if a database is up and running

check_db_status() {

    DB_NAME=$1

    echo "Checking status for database: $DB_NAME..."

    ps -ef | grep pmon | grep $DB_NAME | grep -v grep > $DB_STATUS_FILE

    if [ -s $DB_STATUS_FILE ]; then

        echo "Database $DB_NAME is up and running."

        return 0

    else

        echo "Database $DB_NAME is down."

        DB_NOT_OK+=("$DB_NAME is down: NOT_OK")

        return 1

    fi

}

# Function to perform Data Guard checks with dgmgrl, including lag

check_dgmgrl_lag() {

    DB_NAME=$1

    echo "Performing Data Guard checks for $DB_NAME..."

    # Check if log_archive_config is set

    LOG_ARCHIVE_CONFIG=$(sqlplus -s / as sysdba <<EOF

    SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF;

    SELECT VALUE FROM V\$PARAMETER WHERE NAME = 'log_archive_config';

    EXIT;

EOF

    )

    if [ -z "$LOG_ARCHIVE_CONFIG" ]; then

        echo "$DB_NAME is STANDALONE. Skipping Data Guard checks."

        DB_HEALTH_GREEN+=("$DB_NAME DB HC green (Standalone - no Data Guard checks)")

        return

    fi

    dgmgrl <<EOF > /tmp/dg_status.log

    connect /;

    show configuration;

    exit;

EOF

    if grep -q "ORA-" /tmp/dg_status.log; then

        echo "DG Broker checks for $DB_NAME: NOT_OK"

        DB_NOT_OK+=("$DB_NAME DG Broker checks: NOT_OK")

    else

        echo "Data Guard configuration checked for $DB_NAME."

        # Checking for lag

        LAG_INFO=$(sqlplus -s / as sysdba <<EOF

        SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF;

        select distinct a.name "DBName", b.t1 thread#, b.ps "Last_Seq_On_Primary", c.ss "Last_Seq_Applied_on_Standby", b.ps-c.ss "GAP" from 

        (select name from v\$database) a, 

        (select thread# t1, max(sequence#) ps from v\$archived_log group by thread#) b, 

        (select thread# t2, max(sequence#) ss from v\$archived_log where applied='YES' group by thread#) c 

        where b.t1 = c.t2; 

        EXIT;

EOF

        )

        if echo "$LAG_INFO" | grep -q "GAP"; then

            LAG=$(echo "$LAG_INFO" | grep 'GAP' | awk '{print $NF}')

            if [ "$LAG" -gt $LAG_THRESHOLD ]; then

                echo "$DB_NAME has Data Guard lag: GAP: ${LAG} seconds."

                DB_NOT_OK+=("$DB_NAME has replication lag: NOT_OK")

            else

                DB_HEALTH_GREEN+=("$DB_NAME DB HC green")

            fi

        else

            DB_HEALTH_GREEN+=("$DB_NAME DB HC green")

        fi

    fi

}

# Function to check if the database is in restricted mode and primary/standby

check_db_mode() {

    DB_NAME=$1

    echo "Checking mode for database: $DB_NAME..."

    sqlplus -s / as sysdba <<EOF > /tmp/db_mode.log

    SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF;

    Select restricted, database_role from v\$pdbs, v\$database;

    EXIT;

EOF

    if [ $? -eq 0 ]; then

        DATABASE_ROLE=$(grep -i "STANDBY" /tmp/db_mode.log)

        PRIMARY_ROLE=$(grep -i "PRIMARY" /tmp/db_mode.log)

        RESTRICTED=$(grep -i "YES" /tmp/db_mode.log)

        if [ -n "$DATABASE_ROLE" ]; then

            echo "Database $DB_NAME is a standby database."

            DB_HEALTH_GREEN+=("$DB_NAME DB HC green (Standby)")

        elif [ -n "$PRIMARY_ROLE" ] && [ -z "$RESTRICTED" ]; then

            echo "Database $DB_NAME is in primary role and not restricted."

            DB_HEALTH_GREEN+=("$DB_NAME DB HC green (Primary)")

        elif [ -n "$PRIMARY_ROLE" ] && [ -n "$RESTRICTED" ]; then

            echo "Database $DB_NAME is in primary role but restricted."

            DB_NOT_OK+=("$DB_NAME in restricted mode: NOT_OK")

        else

            echo "Database $DB_NAME is not in the expected mode."

            DB_NOT_OK+=("$DB_NAME not in expected mode: NOT_OK")

        fi

    else

        echo "Failed to check database mode for $DB_NAME."

    fi

}

# Function to check wallet identifiers in /u01/app/oracle/okv/

check_wallet_identifiers() {

    echo "Checking for wallet identifiers in /u01/app/oracle/okv/..."

    # Ensure the directory exists

    if [ -d "/u01/app/oracle/okv" ]; then

        # List files in /u01/app/oracle/okv/ and capture their names

        WALLET_FILES=$(ls /u01/app/oracle/okv/)

        if [ -n "$WALLET_FILES" ]; then

            while IFS= read -r FILE; do

                WALLET_COUNT=$((WALLET_COUNT + 1))

                WALLET_DB_LIST+=("$FILE")

            done <<< "$WALLET_FILES"

        fi

    else

        echo "/u01/app/oracle/okv directory does not exist."

    fi

}

# Function to check for Goldengate executable (ggsci)

check_goldengate() {

    echo "Checking for Goldengate executable using 'locate ggsci'..."

    locate ggsci > /tmp/ggsci_check.log

    if [ -s /tmp/ggsci_check.log ]; then

        echo "Goldengate executable found. Please double-check and perform GG healthcheck manually."

        GOLDENGATE_FOUND="Yes"

    fi

}

# Read oratab and loop through each database

while IFS=: read -r DB_NAME ORACLE_HOME DB_STARTUP_FLAG; do

    # Skip comments and entries with no ORACLE_HOME

    if [[ "$DB_NAME" =~ ^# ]] || [ -z "$ORACLE_HOME" ] || [[ "$DB_NAME" =~ ^\+ASM ]] || [[ "$DB_NAME" == "oracon_1" ]]; then

        continue

    fi

    # Set the Oracle environment for each database

    set_oracle_env "$DB_NAME" "$ORACLE_HOME"

    # Run checks for each database

    check_db_status "$DB_NAME"

    

    # Only perform Data Guard checks if the database is up

    if [ $? -eq 0 ]; then

        check_dgmgrl_lag "$DB_NAME"

    fi

    

    # Always perform the mode check

    check_db_mode "$DB_NAME"

    echo "-------------------------------------------"

done < $ORATAB

# Run wallet identifier check

check_wallet_identifiers

# Run Goldengate check

check_goldengate

# Print summary of wallet identifiers

echo "================== SUMMARY =================="

# Check wallet count and output result

if [ "$WALLET_COUNT" -gt 0 ]; then

    echo "Action required: $WALLET_COUNT databases found with wallet configured."

    echo "Databases with wallets:"

    for wallet_db in "${WALLET_DB_LIST[@]}"; do

        echo "$wallet_db"

    done

else

    echo "No wallet-configured databases found."

fi

# Check for Goldengate and report in summary

if [ "$GOLDENGATE_FOUND" == "Yes" ]; then

    echo "Goldengate executable found. Please double-check and perform GG healthcheck manually."

fi

# Print databases that have any issues (NOT_OK)

echo "================== ATTENTION =================="

if [ ${#DB_NOT_OK[@]} -eq 0 ]; then

    echo "No databases have issues."

else

    echo "Databases with issues:"

    for db in "${DB_NOT_OK[@]}"; do

        echo "$db"

    done

fi

# Print databases that passed all checks (excluding those marked as NOT_OK)

echo "================== HEALTH CHECK =================="

# Check databases in HEALTH_CHECK array

if [ ${#DB_HEALTH_GREEN[@]} -eq 0 ]; then

    echo "No databases passed the health check."

else

    for db in "${DB_HEALTH_GREEN[@]}"; do

        echo "$db"

    done

fi

echo "============================================="