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...

ORA-04063: SYS.SCHEDULER$_EVENT_QUEUE has errors

I've been having a bit of a nightmare with what should have been a straight forward 11.2.0.2 upgrade from 10.2.0.4.

The dbua was falling over at about 64% with:

Problem Description:
ERROR at line 1:
ORA-04063: SYS.SCHEDULER$_EVENT_QUEUE has errors
ORA-06512: at "SYS.DBMS_AQADM", line 270
ORA-06512: at line 2

This seems to be linked to me using in 10.2 the mailjobevent and job_notification packages supplied by Oracle.

My final solution was (**PLEASE SEE UPDATE AT BOTTOM OF POST **):

as sys
drop procedure mailjobevent;
@?/rdbms/admin/catnosch.sql
@?/rdbms/admin/catsch.sql
@?/rdbms/admin/utlrp.sql
select * from dba_objects where status <> 'VALID';

You need zero rows returned before starting an upgrade.

Remember to always ensure you have a good backup before starting,  test on a non critical system before moving to one where it matters if you screw up and follow the upgrade prerequistes in the Oracle documentation.

utl_mail needs work at 11g, as it is different to 10g to I guess that's why my scheduled jobs that use the old packages were unhappy but if I come up with a comprehensive answer to what is the absolute root cause is I will update here, in the meantime I am posting this because I was getting nothing useful in any searches for the error.

*******25/11/2011 UPDATE ******

Please see this comment from Rahul:

I had run in to the same issue but oracle gave me resolution after work around and rerunning the catupgrd.sql went fine.
This "ORA-04063: SYS.SCHEDULER$_EVENT_QUEUE has errors" is Intermittent issue/defect during the upgrade.
if you encounter ,you can follow the workaround suggested.
This is fixed in 11.2.0.2

Work around:

exec dbms_aqadm_sys.validate_queue('SYS', 'SCHEDULER$_EVENT_QUEUE');
.
after this, exec dbms_aqadm.start_queue(queue_name =>'sys.scheduler$_event_queue');
and rerunning the catupgr.sql

I have not had the opportunity to try this but it would be worth giving it a go in your test system before resorting to dropping your mailjobevents like I did.

Read more...

Backing up a Blogger Blog

Why would you want to backup your blog?  Under the normal course of events you probably don't need to. Google will have a level of resilience in their servers that means you aren't likely to lose your blog through any fault of theirs (or if you do so will lots of others!).

But what Google don't protect you against is you. It's really easy to play around with your template and mess it up and it's really quite easy to delete a blog by mistake.  A backup and restore is also a really easy way to create a test blog that you can play around with. If you don't understand why you would want to do that then you are probably blogging because you are a budding writer.  I am a IT person, I have 8 blogs of which 5 are currently test beds for playing.  Mostly I prefer the play to the posting!

There are 2 types of Blogger backup, your template and then your actually content (posts).

TEMPLATE
The template is the html code that controls the look and feel of your blog.  This is what you are changing when you add gadgets or change your background colour.

Design >edit html>download full template

Then click save, save the .xml file into a place where you will remember where it is.  I have a folder called template backups.  I also always change the start of the file name to be something meaningful to me.  Like 'KV_workingsummer2011.xml'.  It's long but I will know what it means if I need to restore later.

There is another way to save your template too.

Design tab>edit html.  Make sure you click the 'expand widget templates' so that it has a tick in it.  Now highlight all that text from start to end. Copy  and paste it into Notepad and save as a .txt file. This is really useful if you want to play around yourself with your code.

CONTENT

To backup your posts go to Settings>Basic>Export Blog

Now 'Download Blog'

Click Save.  Save it to a folder where you will remember where it is.  Mine is called BlogBackups.  

If you use 'jumpbreaks' the export blog function has a bug in it.  It only exports your posts up to the jump break.  The work around for this is to Settings>Site Feed Change allow blog feeds to 'Full'.

Do the export and then change it back again to 'Until Jump Break'

Blogger are aware of this issue but I don't know if they have fixed it yet, if you use jump breaks you might want to test it.  It would be easy to import into a test blog and see if the full post is there.

COMMENTS

If you let Blogger manage your comments then they will be included in the export but my commenting software is IntenseDebate so my comments are not backed up as a part of the Blogger Export.  To backup my comments I have to go to the IntenseDebate site and generate an xml file.  It is easy to do, just follow the links.  Discus will have something similar.

Read more...

How to create an 'About Me' page using Blogger

Before ever making changes to your blog CREATE A BACKUP.

From the top Design tab, choose 'edit html', then click download full template

Then click save, save the .xml file into a place where you will remember where it is.  I have a folder called templatebackups.  I also always change the start of the file name to be something meaningful to me.  Like 'KV_workingbeforepages_template-5184955426276325164.xml'.  It's long but I will know what it means if I need to restore later.

