Infrastructure at your Service

Clemens Bleile

Recommended DB Parameters on the Oracle Database Appliance (ODA)

When creating a DB on the ODA using


# oakcli create database -db <db-name>

a template is used to set a couple of DB parameters like e.g.


_datafile_write_errors_crash_instance=FALSE
_db_writer_coalesce_area_size=16777216
_disable_interface_checking=TRUE
_ENABLE_NUMA_SUPPORT=FALSE
_FILE_SIZE_INCREASE_INCREMENT=2143289344
_gc_policy_time=0
_gc_undo_affinity=FALSE
db_block_checking='FULL'
db_block_checksum='FULL'
db_lost_write_protect='TYPICAL'
filesystemio_options='setall'
parallel_adaptive_multi_user=FALSE
parallel_execution_message_size=16384
parallel_threads_per_cpu=2
use_large_pages='ONLY'

In recent projects I saw a couple of DBs running on ODA, which did not have (all) those parameters set, because the DBs were migrated from a non-ODA-platform and the customer took over the previous settings.

The questions are: Are above parameters mandatory on ODA and where do I find them?

Actually Oracle writes in the documentation

http://docs.oracle.com/cd/E83239_01/doc.121/e83201/database-templates-oracle-database-appliance.htm#CMTAR269

“Oracle strongly recommends that you use the Oracle Database Appliance templates, because they implement best practices and are configured specifically for Oracle Database Appliance.”

So it’s not mandatory, but it’s “strongly recommended” by Oracle to set those parameters on ODA.

The parameters are actually defined in the XML-files

/opt/oracle/oak/onecmd/templates/OAK_oltp.dbt
/opt/oracle/oak/onecmd/templates/OAK_dss.dbt

E.g. on a virtualized X5-2 HA with 12.1.2.8.0 installed:


