Oracle 19c Server

Initialization Parameters

Introduction to Initialization Parameters

Oracle initialization parameters with explicitly-set values are stored in the server parameter file (SPFILE). The default path of the SPFILE is $ORACLE_HOME/dbs/spfileCDB_name.ora. During database creation, DBCA automatically creates an SPFILE in the default location and sets various initialization parameters in the SPFILE based on values entered on the DBCA GUI screens.

Do not modify the SPFILE with a text editor. Although the SPFILE appears to be a text file, it is actually a binary file and should not be modified with a text editor. Modifying the SPFILE with a text editor will corrupt it.

After Oracle database creation, you can use the alter system set command to set or change the value of initialization parameters, and the alter system reset command to reset an initialization parameter back to its default value. The alter system reset command removes the specified parameter from the SPFILE altogether, and in this way resets it to the default. Refer to the Oracle documentation for more information on setting and resetting initialization parameters.

Most of the initialization parameters that we will be explicitly setting should be set at the CDB-level, not the PDB-level. To set initialization parameters at the CDB-level, make sure the current container of your session is CDB$ROOT. You can change the current container to CDB$ROOT by entering at the sqlplus prompt:

SQL> alter session set container = CDB$ROOT;

To verify that CDB$ROOT is the current container of your session, display the current container name by entering at the sqlplus prompt:

SQL> show con_name

Initialization Parameter Values Required by LeasePak

The following initialization parameter values are required by LeasePak. Most of these parameters must be set at the CDB-level, not the PDB-level. OPEN_CURSORS can be set at either level, but it is simpler to just set it at the CDB-level, since PDBs will automatically inherit the CDB value of the parameter.

Initialization Parameter	Required Value	Level
FILESYSTEMIO_OPTIONS	SETALL	CDB
OPEN_CURSORS	2000 (minimum)	CDB or PDB
MEMORY_TARGET	0	CDB
SGA_TARGET	0	CDB
_MEMORY_IMM_MODE_WITHOUT_AUTOSGA	FALSE	CDB
DB_BLOCK_SIZE	8192	CDB

Manual Shared Memory Management

  LeasePak requires Manual Shared Memory Management. Do not use Automatic Shared Memory Management or Automatic Memory Management.

Manual Shared Memory Management is enabled by setting these three initialization parameters as follows:

• MEMORY_TARGET = 0
• SGA_TARGET = 0
• "_MEMORY_IMM_MODE_WITHOUT_AUTOSGA" = FALSE

Commands for setting required initialization parameter values

1. Log on the DBMS host as the Oracle software owner.

2. Enter at the Unix prompt:

   % sqlplus / as sysdba

3. Change the current container to CDB$ROOT by entering at the sqlplus prompt:

   SQL> alter session set container = CDB$ROOT;

4. Ensure the current container is CDB$ROOT:

   SQL> show con_name

5. Run these five commands to set the required initialization parameter values:

   SQL> alter system set FILESYSTEMIO_OPTIONS = SETALL scope = spfile;
   SQL> alter system set OPEN_CURSORS = 2000 scope = both;
   SQL> alter system set MEMORY_TARGET = 0 scope = both;
   SQL> alter system set SGA_TARGET = 0 scope = both;
   SQL> alter system set "_MEMORY_IMM_MODE_WITHOUT_AUTOSGA" = FALSE scope = both;
   

6. DB_BLOCK_SIZE should have already been set to the required value of 8192 by DBCA when the database was created. Run the following command to verify that the value of DB_BLOCK_SIZE is 8192:

   SQL> show parameter DB_BLOCK_SIZE

7. Shutdown and restart the Oracle database.

Initialization Parameter Values Customizable for Your Database

Initialization parameters set by DBCA

The following initialization parameters are those which DBCA automatically set in the SPFILE when the database was created. Review the values of these parameters and make any necessary modifications.

 These parameters should be set at the CDB-level, not the PDB-level.

