Creating trace file, writing to alert.log and retrieve using Amazon RDS for Oracle

Since in Oracle RDS environment, we don't have a direct access to the operating system
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

Popular posts from this blog

Data Guard - Changing IP Addresses

Install Oracle Internet Directory (OID) in Standalone mode

Fixing & Registering ORACLE_HOMES in Central Inventory