Pages are different to blog posts, they don't sit in your time line, they are stand alone.
Create a page from your posting tab, instead of clicking on the New Post tab, go to Edit Pages (even if it is your first)

Once you have clicked on 'New Page', the editing is the same as if you were working on a post.  But  this is going to be static information for someone to come and find out about who you are. 

Once you have clicked publish page, if it is your first, you should get a page like this.  This allows you to add either tabs to your blog, each of which are your pages plus a home, or a side bar list.   

If your blog template already includes a 'Home' tab you may find your new pages are automatically added as new tabs (if you need to manage this further look here)

If you need to create the tabs yourself, because you missed the initial add a gadget option or your template doesn't already have them, then simply add the Blogger Pages gadget.  Go to the top level Design tab, then page elements and click on Add Gadget. 

The pages gadget is one of the basics, see it 4th one down here.

Don't worry about placement, once you've added the gadget, you can drag it around. Towards the top for tabs, or into the sidebar for a side list.

 
 
 
 
 
 
You might not want to include all pages in this gadget.  I use a stand alone page for my Disclaimer.
 

A link to a page is easily placed by using the html/java script gadget. With a link to your page in it. 
 
 
<a href="http://kelloggsville.blogspot.com/p/disclosure-of-material-connection.html">Disclosure Policy</a> 
 

To find the link address just view the page and copy and paste the url from your browser bar.


 
 
 
 
 
I hope you find this useful.

Read more...

Checker run found %d new persistent data failures

I have been getting the warning "Checker run found %d new persistent data failures" in my alert log when renaming datafiles but I struggled to get information about it's source and whether there was a true issue.

The Oracle® Database Administrator's Guide 11g Release 2 (11.2) (Chapter 9 Managing Diagnostic Data, Running Health Checks with Health Monitor) covers the Health Monitor but I didn't find it very helpful.


I managed to recreate the error in test:

CREATE TABLESPACE TEST DATAFILE
'/u03/oradata/test01.dbf' SIZE 64M AUTOEXTEND OFF  LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 32K
SEGMENT SPACE MANAGEMENT AUTO  FLASHBACK ON;
alter tablespace test offline;
cp '/u03/oradata/test01.dbf' '/u03/oradata/test_new01.dbf'
alter tablespace test rename datafile '/u03/oradata/test01.dbf' to '/u03/oradata/test_new01.dbf';
SQL> begin
DBMS_HM.RUN_CHECK('DB Structure Integrity Check', 'my_run');
 end;
 /
PL/SQL procedure successfully completed.
SQL> SELECT DBMS_HM.GET_RUN_REPORT('my_run') FROM DUAL;
alter tablespace test online;
drop tablespace test including datafile;

The alert log reported:
Checker run found 1 new persistent data failures

The report was:
SQL> SELECT DBMS_HM.GET_RUN_REPORT('my_run') FROM DUAL;

DBMS_HM.GET_RUN_REPORT('MY_RUN')
------------------------------------------------------------------------------
Basic Run Information
Run Name : my_run
Run Id : 6421
Check Name : DB Structure Integrity Check
Mode : MANUAL
Status : COMPLETED
Start Time : 2011-07-04 16:19:42.391361 +01:00
End Time : 2011-07-04 16:19:42.423941 +01:00
Error Encountered : 0
Source Incident Id : 0
Number of Incidents Created : 0

Input Paramters for the Run
Run Findings And Recommendations
Finding
Finding Name : Offline Datafile
Finding ID : 6425
Type : FAILURE
Status : OPEN
Priority : HIGH
Message : Datafile 32: '/u03/oradata/test_new01.dbf' is offline
Message : Some objects in tablespace TEST might be unavailable
Finding
Finding Name : Tablespace Offline
Finding ID : 6428
Type : FAILURE
Status : OPEN
Priority : HIGH
Message : Tablespace 28: 'TEST' is offline
Message : Some objects in tablespace TEST might be unavailable


In this particular case, a tablespace is brought offline which is considered an issue as the data in the tablespace is no longer available.
It is also considered 'persistent' because a offline tablespace is not brought online automatically, it needs manual intervention.

By default the database runs the Health Check on a regular basis.  You can see these reactive runs via:

select * from V$HM_RUN;
If you want to see the results of a run in particular then do:

SET LONG 100000;
SET LONGCHUNKSIZE 1000SET PAGESIZE 1000SET LINESIZE 512SELECT DBMS_HM.GET_RUN_REPORT('HM_RUN_XXXX') FROM DUAL;
Where 'XXXX' is the number of the run taken from name in V$HM_RUN.

As there was limited information coming up in searches on the error, I hope this helps you get a little further with your quest to decipher it.

Read more...