23
Migrating from a Single Instance to Parallel Server

This chapter describes database conversion: how to convert from a single instance Oracle database to a multi-instance Oracle database using the Oracle Parallel Server (OPS) option.

The chapter is organized as follows:

Overview

This chapter explains how to enable your database structure to support multiple instances. It also explains how to begin a project with a single instance Oracle database even though you intend to migrate to the multi-instance parallel server. In addition, this chapter can help you extend an existing OPS configuration to additional nodes.

Note:
Before using this chapter to convert to a multi-instance database, use the Oracle8i Migration manual to perform any necessary upgrade of the Oracle Server. That manual also provides information on upgrading and downgrading in replicated systems.

Deciding to Convert

This section describes:

Reasons to Convert

You may decide to convert to a multi-instance database for the following reason:

To move from a single node to a cluster (when you have designed your application with OPS in mind).

In addition, your application may have been designed for OPS but you need more instances to take advantage of your current database design. Or you may have enough nodes but need to bring offline nodes online. You might even already be using OPS but want to add more nodes.

Reasons Not to Convert

Do not convert to OPS in the following situations:

You are using a file system that is not shared.
Your application was not designed for parallel processing; you need to examine your application more.
You are not using a supported configuration (of shared disks, and so on).

Preparing to Convert

This section describes:

Hardware and Software Requirements

To convert to OPS you must have:

A supported hardware and OS software configuration
A license for the Oracle Parallel Server
Oracle Server running on all nodes
OPS linked to your system

Converting the Application from Single- to Multi-instance

Making your database run in parallel does not automatically mean you have effectively implemented OPS. Besides migrating your existing database from single instance Oracle to multi-instance Oracle, you must also migrate existing applications that were designed for single-instance Oracle. Preparing an application for use with a multi-instance database may require application partitioning and physical schema changes.

Administrative Issues

Note the following administrative issues of conversion:

Your backup procedures should be in place before converting from single-instance Oracle to the Oracle Parallel Server.
Additional archiving considerations apply in an OPS environment. In particular, the archive file format needs the thread number. Furthermore, archived logs from all nodes are needed for media recovery. If you archive to a file, then on systems where file systems cannot be shared, some method of accessing the archive logs is required.

See Also:
Chapter 21, "Backing Up the Database".

Converting the Database from Single- to Multi-instance

The following procedure explains how to migrate an existing database from single-instance Oracle to multi-instance Oracle. Remember that you must also migrate the application from single- to multi-instance.

Modify your application to make it OPS-ready.

Make sure all necessary files are shared among the nodes.

OPS assumes disks are shared among instances such that each instance can access all log files, control files, and database files. These files should normally be on raw devices, since the disks are shared through raw devices on most clusters.

Note:
You cannot use NFS to share files for OPS. NFS does not provide adequate availability: if the node goes down, NFS goes down and the files cannot be reached. Moreover, NFS does not provide adequate consistency: a write may be cached and not written to disk immediately.

Check MAXINSTANCES on the single instance.

The MAXINSTANCES parameter was set at database creation, usually to its default value of 1. With MAXINSTANCES set to 1, only one instance can run on database, and the database cannot run in parallel server mode. The number of rows in V$THREAD is one per created thread. The MAXINSTANCES value may be much higher. You can check V$ACTIVE_INSTANCES to find this value.

To check the value of MAXINSTANCES query V$ACTIVE_INSTANCES. Alternatively, you can dump the control file to a trace file by entering:

    ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

The trace file may look like this:

Dump file /mf1/qjones/qj1/rdbms/log/ora_20016.trc
Oracle8 Server Release 8.0.3 
With the distributed, replication, parallel query and 
   Parallel Server options
PL/SQL Release 3.0 
ORACLE_HOME = /mf1/qjones/qj1
ORACLE_SID = mf1qj1
Oracle process number: 19        Unix process id: 20016
System name:    mf1seq
Node name:      mf1seq
Release:        3.2.0
Version:        V2.1.1
Machine:        i386
Wed Feb 22 14:30:22 1997
Wed Feb 22 14:30:23 1997
*** SESSION ID:(18.1)
# The following commands will create a new control file and
# use it to open the database.
# No data other than log history will be lost. Additional logs 	
# may be required for media recovery of offline data files. 
# Use this only if the current version of all online logs are
# available.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "TPCC" NORESETLOGS
NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 2
    MAXDATAFILES 62
    MAXINSTANCES 1
    MAXLOGHISTORY 100
LOGFILE
  GROUP 1 '/dev/rvol/v-qj80W-log11'  SIZE 200M,
  GROUP 2 '/dev/rvol/v-qj80W-log12'  SIZE 200M
DATAFILE
  '/dev/rvol/v-qj80W-sys',
  '/dev/rvol/v-qj80W-temp',
  '/dev/rvol/v-qj80W-cust1',