Initialization Parameter	Description	Level
DB_NAME	CDB name	CDB
DB_DOMAIN	CDB domain	CDB
LOCAL_LISTENER	net service name of local listener	CDB
UNDO_TABLESPACE	undo tablespace name	CDB
CONTROL_FILES	paths of control files	CDB
DIAGNOSTIC_DEST	path of diagnostic destination	CDB
DB_RECOVERY_FILE_DEST	path of fast recovery area	CDB
DB_RECOVERY_FILE_DEST_SIZE	maximum size of fast recovery area	CDB
PROCESSES	maximum number of processes	CDB
DB_CACHE_SIZE	size of buffer cache	CDB
SHARED_POOL_SIZE	size of shared pool	CDB
LARGE_POOL_SIZE	size of large pool	CDB
JAVA_POOL_SIZE	size of java pool	CDB
PGA_AGGREGATE_TARGET	size of program global area	CDB
ENABLE_PLUGGABLE_DATABASE	allows creation of CDB and PDBs	CDB
REMOTE_LOGIN_PASSWORDFILE	password file option	CDB
NLS_LANGUAGE	default language for session	CDB
NLS_TERRITORY	default territory for session	CDB
COMPATIBLE	compatible release	CDB
AUDIT_TRAIL	audit trail option	CDB

You will probably need to change the value of the AUDIT_TRAIL parameter, since the DBCA default for AUDIT_TRAIL is usually incorrect. Refer to the Disable Oracle Auditing section for further instructions.

Initialization parameters not set by DBCA

The following initialization parameters were not set by DBCA in the SPFILE when the database was created. The general recommendation is to change the values of these parameters from the default, since the default value is usually inappropriate.

Initialization Parameter	Description	Level
RESULT_CACHE_MAX_SIZE	maximum size of result cache	CDB
OPTIMIZER_USE_SQL_PLAN_BASELINES	plan baselines usage boolean	CDB
CURSOR_BIND_CAPTURE_DESTINATION	bind variable capture option	CDB
CONTROL_MANAGEMENT_PACK_ACCESS	Server Manageability Pack access	CDB
MAX_DUMP_FILE_SIZE	maximum size of trace files	CDB
RECYCLEBIN	recycle bin option	CDB

Recommendation: disable the result cache. Refer to the Disable the Result Cache section for instructions on how to disable the result cache by setting the RESULT_CACHE_MAX_SIZE parameter to 0.

Recommendation: disable the use of SQL plan baselines. Refer to the Disable the Use of SQL Plan Baselines section for instructions on how to disable the use of SQL plan baselines by setting the OPTIMIZER_USE_SQL_PLAN_BASELINES parameter to FALSE.

Recommendation: disable bind variable capturing. Refer to the Disable Bind Variable Capturing section for instructions on how to disable bind variable capturing by setting the CURSOR_BIND_CAPTURE_DESTINATION parameter to OFF.

Requirement: Unless you have purchased separate licenses for Oracle Diagnostic Pack and Oracle Tuning Pack, you must disable Diagnostic Pack and Tuning Pack. Refer to the Disable Diagnostic Pack and Tuning Pack section forinstructions on how to disable Diagnostic Pack and Tuning Pack by setting the CONTROL_MANAGEMENT_PACK_ACCESS parameter to NONE.

Recommendation: set the maximum size of trace files to 100 megabytes. Refer to the Set Maximum Size of Trace Files section for instructions on how to accomplish this by setting the MAX_DUMP_FILE_SIZE parameter to '100M' (100 megabytes).

Recommendation: disable the Oracle recyclebin. Refer to the Disable the Recyclebin section for instructions on how to disable the recyclebin by setting the RECYCLEBIN parameter to OFF.

Disable Oracle Auditing

The recommendation is to disable Oracle auditing, unless you need it enabled for some reason. When the database was created, DBCA automatically enabled Oracle auditing, so you will need to disable it manually by setting the AUDIT_TRAIL parameter to NONE.

Oracle auditing, when enabled, will decrease the performance of LeasePak and may consume excessive disk space.

To disable Oracle auditing:

1. Log on the DBMS host as the Oracle software owner.

2. Enter at the Unix prompt:

   % sqlplus / as sysdba

3. Change the current container to CDB$ROOT by entering at the sqlplus prompt:

   SQL> alter session set container = CDB$ROOT;

4. Ensure the current container is CDB$ROOT:

   SQL> show con_name

