|Oracle7 Parallel Server Concepts and Administrator's Guide||
John Galsworthy: Justice. Act II.
This chapter describes how to administer instances of a parallel server. It includes the following topics:
To Define and Start Instances
See Also: "Starting Up and Shutting Down" in Oracle7 Server Administrator's Guide.
Figure 19 - 1. Instances with a Common Parameter File
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.
Figure 19 - 2. Instances with Individual Parameter Files
Figure 19 - 3. Instances with Individual Parameter Files and IFILE
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:
SET INSTANCE instance1;
STARTUP PFILE=init_a.ora PARALLEL;
SET INSTANCE instance2;
STARTUP PFILE=init_b.ora PARALLEL;
Here, "instance1" and "instance2" are SQL*Net 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:
The INIT_COMMON.ORA file can contain the following parameter settings, which must be identical on both instances.
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.
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.
See Also: "Instance Numbers and Startup Sequence" .
"Threads of Redo" .
"Parameters Which Must Be Identical" .
Oracle7 Server Reference for details about all other Oracle initialization parameters.
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 the correct values for its global constant parameters.
The global constant parameters for a parallel server are:
|Parameter||Parallel Server Notes|
|CHECKPOINT_PROCESS||In Oracle Parallel Server your database may have more datafiles. To speed up checkpoints, enable the CHECKPOINT_PROCESS parameter.|
|DELAYED_LOGGING_BLOCK_ CLEANOUTS||If set to True, this parameter could potentially reduce pinging between instances.|
|DML_LOCKS||This parameter must be identical on all instances only if set to zero.|
|INSTANCE_NUMBER||If specified, this parameter must have unique values for different instances.|
|LOG_ARCHIVE_FORMAT||You must include thread number.|
|MAX_COMMIT_PROPAGATION_ DELAY||If you want commits to be seen immediately on remote instances, you may need to change the value of this parameter.|
|NLS_* parameters||This parameter can have different values for different instances.|
|PROCESSES||This parameter must have a value large enough to allow for all background processes and all user processes in an instance. Some operating systems can have additional DBWR processes. Defaults for the SESSIONS and TRANSACTIONS parameters are derived directly or indirectly from the value of the PROCESSES parameter If you do not use the defaults, you may want to increase some of these parameter values to allow for LCKn and other optional background processes.|
|RECOVERY_PARALLELISM||To speed up the roll forward or cache recovery phase, you may want to set this parameter.|
|ROLLBACK_SEGMENTS||Specify the private rollback segments for each instance.|
|THREAD||If specified, this parameter must have unique values for different instances.|
See Also: Oracle7 Server Reference for details about each parameter.
The following initialization parameters must have identical values for every instance in a parallel server:
|DML_LOCKS (must be identical only if set to zero)|
Refer to your operating system documentation for complete details on DLM initialization parameters.
See Also: "Allocating PCM Locks" .
"Adjusting DLM Capacity for Non-PCM Locks" .
See Also: "Creating Additional Rollback Segments" .
"Threads of Redo" .
"What is the Total Number of PCM Locks & Resources Needed?" .
CONNECT username/password AS SYSDBA
ALTER DATABASE database_name MOUNT [EXCLUSIVE | PARALLEL]
ALTER DATABASE OPEN
The Server Manager command STARTUP with the OPEN option performs steps 3, 4, and 5.
To Start an Instance Using Server Manager
STARTUP OPEN database_name [EXCLUSIVE | PARALLEL]
If an instance mounts a database with the EXCLUSIVE option, 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 in exclusive mode 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 in exclusive mode, you can omit the INSTANCE_NUMBER parameter from the parameter file.
An instance starting up in exclusive mode can also specify a thread other than 1, to use the online redo log files associated with that thread.
See Also: "Using Free List Groups to Partition Data" for more information.
If one instance mounts a database with the PARALLEL option, other instances can also mount the database with the PARALLEL option but not with the EXCLUSIVE option.
If you omit the PARALLEL option, the instance tries to start up in exclusive mode by default.
There is no difference between the options PARALLEL and SHARED in either the ALTER DATABASE statement or the STARTUP command.
Rather than repeatedly attempting to start the instance, you can use the RETRY option in the STARTUP PARALLEL command or in the dialog box. 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 PARALLEL 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 EXCLUSIVE by one instance, then trying the STARTUP PARALLEL RETRY command will not work for another instance.
An instance can specify its instance number explicitly by using the initialization parameter INSTANCE_NUMBER when it starts up in either shared or exclusive mode. 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.
See Also: "Rollback Segments" on page 6 - 7.
"Creating Additional Rollback Segments" .
"Threads of Redo" .
"Using Free List Groups to Partition Data" for information about allocating free space for inserts and updates.
The default instance is on the machine where you initiate Server Manager. Server Manager commands which cannot be used while you are connected to an instance (such as executing a host command) apply to the default instance.
The current instance is determined by the CONNECT command. Server Manager commands which can be used while you are connected to an instance apply to the current instance.
The current instance can be different from the default instance if you specify a connect string in the CONNECT command.
SQL*Net 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 SQL*Net and the exact format required for the connect string used in the SET INSTANCE and CONNECT commands.
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.
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.
|Server Manager Command||Associated Instance|
|ARCHIVE LOG||always applies to the current instance|
|CONNECT||uses the default instance if no instance is specified in the CONNECT command|
|CONNECT INTERNAL||always applies to the current instance. A privileged Server Manager command.|
|HOST||applies to the node running the Server Manager session, regardless of the location of the current and default instances|
|MONITOR||MONITOR display screens identify the current instance, not the default instance, in the upper left corner.|
|RECOVER||does not apply to any particular instance, but rather to the database|
|SHOW INSTANCE||displays information about the default instance, which can be different from the current instance|
|SHOW PARAMETERS||displays information about the current instance|
|SHOW SGA||displays information about the current instance|
|SHUTDOWN||always applies to the current instance. A privileged Server Manager command.|
|STARTUP||always applies to the current instance. A privileged Server Manager command.|
Additional Information: 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 CONNECT INTERNAL, STARTUP, and SHUTDOWN. For operating systems with non-secure authentication mechanisms, you are usually required to enter a password for CONNECT INTERNAL. For more information, see your Oracle system-specific documentation.
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 Oracle7 Server Release 7.3.2 - Production With the distributed, parallel query and Parallel Server options PL/SQL V18.104.22.168.0 - Production SHOW INSTANCE Instance node2 SET INSTANCE ORACLE7 Server Release 7.3.2 - Production
With the procedural, distributed, and Parallel Server options
PL/SQL V22.214.171.124.0 - Production
SET INSTANCE DEFAULT
ORA-06030: NETDNT: connect failed, unrecognized node name
The CONNECT command has the following syntax:
where instance-path is a valid SQL*Net 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.
See Also: Oracle Server Manager User's Guide for syntax of Server Manager commands.
Oracle Network Manager Administrator's Guide for the proper specification of instance_path.
Oracle7 Server Administrator's Guide for information on connecting with SYSDBA or SYSOPER privileges.
See Also: Oracle7 Server 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.
To Shut Down an Instance Using SQL
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 Oracle7 Server Administrator's Guide, for options of the SHUTDOWN command.
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 parameter PARALLEL_DEFAULT_MAX_INSTANCES 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 initialization parameter PARALLEL_MAX_SERVERS 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: "Initialization Parameters" .
Oracle7 Server Reference for more information about parameters.
Copyright © 1996 Oracle Corporation.
All Rights Reserved.