Release 8.0
A58238-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8 Parallel Server Concepts & Administration Release 8.0 A58238-01 |
|
Justice is a machine that, when someone has once given it the starting push, rolls on of itself.
This chapter describes how to administer instances of a parallel server. It includes the following topics:
This chapter explains how to set up and then start up instances for a parallel server using the following general procedure:
See Also: "Starting Up and Shutting Down" in Oracle8 Administrator's Guide.
Note also that Oracle Parallel Server Management (OPSM) is available. This is a comprehensive and integrated system management solution for the Oracle Parallel Server. OPSM allows you to manage multi-instance databases running in heterogeneous environments through an open client-server architecture.
In addition to managing parallel databases, OPSM allows you to schedule jobs, perform event management, monitor performance, and obtain statistics to tune parallel databases.
For more information about OPSM, refer to the Oracle Parallel Server Management Configuration Guide for UNIX and the Oracle Parallel Server Management User's Guide. For installation instructions, refer to your platform-specific installation guide.
When an instance starts up, Oracle uses the values found in an initialization parameter file to create the System Global Area (SGA) for that instance. You can use various approaches to define multiple instances:
A common parameter file for all instances, shown in Figure 18-1, can make administration easy. If file systems are shared among nodes, you can update all instances by making a change in only one place.
Most clustering systems, however, do not share file systems. In this case you would have to make for each node a separate physical copy of the common file.
Individual parameter files are useful when many parameters should differ from instance to instance. For example, initialization parameters to create difference size SGAs for different size machines may improve performance dramatically.
By setting the IFILE parameter, each individual parameter file can embed an additional parameter file containing common values. This approach is illustrated in Figure 18-3.
In a parallel server, some initialization parameters must have the same values for every instance, whether individual or common parameter files are used. By referencing the same file using the IFILE parameter, instances which have individual parameter files can ensure that they have the correct parameter values for those which must be identical, while allowing individual values for parameters which can differ.
Instances must use individual parameter files in the following cases:
For example, a Server Manager session on the local node can start up two instances on remote nodes using individual parameter files named INIT_A.ORA and INIT_B.ORA:
SET INSTANCE instance1; STARTUP PFILE=init_a.ora; SET INSTANCE instance2; STARTUP PFILE=init_b.ora;
Here, "instance1" and "instance2" are Net8 aliases for the two respective instances, as defined in TNSNAMES.ORA.
Both individual parameter files can use the IFILE parameter to include parameter values from the file INIT_COMMON.ORA. They can reference this file as follows:
INIT_A.ORA:
IFILE=INIT_COMMON.ORA INSTANCE_NUMBER=1 THREAD=1
INIT_B.ORA:
IFILE=INIT_COMMON.ORA INSTANCE_NUMBER=2 THREAD=2
The INIT_COMMON.ORA file can contain the following parameter settings, which must be identical on both instances.
DB_NAME=DB1 CONTROL_FILES=(CTRL_1,CTRL_2,CTRL_3) GC_FILES_TO_LOCKS="1=600:2-4,9=500EACH:5-8=800" GC_ROLLBACK_SEGMENTS=10 GC_SEGMENTS=10 LOG_ARCHIVE_START=TRUE PARALLEL_SERVER=TRUE
Each parameter file must contain the same values for the CONTROL_FILES parameter, for example, because all instances share the control files.
To change the value of a common initialization parameter, you would only have to modify the file INIT_COMMON.ORA, rather than changing both individual parameter files.
When you specify parameters which have identical values in a common parameter file referred to by the IFILE parameter, you can omit parameters for which you are using the default values.
If you use multiple Server Manager sessions on separate nodes to start up the instances, each node must have its own copy of the common parameter file (unless the file systems are shared).
If a parameter is duplicated in an instance-specific file and the common file, or within one file, the last value specified overrides earlier values. You can therefore ensure the use of common parameter values by placing the IFILE parameter at the end of an individual parameter file. Placing IFILE at the beginning of the individual file allows you to override the common values.
You can specify IFILE more than once in a parameter file to include multiple common parameter files. Unlike the other initialization parameters, IFILE does not override previous values. For example, an individual parameter file might include a file INIT_COMMON.ORA and separate command files for the LOG_* and GC_* parameters:
IFILE=INIT_COMMON.ORA IFILE=INIT_LOG.ORA IFILE=INIT_GC.ORA LOG_ARCHIVE_START=FALSE THREAD=3 ROLLBACK_SEGMENTS=(RB_C1,RB_C2,RB_C3)
The individual value of LOG_ARCHIVE_START overrides the value specified in INIT_LOG.ORA, because the IFILE = INIT_LOG.ORA appears before LOG_ARCHIVE_START parameter specification. The individual GC_* values specified in INIT_GC.ORA override any values specified in INIT_COMMON.ORA, because IFILE = INIT_GC.ORA comes after IFILE = INIT_COMMON.ORA.
The PFILE option of the STARTUP command allows you to specify a parameter file other than the default file when you start up an instance. The parameter file specified by PFILE must be on a disk accessible to the local node, even for an instance on a remote node.
This section discusses initialization parameters which are important for a parallel server.
See Also: Oracle8 Reference for details about other Oracle initialization parameters.
Initialization parameters with the prefix GC (Global Constant) are relevant only for a parallel server. The settings of these parameters determine the size of the collection of global locks which protect the database buffers on all instances. The settings you choose affect use of certain operating system resources.
The first instance to start up in shared mode determines the values of the global constant parameters for all instances. The control file records the values of the GC_* parameters when the first instance starts up.
When another instance attempts to start up in shared mode, Oracle compares the values of the global constant parameters in its parameter file with those already in use and issues a message if any values are incompatible. The instance cannot mount the database unless it has correct values for its global constant parameters.
The global constant parameters for a parallel server are:
See Also: Chapter 15, "Allocating PCM Instance Locks".
Multi-instance issues concerning initialization parameters are summarized in Table 18-1.
See Also: Oracle8 Reference for details about each parameter.
Certain initialization parameters that are critical at database creation or that affect certain database operations must have the same value for every instance in a parallel server. For example, the values of DB_BLOCK_SIZE and CONTROL_FILES must be identical for every instance. Other parameters can have different values for different instances. The following initialization parameters must have identical values for every instance in a parallel server:
See Also: Oracle8 Reference for details about each parameter.
Set values for the LM_* initialization parameters. Note that the resources, locks and processes configurations are per OPS instance. For ease of administration, these parameters should be consistent for all the instances.
Increased values will be necessary if you plan to use parallel DML or DML performed on partitioned objects.
Creating a database automatically starts up a single instance with parallel server disabled. Before you can start up multiple instances, however, you must perform certain administrative operations. These tasks may include:
You can perform these operations with a single instance in either exclusive or shared mode.
An Oracle instance can start up with parallel server enabled or disabled. This section includes the following topics:
Note: In Oracle8 the keywords SHARED, EXCLUSIVE, and PARALLEL are obsolete in the STARTUP and ALTER DATABASE MOUNT statements.
For more information, see your Oracle system-specific documentation.
See "Using Group Membership Services" on page 18-21 for more information.
CONNECT username/password AS SYSDBA
STARTUP NOMOUNT
ALTER DATABASE database_name MOUNT
ALTER DATABASE OPEN
Note: The Server Manager command STARTUP with the OPEN option performs steps 4, 5, and 6 of the procedure given above.
For more information, see your Oracle system-specific documentation.
See "Using Group Membership Services" on page 18-21 for more information.
STARTUP OPEN database_name
Parallel server must be disabled whenever you change the archiving mode (ARCHIVELOG or NOARCHIVELOG). To change the archiving mode, the database must be mounted but not open.
If an instance mounts a database with PARALLEL_SERVER set to FALSE, no other instance can mount the database.
Before you can start up an instance in exclusive mode, you must shut down all instances running in shared mode. A single instance running in shared mode is not the same as an instance running in exclusive mode, and the last instance running in shared mode does not automatically revert to exclusive mode.
An instance starting up with parallel server disabled can specify an instance number with the INSTANCE_NUMBER parameter. This is only necessary if the instance will perform inserts and updates and if the tables in your database use the FREELIST GROUPS storage option to allocate free space to instances. If you start up an instance just to perform administrative operations with parallel server disabled, you can omit the INSTANCE_NUMBER parameter from the parameter file.
An instance starting up with parallel server disabled can also specify a thread other than 1, to use the online redo log files associated with that thread.
See Also: Chapter 17, "Using Free List Groups to Partition Data"
In a parallel server, each instance must mount the database in shared mode. Each initialization parameter file for each instance must have the SINGLE_PROCESS parameter set to FALSE and the PARALLEL_SERVER parameter set to TRUE. Before you start up multiple instances in shared mode, you must create at least one rollback segment for each instance sharing the same database and enable a thread containing at least two groups of redo log files for each additional instance.
If one instance mounts a database in shared mode, other instances can also mount the database in shared mode, but not in exclusive mode.
If PARALLEL_SERVER is set to FALSE, the instance tries to start up with parallel server disabled by default.
If you attempt to start an instance and mount a database in shared mode while another instance is currently recovering the same database, your new instance cannot mount the database until the recovery is complete.
Rather than repeatedly attempting to start the instance, you can use the STARTUP RETRY statement. This causes the new instance to retry every five seconds to mount the database until it succeeds or has reached the retry limit. For example:
STARTUP OPEN maildb RETRY
To set the maximum number of times the instance attempts to mount the database, use the Server Manager SET command with the RETRY option; you can specify either an integer (such as 10) or the keyword INFINITE.
If the database cannot be opened for some reason other than recovery by another instance, then the RETRY will not repeat. For example, if the database was mounted in exclusive mode by one instance, then trying the STARTUP RETRY command in shared mode will not work for another instance.
When an instance starts up, it acquires an instance number which maps the instance to one group of free lists for each table created with the FREELIST GROUPS storage option.
An instance can specify its instance number explicitly by using the initialization parameter INSTANCE_NUMBER when it starts up with parallel server enabled or disabled. If an instance does not specify the INSTANCE_NUMBER parameter, it automatically acquires the lowest available number.
Startup order determines the instance numbers for instances which do not specify the INSTANCE_NUMBER parameter. Startup numbers are difficult to control if instances start up in parallel, and they can change after instances shut down and restart.
Instances which use the INSTANCE_NUMBER parameter must specify different numbers. The Server Manager command SHOW PARAMETERS INSTANCE_NUMBER can show the current instance number each instance is using. This command displays a null value if an instance number was assigned based on startup order.
After an instance shuts down, its instance number becomes available again. If a second instance starts up before the first instance restarts, the second instance can acquire the instance number previously used by the first instance.
Instance numbers based on startup order are independent of instance numbers specified with the INSTANCE_NUMBER parameter. After an instance acquires an instance number by one of these methods (either with or without INSTANCE_NUMBER), another instance cannot acquire the same number by the other method. All numbers are unique, regardless of the method by which they are acquired.
Always use the INSTANCE_NUMBER parameter if you need a consistent allocation of extents to instances for inserts and updates. This allows you to maintain data partitioning among instances.
When performing administrative operations in a multi-instance environment, you must be sure that you have specified the correct instance. This section includes the following topics:
Some Server Manager commands apply to the instance to which Server Manager is currently connected, and others apply to the default instance.
The current instance can be different from the default instance if you specify a connect string in the CONNECT command.
Net8 must be installed to use the SET INSTANCE or CONNECT command for an instance running on a remote node.
See Also: Your platform-specific Oracle documentation, for more information about installing Net8 and the exact format required for the connect string used in the SET INSTANCE and CONNECT commands.
Instance-specific SQL statements apply to the current instance. For example, the statement ALTER DATABASE ADD LOGFILE only applies to the instance to which you are currently connected, rather than the default instance or all instances.
ALTER SYSTEM CHECKPOINT LOCAL applies to the current instance. By contrast, ALTER SYSTEM CHECKPOINT or ALTER SYSTEM CHECKPOINT GLOBAL applies to all instances.
ALTER SYSTEM SWITCH LOGFILE applies only to the current instance. To force a global log switch, you can use ALTER SYSTEM ARCHIVE LOG CURRENT. The THREAD option of ALTER SYSTEM ARCHIVE LOG allows you to archive online redo log files for a specific instance.
When you initiate Server Manager, the commands you enter are relevant to the default instance, which is also the current instance.
This is true until you use the SET INSTANCE command to set the current instance. From that point onwards, all Server Manager commands operate on the current instance.
Note: The security mechanism invoked when you use privileged Server Manager commands depends on the operating system you are using. Most operating systems have a secure authentication mechanism when logging onto the operating system. On these systems, your default operating system privileges will usually determine whether you can use STARTUP and SHUTDOWN. For more information, see your Oracle system-specific documentation.
You can change the default instance with the Server Manager statement:
SET INSTANCE instance_path
where instance_path is a valid Net8 connect string (without a user ID/password). If you are connected to an instance, you must disconnect before using SET INSTANCE. Alternatively, if you do not wish to disconnect from the current instance, you may use the CONNECT command with instance_path.
You can use the SET INSTANCE command to specify an instance on a remote node for the commands STARTUP and SHUTDOWN. The parameter file for a remote instance must be on the local node.
The SHOW INSTANCE command displays the connect string for the default instance. SHOW INSTANCE returns the value local if you have not used SET INSTANCE during the Server Manager session.
To reset to the default instance, use SET INSTANCE without specifying a connect string or specify LOCAL (but not DEFAULT, which would indicate a connect string for an instance named "DEFAULT").
The following Server Manager line mode examples illustrate the relationship between SHOW INSTANCE and SET INSTANCE:
SHOW INSTANCE Instance local SET INSTANCE node1 Oracle8 Server Release 8.0 - Production With the distributed, parallel query and Parallel Server options PL/SQL V8.0 - Production SHOW INSTANCE Instance node2 SET INSTANCE ORACLE8 Server Release 8.0 - Production With the procedural, distributed, and Parallel Server options PL/SQL V8.0 - Production SHOW INSTANCE Instance local SET INSTANCE DEFAULT ORA-06030: NETDNT: connect failed, unrecognized node name
The CONNECT command can associate Server Manager with either the default instance or an instance which you specify explicitly. The instance to which Server Manager connects becomes the current instance.
The CONNECT command has the following syntax:
where instance-path is a valid Net8 connect string. CONNECT without the argument @instance-path connects to the default instance (which may have been set previously with SET INSTANCE).
Connecting as SYSOPER or SYSDBA allows you to perform privileged operations, such as instance startup and shutdown.
Multiple Server Manager sessions can connect to the same instance at the same time. When you are connected to one instance, you can connect to a different instance without using the DISCONNECT command. Server Manager disconnects you from the first instance automatically whenever you connect to another one.
The CONNECT @instance-path command allows you to specify an instance before using the Server Manager commands MONITOR, STARTUP, SHUTDOWN, SHOW SGA, and SHOW PARAMETERS.
Group Membership Services (GMS) is used by the Lock Manager (LM) and other Oracle components for inter-instance initialization and coordination. Instances of a distributed service can register with the GMS and retrieve the current set of instances providing the same service cluster-wide. The GMS monitors each of its clients and notifies the other instances of a given service when one instance stops or is shut down. It obtains a view of the current cluster membership from the (system specific) cluster management software.
If a GMS instance or a node stops, the remaining GMS instances are informed through the cluster manager. Providers of distributed services are then be notified by the GMS if any of their peers stopped as a result of the node stoppage.
Platforms which use the opsctl program start GMS automatically. For other platforms, you must start this process by manually issuing the ogmsctl command. This program has the following options:
When you have installed the Oracle Parallel Server option, you must start the GMS, even to bring the instance up with parallel server disabled. If OPS is linked in, Oracle starts the Integrated Distributed Lock Manager and connects to the GMS to obtain a mount lock. This prevents you from accidentally mounting the database exclusive on more than one mode.
See Also: Your Oracle system-specific documentation to determine whether GMS is started automatically, and whether it requires additional cluster configuration.
For ease in administration, you can logically group different instances together and perform parallel operations upon all of the associated instances at once. You can define an instance group as a set of instances used for a specific purpose (such as resource allocation, parallel query or other parallel operations). They thus enable you to partition your resources effectively.
Sometimes, for example, a DBA may wish to prevent users or query processes from obtaining resources on all instances. The DBA may want to keep certain instances available only for users running OLTP processes, and restrict users running parallel queries only to a particular set of instances.
For example, you might create instance groups such that between 9 AM and 5 PM users can use group B, but after 5 PM they can use group D. Or, you might use group C for normal OLTP inserts and updates but use group D for big parallel tasks, to avoid interfering with OLTP performance.
If you simply set the degree of parallelism, the system chooses which specific instances to use (given disk affinity, and the number of instances actually running). By specifying instance groups you can directly specify the instances which should be used for parallel operations.
Note that the instance from which you initiate a query, need not be a member of the group of instances which carry out the query. The parallel coordinator does run on the current instance.
To specify instance groups, set the INSTANCE_GROUPS initialization parameter within the parameter file of each instance you wish to associate to the group. This parameter at once defines a group and adds the current instance to the group.
For example, instance 1 could set the parameter as follows:
INSTANCE_GROUPS = groupB, groupD
Instance 3 could set it as follows:
INSTANCE_GROUPS = groupA, groupD
As a result, instances 1 and 3 would both belong to instance group D, but would also belong to other groups as well.
Note that INSTANCE_GROUPS cannot be changed dynamically.
You can use instance groups for two purposes:
The default for PARALLEL_INSTANCE_GROUP and OPS_ADMIN_GROUP is a group consisting of all currently running instances.
To use a particular instance group for a given parallel operation, specify the following parameter in the initialization parameter file:
PARALLEL_INSTANCE_GROUP = groupname
All parallel operations initiated from that instance will spawn processes only within that group, using the same algorithm as before (either randomly or with disk affinity).
PARALLEL_INSTANCE_GROUP is a dynamic parameter which you can change using an ALTER SESSION or ALTER SYSTEM statement. You can use it to refer to only one instance group; by default it is set to a default group which includes all currently active instances. The instance upon which you are running need not be a part of the instance group which you are going to use for a particular operation.
To determine the instances which should return information in a GV$viewname query, set the OPS_ADMIN_GROUP parameter.
To find out the members of the different instance groups you can query the GV$ global dynamic performance view GV$PARAMETER. Look at all entries for the INSTANCE_GROUPS parameter name.
In this example, instance 1 has the following settings in its initialization parameter file:
INSTANCE_GROUPS = Ga, Gb PARALLEL_INSTANCE_GROUP = Gb
Instance 2 has the following setting in its initialization parameter file:
INSTANCE_GROUPS = Gb, Gc PARALLEL_INSTANCE_GROUP = Gc
On instance 1, if you enter the following statements, the instances in Gb will be used. Two server processes will be spawned on instance 1, and 2 server processes on instance 2.
ALTER TABLE table PARALLEL (DEGREE 2 INSTANCES 2); SELECT COUNT(*) FROM table;
If you enter the following statements on instance 1, Gc will be used. Two server processes will be spawned on instance 2 only.
ALTER SESSION SET PARALLEL_INSTANCE_GROUP = 'Gc'; SELECT COUNT(*) FROM table;
If you enter the following statements on instance 1, the default instance group (all currently running instances) will be used. Two server processes will be spawned on instance 1, and 2 server processes on instance 2.
ALTER SESSION SET PARALLEL_INSTANCE_GROUP = ''; SELECT COUNT(*) FROM table;
You can use a password file to authenticate users performing database administration when running multiple instances on a parallel server. In this case, the environment variable for each instance must point to the same password file. Similarly, the REMOTE_LOGIN_PASSWORDFILE initialization parameter for each instance must be set to the appropriate, identical value.
See Also: Oracle8 Administrator's Guide for information about the REMOTE_LOGIN_PASSWORDFILE parameter.
For more information on the exact name of the password file, or for the name of the environment variable used to specify this name for your operating system, see your Oracle system-specific documentation.
Use the following procedure to shut down an instance:
CONNECT username/password AS SYSDBA
ALTER DATABASE database_name CLOSE
ALTER DATABASE database_name DISMOUNT
Alternatively, you can use the Server Manager command SHUTDOWN, which performs all three of these steps for the current instance.
In a parallel server, shutting down one instance does not interfere with the operations of any instances still running.
To shut down a database which is mounted in shared mode, you must shut down every instance in the parallel server. The parallel server allows you to shut down instances in parallel from different nodes. When an instance shuts down abnormally, Oracle forces all user processes running in that instance to log off the database. If a user process is currently accessing the database, Oracle terminates that access and returns the message "ORA-1092: Oracle instance terminated. Disconnection forced". If a user process is not currently accessing the database when the instance shuts down, Oracle returns the message "ORA-1012: Not logged on" upon the next call or request made to Oracle.
After a NORMAL or IMMEDIATE shutdown, instance recovery is not required. Recovery is required, however, after the SHUTDOWN ABORT command or after an instance terminates abnormally. The SMON process of an instance which is still running performs instance recovery for the instance which shut down. If no other instances are running, the next instance to open the database performs instance recovery for any instances which need it.
If multiple Server Manager sessions are connected to the same instance simultaneously, all but one must disconnect before the instance can be shut down normally. You can use the IMMEDIATE or ABORT option of the SHUTDOWN command to shut down an instance when multiple Server Manager sessions (or any other sessions) are connected to it.
See Also: "Starting Up and Shutting Down" in Oracle8 Administrator's Guide for options of the SHUTDOWN command.
Although the parallel query feature does not require the Oracle Parallel Server, some aspects of parallel query apply only to a parallel server.
The INSTANCES keyword of the PARALLEL clause of the CREATE TABLE, ALTER TABLE, CREATE CLUSTER, and ALTER CLUSTER commands allows you to specify that a table or cluster is split up among the buffer caches of all available instances of a parallel server when the table is scanned in a parallel query.
If you do not want tables to be dynamically partitioned among all the available instances, you can specify the number of instances that can participate in scanning or caching with the PARALLEL_DEFAULT_MAX_INSTANCES parameter or the ALTER SYSTEM command.
If you want to specify the number of instances to participate in parallel query processing at startup time, you can specify a value for the initialization parameter PARALLEL_DEFAULT_MAX_INSTANCES.
If you want to limit the number of instances available for parallel query processing dynamically, use the ALTER SYSTEM command. For example, if your parallel server has ten instances running, but you want only eight to be involved in parallel query processing, while the remaining two instances will be dedicated for other use, you can issue the following command:
ALTER SYSTEM SET SCAN_INSTANCES = 8;
Thereafter, if a table's definition has a value of ten specified for the INSTANCES keyword, the table will be scanned by query servers on only eight of the ten instances. Oracle selects the first eight instances in this example. You can set the PARALLEL_MAX_SERVERS initialization parameter to zero on the instances that you do not want to participate in parallel query processing.
If you wish to limit the number of instances that cache a table, you can issue the following command:
ALTER SYSTEM SET CACHE_INSTANCES = 8;
Thereafter, if a table definition has 10 specified for the INSTANCES keyword and the CACHE keyword was specified, the table will be divided evenly among eight of the ten available instances' buffer caches.
See Also: "Specifying Instance Groups" on page 18-22.
Oracle8 Reference for more information about parameters.
