Manipulating Oracle Files with UTL_FILE

Friday Jan 9th 2004 by James Koopmann
Share:

Take hold of your alert log with the use of a few new subprograms in the UTL_FILE package.

Take hold of your alert log with the use of a few new subprograms in the UTL_FILE package.

Maintenance of the Oracle alert log file has and continually will be a pain to work with until the day that Oracle realizes they need to hook in a few more operating system utilities to manage their own trashcans. About this time last year I wrote an article called 'That Darn Alert Log' where I gave you a simple utility that would allow you to read the alert log from within the Oracle database. This is a great little utility if you have found it a headache to logon to every Oracle database just to view the alert log. While I have changed the code slightly for these new procedures, go back and read this article on how to setup and use the utility. Through this article, I will present the new procedures I have created to take advantage of the new UTL_FILE procedures and present the full code at the end of the article. If you used that utility, and hopefully modified it to warn you of errors, you soon found out that you still needed to log into those database boxes to clean-up the alert log of old information. This article will expand upon the utility and give you a few more options to handle your alert log and actually clean-up the alert log without ever logging into those boxes.

What is the UTL_FILE Package

The UTL_FILE package is simply a set of PL/SQL procedures that allow you to read, write and manipulate operating system files. In past releases of this package, you were limited to only reading and writing to system files and obtaining access to those files was much more difficult since the database needed to be bounced every time a new set of files were to be accessed from a different directory structure on the system. With the new changes to the UTL_FILE package, the access to different directories has been simplified by the use of the CREATE DIRECTORY command.

CREATE DIRECTORY

As noted above, it use to be that granting access to a set of files on the operating system, a DBA would have to make an entry in the init.ora file for the parameter UTL_FILE_DIR to let the UTL_FILE package recognize a directory. This has been greatly simplified by the use of the CREATE DIRECTORY command. By issuing a CREATE DIRECTORY and granting access to the directory, users will have immediate access to system files within that directory. As you can see, the CREATE DIRECTORY command can give you much more control through its dynamic nature than the old UTL_FILE_DIR parameter. Here is what the two commands look like.

SQL> CREATE DIRECTORY BDUMP_DIR 
AS '/u01/app/oracle/admin/saigon/bdump';
SQL> GRANT READ ON DIRECTORY BDUMP_DIR TO PUBLIC;

Here is the new procedure that takes advantage of creating the directory where the alert log resides. It first selects from V$PARAMETER where the background dump destination is defined and then creates the directory to it.

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;

UTL_FILE.FRENAME

This is a little gem of a procedure that will rename one file to another. In addition to renaming a file, this procedure also has the ability to move the file to a different area on disk. In the following procedure, I only rename the file to a date stamp with the alert log name in the same BDUMP_DIR. If you are currently using operating system utilities to move alert logs to different directories, this procedure can quickly be changed to move the alert log to a different directory. Moving to a different directory would require the use of the CREATE DIRECTORY command to set up the alternate directory you would be moving the alert logs to. Please also note that after this command executes, there will no longer be an alert log on disk. Not to fear, the next time Oracle needs to write to the alert log a new one will be created. If you are serious about archiving your alert logs, this procedure should be run every day, week or month depending on how much information you want in each of your alert logs. Be aware that the first time you run this command, the first archived alert log might be very large since it will contain all of the lines in your current alert log since the last time you actually cleaned it out. Also, be aware that the name of the file is a date stamp of the current day plus the normal default alert log name. Access to this archived alert log in future code will be from a date offset that is determined from the current day.

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;

UTL_FILE.FGETATTR



One of the key links to doing file manipulation is the use of the UTL_FILE.FGETATTR procedure. This procedure gets a couple of file attributes and checks for the file's existence on disk. The following procedure makes use of this procedure by accepting a date offset and checking on disk for archived alert logs that follow a pattern of date stamp and default alert log name. This procedure will list the files using the DBMS_OUTPUT.PUT_LINE procedure for files that it has found and print out the associated date offset to the individual files. Since the reading of the alert log and archived alert logs requires you to supply a date offset, you should take special note of this value to display the proper alert log. Before issuing this command, you must use the SET SERVEROUTPUT ON command so that DBMS_OUTPUT.PUT_LINE can send the information to the screen.



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;

UTL_FILE.FREMOVE

This new procedure does exactly what it says; it will remove a file from the operating system. You must have privileges on the operating system to do this but since we are manipulating files that are owned by the user oracle and the UTL_FILE procedure will run as user oracle, there are no problems here with operating system privileges. The following procedure makes use of the input variable IN_DATE_OFFSET that you should supply to tell the procedure how far back in time to go and remove all alert log archives up to but not including the current archived log. Before actually using the UTL_FILE.FREMOVE procedure the code will build viable alert log names and then check to see if the derived archive log name is a good one. If the default alert log name is good it will then cycle through the date offset, check to see if that archived alert log exists through the UTL_FILE.FGTATTR procedure and then remove the archived alert log if it exists. Also, be aware that this code will work if there are holes in the span for the date offset and one of the files does not exist.

PROCEDURE remove (in_date_offset IN NUMBER) IS
BEGIN
  build_alertlog_name;
  check_alertlog_name;
  FOR date_offset IN 1..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;

How to Use the code.

To get you going, let's first go through the 'How to Use' procedures to put the simple shell on a database box and see the results. After you are comfortable with the shell, I would suggest you modify it by removing the DBMS_OUTPUT.PUT_LINE with a call to code that will search the strings returned, insert them into a table and have email or pager notification wrapped around those important error messages. After you get that done, just put the procedures on every database you want to monitor, schedule a task or dbms_job around it and then sleep peacefully.

How to Use

1.      log into your database of choice as the SYS user

2.      compile the package

SQL> @that_darn_alert_log

3.      set serveroutput on

SQL> set serveroutput on

4.      initial setup of directory object

SQL> exec that_darn_alert_log.bdump_dir_setup

5.      to read and display the current alert log

SQL> exec that_darn_alert_log.read(0)

6.      to archive the current alert log

SQL> exec that darn_alert_log.archive

7.      to list all archived logs starting 30 days from current date

SQL> exec that_darn_alert_log.list(30)

8.      to remove a single archived log with date offset of 45

SQL> exec that_darn_alert_log.remove(45,0)

9.      to remove all archived logs from the last week

SQL> exec that_darn_alert_log.remove(14,6)

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

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved