| " $0 " |
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
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 |
Tuesday, January 28, 2025
#!/bin/bash
# Configuration
INPUT_FILE="servers.txt"
HTML_REPORT="health_report_$(date +%Y%m%d_%H%M%S).html"
SSH_USER=$(whoami)
SSH_KEY="$HOME/.ssh/id_rsa"
SSH_TIMEOUT=10
# Email Configuration
EMAIL_ENABLED=1
EMAIL_RECIPIENT="admin@example.com"
EMAIL_SENDER="monitor@example.com"
EMAIL_SUBJECT="Server Health Report - $(date +%F)"
# Thresholds
CPU_CRITICAL=90
CPU_WARNING=70
MEM_CRITICAL=90
MEM_WARNING=70
DISK_CRITICAL=90
DISK_WARNING=80
# Colors
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m'
# Initialize counters
TOTAL_CRITICAL=0
TOTAL_WARNING=0
validate_environment() {
# Check SSH key pair
if [ ! -f "$SSH_KEY" ]; then
echo -e "${RED}Error: SSH key not found at $SSH_KEY${NC}"
echo "Generate SSH key pair with:"
echo " ssh-keygen -t rsa -b 4096 -N '' -f $SSH_KEY"
echo "Then manually copy public key to servers using:"
echo " ssh-copy-id -i $SSH_KEY.pub ${SSH_USER}@server"
exit 1
fi
# Check server list
if [ ! -s "$INPUT_FILE" ]; then
echo -e "${RED}Error: Server list file $INPUT_FILE not found or empty${NC}"
exit 1
fi
}
initialize_html() {
cat < "$HTML_REPORT"
Server Health Report
" ((critical++)) elif [ $usage -ge $DISK_WARNING ]; then disk_status+="$device@$mount: ${usage}%
" ((warning++)) else disk_status+="$device@$mount: ${usage}%
" fi done <<< "$disk_usage" # Service Analysis local service_status="All services up" if [ $services_down -gt 0 ]; then service_status="$services_down services down" ((critical++)) fi # Update Analysis local update_status="Up to date" if [ $updates_available -gt 0 ]; then update_status="$updates_available updates" ((warning++)) fi # Security Analysis local security_issues=() local security_status="Secure" if [ "$selinux_status" != "Enforcing" ]; then security_issues+=("SELinux: $selinux_status") fi if [ "$firewall_status" != "running" ]; then security_issues+=("Firewall: $firewall_status") fi if [ ${#security_issues[@]} -gt 0 ]; then security_status="$(IFS='
'; echo "${security_issues[*]}")" ((critical++)) fi # Update global counters TOTAL_CRITICAL=$((TOTAL_CRITICAL + critical)) TOTAL_WARNING=$((TOTAL_WARNING + warning)) # Determine overall status if [ $critical -gt 0 ]; then status="CRITICAL" elif [ $warning -gt 0 ]; then status="WARNING" else status="OK" fi add_html_row "$server" "$status" "$cpu_status" "$mem_status" "$disk_status" "$service_status" "$update_status" "$security_status" } send_email() { if [ $EMAIL_ENABLED -eq 0 ]; then return fi if ! command -v mailx &> /dev/null; then echo -e "${YELLOW}mailx not installed. Email report disabled.${NC}" return fi echo -e "\n${YELLOW}Sending email report to $EMAIL_RECIPIENT...${NC}" ( echo "From: $EMAIL_SENDER" echo "To: $EMAIL_RECIPIENT" echo "Subject: $EMAIL_SUBJECT" echo "MIME-Version: 1.0" echo "Content-Type: text/html; charset=UTF-8" echo cat "$HTML_REPORT" ) | mailx -t if [ $? -eq 0 ]; then echo -e "${GREEN}Email sent successfully!${NC}" else echo -e "${RED}Failed to send email!${NC}" fi } # Main execution validate_environment initialize_html while IFS= read -r server; do [[ -z "$server" || "$server" == \#* ]] && continue check_server_health "$server" done < "$INPUT_FILE" finalize_html send_email echo -e "\n${GREEN}Report generated: ${HTML_REPORT}${NC}"
Server Health Report - $(date)
| Server | Status | CPU Load | Memory | Disk | Services | Updates | Security |
|---|---|---|---|---|---|---|---|
| $server | $status | $cpu | $mem | $disk | $services | $updates | $security |
Summary
Total Critical Issues: $TOTAL_CRITICAL
Total Warnings: $TOTAL_WARNING
HTML_FOOT } check_server_health() { local server=$1 echo -e "\nChecking ${GREEN}$server${NC}" # Test SSH connection if ! ssh -i "$SSH_KEY" -o ConnectTimeout=$SSH_TIMEOUT -o BatchMode=yes "$SSH_USER@$server" true 2>/dev/null; then add_html_row "$server" "Offline" "" "" "" "" "" echo -e "${RED} ➔ Offline${NC}" return fi # Get health data local health_data=$(ssh -i "$SSH_KEY" -T "$SSH_USER@$server" <<'EOF' { # CPU cpu_cores=$(nproc) load_avg=$(awk '{print $1,$2,$3}' /proc/loadavg) load1=$(awk '{print $1}' /proc/loadavg) # Memory mem_usage=$(free -m | awk '/Mem:/{printf "%.1f", $3/$2*100}') # Disk disk_usage=$(df -h | awk '/^\/dev/{print $1"|"$5"|"$6}' | head -3) # Services services_down=$(systemctl is-active sshd crond firewalld auditd 2>/dev/null | grep -c inactive) # Updates updates_available=$(dnf check-update -q | wc -l) # Security selinux_status=$(getenforce) firewall_status=$(firewall-cmd --state 2>&1) echo -n "{" echo -n "\"cpu_cores\":$cpu_cores," echo -n "\"load_avg\":\"$load_avg\"," echo -n "\"load1\":$load1," echo -n "\"mem_usage\":$mem_usage," echo -n "\"disk_usage\":\"$disk_usage\"," echo -n "\"services_down\":$services_down," echo -n "\"updates_available\":$updates_available," echo -n "\"selinux_status\":\"$selinux_status\"," echo -n "\"firewall_status\":\"$firewall_status\"" echo "}" } EOF ) # Parse JSON data local cpu_cores=$(jq -r '.cpu_cores' <<< "$health_data") local load_avg=$(jq -r '.load_avg' <<< "$health_data") local load1=$(jq -r '.load1' <<< "$health_data") local mem_usage=$(jq -r '.mem_usage' <<< "$health_data") local disk_usage=$(jq -r '.disk_usage' <<< "$health_data") local services_down=$(jq -r '.services_down' <<< "$health_data") local updates_available=$(jq -r '.updates_available' <<< "$health_data") local selinux_status=$(jq -r '.selinux_status' <<< "$health_data") local firewall_status=$(jq -r '.firewall_status' <<< "$health_data") # Analyze metrics local status="OK" local critical=0 local warning=0 # CPU Analysis local load_pct=$(awk -v cores="$cpu_cores" -v load="$load1" 'BEGIN {printf "%.0f", (load/cores)*100}') local cpu_status="Normal" if [ $load_pct -ge $CPU_CRITICAL ]; then cpu_status="CRITICAL ($load_pct%)" ((critical++)) elif [ $load_pct -ge $CPU_WARNING ]; then cpu_status="WARNING ($load_pct%)" ((warning++)) else cpu_status="$load_avg" fi # Memory Analysis local mem_status="Normal" if [ $(echo "$mem_usage >= $MEM_CRITICAL" | bc) -eq 1 ]; then mem_status="CRITICAL (${mem_usage}%)" ((critical++)) elif [ $(echo "$mem_usage >= $MEM_WARNING" | bc) -eq 1 ]; then mem_status="WARNING (${mem_usage}%)" ((warning++)) else mem_status="${mem_usage}%" fi # Disk Analysis local disk_status="" while IFS='|' read -r device usage mount; do usage=${usage%\%} if [ $usage -ge $DISK_CRITICAL ]; then disk_status+="$device@$mount: ${usage}%" ((critical++)) elif [ $usage -ge $DISK_WARNING ]; then disk_status+="$device@$mount: ${usage}%
" ((warning++)) else disk_status+="$device@$mount: ${usage}%
" fi done <<< "$disk_usage" # Service Analysis local service_status="All services up" if [ $services_down -gt 0 ]; then service_status="$services_down services down" ((critical++)) fi # Update Analysis local update_status="Up to date" if [ $updates_available -gt 0 ]; then update_status="$updates_available updates" ((warning++)) fi # Security Analysis local security_issues=() local security_status="Secure" if [ "$selinux_status" != "Enforcing" ]; then security_issues+=("SELinux: $selinux_status") fi if [ "$firewall_status" != "running" ]; then security_issues+=("Firewall: $firewall_status") fi if [ ${#security_issues[@]} -gt 0 ]; then security_status="$(IFS='
'; echo "${security_issues[*]}")" ((critical++)) fi # Update global counters TOTAL_CRITICAL=$((TOTAL_CRITICAL + critical)) TOTAL_WARNING=$((TOTAL_WARNING + warning)) # Determine overall status if [ $critical -gt 0 ]; then status="CRITICAL" elif [ $warning -gt 0 ]; then status="WARNING" else status="OK" fi add_html_row "$server" "$status" "$cpu_status" "$mem_status" "$disk_status" "$service_status" "$update_status" "$security_status" } send_email() { if [ $EMAIL_ENABLED -eq 0 ]; then return fi if ! command -v mailx &> /dev/null; then echo -e "${YELLOW}mailx not installed. Email report disabled.${NC}" return fi echo -e "\n${YELLOW}Sending email report to $EMAIL_RECIPIENT...${NC}" ( echo "From: $EMAIL_SENDER" echo "To: $EMAIL_RECIPIENT" echo "Subject: $EMAIL_SUBJECT" echo "MIME-Version: 1.0" echo "Content-Type: text/html; charset=UTF-8" echo cat "$HTML_REPORT" ) | mailx -t if [ $? -eq 0 ]; then echo -e "${GREEN}Email sent successfully!${NC}" else echo -e "${RED}Failed to send email!${NC}" fi } # Main execution validate_environment initialize_html while IFS= read -r server; do [[ -z "$server" || "$server" == \#* ]] && continue check_server_health "$server" done < "$INPUT_FILE" finalize_html send_email echo -e "\n${GREEN}Report generated: ${HTML_REPORT}${NC}"
Sunday, January 19, 2025
------
SET LONG 100000
SET PAGESIZE 1000
SET SERVEROUTPUT ON;
DECLARE
v_ddl CLOB;
v_modified_ddl CLOB;
v_index_name VARCHAR2(100);
BEGIN
DBMS_OUTPUT.PUT_LINE('Extracting and modifying DDL for all indexes under schema: ');
-- Extract DDL for regular and partitioned indexes
FOR i IN (
SELECT index_name
FROM dba_indexes
WHERE owner = UPPER('')
) LOOP
BEGIN
v_index_name := i.index_name;
-- Get the DDL for the index
v_ddl := DBMS_METADATA.GET_DDL('INDEX', v_index_name, '');
-- Modify the parallel degree in the DDL
IF INSTR(UPPER(v_ddl), 'PARALLEL') > 0 THEN
-- If PARALLEL clause exists, replace it with PARALLEL 2
v_modified_ddl := REGEXP_REPLACE(v_ddl, 'PARALLEL\s+\d+', 'PARALLEL 2', 1, 0, 'i');
ELSE
-- If no PARALLEL clause, add PARALLEL 2 at the end of the DDL
v_modified_ddl := v_ddl || CHR(10) || ' PARALLEL 2';
END IF;
-- Append the ALTER INDEX statement to set parallel degree back to 1
v_modified_ddl := v_modified_ddl || CHR(10) || 'ALTER INDEX .' || v_index_name || ' PARALLEL 1;' || CHR(10);
-- Print the modified DDL
DBMS_OUTPUT.PUT_LINE(v_modified_ddl || '----------------------------------------');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error extracting DDL for index: ' || v_index_name || ' - ' || SQLERRM);
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE('DDL extraction and modification completed.');
END;
/
------------------
SET SERVEROUTPUT ON;
DECLARE
v_sql VARCHAR2(1000);
BEGIN
DBMS_OUTPUT.PUT_LINE('Rebuilding Regular Indexes with Parallel Degree 2...');
-- Rebuild regular table indexes with parallel degree 2
FOR i IN (
SELECT index_name, table_name
FROM dba_indexes
WHERE owner = UPPER('')
AND partitioned = 'NO'
) LOOP
BEGIN
v_sql := 'ALTER INDEX ' || '' || '.' || i.index_name || ' REBUILD PARALLEL 2';
EXECUTE IMMEDIATE v_sql;
DBMS_OUTPUT.PUT_LINE('Rebuilt Index: ' || i.index_name || ' on Table: ' || i.table_name || ' with Parallel Degree 2');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error rebuilding index: ' || i.index_name || ' - ' || SQLERRM);
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Rebuilding Partitioned Indexes with Parallel Degree 2...');
-- Rebuild partitioned table indexes with parallel degree 2
FOR i IN (
SELECT index_name, table_name, partition_name
FROM dba_ind_partitions
WHERE index_owner = UPPER('')
) LOOP
BEGIN
v_sql := 'ALTER INDEX ' || '' || '.' || i.index_name || ' REBUILD PARTITION ' || i.partition_name || ' PARALLEL 2';
EXECUTE IMMEDIATE v_sql;
DBMS_OUTPUT.PUT_LINE('Rebuilt Partitioned Index: ' || i.index_name || ' Partition: ' || i.partition_name || ' on Table: ' || i.table_name || ' with Parallel Degree 2');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error rebuilding partitioned index: ' || i.index_name || ' Partition: ' || i.partition_name || ' - ' || SQLERRM);
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Reverting Regular Indexes to Parallel Degree 1...');
-- Revert parallel degree for regular indexes
FOR i IN (
SELECT index_name, table_name
FROM dba_indexes
WHERE owner = UPPER('')
AND partitioned = 'NO'
) LOOP
BEGIN
v_sql := 'ALTER INDEX ' || '' || '.' || i.index_name || ' PARALLEL 1';
EXECUTE IMMEDIATE v_sql;
DBMS_OUTPUT.PUT_LINE('Reverted Parallel Degree to 1 for Index: ' || i.index_name || ' on Table: ' || i.table_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error reverting parallel degree for index: ' || i.index_name || ' - ' || SQLERRM);
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Reverting Partitioned Indexes to Parallel Degree 1...');
-- Revert parallel degree for partitioned indexes
FOR i IN (
SELECT DISTINCT index_name
FROM dba_ind_partitions
WHERE index_owner = UPPER('')
) LOOP
BEGIN
v_sql := 'ALTER INDEX ' || '' || '.' || i.index_name || ' PARALLEL 1';
EXECUTE IMMEDIATE v_sql;
DBMS_OUTPUT.PUT_LINE('Reverted Parallel Degree to 1 for Partitioned Index: ' || i.index_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error reverting parallel degree for partitioned index: ' || i.index_name || ' - ' || SQLERRM);
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Index rebuilding and parallel degree reversion complete.');
END;
/
---------------------------------------------------------------------------
SET SERVEROUTPUT ON;
DECLARE
v_sql VARCHAR2(1000);
v_initial_count NUMBER;
v_final_count NUMBER;
v_confirmation VARCHAR2(3);
-- Procedure to count objects in the schema
PROCEDURE count_objects(out_count OUT NUMBER) IS
BEGIN
SELECT COUNT(*)
INTO out_count
FROM dba_objects
WHERE owner = UPPER('');
END;
BEGIN
-- Prompt for confirmation
DBMS_OUTPUT.PUT_LINE('Are you sure you want to drop all objects under schema ? (YES/NO)');
DBMS_OUTPUT.PUT_LINE('Enter your confirmation:');
v_confirmation := UPPER('&CONFIRMATION');
IF v_confirmation != 'YES' THEN
DBMS_OUTPUT.PUT_LINE('Operation cancelled by the user.');
RETURN;
END IF;
-- Count total objects before dropping
count_objects(v_initial_count);
DBMS_OUTPUT.PUT_LINE('Total objects before dropping: ' || v_initial_count);
-- Drop tables
FOR t IN (SELECT object_name
FROM dba_objects
WHERE owner = UPPER('')
AND object_type = 'TABLE') LOOP
BEGIN
v_sql := 'DROP TABLE ' || '' || '.' || t.object_name || ' CASCADE CONSTRAINTS';
EXECUTE IMMEDIATE v_sql;
DBMS_OUTPUT.PUT_LINE('Dropped Table: ' || t.object_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error dropping table: ' || t.object_name || ' - ' || SQLERRM);
END;
END LOOP;
-- Drop materialized views
FOR mv IN (SELECT object_name
FROM dba_objects
WHERE owner = UPPER('')
AND object_type = 'MATERIALIZED VIEW') LOOP
BEGIN
v_sql := 'DROP MATERIALIZED VIEW ' || '' || '.' || mv.object_name;
EXECUTE IMMEDIATE v_sql;
DBMS_OUTPUT.PUT_LINE('Dropped Materialized View: ' || mv.object_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error dropping materialized view: ' || mv.object_name || ' - ' || SQLERRM);
END;
END LOOP;
-- Drop views
FOR v IN (SELECT object_name
FROM dba_objects
WHERE owner = UPPER('')
AND object_type = 'VIEW') LOOP
BEGIN
v_sql := 'DROP VIEW ' || '' || '.' || v.object_name;
EXECUTE IMMEDIATE v_sql;
DBMS_OUTPUT.PUT_LINE('Dropped View: ' || v.object_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error dropping view: ' || v.object_name || ' - ' || SQLERRM);
END;
END LOOP;
-- Drop sequences
FOR s IN (SELECT object_name
FROM dba_objects
WHERE owner = UPPER('')
AND object_type = 'SEQUENCE') LOOP
BEGIN
v_sql := 'DROP SEQUENCE ' || '' || '.' || s.object_name;
EXECUTE IMMEDIATE v_sql;
DBMS_OUTPUT.PUT_LINE('Dropped Sequence: ' || s.object_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error dropping sequence: ' || s.object_name || ' - ' || SQLERRM);
END;
END LOOP;
-- Drop procedures, functions, and packages
FOR p IN (SELECT object_name, object_type
FROM dba_objects
WHERE owner = UPPER('')
AND object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE')) LOOP
BEGIN
v_sql := 'DROP ' || p.object_type || ' ' || '' || '.' || p.object_name;
EXECUTE IMMEDIATE v_sql;
DBMS_OUTPUT.PUT_LINE('Dropped ' || p.object_type || ': ' || p.object_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error dropping ' || p.object_type || ': ' || p.object_name || ' - ' || SQLERRM);
END;
END LOOP;
-- Drop indexes
FOR i IN (SELECT index_name
FROM dba_indexes
WHERE owner = UPPER('')) LOOP
BEGIN
v_sql := 'DROP INDEX ' || '' || '.' || i.index_name;
EXECUTE IMMEDIATE v_sql;
DBMS_OUTPUT.PUT_LINE('Dropped Index: ' || i.index_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error dropping index: ' || i.index_name || ' - ' || SQLERRM);
END;
END LOOP;
-- Drop synonyms
FOR syn IN (SELECT synonym_name
FROM dba_synonyms
WHERE owner = UPPER('')) LOOP
BEGIN
v_sql := 'DROP SYNONYM ' || '' || '.' || syn.synonym_name;
EXECUTE IMMEDIATE v_sql;
DBMS_OUTPUT.PUT_LINE('Dropped Synonym: ' || syn.synonym_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error dropping synonym: ' || syn.synonym_name || ' - ' || SQLERRM);
END;
END LOOP;
-- Count total objects after dropping
count_objects(v_final_count);
DBMS_OUTPUT.PUT_LINE('Total objects after dropping: ' || v_final_count);
-- Ensure cleanup is complete
IF v_final_count = 0 THEN
DBMS_OUTPUT.PUT_LINE('All objects have been successfully dropped.');
ELSE
DBMS_OUTPUT.PUT_LINE('Some objects were not dropped. Review the log above for details.');
END IF;
END;
/
----------------------------------------------------------------------------------------------
SET SERVEROUTPUT ON;
DECLARE
v_diff_found BOOLEAN := FALSE;
v_source_count NUMBER;
v_target_count NUMBER;
-- Procedure to display differences
PROCEDURE log_difference(obj_type IN VARCHAR2, obj_name IN VARCHAR2, msg IN VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Difference in ' || obj_type || ': ' || obj_name || ' - ' || msg);
v_diff_found := TRUE;
END;
BEGIN
DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('Comparing Schema Objects...');
DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------');
-- Compare tables (object existence)
FOR t IN (
SELECT object_name
FROM dba_objects@
WHERE owner = UPPER('')
AND object_type = 'TABLE'
MINUS
SELECT object_name
FROM dba_objects
WHERE owner = UPPER('')
AND object_type = 'TABLE'
) LOOP
log_difference('Table', t.object_name, 'Exists in source but not in target');
END LOOP;
FOR t IN (
SELECT object_name
FROM dba_objects
WHERE owner = UPPER('')
AND object_type = 'TABLE'
MINUS
SELECT object_name
FROM dba_objects@
WHERE owner = UPPER('')
AND object_type = 'TABLE'
) LOOP
log_difference('Table', t.object_name, 'Exists in target but not in source');
END LOOP;
-- Compare row counts for tables
DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('Comparing Table Row Counts...');
DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------');
FOR t IN (
SELECT table_name
FROM dba_tables@
WHERE owner = UPPER('')
) LOOP
BEGIN
-- Get row count for source table
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || '' || '.' || t.table_name@ INTO v_source_count;
-- Get row count for target table
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || '' || '.' || t.table_name INTO v_target_count;
-- Compare row counts
IF v_source_count != v_target_count THEN
log_difference('Table Row Count', t.table_name, 'Source Count = ' || v_source_count || ', Target Count = ' || v_target_count);
END IF;
EXCEPTION
WHEN OTHERS THEN
log_difference('Table Row Count', t.table_name, 'Error retrieving row counts (check if table exists in both schemas)');
END;
END LOOP;
-- Compare views
DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('Comparing Views...');
DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------');
FOR v IN (
SELECT object_name
FROM dba_objects@
WHERE owner = UPPER('')
AND object_type = 'VIEW'
MINUS
SELECT object_name
FROM dba_objects
WHERE owner = UPPER('')
AND object_type = 'VIEW'
) LOOP
log_difference('View', v.object_name, 'Exists in source but not in target');
END LOOP;
FOR v IN (
SELECT object_name
FROM dba_objects
WHERE owner = UPPER('')
AND object_type = 'VIEW'
MINUS
SELECT object_name
FROM dba_objects@
WHERE owner = UPPER('')
AND object_type = 'VIEW'
) LOOP
log_difference('View', v.object_name, 'Exists in target but not in source');
END LOOP;
-- Compare procedures
DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('Comparing Procedures...');
DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------');
FOR p IN (
SELECT object_name
FROM dba_objects@
WHERE owner = UPPER('')
AND object_type = 'PROCEDURE'
MINUS
SELECT object_name
FROM dba_objects
WHERE owner = UPPER('')
AND object_type = 'PROCEDURE'
) LOOP
log_difference('Procedure', p.object_name, 'Exists in source but not in target');
END LOOP;
FOR p IN (
SELECT object_name
FROM dba_objects
WHERE owner = UPPER('')
AND object_type = 'PROCEDURE'
MINUS
SELECT object_name
FROM dba_objects@
WHERE owner = UPPER('')
AND object_type = 'PROCEDURE'
) LOOP
log_difference('Procedure', p.object_name, 'Exists in target but not in source');
END LOOP;
-- Summary
DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------');
IF NOT v_diff_found THEN
DBMS_OUTPUT.PUT_LINE('No differences found between the two schemas.');
ELSE
DBMS_OUTPUT.PUT_LINE('Differences found. Review the above log for details.');
END IF;
END;
/
Sunday, August 18, 2024
--
-- Usage:
-- @dash_wait_chains
--
-- Example:
-- @dash_wait_chains username||':'||program2||event2 session_type='FOREGROUND' sysdate-1 sysdate
--
-- Other:
-- This script uses only the DBA_HIST_ACTIVE_SESS_HISTORY view, use
-- @ash_wait_chains.sql for accessiong the GV$ ASH view for realtime info
--
--------------------------------------------------------------------------------
COL wait_chain FOR A300 WORD_WRAP
COL "%This" FOR A6
PROMPT
PROMPT -- Display ASH Wait Chain Signatures script v0.8 by Tanel Poder ( https://tanelpoder.com )
WITH
bclass AS (SELECT /*+ INLINE */ class, ROWNUM r from v$waitstat),
ash AS (SELECT /*+ INLINE QB_NAME(ash) LEADING(a) USE_HASH(u) SWAP_JOIN_INPUTS(u) */
a.*
, o.*
, SUBSTR(TO_CHAR(a.sample_time, 'YYYYMMDDHH24MISS'),1,13) sample_time_10s -- ASH dba_hist_ samples stored every 10sec
, u.username
, CASE WHEN a.session_type = 'BACKGROUND' OR REGEXP_LIKE(a.program, '.*\([PJ]\d+\)') THEN
REGEXP_REPLACE(SUBSTR(a.program,INSTR(a.program,'(')), '\d', 'n')
ELSE
'('||REGEXP_REPLACE(REGEXP_REPLACE(a.program, '(.*)@(.*)(\(.*\))', '\1'), '\d', 'n')||')'
END || ' ' program2
, NVL(a.event||CASE WHEN event like 'enq%' AND session_state = 'WAITING'
THEN ' [mode='||BITAND(p1, POWER(2,14)-1)||']'
WHEN a.event IN (SELECT name FROM v$event_name WHERE parameter3 = 'class#')
THEN ' ['||NVL((SELECT class FROM bclass WHERE r = a.p3),'undo @bclass '||a.p3)||']' ELSE null END,'ON CPU')
|| ' ' event2
, TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p1 ELSE null END, '0XXXXXXXXXXXXXXX') p1hex
, TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p2 ELSE null END, '0XXXXXXXXXXXXXXX') p2hex
, TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p3 ELSE null END, '0XXXXXXXXXXXXXXX') p3hex
, CASE WHEN BITAND(time_model, POWER(2, 01)) = POWER(2, 01) THEN 'DBTIME ' END
||CASE WHEN BITAND(time_model, POWER(2, 02)) = POWER(2, 02) THEN 'BACKGROUND ' END
||CASE WHEN BITAND(time_model, POWER(2, 03)) = POWER(2, 03) THEN 'CONNECTION_MGMT ' END
||CASE WHEN BITAND(time_model, POWER(2, 04)) = POWER(2, 04) THEN 'PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 05)) = POWER(2, 05) THEN 'FAILED_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 06)) = POWER(2, 06) THEN 'NOMEM_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 07)) = POWER(2, 07) THEN 'HARD_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 08)) = POWER(2, 08) THEN 'NO_SHARERS_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 09)) = POWER(2, 09) THEN 'BIND_MISMATCH_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 10)) = POWER(2, 10) THEN 'SQL_EXECUTION ' END
||CASE WHEN BITAND(time_model, POWER(2, 11)) = POWER(2, 11) THEN 'PLSQL_EXECUTION ' END
||CASE WHEN BITAND(time_model, POWER(2, 12)) = POWER(2, 12) THEN 'PLSQL_RPC ' END
||CASE WHEN BITAND(time_model, POWER(2, 13)) = POWER(2, 13) THEN 'PLSQL_COMPILATION ' END
||CASE WHEN BITAND(time_model, POWER(2, 14)) = POWER(2, 14) THEN 'JAVA_EXECUTION ' END
||CASE WHEN BITAND(time_model, POWER(2, 15)) = POWER(2, 15) THEN 'BIND ' END
||CASE WHEN BITAND(time_model, POWER(2, 16)) = POWER(2, 16) THEN 'CURSOR_CLOSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 17)) = POWER(2, 17) THEN 'SEQUENCE_LOAD ' END
||CASE WHEN BITAND(time_model, POWER(2, 18)) = POWER(2, 18) THEN 'INMEMORY_QUERY ' END
||CASE WHEN BITAND(time_model, POWER(2, 19)) = POWER(2, 19) THEN 'INMEMORY_POPULATE ' END
||CASE WHEN BITAND(time_model, POWER(2, 20)) = POWER(2, 20) THEN 'INMEMORY_PREPOPULATE ' END
||CASE WHEN BITAND(time_model, POWER(2, 21)) = POWER(2, 21) THEN 'INMEMORY_REPOPULATE ' END
||CASE WHEN BITAND(time_model, POWER(2, 22)) = POWER(2, 22) THEN 'INMEMORY_TREPOPULATE ' END
||CASE WHEN BITAND(time_model, POWER(2, 23)) = POWER(2, 23) THEN 'TABLESPACE_ENCRYPTION ' END time_model_name
FROM
dba_hist_active_sess_history a
, dba_users u
, (SELECT
object_id,data_object_id,owner,object_name,subobject_name,object_type
, owner||'.'||object_name obj
, owner||'.'||object_name||' ['||object_type||']' objt
FROM dba_objects) o
WHERE
a.user_id = u.user_id (+)
AND a.current_obj# = o.object_id(+)
AND sample_time BETWEEN &3 AND &4
),
ash_samples AS (SELECT /*+ INLINE */ DISTINCT sample_time_10s FROM ash),
ash_data AS (SELECT /*+ INLINE */ * FROM ash),
chains AS (
SELECT /*+ INLINE */
d.sample_time_10s ts
, level lvl
, session_id sid
, REPLACE(SYS_CONNECT_BY_PATH(&1, '->'), '->', ' -> ')||CASE WHEN CONNECT_BY_ISLEAF = 1 AND d.blocking_session IS NOT NULL THEN ' -> [idle blocker '||d.blocking_inst_id||','||d.blocking_session||','||d.blocking_session_serial#||(SELECT ' ('||s.program||')' FROM gv$session s WHERE (s.inst_id, s.sid , s.serial#) = ((d.blocking_inst_id,d.blocking_session,d.blocking_session_serial#)))||']' ELSE NULL END path -- there's a reason why I'm doing this
--, REPLACE(SYS_CONNECT_BY_PATH(&1, '->'), '->', ' -> ') path -- there's a reason why I'm doing this (ORA-30004 :)
--, SYS_CONNECT_BY_PATH(&1, ' -> ')||CASE WHEN CONNECT_BY_ISLEAF = 1 THEN '('||d.session_id||')' ELSE NULL END path
--, REPLACE(SYS_CONNECT_BY_PATH(&1, '->'), '->', ' -> ')||CASE WHEN CONNECT_BY_ISLEAF = 1 THEN ' [sid='||d.session_id||' seq#='||TO_CHAR(seq#)||']' ELSE NULL END path -- there's a reason why I'm doing this (ORA-30004 :)
, CASE WHEN CONNECT_BY_ISLEAF = 1 THEN d.session_id ELSE NULL END sids
, CONNECT_BY_ISLEAF isleaf
, CONNECT_BY_ISCYCLE iscycle
, d.*
FROM
ash_samples s
, ash_data d
WHERE
s.sample_time_10s = d.sample_time_10s
AND d.sample_time BETWEEN &3 AND &4
CONNECT BY NOCYCLE
( PRIOR d.blocking_session = d.session_id
AND PRIOR s.sample_time_10s = d.sample_time_10s
AND PRIOR d.blocking_inst_id = d.instance_number)
START WITH &2
)
SELECT * FROM (
SELECT
LPAD(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100)||'%',5,' ') "%This"
, COUNT(*) * 10 seconds
, ROUND(COUNT(*) * 10 / ((CAST(&4 AS DATE) - CAST(&3 AS DATE)) * 86400), 1) AAS
, path wait_chain
, TO_CHAR(MIN(sample_time), 'YYYY-MM-DD HH24:MI:SS') first_seen
, TO_CHAR(MAX(sample_time), 'YYYY-MM-DD HH24:MI:SS') last_seen
, COUNT(DISTINCT sids) num_sids
, MIN(sids)
, MAX(sids)
FROM
chains
WHERE
isleaf = 1
GROUP BY
&1
, path
ORDER BY
COUNT(*) DESC
)
WHERE
rownum <= 30
/
Saturday, August 10, 2024
比如表T生成了两个dump文件（t_1.dmp,t_2.dmp)，就可以考虑如下的方式来加载，黄色部分是对应的dump文件。
CREATE TABLE T_EXT_1
( id number,object_id number,object_name varchar2(30),object_type varchar2(30),clob_test clob )
ORGANIZATION EXTERNAL
( TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY "EXPDP_LOCATION"
LOCATION
( 't_1.dmp'
)
) ;
CREATE TABLE T_EXT_2
( id number,object_id number,object_name varchar2(30),object_type varchar2(30),clob_test clob )
ORGANIZATION EXTERNAL
( TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY "EXPDP_LOCATION"
LOCATION
( 't_2.dmp'
)
) ;
对应的脚本如下：
其中在DUMP目录下存放着生成的dump文件，根据动态匹配得到最终生成了几个dump文件，来决定创建几个对应的外部表。
target_owner=`echo "2"|awk−F@′print$1′|awk−F/′print$1′|tr′[a−z]″[A−Z]′‘sourceowner=‘echo"1" |awk -F@ '{print 1}'|awk -F/ '{print $1}'|tr '[a-z]' '[A-Z]'`
tab_name=`echo "3"|tr '[a-z]' '[A-Z]'`
owner_account=5tmpparallel=‘ls−l../DUMP/{tab_name}_[0-9]*.dmp|wc -l`
echo parallel :tmpparallelforiin1..$tmpparallel;doecho\'{tab_name}_i.dmp\' >> tmp_{tab_name}_par_dmp.lst
done
sed -e '/^/d' tmp_{tab_name}_par_dmp.lst > ../DUMP_LIST/{tab_name}_par_dmp.lst
rm tmp_{tab_name}_par_dmp.lst
dump_list=`cat ../DUMP_LIST/tabnamepardmp.lst‘print"conn1
set feedback off
set linesize 100
col data_type format a30
set pages 0
set termout off
SELECT
t1.COLUMN_NAME,
t1.DATA_TYPE
|| DECODE (
t1.DATA_TYPE,
'NUMBER', DECODE (
'('
|| NVL (TO_CHAR (t1.DATA_PRECISION), '*')
|| ','
|| NVL (TO_CHAR (t1.DATA_SCALE), '*')
|| ')',
'(*,*)', NULL,
'(*,0)', '(38)',
'('
|| NVL (TO_CHAR (t1.DATA_PRECISION), '*')
|| ','
|| NVL (TO_CHAR (t1.DATA_SCALE), '*')
|| ')'),
'FLOAT', '(' || t1.DATA_PRECISION || ')',
'DATE', NULL,
'TIMESTAMP(6)', NULL,
'(' || t1.DATA_LENGTH || ')') ||','
AS DATA_TYPE
from all_tab_columns t1 where owner=upper('owneraccount′)ANDtablename=upper(′3' )
order by t1.column_id;
"|sqlplus -s /nolog > {tab_name}.temp
sed -e '/^/d' -e 's/.//' -e 's/CLOB(4000)/CLOB/g' -e 's/BLOB(4000)/BLOB/g' tabname.temp>../DESCLIST/{tab_name}.desc
rm tabname.tempforiin1..$tmpparalleldoecholoadingtable{tab_name} as {tab_name}_EXT_i
sqlplus -s 2settimingonsetechoonCREATETABLE{tab_name}_EXT_i(‘cat../DESCLIST/{tab_name}.desc `
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY 4LOCATION(‘sed−n"{i}p" ../DUMP_LIST/${tab_name}_par_dmp.lst`
));
EOF
done
exit
生成的日志类似下面的格式：
loading table T as T_EXT_1
Elapsed: 00:00:01.33
loading table T as T_EXT_2
Elapsed: 00:00:01.30
Saturday, June 29, 2024
SET SERVEROUTPUT ON
DECLARE
v_owner VARCHAR2(30) := 'YOUR_SCHEMA_NAME'; -- Replace with your schema name
v_new_tablespace VARCHAR2(30) := 'NEW_TABLESPACE'; -- Replace with your new tablespace name
BEGIN
FOR rec IN (
SELECT DISTINCT tablespace_name
FROM dba_segments
WHERE owner = v_owner
AND tablespace_name IS NOT NULL
) LOOP
DBMS_OUTPUT.PUT_LINE('REMAP_TABLESPACE=' || rec.tablespace_name || ':' || v_new_tablespace);
END LOOP;
END;
/
Subscribe to:
Posts (Atom)