5. Run these two commands:

   SQL> alter system set AUDIT_TRAIL = NONE scope = spfile;
   SQL> alter system reset AUDIT_FILE_DEST scope = spfile;
   

6. Shutdown and restart the Oracle database.

Disable the Result Cache

The recommendation is to disable the result cache. The result cache is enabled by default, so you will need to disable it manually by setting the RESULT_CACHE_MAX_SIZE parameter to 0.

To disable the result cache:

1. Log on the DBMS host as the Oracle software owner.

2. Enter at the Unix prompt:

   % sqlplus / as sysdba

3. Change the current container to CDB$ROOT by entering at the sqlplus prompt:

   SQL> alter session set container = CDB$ROOT;

4. Ensure the current container is CDB$ROOT:

   SQL> show con_name

5. Run this command:

   SQL> alter system set RESULT_CACHE_MAX_SIZE = 0 scope = both;

Disable the Use of SQL Plan Baselines

The recommendation is to disable the use of SQL plan baselines. The use of SQL plan baselines is enabled by default, so you will need to disable it manually by setting the OPTIMIZER_USE_SQL_PLAN_BASELINES parameter to FALSE.

To disable the use of SQL plan baselines:

1. Log on the DBMS host as the Oracle software owner.

2. Enter at the Unix prompt:

   % sqlplus / as sysdba

3. Change the current container to CDB$ROOT by entering at the sqlplus prompt:

   SQL> alter session set container = CDB$ROOT;

4. Ensure the current container is CDB$ROOT:

   SQL> show con_name

5. Run this command:

   SQL> alter system set OPTIMIZER_USE_SQL_PLAN_BASELINES = FALSE scope = both;

Disable Bind Variable Capturing

The recommendation is to disable bind variable capturing. Bind variable capturing is enabled by default, so you will need to disable it manually by setting the CURSOR_BIND_CAPTURE_DESTINATION parameter to OFF.

To disable bind variable capturing:

1. Log on the DBMS host as the Oracle software owner.

2. Enter at the Unix prompt:

   % sqlplus / as sysdba

3. Change the current container to CDB$ROOT by entering at the sqlplus prompt:

   SQL> alter session set container = CDB$ROOT;

4. Ensure the current container is CDB$ROOT:

   SQL> show con_name

5. Run this command:

   SQL> alter system set CURSOR_BIND_CAPTURE_DESTINATION = OFF scope = both;

Disable Diagnostic Pack and Tuning Pack

Unless you have purchased licenses for Oracle Diagnostic Pack and Oracle Tuning Pack, you must disable Diagnostic Pack and Tuning Pack. Diagnostic Pack and Tuning Pack are enabled by default, so you will need to disable them manually by setting the CONTROL_MANAGEMENT_PACK_ACCESS parameter to NONE.

To disable Diagnostic Pack and Tuning Pack:

1. Log on the DBMS host as the Oracle software owner.

2. Enter at the Unix prompt:

   % sqlplus / as sysdba

3. Change the current container to CDB$ROOT by entering at the sqlplus prompt:

   SQL> alter session set container = CDB$ROOT;

4. Ensure the current container is CDB$ROOT:

   SQL> show con_name

5. Run this command:

   SQL> alter system set CONTROL_MANAGEMENT_PACK_ACCESS = NONE scope = both;

Set Maximum Size of Trace Files

The recommendation is to set the maximum size of trace files to 100 megabytes. By default, the maximum size of trace files is unlimited, so you will need to manually set the MAX_DUMP_FILE_SIZE parameter to '100M'.

To set the MAX_DUMP_FILE_SIZE parameter to '100M' (100 megabytes):

1. Log on the DBMS host as the Oracle software owner.

2. Enter at the Unix prompt:

   % sqlplus / as sysdba

3. Change the current container to CDB$ROOT by entering at the sqlplus prompt:

   SQL> alter session set container = CDB$ROOT;

4. Ensure the current container is CDB$ROOT:

   SQL> show con_name

5. Run this command:

   SQL> alter system set MAX_DUMP_FILE_SIZE = '100M' scope = both;

Disable the Recyclebin

The recommendation is to disable the recyclebin. The recyclebin is enabled by default, so you will need to disable it manually by setting the RECYCLEBIN parameter to OFF.

To disable the recyclebin for the CDB and all PDBs:

1. Log on the DBMS host as the Oracle software owner.

2. Enter at the Unix prompt:

   % sqlplus / as sysdba

3. Change the current container to CDB$ROOT by entering at the sqlplus prompt:

   SQL> alter session set container = CDB$ROOT;

4. Ensure the current container is CDB$ROOT:

   SQL> show con_name

5. Run this command:

   SQL> alter system set RECYCLEBIN = OFF deferred;

 Setting RECYCLEBIN to OFF at the CDB-level causes each PDB to inherit the value of OFF by default. However, you still retain the option of setting RECYCLEBIN to ON at the PDB-level for particular PDBs. You may wish to do this if there are some PDBs (such as production databases) for which you would like to enable the recyclebin, while leaving the recyclebin disabled for the remaining PDBs.

Initialization Parameters That Should NOT Be Changed From the Default

The values of the following initialization parameters should not be changed from the Oracle default value.

Initialization Parameter	Oracle Default Value (do not change)
CLIENT_RESULT_CACHE_SIZE	0
CURSOR_SHARING	EXACT
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES	FALSE
OPTIMIZER_MODE	ALL_ROWS
PARALLEL_DEGREE_POLICY	MANUAL
RESULT_CACHE_MODE	MANUAL
SESSIONS	automatically calculated based on PROCESSES
WORKAREA_SIZE_POLICY	AUTO

Guidelines for Setting the Customizable Initialization Parameters

DB_NAME

The name of the Oracle container database (CDB). Up to 8 characters in length. The name should be the same as the Oracle System Identifier (SID) entered during the Database Identification step of How to Create a Multitenant Database.

Example: orcl

DB_DOMAIN

The domain of the Oracle container database (CDB). The domain should be the same as the domain portion of the Global Database Name entered during the Database Identification step of How to Create a Multitenant Database.

LOCAL_LISTENER

The net service name of the local listener that is running on the same system as the instance. DBCA automatically sets the value of this parameter to LISTENER_DB_NAME. It also adds the net service name and network address of the local listener to the tnsnames.ora file.

Example: LISTENER_ORCL

UNDO_TABLESPACE

The name of the undo tablespace as specified during the Customize Storage: Datafiles step of How to Create a Multitenant Database.

The undo tablespace name will be used for both the CDB undo tablespace and the PDB undo tablespace.

Typical value: UNDOTBS1

CONTROL_FILES

Paths of multiplexed control files.

Typical value:

'/opt/dbdata/oradata/CDB_name/control01.ctl','/opt/dbdata/fast_recovery_area/CDB_name/control02.ctl'

DIAGNOSTIC_DEST

The path of the diagnostic destination directory. It is usually the same as the path of ORACLE_BASE.

Typical value: '/opt/oracle'

DB_RECOVERY_FILE_DEST

The path of the fast recovery area directory.

Typical value: '/opt/dbdata/fast_recovery_area/CDB_name'

DB_RECOVERY_FILE_DEST_SIZE

The maximum size of the fast recovery area. Refer to the Oracle Database Backup and Recovery User's Guide for guidelines on setting this size.

PROCESSES

The maximum number of Oracle operating system processes. Set PROCESSES to the larger of:

• 300 (minimum). This value of 300 is based on a single PDB. If there are multiple PDBs, the minimum value would be greater than 300.
• The maximum number of concurrent Oracle connections plus the approximate number of Oracle background processes. A rough estimate of the number of Oracle background processes is 25. If you are unsure of the maximum number of concurrent Oracle connections, set PROCESSES to an initial value of 300 and adjust it later.

Do not vastly oversize the PROCESSES initialization parameter, since many other parameters and allocations are automatically derived from the value of PROCESSES.

DB_CACHE_SIZE

The size of the buffer cache.

Recommended minimum value: 2G

Proper sizing of the buffer cache improves database performance. The buffer cache size should be large enough to keep the amount of physical I/O (disk access) to a minimum. There is a point of diminishing returns with the DB_CACHE_SIZE parameter, and setting it much larger than the optimal size is not beneficial.

