SQL> Select * From Alert_XML_Errors;

Harry Dragstra

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> desc alert_xml
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
TEXT                                               VARCHAR2(400 CHAR)

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

Type created.

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

Type created.

SQL> 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> 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> set pagesize 0
SQL> 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...
****

< snip >

SQL>

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.

Next Post

How to hide login data of sql-scripts on Windows

Facebook0TwitterLinkedinOne of the often given advices on hardening a database is to run scripts without broadcasting your login data at the same time. According to Arup Nanda in his famous articles on “Project Lockdown” you have three options to run your scripts without letting everybody in on your password secrets: […]