# grep "initParam name" /opt/oracle/oak/onecmd/templates/OAK_oltp.dbt
<initParam name="AUDIT_SYS_OPERATIONS" value="TRUE"/>
<initParam name="AUDIT_TRAIL" value="DB"/>
<initParam name="GLOBAL_NAMES" value="TRUE"/>
<initParam name="OS_AUTHENT_PREFIX" value=""/>
<initParam name="SQL92_SECURITY" value="TRUE"/>
<initParam name="PARALLEL_ADAPTIVE_MULTI_USER" value="FALSE"/>
<initParam name="PARALLEL_EXECUTION_MESSAGE_SIZE" value="16384"/>
<initParam name="PARALLEL_THREADS_PER_CPU" value="2"/>
<initParam name="_disable_interface_checking" value="TRUE"/>
<initParam name="_gc_undo_affinity" value="FALSE"/>
<initParam name="_gc_policy_time" value="0"/>
<initParam name="SESSION_CACHED_CURSORS" value="100"/>
<initParam name="OPEN_CURSORS" value="1000"/>
<initParam name="CURSOR_SHARING" value="EXACT"/>
<initParam name="_ENABLE_NUMA_SUPPORT" value="FALSE"/>
<initParam name="DB_LOST_WRITE_PROTECT" value="TYPICAL"/>
<initParam name="DB_BLOCK_CHECKSUM" value="FULL"/>
<initParam name="DB_BLOCK_CHECKING" value="FULL"/>
<initParam name="FAST_START_MTTR_TARGET" value="300"/>
<initParam name="UNDO_RETENTION" value="900"/>
<initParam name="_FILE_SIZE_INCREASE_INCREMENT" value="2143289344"/>
<initParam name="FILESYSTEMIO_OPTIONS" value="setall"/>
<initParam name="use_large_pages" value="only"/>
<initParam name="DB_FILES" value="1024"/>
<initParam name="processes" value="4800"/>
<initParam name="pga_aggregate_target" value="49152" unit="MB"/>
<initParam name="sga_target" value="98304" unit="MB"/>
<initParam name="db_create_file_dest" value="+DATA"/>
<initParam name="log_buffer" value="64000000" />
<initParam name="cpu_count" value="48"/>
<initParam name="pga_aggregate_limit" value="49152" unit="MB"/>
<initParam name="_datafile_write_errors_crash_instance" value="false"/>
<initParam name="_fix_control" value="18960760:on"/>
<initParam name="db_block_size" value="8" unit="KB"/>
<initParam name="compatible" value="11.2.0.x.0"/>
<initParam name="undo_tablespace" value="UNDOTBS1"/>
<initParam name="control_files" value="("{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/control01.ctl")"/>
<initParam name="audit_file_dest" value="{ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/adump"/>
<initParam name="audit_trail" value="db"/>
<initParam name="diagnostic_dest" value="{ORACLE_BASE}"/>
<initParam name="remote_login_passwordfile" value="EXCLUSIVE"/>
<initParam name="dispatchers" value="(PROTOCOL=TCP) (SERVICE={SID}XDB)"/>
<initParam name="db_recovery_file_dest" value="+RECO"/>
<initParam name="db_recovery_file_dest_size" value="1843200" unit="MB"/>
<initParam name="db_create_online_log_dest_1" value="+REDO" />
<initParam name="_db_writer_coalesce_area_size" value="16777216"/>

Oracle does not take those parameters blindly when creating a DB with oakcli, but adjusts them as e.g. described in the Blog

https://blog.dbi-services.com/oda-32gb-template-but-got-a-database-with-16gb-sga/

I.e. the SGA_TARGET and PGA_AGGREGATE_TARGET parameters are adjusted based on the chosen database class. Also the COMPATIBLE-parameter is set to the current release (e.g. to 12.1.0.2.0).

So if you’re not able to create the DB on ODA using

# oakcli create database -db <db-name>

then I recommend to check the XML-file OAK_oltp.dbt (or in case of a Decision Support System/Warehouse-DB the file OAK_dss.dbt) and set the parameters in your database accordingly. Alternatively (and probably even better) you may create a dummy-DB using oakcli and check Oracle’s settings, which can then be used in your migrated DB, and drop the dummy-DB afterwards.

Here the parameters of a 12c-DB created on a virtualized ODA X6-2 HA 12.1.2.11.0 with oakcli create database using the smallest setting with 1 Core and the DSS-template:


*._datafile_write_errors_crash_instance=false
*._db_writer_coalesce_area_size=16777216
*._disable_interface_checking=TRUE
*._ENABLE_NUMA_SUPPORT=FALSE
*._FILE_SIZE_INCREASE_INCREMENT=2143289344
*._fix_control='18960760:on'
*._gc_policy_time=0
*._gc_undo_affinity=FALSE
*.audit_file_dest='/u01/app/oracle/admin/C12TEST/adump'
*.audit_sys_operations=TRUE
*.audit_trail='db'
*.cluster_database=true
*.compatible='12.1.0.2.0'
*.control_files='/u01/app/oracle/oradata/datastore/C12TEST/C12TEST/controlfile/o1_mf_dpw4ljnv_.ctl'
*.cpu_count=2
*.cursor_sharing='EXACT'
*.db_block_checking='FULL'
*.db_block_checksum='FULL'
*.db_block_size=8192
*.db_create_file_dest='/u02/app/oracle/oradata/datastore/.ACFS/snaps/C12TEST'
*.db_create_online_log_dest_1='/u01/app/oracle/oradata/datastore/C12TEST'
*.db_domain=''
*.db_files=1024
*.db_lost_write_protect='TYPICAL'
*.db_name='C12TEST'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/datastore/C12TEST'
*.db_recovery_file_dest_size=476160m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=C12TESTXDB)'
*.fast_start_mttr_target=300
*.filesystemio_options='setall'
*.global_names=TRUE
*.inmemory_size=0m
*.log_archive_format='%t_%s_%r.dbf'
*.log_buffer=16000000
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=1000
*.os_authent_prefix=''
*.parallel_adaptive_multi_user=TRUE
*.parallel_degree_policy='MANUAL'
*.parallel_execution_message_size=16384
*.parallel_force_local=FALSE
*.parallel_max_servers=80
*.parallel_min_servers=8
*.parallel_threads_per_cpu=2
*.pga_aggregate_limit=2048m
*.pga_aggregate_target=2048m
*.processes=200
*.remote_login_passwordfile='exclusive'
*.session_cached_cursors=100
*.sga_target=1024m
*.sql92_security=TRUE
*.undo_retention=900
*.use_large_pages='ONLY'

 

Leave a Reply


7 × = twenty one

Clemens Bleile
Clemens Bleile

Senior Consultant