ALERT_LOG_MANAGER

Reprinted with Permission by Quest Software June 2002

ALERT_LOG_MANAGER
Darryl Hurley, Quest Pipeline SYSOP

This PL/SQL package takes advantage of external tables in Oracle 9 to make Oracle alert logs and trace files available for display within the database itself as shown in the following demonstrations.

Usage

The current version of alert_log_manager focuses on trace files. Specifically locating them in the alert log and displaying their contents.

Locating Trace Files

Trace file entries in the alert log are located and displayed using the LIST_TRACES procedure shown in figure 1.

SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL> EXEC alert_log_manager.list_traces
--------------------------------------------------
(1) Tue Oct 30 14:21:05 2001
Errors in file /oracle/home/admin/r901/udump/ora_11136_r901.trc:
ORA-00600: internal error code, arguments: [2265], [], [], [], []
--------------------------------------------------
(2) Tue Nov 20 10:05:51 2001
Errors in file /oracle/home/admin/r901/udump/ora_16837_r901.trc:
ORA-00600: internal error code, arguments: [4400], [48], [], [], []
--------------------------------------------------
(3) Tue Nov 20 10:05:55 2001
Errors in file /oracle/home/admin/r901/udump/ora_16837_r901.trc:
ORA-00600: internal error code, arguments: [18095], [0xC0000000210D8BF8], [], [], [], [], []
ORA-00600: internal error code, arguments: [4400], [48], [], [], []

PL/SQL procedure successfully completed.

Figure 1 – LIST_TRACES procedure

The timestamp, trace file name and Oracle error raised are displayed for all trace files. Each one is assigned a sequence number shown on the first line (1,2 and 3 above). This sequence number is then used as an argument to show contents of the trace file.

Displaying Trace Files

Contents of trace files are retrieved and displayed using the SHOW_TRACE procedure shown in figure 2.

SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL> EXEC alert_log_manager.show_trace(3);
Dump file /oracle/home/admin/r901/udump/ora_16837_r901.trc
Oracle9i Enterprise Edition Release 9.0.1.0.0 - 64bit Production
With the Partitioning option
JServer Release 9.0.1.0.0 - Production
ORACLE_HOME = /u/oracle/product/9.0.1
System name: HP-UX
Node name: dbm
Release: B.11.00
Version: U
Machine: 9000/800
Instance name: r901
Redo thread mounted by this instance: 1
Oracle process number: 81
Unix process pid: 16837, image: oracle@dbm (TNS V1-V3)
*** SESSION ID:(101.5) 2001-11-20 10:05:51.637
*** 2001-11-20 10:05:51.637
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [4400], [48], [], [], []

PL/SQL procedure successfully completed.

Figure 2 – SHOW_TRACE procedure

Be default the stack portion of the trace file is not shown. The display of the stack is turned on and off in a toggle fashion using the TOGGLE_STACK procedure shown in figure 3.

SQL> EXEC alert_log_manager.toggle_stack;

PL/SQL procedure successfully completed.

SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL> EXEC alert_log_manager.show_trace(3);
Dump file /oracle/home/admin/r901/udump/ora_16837_r901.trc
Oracle9i Enterprise Edition Release 9.0.1.0.0 - 64bit Production
With the Partitioning option
JServer Release 9.0.1.0.0 - Production
ORACLE_HOME = /u/oracle/product/9.0.1
System name: HP-UX
Node name: dbm
Release: B.11.00
Version: U
Machine: 9000/800
Instance name: r901
Redo thread mounted by this instance: 1
Oracle process number: 81
Unix process pid: 16837, image: oracle@dbm (TNS V1-V3)
*** SESSION ID:(101.5) 2001-11-20 10:05:51.637
*** 2001-11-20 10:05:51.637
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [4400], [48], [], [], [], [], [], []
----- Call Stack Trace -----
calling
location
-------------------- -------- -------------------- -------------------
Cannot read string table section header in /u/oracle/product/9.0.1/bin/oracle
Cannot read string table section header in /u/oracle/product/9.0.1/bin/oracle
<< snipped >>

Figure 3 – TOGGLE_STACK procedure

Keywords

The trace file entries displayed by LIST_TRACES can be restricted to those that contain certain keywords as shown in figure 4.

SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL> EXEC alert_log_manager.add_keyword('Shutting down');

PL/SQL procedure successfully completed.

SQL> EXEC alert_log_manager.list_traces;
--------------------------------------------------
(1) Wed May 15 09:28:37 2002
Errors in file /oracle/home/admin/r901/udump/ora_10919_r901.trc:
ORA-07445: exception encountered: core dump [11] [SIGSEGV] [unknown code] [3000504] [] []
Shutting down instance (abort)
License high water mark = 8
Instance terminated by USER, pid = 11220
--------------------------------------------------
(2) Wed May 15 09:43:09 2002
Shutting down instance (abort)
License high water mark = 9
Instance terminated by USER, pid = 11241

PL/SQL procedure successfully completed.

Figure 4 – ADD_KEYWORD procedure

Keywords are removed using the REM_KEYWORD procedure shown in figure 5. The first version removes the specific keyword and the second removes all current keywords.

SQL> exec alert_log_manager.rem_keyword('Shutting Down');

PL/SQL procedure successfully completed.

SQL> exec alert_log_manager.rem_keyword;

PL/SQL procedure successfully completed.

Figure 5 – REM_KEYWORD procedure

Installation

The alert_log_manager package is best installed as a DBA user with CREATE ANY TABLE and SELECT ANY DICTIONARY privileges. Simply sign onto SQL*Plus and execute the alert.sql script. You will be prompted for the name of the alert log associated with the current database. The name of this file is usually of the format alert_db.log where db is the name of the database. An example installation is shown in figure 6.

SQL> @alert

1 row selected.

1 row selected.

old 1: CREATE OR REPLACE DIRECTORY log_dir AS '&&bdump_dir'
new 1: CREATE OR REPLACE DIRECTORY log_dir AS '/oracle/home/admin/r901/bdump'

Directory created.

old 1: CREATE OR REPLACE DIRECTORY trace_dir AS '&&udump_dir'
new 1: CREATE OR REPLACE DIRECTORY trace_dir AS '/oracle/home/admin/r901/udump'

Directory created.

Table dropped.

Enter value for alert_log_file_name: alert_r901.log
old 15: LOCATION('&alert_log_file_name') )
new 15: LOCATION('alert_r901.log') )

Table created.

Package created.

old 162: IF INSTR(v_alert_rec.detail_line,'&&udump_dir') > 0 THEN
new 162: IF INSTR(v_alert_rec.detail_line,'/oracle/home/admin/r901/udump') > 0 THEN
old 167: v_trace_name := TRIM(SUBSTR(v_alert_rec.detail_line,INSTR(v_alert_rec.detail_line,'&&udump
new 167: v_trace_name := TRIM(SUBSTR(v_alert_rec.detail_line,INSTR(v_alert_rec.detail_line,'/oracle
old 169: v_trace_name := TRIM(SUBSTR(v_trace_name,LENGTH('&&udump_dir') + 2,200));
new 169: v_trace_name := TRIM(SUBSTR(v_trace_name,LENGTH('/oracle/home/admin/r901/udump') + 2,200))

Package body created.

Figure 6 – Example installation

Do not be alarmed if you see messages like “Table or view does not exist.”

Click Here for the complete ALERT_LOG_MANAGER package.

Modifications and Additions

Feel free to make modifications or additions to this code to suit your needs. Post your changes on the pipeline so they can be shared.