The Code
CREATE OR REPLACE PACKAGE
that_darn_alert_log
AS
PROCEDURE bdump_dir_setup;
PROCEDURE build_possible_alert_log_names;
PROCEDURE build_alertlog_name;
PROCEDURE check_alertlog_name;
PROCEDURE read (in_date_offset IN NUMBER);
PROCEDURE archive;
PROCEDURE remove (in_date_offset IN NUMBER, 
number_of_days IN NUMBER);
PROCEDURE list (in_date_offset IN NUMBER);
END that_darn_alert_log;
/
CREATE OR REPLACE PACKAGE BODY
that_darn_alert_log
AS
bdump_dir VARCHAR2(500);
alertlist VARCHAR2(4000);
sid VARCHAR2(100);
comma VARCHAR2(1);
alertfile VARCHAR2(255);
itexists NUMBER;
fileat BFILE;
pointa INTEGER;
pointb INTEGER;
v_length INTEGER;
r_char10 RAW(4);
char10 VARCHAR2(1) := CHR(10);
startdate VARCHAR2(100);
r_startdate RAW(100);
buffer VARCHAR2(800);
buffer2 VARCHAR2(800);
vexists BOOLEAN;
vfile_length NUMBER;
vblocksize NUMBER;
date_offset NUMBER;
date_file VARCHAR2(255);
PROCEDURE bdump_dir_setup IS
BEGIN
EXECUTE IMMEDIATE
'SELECT value '||
' FROM v$parameter '||
' WHERE name = ''background_dump_dest'''
INTO bdump_dir;
EXECUTE IMMEDIATE
'CREATE DIRECTORY BDUMP_DIR '||
' AS '''||bdump_dir||'''';
END bdump_dir_setup;
PROCEDURE build_alertlog_name IS
BEGIN
alertlist := NULL;
comma := NULL;
EXECUTE IMMEDIATE
'SELECT value '||
' FROM v$parameter '||
' WHERE name = ''db_name'''
INTO sid;
build_possible_alert_log_names;
EXECUTE IMMEDIATE
'SELECT value '||
' FROM v$parameter '||
' WHERE name = ''instance_name'''
INTO sid;
build_possible_alert_log_names;
EXECUTE IMMEDIATE
'SELECT substr(global_name,1,'||
' instr(global_name,''.'',-1)-1) '||
' FROM global_name'
INTO sid;
build_possible_alert_log_names;
r_char10 := UTL_RAW.CAST_TO_RAW(char10);
END build_alertlog_name;
PROCEDURE check_alertlog_name IS
BEGIN
pointa := 1;
LOOP
pointb := INSTR(alertlist,
',',
pointa,1);
v_length := pointb - pointa;
alertfile:= SUBSTR(alertlist,
pointa,
v_length);
pointa := pointb + 1;
itexists := DBMS_LOB.FILEEXISTS(
BFILENAME('BDUMP_DIR',alertfile));
IF itexists = 1 THEN
pointb := 0;
EXIT;
END IF;
END LOOP;
END check_alertlog_name;
PROCEDURE build_possible_alert_log_names IS
BEGIN
alertlist := alertlist||
comma||
'alert_'||
lower(sid)||
'.log';
comma := ',';
alertlist := alertlist||
comma||
upper(sid)||
'ALRT.LOG';
END build_possible_alert_log_names;
PROCEDURE read (in_date_offset IN NUMBER) IS
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
build_alertlog_name;
check_alertlog_name;
date_offset := in_date_offset;
IF date_offset = 0 THEN
date_file := alertfile;
ELSE
date_file := TO_CHAR(SYSDATE-date_offset,'YYYYMMDD')||'_'||alertfile;
END IF;
UTL_FILE.FGETATTR('BDUMP_DIR',date_file,vexists,vfile_length,vblocksize);
IF vexists THEN
fileat := BFILENAME('BDUMP_DIR',date_file);
DBMS_LOB.FILEOPEN(fileat,DBMS_LOB.FILE_READONLY);
startdate := TO_CHAR(TRUNC(SYSDATE),'Dy Mon DD');
r_startdate := UTL_RAW.CAST_TO_RAW(startdate);
pointa := DBMS_LOB.INSTR(fileat,r_startdate,1,1);
IF pointa = 0 THEN
EXECUTE IMMEDIATE
'SELECT TO_CHAR(TRUNC(SYSDATE),''Dy Mon '')||'||
' DECODE(SUBSTR(TO_CHAR(TRUNC(SYSDATE),''DD''),1,1),0,'' '')'||
' ||SUBSTR(TO_CHAR(TRUNC(SYSDATE),''DD''),2,1)'||
' FROM dual'
INTO startdate;
r_startdate := UTL_RAW.CAST_TO_RAW(startdate);
pointa := DBMS_LOB.INSTR(fileat,r_startdate,1,1);
END IF;
IF pointa != 0 THEN
LOOP
pointb := DBMS_LOB.INSTR(fileat,r_char10,pointa,1);
EXIT WHEN pointb = 0;
v_length := pointb - pointa;
buffer := DBMS_LOB.SUBSTR(fileat,v_length,pointa);
buffer2 := utl_raw.cast_to_varchar2(buffer);
DBMS_OUTPUT.PUT_LINE(buffer2);
pointa := pointb + 1;
END LOOP;
END IF;
DBMS_LOB.FILECLOSE(fileat);
END IF;
END read;
PROCEDURE archive IS
BEGIN
build_alertlog_name;
check_alertlog_name;
UTL_FILE.FRENAME ('BDUMP_DIR',
alertfile,
'BDUMP_DIR',
TO_CHAR(SYSDATE,'YYYYMMDD')||
'_'||alertfile);
END archive;
PROCEDURE remove (in_date_offset IN NUMBER, number_of_days IN NUMBER) IS
BEGIN
build_alertlog_name;
check_alertlog_name;
FOR date_offset IN (in_date_offset - number_of_days)..in_date_offset LOOP
date_file := TO_CHAR(SYSDATE-date_offset,'YYYYMMDD')||
'_'||alertfile;
UTL_FILE.FGETATTR('BDUMP_DIR',
date_file,
vexists,
vfile_length,
vblocksize);
IF vexists THEN
UTL_FILE.FREMOVE ('BDUMP_DIR',date_file);
dbms_output.put_line(date_file||' - REMOVED');
END IF;
END LOOP;
END remove;
PROCEDURE list (in_date_offset IN NUMBER) IS
BEGIN
build_alertlog_name;
check_alertlog_name;
FOR date_offset IN 0..in_date_offset LOOP
IF date_offset = 0 THEN
date_file := alertfile;
UTL_FILE.FGETATTR('BDUMP_DIR',
date_file,
vexists,
vfile_length,
vblocksize);
IF vexists THEN
dbms_output.put_line('OffSet : '||
date_offset||
', '||
date_file||
' '||
vfile_length);
END IF;
END IF;
date_file := TO_CHAR(SYSDATE-date_offset,'YYYYMMDD')||
'_'||alertfile;
UTL_FILE.FGETATTR('BDUMP_DIR',
date_file,
vexists,
vfile_length,
vblocksize);
IF vexists THEN
dbms_output.put_line('OffSet : '||
date_offset||
', '||
date_file||
' '||
vfile_length);
END IF;
END LOOP;
END list;
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
END that_darn_alert_log;
/
The Oracle
alert log contains loads of information. It is not enough just to look at the
alert log every day; you should also be in the routine of archiving the alert
log so that you can go back in time to determine the history of errors and
database happenings. Your ability to keep the current archive log manageable, and
maintain a clean mechanism to archive the alert log are the first steps in
maintaining a clean database environment. Using some new features in the
UTL_FILE package, you now have the tools. Feel free to modify these procedures
for the alert log but also be aware there are other files such as sqlnet.log
that could be encapsulated in these procedures. Happy file hunting.
»
See All Articles by Columnist James Koopmann
» 
Comment and Contribute