14
Creating a Database & Objects for Multiple Instances

This chapter describes:

Creating a Database for a Multi-instance Environment

This section covers aspects of database creation that are specific to a parallel server:

Summary of Tasks

Database creation tasks specific to the parallel server can be summarized as follows:

Set initialization parameters, including log archiving.
With parallel server disabled, enter the CREATE DATABASE statement, setting MAXINSTANCES and other important options that are specific to a multi-instance environment.
Create rollback segments for each node.
Dismount the database, then remount it with parallel server enabled. Then start up the parallel server.

See Also: "Creating a Database" in Oracle8 Administrator's Guide.

Setting Initialization Parameters for Database Creation

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. Be sure that these are set appropriately before you create a database for a multi-instance environment.

Using ARCHIVELOG Mode

To enable the ARCH process while creating a database, you must set the initialization parameter LOG_ARCHIVE_START to TRUE. Then you can change the mode to ARCHIVELOG with the ALTER DATABASE statement before you start up the instance that creates the database.

Alternatively, you can reduce overhead by creating the database in NOARCHIVELOG mode (the default). Then change to ARHIVELOG mode.

You cannot use the STARTUP command to change the database archiving mode. After creating a database, you can use the following Server Manager commands to change archiving mode and reopen the database with parallel server enabled:

ALTER DATABASE CLOSE;
ALTER DATABASE ARCHIVELOG;
SHUTDOWN;
STARTUP PARALLEL;

See Also: "Archiving the Redo Log Files" on page 21-2
"Parameters Which Must Be Identical on Multiple Instances" on page 18-10

Creating a Database and Starting Up

Use the standard procedure to create a database.

Attention: The CREATE DATABASE statement mounts and opens the newly created database, leaving the parallel server disabled. You must close and dismount the database, then remount it with parallel server enabled.

Start Server Manager.
Connect with SYSDBA privileges.
Start up an instance with the NOMOUNT option.
Issue the CREATE DATABASE statement.
Create additional rollback segments and threads, as needed.
Close and dismount the database.
```
	SVRMGR> SHUTDOWN
```
Update the initialization files to be sure they point to the proper rollback segments and threads, and that parallel server is enabled.

Remount the database.

	SVRMGR> STARTUP [ OPEN databasename ]

Setting CREATE DATABASE Options

This section describes CREATE DATABASE options specific to the parallel server.

Setting MAXINSTANCES

The MAXINSTANCES option of CREATE DATABASE limits the number of instances that can access a database concurrently. MAXINSTANCES defaults to the maximum value specific to your operating system; on most systems the default is 2.

For a parallel server, you should set MAXINSTANCES to a value greater than the maximum number of instances you expect to run concurrently. In this way, if instance A fails and is being recovered by instance B, you will be able to start instance C before instance A is fully recovered.

Setting MAXLOGFILES and MAXLOGMEMBERS

The MAXLOGFILES option of CREATE DATABASE specifies the maximum number of redo log groups that can be created for the database, and the MAXLOGMEMBERS option specifies the maximum number of members (copies) per group.

For a parallel server, you should set MAXLOGFILES to the maximum number of threads possible, times the maximum anticipated number of groups per thread.

Setting MAXLOGHISTORY

The MAXLOGHISTORY option of CREATE DATABASE specifies the maximum number of redo log files that can be recorded in the log history of the control file. The log history is used for automatic media recovery of a parallel server.

For a parallel server, you should set MAXLOGHISTORY to a large value, such as 1000. The control files can then only store information about this number of redo log files. When the log history exceeds this limit, the old history entries are overwritten in a circular fashion. The default for MAXLOGHISTORY is zero, which disables the log history.

Setting MAXDATAFILES

The MAXDATAFILES option is generic, but a parallel server tends to have more data files and log files than standard systems. On your platform the default value of this option may be too low.

See Also: Oracle8 SQL Reference for complete descriptions of the SQL statements CREATE DATABASE and ALTER DATABASE.
See your Oracle operating system-specific documentation for information on default values of CREATE DATABASE options.
"Redo Log Files" on page 6-3 for more information about redo log groups and members.
"Redo Log History in the Control File" on page 21-6 for more information on MAXLOGHISTORY.

Creating Database Objects to Support Multiple Instances

In order to prepare a new database for the parallel server environment, you must also create and configure the following additional database objects.

Creating Additional Rollback Segments

You must create at least one rollback segment for each instance of a parallel server. To avoid contention, create these rollback segments in a tablespace other than the SYSTEM tablespace.

