How to Modify initialization parameters in a Oracle RAC environment
RAC Initialization Parameter Files
When you create the database, Oracle Database creates an SPFILE in the file location that you specify. This location can be either an Oracle ASM disk group or a cluster file system. If you manually create your database, then Oracle recommends that you create an SPFILE from an initialization parameter file (PFILE).
All instances in the cluster database use the same SPFILE at startup. Because the SPFILE is a binary file, do not directly edit the SPFILE with an editor. Instead, change SPFILE parameter settings using Oracle Enterprise Manager or ALTER SYSTEM SQL statements.
When creating an SPFILE, if you include the FROM MEMORY clause (for example, CREATE PFILE FROM MEMORY or CREATE SPFILE FROM MEMORY), then the CREATE statement creates a PFILE or SPFILE using the current system-wide parameter settings. In an Oracle RAC environment, the created file contains the parameter settings from each instance. Because the FROM MEMORY clause requires all other instances to send their parameter settings to the instance that is trying to create the parameter file, the total execution time depends on the number of instances, the number of parameter settings on each instance, and the amount of data for these settings.
SPFILE Parameter Values and RAC
You can modify the value of your initialization parameters by using the ALTER SYSTEM SET command. This is the same as with a single-instance database except that you have the possibility to specify the SID clause in addition to the SCOPE clause.
ALTER SYSTEM SET SCOPE=MEMORY sid='';
SPFILE entries such as:
- *.[pname] apply to all instances
- [sid].[pname] apply only to [sid]
- [sid].[pname] takes precedence over *.[pname]
By using the SID clause, you can specify the SID of the instance where the value takes effect. Specify SID=’*’ if you want to change the value of the parameter for all instances. Specify SID=‘sid’ if you want to change the value of the parameter only for the instance sid. This setting takes precedence over previous and subsequent ALTER SYSTEM SET statements that specify SID=’*’. If the instances are started up with an SPFILE, then SID=’*’ is the default if you do not specify the SID clause.
If you specify an instance other than the current instance, then a message is sent to that instance to change the parameter value in its memory if you are not using the SPFILE scope. The combination of SCOPE=MEMORY and SID=‘sid’ of the ALTER SYSTEM RESET command allows you to override the precedence of a currently used[sid].[dparam]entry. This allows for the current *.[dparam]entry to be used, or for the next created *[dparam]entry to be taken into account on that particular SID. Using the last example, you can remove a line from your SPFILE.
In a multitenant environment, pluggable database (PDB) parameter values are inherited from the root database (CDB). If you want to change a parameter of a PDB, check the GV$SYSTEM_PARAMETER view. If the ISPDB_MODIFIABLE value of the parameter is TRUE, you can change it by using the ALTER SYSTEM SET command.
SQL> ALTER SYSTEM SET CONTAINER=[PDB_NAME] SQL> ALTER SYSTEM SET [DP_NAME]=VALUE SCOPE=MEMORY sid='[sid|*]';
- Use current or future *.[dpname] settings for [sid]:
ALTER SYSTEM RESET [dpname] SCOPE=MEMORY sid='[sid]';
- Remove an entry from your SPFILE:
ALTER SYSTEM RESET [dpname] SCOPE=SPFILE sid='[sid|*]';
EM and SPFILE Parameter Values
You can access the Initialization Parameters page on the Cluster Database page by clicking the Administration tab and selecting Initialization Parameters from the pull-down menu. The Current tabbed page shows you the values currently used by the initialization parameters of all the instances accessing the RAC database. You can filter the Initialization Parameters page to show only those parameters that meet the criteria of the filter that you entered in the Name field.
The Instance column shows the instances for which the parameter has the value listed in the table. An asterisk (*) indicates that the parameter has the same value for all remaining instances of the cluster database.
Choose a parameter from the Select column and perform one of the following steps:
- Click Add to add the selected parameter to a different instance. Enter a new instance name and value in the newly created row in the table.
- Click Reset to reset the value of the selected parameter. Note that you can reset only those parameters that do not have an asterisk in the Instance column. The value of the selected column is reset to the value of the remaining instances.
RAC Initialization Parameters
CLUSTER_DATABASE: Enables a database to be started in cluster mode. Set this to TRUE. CLUSTER_DATABASE_INSTANCES: Sets the number of instances in your RAC environment. A proper setting for this parameter can improve memory use. CLUSTER_INTERCONNECTS: Specifies the cluster interconnect when there is more than one interconnect. Refer to your Oracle platform-specific documentation for the use of this parameter, its syntax, and its behavior. You typically do not need to set the CLUSTER_INTERCONNECTS parameter. For example, do not set this parameter for the following common configurations:
- If you have only one cluster interconnect
- If the default cluster interconnect meets the bandwidth requirements of your RAC database, which is typically the case
- If NIC bonding is being used for the interconnect
- When OIFCFG’s global configuration can specify the right cluster interconnects. It only needs to be specified as an override for OIFCFG.
DB_NAME: If you set a value for DB_NAME in instance-specific parameter files, the setting must be identical for all instances. DISPATCHERS: Set this parameter to enable a shared-server configuration, that is, a server that is configured to allow many user processes to share very few server processes.
With shared-server configurations, many user processes connect to a dispatcher. The DISPATCHERS parameter may contain many attributes. Oracle recommends that you configure at least the PROTOCOL and LISTENER attributes.
PROTOCOL specifies the network protocol for which the dispatcher process generates a listening endpoint. LISTENER specifies an alias name for the Oracle Net Services listeners. Set the alias to a name that is resolved through a naming method, such as a tnsnames.ora file. Other parameters that can affect RAC database configurations include:
- ACTIVE_INSTANCE_COUNT: This initialization parameter was deprecated in Oracle RAC 11.2. Instead, use a service with one preferred and one available instance.
- GCS_SERVER_PROCESSES: This static parameter specifies the initial number of server processes for an Oracle RAC instance’s Global Cache Service (GCS). The GCS processes manage the routing of inter-instance traffic among Oracle RAC instances. The default number of GCS server processes is calculated based on system resources with a minimum setting of 2. For systems with one CPU, there is one GCS server process. For systems with two to eight CPUs, there are two GCS server processes. For systems with more than eight CPUs, the number of GCS server processes equals the number of CPUs divided by 4, dropping any fractions. For example, if you have 10 CPUs, then 10 divided by 4 means that your system has 2 GCS processes. You can set this parameter to different values on different instances.
- INSTANCE_NAME: The instance’s SID. The SID identifies the instance’s shared memory on a host. Any alphanumeric characters can be used. The value for this parameter is automatically set to the database unique name followed by an incrementing number during the creation of the database when using DBCA.
- RESULT_CACHE_MAX_SIZE: In a clustered database, you can either set it to 0 on every instance to disable the result cache, or use a nonzero value on every instance to enable the result cache.
- SERVICE_NAMES: When you use services, Oracle recommends that you do not set a value for the SERVICE_NAMES parameter but instead you should create cluster managed services through the Cluster Managed Services page in EM Cloud Control. This is because Oracle Clusterware controls the setting for this parameter for the services that you create and for the default database service.
- SPFILE: When you use an SPFILE, all Oracle RAC database instances must use the SPFILE and the file must be on shared storage.
- THREAD: Specifies the number of the redo threads to be used by an instance. You can specify any available redo thread number if that thread number is enabled and is not used. If specified, this parameter must have unique values on all instances. The best practice is to use the INSTANCE_NAME parameter to specify redo log groups.
Parameters That Require Identical Settings
Certain initialization parameters that are critical at database creation or that affect certain database operations must have the same value for every instance in an Oracle RAC database. Specify these parameter values in the SPFILE or in the individual PFILEs for each instance. The list given below contains the parameters that must be identical on every instance.
- INSTANCE_TYPE (RDBMS or ASM)
Parameters That Require Unique Settings
When it is necessary to set parameters with unique settings on a policy-managed database, you can ensure that instances always use the same name on particular nodes by running the srvctl modify instance -n node -i instance_name command for each server that can be assigned to the database’s server pool. Then a unique value of the parameter can be specified for instance_name used whenever the database runs on node_name.
Specify the ORACLE_SID environment variable, which consists of the database name and the number of the INSTANCE_NAME assigned to the instance. Use the CLUSTER_INTERCONNECTS parameter to specify an alternative interconnect to the one Oracle Clusterware is using for the private network. Each instance of the RAC database gets a unique value when setting the CLUSTER_INTERCONNECTS initialization parameter.
Oracle Database uses the INSTANCE_NUMBER parameter to distinguish among instances at startup and the INSTANCE_NAME parameter to assign redo log groups to specific instances. The instance name can take the form db_unique_name_instance_number and when it has this form of name and number separated by an underscore, the number after the underscore is used as the INSTANCE_NUMBER. When UNDO_TABLESPACE is specified with automatic undo management enabled, then set this to a unique undo tablespace name for each instance.
If you use the ROLLBACK_SEGMENTS parameters, then Oracle recommends setting unique values for it by using the SID identifier in the SPFILE. However, you must set a unique value for INSTANCE_NUMBER for each instance and you cannot use a default value.