SQL> Select * From Alert_XML_Errors;

Read Time:3 Minute, 12 Second

Once you are able to show the xml version of the alert log as data in database table Alert_XML, it would be nice to checkout the errors with accompanying timestamps from within view Alert_XML_Errors. Like this, with the help of 2 types and a pipelined function.

su - oracle
. oraenv [ orcl ]
[oracle@localhost ~]$ sqlplus harry/*****
....
SQL&gt; desc alert_xml
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
TEXT                                               VARCHAR2(400 CHAR)

SQL&gt; CREATE OR REPLACE TYPE v2_row AS OBJECT ( text varchar2(400));
/

Type created.

SQL&gt; CREATE OR REPLACE TYPE v2_table AS TABLE OF v2_row;
/

Type created.

SQL&gt; CREATE OR REPLACE FUNCTION Get_Errors
( P sys_refcursor )
RETURN v2_table PIPELINED
IS
out_rec  v2_row := v2_row(NULL);
this_rec alert_xml%ROWTYPE;
currdate VARCHAR2(400) := 'NA';
last_printed_date VARCHAR2(400) := currdate;
testday VARCHAR2(3);
testerr VARCHAR2(4);
firstdate BOOLEAN := TRUE;
BEGIN
currdate := 'NA';
last_printed_date := currdate;
LOOP
FETCH p INTO this_rec;
EXIT WHEN p%NOTFOUND;

this_rec.text := LTRIM(this_rec.text);

-- check if this line contains a date stamp
testday := SUBSTR(this_rec.text,1,3);
IF testday = '201'
THEN
-- show dates as in de text version of the alert log
currdate := to_char(to_date(substr(this_rec.text,1,19),'YYYY-MM-DD HH24:MI:SS'),'Dy Mon DD hh24:mi:ss yyyy','NLS_DATE_LANGUAGE = AMERICAN');
ELSIF
testday = 'Sat'
OR testday = 'Sun'
OR testday = 'Mon'
OR testday = 'Tue'
OR testday = 'Wed'
OR testday = 'Thu'
OR testday = 'Fri'
THEN
currdate := this_rec.text;
END IF;

testerr := SUBSTR(this_rec.text,1,4);
IF testerr = 'ORA-'
OR testerr = 'TNS-'
THEN
IF last_printed_date != currdate
OR ( currdate != 'NA' AND firstdate )
THEN
last_printed_date := currdate;
firstdate := FALSE;
out_rec.text := '****';
PIPE ROW(out_rec);
out_rec.text := currdate;
PIPE ROW(out_rec);
out_rec.text := '****';
PIPE ROW(out_rec);
END IF;
out_rec.text := this_rec.text;
pipe ROW(out_rec);

END IF;
END LOOP;

CLOSE P;
RETURN;
END Get_Errors;
/

Function created.

SQL&gt; CREATE OR REPLACE FORCE VIEW ALERT_XML_ERRORS
AS
SELECT "TEXT"
FROM TABLE (get_errors (CURSOR (SELECT * FROM alert_xml)));

View created.

And checkout the errors now:

SQL&gt; set pagesize 0
SQL&gt; select * from alert_xml_errors;
****
Tue Aug 26 11:01:08 2014
****
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u02/oradata/orcl/users01.dbf'
ORA-27037: unable to obtain file status
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u02/oradata/orcl/users01.dbf'
ORA-1157 signalled during: ALTER DATABASE OPEN...
****
Tue Aug 26 11:12:51 2014
****
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u02/oradata/orcl/users01.dbf'
ORA-27037: unable to obtain file status
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u02/oradata/orcl/users01.dbf'
ORA-1157 signalled during: ALTER DATABASE OPEN...
****
Tue Aug 26 13:39:36 2014
****
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u02/oradata/orcl/users01.dbf'
ORA-27037: unable to obtain file status
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u02/oradata/orcl/users01.dbf'
ORA-1157 signalled during: ALTER DATABASE OPEN...
****

&lt; snip &gt;

SQL&gt;

And yes, the pipelined function will only work till 2020 on the xml version of the alert log – see if you can find the code line! – , and yes, it should be functional on the text version of the alert log too, provided the external table describes like alert_xml.

About Post Author

Harry Dragstra

Harry Dragstra is Senior Oracle DBA. He is specialized in Oracle RAC, Oracle Virtualization, and has a a strong interest in APEX development and PL/SQL.

Harry.Dragstra@amis.nl