Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Tracking Oracle initialization parameter changes

Oracle Tips by Kamran Agayev Agamehdi
May 29,  2015

Providing a history of changes to your Oracle database parameters is not only a DBA best practice, it's absolutely required in a mission-critical production environment.  Back when Oracle parameters were kept in a flat file on the server (the init.ora file) tracking changes was difficult.

However, once Oracle introduced the "spfile" feature it became easier to track changes to initialization parameters.  Below are two common techniques for tracking Oracle initialization parameters, auditing, and using the extra-cost AWR method.

Note:  If you have purchased the extra cost performance pack and diagnostic pack (and have access to the AWR dba_hist_parameter table), it's easy to run a script to track all changes to your initialization parameters. 

Oracle guru Kerry Osborne offers this nice method for tracking init.ora parm changes:

-- parm_mods.sql
-- Shows all parameters (including hidden) that have been modified.
-- Uses the lag function so that a single record is returned for each change.
-- It uses AWR data - so only snapshots still in the database will be included.
-- The script prompts for a parameter name (which can be wild carded).
-- Leaving the parameter name blank matches any parameter (i.e. it will show all changes).
-- Calculated hidden parameters (those that start with two underscores like "__shared_pool_size")
-- will not be displayed unless requested with a Y.
-- Kerry Osborne
-- Note: I got this idea from Jeff White.
set linesize 155
col time for a15
col parameter_name format a50
col old_value format a30
col new_value format a30
break on instance skip 3
select instance_number instance, snap_id, time, parameter_name, old_value, new_value from (
select a.snap_id,to_char(end_interval_time,'DD-MON-YY HH24:MI') TIME, a.instance_number, parameter_name, value new_value,
lag(parameter_name,1) over (partition by parameter_name, a.instance_number order by a.snap_id) old_pname,
lag(value,1) over (partition by parameter_name, a.instance_number order by a.snap_id) old_value ,
decode(substr(parameter_name,1,2),'__',2,1) calc_flag
from dba_hist_parameter a, dba_Hist_snapshot b , v$instance v
where a.snap_id=b.snap_id
and a.instance_number=b.instance_number
and parameter_name like nvl('&parameter_name',parameter_name)
and a.instance_number like nvl('&instance_number',v.instance_number)
new_value != old_value
and calc_flag not in (decode('&show_calculated','Y',3,2))
order by 1,2


Oracle tip by Kamran Agayev Agamehdi , author of Oracle Backup & Recovery.

A very important auditing task for any production database is the ability to track changes to the powerful initialization parameters.  Many Oracle parameters are "silver bullets", single parameters that have a profound impact on system-wide behavior.  This is especially true for changes to the optimizer parameters. 

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;


3.       STARTUP



6.       AUDIT ALTER SYSTEM BY test;



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;

------------------------------ --------- ----------------------------
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
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 - 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 2015
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 2015
ACTION : 'alter system set audit_trail=db scope=spfile'
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
    # 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 ]
       cat /oracle/MY_SID/scripts/oracheck.log | mail oramy_sid 
    sendmail  . . .
    see code depot for complete script


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.

By Kamran Agayev Agamehdi
Oracle DBA and Application Developer (9i OCA, 10g OCP)
Baki Electrical Distribution Grid



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational