Check changed parameter history in oracle

Check the history of parameter is changed in past in Oracle Database. If we are facing any specific issue like performance and we want to check any parameter is changed at database level in past few days then following ways help in it.

Following are the few ways to check the parameter changed in past:

1. Check the alert log file.

Search for the alter system command in alert log file.


2. If you have tuning pack then you can check from dba_hist_parameter table with following query.

Refer: http://www.dba-oracle.com/t_tracking_auditing_changes_initialization_parameters.htm

Note: Table has snap id column, you can fetch time from dba_Hist_snapshot table by matching snap_id column


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)
)
where
new_value != old_value
order by 1,2;


3. You can keep track of this changes by enabling audit on ALTER SYSTEM command

-- Enable the Audit
ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
--Enable also for SYS user
ALTER SYSTEM SET audit_sys_operations=true SCOPE=SPFILE;
-- Restart the database for audit parameter in effect.
shutdown immediate
startup
-- Enable the audit on ALTER SYSTEM privileges on user you want.
AUDIT ALTER SYSTEM BY test;