Oracle check parameter differences between Rac instances and nodes

oracle script to  list database parameter difference between 4 rac instances

SELECT p1.inst_id AS instance_id_1,

       p2.inst_id AS instance_id_2,

       p1.name AS parameter_name,

       p1.value AS value_instance_1,

       p2.value AS value_instance_2

FROM gv$parameter p1

JOIN gv$parameter p2 ON p1.name = p2.name AND p1.inst_id != p2.inst_id

WHERE p1.value != p2.value

ORDER BY p1.name, p1.inst_id, p2.inst_id;

SELECT parameter_name,

       COUNT(DISTINCT parameter_value) AS distinct_value_count,

       LISTAGG(instance_id || ': ' || parameter_value, ', ') WITHIN GROUP (ORDER BY instance_id) AS values_per_instance

FROM (

    SELECT p.inst_id AS instance_id,

           p.name AS parameter_name,

           p.value AS parameter_value

    FROM gv$parameter p

    WHERE p.inst_id BETWEEN 1 AND 5

)

GROUP BY parameter_name

HAVING COUNT(DISTINCT parameter_value) > 1

ORDER BY parameter_name;

 

--------------- To check when was  parameter  modified 

-- dba_hist_active_sess_history

SELECT sql_text, user_id, sample_time

FROM dba_hist_active_sess_history

WHERE sql_text LIKE '%ALTER SYSTEM%'

ORDER BY sample_time DESC;

-- audit 

SELECT dbusername, action_name, os_username, userhost, dbid, obj_name, sql_text, event_timestamp

FROM unified_audit_trail

WHERE action_name = 'ALTER SYSTEM'

ORDER BY event_timestamp DESC;

SELECT os_username, username, userhost, terminal, timestamp, owner, obj_name, action_name, sql_text

FROM dba_audit_trail

WHERE action_name = 'ALTER SYSTEM'

ORDER BY timestamp DESC;

-- alert log 

Oracle parameter change history :

SQL> alter system set open_cursors=1000 comment='07-Jan-2012 Changed AJ needed for application XYZ';

$ strings spfileTESTDB.ORA | grep open_cursors

*.open_cursors=400#07-Jan-2012 Changed AJ needed for application XYZ

 

WITH

all_parameters AS (

SELECT snap_id,

       dbid,

       instance_number,

       parameter_name,

       value,

       isdefault,

       ismodified,

       lag(value) OVER (PARTITION BY dbid, instance_number, parameter_hash ORDER BY snap_id) prior_value

  FROM dba_hist_parameter

)

SELECT TO_CHAR(s.begin_interval_time, 'YYYY-MM-DD HH24:MI') begin_time,

       TO_CHAR(s.end_interval_time, 'YYYY-MM-DD HH24:MI') end_time,

       p.snap_id,

       p.dbid,

       p.instance_number,

       p.parameter_name,

       p.value,

       p.isdefault,

       p.ismodified,

       p.prior_value

  FROM all_parameters p,

       dba_hist_snapshot s

 WHERE p.value != p.prior_value

   AND s.snap_id = p.snap_id

   AND s.dbid = p.dbid

   AND s.instance_number = p.instance_number

 ORDER BY

       s.begin_interval_time DESC,

       p.dbid,

       p.instance_number,

       p.parameter_name

/

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;

-- 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)

) 

where 

new_value != old_value

and calc_flag not in (decode('&show_calculated','Y',3,2))

order by 1,2

/