Wallet final

#!/bin/bash

# -------------------------------------------------------------------------------------

# Script Name: wallet_validation.sh

# Purpose: Validate Oracle TDE wallet configuration for a given DB instance.

# - Checks if the Oracle SID is listed in oratab.

# - Checks if the DB is running (via SMON process).

# - Retrieves DB version and wallet information.

# - Backs up the wallet if the wallet path contains the DB name.

# - Validates the wallet password (tries cloakware first, then standard).

# - Generates a final report in a single padded line.

#

# Usage: ./wallet_validation.sh <ORACLE_SID> <DEFAULT_LOCATION> <LOG_LOCATION>

#

# Manager Highlights:

# - Completed: Instance verification from /etc/oratab.

# - Completed: DB running check using SMON process (ps -ef).

# - Completed: Retrieval of DB version and wallet details via SQLPlus.

# - Completed: Wallet backup if DB name is present in the wallet path.

# - Completed: Wallet password validation using exit status of mkstore.

# - Enhancement Today: Final report generation in a padded single-line format.

#

# -------------------------------------------------------------------------------------


# --- Phase 1: Validate Input Parameters ---

if [[ $# -ne 3 ]]; then

    echo "Usage: $0 <ORACLE_SID> <DEFAULT_LOCATION> <LOG_LOCATION>"

    exit 1

fi


# Accept input parameters and export them as needed

export ORACLE_SID=$1

export DEFAULT_LOCATION=$2

export LOG_LOCATION=$3


# --- Phase 2: Set Environment Variables ---

export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

SQLPLUS="$ORACLE_HOME/bin/sqlplus -s / as sysdba"


# Standard password for fallback validation

STANDARD_PWD="xyxyxyxyxytsts"


# --- Phase 3: Retrieve Cloakware Password ---

# Using an external tool (pwEcho.exe) to fetch the cloakware password.

NEWPSWD=$(/path/to/pwEcho.exe "$ORACLE_SID" WALLET)

[[ -z "$NEWPSWD" ]] && NEWPSWD="NA"


# --- Phase 4: Create Necessary Directories for Logs and Reports ---

RUN_DATE=$(date '+%Y%m%d')

mkdir -p "$DEFAULT_LOCATION/$RUN_DATE/$ORACLE_SID"

mkdir -p "$LOG_LOCATION/encryption/wallet_backup/logs"

LOG_FILE="$LOG_LOCATION/encryption/wallet_backup/logs/wallet_validate.log"

REPORT_FILE="$DEFAULT_LOCATION/$RUN_DATE/$ORACLE_SID/wallet_report.log"


# --- Phase 5: Verify DB Instance in /etc/oratab ---

if grep "^$ORACLE_SID:" /etc/oratab >/dev/null 2>&1; then

    echo "Instance $ORACLE_SID is found in /etc/oratab." | tee -a "$LOG_FILE"

else

    echo "ERROR: DB instance '$ORACLE_SID' is not found in /etc/oratab." | tee -a "$LOG_FILE"

    cat <<EOF > "$REPORT_FILE"

DB Name: $ORACLE_SID

DB Running: NOT FOUND in oratab

Status: NOT FOUND

Run Date: $(date '+%Y-%m-%d %H:%M:%S')

EOF

    exit 1

fi


# --- Phase 6: Check if the DB is Running using SMON Process ---

if ps -ef | grep -i "ora_smon_${ORACLE_SID}" | grep -v grep >/dev/null 2>&1; then

    DB_RUNNING="YES"

else

    DB_RUNNING="NO"

fi


if [[ "$DB_RUNNING" == "NO" ]]; then

    echo "ERROR: DB instance '$ORACLE_SID' is not running." | tee -a "$LOG_FILE"

    cat <<EOF > "$REPORT_FILE"

DB Name: $ORACLE_SID

DB Running: NO

Status: NOT RUNNING

Run Date: $(date '+%Y-%m-%d %H:%M:%S')

EOF

    exit 1

fi


# --- Phase 7: Retrieve DB Version (via SQLPlus) ---

get_db_version() {

    $SQLPLUS <<EOF

SET HEAD OFF FEEDBACK OFF PAGESIZE 0 LINESIZE 100

SELECT version FROM v\$instance;

EXIT;

EOF

}

DB_VERSION=$(get_db_version | tr -d '[:space:]')


# --- Phase 8: Retrieve Wallet Information ---

# Different query for 19c (which includes WALLET_TYPE) and for older versions.

get_db_info() {

    if [[ "$DB_VERSION" =~ 19 ]]; then

        QUERY="SELECT name || '|' || status || '|' || \

NVL((SELECT WRL_PARAMETER FROM gv\$encryption_wallet WHERE inst_id = (SELECT INSTANCE_NUMBER FROM v\$instance)), 'NA') || '|' || \

(SELECT CASE WHEN COUNT(*) > 0 THEN 'YES' ELSE 'NO' END FROM dba_tablespaces WHERE ENCRYPTED='YES') || '|' || \

WALLET_TYPE || '|' || '$DB_VERSION' FROM v\$database, v\$encryption_wallet;"

    else

        QUERY="SELECT name || '|' || status || '|' || \

NVL((SELECT WRL_PARAMETER FROM gv\$encryption_wallet WHERE inst_id = (SELECT INSTANCE_NUMBER FROM v\$instance)), 'NA') || '|' || \

(SELECT CASE WHEN COUNT(*) > 0 THEN 'YES' ELSE 'NO' END FROM dba_tablespaces WHERE ENCRYPTED='YES') || '|' || \

'NA' || '|' || '$DB_VERSION' FROM v\$database, v\$encryption_wallet;"

    fi

    $SQLPLUS <<EOF

SET HEAD OFF FEEDBACK OFF PAGESIZE 0 LINESIZE 200

$QUERY

EXIT;

EOF

}


DB_INFO=$(get_db_info)

IFS='|' read -r DBNAME WALLET_STATUS WALLET_LOCATION TBS_ENCR WALLET_TYPE DB_VERSION <<< "$DB_INFO"

[[ -z "$WALLET_TYPE" ]] && WALLET_TYPE="NA"


# --- Phase 9: Check for Wallet Directory Existence ---

if [[ "$WALLET_LOCATION" != "NA" && -d "$WALLET_LOCATION" ]]; then

    WRL_PRESENT="Y"

else

    WRL_PRESENT="N"

fi


# --- Phase 10: Wallet Backup (Independent of Password Validation) ---

if [[ "$WRL_PRESENT" == "Y" ]]; then

    echo "Wallet found at: $WALLET_LOCATION" | tee -a "$LOG_FILE"

    if [[ "$WALLET_LOCATION" == *"$DBNAME"* ]]; then

        echo "Database name found in wallet path, initiating backup..." | tee -a "$LOG_FILE"

        BACKUP_LOCATION="$DEFAULT_LOCATION/$RUN_DATE/$DBNAME/wallet_backup"

        mkdir -p "$BACKUP_LOCATION"

        cp -r "$WALLET_LOCATION" "$BACKUP_LOCATION"

        WRL_BKUP="Y"

    else

        WRL_BKUP="N"

    fi

fi


# --- Phase 11: Wallet Password Validation ---

PWD_MATCH="N"

CLK_PWD="N"

if [[ "$WRL_PRESENT" == "Y" ]]; then

    echo "Starting Wallet Password Validation..." | tee -a "$LOG_FILE"

    # Try Cloakware Password First

    echo "$NEWPSWD" | $ORACLE_HOME/bin/mkstore -wrl "$WALLET_LOCATION" -list >/dev/null 2>&1

    if [[ $? -eq 0 ]]; then

        PWD_MATCH="Y"

        CLK_PWD="Y"

        echo "✅ Cloakware password is valid." | tee -a "$LOG_FILE"

    else

        # Try Standard Password

        echo "$STANDARD_PWD" | $ORACLE_HOME/bin/mkstore -wrl "$WALLET_LOCATION" -list >/dev/null 2>&1

        if [[ $? -eq 0 ]]; then

            PWD_MATCH="Y"

            CLK_PWD="N"

            echo "✅ Standard password is valid." | tee -a "$LOG_FILE"

        else

            PWD_MATCH="N"

            CLK_PWD="N"

            echo "❌ Wallet password validation failed." | tee -a "$LOG_FILE"

        fi

    fi

fi


# --- Phase 12: Generate Final Report (Padded Single-Line Format) ---

RUN_DT=$(date '+%Y-%m-%d %H:%M:%S')

# Report columns: DB_NAME, DB_VERSION, DB_RUNNING, WALLET_STATUS, WALLET_TYPE, WRL_PRESENT, TBS_ENCR,

# WRL_BKUP, CLK_PWD, PWD_MATCH, RUN_DT, WALLET_LOCATION.

printf "%-8s | %-10s | %-6s | %-12s | %-10s | %-4s | %-4s | %-6s | %-4s | %-10s | %-20s | %-50s\n" \

    "$DBNAME" "$DB_VERSION" "$DB_RUNNING" "$WALLET_STATUS" "$WALLET_TYPE" "$WRL_PRESENT" "$TBS_ENCR" "$WRL_BKUP" "$CLK_PWD" "$PWD_MATCH" "$RUN_DT" "$WALLET_LOCATION" \

    >> "$REPORT_FILE"


echo "Report saved at $REPORT_FILE" | tee -a "$LOG_FILE"



****

Below is the final version of the script with detailed inline comments explaining each activity phase. You can share this with your manager to highlight the completed work and today's enhancements.



---


#!/bin/bash

# -------------------------------------------------------------------------------------

# Script Name: wallet_validation.sh

# Purpose: Validate Oracle TDE wallet configuration for a given DB instance.

# - Checks if the Oracle SID is listed in oratab.

# - Checks if the DB is running (via SMON process).

# - Retrieves DB version and wallet information.

# - Backs up the wallet if the wallet path contains the DB name.

# - Validates the wallet password (tries cloakware first, then standard).

# - Generates a final report in a single padded line.

#

# Usage: ./wallet_validation.sh <ORACLE_SID> <DEFAULT_LOCATION> <LOG_LOCATION>

#

# Manager Highlights:

# - Completed: Instance verification from /etc/oratab.

# - Completed: DB running check using SMON process (ps -ef).

# - Completed: Retrieval of DB version and wallet details via SQLPlus.

# - Completed: Wallet backup if DB name is present in the wallet path.

# - Completed: Wallet password validation using exit status of mkstore.

# - Enhancement Today: Final report generation in a padded single-line format.

#

# -------------------------------------------------------------------------------------


# --- Phase 1: Validate Input Parameters ---

if [[ $# -ne 3 ]]; then

    echo "Usage: $0 <ORACLE_SID> <DEFAULT_LOCATION> <LOG_LOCATION>"

    exit 1

fi


# Accept input parameters and export them as needed

export ORACLE_SID=$1

export DEFAULT_LOCATION=$2

export LOG_LOCATION=$3


# --- Phase 2: Set Environment Variables ---

export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

SQLPLUS="$ORACLE_HOME/bin/sqlplus -s / as sysdba"


# Standard password for fallback validation

STANDARD_PWD="xyxyxyxyxytsts"


# --- Phase 3: Retrieve Cloakware Password ---

# Using an external tool (pwEcho.exe) to fetch the cloakware password.

NEWPSWD=$(/path/to/pwEcho.exe "$ORACLE_SID" WALLET)

[[ -z "$NEWPSWD" ]] && NEWPSWD="NA"


# --- Phase 4: Create Necessary Directories for Logs and Reports ---

RUN_DATE=$(date '+%Y%m%d')

mkdir -p "$DEFAULT_LOCATION/$RUN_DATE/$ORACLE_SID"

mkdir -p "$LOG_LOCATION/encryption/wallet_backup/logs"

LOG_FILE="$LOG_LOCATION/encryption/wallet_backup/logs/wallet_validate.log"

REPORT_FILE="$DEFAULT_LOCATION/$RUN_DATE/$ORACLE_SID/wallet_report.log"


# --- Phase 5: Verify DB Instance in /etc/oratab ---

if grep "^$ORACLE_SID:" /etc/oratab >/dev/null 2>&1; then

    echo "Instance $ORACLE_SID is found in /etc/oratab." | tee -a "$LOG_FILE"

else

    echo "ERROR: DB instance '$ORACLE_SID' is not found in /etc/oratab." | tee -a "$LOG_FILE"

    cat <<EOF > "$REPORT_FILE"

DB Name: $ORACLE_SID

DB Running: NOT FOUND in oratab

Status: NOT FOUND

Run Date: $(date '+%Y-%m-%d %H:%M:%S')

EOF

    exit 1

fi


# --- Phase 6: Check if the DB is Running using SMON Process ---

if ps -ef | grep -i "ora_smon_${ORACLE_SID}" | grep -v grep >/dev/null 2>&1; then

    DB_RUNNING="YES"

else

    DB_RUNNING="NO"

fi


if [[ "$DB_RUNNING" == "NO" ]]; then

    echo "ERROR: DB instance '$ORACLE_SID' is not running." | tee -a "$LOG_FILE"

    cat <<EOF > "$REPORT_FILE"

DB Name: $ORACLE_SID

DB Running: NO

Status: NOT RUNNING

Run Date: $(date '+%Y-%m-%d %H:%M:%S')

EOF

    exit 1

fi


# --- Phase 7: Retrieve DB Version (via SQLPlus) ---

get_db_version() {

    $SQLPLUS <<EOF

SET HEAD OFF FEEDBACK OFF PAGESIZE 0 LINESIZE 100

SELECT version FROM v\$instance;

EXIT;

EOF

}

DB_VERSION=$(get_db_version | tr -d '[:space:]')


# --- Phase 8: Retrieve Wallet Information ---

# Different query for 19c (which includes WALLET_TYPE) and for older versions.

get_db_info() {

    if [[ "$DB_VERSION" =~ 19 ]]; then

        QUERY="SELECT name || '|' || status || '|' || \

NVL((SELECT WRL_PARAMETER FROM gv\$encryption_wallet WHERE inst_id = (SELECT INSTANCE_NUMBER FROM v\$instance)), 'NA') || '|' || \

(SELECT CASE WHEN COUNT(*) > 0 THEN 'YES' ELSE 'NO' END FROM dba_tablespaces WHERE ENCRYPTED='YES') || '|' || \

WALLET_TYPE || '|' || '$DB_VERSION' FROM v\$database, v\$encryption_wallet;"

    else

        QUERY="SELECT name || '|' || status || '|' || \

NVL((SELECT WRL_PARAMETER FROM gv\$encryption_wallet WHERE inst_id = (SELECT INSTANCE_NUMBER FROM v\$instance)), 'NA') || '|' || \

(SELECT CASE WHEN COUNT(*) > 0 THEN 'YES' ELSE 'NO' END FROM dba_tablespaces WHERE ENCRYPTED='YES') || '|' || \

'NA' || '|' || '$DB_VERSION' FROM v\$database, v\$encryption_wallet;"

    fi

    $SQLPLUS <<EOF

SET HEAD OFF FEEDBACK OFF PAGESIZE 0 LINESIZE 200

$QUERY

EXIT;

EOF

}


DB_INFO=$(get_db_info)

IFS='|' read -r DBNAME WALLET_STATUS WALLET_LOCATION TBS_ENCR WALLET_TYPE DB_VERSION <<< "$DB_INFO"

[[ -z "$WALLET_TYPE" ]] && WALLET_TYPE="NA"


# --- Phase 9: Check for Wallet Directory Existence ---

if [[ "$WALLET_LOCATION" != "NA" && -d "$WALLET_LOCATION" ]]; then

    WRL_PRESENT="Y"

else

    WRL_PRESENT="N"

fi


# --- Phase 10: Wallet Backup (Independent of Password Validation) ---

if [[ "$WRL_PRESENT" == "Y" ]]; then

    echo "Wallet found at: $WALLET_LOCATION" | tee -a "$LOG_FILE"

    if [[ "$WALLET_LOCATION" == *"$DBNAME"* ]]; then

        echo "Database name found in wallet path, initiating backup..." | tee -a "$LOG_FILE"

        BACKUP_LOCATION="$DEFAULT_LOCATION/$RUN_DATE/$DBNAME/wallet_backup"

        mkdir -p "$BACKUP_LOCATION"

        cp -r "$WALLET_LOCATION" "$BACKUP_LOCATION"

        WRL_BKUP="Y"

    else

        WRL_BKUP="N"

    fi

fi


# --- Phase 11: Wallet Password Validation ---

PWD_MATCH="N"

CLK_PWD="N"

if [[ "$WRL_PRESENT" == "Y" ]]; then

    echo "Starting Wallet Password Validation..." | tee -a "$LOG_FILE"

    # Try Cloakware Password First

    echo "$NEWPSWD" | $ORACLE_HOME/bin/mkstore -wrl "$WALLET_LOCATION" -list >/dev/null 2>&1

    if [[ $? -eq 0 ]]; then

        PWD_MATCH="Y"

        CLK_PWD="Y"

        echo "✅ Cloakware password is valid." | tee -a "$LOG_FILE"

    else

        # Try Standard Password

        echo "$STANDARD_PWD" | $ORACLE_HOME/bin/mkstore -wrl "$WALLET_LOCATION" -list >/dev/null 2>&1

        if [[ $? -eq 0 ]]; then

            PWD_MATCH="Y"

            CLK_PWD="N"

            echo "✅ Standard password is valid." | tee -a "$LOG_FILE"

        else

            PWD_MATCH="N"

            CLK_PWD="N"

            echo "❌ Wallet password validation failed." | tee -a "$LOG_FILE"

        fi

    fi

fi


# --- Phase 12: Generate Final Report (Padded Single-Line Format) ---

RUN_DT=$(date '+%Y-%m-%d %H:%M:%S')

# Report columns: DB_NAME, DB_VERSION, DB_RUNNING, WALLET_STATUS, WALLET_TYPE, WRL_PRESENT, TBS_ENCR,

# WRL_BKUP, CLK_PWD, PWD_MATCH, RUN_DT, WALLET_LOCATION.

printf "%-8s | %-10s | %-6s | %-12s | %-10s | %-4s | %-4s | %-6s | %-4s | %-10s | %-20s | %-50s\n" \

    "$DBNAME" "$DB_VERSION" "$DB_RUNNING" "$WALLET_STATUS" "$WALLET_TYPE" "$WRL_PRESENT" "$TBS_ENCR" "$WRL_BKUP" "$CLK_PWD" "$PWD_MATCH" "$RUN_DT" "$WALLET_LOCATION" \

    >> "$REPORT_FILE"


echo "Report saved at $REPORT_FILE" | tee -a "$LOG_FILE"



---


Manager Highlights and Activity Phases


Overall Activity Phases:


1. Input Validation & Environment Setup:


Validates parameters.


Sets ORACLE_HOME and PATH.




2. Instance Verification:


Checks for the instance in /etc/oratab using a grep command.




3. DB Running Check:


Uses ps -ef and grep to look for the SMON process.


Sets DB_RUNNING accordingly.


Exits and writes a minimal report if the DB is not running.




4. DB Version and Wallet Info Retrieval:


Retrieves the DB version using SQLPlus.


Fetches wallet details (with different queries for 19c vs. older versions).




5. Wallet Backup:


Checks if the wallet directory exists and if it contains the DB name.


Backs up the wallet files irrespective of password validation.




6. Wallet Password Validation:


Validates the wallet password by trying the cloakware password first, then the standard password.


Uses the exit status of mkstore commands.




7. Final Report Generation:


Generates a padded single-line report with all key columns (including DB_RUNNING).


Saves the report and logs the activity.





Today's Enhancements:


Implemented a robust check for the DB instance in /etc/oratab.


Added an alternative DB running check using ps -ef to detect the SMON process.


Included the DB running status in the final report.


Updated wallet password validation logic to rely on exit codes.


Formatted the final output into a single padded

 line for clear, aligned reporting.



This complete script and its documented phases should provide a clear overview of the work completed and today's enhancements.


Comments

Post a Comment

Popular posts from this blog

Database growth

#!/bin/bash # Log file LOGFILE="db_growth_check.log" echo "Checking yearly database growth on all running Oracle databases..." > $LOGFILE echo "DATABASE_NAME | YEAR | GROWTH_GB" >> $LOGFILE echo "---------------------------------" >> $LOGFILE # Find running Oracle instances (excluding ASM and APX) ps -ef | grep pmon | grep -v grep | grep -Ev "(\+ASM|APX)" | awk -F'_' '{print $3}' | while read -r ORACLE_SID do     if [[ -n "$ORACLE_SID" ]]; then         echo "Processing database: $ORACLE_SID"         # Set Oracle environment         export ORACLE_SID=$ORACLE_SID         export ORAENV_ASK=NO         . oraenv > /dev/null 2>&1         # Run SQL query to check yearly database growth         SQL_OUTPUT=$(sqlplus -s / as sysdba <<EOF SET HEADING OFF; SET FEEDBACK OFF; SELECT '$ORACLE_SID' AS DATABASE_NAME,  ...
Read more

DBA Day-2 ve

 Operating system  CPU mpstat top  load average  pidstat dstat -vr strace---> debug if system CPU usage % is high but if you dont know then use strace -tp `grep testdb1 2>&1 | head 100 debug if User CPU usage % is high but if you dont know then use 6. jobs (hit enter) 6.1 perf record -F 99 -a -g --sleep 10 (Profiling ) 6.2 perf report -n --stdio 6.3 ./perf-tools/execsnoop Memory vmstat I/O iostat -xmd 1 iotop Network sar -n DEV 1 mtr netstat -antpl | grep 1521 nmcli device status ethtool iperf3 -c 10.1.90.51 https://www.youtube.com/watch?v=eWUeJBAiX80 fs.aio-max-nr--> Async I/O max fs.file-max --> Max file open kernal.shmmin --> how many program segment can access(permission) the shared memory kernal.shmall --> maximum amount of shared memory can accessed by all the process other than OS(set to sum of all SGAs on the server divided by page size – ‘getconf PAGESIZE’) kernal.shmmax --> maximum amount of shared memory can accessed by ind...
Read more

Sql1

#!/bin/bash LOGFILE="/home/oracle/pdb_output.log" : > $LOGFILE # Clear the log file get_pdb_details() {   echo "CDB_UNIQUE_NAME|PDB_NAME|CPU|SGA_MAX_SIZE_GB|SGA_TARGET_GB|PGA_GB|DB_SIZE_GB" > $LOGFILE   # Iterate over each CDB   for ENV_FILE in /home/oracle/*.env; do     source "${ENV_FILE}"     # Validate environment variables     if [[ -z "$ORACLE_SID" || -z "$ORACLE_HOME" ]]; then       continue     fi     # Check if CDB is running     INSTANCE_STATUS=$(timeout 5 sqlplus -s / as sysdba <<EOF SET HEAD OFF FEEDBACK OFF SELECT instance_name FROM v\$instance WHERE status = 'OPEN'; EXIT; EOF )     if [[ -z "$INSTANCE_STATUS" ]]; then       continue     fi     # Fetch CDB unique name     CDB_UNIQUE_NAME=$(sqlplus -s / as sysdba <<EOF SET HEAD OFF FEEDBACK OFF SELECT value FROM v\$parameter WHERE name = 'db_unique_name'; EXIT; EO...
Read more