Page 693
between themselves. Also, a failed instance cannot transmit its cached numbers to another instance. Consequently, if you define a sequence generator with both the CACHE and the ORDER options, Oracle ignores the CACHE option when you start an instance in parallel mode. This is likely to reduce the efficiency of the sequence generator.
Whether you are building your parallel database to support failover or scalability, you need to pay attention to various options regarding the basic structure. By the time you are ready to build your database, you should have already decided how many instances you need and how you are going to partition your data, if needed. You will also have to consider the other options for the database structure that are part of any Oracle database design, such as the number of redo log groups you need, whether you will use the database in ARCHIVELOG or NOARCHIVELOG mode, and what character set you need to support all required character sets.
When you have determined these design issues, you can build your database using the CREATE DATABASE command. You should plan on including a value for most of the options provided by this command in order to create a structure that supports all your instances. You may even wish to set some of the values higher than indicated by your current needs in case the database or its use grows to the point that you add more instances. The reason for being so careful with this command is that, once the database is built, the control file contains only enough "slots" to hold the number of resources requested via the CREATE DATABASE command. The only way to change these values later is to rebuild your control file with higher limits. This is not a pleasant activity, requiring a prudent DBA to shut down all the database instances in order to perform a full backup of the old structure, run a CREATE CONTROLFILE command, and back up the new database structure.
In Table 27.7, the options for the CREATE DATABASE command are listed along with the issues you should consider when building your database.
Table 27.7 CREATE DATABASE Command OptionsOption | Description and Usage |
CONTROLFILE REUSE | Only needed if you plan to overwrite an existing control file. You are advised not to use this option because it can destroy a good control file accidentally if you should start the instance with the wrong init.ora parameter file. |
LOGFILE | As with a single instance database, this parameter lets you create as many redo log file groups as you wish, each containing your preferred number of multiplexed copies. The groups you create with this option all belong to the first thread of redo and will be automatically enabled as a public thread. You can disable this thread and reenable it as a private thread, if you wish, after you have started a different instance and can shut this one down. |
continues
Page 694
Table 27.7 ContinuedOption | Description and Usage |
MAXLOGFILES | Sets the maximum number of redo log groups you are able to create for the database. This number should therefore be the sum of the redo log groups from all of the instances you intend to create. You may want to set the value large enough to support additional log groups in case you need to add redo to one or more instances and to support additional instances, should they become available. |
MAXLOGMEMBERS | Sets the maximum number of multiplexed redo log files that can be assigned to any one redo log group. Generally, the number of log members are the same for the redo on all instances, but if not, you should set this to the highest number you will need for a log group. |
MAXLOGHISTORY | Determines the number of archive log file entries that are stored in the control file. When this number is reached, the oldest entries are replaced as the new entries are added. These entries contain information about the archive log names and contents and are used by the automatic media recovery option to identify which archive log files it needs to apply. If you intend to use automatic media recovery, you should set this parameter value to at least the number of archive log files that are created by all instances during a single backup cycle. |
MAXDATAFILES | Sets the maximum number of datafiles that you are able to create for the database. This will typically be a higher number than for single instance databases due to the need for partitioning tables and indexes to avoid inter-instance conflicts for blocks. As with MAXLOGFILES, you should consider making this value sufficiently large to accommodate future growth or further partitioning requirements. |
MAXINSTANCES | Specifies the maximum number of instances that can simultaneously access the database. If you are using one or more instances for failover, you need to include these in this total, even though you will not have users connecting to them under normal circumstances. This is another parameter that you might want to set with consideration for future growth. |
[NO]ARCHIVELOG | Enables you to determine whether the database is placed in ARCHIVELOG mode or not at the time of creation. Although ARCHIVELOG mode is recommended for most production databases, it is generally not necessary to place it in this mode immediately. The default value is NOARCHIVELOG. |
Page 695
EXCLUSIVE | This is an optional keyword and becomes obsolete in Oracle8. It simply reinforces the fact that the initial instance cannot be opened in shared, or parallel, mode. You are advised to leave it out. |
CHARACTER SET | Specifies the character set to be used by the database to store data. This parameter has no particular characteristics specific to Parallel Server. |
DATAFILE | Names the datafile or datafiles that initially comprise the SYSTEM tablespace. This parameter has no particular characteristics specific to Parallel Server. Similarly, the AUTOEXTEND options for the named datafiles work identically in single and multi-instance database. |
You might want to build a SQL script file to contain this DATAFILE command, along with the other commands you need to customize the database for Parallel Server use. These would include, among others, commands:
You should be familiar with special dynamic performance tables in order to monitor the performance of a parallel database. These tuning tools and tuning steps have to be used in addition to the standard tuning approaches you need for any Oracle database.
The dynamic performance tables provided by Oracle for Parallel Server are created using a script called catparr.sql. Run this script as the user SYS to create the tables and to update them if you add new segments or extents you wish to track. You also need to run it after Oracle has added extents dynamically to tables or indexes that you want to monitor.
TIP |
You should get into the habit of routinely running this script before beginning any monitoring or tuning work. This way, you can be assured you will be seeing the most current information when you examine the various views you need. |
In Oracle8, each of the following tables, as well as a number of the other dynamic performance tables, has a global version that shows the values for each of the active instances. An instance number column identifies from which instance the data is being reported. To use one of these global tables, you need to include a "g" in front of the name. Listing 27.6 shows the use of a global view of V$VERSION.