Check2

 #!/bin/bash


export ORACLE_SID=<your_cdb_sid>

export ORAENV_ASK=NO

. oraenv > /dev/null 2>&1


LOG_DIR=/tmp/post_migration_check

mkdir -p "$LOG_DIR"

LOGFILE="$LOG_DIR/post_migration_check_$(date +%Y%m%d_%H%M%S).log"


exec_sql() {

sqlplus -s "/ as sysdba" <<EOF

set pages 100 lines 200 feedback off heading on echo off

col name for a20

col open_mode for a20

col restricted for a15

col object_type for a20

col object_name for a40

col owner for a20

col comp_name for a35

col status for a15

col wallet_status for a20

col tablespace_name for a30

col encrypted for a10

col activating_pdbname for a25

$1

exit

EOF

}


check_flag() {

  if grep -iq "$2" <<< "$1"; then

    echo "$3 : PASSED" | tee -a $LOGFILE

  else

    echo "$3 : NOT PASSED" | tee -a $LOGFILE

  fi

}


echo "=== Post Migration Certification Checks ===" | tee -a $LOGFILE


# ASM SID detection

ASM_SID=$(ps -ef | grep pmon | grep ASM | grep -v grep | awk -F_ '{print $3}' | head -1)

if [ -z "$ASM_SID" ]; then

  echo "No ASM instance found on host." | tee -a $LOGFILE

  exit 1

fi

export ORACLE_SID=$ASM_SID

. oraenv > /dev/null 2>&1


# Listener/VIP Info

echo -e "\n--- Network Configuration ---" | tee -a $LOGFILE

NODE_VIP=$(srvctl config nodeapps -n "$(hostname)" | grep -i "VIP IPv4 Address" | awk -F':' '{print $2}' | xargs)

SCAN_VIP=$(srvctl config scan | grep "SCAN name:" | awk '{print $NF}' | xargs)

LISTENER_ENDPOINTS=$(srvctl config scan_listener | grep -i "Endpoints:" | awk -F':' '{print $2}' | xargs)

TCP_PORT=$(echo "$LISTENER_ENDPOINTS" | awk -F',' '{print $1}' | awk -F'/' '{print $2}' | awk -F':' '{print $2}')

TCPS_PORT=$(echo "$LISTENER_ENDPOINTS" | awk -F',' '{print $2}' | awk -F'/' '{print $2}' | awk -F':' '{print $2}')

echo "Node VIP : $NODE_VIP" | tee -a $LOGFILE

echo "SCAN VIP : $SCAN_VIP" | tee -a $LOGFILE

echo "TCP Listener Port: $TCP_PORT" | tee -a $LOGFILE

echo "TCPS Listener Port: $TCPS_PORT" | tee -a $LOGFILE


# SGA Classification

echo -e "\n--- SGA Classification ---" | tee -a $LOGFILE

sga_target_gb=$(exec_sql "SELECT ROUND(value/1024/1024/1024) FROM v\\$parameter WHERE name='sga_target';" | grep -Eo '[0-9]+')

if [ "$sga_target_gb" -eq 8 ]; then echo "SGA: 8 GB => SMALL : PASSED" | tee -a $LOGFILE

elif [ "$sga_target_gb" -eq 12 ]; then echo "SGA: 12 GB => MEDIUM : PASSED" | tee -a $LOGFILE

elif [ "$sga_target_gb" -eq 24 ]; then echo "SGA: 24 GB => LARGE : PASSED" | tee -a $LOGFILE

else echo "SGA: $sga_target_gb GB => NON-STANDARD : NOT PASSED" | tee -a $LOGFILE

fi


# PGA Limit

echo -e "\n--- PGA_AGGREGATE_LIMIT Check ---" | tee -a $LOGFILE

pga_limit=$(exec_sql "show parameter pga_aggregate_limit;" | awk '/pga_aggregate_limit/ {print $3}')

if [[ -z "$pga_limit" || "$pga_limit" == "0" ]]; then

  echo "PGA_AGGREGATE_LIMIT not set => NOT PASSED" | tee -a $LOGFILE

else

  echo "PGA_AGGREGATE_LIMIT = $pga_limit => PASSED" | tee -a $LOGFILE

fi


# Step 1: PDB Open

pdb_open_status=$(exec_sql "SELECT name, open_mode, restricted FROM v\$pdbs;")

echo "$pdb_open_status" | tee -a $LOGFILE

check_flag "$pdb_open_status" "READ WRITE" "Step 1: CDB/PDB Open Status"


# Step 2: Plugin Violations

plugin_violations=$(exec_sql "SELECT name, message_type, status FROM pdb_plug_in_violations WHERE status='PENDING';")

echo "$plugin_violations" | tee -a $LOGFILE

check_flag "$plugin_violations" "no rows selected" "Step 2: Plugin Violations"


# Step 3: Listener Param

listener_param=$(exec_sql "show parameter listener;")

echo "$listener_param" | tee -a $LOGFILE

check_flag "$listener_param" "listener" "Step 3: Listener Parameter"


# Step 4–5: SGA/PGA already checked above


# Step 6: INVALID Objects

echo -e "\n--- Step 6: INVALID Objects ---" | tee -a $LOGFILE

invalid_objects_found=0

pdb_list=$(exec_sql "set heading off; SELECT name FROM v\$pdbs WHERE open_mode='READ WRITE';")

