Creating trace file, writing to alert.log and retrieve using Amazon RDS for Oracle
I have recently been asked to provide a workaround from the database.
A procedure that:
1. Write to the alertlog
2. Generate a trace file (no matter its content).
3. Let the user know how to query
a. The alertlog
b. The trace file
Oracle RDS implementation enable us this functionality without a need to create objects or have a special grants.
SET SERVEROUTPUT ON VERIFY OFF FEED OFF LINES 300 PAGES 200
DECLARE
full_file_name VARCHAR2 (512);
file_name VARCHAR2 (512);
MESSAGE VARCHAR2 (1024);
ALERT_TABLE VARCHAR2 (30);
rds_exists NUMBER;
TABLE_MISSING EXCEPTION;
PRAGMA EXCEPTION_INIT (TABLE_MISSING, -942);
FUNCTION FIND_VIEW (object_name VARCHAR2)
RETURN NUMBER
IS
BEGIN
EXECUTE IMMEDIATE 'select count(1) from ' || object_name;
RETURN 1;
EXCEPTION
WHEN TABLE_MISSING
THEN
RETURN 0;
END FIND_VIEW;
BEGIN
SELECT COUNT (1)
INTO rds_exists
FROM all_users
WHERE username = 'RDSADMIN';
IF FIND_VIEW ('X$DBGALERTEXT') = 1
THEN
ALERT_TABLE := 'X$DBGALERTEXT';
ELSE
IF FIND_VIEW ('ALERTLOG') = 1
THEN
ALERT_TABLE := 'ALERTLOG';
ELSE
ALERT_TABLE := '/* no acess to alert table X$DBGALERTEXT or ALERTLOG */';
END IF;
END IF;
EXECUTE IMMEDIATE 'ALTER SESSION SET max_dump_file_size = unlimited';
EXECUTE IMMEDIATE 'ALTER SESSION SET tracefile_identifier = ''&&identifier''';
EXECUTE IMMEDIATE 'ALTER SESSION SET statistics_level = ALL';
EXECUTE IMMEDIATE 'ALTER SESSION SET events ''10046 trace name context forever, level 12''';
EXECUTE IMMEDIATE 'select 1 from dual';
SELECT VALUE
INTO full_file_name
FROM V$DIAG_INFO
WHERE NAME = 'Default Trace File';
MESSAGE := 'Errors in file ' || full_file_name || ':';
file_name :=
SUBSTR (full_file_name,
( INSTR (full_file_name,
'/',
-1,
1)
+ 1),
LENGTH (full_file_name));
sys.DBMS_SYSTEM.ksdind (10);
sys.DBMS_SYSTEM.ksdwrt (sys.DBMS_SYSTEM.alert_file, MESSAGE);
sys.DBMS_SYSTEM.ksdfls;
DBMS_OUTPUT.put_line (CHR (10));
DBMS_OUTPUT.put_line (' ---------- Line will added to alert log -------------------------');
DBMS_OUTPUT.put_line (MESSAGE);
DBMS_OUTPUT.put_line (CHR (10));
DBMS_OUTPUT.put_line ('Query alert.log file using the following command:');
DBMS_OUTPUT.put_line ('------------------------------------------------');
DBMS_OUTPUT.put_line (' SELECT TO_CHAR (ORIGINATING_TIMESTAMP, ''YYYY-MM-DD HH24:MI:SS'') AS ORIGINATING_TIMESTAMP, MESSAGE_TEXT');
DBMS_OUTPUT.put_line (' FROM ' || ALERT_TABLE);
DBMS_OUTPUT.put_line (' WHERE originating_timestamp > (SYSDATE - 5 / 1440)');
DBMS_OUTPUT.put_line ('ORDER BY RECORD_ID;');
DBMS_OUTPUT.put_line (CHR (10));
IF rds_exists = 1
THEN
DBMS_OUTPUT.put_line ('Retrieving the generated trace file (on RDS) using the following command:');
DBMS_OUTPUT.put_line ('----------------------------------------------------------------');
DBMS_OUTPUT.put_line ('SELECT text FROM table(rdsadmin.rds_file_util.read_text_file(''BDUMP'',''' || file_name || ''')) where rownum < 20;');
DBMS_OUTPUT.put_line (CHR (10));
END IF;
EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ''10046 trace name context off''';
END;
/
EXIT
Comments
Post a Comment