Wednesday, February 5, 2025
Oracle RAC Build/Installation
Prerequisites & Planning
What prerequisites (OS packages, kernel parameters, network setup) are critical for an Oracle RAC installation?
How do you configure shared storage (ASM, NFS, etc.) for RAC? What tools (e.g., asmca, crsctl) would you use?
Installation Process
Walk me through the steps to install Oracle RAC using Oracle Universal Installer (OUI).
What role does the Grid Infrastructure (GI) play in a RAC environment? How does it differ from a standalone installation?
Network Configuration
Explain the purpose of SCAN (Single Client Access Name) listeners. How do they differ from regular listeners?
How would you troubleshoot interconnect communication issues between RAC nodes?
Post-Installation Checks
What commands (e.g., crsctl, srvctl) would you use to verify the health of the cluster?
How do you validate that all RAC nodes are functioning correctly and can access shared storage?
ASM Integration
How do you configure ASM disk groups for RAC? What redundancy levels (normal, high, external) are appropriate for different scenarios?
What is the role of voting disks and OCR (Oracle Cluster Registry) in RAC? How would you back them up?
Oracle RAC Patching & Upgrades
Patch Types
What is the difference between a PSU (Patch Set Update), CPU (Critical Patch Update), and a one-off patch?
When would you use an OPatch auto rollback versus manual rollback?
Rolling vs. Non-Rolling Patches
Explain the process for applying a rolling patch to Oracle RAC. What components can be patched without downtime?
How do you handle a non-rolling patch (e.g., Grid Infrastructure upgrade) in a RAC environment?
Patching Workflow
Walk through the steps to patch a RAC environment using OPatch. What pre-checks are mandatory?
How do you verify that all nodes are running the same patch level post-update?
Troubleshooting Patching Failures
What logs would you check if a RAC patch fails during application (e.g., GI patching errors)?
How do you resolve OPatch conflicts or missing dependencies during patching?
Upgrade Strategies
How would you plan a RAC database upgrade from 12c to 19c with minimal downtime?
What are the risks of using DBUA (Database Upgrade Assistant) versus manual upgrades in a RAC environment?
Troubleshooting RAC-Specific Issues
Node Eviction & Split Brain
What causes node evictions in RAC? How would you diagnose and resolve this?
What is a "split-brain" scenario, and how does RAC prevent it?
Performance in RAC
How does "Global Cache Block Transfer" impact RAC performance? What wait events (e.g., gc cr block busy) would you monitor?
What tools (AWR, ADDM) or views (GV$SESSION, GV$LOCK) do you use to diagnose RAC-specific performance issues?
Clusterware Issues
How do you troubleshoot OCR or voting disk corruption in RAC?
What steps would you take if crsctl start crs fails on a RAC node?
Situational/Scenario-Based Questions
Disaster Recovery
A RAC node crashes due to storage failure. How do you restore the cluster to full health?
How would you rebuild a lost OCR file or voting disk in a live RAC environment?
Scaling & Maintenance
How do you add a new node to an existing RAC cluster? What steps are involved?
Describe a time you performed a rolling maintenance task (e.g., OS patching) on a RAC cluster.
High Availability
How do you configure services in RAC to ensure workload balancing and failover?
What is the role of FAN (Fast Application Notification) in RAC, and how does it integrate with applications?
Advanced Topics
RAC on Cloud/Exadata
What unique considerations apply when deploying RAC on Oracle Exadata or OCI (Oracle Cloud Infrastructure)?
How does RAC differ from Oracle Autonomous Database in terms of scalability and management?
Security
How do you enforce TDE (Transparent Data Encryption) in a RAC environment?
What security best practices apply to RAC interconnect traffic?
Automation
How have you automated RAC provisioning or patching using tools like Ansible, Terraform, or OEM?
Sunday, February 2, 2025
@echo off
setlocal enabledelayedexpansion
set "urls_file=urls.txt"
set "username=your_username"
set "password=your_password"
set "edge_path=%ProgramFiles(x86)%\Microsoft\Edge\Application\msedge.exe"
:: Loop through each URL
for /f "usebackq delims=" %%u in ("%urls_file%") do (
set "url=%%u"
:: Launch Edge with URL
start "" "%edge_path%" --new-window "!url!"
:: Wait for page to load (adjust delay as needed)
timeout /t 7 >nul
:: Send credentials using PowerShell
powershell -command "$wshell = New-Object -ComObject wscript.shell; $wshell.AppActivate('Microsoft Edge'); Start-Sleep -Milliseconds 800; $wshell.SendKeys('{TAB}'); $wshell.SendKeys('%username%'); $wshell.SendKeys('{TAB}'); $wshell.SendKeys('%password%'); $wshell.SendKeys('~')"
:: Wait for login to process
timeout /t 5 >nul
:: Close Edge window (Alt+F4)
powershell -command "$wshell = New-Object -ComObject wscript.shell; $wshell.AppActivate('Microsoft Edge'); $wshell.SendKeys('%%{F4}')"
:: Add delay before next URL
timeout /t 2 >nul
)
##
#!/bin/bash
# Database Connection Parameters
DB_USER="sysman"
DB_PASS="your_password"
DB_HOST="rac-host.example.com"
DB_SERVICE="your_service"
SYSDBA="as sysdba"
# Email Parameters
EMAIL_TO="dba@example.com"
EMAIL_SUBJECT="Oracle RAC Health Check Report"
# Output File
OUTPUT_FILE="/tmp/rac_health_report.html"
# Function to run SQL query and format HTML
run_query() {
sqlplus -s /nolog << EOF | awk 'BEGIN{print ""} {print "
"}' | sed 's/|/\<\/td\>\/g'
connect $DB_USER/$DB_PASS@$DB_HOST/$DB_SERVICE $SYSDBA
set pagesize 50000
set linesize 1000
set heading off
set feedback off
$1
exit
EOF
}
# Start HTML Report
cat > $OUTPUT_FILE << EOF
Oracle RAC Health Check Report
| " $0 " |
Oracle RAC Health Check Report
Generated: $(date)
EOF # 1. All Instance/DB Status echo "1. Database/Instance Status
" >> $OUTPUT_FILE run_query "SELECT INST_ID, INSTANCE_NAME, HOST_NAME, STATUS, DATABASE_STATUS FROM GV\$INSTANCE;" >> $OUTPUT_FILE # 2. Long Running Sessions echo "2. Long Running Sessions (> 1 hour)
" >> $OUTPUT_FILE run_query "SELECT INST_ID, SID, SERIAL#, USERNAME, SQL_ID, ROUND((SYSDATE - SQL_EXEC_START)*24,2) HOURS, STATUS FROM GV\$SESSION WHERE (SYSDATE - SQL_EXEC_START)*24 > 1;" >> $OUTPUT_FILE # 3. DB Blocking echo "3. Database Blocking Sessions
" >> $OUTPUT_FILE run_query "SELECT blocker.INST_ID, blocker.SID, blocker_user.username blocker_user, waiter.INST_ID, waiter.SID, waiter_user.username waiter_user FROM GV\$LOCK blocker JOIN GV\$LOCK waiter ON (blocker.ID1 = waiter.ID1 AND blocker.ID2 = waiter.ID2) JOIN GV\$SESSION blocker_user ON (blocker.SID = blocker_user.SID) JOIN GV\$SESSION waiter_user ON (waiter.SID = waiter_user.SID) WHERE blocker.BLOCK > 0 AND waiter.REQUEST > 0;" >> $OUTPUT_FILE # 4. DB Locking echo "4. Database Locking
" >> $OUTPUT_FILE run_query "SELECT INST_ID, SID, TYPE, LMODE, REQUEST, CTIME FROM GV\$LOCK WHERE TYPE NOT IN ('MR','AE');" >> $OUTPUT_FILE # 5. DB Load Last 1 Hour echo "5. Database Load (Last 1 Hour)
" >> $OUTPUT_FILE run_query "SELECT BEGIN_TIME, END_TIME, AVG(CPU) CPU_AVG, AVG(IO) IO_AVG FROM GV\$SYSMETRIC_HISTORY WHERE GROUP_ID = 2 AND INTSIZE_CSEC < (60 * 100 * 60) GROUP BY BEGIN_TIME, END_TIME ORDER BY BEGIN_TIME DESC FETCH FIRST 12 ROWS ONLY;" >> $OUTPUT_FILE # 6. DB Load Last 5 Minutes echo "6. Database Load (Last 5 Minutes)
" >> $OUTPUT_FILE run_query "SELECT BEGIN_TIME, END_TIME, CPU, IO FROM GV\$SYSMETRIC WHERE GROUP_ID = 2 ORDER BY BEGIN_TIME DESC FETCH FIRST 1 ROW ONLY;" >> $OUTPUT_FILE # 7. DB Parallel echo "7. Database Parallel Processes
" >> $OUTPUT_FILE run_query "SELECT INST_ID, COUNT(*) PARALLEL_PROCS FROM GV\$PX_PROCESS GROUP BY INST_ID;" >> $OUTPUT_FILE # 8. Invalid Objects echo "8. Invalid Objects
" >> $OUTPUT_FILE run_query "SELECT OWNER, OBJECT_TYPE, COUNT(*) FROM DBA_OBJECTS WHERE STATUS != 'VALID' GROUP BY OWNER, OBJECT_TYPE;" >> $OUTPUT_FILE # 9. Session Failure echo "9. Session Failures
" >> $OUTPUT_FILE run_query "SELECT INST_ID, COUNT(*) FROM GV\$SESSION WHERE FAILOVER_TYPE IS NOT NULL AND FAILED_OVER = 'YES' GROUP BY INST_ID;" >> $OUTPUT_FILE # 10. Active Session Count echo "10. Active Session Count
" >> $OUTPUT_FILE run_query "SELECT INST_ID, COUNT(*) ACTIVE_SESSIONS FROM GV\$SESSION WHERE STATUS = 'ACTIVE' GROUP BY INST_ID;" >> $OUTPUT_FILE # 11. All Database Service Info echo "11. Database Services
" >> $OUTPUT_FILE run_query "SELECT INST_ID, NAME, NETWORK_NAME, CREATED, GOAL, ENABLED FROM GV\$SERVICES;" >> $OUTPUT_FILE # 12. All ORA-Errors echo "12. Recent ORA-Errors
" >> $OUTPUT_FILE run_query "SELECT ORIGINATING_TIMESTAMP, MESSAGE_TEXT FROM GV\$DIAG_ALERT_EXT WHERE MESSAGE_TEXT LIKE 'ORA-%' AND ORIGINATING_TIMESTAMP > SYSDATE - 1/24;" >> $OUTPUT_FILE # Close HTML echo "" >> $OUTPUT_FILE # Send Email mailx -a "$OUTPUT_FILE" -s "$EMAIL_SUBJECT" -S content-type=text/html "$EMAIL_TO" < /dev/null # Cleanup rm -f $OUTPUT_FILE
Saturday, February 1, 2025
#!/bin/bash
# Configuration
ORACLE_USER="remote_user"
ORACLE_PASS="password"
RAC_NODE="rac_node1"
EMAIL_RECIPIENT="admin@example.com"
REPORT_FILE="/tmp/oracle_health_report.html"
# Create HTML Report Header
cat << EOF > $REPORT_FILE
Oracle RAC Health Check Report
Oracle RAC Health Check Report
"
echo "To: $EMAIL_RECIPIENT"
echo "Subject: $EMAIL_SUBJECT"
echo "MIME-Version: 1.0"
echo "Content-Type: text/html"
echo
cat "$REPORT_FILE"
) | mailx -s "$EMAIL_SUBJECT" -a "$REPORT_FILE" "$EMAIL_RECIPIENT"
# Cleanup
rm -f "$REPORT_FILE" "$LOG_FILE"
#!/bin/bash
# Configuration (Modify these parameters)
DB_USER="your_db_user"
DB_PASS="your_db_password"
DB_HOST="your_db_host"
DB_SERVICE="your_db_service"
EMAIL_TO="your_email@example.com"
EMAIL_SUBJECT="Oracle RAC Health Check Report"
HTML_REPORT="/tmp/oracle_rac_health_check.html"
# Start HTML Output
echo "Oracle RAC Health Check " > $HTML_REPORT
echo "
" >> $HTML_REPORT # Function to run SQL and format output as HTML run_sql() { local title=$1 local sql_query=$2 echo "
" >> $HTML_REPORT } # 1) All Instance / DB Status run_sql "RAC Instance and Database Status" "SELECT inst_id, instance_name, status, database_status FROM GV\$INSTANCE;" # 2) Long Running Sessions (active > 1 hour) run_sql "Long Running Sessions (per instance)" " SELECT inst_id, sid, serial#, username, status, sql_id, last_call_et FROM GV\$SESSION WHERE status='ACTIVE' AND last_call_et > 3600 ORDER BY last_call_et DESC;" # 3) DB Blocking run_sql "Blocking Sessions Across Instances" " SELECT inst_id, blocking_session, sid, serial#, wait_class, seconds_in_wait FROM GV\$SESSION WHERE blocking_session IS NOT NULL;" # 4) DB Locking run_sql "Database Locks" " SELECT inst_id, sid, type, id1, id2, lmode, request, block FROM GV\$LOCK WHERE block != 0;" # 5) DB Load Last 1 Hour run_sql "Database Load Last 1 Hour" " SELECT inst_id, TO_CHAR(begin_time, 'YYYY-MM-DD HH24:MI:SS'), average_active_sessions FROM GV\$SYSMETRIC_HISTORY WHERE metric_name = 'Average Active Sessions' AND group_id = 2 ORDER BY begin_time DESC FETCH FIRST 12 ROWS ONLY;" # 6) DB Load Last 5 Minutes run_sql "Database Load Last 5 Minutes" " SELECT inst_id, TO_CHAR(begin_time, 'YYYY-MM-DD HH24:MI:SS'), average_active_sessions FROM GV\$SYSMETRIC_HISTORY WHERE metric_name = 'Average Active Sessions' AND group_id = 2 ORDER BY begin_time DESC FETCH FIRST 1 ROW ONLY;" # 7) DB Parallel Processing run_sql "Parallel Execution Processes" " SELECT inst_id, degree, req_degree, dop, requested_dop FROM GV\$PX_PROCESS;" # 8) Invalid Objects run_sql "Invalid Objects Across All Nodes" " SELECT inst_id, owner, object_name, object_type FROM GV\$DBA_OBJECTS WHERE status <> 'VALID';" # 9) Session Failures run_sql "Failed User Sessions" " SELECT inst_id, username, machine, terminal, logon_time FROM GV\$AUDIT_SESSION WHERE returncode != 0;" # 10) Active Session Count run_sql "Active Sessions Count per Instance" " SELECT inst_id, COUNT(*) AS active_sessions FROM GV\$SESSION WHERE status = 'ACTIVE' GROUP BY inst_id;" # 11) Database Service Info run_sql "Database Services Across All Instances" " SELECT inst_id, name, pdb FROM GV\$ACTIVE_SERVICES;" # 12) ORA-ERRORs in Alert Logs (Last 24 Hours) run_sql "Recent ORA-Errors in Alert Logs" " SELECT inst_id, originating_timestamp, message_text FROM GV\$DIAG_ALERT_EXT WHERE message_text LIKE 'ORA-%' AND originating_timestamp > SYSDATE - 1 ORDER BY originating_timestamp DESC;" # Close HTML Output echo "" >> $HTML_REPORT # Send Email with the HTML report mailx -a "$HTML_REPORT" -s "$EMAIL_SUBJECT" "$EMAIL_TO" < $HTML_REPORT echo "RAC Health Check Report Sent to $EMAIL_TO" ### #!/bin/bash set -eo pipefail # Configuration DB_USER="sys as sysdba" DB_PASSWORD="your_password" DB_HOST="rac-scan.example.com" DB_PORT=1521 SERVICE_NAME="ORCLCDB" EMAIL_TO="dba@example.com" REPORT_FILE="/tmp/rac_health_report.html" # Oracle Connection String CONN_STR="${DB_USER}/${DB_PASSWORD}@//${DB_HOST}:${DB_PORT}/${SERVICE_NAME}" # HTML Header cat > $REPORT_FILE <
Oracle RAC Health Report
No issues found" >> $REPORT_FILE
else
echo "" >> $REPORT_FILE
while IFS= read -r line; do
echo "
" >> $REPORT_FILE
fi
}
# 1. Instance/DB Status
run_sql "
SELECT 'Instance ' || instance_name || ' - ' || status ||
' | Version: ' || version || ' | Uptime: ' ||
ROUND((SYSDATE - startup_time)*24) || ' hours'
FROM gv\$instance;" "Cluster Instance Status"
# 2. Long Running Sessions (>30 minutes)
run_sql "
SELECT 'SID: ' || sid || ', Inst: ' || inst_id ||
', User: ' || username || ', SQL_ID: ' || sql_id ||
', Elapsed: ' || ROUND((SYSDATE - logon_time)*1440) || ' mins'
FROM gv\$session
WHERE status = 'ACTIVE' AND (SYSDATE - logon_time)*1440 > 30;" "Long Running Sessions" "warning"
# 3. DB Blocking/Locking
run_sql "
SELECT 'Blocker: ' || b.inst_id || ':' || b.sid ||
' | Waiter: ' || w.inst_id || ':' || w.sid ||
' | Object: ' || o.object_name ||
' | Wait: ' || ROUND((SYSDATE - w.logon_time)*1440) || ' mins'
FROM gv\$lock b, gv\$lock w, dba_objects o
WHERE b.block = 1 AND w.request > 0
AND b.id1 = w.id1 AND b.id2 = w.id2
AND o.object_id = b.id1;" "Blocking Sessions" "critical"
# 4. DB Load Analysis
run_sql "
SELECT 'Last Hour: CPU ' || ROUND(AVG(cpu_percent)) || '%' ||
' | Active: ' || AVG(active_sessions) ||
' | Parallel: ' || AVG(parallel_servers) ||
' | Last 5m: ' || ROUND(5min_cpu) || '%'
FROM (
SELECT h.metric_value cpu_percent,
(SELECT COUNT(*) FROM gv\$session WHERE status = 'ACTIVE') active_sessions,
(SELECT SUM(servers_in_use) FROM gv\$px_process_sysstat) parallel_servers,
LEAD(h.metric_value, 12) OVER (ORDER BY h.begin_time) 5min_cpu
FROM gv\$sysmetric_history h
WHERE h.metric_name = 'CPU Usage Per Sec'
AND h.begin_time > SYSDATE - 1/24
);" "Load Analysis"
# 5. Parallel Processes
run_sql "
SELECT 'Inst ' || inst_id ||
' | Used: ' || servers_in_use ||
' | Available: ' || servers_available ||
' | Status: ' || status
FROM gv\$px_process_sysstat;" "Parallel Execution Status"
# 6. Invalid Objects
run_sql "
SELECT owner || '.' || object_name || ' (' || object_type || ')'
FROM dba_objects
WHERE status != 'VALID' AND owner NOT IN ('SYS','SYSTEM');" "Invalid Objects" "warning"
# 7. Session Failures
run_sql "
SELECT 'Inst ' || inst_id ||
' | User: ' || username ||
' | Count: ' || COUNT(*) ||
' | Last: ' || MAX(timestamp)
FROM gv\$session
WHERE status = 'FAILED'
GROUP BY inst_id, username;" "Session Failures" "critical"
# 8. Active Session Count
run_sql "
SELECT 'Inst ' || inst_id ||
' | Active: ' || COUNT(*) ||
' | CPU: ' || SUM(CASE WHEN state = 'ON CPU' THEN 1 ELSE 0 END)
FROM gv\$session
WHERE status = 'ACTIVE'
GROUP BY inst_id;" "Active Sessions"
# 9. Database Services
run_sql "
SELECT name || ' | ' || status ||
' | Node: ' || node ||
' | Created: ' || TO_CHAR(creation_date, 'YYYY-MM-DD HH24:MI')
FROM gv\$services;" "Database Services"
# 10. ORA-Errors
run_sql "
SELECT 'Inst ' || inst_id ||
' | ' || TO_CHAR(timestamp, 'YYYY-MM-DD HH24:MI') ||
' | ' || REGEXP_SUBSTR(message_text, 'ORA-[0-9]+:.*')
FROM gv\$diag_alert_ext
WHERE message_type = 'ERROR'
AND timestamp > SYSDATE - 1;" "Recent ORA Errors" "critical"
# Complete HTML
cat >> $REPORT_FILE <
EOF
# Send Email
(
echo "From: Oracle Monitor "
echo "To: $EMAIL_TO"
echo "Subject: Oracle RAC Health Report"
echo "MIME-Version: 1.0"
echo "Content-Type: text/html"
echo
cat $REPORT_FILE
) | sendmail -t
# Cleanup
rm -f $REPORT_FILE
Oracle RAC Health Check Report
Generated on: $(date)
EOF # Run Database Checks sqlplus -s /nolog << EOF >> $REPORT_FILE connect $ORACLE_USER/$ORACLE_PASS@$RAC_NODE set markup html on pre off entmap off set pagesize 500 linesize 200 set feedback off heading on promptRecent ORA Errors (Last 5 Minutes)
SELECT INST_ID, TO_CHAR(ORIGINATING_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP, MESSAGE_TEXT FROM GV\$DIAG_ALERT_EXT WHERE MESSAGE_TEXT LIKE 'ORA-%' AND ORIGINATING_TIMESTAMP >= SYSDATE - 5/1440 ORDER BY ORIGINATING_TIMESTAMP DESC; promptTop Waiting Events (Last 5 Minutes)
SELECT INST_ID, EVENT, COUNT(*) AS WAIT_COUNT, ROUND(COUNT(*)*100/SUM(COUNT(*)) OVER(), 2) AS PCT FROM GV\$ACTIVE_SESSION_HISTORY WHERE SAMPLE_TIME >= SYSDATE - 5/1440 AND EVENT IS NOT NULL GROUP BY INST_ID, EVENT ORDER BY WAIT_COUNT DESC; exit EOF # Add HTML Footer cat << EOF >> $REPORT_FILE EOF # Send Email with HTML Report mailx -s "$(echo -e "Oracle RAC Health Check Report\nContent-Type: text/html")" \ $EMAIL_RECIPIENT < $REPORT_FILE # Cleanup rm -f $REPORT_FILE #### #!/bin/bash set -eo pipefail trap 'echo "Error at line $LINENO"; exit 1' ERR # Configuration REMOTE_USER="oracleuser" REMOTE_HOSTS=("rac-node1" "rac-node2") # Multiple RAC nodes ORACLE_SID="ORCLCDB" ORACLE_HOME="/u01/app/oracle/product/19.0.0/dbhome_1" EMAIL_RECIPIENT="dba@example.com" EMAIL_SUBJECT="Oracle RAC Health Check Report - $(date +%Y%m%d)" REPORT_FILE="/tmp/rac_health_report.html" LOG_FILE="/tmp/rac_health_check.log" AWK_SCRIPT="/tmp/rac_stats.awk" declare -A THRESHOLDS=( [TABLESPACE]=90 [WAIT_TIME]=1000 [CPU_PER_SESSION]=75 [ASM_FREE]=10 ) # Initialize files : > "$REPORT_FILE" : > "$LOG_FILE" # Create HTML template cat >> "$REPORT_FILE" << EOFOracle RAC Health Check Report
Generated on: $(date "+%Y-%m-%d %H:%M:%S")
EOF # Function to run remote SQL run_remote_sql() { local host=$1 local sql=$2 ssh -q "$REMOTE_USER@$host" " export ORACLE_SID=$ORACLE_SID export ORACLE_HOME=$ORACLE_HOME \$ORACLE_HOME/bin/sqlplus -S / as sysdba << SQLEND SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF $sql SQLEND" } # Cluster Health Check echo "► Cluster Health
" >> "$REPORT_FILE" echo "" >> "$REPORT_FILE" # AWR/ASH Report Generation echo "► AWR/ASH Reports
" >> "$REPORT_FILE" echo "" >> "$REPORT_FILE" # Historical Trend Analysis echo "► Historical Trends
" >> "$REPORT_FILE" echo "" >> "$REPORT_FILE" # Backup Status Check echo "► Backup Status
" >> "$REPORT_FILE" echo "" >> "$REPORT_FILE" # JavaScript for collapsible sections cat >> "$REPORT_FILE" << EOF EOF # Send email with mailx ( echo "From: Oracle Health CheckOracle RAC Database Health Check Report
" >> $HTML_REPORT echo "" >> $HTML_REPORT # Function to run SQL and format output as HTML run_sql() { local title=$1 local sql_query=$2 echo "
$title
" >> $HTML_REPORT echo "" >> $HTML_REPORT
sqlplus -s "$DB_USER/$DB_PASS@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=$DB_HOST)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=$DB_SERVICE)))" <> $HTML_REPORT
SET LINESIZE 200
SET PAGESIZE 100
SET TRIMSPOOL ON
SET HEADING OFF
$sql_query
EXIT;
EOF
echo " " >> $HTML_REPORT } # 1) All Instance / DB Status run_sql "RAC Instance and Database Status" "SELECT inst_id, instance_name, status, database_status FROM GV\$INSTANCE;" # 2) Long Running Sessions (active > 1 hour) run_sql "Long Running Sessions (per instance)" " SELECT inst_id, sid, serial#, username, status, sql_id, last_call_et FROM GV\$SESSION WHERE status='ACTIVE' AND last_call_et > 3600 ORDER BY last_call_et DESC;" # 3) DB Blocking run_sql "Blocking Sessions Across Instances" " SELECT inst_id, blocking_session, sid, serial#, wait_class, seconds_in_wait FROM GV\$SESSION WHERE blocking_session IS NOT NULL;" # 4) DB Locking run_sql "Database Locks" " SELECT inst_id, sid, type, id1, id2, lmode, request, block FROM GV\$LOCK WHERE block != 0;" # 5) DB Load Last 1 Hour run_sql "Database Load Last 1 Hour" " SELECT inst_id, TO_CHAR(begin_time, 'YYYY-MM-DD HH24:MI:SS'), average_active_sessions FROM GV\$SYSMETRIC_HISTORY WHERE metric_name = 'Average Active Sessions' AND group_id = 2 ORDER BY begin_time DESC FETCH FIRST 12 ROWS ONLY;" # 6) DB Load Last 5 Minutes run_sql "Database Load Last 5 Minutes" " SELECT inst_id, TO_CHAR(begin_time, 'YYYY-MM-DD HH24:MI:SS'), average_active_sessions FROM GV\$SYSMETRIC_HISTORY WHERE metric_name = 'Average Active Sessions' AND group_id = 2 ORDER BY begin_time DESC FETCH FIRST 1 ROW ONLY;" # 7) DB Parallel Processing run_sql "Parallel Execution Processes" " SELECT inst_id, degree, req_degree, dop, requested_dop FROM GV\$PX_PROCESS;" # 8) Invalid Objects run_sql "Invalid Objects Across All Nodes" " SELECT inst_id, owner, object_name, object_type FROM GV\$DBA_OBJECTS WHERE status <> 'VALID';" # 9) Session Failures run_sql "Failed User Sessions" " SELECT inst_id, username, machine, terminal, logon_time FROM GV\$AUDIT_SESSION WHERE returncode != 0;" # 10) Active Session Count run_sql "Active Sessions Count per Instance" " SELECT inst_id, COUNT(*) AS active_sessions FROM GV\$SESSION WHERE status = 'ACTIVE' GROUP BY inst_id;" # 11) Database Service Info run_sql "Database Services Across All Instances" " SELECT inst_id, name, pdb FROM GV\$ACTIVE_SERVICES;" # 12) ORA-ERRORs in Alert Logs (Last 24 Hours) run_sql "Recent ORA-Errors in Alert Logs" " SELECT inst_id, originating_timestamp, message_text FROM GV\$DIAG_ALERT_EXT WHERE message_text LIKE 'ORA-%' AND originating_timestamp > SYSDATE - 1 ORDER BY originating_timestamp DESC;" # Close HTML Output echo "" >> $HTML_REPORT # Send Email with the HTML report mailx -a "$HTML_REPORT" -s "$EMAIL_SUBJECT" "$EMAIL_TO" < $HTML_REPORT echo "RAC Health Check Report Sent to $EMAIL_TO" ### #!/bin/bash set -eo pipefail # Configuration DB_USER="sys as sysdba" DB_PASSWORD="your_password" DB_HOST="rac-scan.example.com" DB_PORT=1521 SERVICE_NAME="ORCLCDB" EMAIL_TO="dba@example.com" REPORT_FILE="/tmp/rac_health_report.html" # Oracle Connection String CONN_STR="${DB_USER}/${DB_PASSWORD}@//${DB_HOST}:${DB_PORT}/${SERVICE_NAME}" # HTML Header cat > $REPORT_FILE <
Oracle RAC Health & Performance Report
Generated: $(date "+%Y-%m-%d %H:%M:%S")
EOF # Function to run SQL and format output run_sql() { local sql=$1 local title=$2 local critical=$3 echo "$title
" >> $REPORT_FILE output=$(sqlplus -S -L "${CONN_STR}" <| $line |
Subscribe to:
Posts (Atom)