|
|
|
|
Overview
For Unix based console applications it is sometimes important to log messages to Oracle as well as to log files. Long running processes may require real-time logging for meaningful feedback and with the prevalence of web applications against Oracle databases it is a convenient way of storing these messages for easy client access. This example demonstrates a method of allowing Unix applications to log to Oracle without incurring the time penalty of repeated connect/disconnects or the complexity of writing custom ProC/Java Daemons (i.e. a program running in the background that would handle all database logging requests for the application).
Architecture
This mechanism makes use of Unix named pipes so it only works where Oracle resides on some flavor of Unix (only HP tested). A named pipe (also known as a FIFO) is a special type of file which allows unrelated processes to exchange data. There is usually one "server" (a program reading from the pipe) and one or more "clients" (a program writing to the pipe). When a client tries to write to a named pipe it is blocked (frozen) until the server reads from the pipe. Similarly, when the server tries to read from a named pipe it is blocked until a client writes to the pipe.
By using UTL_FILE to read from a Unix named pipe Oracle can listen (become a server) to a Unix process without requiring that Unix process to connect directly to Oracle. The UTL_FILE commands can be encapsulated in a procedure or package and can be executed by either DBMS_JOB or by an external connection such as sqlplus. Oracle also acknowledges the Unix blocking signals so the Oracle process reading from the pipe sleeps with little or no processor usage until incoming data is received.
Usage
The first step is to create a named pipe using the Unix "mkfifo" command, e.g. mkfifo pipe_name. The security on the named pipe such as the owner/group and permissions will probably need to be tweaked to restrict read access to Oracle and write access to the client application(s). Oracle also requires modification of the init.ora file UTL_FILE_DIR parameter to allow UTL_FILE access to the pipe name directory. Once this directory is available then Oracle should be able to poll this named pipe (see example procedure at end of document). Any Unix process with permission to write to the pipe can send text to Oracle by using the > or >> I/O redirectors, e.g. echo "Houston, we have a problem" > pipe_name. The named pipe is also a file so it can be treated as an output file for any Unix commands that log to a file. Files can be copied directly to the pipe using cat or with additional information using awk.
Multiple processes could potentially be writing to the same pipe so it may be necessary to come up with a protocol that Oracle can use to decipher the text streams. A simple one may be a file name followed by text with a ~ delimiter, e.g. File Name~Text. Oracle would then parse this stream and handle each type of command. More complex handlers can separate warnings vs. errors or other complicated forms of logging.
The writing process can be blocked if the Oracle process is not reading from the other end of the pipe or if the pipe is full. A small C program that opens the pipe as non-blocking and returns 1/0 depending on if it is being read from is provided at the end of this document. The Oracle process that polls the pipe can be stopped by sending an agreed upon text message that is embedded in the stored procedure through the pipe (e.g. echo "EXIT" > pipe). This would be the exit criteria in the read loop and is the only reliable method as the process is physically blocked by the OS until incoming data is received.
Conclusion
The performance of this architecture is surprisingly good. Oracle can read from the named pipe at a similar speed as that of a standard file. This speed is limited by how quickly the data is supplied to the named pipe by the client application. Parsing and/or database inserts of incoming data incurs additional overhead to the reading process and may even signal the client to slow down (block client until buffer is flushed). This process would save a considerable amount of time if the existing logging mechanism connects, logs, then disconnects for each message.
Security is probably the largest issue as it does not require the Unix process to connect to Oracle. This can be partially alleviated with careful planning (owner/group/privs) but is still limited by the Unix security mechanism. It is also necessary to keep the data size less than the pipe buffer size (PIPE_BUF) so as to prevent any interleaving of the client writes.
This framework can be expanded to handle other scenarios. By modifying the parsing routine/text protocol it would be possible to execute code or DML statements with the use of dynamic sql. Multiple pipe names can be setup and polling jobs can be initiated through database triggers when the database is started. It is even possible to have handlers registered to certain codes and executed via dynamic sql or separate the reading from the process, e.g. log all commands to a table, then have another job that polls and executes the commands from the table.
Example Code
Sample Oracle Polling Procedure
This procedure is a simple example of polling from a named pipe. The pipe name is supplied and the procedure opens the pipe for reading.
At this point unless a Unix program tried writing to the pipe Oracle would be blocked and the procedure would sleep at the open command.
When the data is written to the pipe (e.g. echo "message" > pipe) the procedure enters the loop, receives the line via the get_line command
and stores it in the buffer. If the buffer matches the quit command (defaulting to EXIT from the parameter) then the procedure saves any
data and cleans up the open file, otherwise it inserts the buffer into the table. When oracle receives an EOF character it raises the
exception 'NO_DATA_FOUND', enters the exception routine, closes the pipe and reopens it. It may be blocked again at this point, only to
re-enter the loop upon receiving more data.
CREATE OR REPLACE PROCEDURE poll_unix_pipe1 (v_pipe VARCHAR2, v_quit_cmd VARCHAR2 DEFAULT 'EXIT')
IS
line_size CONSTANT PLS_INTEGER := 32767;
utl_file_dir CONSTANT VARCHAR2 (100) := '/ora_dump/oramail';
v_file_handle UTL_FILE.file_type;
v_buffer VARCHAR2 (32767);
v_counter PLS_INTEGER := 1;
BEGIN
-- Opens unix pipe and blocked (sleeps)
-- Only continues after text sent to pipe e.g. print "hello world" > pipe_name
v_file_handle := UTL_FILE.fopen (utl_file_dir, v_pipe, 'r', line_size);
LOOP
BEGIN
-- When end of file will throw NO_DATA_FOUND exception so part of program
UTL_FILE.get_line (v_file_handle, v_buffer);
EXIT WHEN v_buffer = v_quit_cmd;
-- This example inserts text directly to log table but may want to send to
-- procedure to parse a formatted string if used by more than one unix process.
INSERT INTO LOG
(log_text,
TIMESTAMP
)
VALUES (v_buffer,
SYSDATE
);
-- Incremental commit
IF MOD (v_counter, 5000) = 0
THEN
COMMIT;
END IF;
v_counter := v_counter + 1;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
COMMIT;
-- Have to close file handle to reset read pointer (unlike C)
UTL_FILE.fclose (v_file_handle);
-- Opens unix pipe and blocked (sleeps)
v_file_handle := UTL_FILE.fopen (utl_file_dir, v_pipe, 'r', line_size);
WHEN VALUE_ERROR
THEN
-- Data exceeds 32767 bytes which is pretty unlikely.
-- Could die or skip depending on application requirements
-- This example will die and cascade out unhandled
UTL_FILE.fclose (v_file_handle);
RAISE;
END;
END LOOP;
COMMIT;
-- Cleanup file handle
UTL_FILE.fclose (v_file_handle);
EXCEPTION
WHEN UTL_FILE.invalid_path
THEN
raise_application_error (-20000, utl_file_dir || ' is an invalid directory');
WHEN UTL_FILE.invalid_operation
THEN
raise_application_error (-20001, utl_file_dir || '/' || v_pipe || ' is an invalid pipe');
END poll_unix_pipe1;
/
Sample C Read Process Monitor Program
This program is used to check if a process has opened the named pipe for reading. This may be necessary as it is possible for a write
process to be blocked in the case where the polling procedure has crashed. The program will return a 1 if there is no process reading and a
0 if there is. If the supplied file is not accessible or is not a named pipe then it will return an error with a return code 2.
#include <stdio.h>
#include <fcntl.h>
#include <errno.h>
#include <sys/stat.h>
char *program_name; /* name of the program (for errors) */
/*********************************************************
* usage -- Tells the user how to use this program and *
* exit. *
********************************************************/
void usage(void)
{
fprintf(stderr,"usage: %s pipe_name\n", program_name);
exit (2);
}
int main( int argc, char *argv[] )
{
char *pipe_name; /* name of pipe */
int pipe_descriptor;
int stat_descriptor;
struct stat file_stat;
/* save the program name for future use */
program_name = argv[0];
pipe_name = argv[1];
/* Check arguments */
if ( argc != 2 )
{
usage();
}
/* Retrieve file statistics to see if named pipe */
/* Some form of error with file */
if (stat_descriptor = stat(pipe_name, &file_stat) == -1)
{
perror (program_name);
exit(2);
}
/* File mode not named pipe */
else if (! S_ISFIFO(file_stat.st_mode))
{
fprintf (stderr, "%s: %s must be a named pipe!\n", program_name, pipe_name);
exit(2);
}
else
{
/* Open named pipe using NONBLOCK in case no process reading from other end */
if ((pipe_descriptor = open(pipe_name, O_WRONLY | O_NONBLOCK)) == -1)
{
/* No process reading from pipe so don't display error */
if ( errno == ENXIO )
exit(1);
else
{
perror (program_name);
exit(2);
}
}
/* Process reading from other end so ok to write */
else
{
close(pipe_descriptor);
exit(0);
}
}
}