Post check

#!/bin/bash


# Set Oracle environment

export ORACLE_SID=<your_cdb_sid>

export ORAENV_ASK=NO

. oraenv > /dev/null


# Log file setup

LOG_DIR=/tmp/post_migration_check

mkdir -p $LOG_DIR

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


# SQL execution wrapper

exec_sql() {

sqlplus -s "/ as sysdba" <<EOF

set pagesize 500

set linesize 200

set feedback off

set echo off

$1

exit

EOF

}


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


# 1. CDB & PDB open modes

echo -e "\n--- 1. CDB & PDB Open Modes ---" | tee -a $LOGFILE

exec_sql "SELECT name, open_mode, restricted FROM v\$pdbs;" | tee -a $LOGFILE


# 2. PDB violations

echo -e "\n--- 2. PDB Plug-in Violations ---" | tee -a $LOGFILE

exec_sql "SELECT * FROM pdb_plug_in_violations WHERE status='PENDING';" | tee -a $LOGFILE


# 3. Listener parameters

echo -e "\n--- 3. Listener Parameter ---" | tee -a $LOGFILE

exec_sql "show parameter listener;" | tee -a $LOGFILE


# 4. SGA info

echo -e "\n--- 4. SGA Parameters ---" | tee -a $LOGFILE

exec_sql "show parameter sga;" | tee -a $LOGFILE


# 5. PGA info

echo -e "\n--- 5. PGA Parameters ---" | tee -a $LOGFILE

exec_sql "show parameter pga;" | tee -a $LOGFILE


# 6. INVALID objects

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

exec_sql "SELECT owner, object_type, object_name FROM dba_objects WHERE status='INVALID';" | tee -a $LOGFILE


# 7. DB components

echo -e "\n--- 7. Component Status ---" | tee -a $LOGFILE

exec_sql "SELECT comp_name, version, status FROM dba_registry;" | tee -a $LOGFILE


# 8. TDE wallet status

echo -e "\n--- 8. TDE Wallet Status (gv\$) ---" | tee -a $LOGFILE

exec_sql "SELECT inst_id, wallet_type, wallet_status FROM gv\$encryption_wallet;" | tee -a $LOGFILE


# 9. Encryption keys in PDB

echo -e "\n--- 9. Encryption Keys per PDB ---" | tee -a $LOGFILE

exec_sql "SELECT key_id, activating_pdbname FROM v\$encryption_keys;" | tee -a $LOGFILE


# 10. SSL connections

echo -e "\n--- 10. SSL Connections ---" | tee -a $LOGFILE

exec_sql "SELECT sid, serial#, network_service_banner FROM v\$session_connect_info WHERE network_service_banner LIKE '%SSL%';" | tee -a $LOGFILE


# 11. Encrypted tablespaces

echo -e "\n--- 11. Encrypted Tablespaces ---" | tee -a $LOGFILE

exec_sql "SELECT tablespace_name, encrypted FROM dba_tablespaces WHERE contents='PERMANENT';" | tee -a $LOGFILE


# 12. RMAN controlfile autobackup

echo -e "\n--- 12. RMAN Controlfile Autobackup ---" | tee -a $LOGFILE

exec_sql "SHOW ALL;" | tee -a $LOGFILE | grep -i controlfile


# 13. Autosys job check (manual or CLI)

echo -e "\n--- 13. Autosys Job Check ---" | tee -a $LOGFILE

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

  autorep -J % -q | tee -a $LOGFILE

else

  echo "Autosys not found or not in PATH. Please verify manually." | tee -a $LOGFILE

fi


# 14. OEM monitoring (basic alert example)

echo -e "\n--- 14. OEM Monitoring - Outstanding Alerts ---" | tee -a $LOGFILE

exec_sql "SELECT object_type, reason, message_level FROM dba_outstanding_alerts;" | tee -a $LOGFILE


# 15. SNOW entry check (custom table, update if needed)

echo -e "\n--- 15. SNOW Entries in PDB (Assumed custom table) ---" | tee -a $LOGFILE

exec_sql "SELECT * FROM pdb_snow_entries;" 2>/dev/null | tee -a $LOGFILE


# 16. Cloakware user check

echo -e "\n--- 16. Cloakware Users Present ---" | tee -a $LOGFILE

exec_sql "SELECT username FROM dba_users WHERE username IN ('OPS_USER', 'APP_USER', 'SERVICE_ACCT');" | tee -a $LOGFILE


echo -e "\n=== All Checks Completed Successfully. Log: $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