for pdb in $pdb_list; do

  result=$(sqlplus -s "/ as sysdba" <<EOF

ALTER SESSION SET CONTAINER=$pdb;

set pages 100 lines 200

SELECT owner, object_type, object_name FROM dba_objects WHERE status='INVALID';

exit

EOF

)

  echo "$result" | tee -a $LOGFILE

  if echo "$result" | grep -iq "no rows selected"; then

    echo "$pdb: INVALID Objects : PASSED" | tee -a $LOGFILE

  else

    echo "$pdb: INVALID Objects : NOT PASSED" | tee -a $LOGFILE

    invalid_objects_found=1

  fi

done


# Step 7: Component Status

component_status=$(exec_sql "SELECT comp_name, version, status FROM dba_registry;")

echo "$component_status" | tee -a $LOGFILE

check_flag "$component_status" "VALID" "Step 7: Component Status"


# Step 8: TDE Wallet

tde_wallet_status=$(exec_sql "SELECT inst_id, wallet_type, wallet_status FROM gv\$encryption_wallet;")

echo "$tde_wallet_status" | tee -a $LOGFILE

check_flag "$tde_wallet_status" "OPEN" "Step 8: TDE Wallet Status"


# Step 9: TDE Key Registration

tde_keys_registered=$(exec_sql "SELECT key_id, activating_pdbname FROM v\$encryption_keys;")

echo "$tde_keys_registered" | tee -a $LOGFILE

check_flag "$tde_keys_registered" "KEY_ID" "Step 9: TDE Keys Registered"


# Step 10: SSL Connection

ssl_connection_status=$(exec_sql "SELECT sid, serial#, network_service_banner FROM v\$session_connect_info WHERE network_service_banner LIKE '%SSL%';")

echo "$ssl_connection_status" | tee -a $LOGFILE

check_flag "$ssl_connection_status" "SSL" "Step 10: SSL Connection"


# Step 11: Encrypted TBS

encrypted_tablespaces=$(exec_sql "SELECT tablespace_name, encrypted FROM dba_tablespaces WHERE contents='PERMANENT';")

echo "$encrypted_tablespaces" | tee -a $LOGFILE

check_flag "$encrypted_tablespaces" "YES" "Step 11: Encrypted Tablespaces"


# Step 12: RMAN Controlfile Autobackup

rman_config=$(rman target / <<EOF

show all;

exit

EOF

)

echo "$rman_config" | tee -a $LOGFILE

check_flag "$rman_config" "CONTROLFILE AUTOBACKUP ON" "Step 12: RMAN Controlfile Autobackup"


# Step 13: Autosys Jobs

if command -v autorep >/dev/null; then

  autosys_output=$(autorep -J % -q)

  echo "$autosys_output" | tee -a $LOGFILE

  check_flag "$autosys_output" "job" "Step 13: Autosys Jobs Found"

else

  echo "Autosys not available. Check manually." | tee -a $LOGFILE

fi


# Step 14: OEM Alerts

oem_alerts=$(exec_sql "SELECT object_type, reason FROM dba_outstanding_alerts;")

echo "$oem_alerts" | tee -a $LOGFILE

check_flag "$oem_alerts" "no rows selected" "Step 14: OEM Alerts"


# Step 15: SNOW Entries (custom)

snow_entry_output=$(exec_sql "SELECT * FROM pdb_snow_entries;")

echo "$snow_entry_output" | tee -a $LOGFILE

check_flag "$snow_entry_output" "INC" "Step 15: SNOW Entry Check"


# Step 16: Cloakware Users

cloakware_user_check=$(exec_sql "SELECT username FROM dba_users WHERE username IN ('OPS_USER','APP_USER','SERVICE_ACCT');")

echo "$cloakware_user_check" | tee -a $LOGFILE

check_flag "$cloakware_user_check" "OPS_USER" "Step 16: Cloakware Users"


# AWR Autoflush Check

echo -e "\n--- AWR PDB Auto Flush Check ---" | tee -a $LOGFILE

awr_flush=$(exec_sql "SELECT value FROM v\$parameter WHERE name='awr_pdb_autoflush_enabled';" | grep -i true)

check_flag "$awr_flush" "true" "AWR PDB Autoflush Enabled"


echo -e "\n=== ✅ Post-Migration Checks Completed. Log saved at: $LOGFILE ==="

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

Asm

#!/bin/bash # Set environment variables ORACLE_SID=YOUR_SID ORACLE_HOME=/path/to/oracle_home export ORACLE_SID ORACLE_HOME LOG_DIR=/path/to/logs SQL_DIR=/path/to/sql mkdir -p "$LOG_DIR" mkdir -p "$SQL_DIR" DB_USER="/ as sysdba" # Function to generate SQL data for each target type generate_sql() {   local TARGET_TYPE=$1   local SQL_FILE="$SQL_DIR/${TARGET_TYPE}_data.sql"   local OUTPUT_FILE="/tmp/${TARGET_TYPE}_data.txt"   case "$TARGET_TYPE" in     cluster)       echo "SET HEADING OFF; SET FEEDBACK OFF; SET PAGESIZE 0;" > "$SQL_FILE"       echo "SELECT TARGET_NAME || '|' || HOST_NAME || '|' || CREDENTIALS || '|' || CLUSTER_NAME || '|' || VIP_ADDRESS FROM CLUSTER_TARGETS;" >> "$SQL_FILE"       ;;     # Additional target types can be added here     *)       echo "[ERROR] Unknown target type: $TARGET_TYPE"       return 1       ;;   esac ...
Read more