Oracle 11.2 Managing Alert log and Trc files

Oracle now want you to use adrci to manage the alert log and trace files.

It's worth getting to grips with too, but it's also worth knowing that adrci doesn't actually control the alert_${ORACLE_SID}.log.  It will control the xml logs and the .trc files but the .log is still your own problem!

Put this as part of a shell script to look after your Alert xmls, incidents, trace files, core dumps and health monitor data and reports (remember them)

###

##adrci purge -age in_minutes -type ALERT

## 6 months = 267840, 3 months = 129600

export ORACLE_HOME=/set/my/path/if/it/wasn't/already

echo INFO: adrci purge started at `date +%D-%T`

$ORACLE_HOME/bin/adrci exec="show homes"|grep -v : | grep -v tnslsnr | while read file_line

do

echo INFO: adrci purging diagnostic destination $file_line

echo INFO: purging ALERT xml

$ORACLE_HOME/bin/adrci exec="set homepath $file_line;purge -age 267840 -type ALERT"

echo INFO: purging INCIDENT

$ORACLE_HOME/bin/adrci exec="set homepath $file_line;purge -age 129600 -type INCIDENT"

echo INFO: purging TRACE

$ORACLE_HOME/bin/adrci exec="set homepath $file_line;purge -age 129600 -type TRACE"

echo INFO: purging CDUMP

$ORACLE_HOME/bin/adrci exec="set homepath $file_line;purge -age 129600 -type CDUMP"

echo INFO: purging HM

$ORACLE_HOME/bin/adrci exec="set homepath $file_line;purge -age 129600 -type HM"

done

###

This will look after your logs by rolling them each time the script is run:

#### BEGIN

## alert log text file

export ORACLE_HOME=/set/my/path/if/it/wasn't/already

export ORACLE_SID=MYSID

$ORACLE_HOME/bin/sqlplus -s / as sysdba << EOF | tee /tmp/diagdest

set echo off;

SET SERVEROUTPUT ON;

set heading off;

select value from v\$diag_info where name='Diag Trace';

exit

EOF

diagdest=`cat /tmp/diagdest`

ALERT_LOG_PATH=$diagdest

if [ -f ${ALERT_LOG_PATH}/alert_log_trim ]

then

echo "alert log ${ALERT_LOG} already being trimmed don't try to do it again it really messes things up"

###I have a function here that sends the error off to my alerting system you could just exit 1

exit 1

fi

touch ${ALERT_LOG_PATH}/alert_log_trim

ALERT_LOG=${ALERT_LOG_PATH}/alert_${ORACLE_SID}.log

echo ALERT_LOG=$ALERT_LOG

if [ ! -f ${ALERT_LOG} ]

then echo "alert log does not exist error "

rm ${ALERT_LOG_PATH}/alert_log_trim

###I have a function here that sends the error off to my alerting system  you could just exit 1

exit 1

fi

ls -l ${ALERT_LOG}*

## Roll the text based alert logs, remember adrci ony deals with the xml version of the log

### if you ran this once a month you'd have 3 months worth stored away

cp -p ${ALERT_LOG}.old3 ${ALERT_LOG}.old4

cp -p ${ALERT_LOG}.old2 ${ALERT_LOG}.old3

cp -p ${ALERT_LOG}.old1 ${ALERT_LOG}.old2

cp -p ${ALERT_LOG} ${ALERT_LOG}.old1

cd ${ALERT_LOG_PATH}

echo "cat /dev/null > alert_${ORACLE_SID}.log"

cat /dev/null > alert_${ORACLE_SID}.log

ls -l ${ALERT_LOG}*

rm ${ALERT_LOG_PATH}/alert_log_trim

###### END

You need to slot this 2 extracts into a proper shell script with appropriate error reporting etc.


