#!/bin/bash
# Usage: ./compare_db_params_hidden.sh
# Input file format (two lines):
# source_host source_db
# target_host target_db
# Check if input file is provided
if [ $# -ne 1 ]; then
echo "Usage: $0 "
exit 1
fi
INPUT_FILE=$1
# Read source and target details from input file
SRC_HOST=$(sed -n 1p $INPUT_FILE | awk '{print $1}')
SRC_DB=$(sed -n 1p $INPUT_FILE | awk '{print $2}')
TGT_HOST=$(sed -n 2p $INPUT_FILE | awk '{print $1}')
TGT_DB=$(sed -n 2p $INPUT_FILE | awk '{print $2}')
# Validate input file content
if [[ -z "$SRC_HOST" || -z "$SRC_DB" || -z "$TGT_HOST" || -z "$TGT_DB" ]]; then
echo "Invalid input file format. Expected:"
echo "source_host source_db"
echo "target_host target_db"
exit 1
fi
# Temporary files for parameters
SRC_TMP=$(mktemp)
TGT_TMP=$(mktemp)
# Function to fetch ALL parameters (including hidden)
fetch_params() {
local host=$1
local db=$2
local tmp_file=$3
read -s -p "Enter SYS password for $host/$db: " password
echo
echo "Fetching parameters (including hidden) from $host/$db..."
sqlplus -S "sys/${password}@//${host}:1521/${db} as sysdba" << EOF > "$tmp_file" 2>&1
SET HEADING OFF
SET PAGESIZE 0
SET FEEDBACK OFF
SELECT pi.ksppinm || '=' || cv.ksppstvl
FROM x\$ksppi pi
JOIN x\$ksppcv cv ON pi.indx = cv.indx
ORDER BY pi.ksppinm;
EXIT
EOF
# Check for errors
if grep -q "ORA-" "$tmp_file"; then
echo "Failed to connect to $host/$db. Check credentials/networking."
rm -f "$tmp_file"
exit 1
fi
}
# Fetch parameters for source and target
fetch_params "$SRC_HOST" "$SRC_DB" "$SRC_TMP"
fetch_params "$TGT_HOST" "$TGT_DB" "$TGT_TMP"
# Compare parameters (including hidden)
echo "Comparing parameters (including hidden) between $SRC_DB and $TGT_DB..."
diff --side-by-side --suppress-common-lines "$SRC_TMP" "$TGT_TMP"
# Cleanup
rm -f "$SRC_TMP" "$TGT_TMP"
##
#!/bin/bash
# Usage: ./rac_health_scan.sh
# Input file format (single line):
# scan_host db_name
# Check input file
if [ $# -ne 1 ]; then
echo "Usage: $0 "
exit 1
fi
INPUT_FILE="$1"
SCAN_HOST=$(awk '{print $1}' "$INPUT_FILE")
DB_NAME=$(awk '{print $2}' "$INPUT_FILE")
REPORT_FILE="rac_health_$(date +%Y%m%d_%H%M).html"
# Validate input
if [[ -z "$SCAN_HOST" || -z "$DB_NAME" ]]; then
echo "Invalid input file format. Expected:"
echo "scan_host db_name"
exit 1
fi
# Get SYS password
read -s -p "Enter SYS password for ${SCAN_HOST}/${DB_NAME}: " SYS_PASSWORD
echo
# HTML Header
cat > "$REPORT_FILE" << EOF
RAC Health Check: ${DB_NAME}
Oracle RAC Health Report: ${DB_NAME}
Generated at: $(date)
SCAN Name: ${SCAN_HOST}
EOF
# Function to run SQL and format as HTML table
run_sql_to_html() {
local title="$1"
local query="$2"
local critical="$3"
echo "${title}
" >> "$REPORT_FILE"
sqlplus -S "sys/${SYS_PASSWORD}@//${SCAN_HOST}:1521/${DB_NAME} as sysdba" << EOF | awk '
BEGIN { print ""; print "| Metric | Value |
|---|
" }
/^ERROR/ { print "| " $0 " |
" }
/=/ {
split($0, arr, "=");
cls = "";
if (arr[2] ~ /CRITICAL/) cls = "critical";
if (arr[2] ~ /WARNING/) cls = "warning";
print "| " arr[1] " | " arr[2] " |
"
}
END { print "
" }
' >> "$REPORT_FILE"
SET FEEDBACK OFF HEADING OFF PAGESIZE 0 LINESIZE 1000
${query}
EXIT
EOF
}
# RAC Node Status
run_sql_to_html "Cluster Node Status" "
SELECT 'Node ' || instance_number || ': ' ||
instance_name || ' (' || host_name || ')' || '=' ||
status || ' | Version: ' || version || ' | Startup: ' ||
TO_CHAR(startup_time, 'YYYY-MM-DD HH24:MI')
FROM gv\$instance;
"
# Wait Events Analysis
run_sql_to_html "Top Wait Events (Non-Idle)" "
SELECT event || '=' ||
ROUND(time_waited_micro/1000000,1) || 's (Waits: ' ||
total_waits || ', Avg: ' ||
ROUND(time_waited_micro/total_waits/1000,2) || 'ms)'
FROM (
SELECT event, total_waits, time_waited_micro
FROM gv\$system_event
WHERE wait_class NOT IN ('Idle', 'System I/O')
ORDER BY time_waited_micro DESC
FETCH FIRST 10 ROWS ONLY
);
"
# Global Cache Statistics
run_sql_to_html "Global Cache Performance" "
SELECT
'Global Cache CR Block Receive Time (ms)' || '=' ||
ROUND((SUM(CASE name WHEN 'gc cr block receive time' THEN value END) /
SUM(CASE name WHEN 'gc cr blocks received' THEN value END)) * 10,2)
|| 'ms [CR] | ' ||
ROUND((SUM(CASE name WHEN 'gc current block receive time' THEN value END) /
SUM(CASE name WHEN 'gc current blocks received' THEN value END)) * 10,2)
|| 'ms [Current]'
FROM gv\$sysstat
WHERE name IN (
'gc cr block receive time', 'gc cr blocks received',
'gc current block receive time', 'gc current blocks received'
);
"
# Tablespace Usage
run_sql_to_html "Tablespace Usage" "
SELECT tablespace_name || '=' ||
ROUND(used_percent,1) || '% used | ' ||
CASE WHEN used_percent > 90 THEN 'CRITICAL'
WHEN used_percent > 80 THEN 'WARNING'
ELSE 'OK' END
FROM (
SELECT a.tablespace_name,
(a.bytes_alloc - nvl(b.bytes_free,0))/a.bytes_alloc*100 used_percent
FROM (
SELECT tablespace_name, SUM(bytes) bytes_alloc
FROM dba_data_files GROUP BY tablespace_name
) a,
(
SELECT tablespace_name, SUM(bytes) bytes_free
FROM dba_free_space GROUP BY tablespace_name
) b
WHERE a.tablespace_name = b.tablespace_name(+)
)
ORDER BY used_percent DESC;
"
# Cluster Interconnect
run_sql_to_html "Interconnect Health" "
SELECT
'Network Latency (' || name || ')' || '=' ||
ROUND(value/100,2) || 'ms' ||
CASE WHEN value/100 > 2 THEN ' CRITICAL'
WHEN value/100 > 1 THEN ' WARNING' ELSE '' END
FROM gv\$sysmetric
WHERE metric_name = 'Network Latency'
AND group_id = 2
AND value > 0;
"
# HTML Footer
cat >> "$REPORT_FILE" << EOF
EOF
echo -e "\nReport generated: ${REPORT_FILE}"
