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('¶meter_name',parameter_name)
and a.instance_number like nvl('&instance_number',v.instance_number)
)
where
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;
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 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'
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.
By Kamran Agayev Agamehdi
Oracle DBA and
Application Developer (9i OCA, 10g OCP)
Baki Electrical Distribution
Grid