#!/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 - $(date)

HTML_HEAD } add_html_row() { local server=$1 status=$2 cpu=$3 mem=$4 disk=$5 services=$6 updates=$7 security=$8 local row_class="good" [[ $status == *"CRITICAL"* ]] && row_class="critical" [[ $status == *"WARNING"* ]] && row_class="warning" [[ $status == "Offline"* ]] && row_class="offline" cat <> "$HTML_REPORT" HTML_ROW } finalize_html() { cat <> "$HTML_REPORT"

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}"

------ 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; /