Saturday, April 12, 2025
#!/bin/bash
# Usage: ./primary_standby_param_compare.sh
# Input file format (single line):
# primary_host primary_db
# Check input file
if [ $# -ne 1 ]; then
echo "Usage: $0 "
exit 1
fi
INPUT_FILE="$1"
PRIMARY_HOST=$(awk '{print $1}' "$INPUT_FILE")
PRIMARY_DB=$(awk '{print $2}' "$INPUT_FILE")
REPORT_FILE="param_diff_${PRIMARY_DB}_$(date +%Y%m%d_%H%M).log"
# Validate input
if [[ -z "$PRIMARY_HOST" || -z "$PRIMARY_DB" ]]; then
echo "Invalid input file format. Expected:"
echo "primary_host primary_db"
exit 1
fi
# Get SYS password
read -s -p "Enter SYS password for ${PRIMARY_HOST}/${PRIMARY_DB}: " SYS_PASSWORD
echo
# Temporary files
PRIMARY_TMP=$(mktemp)
STANDBY_TMP=$(mktemp)
# Function to get all parameters (including hidden)
get_all_parameters() {
local host="$1"
local db="$2"
local out_file="$3"
sqlplus -S "sys/${SYS_PASSWORD}@//${host}:1521/${db} as sysdba" << EOF > "$out_file" 2>&1
SET FEEDBACK OFF HEADING OFF PAGESIZE 0 LINESIZE 1000
SELECT pi.ksppinm || '=' || cv.ksppstvl
FROM x\$ksppi pi
JOIN x\$ksppcv cv ON pi.indx = cv.indx
UNION
SELECT name || '=' || value
FROM v\$parameter
ORDER BY 1;
EXIT
EOF
if grep -q "ORA-" "$out_file"; then
echo "Error connecting to ${host}/${db}"
return 1
fi
}
# Verify primary and get standbys
echo -n "Checking primary database status... "
ROLE=$(sqlplus -S "sys/${SYS_PASSWORD}@//${PRIMARY_HOST}:1521/${PRIMARY_DB} as sysdba" << EOF
SET FEEDBACK OFF HEADING OFF PAGESIZE 0
SELECT DATABASE_ROLE FROM v\$database;
EXIT;
EOF
)
if [[ "$ROLE" != "PRIMARY" ]]; then
echo -e "\nError: ${PRIMARY_HOST}/${PRIMARY_DB} is not a primary database (Role: ${ROLE})"
exit 1
fi
echo "OK (Primary confirmed)"
# Get standby databases
echo -n "Discovering standby databases... "
STANDBY_LIST=$(sqlplus -S "sys/${SYS_PASSWORD}@//${PRIMARY_HOST}:1521/${PRIMARY_DB} as sysdba" << EOF
SET FEEDBACK OFF HEADING OFF PAGESIZE 0
SELECT DISTINCT UPPER(destination)
FROM v\$archive_dest
WHERE target='STANDBY' AND status='VALID' AND destination IS NOT NULL;
EXIT;
EOF
)
if [ -z "$STANDBY_LIST" ]; then
echo -e "\nNo standby databases found"
exit 0
fi
echo "Found: $(echo "$STANDBY_LIST" | wc -l)"
# Get primary parameters
echo -e "\nCollecting parameters from primary (${PRIMARY_DB})..."
get_all_parameters "$PRIMARY_HOST" "$PRIMARY_DB" "$PRIMARY_TMP"
# Compare with each standby
for STANDBY in $STANDBY_LIST; do
echo -e "\nProcessing standby: ${STANDBY}"
# Get standby parameters
get_all_parameters "$PRIMARY_HOST" "$STANDBY" "$STANDBY_TMP"
# Compare and save results
echo -e "\n=== Differences between ${PRIMARY_DB} (Primary) and ${STANDBY} (Standby) ===" | tee -a "$REPORT_FILE"
diff --side-by-side --suppress-common-lines "$PRIMARY_TMP" "$STANDBY_TMP" | tee -a "$REPORT_FILE"
# Cleanup standby temp file
rm -f "$STANDBY_TMP"
done
# Final cleanup and report
rm -f "$PRIMARY_TMP"
echo -e "\nComparison complete. Report saved to: ${REPORT_FILE}"
