Easy way to verify Oracle Database initialization parameters!

I wanted to share an easy way that we could verify the value of certain oracle database initialization parameters on-premise or in the Cloud across a 2 node RAC instance. I tested this script on a 12.2 multitenant oracle database and employ using decode to check for a value otherwise printing a message that the parameter is not set. Once you run the script you can run a simple command from the Unix prompt to see which parameters are not set correctly and then take the appropriate action to adjust the parameters. I also verify a database property and numeric values as well such as parallel_max_servers. This method should help you run a simple script to crosscheck your standard initilization parameters.

spool /tmp/verify_params.out

-- parallel_force_local FALSE
select decode(( select count(*) from gv$parameter where inst_id=1 and value='FALSE' and name='parallel_force_local' ),1, 'YES', 'NO parallel_force_local is not set to FALSE') from dual;
select decode(( select count(*) from gv$parameter where inst_id=2 and value='FALSE' and name='parallel_force_local' ),1, 'YES', 'NO parallel_force_local is not set to FALSE') from dual;

-- *._fix_control='14033181:0'
select decode(( select count(*) from gv$parameter where inst_id=1 and value='14033181:0' and name='_fix_control' ),1, 'YES', 'NO _fix_control is not set to 14033181:0') from dual;
select decode(( select count(*) from gv$parameter where inst_id=2 and value='14033181:0' and name='_fix_control' ),1, 'YES', 'NO _fix_control is not set to 14033181:0') from dual;

-- *._gby_hash_aggregation_enabled=FALSE
select decode(( select count(*) from gv$parameter where inst_id=1  and value='FALSE' and name='_gby_hash_aggregation_enabled' ),1, 'YES', 'NO _gby_hash_aggregation_enabled is not set to FALSE') from dual;
select decode(( select count(*) from gv$parameter where inst_id=2  and value='FALSE' and name='_gby_hash_aggregation_enabled' ),1, 'YES', 'NO _gby_hash_aggregation_enabled is not set to FALSE') from dual;

-- *._gc_trace_freelist_empty=FALSE
select decode(( select count(*) from gv$parameter where inst_id=1  and value='FALSE' and name='_gc_trace_freelist_empty' ),1, 'YES', 'NO _gc_trace_freelist_empty is not set to FALSE') from dual;
select decode(( select count(*) from gv$parameter where inst_id=2  and value='FALSE' and name='_gc_trace_freelist_empty' ),1, 'YES', 'NO _gc_trace_freelist_empty is not set to FALSE') from dual;

-- *._ignore_desc_in_index=TRUE
select decode(( select count(*) from gv$parameter where inst_id=1  and value='TRUE' and name='_ignore_desc_in_index' ),1, 'YES', '_ignore_desc_in_index is not set to TRUE') from dual;
select decode(( select count(*) from gv$parameter where inst_id=2  and value='TRUE' and name='_ignore_desc_in_index' ),1, 'YES', '_ignore_desc_in_index is not set to TRUE') from dual;

-- *._optimizer_skip_scan_enabled=TRUE
select decode(( select count(*) from gv$parameter where inst_id=1  and value='TRUE' and name='_optimizer_skip_scan_enabled' ),1, 'YES', '_optimizer_skip_scan_enabled is not set to TRUE') from dual;
select decode(( select count(*) from gv$parameter where inst_id=2  and value='TRUE' and name='_optimizer_skip_scan_enabled' ),1, 'YES', '_optimizer_skip_scan_enabled is not set to TRUE') from dual;

-- *._unnest_subquery=FALSE
select decode(( select count(*) from gv$parameter where inst_id=1  and value='FALSE' and name='_unnest_subquery' ),1, 'YES', '_unnest_subquery is not set to FALSE') from dual;
select decode(( select count(*) from gv$parameter where inst_id=2  and value='FALSE' and name='_unnest_subquery' ),1, 'YES', '_unnest_subquery is not set to FALSE') from dual;

-- *.audit_trail='DB'
select decode(( select count(*) from gv$parameter where inst_id=1  and value='DB' and name='audit_trail' ),1, 'YES', 'audit_trail is not set to DB') from dual;
select decode(( select count(*) from gv$parameter where inst_id=2  and value='DB' and name='audit_trail' ),1, 'YES', 'audit_trail is not set to DB') from dual;

-- *.compatible='12.2.0'
select decode(( select count(*) from gv$parameter where inst_id=1  and value='12.2.0' and name='compatible' ),1, 'YES', 'compatible is not set to 12.2') from dual;
select decode(( select count(*) from gv$parameter where inst_id=2  and value='12.2.0' and name='compatible' ),1, 'YES', 'compatible is not set to 12.2') from dual;

-- *.db_files=4000
select decode(( select count(*) from gv$parameter where inst_id=1  and to_number(value) >= 4000 and name='db_files' ),1, 'YES', 'db_files is not set to 4000 or greater') from dual;
select decode(( select count(*) from gv$parameter where inst_id=2  and to_number(value) >= 4000 and name='db_files' ),1, 'YES', 'db_files is not set to 4000 or greater') from dual;

-- *.diagnostic_dest='/u02/app/oracle'
select decode(( select count(*) from gv$parameter where inst_id=1  and value = '/u02/app/oracle' and name='diagnostic_dest' ),1, 'YES', 'diagnostic_dest is not set to /u02/app/oracle') from dual;
select decode(( select count(*) from gv$parameter where inst_id=2  and value = '/u02/app/oracle' and name='diagnostic_dest' ),1, 'YES', 'diagnostic_dest is not set to /u02/app/oracle') from dual;

-- *.log_archive_format='%t_%s_%r.arc'
select decode(( select count(*) from gv$parameter where inst_id=1  and value = '%t_%s_%r.arc' and name='log_archive_format' ),1, 'YES', 'log_archive_format is not set to %t_%s_%r.arc ') from dual;
select decode(( select count(*) from gv$parameter where inst_id=2  and value = '%t_%s_%r.arc' and name='log_archive_format' ),1, 'YES', 'log_archive_format is not set to %t_%s_%r.arc ') from dual;


-- *.max_dump_file_size='50K'
select decode(( select count(*) from gv$parameter where inst_id=1  and value = '50K' and name='max_dump_file_size' ),1, 'YES', 'max_dump_file_size is not set to 50K') from dual;
select decode(( select count(*) from gv$parameter where inst_id=2  and value = '50K' and name='max_dump_file_size' ),1, 'YES', 'max_dump_file_size is not set to 50K') from dual;

-- *.nls_length_semantics='CHAR'
select decode(( select count(*) from gv$parameter where inst_id=1  and value = 'CHAR' and name='nls_length_semantics' ),1, 'YES', 'nls_length_semantics is not set to CHAR') from dual;
select decode(( select count(*) from gv$parameter where inst_id=2  and value = 'CHAR' and name='nls_length_semantics' ),1, 'YES', 'nls_length_semantics is not set to CHAR') from dual;

-- *.open_cursors=10000
select decode(( select count(*) from gv$parameter where inst_id=1  and to_number(value) >= 10000 and name='open_cursors' ),1, 'YES', 'open_cursors is not set 10000 or higher') from dual;
select decode(( select count(*) from gv$parameter where inst_id=2  and to_number(value) >= 10000 and name='open_cursors' ),1, 'YES', 'open_cursors is not set 10000 or higher') from dual;

-- *.processes=4000
select decode(( select count(*) from gv$parameter where inst_id=1  and to_number(value) >= 4000 and name='processes' ),1, 'YES', 'processes is not set to 4000 or higher') from dual;
select decode(( select count(*) from gv$parameter where inst_id=2  and to_number(value) >= 4000 and name='processes' ),1, 'YES', 'processes is not set to 4000 or higher') from dual;

-- *.parallel_max_servers should be 6 per pdb
select decode(( select count(*) from gv$parameter where inst_id=1  and to_number(value) * 6 >= (select count(*) - 1 from v$pdbs) and name='parallel_max_servers' ),1, 'YES', 'parallel_max_servers needs to be 6 per pdb') from dual;
select decode(( select count(*) from gv$parameter where inst_id=2  and to_number(value) * 6 >= (select count(*) - 1 from v$pdbs) and name='parallel_max_servers' ),1, 'YES', 'parallel_max_servers needs to be 6 per pdb') from dual;

-- *.temp_undo_enabled=TRUE
select decode(( select count(*) from gv$parameter where inst_id=1 and value = 'TRUE' and name='temp_undo_enabled' ),1, 'YES', 'temp_undo_enabled is not set to TRUE') from dual;
select decode(( select count(*) from gv$parameter where inst_id=2 and value = 'TRUE' and name='temp_undo_enabled' ),1, 'YES', 'temp_undo_enabled is not set to TRUE') from dual;

-- *.undo_retention=10800
select decode(( select count(*) from gv$parameter where inst_id=1 and to_number(value) >= 10800 and name='undo_retention' ),1, 'YES', 'undo_retention is not 10800 or greater') from dual;
select decode(( select count(*) from gv$parameter where inst_id=2 and to_number(value) >= 10800 and name='undo_retention' ),1, 'YES', 'undo_retention is not 10800 or greater') from dual;

-- LOCAL_UNDO_ENABLED
select decode((SELECT count(*) FROM   database_properties WHERE  property_name = 'LOCAL_UNDO_ENABLED' and PROPERTY_VALUE='TRUE'), 1, 'YES','LOCAL_UNDO_ENABLED property is not TRUE') from dual;

exit

Example run of verifying the output and what is not set correctly. In this case we have 3 parameters that requires attention.

$ grep -i not /tmp/verify_params.out|sort -u

max_dump_file_size is not set to 50K                                                                                                                             

NO _fix_control is not set to 14033181:0                                                                                                                         

undo_retention is not 10800 or greater