Tuesday, July 10, 2012

How to absolutely panic a DBA using AUDIT_SYSLOG_LEVEL

I'm teaching the Oracle Database 11gR2: Security course this week.

During the auditing module, students are asked to enable auditing, and then configure to send audits to syslogd.  The steps are simple:  tell the database to send audits to OS; tell the database to flag the audits as a specific syslog facililty and level.  These two items are in the initialization parameter file, and you restart the DB after making the change.

The challenge:  get the facility right OR ELSE!

If the syslog facility or the message level being requested is not valid, the database instance will NOT restart.

Heck - it won't even MOUNT. 

So there is NO WAY of fixing the now-corrupt SPFILE using the ALTER SYSTEM command.

So how did we get around this?

In the classroom Linux environment, the spfile is stored in ASM.  So we took the following steps to recover for instance XYZ:

  • configure to access ASM by using ". oraenv" (dot space oraenv) and enter "+ASM"
  • use "asmcmd" to get to the SPFILE (as identified in the ifile line of the initXYZ.ora)
  • copy the spfile to the OS using "cp {spfile} /home/oracle/initXYZ.ora"
  • edit the wierd initXYZ.ora to remove the binary lines and to fix the AUDIT_SYSLOG_LEVEL  information
  • configure back to access the database instance
  • STARTUP MOUNT pfile=/home/oracle/initXYZ.ora
  • CREATE SPFILE='{ASM location of SPFILE}' from MEMORY;
  • SHUTDOWN IMMEDIATE;
  • STARTUP
 I'm more than a bit surprised that an unknown syslog facility setting would stop getting into the MOUNT state.  And this one could cause an extended outage for a relatively lightweight reason.

1 comment:

Aman Sharma said...

Wow, that's a very annoying thing. It would be interesting to see that what actually makes it happen?

Thanks for bringing it out Hans :) .