I then cron my script to run on the first Wednesday of every month:
(I don't want to do it by date as it would sometimes run on the weekends.  So by choosing a weekday, if it errors for any reason , I'm probably going to be at work already - unless it's Christmas, I know!)

00 10 * * 3 [ `date +\%d` -le 7 ] && /pth/to/my/script/myscriptname 2>&1

Good Luck, Enjoy!

(I'm sure when I first started to try and get to grips with this I took the adrci putge lines from another blog, but now I have no idea where. If you spot the lines as character for character yours, then thank you for sharing them originally, poimt it out to me and I'll link back to you.)

Read more...

Sanity Lost. Data Sanitization

I woke early in a sweat unable to get back to sleep and unable to get out of my mind what I'd dreamt. My daughter was taken away by the Social Services for her own protection.

I had been working on a local authority test database that held the information relating to calls made about child neglect. Whilst testing the application I had used COGs name and entered 9 different calls. The test environment had been promoted to production but the test data hadn't been removed. COG had been reported out by the system and they took her away no matter how much I argued the case.

After waking I lay for a while trying to decide what I would do. I assumed if the test data was promoted live so would the test users. I would simply delete the 9 calls and and and...it was all too much, it's left me upset about it since. (I always try to work out happy endings if I wake from a bad dream, don't you?)

But this isn't just a far fetched dream. We often worry about protecting live data from being used in development and preproduction, obfuscating and sanitising a frenzy. But simply building production databases from preproduction copies is as bad in my book and yet how often do we have to do just that in a massive JFDI rush.

People ( us ITites anyway) often talk about Bobby Tables and sanitising data input, but developers and DBAs create an internal risk to data that we often ignore. When moving systems to production any static data promoted needs to be reconsidered. But when the data and definitions are in a foreign language how am I to even sense check it? I must rely on developers and that leaves me worrying.

This dream has really shaken me firstly as a mother but secondly as a professional wanting to maintain the integrity of the data I'm responsible for. Hate it when work gets to your core like this.

Read more...

Oracle 11.2 automatic .aud file maintenance

For Oracle on unix .aud files are created whether you have auditing enabled or not.  They record sys operations.  If you've got a lot of shell scripts that connect / as sysdba you are going to get a lot of .aud files. (In windows they are written to the event viewer)

They will go to where you set audit_file_dest (aka adump) to be.  If you don't set adump the first default value is ORACLE_BASE/admin/ORACLE_SID/adump. If this doesn't exist then they will go in ORACLE_HOME/rdbms/audit

If you didn't know about this they tend to raise their heads when $ORACLE_HOME fills up and you wonder why.

Please be clear, I am talking about the OS .aud files.  This process will not touch $aud in your database.


Oracle at 11.2  provide a way to manage these .aud OS files using the audit management package DBMS_AUDIT_MGMT

Here's how:

Initialize DBMS_AUDIT_MGMT

Call just once the initialization procedure INIT_CLEANUP to set up the audit management infrastructure.

BEGIN
 DBMS_AUDIT_MGMT.INIT_CLEANUP(
  AUDIT_TRAIL_TYPE            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
  DEFAULT_CLEANUP_INTERVAL    => 24 );
END;
/



Create the Procedure to delete files (over a year old) for a single instance

CREATE OR REPLACE procedure SYS.delete_OSaud_files
is
ThisProc             VARCHAR2(30) := 'delete_OSaud_files';
ThisAppErr            NUMBER := -20000;
BEGIN
  DBMS_AUDIT_MGMT.set_last_archive_timestamp(
    audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
    last_archive_time => SYSTIMESTAMP-366);
 dbms_audit_mgmt.clean_audit_trail(
   audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
   use_last_arch_timestamp => TRUE
  );
EXCEPTION
WHEN OTHERS THEN
   dbms_output.put_line(ThisProc||' - '||SQLERRM);
   ROLLBACK;
   RAISE_APPLICATION_ERROR(ThisAppErr, ThisProc);
END;
/

Create the Procedure to delete files (over a year old) for RAC