You must create and bring online one additional rollback segment in the SYSTEM tablespace before you can create rollback segments in any other tablespace. The instance that creates the database can create this additional rollback segment and new tablespaces, but it cannot create database objects in non-SYSTEM tablespaces until you bring the additional rollback segment online.

Using Private Rollback Segments

To allocate a private rollback segment to one instance, follow these steps:

Create the rollback segment with the SQL statement CREATE ROLLBACK SEGMENT, omitting the keyword PUBLIC. Optionally, before creating the rollback segment you can create a tablespace for it.
Specify the rollback segment in the instance's parameter file by naming it as a value for the parameter. This reserves the rollback segment for that instance.
Use ALTER ROLLBACK SEGMENT to bring the rollback segment online. You can also restart the instance to use the reserved rollback segment.

A private rollback segment should be specified in only one parameter file so that it is associated with only one instance. If an instance attempts to acquire a private rollback segment that another instance has already acquired, Oracle generates a message and prevents the instance from starting up.

Using Public Rollback Segments

Any instance can create a public rollback segment, which can then be claimed by any instance when it starts up. Once a rollback segment has been claimed, it is only used by the instance that claimed it until the instance shuts down, releasing the rollback segment for use by another instance.

To create a public rollback segment, use the SQL statement CREATE PUBLIC ROLLBACK SEGMENT.

Typically, the parameter file for any particular instance does not specify public rollback segments because they are assumed to be available to any instance needing them. However, if another instance is not already using it, you can name a public rollback segment as a value of the ROLLBACK_SEGMENTS parameter.

Public rollback segments are identified in the data dictionary view DBA_ROLLBACK_SEGS as having the owner PUBLIC.

If the parameter file omits the ROLLBACK_SEGMENTS initialization parameter, the instance uses public rollback segments by default.

A public rollback segment is brought online when an instance that requires public rollback segments starts up and acquires it. However, starting an instance that uses public rollback segments does not ensure that any particular public rollback segment comes online, unless the instance acquires all of the available public rollback segments. Once acquired, a public rollback segment is used exclusively by the acquiring instance.

Bringing online, taking offline, creating, and dropping rollback segments, whether private or public, is the same whether parallel server is enabled or disabled.

Private rollback segments stay offline until brought online or the owning instance restarts. A public rollback segment stays offline until brought online for a specific instance or until an instance that requires a public rollback segment starts up and acquires it.

If you need to keep a public rollback segment offline and do not want to drop it and re-create it, you must ensure no instance starts up that requires public rollback segments.

Monitoring Rollback Segments

You can use the Server Manager command MONITOR ROLLBACK to display information about the status of the rollback segments that the current instance uses.

Alternatively, you can query the dynamic performance views V$ROLLNAME and V$ROLLSTAT for information about the current instance's rollback segments.

Use the Server Manager command CONNECT @instance-path to change the current instance before using the MONITOR command or querying the V$ views. You must have Net8 installed to use the CONNECT command for an instance on a remote node.

You can also query the data dictionary views DBA_ROLLBACK_SEGS and DBA_SEGMENTS for information about the current status of all rollback segments in your database.

For example, to list all the current rollback segments, you can query DBA_ROLLBACK_SEGS with the following statement:

SELECT segment_name, segment_id, owner, status 
      FROM dba_rollback_segs

This query displays the rollback segment's name, ID number, owner, and whether it is in use, as shown in the following example:

SEGMENT_NAME             SEGMENT_ID     OWNER     STATUS 
------------------------ ----------     ------    ------------ 
SYSTEM                            0     SYS       ONLINE
PUBLIC_RS                         1     PUBLIC    ONLINE
USERS1_RS                         2     SYS       ONLINE
USERS2_RS                         3     SYS       OFFLINE
USERS3_RS                         4     SYS       ONLINE
USERS4_RS                         5     SYS       ONLINE
PUBLIC2_RS                        6     PUBLIC    OFFLINE

In the above example, rollback segments identified as owned by user SYS are private rollback segments; the rollback segments identified as owned by user PUBLIC are public rollback segments. The view DBA_ROLLBACK_SEGS also includes information (not shown) about the tablespace containing the rollback segment, the datafile containing the segment header, and the extent sizes. The view DBA_SEGMENTS includes additional information about the number of extents in each rollback segment and the segment size.

See Also: Oracle8 Administrator's Guide for more information about rollback segments, and about connecting to a database.
Oracle Net8 Administrator's Guide and your Oracle system-specific documentation for the format of the connect string in instance-path.
Oracle8 Reference for a description of DBA_ROLLBACK_SEGS and DBA_SEGMENTS, and other dynamic performance views.

Configuring the Online Redo Log for a Parallel Server

