Tracking & auditing changes to your init.ora parameters
Article by author Kamran Agayev Agamehdi
Kamran
Agayev Agamehdi is an Oracle Certified Professional DBA (9i,
10g) with over 5 years experience with UNIX Systems and with
Oracle Databases and author of
Oracle Backup & Recovery by Rampant Tech Press.
A very important auditing task for any
production database is the ability to track changes to the
powerful initialization parameters. Many of these
parameters are "silver
bullets", single parameters that have a profound impact on
system-wide behavior. This is especially true for SQL
optimizer changes.
A single
change to an optimizer parameter might effect thousands of
SQL statements, a disaster in a controlled production
environment.
Also see these important notes on
Oracle best practices.
Auditing changes to init.ora parameters
(via pfile or spfile) is an important DBA
task. Sometimes, users which have “alter
system” privilege can make unauthorized changes to the
initialization parameters in the spfile on a production
database. Hence, auditing changes to parameters is a
critical DBA task. Fortunately, it's quite simple to audit
these changes by implementing the audit_sys_operations=true.
Here is a method to track changes to the
initialization parameters. In order
to track all changes to parameters we can use audit for
the alter system statement for any specific user
We should follow below steps to track
changes to init.ora parms:
1.
ALTER SYSTEM SET
audit_trail=db SCOPE=SPFILE;
2.
SHUTDOWN IMMEDIATE
3.
STARTUP
4.
CREATE USER TEST
IDENTIFIED BY TEST;
5.
GRANT DBA TO TEST;
6.
AUDIT ALTER SYSTEM BY
test;
7.
CONN TEST/TEST
8.
ALTER SYSTEM SET
AUDIT_TRAIL=db SCOPE=SPFILE;
9.
Create an alert script to notify the DBA when a parameter has
changed.
Let's start by finding the action_name
in the dba_audit_trail view for the alter system
command:
SQL> select username, timestamp, action_name
from dba_audit_trail;
USERNAME
TIMESTAMP ACTION_NAME
------------------------------ ---------
----------------------------
TEST
29-MAY-09
ALTER SYSTEM
STEP 1 - We can track changes made by SYS
user by setting audit_sys_operations parameter to TRUE.
SQL> alter system set audit_sys_operations=true
scope=spfile;
System altered.
STEP 2 - Next, we bounce the instance
to make the change take effect:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area
285212672 bytes
Fixed Size
1218992 bytes
Variable Size
92276304 bytes
Database Buffers
188743680 bytes
Redo Buffers
2973696 bytes
Database mounted.
Database opened.
Here we see our auditing parameters:
SQL> show parameter audit
NAME
TYPE
VALUE
------------------------------------ -----------
------------------------------
audit_file_dest
string
/home/oracle/oracle/product/10
.2.0/db_1/admin/kam/adump
audit_sys_operations
boolean
TRUE
audit_syslog_level
string
audit_trail
string
DB
SQL> alter system set audit_trail=db scope=spfile;
System altered.
STEP 3 - Here we go to the adump directory and
examine the audit files:
SQL> host
[oracle@localhost bin]$ cd
/home/oracle/oracle/product/10.2.0/db_1/admin/kam/adump/
[oracle@localhost adump]$ ls
ora_5449.aud
ora_5476.aud
ora_5477.aud
ora_5548.aud
ora_5575.aud
ora_5576.aud
[oracle@localhost adump]$ cat
ora_5576.aud
Audit file /home/oracle/oracle/product/10.2.0/db_1/admin/kam/adump/ora_5576.aud
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 -
Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1/
System name:
Linux
Node name:
localhost.localdomain
Release:
2.6.18-92.el5
Version:
#1 SMP Tue Jun 10 18:49:47 EDT
2008
Machine:
i686
Instance name: kam
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 5576, image: oracle@localhost.localdomain
(TNS V1-V3)
Fri May 29 02:38:30 2009
ACTION : 'alter system set audit_trail=db
scope=spfile'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/2
STATUS: 0
STEP 4 - Now, create a crontab job to seek
new entries in the adump directory.
#******************************************************
#
list the full-names of all possible adump files . . . .
#******************************************************
rm -f /tmp/audit_list.lst
find $DBA/$ORACLE_SID/adump/*.trc
-mtime -1 -print >> /tmp/audit_list.lst
STEP 5 - When found, send the DBA an
e-mail:
#
If initialization paramneter has changed, send an e-mail
if [ -f
/tmp/audit_list.lst]; then
then
#
Now, be sure that we don't clog the mailbox.
#
the following statement checks to look for existing mail,
#
and only sends mail when mailbox is empty . . .
if
[ ! -s /var/spool/mail/oramy_sid ]
then
cat /oracle/MY_SID/scripts/oracheck.log | mail oramy_sid
fi
sendmail . . .
see code depot for
complete script
fi
Please beware that using the auditing command imposes
additional work on the production database.
If you do not wish to write your own alert mechanisms, see
the book Oracle
Shell Scripting. You can also down the
Oracle script
collection, over 600 working Oracle scripts.
|
