First, put this in place as a package:
CREATE OR REPLACE PACKAGE 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);
PROCEDURE INIT_ALERT_LOG;
END alert_log;
/
CREATE OR REPLACE PACKAGE BODY 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);
cursor_id INTEGER;
return_val INTEGER;
file_type UTL_FILE.FILE_TYPE;
sql_statement VARCHAR2(500);
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
DBMS_OUTPUT.ENABLE(1000000);
build_alertlog_name;
check_alertlog_name;
UTL_FILE.frename('BDUMP_DIR',
alertfile,
'BDUMP_DIR',
TO_CHAR(SYSDATE, 'YYYYMMDD')
'_' alertfile);
SELECT directory_path
INTO bdump_dir
FROM all_directories
WHERE directory_name = 'BDUMP_DIR';
init_alert_log;
DBMS_OUTPUT.put_line('Archived to: '
' 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;
PROCEDURE INIT_ALERT_LOG IS
BEGIN
build_alertlog_name;
--check_alertlog_name;
-- ******************************************************
-- Open the alert log file for write access
-- ******************************************************
file_type := utl_file.fopen('BDUMP_DIR',alertfile,'W');
-- ******************************************************
-- Write the custom message to the alert log file
-- ******************************************************
utl_file.put_line(file_type,
'Alert log initialized by '
'HP_ALERT_LOG.INIT_ALERT_LOG on '
SYSDATE);
-- ******************************************************
-- Close the alert log file
-- ******************************************************
utl_file.fclose(file_type);
END INIT_ALERT_LOG;
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
END alert_log;
/
Now, save the following as a PL/SQL script:set serveroutput on;
exec alert_log.archive;
exit;
Finally, save this to a Korn shell script file executable by oracle Unix user only:#!/bin/ksh
USER=psgPASS=psg4admin
ORATAB=/etc/oratab
# Set path if path not set (if called from /etc/rc)
case $PATH in
"") PATH=/bin:/usr/bin:/etc
export PATH
;;
esac
SAVE_PATH=$PATH
# Save LD_LIBRARY_PATHSAVE_LLP=$LD_LIBRARY_PATH
## Loop for every entry in oratab file and and try to shut down# that ORACLE#
cat $ORATAB while read LINE
do
case $LINE in
\#*) ;; #comment-line in oratab
*)
# Proceed only if third field is 'Y'.
if [ "$(echo $LINE awk -F: '{print $3}' -)" = "Y" ] ; then
ORACLE_SID=$(echo $LINE awk -F: '{print $1}' -)
if [ "$ORACLE_SID" = '*' ] ; then
ORACLE_SID=""
fi
# Called programs use same database ID
export ORACLE_SID
ORACLE_HOME=$(echo $LINE awk -F: '{print $2}' -)
# Called scripts use same home directory
export ORACLE_HOME
# Put $ORACLE_HOME/bin into PATH and export.
PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/etc:$SAVE_PATH
export PATH
# add for bug 652997
LD_LIBRARY_PATH=${SAVE_LLP}:${ORACLE_HOME}/lib
export LD_LIBRARY_PATH
PFILE=${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora
BDUMP_DIR=$(sqlplus $USER/$PASS @runAlertLogRead grep Archive cut -f3 -d' ')
gzip $BDUMP_DIR
PATH=$SAVE_PATH
fi
;;
esac
done
As the final step, add the Korn shell script to a cron job. This can then be run at regular intervals. 
No comments:
Post a Comment