Oracle8i Parallel Server Administration, Deployment, and Performance
Release 2 (8.1.6)

Part Number A76970-01





Go to previous page Go to next page

Oracle Parallel Server Database Creation Issues

This chapter describes issues surrounding the creation of Oracle Parallel Server databases. Information in this chapter supplements information presented in the Oracle8i Parallel Server Setup and Configuration Guide for using the Database Creation Assistant. Topics in this chapter include:

Creating a Database for Multi-Instance Environments

This section covers aspects of database creation specific to Oracle Parallel Server such as:

Setting Initialization Parameters for Database Creation

As described in Chapter 1, certain initialization parameters that are critical for database creation or that affect certain database operations must have the same value for every instance. Be sure the settings for these parameters are identical across all instances before creating an Oracle Parallel Server database.


To enable the archiving process (ARCH) while creating a database, set the initialization parameter LOG_ARCHIVE_START to TRUE. Then change the mode to ARCHIVELOG with the ALTER DATABASE statement before starting the instance that creates the database.

Alternatively, you can reduce overhead by creating the database in NOARCHIVELOG mode. This is the default. Then change to ARCHIVELOG mode.

You cannot use the STARTUP command to change the database archiving mode. Instead, after you create the database, use the following commands to change to archiving mode and reopen the database with Parallel Server enabled:



This section describes the following CREATE DATABASE options specific to Oracle Parallel Server.


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.

For Oracle Parallel Server, set MAXINSTANCES to a value greater than the maximum number of instances you expect to run concurrently. 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.


The MAXLOGFILES option of CREATE DATABASE specifies the maximum number of redo log groups that can be created for the database. The MAXLOGMEMBERS option specifies the maximum number of members or copies per group. For Parallel Server, set MAXLOGFILES to the maximum number of threads possible, multiplied by the maximum anticipated number of groups per thread.


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 Oracle Parallel Server.

For Oracle 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, Oracle overwrites the oldest entries. The default for MAXLOGHISTORY is zero, which disables log history.


The MAXDATAFILES option is generic, but Oracle 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:


Database Objects to Support Multiple Instances

To prepare a new database for Oracle Parallel Server, create and configure the additional database objects as described under the following headings:

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 separate tablespace. Do not store these rollback segments in the SYSTEM tablespace.

You must create and bring online one additional rollback segment in the SYSTEM tablespace before creating rollback segments in other tablespaces. 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:

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

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

  3. 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 the instance initialization 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 an error message and prevents the instance from starting up.

Using Public Rollback Segments

Any instance can create public rollback segments that are available for any instance to use. Once a rollback segment is in use by an instance, it is only used by that instance until the instance shuts down. When it shuts down, the instance that used the rollback segment releases it for use by other instances.

To create public rollback segments, use the SQL statement CREATE PUBLIC ROLLBACK SEGMENT. Public rollback segments are owned as PUBLIC in the data dictionary view DBA_ROLLBACK_SEGS. If you do not set a value for the ROLLBACK_SEGMENTS parameter for an instance, the instance uses public rollback segments. The procedures you use to create and manage rollback segments are the same regardless of whether Parallel Server is enabled or disabled.

Typically, the parameter file for a 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.

A public rollback segment comes online when an instance acquires it at startup. However, starting an instance that uses public rollback segments does not ensure that the instance uses a particular public rollback segment. The exception to this is when the instance acquires all available public rollback segments.

Private rollback segments stay offline until brought online or until the owning instance restarts. A public rollback segment stays offline until brought online for a specific instance or until an instance requiring 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 recreate it, you must prevent other instances that require public rollback segments from starting up.

Monitoring Rollback Segments

To monitor rollback segments, query the dynamic performance views V$ROLLNAME and V$ROLLSTAT for information about the current instance's rollback segments. You can also query the data dictionary views DBA_ROLLBACK_SEGS and DBA_SEGMENTS or the global dynamic views GV$ROLLNAME and GV$ROLLSTAT for rollback segment information.

To monitor rollback segments on another instance, use the command CONNECT @instance-path to change the current instance before using the MONITOR command or querying the V$ views.

To list the rollback segments currently in use by an instance, query DBA_ROLLBACK_SEGS with the following syntax:

   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, or "online", as shown in the following sample output:

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

  • Oracle8i Administrator's Guide for more information about rollback segments, and about connecting to a database.

  • Oracle8i Reference for a description of DBA_ROLLBACK_SEGS and DBA_SEGMENTS, and for information about other dynamic performance views.


Configuring the Online Redo Log for Oracle Parallel Server

Each database instance has its own "thread" of online redo, consisting of its own online redo log groups. When running Oracle 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 Oracle Parallel Server.

You must create each thread with at least two redo log files (multiplexing), 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. 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 you set THREAD to zero, which is the default, the instance acquires an available public thread.

Each thread must be created with at least two redo log files or multiplexed groups. You must also enable each thread 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.

   GROUP 4 (disk1_file4, disk2_file4, disk3_file4) SIZE 1M REUSE 
   GROUP 5 (disk1_file5, disk2_file5, disk3_file5) SIZE 1M REUSE; 

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.

See Also: :

Oracle8i Parallel Server Concepts for more information about threads of redo. 

Disabling Threads

Disable a public or private thread with the ALTER DATABASE DISABLE THREAD statement. 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 the thread 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. You cannot drop or archive these log files. In this case, disable the thread, even though it is already disabled, then re-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 modes, the database must be mounted with Oracle Parallel Server disabled, but the database 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, either 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, such as adding, dropping, or renaming a log file or log file member, while the database is mounted with Oracle Parallel Server either enabled or disabled. The only restrictions are that you cannot drop or rename a log file or log file member currently in use by any thread. Moreover, you cannot drop a log file if that would reduce the number of log groups to less than 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.

See Also:

"Archiving the Redo Log Files".  

Providing Locks for Added Data Files

If you add data files while Oracle Parallel Server is running, evaluate whether enough locks are available to cover the new files. Data files that you add in this way use any unassigned locks that were created when Oracle Parallel Server initially created locks to accommodate the value for GC_FILES_TO_LOCKS.

If the remaining number of locks is inadequate to protect the new files and avoid contention, provide more locks by increasing the value for the GC_FILES_TO_LOCKS parameter. Performance problems are likely if you neglect to make these adjustments. This is especially true if your database experiences a high number of inserts. Note, however, that in read-only databases extra locks are unnecessary even if you added many new data files.

If you determine that you need more locks, do the following:

  1. Shut down your database.

  2. Modify the GC_FILES_TO_LOCKS initialization parameter to provide enough locks for the additional data files.

  3. Restart the system.

    See Also:

    "Tips for Setting GC_FILES_TO_LOCKS"

Changing The Values for CREATE DATABASE Options

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

Go to previous page Go to next page
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.