#!/bin/bash
# rac_health_check.sh
# Configuration
CONFIG_FILE="/path/to/rac_config.env"
HTML_REPORT="/tmp/rac_health_$(date +%Y%m%d%H%M).html"
LOG_FILE="/tmp/rac_health_check.log"
MAIL_TEMP="/tmp/rac_email.html"
# Load configuration
source $CONFIG_FILE
# Initialize HTML Report
initialize_report() {
cat <
$HTML_REPORT
Oracle RAC Health Check - $ORACLE_SID
Generated: $(date)
Database: $ORACLE_SID
EOF
}
# Add section to report
add_section() {
echo "" >> $HTML_REPORT
echo "
$1
" >> $HTML_REPORT
echo "$2" >> $HTML_REPORT
echo "" >> $HTML_REPORT
}
# RAC Performance Checks
check_rac_performance() {
local output
output=$(sqlplus -s "/ as sysdba" << EOF
set pagesize 0 feedback off verify off linesize 1000
prompt
prompt | Metric | Value | Status |
|---|
-- Cluster Interconnect Traffic
select '| Interconnect Traffic (MB/s) | '||ROUND(value/1024/1024,2)||
' | '||
CASE WHEN value/1024/1024 > 100 THEN 'CRITICAL'
WHEN value/1024/1024 > 50 THEN 'WARNING'
ELSE 'OK' END||' |
'
from gv\$osstat where stat_name = 'PHYSICAL_INTERCONNECT_BYTES';
-- Global Cache Efficiency
select '| Global Cache Hit Ratio | '||ROUND((1 - (sum(CR_BLOCK_SERVER) + sum(CURRENT_BLOCK_SERVER)) /
(sum(CR_BLOCK_SERVER) + sum(CURRENT_BLOCK_SERVER) + sum(CONSISTENT_GETS)),2)*100||'% | '||
CASE WHEN (1 - (sum(CR_BLOCK_SERVER) + sum(CURRENT_BLOCK_SERVER)) /
(sum(CR_BLOCK_SERVER) + sum(CURRENT_BLOCK_SERVER) + sum(CONSISTENT_GETS)) < 0.9 THEN 'CRITICAL'
WHEN (1 - (sum(CR_BLOCK_SERVER) + sum(CURRENT_BLOCK_SERVER)) /
(sum(CR_BLOCK_SERVER) + sum(CURRENT_BLOCK_SERVER) + sum(CONSISTENT_GETS)) < 0.95 THEN 'WARNING'
ELSE 'OK' END||' |
'
from gv\$buffer_pool_statistics;
-- Instance Load
select '| Instance Load ('||inst_id||') | '||load||' | '||
CASE WHEN load > 10 THEN 'CRITICAL'
WHEN load > 5 THEN 'WARNING'
ELSE 'OK' END||' |
'
from gv\$instance;
prompt
exit
EOF
)
add_section "RAC Performance Metrics" "$output"
}
# Cluster Resources Check
check_cluster_resources() {
local output
output=$(crsctl stat res -t | awk '
BEGIN { print "| Resource | Target | State |
|---|
" }
/^NAME/ { next }
{
status = $NF
print "| " $1 " | " $2 " | " status " |
"
}
END { print "
" }'
)
add_section "Cluster Resources Status" "$output"
}
# Send Email
send_email() {
cat < $MAIL_TEMP
Oracle RAC Health Check Report
Database: $ORACLE_SID
Generated at: $(date)
$(cat $HTML_REPORT)
EOF
mailx -s "$EMAIL_SUBJECT" \
-a "Content-type: text/html" \
-S smtp="$SMTP_SERVER" \
-S smtp-auth=login \
-S smtp-auth-user="$EMAIL_USER" \
-S smtp-auth-password="$EMAIL_PASSWORD" \
-r "$EMAIL_FROM" \
"$EMAIL_TO" < $MAIL_TEMP
}
# Main Execution
initialize_report
check_rac_performance
check_cluster_resources
# Close HTML
echo "" >> $HTML_REPORT
# Send Email
send_email
# Cleanup
rm -f $MAIL_TEMP
Directory Structure
rac_health/
├── bin/
│ └── rac_health_check.sh # Main script
├── cfg/
│ ├── config.env # Main configuration
│ └── email.env # Email settings
├── lib/
│ ├── report_functions.sh # HTML/Email functions
│ └── rac_functions.sh # Health check functions
├── sql/
│ ├── rac_perf_interconnect.sql
│ ├── rac_perf_global_cache.sql
│ ├── rac_perf_instance_load.sql
│ └── cluster_resources.sql
└── tmp/ # Temporary files
1. cfg/config.env
bash
# Path Configuration
BASE_DIR=$(dirname "$(dirname "$0")")
SQL_DIR="$BASE_DIR/sql"
LIB_DIR="$BASE_DIR/lib"
LOG_DIR="$BASE_DIR/logs"
REPORT_DIR="$BASE_DIR/reports"
# Database Configuration
ORACLE_SID=ORCL
ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1
SYSDBA_USER="/ as sysdba"
# Thresholds
INTERCONNECT_CRITICAL=100 # MB/s
INTERCONNECT_WARNING=50
GC_HITRATIO_CRITICAL=90 # %
GC_HITRATIO_WARNING=95
INSTANCE_LOAD_CRITICAL=10
INSTANCE_LOAD_WARNING=5
2. cfg/email.env
bash
EMAIL_ENABLED=true
EMAIL_SUBJECT="Oracle RAC Health Report"
SMTP_SERVER="smtp.example.com:587"
EMAIL_FROM="dba@example.com"
EMAIL_TO="team@example.com"
EMAIL_USER="alert_user"
EMAIL_PASSWORD="secure_pass"
USE_TLS=true
3. lib/report_functions.sh
bash
#!/bin/bash
init_report() {
REPORT_FILE="$REPORT_DIR/rac_health_$(date +%Y%m%d%H%M).html"
mkdir -p "$REPORT_DIR"
cat < $REPORT_FILE
Oracle RAC Health Check
$(get_report_styles)
Generated: $(date "+%Y-%m-%d %H:%M")
Database: $ORACLE_SID
EOF
}
get_report_styles() {
cat <
body { font-family: Arial, sans-serif; margin: 20px; }
.header { color: #2c3e50; border-bottom: 2px solid #3498db; }
.section { margin: 25px 0; }
table { width: 100%; border-collapse: collapse; margin-top: 15px; }
th, td { padding: 10px; border: 1px solid #ddd; }
th { background-color: #3498db; color: white; }
.critical { background-color: #ffcccc; }
.warning { background-color: #fff3cd; }
.ok { background-color: #d4edda; }
EOF
}
add_section() {
echo "" >> $REPORT_FILE
echo "
$1
" >> $REPORT_FILE
echo "$2" >> $REPORT_FILE
echo "" >> $REPORT_FILE
}
finalize_report() {
echo "" >> $REPORT_FILE
}
send_email() {
[ "$EMAIL_ENABLED" = "true" ] || return 0
local mail_body=$(mktemp)
cat < $mail_body
$(cat $REPORT_FILE)
EOF
mailx -s "$EMAIL_SUBJECT" \
-a "Content-type: text/html" \
-S smtp="$SMTP_SERVER" \
-S smtp-auth=login \
-S smtp-auth-user="$EMAIL_USER" \
-S smtp-auth-password="$EMAIL_PASSWORD" \
-S smtp-use-starttls \
-r "$EMAIL_FROM" \
"$EMAIL_TO" < $mail_body
rm -f $mail_body
}
4. lib/rac_functions.sh
bash
#!/bin/bash
run_sql() {
local sql_file=$1
local output_var=$2
local tmp_file=$(mktemp)
sqlplus -S "$SYSDBA_USER" @"$SQL_DIR/$sql_file" > $tmp_file
local result=$(cat $tmp_file)
rm -f $tmp_file
eval $output_var="'$result'"
}
check_interconnect() {
local output
run_sql "rac_perf_interconnect.sql" output
add_section "Interconnect Traffic" "$output"
}
check_global_cache() {
local output
run_sql "rac_perf_global_cache.sql" output
add_section "Global Cache Efficiency" "$output"
}
check_instance_load() {
local output
run_sql "rac_perf_instance_load.sql" output
add_section "Instance Load" "$output"
}
check_cluster_resources() {
local output
run_sql "cluster_resources.sql" output
add_section "Cluster Resources" "$output"
}
5. SQL Files (sql/*.sql)
sql/rac_perf_interconnect.sql
sql
set pagesize 0 feedback off verify off linesize 1000
select ''
'| Node | Interconnect Traffic (MB/s) | Status |
|---|
' from dual;
select ''
'| '||inst_id||' | '
''||ROUND(value/1024/1024,2)||' | '
''||
case
when value/1024/1024 > &INTERCONNECT_CRITICAL then 'CRITICAL'
when value/1024/1024 > &INTERCONNECT_WARNING then 'WARNING'
else 'OK' end||' |
'
from gv$osstat
where stat_name = 'PHYSICAL_INTERCONNECT_BYTES';
select '
' from dual;
exit;
sql/rac_perf_global_cache.sql
sql
set pagesize 0 feedback off verify off
-- Similar structure with &GC_HITRATIO_CRITICAL/_WARNING parameters
sql/cluster_resources.sql
sql
sql/cluster_resources.sql
sql
set pagesize 0 feedback off
select '| Resource | Target | State |
|---|
' from dual;
!crsctl stat res -t | awk '/^NAME/ {next} {printf "| %s | %s | %s |
\n", $1, $2, ($NF=="ONLINE"?"ok":"critical"), $NF}'
select '
' from dual;
exit;
6. bin/rac_health_check.sh (Main Script)
bash
#!/bin/bash
BASE_DIR=$(dirname "$0")
source "$BASE_DIR/../cfg/config.env"
source "$BASE_DIR/../cfg/email.env"
source "$BASE_DIR/../lib/report_functions.sh"
source "$BASE_DIR/../lib/rac_functions.sh"
# Initialize
init_report
# Run checks
check_interconnect
check_global_cache
check_instance_load
check_cluster_resources
# Finalize
finalize_report
# Send notification
send_email
Key Benefits:
Modular Architecture:
SQL queries separated from business logic
Configuration managed externally
Easy to add new checks by creating:
New SQL file in sql/
Corresponding function in rac_functions.sh
Parameterized SQL:
sql
-- Uses threshold variables from config.env
where value/1024/1024 > &INTERCONNECT_CRITICAL
Consistent Styling:
Centralized CSS management
Standardized HTML table structures
Easy Maintenance:
Update thresholds in config.env
Modify SQL without touching shell scripts
Add/remove checks via config
Usage:
bash
chmod +x bin/rac_health_check.sh
./bin/rac_health_check.sh
Enhancement Guide:
Add New Check:
Create new SQL file in sql/
Add function in rac_functions.sh
Call function in main script
Modify Thresholds:
Edit values in cfg/config.env
Change Email Format:
Modify lib/report_functions.sh
This structure enables:
Team collaboration (DBAs manage SQL, DevOps manage shell)
Version control efficiency
Safe code updates
Automated testing per component
Would you like me to create any additional SQL checks or demonstrate how to add a new resource type check?
File Structure
rac_health/
├── bin/
│ └── rac_health_check.sh
├── cfg/
│ ├── config.env
│ ├── email.env
│ └── databases.list # New database list file
├── lib/
│ ├── report_functions.sh
│ └── rac_functions.sh
├── sql/
│ └── ... (existing SQL files)
└── reports/
├── details/
└── summary/
1. cfg/databases.list
text
# Format: DB_NAME|HOST:PORT/SERVICE|SYS_PWD
DB1|db1-host:1521/DB1_SVC|SysPass123
DB2|db2-host:1522/DB2_SVC|SecurePwd456
2. cfg/config.env (Updated)
bash
# Add these
DB_LIST_FILE="$BASE_DIR/cfg/databases.list"
DETAIL_REPORT_DIR="$REPORT_DIR/details"
SUMMARY_REPORT_DIR="$REPORT_DIR/summary"
3. lib/report_functions.sh (Updated)
bash
init_summary_report() {
SUMMARY_FILE="$SUMMARY_REPORT_DIR/summary_$(date +%Y%m%d%H%M).html"
mkdir -p "$SUMMARY_REPORT_DIR"
cat < $SUMMARY_FILE
Oracle RAC Health Summary
$(get_report_styles)
Generated: $(date "+%Y-%m-%d %H:%M")
| Database |
Interconnect |
Global Cache |
Instance Load |
Resources |
Overall Status |
Details |
EOF
}
add_db_to_summary() {
local db_name=$1
local metrics=$2 # "INTERCONNECT:OK,GLOBAL_CACHE:WARN..."
local detail_file=$3
# Parse metrics
declare -A status_map
IFS=',' read -ra pairs <<< "$metrics"
for pair in "${pairs[@]}"; do
IFS=':' read key value <<< "$pair"
status_map[$key]=$value
done
# Determine overall status
local overall_status="OK"
for key in "${!status_map[@]}"; do
if [[ "${status_map[$key]}" == "CRITICAL" ]]; then
overall_status="CRITICAL"
break
elif [[ "${status_map[$key]}" == "WARNING" && "$overall_status" != "CRITICAL" ]]; then
overall_status="WARNING"
fi
done
cat <> $SUMMARY_FILE
| $db_name |
${status_map[INTERCONNECT]} |
${status_map[GLOBAL_CACHE]} |
${status_map[INSTANCE_LOAD]} |
${status_map[CLUSTER_RESOURCES]} |
$overall_status |
View Details |
EOF
}
finalize_summary_report() {
echo "
" >> $SUMMARY_FILE
}
4. lib/rac_functions.sh (Updated)
bash
check_database() {
local db_name=$1
local conn_str=$2
local sys_pwd=$3
local metrics=""
local detail_file="$DETAIL_REPORT_DIR/${db_name}_report_$(date +%Y%m%d%H%M).html"
# Initialize detail report
init_report "$detail_file" "$db_name"
# Run checks and capture statuses
check_interconnect "$conn_str" "$sys_pwd"
metrics+="INTERCONNECT:$INTERCONNECT_STATUS,"
check_global_cache "$conn_str" "$sys_pwd"
metrics+="GLOBAL_CACHE:$GLOBAL_CACHE_STATUS,"
check_instance_load "$conn_str" "$sys_pwd"
metrics+="INSTANCE_LOAD:$INSTANCE_LOAD_STATUS,"
check_cluster_resources "$conn_str" "$sys_pwd"
metrics+="CLUSTER_RESOURCES:$CLUSTER_RESOURCES_STATUS"
finalize_report
# Add to summary
add_db_to_summary "$db_name" "$metrics" "$(basename $detail_file)"
echo "$metrics" # Return statuses for tracking
}
check_interconnect() {
local conn_str=$1
local sys_pwd=$2
local output
local sql_file="rac_perf_interconnect.sql"
run_sql "$sql_file" "$conn_str" "$sys_pwd" output
# Parse status from SQL output
INTERCONNECT_STATUS=$(echo "$output" | grep 'class="' | head -1 |
awk -F'"' '{if ($2 == "critical") print "CRITICAL";
else if ($2 == "warning") print "WARNING";
else print "OK"}')
add_section "Interconnect Traffic" "$output"
}
5. bin/rac_health_check.sh (Updated)
bash
#!/bin/bash
BASE_DIR=$(dirname "$0")
source "$BASE_DIR/../cfg/config.env"
source "$BASE_DIR/../cfg/email.env"
source "$BASE_DIR/../lib/report_functions.sh"
source "$BASE_DIR/../lib/rac_functions.sh"
# Initialize reports
init_summary_report
mkdir -p "$DETAIL_REPORT_DIR"
# Process databases
while IFS='|' read -r db_name conn_str sys_pwd; do
[[ "$db_name" =~ ^#|^$ ]] && continue # Skip comments/empty lines
echo "Checking database: $db_name"
metrics=$(check_database "$db_name" "$conn_str" "$sys_pwd")
# Log status
echo "$(date) - $db_name - $metrics" >> "$LOG_DIR/health_check.log"
done < "$DB_LIST_FILE"
# Finalize and send
finalize_summary_report
send_email "$SUMMARY_FILE"
6. New CSS Classes
css
/* Add to report styles */
.CRITICAL { background-color: #ffcccc; color: #cc0000; }
.WARNING { background-color: #fff3cd; color: #856404; }
.OK { background-color: #d4edda; color: #155724; }
td.OK, td.WARNING, td.CRITICAL {
font-weight: bold;
text-align: center;
}
1. Performance Check SQL Scripts (sql/rac_perf_checks/)
a. interconnect_perf.sql
sql
set pagesize 0 feedback off linesize 100
select 'INTERCONNECT_LOAD:'||
round((sum(decode(stat_name,'PHYSICAL_INTERCONNECT_BYTES',value))/1024/1024)/60,2)||' MB/s,'||
'LATENCY:'||round(avg(decode(stat_name,'INTERCONNECT_LATENCY',value)),2)||' ms,'||
'STATUS:'||case
when (sum(decode(stat_name,'PHYSICAL_INTERCONNECT_BYTES',value))/1024/1024)/60 > 100 then 'CRITICAL'
when (sum(decode(stat_name,'PHYSICAL_INTERCONNECT_BYTES',value))/1024/1024)/60 > 50 then 'WARNING'
else 'OK' end
from gv$osstat
where stat_name in ('PHYSICAL_INTERCONNECT_BYTES','INTERCONNECT_LATENCY');
exit;
b. global_cache_waits.sql
sql
set pagesize 0 feedback off linesize 100
select 'GCS_WAITS:'||total_waits||','||
'AVG_WAIT_TIME:'||round(time_waited_micro/nullif(total_waits,0)/1000,2)||' ms,'||
'STATUS:'||case
when time_waited_micro/nullif(total_waits,0)/1000 > 10 then 'CRITICAL'
when time_waited_micro/nullif(total_waits,0)/1000 > 5 then 'WARNING'
else 'OK' end
from v$system_event
where event like 'gc%block%';
exit;
c. instance_imbalance.sql
sql
set pagesize 0 feedback off linesize 100
select 'LOAD_IMBALANCE:'||round((max(load) - min(load))/avg(load)*100||'%,'||
'CPU_IMBALANCE:'||round((max(cpu) - min(cpu))/avg(cpu)*100||'%,'||
'STATUS:'||case
when (max(load) - min(load))/avg(load)*100 > 30 then 'CRITICAL'
when (max(load) - min(load))/avg(load)*100 > 20 then 'WARNING'
else 'OK' end
from (
select inst_id, value load,
(select value from gv$sysstat where name = 'CPU used by this session' and inst_id = a.inst_id) cpu
from gv$sysstat a
where name = 'logons current'
);
exit;
2. Performance Check Functions (lib/rac_perf_functions.sh)
bash
check_interconnect_perf() {
local conn=$1
local output
run_sql "rac_perf_checks/interconnect_perf.sql" "$conn" output
IFS=',' read -ra metrics <<< "$output"
declare -A results
for metric in "${metrics[@]}"; do
IFS=':' read key value <<< "$metric"
results["$key"]="$value"
done
add_perf_metric "Interconnect" \
"${results[INTERCONNECT_LOAD]}" \
"${results[LATENCY]}" \
"${results[STATUS]}"
}
check_global_cache_waits() {
local conn=$1
local output
run_sql "rac_perf_checks/global_cache_waits.sql" "$conn" output
IFS=',' read -ra metrics <<< "$output"
declare -A results
for metric in "${metrics[@]}"; do
IFS=':' read key value <<< "$metric"
results["$key"]="$value"
done
add_perf_metric "Global Cache" \
"${results[GCS_WAITS]}" \
"${results[AVG_WAIT_TIME]}" \
"${results[STATUS]}"
}
check_instance_balance() {
local conn=$1
local output
run_sql "rac_perf_checks/instance_imbalance.sql" "$conn" output
IFS=',' read -ra metrics <<< "$output"
declare -A results
for metric in "${metrics[@]}"; do
IFS=':' read key value <<< "$metric"
results["$key"]="$value"
done
add_perf_metric "Instance Balance" \
"${results[LOAD_IMBALANCE]}" \
"${results[CPU_IMBALANCE]}" \
"${results[STATUS]}"
}
3. HTML Report Generation Enhancements
a. Performance Metrics Table
bash
add_perf_metric() {
local metric_name=$1
local value1=$2
local value2=$3
local status=$4
local status_class
case $status in
CRITICAL) status_class="critical" ;;
WARNING) status_class="warning" ;;
*) status_class="ok" ;;
esac
cat <> $REPORT_FILE
| $metric_name |
$value1 |
$value2 |
$status |
EOF
}
b. Performance Section in Report
bash
add_performance_section() {
cat <> $REPORT_FILE
EOF
}
4. Threshold Configuration (cfg/performance_thresholds.env)
bash
# Interconnect
INTERCONNECT_LOAD_CRITICAL=100 # MB/s
INTERCONNECT_LATENCY_CRITICAL=5 # ms
# Global Cache
GC_WAIT_TIME_CRITICAL=10 # ms
GC_WAIT_COUNT_CRITICAL=1000 # waits/sec
# Instance Balance
LOAD_IMBALANCE_CRITICAL=30 # %
CPU_IMBALANCE_CRITICAL=20 # %
# Wait Events
CRITICAL_WAIT_EVENTS="gc buffer busy acquire, gc cr block busy, enq: TX - row lock contention"
5. Advanced Bottleneck Detection (sql/rac_bottleneck_analysis.sql)
sql
set pagesize 0 feedback off linesize 200
select 'CATEGORY:'||category||','||
'WAIT_COUNT:'||total_waits||','||
'TIME_WAITED:'||round(time_waited_micro/1000000,2)||' sec,'||
'AVG_WAIT:'||round(time_waited_micro/nullif(total_waits,0)/1000,2)||' ms'
from (
select case
when event like 'gc%' then 'Global Cache'
when event like 'enq%' then 'Locking'
when event like 'log file sync' then 'Commit'
when event like 'db file%' then 'IO'
else 'Other'
end category,
sum(total_waits) total_waits,
sum(time_waited_micro) time_waited_micro
from gv$system_event
where wait_class <> 'Idle'
group by case
when event like 'gc%' then 'Global Cache'
when event like 'enq%' then 'Locking'
when event like 'log file sync' then 'Commit'
when event like 'db file%' then 'IO'
else 'Other'
end
);
exit;
6. Bottleneck Analysis Function
bash
analyze_bottlenecks() {
local conn=$1
local output
run_sql "rac_perf_checks/rac_bottleneck_analysis.sql" "$conn" output
declare -A categories
while IFS= read -r line; do
IFS=',' read -ra metrics <<< "$line"
declare -A values
for metric in "${metrics[@]}"; do
IFS=':' read key value <<< "$metric"
values["$key"]="$value"
done
categories["${values[CATEGORY]}"]="${values[WAIT_COUNT]}:${values[TIME_WAITED]}:${values[AVG_WAIT]}"
done <<< "$output"
cat <> $REPORT_FILE
Bottleneck Analysis
| Category |
Total Waits |
Time Waited (sec) |
Avg Wait (ms) |
EOF
for category in "${!categories[@]}"; do
IFS=':' read waits time avg <<< "${categories[$category]}"
cat <> $REPORT_FILE
| $category |
$waits |
$time |
$avg |
EOF
done
cat <> $REPORT_FILE
EOF
}
Key Features:
Comprehensive RAC-Specific Checks
Interconnect performance (throughput + latency)
Global cache wait analysis
Instance load balancing
Wait event breakdown
Lock contention analysis
Threshold-Based Alerting
bash
# Example Critical Thresholds:
- Interconnect latency >5ms
- GC avg wait >10ms
- Instance load imbalance >30%
Visual Bottleneck Identification
html
| Global Cache |
15,234 |
325.12 |
21.3 |
| Locking |
8,765 |
412.45 |
47.1 |
Performance Recommendations
High Global Cache Waits:
Verify interconnect configuration
Check hot blocks with v$gc_element
Consider application partitioning
Instance Imbalance:
Review services configuration
Check connection balancing
Verify workload distribution
Trend Analysis Integration
bash
# To enable historical tracking:
./rac_perf_check.sh --track --compare-day 7
Usage Example:
bash
# Run full performance check
./rac_health_check.sh --mode performance
# Sample output:
================================================================
Oracle RAC Performance Health Check - ORCL
================================================================
[✓] Interconnect: 45 MB/s | 2.1 ms
[!] Global Cache: 823 waits | 8.2 ms (Warning)
[✗] Instance Balance: 35% load imbalance (Critical)
----------------------------------------------------------------
Top Wait Categories:
1. Global Cache (45% of waits)
2. IO (30% of waits)
3. Locking (15% of waits)
----------------------------------------------------------------
Recommendations:
1. Investigate instance load distribution
2. Check for hot blocks in global cache
3. Review storage performance metrics
This implementation provides:
Real-time RAC performance diagnostics
Automatic bottleneck identification
Actionable recommendations
Capacity planning data
Historical performance tracking
Integration with Enterprise Manager data (optional)
1. SQL Scripts (sql/performance_historical/)
a. db_load_last_7d.sql
sql
set pagesize 0 feedback off linesize 200
select 'DAY:'||to_char(end_time,'YYYY-MM-DD')||','||
'AVG_LOAD:'||round(average,1)||','||
'PEAK_LOAD:'||round(maximum,1)
from dba_hist_sysmetric_summary
where metric_name = 'Database CPU Time Ratio'
and end_time >= sysdate - 7
group by to_char(end_time,'YYYY-MM-DD')
order by 1;
exit;
b. session_history_7d.sql
sql
set pagesize 0 feedback off linesize 200
select 'HOUR:'||to_char(sample_time,'YYYY-MM-DD HH24')||','||
'ACTIVE:'||count(case when session_state='ON CPU' then 1 end)||','||
'INACTIVE:'||count(case when session_state='WAITING' then 1 end)
from dba_hist_active_sess_history
where sample_time >= sysdate - 7
group by to_char(sample_time,'YYYY-MM-DD HH24')
order by 1;
exit;
c. session_utilization.sql
sql
set pagesize 0 feedback off linesize 200
select 'CURRENT:'||(select count(*) from v\$session)||','||
'MAX_LIMIT:'||value||','||
'UTILIZATION:'||round((select count(*) from v\$session)/value*100,1)||'%'
from v\$parameter where name='processes';
exit;
d. memory_usage_7d.sql
sql
set pagesize 0 feedback off linesize 200
select 'DAY:'||to_char(end_time,'YYYY-MM-DD')||','||
'SGA_SIZE_GB:'||round(sum(case when memory_area='SGA' then value end)/1024/1024/1024,2)||','||
'PGA_SIZE_GB:'||round(sum(case when memory_area='PGA' then value end)/1024/1024/1024,2)
from dba_hist_memory_stat
where end_time >= sysdate - 7
group by to_char(end_time,'YYYY-MM-DD')
order by 1;
exit;
e. blocking_sessions.sql
sql
set pagesize 0 feedback off linesize 200
select 'BLOCKER:'||b.sid||','||
'WAITER:'||w.sid||','||
'DURATION:'||(w.seconds_in_wait)||','||
'SQL_ID:'||w.sql_id
from gv\$lock b, gv\$lock w
where b.id1 = w.id1
and b.id2 = w.id2
and b.block > 0
and w.request > 0;
exit;
f. long_running_sessions.sql
sql
set pagesize 0 feedback off linesize 200
select 'SID:'||sid||','||
'USER:'||username||','||
'MODULE:'||module||','||
'DURATION:'||(sysdate - logon_time)*24||','||
'SQL_ID:'||sql_id
from gv\$session
where status = 'ACTIVE'
and (sysdate - logon_time)*24 > 1;
exit;
g. current_io_stats.sql
sql
set pagesize 0 feedback off linesize 200
select 'READ_IOPS:'||value||','||
'LATENCY_MS:'||(select average_sync_time from v\$iostat_file)||','||
'THROUGHPUT_MB:'||(select sum(bytes)/1024/1024 from v\$iostat_function)
from v\$sysmetric
where metric_name='I/O Requests per Second'
and group_id=2;
exit;
2. Shell Script Functions (lib/performance_functions.sh)
bash
track_db_load() {
local conn=$1
local output
run_sql "performance_historical/db_load_last_7d.sql" "$conn" output
cat <> $REPORT_FILE
Database Load History (7 Days)
| Date | Avg Load (%) | Peak Load (%) |
|---|
$(echo "$output" | awk -F',' '{split($1,d,":"); split($2,avg,":"); split($3,peak,":");
printf "| %s | %s | %s |
\n", d[2], avg[2], peak[2]}')
EOF
}
show_session_history() {
local conn=$1
local output
run_sql "performance_historical/session_history_7d.sql" "$conn" output
cat <> $REPORT_FILE
Session Activity History (7 Days)
| Hour | Active | Inactive |
|---|
$(echo "$output" | awk -F',' '{split($1,h,":"); split($2,act,":"); split($3,inact,":");
printf "| %s | %s | %s |
\n", h[2], act[2], inact[2]}')
EOF
}
check_session_utilization() {
local conn=$1
local output
run_sql "performance_historical/session_utilization.sql" "$conn" output
IFS=',' read -ra metrics <<< "$output"
declare -A sess_stats
for metric in "${metrics[@]}"; do
IFS=':' read key value <<< "$metric"
sess_stats["$key"]="$value"
done
cat <> $REPORT_FILE
Session Utilization
| Current Sessions | ${sess_stats[CURRENT]} |
| Max Allowed | ${sess_stats[MAX_LIMIT]} |
| Utilization | ${sess_stats[UTILIZATION]} |
EOF
}
# Similar functions for memory_usage_7d, blocking_sessions, etc...
3. HTML Report Enhancements
Add these styles to the CSS:
css
/* Heatmap styling for historical data */
.heatmap {
display: grid;
grid-template-columns: repeat(24, 1fr);
gap: 2px;
margin: 20px 0;
}
.heatmap-cell {
padding: 5px;
text-align: center;
font-size: 0.8em;
background-color: #e0e0e0;
}
.critical-cell {
background-color: #ff4444 !important;
}
.warning-cell {
background-color: #ffd700 !important;
}
4. Main Script Integration (rac_health_check.sh)
Add these function calls in the main script:
bash
perform_advanced_checks() {
local conn=$1
track_db_load "$conn"
show_session_history "$conn"
check_session_utilization "$conn"
check_memory_usage "$conn"
detect_blocking_sessions "$conn"
find_long_running_sessions "$conn"
check_io_performance "$conn"
}
