Managing Instances in Oracle RAC
Starting One or More Instances and Oracle RAC Databases Using SRVCTL
$ srvctl start database -db db_unique_name [-startoption start_options]
$ srvctl stop database -db db_unique_name [-o stop_options]
$ srvctl start database -db orcl -startoption mount
To start administrator-managed databases, enter a comma-delimited list of instance names:
$ srvctl start instance -db db_unique_name -instance instance_name_list [-startoption start_options]
To start policy-managed databases, enter a single node name:
$ srvctl start instance -db db_unique_name -node node_name [-startoption start_options]
To stop one or more instances, enter the following SRVCTL syntax from the command line:
$ srvctl stop instance -db db_unique_name [-instance "instance_name_list" | -node node_name] [-stopoption stop_options]
This command also stops the services related to the terminated instances on the nodes where the instances were running. As an example, the following command shuts down the two instances,
orcl4, on the
orcl database using the
immediate stop option:
$ srvctl stop instance -db orcl -instance "orcl3,orcl4" -stopoption immediate
Starting and Stopping Individual Instances Using SQL*Plus
To start or shutdown your local instance, initiate a SQL*Plus session and connect with the SYSRAC or SYSOPER privilege and then issue the required command. For example to start and mount an instance on your local node, run the following commands in your SQL*Plus session:
CONNECT / AS SYSRAC STARTUP MOUNT
You can start multiple instances from a single SQL*Plus session on one node using Oracle Net Services. Connect to each instance in turn by using a Net Services connection string, typically an instance-specific alias from your
For example, you can use a SQL*Plus session on a local node to perform a transactional shutdown for two instances on remote nodes by connecting to each in turn using the instance’s individual alias name. Assume the alias name for the first instance is
db1 and that the alias for the second instance is
db2. Connect to the first instance and shut it down as follows:
CONNECT /@db1 AS SYSRAC SHUTDOWN TRANSACTIONAL
Then connect to and shutdown the second instance by entering the following from your SQL*Plus session:
CONNECT /@db2 AS SYSRAC SHUTDOWN TRANSACTIONAL
Starting and Stopping PDBs in Oracle RAC
You manage PDBs in an Oracle RAC CDB by managing services. This is true regardless of whether the PDBs are policy managed or administrator managed. Assign one dynamic database service to each PDB to coordinate start, stop, and placement of PDBs across instances in a clustered container database.
For example, if you have a CDB called
raccont with a policy-managed PDB called
spark in a server pool called
prod, then assign a service called
plug to this database using the following command:
srvctl add service –db raccont –pdb spark –service plug –serverpool prod
To open the PDB
spark, you must start the service
plug as follows:
srvctl start service -db raccont -service plug
To stop the service
srvctl stop service -db raccont -service plug
spark remains open until you close the PDB using the SQL command
ALTER PLUGGABLE DATABASE PDB_NAME CLOSE IMMEDIATE. You can check the status of the database using the
srvctl status service command.
Using SRVCTL to Verify That Instances are Running
$ srvctl status database -db mail Instance mail1 is running on node betal011Instance mail2 is running on node betal010
Additionally, you can check whether PDBs are running in the cluster by checking the availability of their assigned services, as follows:
$ srvctl status service -db db_unique_name -service service_name
Using SQL*Plus to Verify That Instances are Running
CONNECT SYS/as SYSRAC Enter password: password SELECT * FROM V$ACTIVE_INSTANCES; INST_NUMBER INST_NAME ----------- ----------------- 1 db1-sun:db1 2 db2-sun:db2 3 db3-sun:db3
Overview of Initialization Parameter Files in Oracle RAC
If another DBA runs the following statement, then Oracle Database updates the setting on all instances:
ALTER SYSTEM SET OPEN_CURSORS=1500 sid='*' SCOPE=SPFILE;
Parameter File Search Order in Oracle RAC
Oracle Database searches for your parameter file in a particular order depending on your platform. For Oracle RAC databases, you can easily determine the location of the parameter file by using the
srvctl config database command.
On Linux and UNIX platforms, the search order is as follows:
On Windows platforms, the search order is as follows:
Backing Up the Server Parameter File
CREATE PFILE='/u01/oracle/dbs/test_init.ora' FROM SPFILE='/u01/oracle/dbs/test_spfile.ora';
Initialization Parameters Specific to Oracle RAC
This initialization parameter was deprecated in Oracle RAC 11g release 2 (11.2). Instead, use a service with one preferred and one available instance.
Specifies a set of disks to be the preferred disks from which to read mirror data copies. The values you set for this parameter are instance specific and need not be the same on all instances.
Enables a database to be started in cluster mode. Set this parameter to TRUE.
Oracle RAC uses this parameter to allocate adequate memory resources. It must be set to the same value on all instances.
For policy-managed databases, Oracle internally sets this parameter to 16
For administrator-managed databases, Oracle internally sets it to the number of configured Oracle RAC instances
You can set this parameter to a value that is greater than the current number of instances, if you are planning to add instances. For policy-managed databases, you should set this parameter to a higher value only if you intend to run a database with more than 16 instances. In this case, set the parameter to the expected maximum number of instances on which this database will run.
Specifies an alternative cluster interconnect for the private network when there are multiple interconnects.
Oracle recommends that all Oracle databases and Oracle Clusterware use the same interconnect network.
Oracle does not recommend setting the CLUSTER_INTERCONNECTS parameter except in certain situations. See “Administering Multiple Cluster Interconnects on Linux and UNIX Platforms” for more details.
This parameter is stored in the Grid Plug and Play profile in a Grid Plug and Play environment.
If you set a value for DB_NAME in instance-specific parameter files, the setting must be identical for all instances.
Set the DISPATCHERS parameter to enable a shared server configuration, that is a server that is configured to enable 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 end point. 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. The tnsnames.ora file contains net service names. Clients, nodes, and the Oracle Performance Manager node need this file. Oracle Enterprise Manager does not require tnsnames.ora entries on the client for Cloud Control.
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.
Specifies the unique name of an instance. Clients can use this name to force their session to be connected to a specific instance in the cluster. The format of the INSTANCE_NAME parameter is generally db_unique_name_instance_number, such as orcldb_2.
Note: In Grid Plug and Play environments, the INSTANCE_NAME parameter is not required and defaults to db_unique_name_instance_number if not specified.
In a clustered database, you can either set RESULT_CACHE_MAX_SIZE=0 on every instance to disable the result cache, or use a nonzero value on every instance to enable the result cache. To switch between enabled and disabled result cache requires that you restart every instance:
Enabling the result cache: Set RESULT_CACHE_MAX_SIZE to a value greater than 0, or leave the parameter unset. You can size the cache differently on individual instances.
Disabling the result cache: Set RESULT_CACHE_MAX_SIZE=0 on all instances to disable the result cache. If you set RESULT_CACHE_MAX_SIZE=0 upon startup of any one instance, then you must set the parameter to zero on all instance startups because disabling the result cache must done clusterwide. Disabling the result cache on some instances may lead to incorrect results.
If you do not set the RESULT_CACHE_MAX_SIZE parameter, the parameter resolves to a default, nonzero value.
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 Oracle Enterprise Manager 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. The service features described in Workload Management with Dynamic Database Services are not directly related to the features that Oracle provides when you set SERVICE_NAMES. In addition, setting a value for this parameter may override some benefits of using services.
Note: Oracle recommends that client connections use services rather than instance names. Entries in the SERVICE_NAMES parameter may be used by client connections rather than the INSTANCE_NAME parameter value. The SERVICE_NAMES parameter may include one or more names and different instances may share one or more names with other instances, enabling a client to connect to either a specific instance or to any one of a set of instances, depending on the service name chosen in the connection string.
When you use an SPFILE, all Oracle RAC database instances must use the SPFILE and the file must be on shared storage.
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 Should Have Identical Settings on All Instances
Different values for instances in your Oracle RAC database are likely to increase overhead because of additional automatic synchronization performed by the database processing.
When using either Oracle GoldenGate downstream capture or Oracle GoldenGate integrated capture mode in a downstream capture configuration with your Oracle RAC database, the value must be greater than zero.
While it is preferable for this parameter to have identical settings across all Oracle RAC database instances, it is not required.
Because this parameter determines a database-wide limit on the number of users defined in the database, it is useful to have the same value on all instances of your database so you can see the current value no matter which instance you are using. Setting different values may cause Oracle Database to generate additional warning messages during instance startup, or cause commands related to database user management to fail on some instances.
If you do not use the same value for all your instances, then you unnecessarily complicate media recovery. The recovering instance expects the required archive log file names to have the format defined by its own value of LOG_ARCHIVE_FORMAT, regardless of which instance created the archive log files.
Databases that support Oracle Data Guard, either to send or receive archived redo log files, must use the same value of LOG_ARCHIVE_FORMAT for all instances.
If this parameter does not identify the same file to all instances, then each instance may behave differently and unpredictably in fail over, load-balancing, and during normal operations. Additionally, a change you make to the SPFILE with an ALTER SYSTEM SET or ALTER SYSTEM RESET command is saved only in the SPFILE used by the instance where you run the command. Your change is not reflected in instances using different SPFILEs.
If the SPFILE values are different in instances for which the values were set by the server, then you should restart the instances that are not using the default SPFILE.
If you want diagnostic trace information to be always available for your Oracle RAC database, you must set TRACE_ENABLED to TRUE on all of your database instances. If you trace on only some of your instances, then diagnostic information might not be available when required should the only accessible instances be those with TRACE_ENABLED set to FALSE.
By setting different values for UNDO_RETENTION in each instance, you are likely to reduce scalability and encounter unpredictable behavior following a failover. Therefore, you should carefully consider whether there are any benefits before you assign different values for this parameter to the instances in your Oracle RAC database.
Converting an Administrator-Managed Database to a Policy-Managed Database
To convert an administrator-managed database:
Check the current configuration of all services and the database (if you make a mistake and need to recover, then you can know what the configuration looked like when you began), as follows:
srvctl config database -db db_unique_name srvctl config service -db db_unique_name
Create a server pool for the policy-managed database (you must be a cluster administrator to do this), as follows:
srvctl add srvpool -serverpool server_pool -min 0 -max n
Stop the database using Oracle Enterprise Manager or SRVCTL, as follows:
srvctl stop database -db db_unique_name
Modify the database to be in the new server pool, as follows:
srvctl modify database -db db_unique_name -serverpool server_pool
Add a service user to the wallet, as follows:
crsctl add wallet -type OSUSER -user user_name -passwd
Display the Current Management Policy
Use the following command syntax to display the current management policy where
db_unique_name is the name of the database for which you want to change management policies:
srvctl config database -db db_unique_name -all
Change the Current Management Policy to Another Management Policy
Use the following SRVCTL command syntax to change the current management policy to either AUTOMATIC, MANUAL, or NORESTART:
srvctl modify database -db db_unique_name -policy [AUTOMATIC | MANUAL | NORESTART]
Specify a Management Policy for a New Database
When you add a new database using the
srvctl add database command, you can use the
-policy parameter to specify the management policy as either
NORESTART, as shown in the following example where
db_unique_name is the name of the database:
srvctl add database -db db_unique_name -policy [AUTOMATIC | MANUAL | NORESTART] -oraclehome $ORACLE_HOME -dbname DATA