Comparing Oracle Databases: Initialization Parameters

Sometimes I encounter a setup with no change and configuration management process. In short, they might end up every six months or so with significant discrepancies between their pre-production or acceptance environments and the production one. What makes things worse, is that even production is sometimes the environment that is not kept clean. Therefore, they have a need to design a simple flow that would allow any technical project member with access to check periodically for the parameters that are different between those three environments (one Prod and two Pre’Prod).

Workflow Restrictions

• The process must de run from a client workstation: there is no server access
• There is no command line access to the database server, no SQL*Plus, no sqlcli
• Basically, the only client application we can use is Oracle SQL Developer!

In this post, we will compare the database initialization parameters between three environments.

Easy Steps to Follow

• Identify the staging environment to be used for storing the configuration data of all environments involved. Usually, the recommendation is to use the lowest environment and bring there the data from the upper environments.
• In the staging environment, create the empty tables to store the data from the other environments

create table prod_par as
select inst_id, num, name, value from gv$parameter where 1=0; 
create table acc_par as
select inst_id, num, name, value from gv$parameter where 1=0; 

• In all the upper environments, run the below query in Oracle SQL Developer. Make sure all rows are retrieved by pressing Ctrl+A or equivalent on Mac.

select
    inst_id,
    num,
    name,
    value
from
    gv$parameter p
where
    p.name not in ( 'log_archive_config', 
                    'log_archive_format', 
                    'cluster_interconnects', 
                    'service_names',
                    'core_dump_dest',
                    'audit_file_dest',
                    'dispatchers',
                    'db_name',
                    'db_unique_name',
                    'db_domain',
                    'instance_name' );

• Right click on the results and choose Export. We will export to Excel.

• Choose a suitable location and name for the Excel file. Repeat the same procedure for all other environments except the environment that is used for staging

• In the staging environment, right click the tables created above and choose Import in order to import the data:
• Open the Import screen and choose the excel file created above:
• Click Next and choose as Import Method the Insert Script:
• Select all the columns displayed, do not alter the order:
• Click Next. Keep the Match by Name option.
• Click Next and Finish. You should get and pop up confirming that the import was done and committed. As well, A file with the INSERT statements is created:
• Repeat the Import steps with all the other environments. Same process: export the parameters to an Excel and import the Excel data into the staging database’s corresponding _PAR table
• After this process is complete and we have all the data into the staging database, we are ready to run a query that will compare the parameters values in all tables and return the rows with parameters that have different values between environments:

select                p.name,
                   p.inst_id,
          p.value PROD_VALUE,
   g.value ACCEPTANCE$_VALUE,
   a.value ACCEPTANCE_VALUE
from   prod_par p,
        acc_par a, 
   gv$parameter g
where (p.num = g.num
   and a.num = g.num)
and   (p.inst_id = g.inst_id
   and a.inst_id = g.inst_id)
and (p.value != g.value
  or a.value != g.value
  or p.value != a.value)
and p.name not in (
         'log_archive_config',      
         'log_archive_format',
      'cluster_interconnects',
              'service_names',
             'core_dump_dest',
            'audit_file_dest',
                'dispatchers',
                    'db_name',
             'db_unique_name',
                  'db_domain',
              'instance_name',
       'db_file_name_convert',
     'dg_broker_config_file1',
     'dg_broker_config_file2',
          'listener_networks',
             'local_listener',
         'log_archive_dest_2',
      'log_file_name_convert',
            'undo_tablespace',
            'remote_listener')
order by p.name,
      p.inst_id;

• The results of this query can now be exported to an Excel (Right click on the results and choose Export) and processed further:

Tested on:

Oracle Database Enterprise Edition 19c