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.
