preserve oracle logs

[John Kittel]

I have been handed requirements to preserve some oracle log files. Oracle is throughout the day creating new log files in a directory. It creates a log file in more or less one blast, then is done with it, not continuing to append to it, then a while later creates the next file, etc.

The requirements are that once oracle creates a file, no one other than root be allowed to change or remove the files. Root is to copy the files elsewhere, then remove the originals.

What are some approaches to meeting these requirements?

- John

[TwoProc]

John, probably the easiest I can think of would be an RMAN backup of the archive logs (it can do that), which stores the stuff (encrypted) in the tape library. If you then limit who has access to the tape library (and you should already have done that), then you should be OK with compliance. Now, can it ALWAYS be run as soon as a file lands ? Well, you'd have to poll for the new files, and that would mean running the RMAN archive log backup more often, but is every 5 minutes enough???
I don't know of a way to have the event triggered (maybe there is a way of putting a trigger on a system table...) but if they could give you a guideline of how long can a file be there before it must be moved, and then have your backup interval tighter than that...

Let's say you don't go the RMAN route, and decide to do it "by hand". Keep in mind that if you write some sort of a script that watches the directory to see if new files show up, you'll have to make sure that the file is finished being written to before trying to pick it up and move it. How would you know that it's finished?

A) You could wait until it is the "standard size" of the archive log files. Bzzzt wrong - archive log files can often be smaller than their standard size. Checkpoints, switchlogs, shutdowns, etc. can alter the standard size dramatically.

B) You could wait until another file shows up and assume that the prior file is finished. Bzzzt, that's not necessarily true either. At peak loads, you could be writing multiple of them at a time, finishing some later files before the first ones are done. It's rare (and indicates you need to do some archive log tuning), but possible, I've seen it myself.

C) You could wait until 20 files are written and assume that the last ones are finished. That's probably true, but BZZZT, that violates what you're trying to do - you can't leave 20 files out there w/o tending to them.

D) You could write a script to query from oracle what archive log files are out there, and whether or not they finished with, and have the result of this be used to create a UNIX script that something else can come along and run to move it out. I've done something similar to this (before RMAN), but the best precision you can get is 1 minute from cron, and even then, that's if you don't have files to copy. If you're already copying files, and plenty more files show up while copying... then that puts you behind.

E) Or, hey - I just thought of this - but when an archive file is completed being shifted out to disk - it's written in the Oracle database alert log.
You could write a program to watch that alert log, and if you see:
ARC1: Beginning to archive log 4 thread 1 sequence 32340
Creating archive destination LOG_ARCHIVE_DEST: '/path/to/arch/logs/filename.log'

Then you know a write started,
and keep in looking in the file for:

ARC1: Completed archiving log 4 thread 1 sequence 32340

When you see a sequence with the same number do a "Begin" and then a "Completed" entry in the alert log, you can go ahead with your copy/move/protect operation.

Those are some ideas to think about, but the critical question becomes how long of a period do you have to get those files moved out? The answer to that question tells you something of your approach and methodology.

[James R. Ferguson]

Hi John:

There are a number of approaches here. One way is to start a script that queries the directory with a 'ls -t'. This builds you a list of the directroy in the order of most recently modified first; to least recently modified last.

Now, by skipping the first (most recent) log with 'head' or 'awk' you have the list of files that are *not* currently in-use by oracle and can be moved elsewhere.

The root user has infinite privilege, so if you script runs as root, you can otherwise restrict the query of the directory to Oracle and/or its group.

Regards!

...JRF...

[James A. Donovan]

You may want to check out this thread.

http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=1007790

[John Kittel]

Thanks everyone so far.

Sorry, I suppose I should have mentioned these are not archive logs. They're some other kind of log. I don't know how to describe them. I don't know oracle very well ( well, don't know much about it at all, never needed to, yet.) And DBA has left for the day. He explained, but I didn't understand and /or retain it very well. Also, I asked DBA if there was a way to hook into whatever mechanism oracle is using to trigger creation of these files to add the ability to trigger another process, and he says no.

Thanks John for extensive comments regarding ensuring that the files are done being written before I try to mess with them. I had been wondering exactly about those issues.

Possibly the creation of these logs is also noted in the alert log, and I will be able to use your ( John's) suggetion E approach.

- John

[James A. Donovan]

Oracle itself only generates a few different kinds of logs automatically. The archive logs, the alert log, and from time to time, trace files.

The location where the alert log and trace files are written to is controlled by the init.ora file. This file is located in $ORACLE_HOME/dbs.

The alert log and trace files can be copied/moved at any time without affecting Oracle's operation. Delete the current alert log, and Oracle will just start a new file.

If these are still not the logs you're referring to, then some application process must be generating them, and you can use any method you like to move them without affecting the operation of the database.

[John Kittel]

Thank you James. Again, DBA is not here right now, but I can say they are not archive logs, and not alert log. Oracle is creating them. They are named like this for example: ora_27467.aud , etc.

They contain info like this:

Thu Mar 9 16:32:00 2006
ACTION : 'd (+) = lo.object_id
and s.sid = hl.sid
and rtrim(hu.user_id (+)) = upper(rtrim(s.osuser))'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL:
STATUS: 0

Thu Mar 9 16:32:05 2006
ACTION : 'select 'exit'||chr(13)||chr(10) from dual'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL:
STATUS: 0

[James A. Donovan]

Ahhh....audit logs. Again, they can be copied/moved/deleted at any time. I would think the easiest thing to do would be to cron a script that tar'ed up all the audit logs once a night, gzip the tarball and place it in a secure location. Then you can delete whatever audit logs you just backed up.

[John Kittel]

Yes, except that, silly as it may be, auditors are concerned that once written, the logs should not possibly be altered by oracle or dba accounts / users. So I have to have root get ahold of them and prevent access as soon as they're done being written.

( similar requirements for archive and alert logs will probably come dribbling down to my level eventually as well... )

- John

[James R. Ferguson]

Hi (again) John:

As long as once a file is closed it isn't modifed further (or isn't modified in parallel) after a succeeding file is opened, then the approach I first suggested should be applicable.

You can always add an additional check with 'fuser' to make sure that you don't have any processes associated with a file before you move (or copy) it.

Regards!

...JRF...