SET SERVEROUTPUT ON SIZE UNLIMITED; -- Required to see DBMS_OUTPUT
DECLARE
-- === Input Parameters (Adjust these as needed) ===
p_begin_time DATE := TO_DATE('2025-07-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS'); -- Selected start time (adjust to spike start)
p_end_time DATE := TO_DATE('2025-07-10 06:00:00', 'YYYY-MM-DD HH24:MI:SS'); -- Selected end time (adjust to spike end)
p_report_type_param VARCHAR2(3) := 'AWR'; -- 'AWR' or 'ASH'
p_report_format_param VARCHAR2(4) := 'HTML'; -- 'HTML' or 'TEXT' (case-insensitive for RDS procs)
p_dump_directory VARCHAR2(30) := 'BDUMP'; -- Default; can change to custom directory if created
-- Variables for snap IDs (for AWR)
v_begin_snap_id NUMBER;
v_end_snap_id NUMBER;
-- Variable to hold the constructed filename
v_generated_filename VARCHAR2(256);
-- Cursor for report content retrieval
TYPE report_line_cur_type IS REF CURSOR;
report_line_cur report_line_cur_type;
v_report_line VARCHAR2(32767); -- To hold each line of the report file
-- Formatting
v_separator VARCHAR2(80) := RPAD('-', 80, '-');
BEGIN
DBMS_OUTPUT.PUT_LINE(v_separator);
DBMS_OUTPUT.PUT_LINE('-- Oracle RDS AWR/ASH Report Generator --');
DBMS_OUTPUT.PUT_LINE('-- Analysis Period: ' || TO_CHAR(p_begin_time, 'YYYY-MM-DD HH24:MI:SS') || ' to ' || TO_CHAR(p_end_time, 'YYYY-MM-DD HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE('-- Report Type: ' || p_report_type_param || ', Format: ' || p_report_format_param || ', Directory: ' || p_dump_directory);
DBMS_OUTPUT.PUT_LINE(v_separator);
DBMS_OUTPUT.PUT_LINE(' ');
IF UPPER(p_report_type_param) = 'AWR' THEN
-- Improved snap ID logic: Bracket the time range accurately
BEGIN
SELECT MIN(snap_id)
INTO v_begin_snap_id
FROM dba_hist_snapshot
WHERE end_interval_time > p_begin_time;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_begin_snap_id := NULL;
END;
BEGIN
SELECT MAX(snap_id)
INTO v_end_snap_id
FROM dba_hist_snapshot
WHERE begin_interval_time < p_end_time;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_end_snap_id := NULL;
END;
IF v_begin_snap_id IS NULL OR v_end_snap_id IS NULL OR v_begin_snap_id > v_end_snap_id THEN
RAISE_APPLICATION_ERROR(-20001, 'Error: Could not find AWR snapshots covering the specified time interval. ' ||
'Ensure AWR retention covers the period and check time boundaries. ' ||
'Begin Snap ID found: ' || NVL(TO_CHAR(v_begin_snap_id), 'N/A') ||
', End Snap ID found: ' || NVL(TO_CHAR(v_end_snap_id), 'N/A'));
END IF;
DBMS_OUTPUT.PUT_LINE('Generating AWR report for Snap IDs: ' || v_begin_snap_id || ' to ' || v_end_snap_id || '...');
-- Generate AWR report (procedure call, no return value)
EXECUTE IMMEDIATE 'BEGIN rdsadmin.rdsadmin_diagnostic_util.awr_report(' ||
v_begin_snap_id || ', ' ||
v_end_snap_id || ', ''' ||
UPPER(p_report_format_param) || ''', ''' ||
p_dump_directory || '''); END;';
-- Construct filename (predictable pattern)
v_generated_filename := 'awrrpt_' || v_begin_snap_id || '_' || v_end_snap_id || '.' || LOWER(p_report_format_param);
ELSIF UPPER(p_report_type_param) = 'ASH' THEN
-- ASH report directly uses timestamps
DBMS_OUTPUT.PUT_LINE('Generating ASH report for time range: ' || TO_CHAR(p_begin_time, 'YYYY-MM-DD HH24:MI:SS') || ' to ' || TO_CHAR(p_end_time, 'YYYY-MM-DD HH24:MI:SS') || '...');
-- Generate ASH report (procedure call, no return value)
EXECUTE IMMEDIATE 'BEGIN rdsadmin.rdsadmin_diagnostic_util.ash_report(' ||
'TO_DATE(''' || TO_CHAR(p_begin_time, 'YYYY-MM-DD HH24:MI:SS') || ''', ''YYYY-MM-DD HH24:MI:SS''), ' ||
'TO_DATE(''' || TO_CHAR(p_end_time, 'YYYY-MM-DD HH24:MI:SS') || ''', ''YYYY-MM-DD HH24:MI:SS''), ''' ||
UPPER(p_report_format_param) || ''', ''' ||
p_dump_directory || '''); END;';
-- Construct filename (predictable pattern; adjust if RDS uses different)
v_generated_filename := 'ashrpt_' || TO_CHAR(p_begin_time, 'YYYYMMDDHH24MISS') || '_' || TO_CHAR(p_end_time, 'YYYYMMDDHH24MISS') || '.' || LOWER(p_report_format_param);
ELSE
RAISE_APPLICATION_ERROR(-20002, 'Invalid p_report_type_param. Use ''AWR'' or ''ASH''.');
END IF;
DBMS_OUTPUT.PUT_LINE('Report generated successfully on RDS file system.');
DBMS_OUTPUT.PUT_LINE('File Name: ' || v_generated_filename);
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(v_separator);
DBMS_OUTPUT.PUT_LINE('-- Report Content (' || UPPER(p_report_format_param) || ' starts here) --');
DBMS_OUTPUT.PUT_LINE(v_separator);
-- --- Retrieve and Output Report Content ---
-- Using rdsadmin.rds_file_util.read_text_file to get the content line by line
OPEN report_line_cur FOR
SELECT text
FROM TABLE(rdsadmin.rds_file_util.read_text_file(p_dump_directory, v_generated_filename));
LOOP
FETCH report_line_cur INTO v_report_line;
EXIT WHEN report_line_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_report_line);
END LOOP;
CLOSE report_line_cur;
DBMS_OUTPUT.PUT_LINE('--- Report Content Ends ---');
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('NOTE: For HTML reports, download the file ' || v_generated_filename || ' from RDS Console (Logs & events -> Logs tab) and open in a web browser for proper formatting.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(v_separator);
DBMS_OUTPUT.PUT_LINE('!!! An ERROR occurred during report generation !!!');
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
-- Clean up: Ensure cursor is closed if error occurs during fetch
IF report_line_cur%ISOPEN THEN
CLOSE report_line_cur;
END IF;
DBMS_OUTPUT.PUT_LINE(v_separator);
RAISE; -- Re-raise the exception to stop execution and indicate failure
END;
/
No comments:
Post a Comment