23
Migrating from Single Instance to Parallel Server

This chapter describes database conversion: how to convert from a single instance Oracle8 database to a multi-instance Oracle8 database using the parallel server option.

The chapter is organized as follows:

Overview

The present chapter explains how to enable your database structure to support multiple instances. It can also prepare you to start a project with a single instance Oracle8 database, while being ready to migrate to multi-instance in the future. In addition, it can help you extend an existing Oracle Parallel Server configuration to additional nodes.

Attention: Before using this chapter to convert to a multi-instance database, use the Oracle8 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 wish to convert to a multi-instance database for the following reasons:

You want to move from a single node to a cluster (when you have designed your application with Oracle Parallel Server in mind).
You are already running Oracle Parallel Server, but want to extend your database to include more nodes.
Your application was already designed for Oracle Parallel Server, but you ended up running without enough instances (without enough nodes specified for the database).
You have enough nodes specified, but need to bring the other nodes online.

Reasons Not to Convert

Do not attempt to convert to a multi-instance database in the following situations:

You are using a file system which 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 a multi-instance database you must have:

a supported hardware and OS software configuration
license for Oracle Parallel Server
Oracle Server running on all nodes
Oracle Parallel Server linked in

Converting the Application from Single- to Multi-instance

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

See Also: Chapter 12, "Application Analysis", for a full discussion.

Administrative Issues

Note the following ramifications of conversion:

Your regular backup procedures should be in place before you proceed to convert from single-instance Oracle8 to the Oracle8 Parallel Server.
Additional archiving considerations apply in an Oracle Parallel Server environment. In particular, the archive file format must have 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.

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-instance to multi-instance.

Modify your application to make it Oracle Parallel Server ready.
Make sure that all necessary files are shared between the nodes.
Oracle8 Parallel Server assumes that disks are shared between the different 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.

Attention: NFS cannot be used to share files for Oracle8 Parallel Server. NFS does not provide adequate availability: if the node goes down, NFS goes down and the files cannot be reached. Likewise, 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 the database, and the database cannot run in parallel server mode. Note that 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 you can check V$ACTIVE_INSTANCES. Alternatively, you can dump the control file to a trace file by entering

	SQL> 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 that it only contains the SQL commands necessary 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 will recover and reopen your database if necessary.

Before you run the SQL file, make sure that the current control file(s) are moved to 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. Oracle Corporation also recommends that you commence your backup procedures for the database.
Decide how to administer the initialization parameter file(s).
Each instance will have private initialization parameters, but some of the parameters need to have the same value on each instance. There are two alternative ways of administering this.

One approach is for each instance to have a private parameter file that includes the common parameter file that is shared between the instances. The common parameter file must be on a shared device accessible to 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 that participates in the Oracle Parallel Server. In this case you would need to update all of the 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 will assign a number based on the order of start up.
2. Specify ROLLBACK_SEGMENTS. Each instance should have a set of private rollback segments to work on.
3. Specify the THREAD parameter in the initialization parameter file so that the instance always starts with the same set of redo log files. A thread number will be 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 that the following common initialization parameters have the same 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)
LOG_FILES
MAX_COMMIT_PROPAGATION_DELAY
ROW_LOCKING
SINGLE_PROCESS
Make sure that the Oracle executable is linked with the Parallel Server Option, and that each node is running the same versions of the executable. The banner displayed 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 shutdown normal of the database.
Back up the control files using operating system commands.
Remove the control files (keep the backups).
Run the new script you have built, which will recreate 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 Up in Shared Mode" on page 18-14. (Note that the second instance will only succeed 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 Oracle Parallel Server, you would have to 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 Oracle Parallel Server.

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 that contain rollback segments are lost.

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

Inadvisable NFS Mounting of Parameter File

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. Note also that access to control files and data files is not supported over NFS.

23Migrating from Single Instance to Parallel Server