CREATE OR REPLACE procedure SYS.delete_OSaud_files
is
ThisProc             VARCHAR2(30) := 'delete_OSaud_files';
ThisAppErr            NUMBER := -20000;
BEGIN
  DBMS_AUDIT_MGMT.set_last_archive_timestamp(
    audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
    last_archive_time => SYSTIMESTAMP-366,
    rac_instance_number => 1);
   DBMS_AUDIT_MGMT.set_last_archive_timestamp(
    audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
    last_archive_time => SYSTIMESTAMP-366,
    rac_instance_number => 2);
 dbms_audit_mgmt.clean_audit_trail(
   audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
   use_last_arch_timestamp => TRUE);
EXCEPTION
WHEN OTHERS THEN
   dbms_output.put_line(ThisProc||' - '||SQLERRM);
   ROLLBACK;
   RAISE_APPLICATION_ERROR(ThisAppErr, ThisProc);
END;
/


If you don't need to keep a years worth, just change SYSTIMESTAMP-366


Feel free to moan at me about 'when others then' and post a better procedure in the comments - I'll readily admit my plsql is not what it should be and I'm happy to be corrected.


Create a Schedule
(I like this type of thing to run when I'm actually working so I don't get called out of hours if something goes wrong)

Begin
DBMS_SCHEDULER.CREATE_SCHEDULE (
   schedule_name   => 'DELETE_OSAUD_FILES_SCHED',
      repeat_interval =>'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=10;',
      comments        => 'Delete adump files');
END;
/

Schedule the Job

BEGIN
dbms_scheduler.create_job (
job_name =>'DELETE_OSAUD_FILES_JOB',
job_type =>'STORED_PROCEDURE',
job_action => 'SYS.delete_OSaud_files',
enabled    =>  TRUE,
auto_drop => false,
schedule_name =>  'DELETE_OSAUD_FILES_SCHED',
comments => 'Remove aud files from adump');
END;
/


Set up mail notifications
(I like to know when my jobs error)

BEGIN
DBMS_SCHEDULER.set_scheduler_attribute('email_server', 'smtp.mycompany.com:25');
DBMS_SCHEDULER.set_scheduler_attribute('email_sender', 'do_not_reply@mydatabase');
END;
/

Create the events that I want mailing about

to test notifications work first set up events for everything

BEGIN
 DBMS_SCHEDULER.add_job_email_notification (
  job_name   =>  'DELETE_OSAUD_FILES_JOB',
  recipients =>  'me@mycompany.com',
  events     =>  'job_all_events');
  END;
  /

run your job

BEGIN
dbms_scheduler.run_job (
job_name =>'DELETE_OSAUD_FILES_JOB');
END;
/

You should get an email saying it ran.
But you don't want an 'I ran' mail every day, I only want to know if it's failed so:

BEGIN
 DBMS_SCHEDULER.remove_job_email_notification (
  job_name   =>  'DELETE_OSAUD_DATA_JOB',
  recipients =>  'me@mycompany.com',
  events     =>  'job_succeeded');
END;
/
BEGIN
 DBMS_SCHEDULER.remove_job_email_notification (
  job_name   =>  'DELETE_OSAUD_DATA_JOB',
  recipients =>  'me@mycompany.com',
  events     =>  'job_started');
END;
/
 BEGIN
 DBMS_SCHEDULER.remove_job_email_notification (
  job_name   =>  'DELETE_OSAUD_DATA_JOB',
  recipients =>  'me@mycompany.com',
  events     =>  'job_completed');
END;
/


Which is the equivalent of :

BEGIN
 DBMS_SCHEDULER.add_job_email_notification (
  job_name   =>  'DELETE_OSAUD_DATA_JOB',
  recipients =>  'me@mycompany.com',
  events     =>  'job_broken,job_chain_stalled,job_completed,job_disabled,job_failed,
job_over_max_dur,job_sch_lim_reached,job_stopped');
  END;
  /



So now your .aud files will be kept in check for you. 

Read more...