Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

 
 

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.
 

 

 

   

 Copyright © 1996 -2017 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks