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                                                

Oracle Data Pump Presentation online now

Thank you to the Dallas Oracle Users Group for letting me present recently on my "Optimize your Database Import" topic.

I covered the following topics in my presentation -

• Data Pump Overview
• 12c (12.1 & 12.2) New Features
• Data guard & Data pump working together
• Customer case study - Optimizing Data pump import

You may view my presentation at the following link.

https://www.slideshare.net/NabilNawaz/optimizing-your-database-import

Presenting at the Dallas Oracle Users Group on Feb 22, 2018!

Tomorrow (Thursday, 2/22/18 from 5 – 7:15 pm)  I will be presenting at the Dallas Oracle Users Group(OUG). My topic is "Optimize your Database Import". I am happy to be presenting at the Dallas OUG once again, thank you! I also want to thank BIAS Corporation for sponsoring my event as well. 

Presentation Overview “Optimize your Database Import” by Nabil Nawaz, BIAS Corporation. Oracle DataPump is an excellent tool for cloning databases and schemas and it is widely used as a common toolset today among DBAs and Developers to transfer data and structure between databases. Please come and learn about new Data pump features for Oracle version 12.2. We will also be sharing a case study for a large multi-terabyte database for optimizing a data pump import process that originally ran for more than a day and then the process was tuned to run in just about 4-6 hours a nearly 90% performance enhancement. The tips that will be shared will be of great value and help to ensure you are able to have a well-tuned import process with DataPump. Refreshments sponsored by BIAS Corporation. To attend, RSVP here. Contact dougadministrative@gmail.com if your plans change, so we can order the right amount of food. Location/Directions University of Dallas, Gorman Lecture Center, Room C 1845 East Northgate Drive Irving, Texas *Park near the bell tower.

Directions to the campus:http://udallas.edu/visitors/directions.php Campus map:http://udallas.edu/visitors/documents/CampusMap_11-16-16.pdf

Presenter Bio Nabil Nawaz started his career with Oracle in 1997 and is currently a Technical Manager at BIAS Corporation and has 20 years of experience working as an Oracle DBA & Solution Architect starting with version 7.1.6, he is Cloud IaaS, OCP and Exadata certified and also an Oracle ACE associate. He is a contributing author on the recent book Oracle Exadata Expert's Handbook. His background is quite vast with Oracle and has had the opportunity to work as a consultant in many large fortune 500 companies focusing on architecting high available solutions using RAC & Data guard and currently working with architecting Oracle Cloud and Engineered systems such as Exadata, Supercluster, ODAs and Virtualization technologies. He can be followed at his blog, http://nnawaz.blogspot.com/ and on Twitter @Nabil_Nawaz

Visit DOUG at http://doug.org/ or join our LinkedIn Group.