How to Compare Two Oracle Database Parameters Across Environments

Comparing Oracle database parameters across different environments like production and pre-production is crucial for maintaining consistency and preventing unexpected issues. This article provides a step-by-step guide on how to compare these parameters using Oracle SQL Developer, especially useful when direct server access or command-line tools are unavailable.

Comparing Parameters Using SQL Developer and Excel

This method leverages Oracle SQL Developer’s export/import functionality and Excel for a straightforward comparison process. It’s designed for situations where direct server access is restricted.

Setting Up the Staging Environment

Choose a Staging Environment: Select one environment (typically the lowest level, like development) as your staging area. This environment will house the parameter data from all other environments for comparison.
Create Staging Tables: In the staging environment, create empty tables to store parameter data from each environment you want to compare. For example, to compare production and two pre-production environments (acceptance and acceptance$), use the following SQL scripts:
```
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;
create table acc_par as select inst_id, num, name, value from gv$parameter where 1=0;
```

Extracting Parameter Data

Query Parameter Data: In each environment except the staging environment, run the following query in SQL Developer to extract relevant parameters. This query excludes certain dynamic parameters that are expected to differ across environments. Ensure you fetch all results (Ctrl+A or Cmd+A).
```
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'
);
```
Export to Excel: Right-click on the query results and select “Export.” Choose “Excel” as the export format and save the file with a descriptive name (e.g., prod_parameters.xlsx). Repeat this process for each environment.

Importing Data into the Staging Environment

Import Data: In the staging environment, right-click on each of the empty _par tables and select “Import.” Choose the corresponding Excel file you exported earlier.
Configure Import Settings:
- Import Method: Select “Insert Script.”
- Column Selection: Ensure all columns are selected in the correct order.
- Matching: Use “Match by Name.”
Complete Import: Click “Next” and “Finish.” You should receive a confirmation message. An INSERT script file will also be generated. Repeat these steps for each environment’s Excel file.

Comparing the Parameters

After importing all data, run the following query in the staging environment to identify parameters with differing values:

select p.name, p.inst_id, p.value PROD_VALUE, g.value ACC$_VALUE, a.value ACC_VALUE
from prod_par p, acc$_par g, acc_par a
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;

You can then export these results to Excel for further analysis. This provides a clear list of discrepancies between your Oracle database environments.