SHARED_POOL_SIZE

The size of the shared pool.

Recommended minimum value: 1G

Proper sizing of the shared pool improves database performance. There is a point of diminishing returns with the SHARED_POOL_SIZE parameter, and setting it much larger than the optimal size may decrease performance.

Periodically query the Oracle v$sgainfo view and check the number of bytes used by the "Startup overhead in Shared Pool". If the shared pool startup overhead is found to be consuming too large of a percentage of the SHARED_POOL_SIZE, increase the SHARED_POOL_SIZE. The shared pool startup overhead can unexpectedly increase as a result of modifying various initialization parameters, so it is recommended to re-check the shared pool startup overhead in v$sgainfo after changing other initialization parameters.

LARGE_POOL_SIZE

The size of the large pool.

Recommended minimum value: 128M

JAVA_POOL_SIZE

The size of the java pool.

Recommended minimum value: 128M

PGA_AGGREGATE_TARGET

The size of the program global area.

Recommended minimum value: 2G

ENABLE_PLUGGABLE_DATABASE

Boolean to allow creation of a CDB and PDBs.

Required value: TRUE

REMOTE_LOGIN_PASSWORDFILE

Password file option.

Required value: EXCLUSIVE

NLS_LANGUAGE

Default language for session.

Recommended value: AMERICAN

NLS_TERRITORY

Default territory for session.

Recommended value: AMERICA

COMPATIBLE

Compatible release of Oracle.

Required value: 19.0.0

AUDIT_TRAIL

Oracle auditing option.

Recommended value: NONE

RESULT_CACHE_MAX_SIZE

Maximum size of the result cache.

Recommended value: 0

OPTIMIZER_USE_SQL_PLAN_BASELINES

SQL plan baselines usage boolean.

Recommended value: FALSE

CURSOR_BIND_CAPTURE_DESTINATION

Bind variable capture option.

Recommended value: OFF

MAX_DUMP_FILE_SIZE

Maximum size of trace files.

Recommended value: '100M'

RECYCLEBIN

Oracle recycle bin option.

Recommended value: OFF

Verifying the CDB Initialization Parameter Settings

After the Oracle database has been created and started, verify the CDB (container database) initialization parameter settings for correctness:

1. Log on the DBMS host as the Oracle software owner.

2. Enter at the Unix prompt:

   % sqlplus / as sysdba

3. Change the current container to CDB$ROOT by entering at the sqlplus prompt:

   SQL> alter session set container = CDB$ROOT;

4. Ensure the current container is CDB$ROOT:

   SQL> show con_name

5. To display the values of initialization parameters that are explicitly set in the server parameter file (SPFILE):

   SQL> show spparameters

   Parameters with a non-blank value are explicitly set in the SPFILE. Parameters with a blank value are not explicitly set in the SPFILE.

6. To display the values of initialization parameters in effect for the current session:

   SQL> show parameters

   The parameter values in effect for the current session originate from the CDB, but some of the CDB level parameters can be overridden at the session level via the alter session set command.

Verifying the PDB Initialization Parameter Settings

After the Oracle database has been created and started, verify the PDB (pluggable database) initialization parameter settings for correctness:

1. Log on the DBMS host as the Oracle software owner.

2. Enter at the Unix prompt:

   % sqlplus / as sysdba

3. Change the current container to the PDB by entering at the sqlplus prompt:

   SQL> alter session set container = PDB_name;

4. Ensure the current container is the PDB:

   SQL> show con_name

5. To display the values of initialization parameters that are explicitly set at the PDB level:

   SQL> show spparameters

   Parameters with a non-blank value are explicitly set at the PDB level. Parameters with a blank value are not explicitly set at the PDB level.

   Not many initialization parameters are set at the PDB level. If an initialization parameter is set at the PDB level, the PDB level value is stored in the Oracle data dictionary, not the SPFILE.

6. To display the values of initialization parameters in effect for the current session:

   SQL> show parameters

   The parameter values in effect for the current session originate from the CDB, but some of the CDB level parameters can be overridden at the PDB level. Additionally, some CDB level parameters and PDB level parameters can be overridden at the session level via the alter session set command.