Each database instance has its own "thread" of online redo, consisting of its own online redo log groups. When running a parallel server, two or more instances concurrently access a single database, and each instance must have its own thread. This section explains how to configure these online redo threads for multiple instances with a parallel server.

You must create each thread with at least two redo log files (or multiplexed groups), and you must enable the thread before an instance can use it.

The CREATE DATABASE statement creates thread number 1 as a public thread and enables it automatically. You must use the ALTER DATABASE statement to create and enable subsequent threads.

Creating Threads

Threads can be either public or private. The initialization parameter THREAD assigns a unique thread number to the instance. If THREAD is zero, the default, the instance acquires an available public thread by default.

Each thread must be created with at least two redo log files, or multiplexed groups, and the thread must then be enabled before an instance can use it.

The CREATE DATABASE statement creates thread number 1 as a public thread and enables it automatically. Subsequent threads must be created and enabled with the ALTER DATABASE statement. For example, the following statements create thread 2 with two groups of three members each, as shown in Figure 6-1 on page 6 - 4:

ALTER DATABASE ADD LOGFILE THREAD 2 
  GROUP 4 (disk1_file4, disk2_file4, disk3_file4) SIZE 1M REUSE 
  GROUP 5 (disk1_file5, disk2_file5, disk3_file5) SIZE 1M REUSE; 
ALTER DATABASE ENABLE PUBLIC THREAD 2;

If you omit the keyword PUBLIC when you enable the thread, it will be a private thread that cannot be acquired by default. Only one thread number may be specified in the ALTER DATABASE ADD LOGFILE statement, and the THREAD clause must be specified if the thread number of the current instance was chosen by default.

Disabling Threads

You can disable a public or private thread with the statement ALTER DATABASE DISABLE THREAD. You cannot disable a thread if an instance using the thread has the database mounted. To change a thread from public to private, or vice versa, you must disable it and then enable it again. An instance cannot disable its own thread. The database must be open when you disable or enable a thread.

When you disable a thread, Oracle marks its current redo log file as needing to be archived. If you want to drop that file, you might need to first archive it manually.

An error or failure while a thread is being enabled can result in a thread that has a current set of log files but is not enabled. These log files cannot be dropped or archived. In this case, you should disable the thread, even though it is already disabled, then enable it.

Setting the Log's Mode

The mode of using the redo log (ARCHIVELOG or NOARCHIVELOG) is set at database creation. Although rarely necessary, the archive mode can be changed by the SQL statement ALTER DATABASE. When archiving is enabled, online redo log files cannot be reused until they are archived. To switch archiving mode, the database must be mounted with parallel server disabled, but it cannot be open.

The redo log mode is associated with the database rather than with individual instances. For most purposes, all instances should use the same archiving method (automatic or manual) if the redo log is being used in ARCHIVELOG mode.

Changing the Redo Log

You can change the configuration of the redo log (add, drop, or rename a log file or log file member) while the database is mounted with parallel server either enabled or disabled. The only restrictions are that you cannot drop or rename a log file or log file member that is currently in use by any thread, and you cannot drop a log file if that would reduce the number of log groups below two for the thread it is in.

Any instance can add or rename redo log files (or members) of any group for any other instance. As long as there are more than two groups for an instance, a redo log group can be dropped from that instance by any other instance. Changes to redo log files and log members take effect on the next log switch.

Providing Locks for Added Datafiles

If datafiles are added while a parallel server is running, you must evaluate whether enough locks are available to cover the new files.

Added datafiles use the unassigned locks which were created when the value for GC_FILES_TO_LOCKS was set. If the remaining locks are not adequate to protect the new files and avoid contention, then you must provide more locks by adjusting these two GC parameters. Performance problems are likely if you neglect to make these adjustments.

Note that in a read-only database extra locks would not be necessary even if you added many new datafiles. In a database heavily used for inserts, however, you might very well need to provide for more locks.

Analyze whether the remaining locks are adequate. If more are needed, then go on to the next step.
Shut down the system.
Modify the GC_FILES_TO_LOCKS initialization parameter to provide enough locks for the additional datafiles.
Restart the system.

Changing the Value of CREATE DATABASE Options

You can use the CREATE CONTROLFILE statement to change the value of the following database parameters for an existing database:

MAXINSTANCES
MAXLOGFILES
MAXLOGMEMBERS
MAXLOGHISTORY
MAXDATAFILES

See Also: Oracle8 SQL Reference for a description of the statements CREATE CONTROLFILE and ALTER DATABASE BACKUP CONTROLFILE TO TRACE.

14Creating a Database & Objects for Multiple Instances