Skip to content

Managing Instances in Oracle RAC

    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, orcl3 and 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 tnsnames.ora file.

    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 plug:

    srvctl stop service -db raccont -service plug

    The PDB 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:

    1. $ORACLE_HOME/dbs/spfilesid.ora
    2. $ORACLE_HOME/dbs/spfile.ora
    3. $ORACLE_HOME/dbs/initsid.ora

    On Windows platforms, the search order is as follows:

    1. %ORACLE_HOME%\database\spfilesid.ora
    2. %ORACLE_HOME%\database\spfile.ora
    3. %ORACLE_HOME%\database\initsid.ora

    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

    • ACTIVE_INSTANCE_COUNT   

    This initialization parameter was deprecated in Oracle RAC 11g release 2 (11.2). Instead, use a service with one preferred and one available instance.

    • ASM_PREFERRED_READ_FAILURE_GROUPS       

    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.

    • CLUSTER_DATABASE    

    Enables a database to be started in cluster mode. Set this parameter to TRUE.

    • CLUSTER_DATABASE_INSTANCES   

    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.

    • CLUSTER_INTERCONNECTS   

    Specifies an alternative cluster interconnect for the private network when there are multiple interconnects.

    Notes:

    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.

    • 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 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.

    • 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

    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.

    • RESULT_CACHE_MAX_SIZE   

    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.

    • 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 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.

    • 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 Should Have Identical Settings on All Instances

    • ARCHIVE_LAG_TARGET         

    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.

    • CLUSTER_DATABASE_INSTANCES   

    While it is preferable for this parameter to have identical settings across all Oracle RAC database instances, it is not required.

    • LICENSE_MAX_USERS  

    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.

    • LOG_ARCHIVE_FORMAT         

    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.

    • SPFILE         

    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.

    • TRACE_ENABLED

    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.

    • UNDO_RETENTION         

    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 AUTOMATIC, MANUAL, or 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

    Also See:

    Databases and Database Instances in Oracle 19c

    Oracle RAC Cluster Health Advisor(ochad)