|Oracle8i Parallel Server Concepts and Administration
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:
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.
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.
This section describes:
You may decide to convert to a multi-instance database for the following reason:
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.
Do not convert to OPS in the following situations:
This section describes:
To convert to OPS you must have:
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.
Chapter 12, "Application Analysis" for a full discussion of this topic.
Note the following administrative issues of conversion:
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.
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.
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 trace file so it only contains SQL commands to generate the CREATE CONTROLFILE statement. Then make the following changes:
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;
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.
This section explains how to resolve common errors:
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.
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.
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.