.
.
.
;
# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or 
# immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;

Edit the control file script to include a larger MAXINSTANCES value.

Edit the trace file so it only contains SQL commands to generate the CREATE CONTROLFILE statement. Then make the following changes:

Set PFILE to point to the correct initialization file.
Increase the MAXINSTANCES parameter to the number of Oracle instances you want to support.
Use a large value for the MAXLOGHISTORY parameter.

The resulting control file is a script that recovers and reopens your database if necessary.

Before running the SQL file, make sure the current control file(s) are in the backup directory.

A sample script follows:

STARTUP NOMOUNT PFILE=$HOME/perf/tkvc/admin/tkvcrun.ora
CREATE CONTROLFILE REUSE DATABASE "TPCC" NORESETLOGS
NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 2
    MAXDATAFILES 62
    MAXINSTANCES 1
    MAXLOGHISTORY 100
LOGFILE
  GROUP 1 '/dev/rvol/v-qj80W-log11'  SIZE 200M,
  GROUP 2 '/dev/rvol/v-qj80W-log12'  SIZE 200M
DATAFILE
  '/dev/rvol/v-qj80W-sys',
  '/dev/rvol/v-qj80W-temp',
  '/dev/rvol/v-qj80W-cust1',
.
.
.
;
# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or 
# immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;

Back up the new control file immediately after conversion. We also recommend beginning your backup procedures for the database.
Decide how to administer the initialization parameter file(s).

Each instance has private initialization parameters. However, some of the parameters need to have the same value on each instance. There are two ways of administering this.

One approach is for each instance to have a private parameter file that includes the common parameter file shared between the instances. The common parameter file must be on a shared device accessible by all nodes. This way, when you need to make a generic change to one of the common initialization parameters, you need only make the change on one node rather than on all nodes.

Alternatively, you can make multiple copies of the parameter file and place one on the private disk of each node of your OPS environment. In this case, you must update all parameter files each time you make a generic change.
Edit the following parameters in the instance-specific initialization parameter file:
1. Specify an INSTANCE_NUMBER for this instance. Each instance will be numbered at startup time. The instance number is used in the free list group assignment. If you do not specify the INSTANCE_NUMBER, Oracle assigns a number based on start up order.
2. Specify ROLLBACK_SEGMENTS. Each instance should have a set of private rollback segments.
3. Specify the THREAD parameter in the initialization parameter file so the instance always starts with the same set of redo log files. A thread number is assigned at startup time to associate an instance with the log files of that thread. By default this value is 0; you can set it to 1 for the first instance.
4. Add the DB_NAME parameter to the initialization parameter file.
Make sure the following common initialization parameters have identical values for all instances:

CONTROL_FILES
DB_BLOCK_SIZE
DB_FILES
DB_NAME
GC_FILES_TO_LOCKS
GC_ROLLBACK_LOCKS
LM_LOCKS (identical values recommended)
LM_PROCS (identical values recommended)
LM_RESS (identical values recommended)
MAX_COMMIT_PROPAGATION_DELAY
ROW_LOCKING
SINGLE_PROCESS

Make sure the Oracle executable is linked with the OPS option and that each node is running the same versions of the executable. The banner you see upon connection should display the words "Parallel Server".

Note:
Corruption may occur if one node opens the database in shared mode and another node opens it in exclusive mode.

Perform a normal shutdown of the database.

Back up the control files using operating system commands.

Remove the control files but retain backups of them.

Run the new script you built that recreates the old control files with new data--larger structures for some of the database objects.

Add rollback segments.

Add additional threads.

Shut down the database.

Start up the database in shared mode. The first instance will be started.

Add the second instance in shared mode, using the standard procedure described in "Starting in Shared Mode". (Note that the second instance only succeeds if the first instance is in shared mode.) Add redo log files, rollback segments, and so on.

Tune the GC_* and LM_* parameters for optimal performance.

Troubleshooting the Conversion

This section explains how to resolve common errors:

Database Recovery After Conversion

If you should lose your database and Oracle8 files after converting from single-instance Oracle to OPS, restore your cold backup and then apply all changes from the redo logs. In this case, your old control file would be used as though you had never done the conversion. You would have to recreate the new control file if you migrate to OPS.

Loss of Rollback Segment Tablespace

The following problem may occur if a user has created tablespaces for private rollback segments and allocated them to specific instances at startup. It may also occur if files containing rollback segments are lost.

If you lose one rollback segment tablespace or file containing rollback segments due to media failure, all instances will fail. To recover, shut down all instances. All other rollback segments must remain offline so you can bring the one you want to recover off line.

Inadvisable NFS Mounting of Parameter File

As mentioned earlier, it is not advisable to access a common parameter file (or any Oracle file or executable) over NFS. If the NFS disk were to go down, no other instance could start. Access to control files and data files is not supported over NFS.

23Migrating from a Single Instance to Parallel Server