In this chapter, we will describe how to install the RDBMS software using the Oracle Universal Installer and the database management tools using the Database Configuration Assistant (DBCA). An important thing to bear in mind when reading this chapter is that the RDBMS software installed for a RAC database home is identical to the installation for a single-instance database home, with one exception: it must have the software for the RAC option linked in. At any stage, the RAC option can be unlinked to use the same home for single-instance Oracle databases. You can also run a single instance database from a RAC home by setting the init.ora parameter cluster_database to FALSE.
Since the introduction of ASM in Oracle 10g, the ASM and RDBMS software have shared a common code base. However, prior to Oracle 11g Release 2, the ASM and RDBMS software could be installed from a single OUI in the same Oracle home directory. Optionally, both sets of software could be installed in separate Oracle Homes. Separate Oracle homes improved manageability by enabling the separation of roles between database and storage management. This included the disadvantage of an additional Oracle home to maintain; however, unlike the RDBMS software, there could never more than one ASM instance operational on a node at a particular point in time. In Oracle 11.2, ASM is part of the Grid Infrastructure software. This means that it is installed in conjunction with the clusterware, as opposed to the RDBMS software. Such a separation enables you to group the ASM and clusterware that provides central management of your software. Note that only one installation can run on a node. You can learn how to install the Grid Infrastructure software in Chapter 7. The RDBMS software is now called the RAC software, and it requires a separate Oracle home and installation procedure.
As in previous versions, the RAC software is installed using the Oracle Universal Installer (OUI). Initial installations require an X-Windows environment. If you wish, you can create a response file to use in subsequent silent installations.
You begin by starting in the installer. Do so as the oracle user in an X session, as in this example:
[oracle@london1]$ cd /home/oracle/stage/database [oracle@london1]$ ../runInstaller
The installer takes a few moments to load; once it does, the Configure Security Updates page will be displayed.
The Configure Security Updates page (see Figure 10-1) allows you to specify an email address to which security alerts and other product issues will be sent. This feature has also been included in Oracle 10gR2 and Oracle 11gR1 patchsets. Oracle recommends that you specify your My Oracle Support email address for this purpose.
Specifying an email address is not mandatory. A warning message will be displayed f you do not specify an address; however, the installation will continue without any problem.
The Installation Options page (see Figure 10-2) presents you with with three options:
The first option will install the RAC software and then create a database. We recommend splitting that process into two separate steps. Doing so will make it easier to find and resolve any faults if an error occurs. Therefore, we recommend selecting the "Install database software only" option at this stage. You can create a database later, using the Database Configuration Assistant (DBCA).
The Node Selection page (see Figure 10-3) lets you select between a single-instance database installation and a RAC database installation. This selection determines which libraries are linked into the executables during installation.
If the Grid Infrastructure is detected, then the default will be set to a RAC database installation, and all known nodes will be added to the node list. This list is useful for verifying that Grid Infrastructure is installed on all nodes and that they are currently running. If any of the expected nodes are missing from this list, we recommend that you exit the installer, remedy the issue, and then restart the RAC software installation.
Press Next to continue to the Product Language selection page. This is identical to the page used in the Grid Infrastructure installation. Select any additional languages you wish error messages to appear in, and then press Next to continue to the Database Editions page.
The Database Edition page (see Figure 10-4) allows you to choose between the Standard Edition and Enterprise Edition of the Oracle database. In Standard Edition, the RAC option is included as part of the licensing. This version's licensing conditions mean that the cluster is limited to four processor sockets; note that these sockets may have multiple cores. The number of sockets is a restriction across the entire cluster. For example, you may have a cluster of four single-socket servers or two dual-socket servers. The RAC option is included in Standard Edition; however, other functionality is not included. For example, Standard Edition doesn't include the parallel execution (see Chapter 14 for more information on this feature).
Enterprise Edition provides more functionality, including physical and logical Data Guard, Flashback Database, Resource Manager, Oracle Streams, bitmap indexes, and parallel execution. If you are using Enterprise Edition, you can also purchase additional functionality, including the Partitioning option and Enterprise Manager options such as the Diagnostics Pack and the Tuning Pack.
It is possible to upgrade from Standard Edition to Enterprise Edition after installation. However, the upgrade process requires a software reinstallation, and it cannot be performed while the database is online. Downgrading from Enterprise Edition to Standard Edition requires all data to be exported and reimported into the recreated database. Consequently, the edition most applicable to your environment should be considered carefully before you select one.
The next screen you'll see is the Installation Location page (see Figure 10-5). The Oracle Base location should be identical to that used for the Grid Infrastructure installation. This holds true as long as the software is installed under the same user or if different users share the same operating system groups. Unlike the Grid Infrastructure installation, where ownership is changed to the root user as part of the installation process, the software location for the RAC software installation can be below the Oracle base and maintain ownership under the RDBMS software user.
In Oracle 10gR2, the software location defaulted to $ORACLE_BASE/product/10.2.0/db_1. Additional homes were calle db_2, db_3, and so on. In Oracle 11gR2, the RDBMS homes are called dbhome1, dbhome2, and so on. If you planto create more than one database in the cluster, then we recommend retaining the recommended dbhome_1 directory name to allow for different versions of the RDBMS Oracle home to be installed. This approach will provide more flexibility during upgrades (e.g., dbhome_2, dbhome_3, and so on).
The Privileged Operating System Groups page (see Figure 10-6) lets you optionally specify groups for the Database Administrator (OSDBA) group and the Database Operator (OSOPER) group. The default for both groups is dba. In practice, most sites use the OSDBA group; it is very rare to see the OSOPER group used, although it can provide a useful separation of duties if you are concerned about security, or you wish to split responsibilities between different teams or companies.
Press Next to continue to the Prerequisites Checks page. This page is similar to the Prerequisites Checks page in the Advanced Installation. When all prerequisite checks failures have been resolved or ignored, press Next to continue to the Summary page.
The Summary page (see Figure 10-7) displays the details of the options selected during the interview process. You can optionally save these options to a response file that can be used as input for subsequent silent installations.
Press Finish to start the installation process.
At the end of the installation process, the Execute Configuration Scripts page will be displayed (see Figure 10-8).
We recommend that the scripts should be executed separately, in the order that the servers are listed. You should do this even though scripts other than the first may be executed in parallel or not in the listed order. We recommend running them one-at-a-time because it can ease troubleshooting. It can also help you ensure that the last script is run separately because it activates the new code version.
Press OK when the root scripts have been executed on all nodes.
The Finish page should be displayed, indicating a successful installation. This completes the RAC Software installation procedure.
In Oracle 10g and later, the Database Configuration Assistant (DBCA) has been the preferred tool for creating and dropping databases. DBCA allows supports limited modification of the database.
In Oracle 10g and 11.1, DBCA also supported maintaining ASM instances and diskgroups; in Oracle 11.2, this functionality has been moved to the new ASM Configuration Assistant (ASMCA).
In Oracle 10g, DBCA also provided limited support database services, including the ability to create, delete, and specify preferred and available instances. In Oracle 11g, this functionality was moved to the Enterprise Manager Database and Grid controls.
Before running DBCA, you need to ensure that any ASM diskgroups required by the database have been created. In Oracle 11.2, disk groups can be created using ASMCA or SQL*Plus commands. For most ASM configurations, we would recommend creating two ASM disk groups: one for the database files and another for recovery files. For production systems, these disk groups should be on physically separate disks. For non-production systems, a single ASM disk group may be sufficient. If you are using a cluster file system, then no ASM configuration is required.
DBCA is installed in the RAC Software home. It can run in GUI or silent mode. The GUI mode requires an X session. We recommend that you initially use the GUI interface to specify new databases. It is possible to save the results of the interview process as a database template that can be used as a basis for subsequent database creation. Alternatively, you can create a set of database creation scripts that, with minor modifications, can subsequently be used to build new copies of the database.
Prior to running DBCA, you need to ensure that the $ORACLE_HOME and PATH environment variables are set correctly to specify the RAC software home, as in this example:
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 export PATH=$ORACLE_HOME/bin:$PATH
Next, start DBCA as the oracle user:
[oracle@london1]$ $ORACLE_HOME/bin/dbca
The Welcome page will be displayed.
The Welcome page (see Figure 10-9) allows you to choose whether you wish to create or administer a RAC database or a single-instance database. If the Grid Infrastructure and RDBMS Software has been successfully installed, then the default will be RAC.
The operations page (see Figure 10-10) is where you tell DBCA what operation you wish to perform. We'll be creating a database, but it provides other choices, as well.
In Oracle 11.2, DBCA supports five options:
Create a database
Configure database options
Delete a database
Manage templates
Instance management
Initially, you can only create a database or manage templates. The remaining options will become available once a database has been successfully created.
When you select the Create a Database option from the DBCA Welcome page, the Database Templates page will be displayed. This launches the database creation process.
The next screen you see is the Database Templates page (see Figure 10-11). In Oracle 10g, DBCA supported four default database templates:
General Purpose
Transaction Processing
Data Warehouse
Custom Database
The General Purpose and Transaction Processing templates were very similar, so these templates were combined in Oracle 11.1. Therefore, Oracle 11.2 lists only three default templates.
The templates are defined by XML files in $ORACLE_HOME/assistants/dbca/templates, as shown in Table 10-1. (There is no template file for the Custom Database template.)
The General Purpose and Data Warehouse templates create an initial database that is based on the contents of the files, Seed_Database.ctl and Seed_Database.dfb. These files are also located in $ORACLE_HOME/assistants/dbca/templates. The RMAN utility is used to recover the contents of the seed database backup files into the newly created database.
At the time of writing, the only difference between the General Purpose and the Data Warehousing templates in Oracle 11.2 is that the STAR_TRANSFORMATION_ENABLED parameter is FALSE in the former and TRUE in the latter.
The Custom Database template creates a database and then executes the catalog scripts in $ORACLE_HOME/rdbms/admin, including catproc.sql and catalog.sql. Although the resulting database is smaller, the Custom Database template takes significantly longer to create due to the time required to execute the scripts. Databases created using the General Purpose template may present a higher security risk than those created using the Custom Database template. Therefore, we recommend that you use the faster General Purpose template to create test databases and the Custom Database template to create production databases.
The Database Identification page (see Figure 10-12) allows you to select between an Admin-Managed and a Policy-Managed database. The default is Admin-Managed. If you select Policy-Managed, then the page is redisplayed with a different set of fields.
The Admin-Managed page allows you to enter a Global Database Name. By default, the name you enter will also be used as the prefix for instance names. Consequently, we recommend that you choose a Global Database Name that does not end in a digit. In other words, don't use PROD1 as a Global Database Name because the instance names will be PROD11, PROD12, which could be confusing down the road.
The Admin-Managed page also allows you to specify which nodes you wish to create the cluster database in. We would always recommend that instances be created on all nodes in the cluster; this maximizes operational flexibility in the future, especially during upgrades, migrations, and following node failures. Remember that instances can always be stopped and disabled to reduce resource usage.
If you select a Policy-Managed configuration, then the Database Identication page will be redisplayed (see Figure 10-13). Notice that the fields are different this time you see it.
The Policy-Managed Database Identification page also allows you to specify a Global Database Name. However, the naming issues discussed in the preceding section do not apply to Policy-Managed databases. In a Policy-Managed database, the instance name includes an underscore between the prefix and the instance number. Therefore, if the database name is PROD, the instance names will be PROD_1, PROD_2, and so on. It may still be necessary to manage these instances explicitly occasionally, but there is less possibility of confusion.
The Policy-Managed Database Identification page also allows you to specify a server pool in which to create the database. The concepts behind server pools are discussed in Chapter 2. You can either create a new server pool or specify the name of an existing pool. For new server pools, you can also specify the cardinality; this will default to the number of nodes in the cluster. For example, specifying a cardinality of 4 will result in a new pool with the attribute values shown in Table 10-2.
If you select an existing server pool, the cardinality will be displayed, and that cardinality cannot be altered within DBCA.
If any of the selected nodes are unavailable, a warning message will be displayed. Nodes are reported as being unavailable if they are down or if Oracle Grid Infrastructure is not running on them. We recommend that you resolve such problems before continuing with the DBCA session.
The Management Options page (see Figure 10-14) lets you optionally configure Enterprise Manager. If an Enterprise Manager Grid Control Agent is detected, then you will be prompted to register the database with the Enterprise Manager Grid control. Otherwise, DBCA will offer to install the Enterprise Manager Database control; in this case, a Management Repository will be installed in the newly created database.
On this page, you can specify an SMTP mail server and e-mail address for alert notification. You can also enable a daily backup of the database to the recovery area.
If you do not wish to configure the Management Options at this time, this page is also included in the Configure Database Options operation, which is accessed from the Operations page in DBCA. However, note that this page will no longer be available once the Management Options have been configured. To alter Enterprise Manager settings after configuration, you must use the emca tool instead.
The Automatic Maintenance Task tab allows you to enable or disable automatic tasks such as optimizer statistics collection and advisor reports. By default, these tasks are performed during the maintenance window that runs from 10:00pm to 6:00am on weekdays and all day on Saturdays and Sundays. If these times are not appropriate, then you can modify the maintenance window using Enterprise Manager.
The Database Credentials page (see Figure 10-15) lets you specify passwords for the users required to create the database. If you have enabled Enterprise Manager, then you will be asked for passwords for the SYS, SYSTEM, DBSNMP, and SYSMAN users. For test databases, you can use the same administrative passwords for all accounts. However, for production databases, you should use a different password for each account to ensure robust security.
The Database File Locations page (see Figure 10-16) allows you to specify shared storage for the database. In Oracle 11.2, you can use Automatic Storage Management (ASM) or a Cluster File System. Note that raw devices and block devices are no longer supported for shared storage.
You can choose from the following approaches when deciding a storage location:
You can use a set of locations read from a DBCA template.
You can specify a common location for all database files.
You can let the database manage locations through the Oracle-Managed Files feature.
If you select Automatic Storage Management (ASM), then the default will be to use Oracle-Managed Files; if you select Cluster File System, then the default will be to use a common location for all database files. In either case, you can choose any of the three options.
If you choose to use Oracle-Managed Files, then you can optionally multiplex your redo logs and control files. You can choose up to five destinations; these should be spread across different physical disks to provide greater fault tolerance.
You can also display the file location variables. You cannot change the file location variables, but you can override them in the common location for all datafiles and the Oracle-Managed files options.
The Recovery Configuration page (see Figure 10-17) lets you specify a Flash Recovery Area. You can also choose to enable archiving.
The Flash Recovery Area was introduced in Oracle 10.1. If configured, it is used as the default location for all disk-based backup and recovery operations. File types stored in the Flash Recovery Area include archived redo logs, datafile copies, and flashback logs.
Space usage in the Flash Recovery Area is managed by Oracle. An initial allocation of space is specified. When this space is exhausted, Oracle will start to release space by deleting files based on a set of rules designed to ensure that the database can still be recovered. If no files can be deleted, then ultimately the database will hang. If that occurs, possible solutions are to increase the size of the Flash Recovery Area, to perform a backup, or to manually delete unnecessary files.
In our experience, the majority of users in Oracle 10.2 and later are now configuring the Flash Recovery Area to allow Oracle to manage database recovery files.
In addition to requesting a Flash Recovery Area, you may also choose to enable archiving on the Recovery Configuration page. By default, archiving is disabled during database creation. If you are building a database using the Custom Template database, it will be created be much more quickly with archiving disabled. If you are using the General Purpose or Data Warehouse Templates, then archiving has less impact because the initial database is created from an RMAN backup rather than SQL*Plus scripts.
If you enable archiving, then you can optionally specify multiple archive log destinations on this page. However, it is usually more efficient to specify multiple log destinations after the database has been created.
What you see on the Database Content page depends upon which template you selected. If you selected the Custom template or a template derived from the Custom template, then you will see the page shown in Figure 10-18.
You can optionally install the following components:
Oracle Text
Oracle OLAP
Oracle Spatial
Oracle Label Security
Sample Schemas
Enterprise Manager Repository
Oracle Warehouse Builder
Oracle Database Vault
You can also click the Standard Database Components button to display the Standard Database Components page.
The Standard Database Components page (Figure 10-19) lets you to install the following components:
Oracle JVM
Oracle XML DB
Oracle Multimedia
Oracle Application Express
If you selected the General Purpose or Data Warehouse template, you can choose to install the sample schemas. This template will be stored in an additional tablespace called EXAMPLE that is approximately 130MB in size.
In Oracle 11.2, the sample schemas are supplied as a transportable tablespace. The files are located at the following llcoation: $ORACLE_HOME/assistants/dbca/templates. The metadata file is example.dmp, and the datafile is example01.dfb.
The sample schemas include those listed in Table 10-3.
Table 10.3. Standard Example Schemas
Schema Name | Description |
|---|---|
HR | Human Resources |
OE | Order Entry |
JMD Check | Online Catalog |
PM | Product Media |
IX | Information Exchange |
SH | Sales History |
On the Custom Scripts tab of the Database Content page, you can specify any Custom Scripts that you may wish to execute during database creation. However, we recommend that you defer running custom scripts until the database has been successfully created. This simplifies finding faults, should any errors occur.
The Initialization Parameters page includes four tabs:
Memory
Sizing
Character Sets
Connection Mode
On each tab, you press the All Initialization Parameters button to modify the 30 or so basic parameters, such as DB_BLOCK_SIZE. You can modify any supported parameter by pressing Show Advanced Parameters button on the All Initialization Parameters page. Oracle 11.2 has around 340 supported parameters; the actual number will vary by version and port.
The Memory tab (see Figure 10-20) defaults to using Automatic Memory Management, which was introduced in Oracle 11.1. This feature specifies Oracle will manage both SGA and PGA memory allocation. The concepts behind selecting Automatic Memory Management or Automatic Shared Memory Management are discussed in Chapter 4.
The Sizing tab (see Figure 10-21) allows you to specify the number of processes that each instance will support; the default is 150. The values of the TRANSACTIONS and SESSIONS parameters are derived from the PROCESSES parameter, if they have not been set manually.
The Sizing tab also displays the database block size, but it does not allow you to change it. However, it can be modified if you press the All Intiialization Parameters button at the bottom of the page.
The Character Sets tab on the Initialization Parameters Page lets you specify a Database Character Set and a National Character Set. Figure 10-22 shows the Character Sets tab with the default choice selected.
The default Database Character Set will be derived from the character set selected when the operating system was installed. For example, the default character set in the UK is WE8MSWIN1252, which is a Microsoft Windows code page. Many sites with international applications are now chosing to use Unicode (AL32UTF8) for their Database Character Set because this provides more flexibility with multilanguage data. Alternatively, you can select from a list of about 30 other more localized character sets.
The National Character Set defaults to Unicode (AL16UTF16), which is a 16-bit universal character set. You can optionally use Unicode 3.0 (UTF8). The default language and default territory can also be modified on this page.
The Connection Mode tab (see Figure 10-23) allows you to select between Dedicated Server mode and Shared Server mode. The default is Dedicated Server mode. We strongly recommend that you use this mode unless you are extremely short of memory. Shared Server mode may be required by certain Oracle features, but these will typically enable shared servers, if necessary. While Shared Server mode potentially uses less memory than Dedicated Server mode, experience has shown that it can cause severe serialization issues and contention between processes. Therefore, in our opinion, careful consideration and testing should be done before enabling Shared Server mode.
Depending on the storage type selected, the Database Storage page (see Figure 10-24) allows you to specify locations and sizes for various database files, including the following:
Control Files
Data Files
Redo Log Groups
You can override the locations of any datafiles. You can also change the data file sizes, unless you are using ASM with Oracle-Managed files. Finally, you can change the size of redo log groups and add new groups and members—again, unless you are using ASM with Oracle-Managed Files.
Note that you can override the values for control files shown in Table 10-4.
Table 10.4. Overridable Storage Parameters
Parameter | Value |
|---|---|
Maximum Datafiles | 1024 |
Maximum Instances | 32 |
Maximum Log History | 1 |
Maximum Redo Log Files | 192 |
Maximum Log Members | 3 |
These values determine how large individual sections of the control file will be. They are difficult to modify after the database has been created, so take care that you anticipate whether your database will become very large in the future.
The Creation Options page (see Figure 10-25) allows you to perform the following tasks:
Create a new database (this is the default)
Save as a Database template
Generate database-creation scripts
You can only choose the Save as a Database Template option if you originally selected the Custom template or a user-defined template. This option will not appear if you originally selected the General Purpose or Data Warehouse template.
If you choose to generate database creation scripts, you will be prompted for a location; the default is $ORACLE_BASE/admin/<DatabaseName>/scripts. Take care when accepting the default; if you subsequently use DBCA to delete the database, the scripts directory will also be removed, which may not be your intention. If you wish to preserve the creation scripts so that you can repeatedly build a database, then we recommend you save them to another directory, such as /home/oracle/<DatabaseName>/scripts.
Press Finish to continue after choosing the options described in the preceding section. You'll see a Summary page similar to the one shown in Figure 10-26. You may optionally save the page as an HTML file.
Press OK to return to the Creation Options page and begin the installation.
Warning
Do not press Finish again on the Creation Options page; doing so will start a second installation process. If you do begin a second install process, it will run for a few minutes before causing both installation processes to fail.
A progress page is displayed while DBCA performs the following steps:
Copying database files
Creating and starting Oracle instance
Creating cluster database views
Completing database creation
When the installation has been successfully completed, you will see a message similar to the one shown in Figure 10-26. This message contains information about the new database. Make sure that you capture this information because you will need it to access the database.
This completes the database installation. If an Admin-Managed database has been created, one instance should by running on each specified node; if a policy-managed database has been created then the maximum number of instances that will be running is equivalent to the cardinality of the server pool.
After a database has been created, you can configure certain database options through DBCA. Ensure that the database is started, and then login to an X session as the oracle user and run dbca.
On the Welcome page, select Oracle Real Application Clusters Database; then, on the Operations page, select Configure Database Options. A list of cluster databases will be displayed. Select the database you wish to configure and press Next to continue.
If you have not previously configured Enterprise Manager, then the Management Options page will be displayed. If an Enterprise Manager Grid Control agent is detected on the node, then you can confirm that you wish to register your database with the Grid Control agent. Alternatively, you can install a management repository in the local database, so you can use Enterprise Manager Database control. The Management Options page also allows you to specify an e-mail address for alert notifications, as well as to configure a basic daily backup for the database.
Next, you will see the Database Components page. If you created the database from the General Purpose or Data Warehouse template, all options on the Database Components page will be disabled. If you created the database from the Custom Template, then you will be able to install the following additional components if they have not already been installed:
Oracle Text
Oracle OLAP
Oracle Spatial
Oracle Label Security
Sample Schemas
Enterprise Manager Repository
Oracle Warehouse Builder
Oracle Database Vault
You can also click the Standard Database Components button to display the Standard Database Components page. The Standard Database Components page allows you to install the following components:
Oracle JVM
Oracle XML DB
Oracle Multimedia
Oracle Application Express
The Database Credentials page comes next; it allows you to enter passwords for some or all of the SYS, SYSTEM, SYSMAN, and DBSNMP users, depending on which management options have been configured in the database.
Next, you will see the Connection Mode page, which allows you to select either Dedicated Server mode or Shared Server mode. As noted in the Database Creation selection, we strongly recommend that you use Dedicated Server mode whenever sufficient memory is available,
Press Next to continue. If any configuration options have been updated, a confirmation message will be displayed. Press OK to continue.
When the configuration is complete, a message is displayed. This message reports the actions that have been completed successfully. For example, if the Enterprise Manager Database control has been added to the database, you'll see the message shown in Figure 10-27.
DBCA can delete any existing database, including its instances and administrative directories and files. To delete a new database, log in to an X session as the oracle user and run DBCA. On the Welcome page, select the Oracle Real Application Clusters Database option; and on the Operations page, select the Delete a Database optoin.
A list of existing databases will be displayed. Figure 10-28 shows an example list with only one database in it. Select a database to be deleted and press Finish.
This displays a confirmation message. Press Yes to delete the database; press No to exit.
If the any instances are currently running, these will be shutdown. The database files will be deleted, and a message will be displayed when the operation is completed.
DBCA allows you to create, modify, and delete database templates that can subsequently be used to create new databases. To manage database templates, log in to an X session as the oracle user and run dbca. On the Welcome page, select Oracle Real Application Clusters Database; and on the Operations page, select Manage Templates.
In addition letting you creating a database directly, DBCA lets you generate database creation scripts. There are three main benefits to building databases using these scripts:
The default location for the DBCA scripts is $ORACLE_BASE/admin/<database_name>/scripts. However, this directory will be deleted if you drop the database using DBCA. Therefore, if you wish to use the scripts to rebuild the database more than once, you should store them in another location, such as ˜/admin/<database_name>/scripts.
The same scripts are generated for both Admin-Managed and Policy-Managed databases. However, the content differs significantly, depending on your choice of configuration type.
Scripts are generated on each node in the cluster. To simplify the database creation process, ensure that all nodes are running before generating the scripts. The scripts differ on each node. Those generated on the first node perform the actual database creation; on the remaining nodes, most of the commands in the SQL*Plus scripts are commented out using the REM clause. The scripts should be run on each node to complete the database creation.
The following sections describe the scripts generated for Admin-Managed and Policy-Managed databases. We also highlight differences between them. We generated the scripts in our examples using a four-node cluster created with the Custom template, and we used the default DBCA options whenever possible.
In the Admin-Managed example, the database was called ADMIN, and the following scripts were generated on each node:
The contents of these scripts are discussed in the upcoming sections.
The ADMIN1.sh script is the top level shell script; it should be executed on each node in the cluster. This shell script calls all other scripts generated by DBCA. Here is the code for our example script:
#!/bin/sh
OLD_UMASK=`umask`
umask 0027
mkdir -p /u01/app/oracle/admin/ADMIN/adump
mkdir -p /u01/app/oracle/admin/ADMIN/dpdump
mkdir -p /u01/app/oracle/admin/ADMIN/hdump
mkdir -p /u01/app/oracle/admin/ADMIN/pfile
mkdir -p /u01/app/oracle/cfgtoollogs/dbca/ADMIN
umask ${OLD_UMASK}
ORACLE_SID=ADMIN1; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH; export PATH
echo You should Add this entry in the /etc/oratab:
ADMIN:/u01/app/oracle/product/11.2.0/dbhome_1:Y
/u01/app/oracle/product/11.2.0/dbhome_1/bin/sqlplus /nolog
@/u01/app/oracle/admin/ADMIN/scripts/ADMIN1.sqlThe preceding script overrides the default umask values, and then creates the administrative directories. In Oracle 11.2, foreground and background trace files and the alert log are written to directories in the Automated Diagostics Repository (ADR). Thus only the four administrative directories listed in Table 10-6 are now required.
Table 10.6. The Four Administrative Directories
Directory | Purpose |
|---|---|
adump | Auditing files |
dpdump | Data Pump Files |
hdump | High availability trace files |
pfile | Initialization file |
DBCA also creates a scripts directory for the database creation scripts, as well as a directory below $ORACLE_BASE/cfgtoollogs/dbca for DBCA trace and log files for the database.
After resetting umask, the script sets the ORACLE_SID. For Admin-Managed databases, the default SID is <database_name><instance_number> (e.g., ADMIN1). The script then invokes SQL*Plus to run the ADMIN1.sql, which will create the database and run the remaining SQL scripts.
Note that the shell script contains a reminder to add the database to /etc/oratab on each node in the cluster. If you create the database using the DBCA GUI, /etc/oratab will be updated automatically; if you create the database using DBCA-generated scripts, you will need to remember to update /etc/oratab manually.
The ADMIN1.sql script is the top-level SQL*Plus script. It is called by the shell script (in this case, ADMIN1.sh). Here is the code from our example ADMIN1.sql script:
set verify off ACCEPT sysPassword CHAR PROMPT 'Enter new password for SYS: ' HIDE ACCEPT systemPassword CHAR PROMPT 'Enter new password for SYSTEM: ' HIDE ACCEPT sysmanPassword CHAR PROMPT 'Enter new password for SYSMAN: ' HIDE ACCEPT dbsnmpPassword CHAR PROMPT 'Enter new password for DBSNMP: ' HIDE ACCEPT asmSysPassword CHAR PROMPT 'Enter ASM SYS user password: ' HIDE host /u01/app/oracle/product/11.2.0/dbhome_1/bin/orapwd file=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwADMIN1 force=y host /u01/app/11.2.0/grid/bin/setasmgidwrap o=/u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle host /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvctl add database -d ADMIN -o /u01/app/oracle/product/11.2.0/dbhome_1 -p +DATA/ADMIN/spfileADMIN.ora -n ADMIN -a DATA,REDO host /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvctl add instance -d ADMIN -i ADMIN1 -n london1 host /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvctl add instance -d ADMIN -i ADMIN2 -n london2 host /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvctl add instance -d ADMIN -i ADMIN3 -n london3 host /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvctl add instance -d ADMIN -i ADMIN4 -n london4 host /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvctl disable database -d ADMIN @/u01/app/oracle/admin/ADMIN/scripts/CreateDB.sql @/u01/app/oracle/admin/ADMIN/scripts/CreateDBFiles.sql @/u01/app/oracle/admin/ADMIN/scripts/CreateDBCatalog.sql @/u01/app/oracle/admin/ADMIN/scripts/JServer.sql @/u01/app/oracle/admin/ADMIN/scripts/context.sql @/u01/app/oracle/admin/ADMIN/scripts/xdb_protocol.sql @/u01/app/oracle/admin/ADMIN/scripts/ordinst.sql @/u01/app/oracle/admin/ADMIN/scripts/interMedia.sql @/u01/app/oracle/admin/ADMIN/scripts/cwmlite.sql @/u01/app/oracle/admin/ADMIN/scripts/spatial.sql @/u01/app/oracle/admin/ADMIN/scripts/emRepository.sql @/u01/app/oracle/admin/ADMIN/scripts/apex.sql @/u01/app/oracle/admin/ADMIN/scripts/CreateClustDBViews.sql
host echo "SPFILE='+DATA/ADMIN/spfileADMIN.ora'" > /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initADMIN1.ora @/u01/app/oracle/admin/ADMIN/scripts/lockAccount.sql @/u01/app/oracle/admin/ADMIN/scripts/postDBCreation.sql
Although you will have entered passwords during the interview stage of DBCA, these passwords are not stored in the scripts for security reasons. Therefore, you will be prompted to enter the passwords again, in this case, you will need to enter them for the SYS, SYSTEM, SYSMAN, and DBSNMP users. Note that in Oracle 11.2, the ACCEPT command includes a HIDE clause to prevent passwords from being echoed.
The script then creates a password file on the local node using the orapwd utility. The default password file name is $ORACLE_HOME/dbs/orapw<instance_name>.
In Oracle 10.2, the DBCA scripts did not include the SRVCTL statements required to add a RAC database to the OCR. Consequently, these commands had to be executed manually after using scripts to create the database. In Oracle 11.2, the SRVCTL commands are included in the scripts generated by DBCA. The SRVCTL commands perform the following actions:
Add the database to the OCR.
Add each instance to the OCR.
Temporarily disable the database.
Note that the srvctl statements are included in this version
The next part of the script invokes a series of SQL*Plus scripts that create the database and install various options. In this case, the following scripts were generated:
CreateDB.sqlCreateDBFiles.sqlCreateDBCatalog.sqlJServer.sqlxdb_protocol.sqlordinst.sqlinterMedia.sqlemRepository.sqlapex.sqlCreateClustDBViews.sql
These additional scripts are described in more detail in the upcoming sections.
The next step is to create a local init.ora file called init<instance_name>.ora with an SPFILE parameter that specifies the location of the Server Parameter file for the database. Two additional scripts are then executed. The first locks any unused accounts, while the second performs a series of post-database creation steps.
A default init.ora file is generated. This file contains default values for various instance parameters, as well as values for parameters that can be overridden during the DBCA interview phase (e.g., memory and block size).
The following code shows the parameters set in our init.ora example (the file has been modified for readability):
audit_file_dest=/u01/app/oracle/admin/ADMIN/adump audit_trail=db #cluster_database=true compatible=11.2.0.0.0 db_block_size=8192 db_create_file_dest=+DATA db_domain="" db_name=ADMIN db_recovery_file_dest=+REDO db_recovery_file_dest_size=4294967296 diagnostic_dest=/u01/app/oracle dispatchers="(PROTOCOL=TCP) (SERVICE=ADMINXDB)" log_archive_format=%t_%s_%r.dbf memory_target=845152256 open_cursors=300 processes=150 #remote_listener=cluster1-scan.example.com:1521 remote_login_passwordfile=exclusive ADMIN1.instance_number=1 ADMIN2.instance_number=2 ADMIN3.instance_number=3 ADMIN4.instance_number=4 ADMIN1.thread=1 ADMIN2.thread=2 ADMIN3.thread=3 ADMIN4.thread=4 ADMIN1.undo_tablespace=UNDOTBS1 ADMIN2.undo_tablespace=UNDOTBS2 ADMIN3.undo_tablespace=UNDOTBS3 ADMIN4.undo_tablespace=UNDOTBS4
The DIAGNOSTIC_DEST parameter was introduced in Oracle 11.1, and it specifies the location of the ADR for the database. This defaults to the value of $ORACLE_BASE.
For Admin-Managed databases, several instance-specific parameters must be specified (as shown in the second part of the file):
instance_number
thread
undo_tablespace
The contents of the init.ora file are used to start the instance and are subsequently stored in a central Server Parameter file (SPFILE).
Initially, the CLUSTER_DATABASE and REMOTE_LISTENER parameters are commented out. These parameters will not set to non-default values until configuration is complete and additional redo threads have been created for the database. If you wish to rebuild the database using these scripts, it will be necessary to remove any values set during previous database creations for these parameters, as well as for the CONTROL_FILES parameter.
Also, note that the REMOTE_LISTENER parameter now references the SCAN net service name, which resolves to a list of SCAN listeners. In previous versions, this parameter would specify a list of regular listeners.
The CreateDB.sql script executes the CREATE DATABASE statement, as in this example:
SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /u01/app/oracle/admin/ADMIN/scripts/CreateDB.log append
startup nomount pfile="/u01/app/oracle/admin/ADMIN/scripts/init.ora";
CREATE DATABASE "ADMIN"
MAXINSTANCES 32
MAXLOGHISTORY 1
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
DATAFILE SIZE 700M AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE SIZE 600M AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE SIZE 20M AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE SIZE 200M AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET WE8MSWIN1252
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 SIZE 51200K,
GROUP 2 SIZE 51200K
USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY
"&&systemPassword";
set linesize 2048;
column ctl_files NEW_VALUE ctl_files;
select concat('control_files=''', concat(replace(value, ', ', ''','''), ''''))
ctl_files from v$parameter where name ='control_files';
host echo &ctl_files >>/u01/app/oracle/admin/ADMIN/scripts/init.ora;
spool offThe preceding script starts an instance in NOMOUNT mode, and then executes the CREATE DATABASE statement. The statement creates three permanent tablespaces: SYSTEM, SYSAUX, and UNDOTBS1; it also creates one temporary tablespace called TEMP. In addition to the tablespaces, the script creates a single redo log thread. In this example, the redo log contains two logfile groups.
The CREATE DATABASE statement also creates the initial data dictionary tables, based on the contents of $ORACLE_HOME/rdbms/admin/sql.bsq. In Oracle 11.2, sql.bsq includes references to around 20 other *.bsq files that are also found in $ORACLE_HOME/rdbms/admin. These other *.bsq files contain definitions for various data dictionary components, such as dcore.bsq, dsqlddl.bsq, dmanage.bsq, dplsql.bsq, and so on.
For this database, we specified that ASM would be used for shared storage. ASM presents some interesting control file issues during database creation. When you create a file in ASM, you can only specify a partial path name; ASM will assign the next file number in the disk group to the new file. Therefore, you cannot know in advance what the file number will be assigned to a new file. Consequently, after the CREATE DATABASE statement has created the new database, including the controlfiles in the ASM disk group, this script selects the value of the CONTROL_FILES parameter from V$PARAMETER and appends this value to the init.ora file.
Note
Before you can create the database again, you will need to use these scripts to delete the CONTROL_FILES parameter from the init.ora file.
The CreateDBFiles.sql script creates the remaining tablespaces that are initialized by the CREATE DATABASE statement. In this example, the tablespaces include the UNDO tablespaces for the remaining three instances and a USERS tablespace that is also the default permanent tablespace.
Oracle sets all tablespaces to auto extend by default, as in this example:
SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool /u01/app/oracle/admin/ADMIN/scripts/CreateDBFiles.log append CREATE SMALLFILE UNDO TABLESPACE "UNDOTBS2" DATAFILE SIZE 200M AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED; CREATE SMALLFILE UNDO TABLESPACE "UNDOTBS3" DATAFILE SIZE 200M AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED; CREATE SMALLFILE UNDO TABLESPACE "UNDOTBS4" DATAFILE SIZE 200M AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED; CREATE SMALLFILE TABLESPACE "USERS" LOGGING DATAFILE SIZE 5M AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; ALTER DATABASE DEFAULT TABLESPACE "USERS"; spool off
Note that the SMALLFILE clause is specified for each new tablespace. This optional keyword was introduced in Oracle 10.1; it is the default value, and it indicates that this is not a BIGFILE tablespace.
Once the database has been created, the data dictionary must be populated. The CreateDBCatalog.sql script accomplishes this task:
SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool /u01/app/oracle/admin/ADMIN/scripts/CreateDBCatalog.log append @/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catalog.sql; @/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catblock.sql; @/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catproc.sql; @/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catoctk.sql; @/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/owminst.plb; connect "SYSTEM"/"&&systemPassword" @/u01/app/oracle/product/11.2.0/dbhome_1/sqlplus/admin/pupbld.sql; connect "SYSTEM"/"&&systemPassword" set echo on spool /u01/app/oracle/admin/ADMIN/scripts/sqlPlusHelp.log append @/u01/app/oracle/product/11.2.0/dbhome_1/sqlplus/admin/help/hlpbld.sql helpus.sql; spool off spool off
Next, the CreateDBCatalog.sql script executes several additional scripts (see Table 10-7).
Table 10.7. Data Dictionary Creation Scripts Executed by CreateDBCatalog.sql
Script Name | Description |
|---|---|
| Data dictionary views |
| Lock views |
| Procedural option scripts (PLSQL) |
| Oracle Cryptographic Toolkit |
| Oracle Workspace Manager |
| SQL*Plus Product User Profile |
| SQL*Plus Help |
The JServer.sql script installs JServer, which is the optional Java Virtual Machine that runs within the database. The following snippet shows the script contents from our example:
SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool /u01/app/oracle/admin/ADMIN/scripts/JServer.log append @/u01/app/oracle/product/11.2.0/dbhome_1/javavm/install/initjvm.sql; @/u01/app/oracle/product/11.2.0/dbhome_1/xdk/admin/initxml.sql; @/u01/app/oracle/product/11.2.0/dbhome_1/xdk/admin/xmlja.sql; @/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catjava.sql; @/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catexf.sql; spool off
The xdb_protocol.sql script installs the XDB database option, as in this example:
SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool /u01/app/oracle/admin/ADMIN/scripts/xdb_protocol.log append @/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catqm.sql change_on_install SYSAUX TEMP; connect "SYS"/"&&sysPassword" as SYSDBA @/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catxdbj.sql; @/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catrul.sql; spool off
The ordinst.sql script installs the ORD components, which include Oracle Multimedia and Spatial:
SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool /u01/app/oracle/admin/ADMIN/scripts/ordinst.log append @/u01/app/oracle/product/11.2.0/dbhome_1/ord/admin/ordinst.sql SYSAUX SYSAUX; spool off
The intermedia.sql script uses the following commands to install Oracle Multimedia:
SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool /u01/app/oracle/admin/ADMIN/scripts/interMedia.log append @/u01/app/oracle/product/11.2.0/dbhome_1/ord/im/admin/iminst.sql; spool off
The emRepository.sql script installs and configures the Enterprise Manager Database control. The script creates an Enterprise Manager management repository in the local database. If you are using the Enterprise Manager Grid control, it is not necessary to create a local management repository. Our sample emRepository.sql script looks like this:
SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo off spool /u01/app/oracle/admin/ADMIN/scripts/emRepository.log append @/u01/app/oracle/product/11.2.0/dbhome_1/sysman/admin/emdrep/sql/emreposcre /acfs1/product/11.2.0/dbhome_1 SYSMAN &&sysmanPassword TEMP ON; WHENEVER SQLERROR CONTINUE; spool off
The preceding script only creates the Enterprise Manager management repository; it does not configure the Enterprise Manager Database control.
The apex.sql script is new in Oracle 11.2. It installs objects for Application Express, which is a new rapid development tool that has been integrated with the database in Oracle 11g Release 2. Oracle Application Express (Apex) was formerly known as HTML DB. Here is the content of the script on our system:
SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool /u01/app/oracle/admin/ADMIN/scripts/apex.log append @/u01/app/oracle/product/11.2.0/dbhome_1/apex/catapx.sql change_on_install SYSAUX SYSAUX TEMP /i/ NONE; spool off
The CreateClustDBView.sql script is only executed for RAC databases, and it creates a number of additional RAC-specific data dictionary views, as in this example:
SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool /u01/app/oracle/admin/ADMIN/scripts/CreateClustDBViews.log append @/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catclust.sql; spool off
Chapter 12 discusses how to use these data dictionary views in more depth.
The lockAccount.sql script is also new in Oracle 11.2. It locks and expires all unlocked user accounts with the exception of SYS and SYSTEM, as in this example:
SET VERIFY OFF
set echo on
spool /u01/app/oracle/admin/ADMIN/scripts/lockAccount.log append
BEGIN
FOR item IN ( SELECT USERNAME FROM DBA_USERS WHERE ACCOUNT_STATUS IN
('OPEN', 'LOCKED', 'EXPIRED') AND USERNAME NOT IN (
'SYS','SYSTEM') )
LOOP
dbms_output.put_line('Locking and Expiring: ' || item.USERNAME);
execute immediate 'alter user ' ||
sys.dbms_assert.enquote_name(
sys.dbms_assert.schema_name(
item.USERNAME),false) || ' password expire account lock' ;
END LOOP;
END;
/
spool offThis postDBCreation.sql script performs post database creation processing. Here is the content of the script from our example:
SET VERIFY OFF connect "SYS"/"&&sysPassword" as SYSDBA set echo on spool /u01/app/oracle/admin/ADMIN/scripts/postDBCreation.log append select 'utl_recomp_begin: ' || to_char(sysdate, 'HH:MI:SS') from dual;
execute utl_recomp.recomp_serial(); select 'utl_recomp_end: ' || to_char(sysdate, 'HH:MI:SS') from dual; shutdown immediate; connect "SYS"/"&&sysPassword" as SYSDBA startup mount pfile="/u01/app/oracle/admin/ADMIN/scripts/init.ora"; alter database archivelog; alter database open; select group# from v$log where group# =7; select group# from v$log where group# =8; ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 7 SIZE 51200K, GROUP 8 SIZE 51200K; ALTER DATABASE ENABLE PUBLIC THREAD 4; select group# from v$log where group# =5; select group# from v$log where group# =6; ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 5 SIZE 51200K, GROUP 6 SIZE 51200K; ALTER DATABASE ENABLE PUBLIC THREAD 3; select group# from v$log where group# =3; select group# from v$log where group# =4; ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 3 SIZE 51200K, GROUP 4 SIZE 51200K; ALTER DATABASE ENABLE PUBLIC THREAD 2; host echo cluster_database=true >>/u01/app/oracle/admin/ADMIN/scripts/init.ora; host echo remote_listener=cluster3scan.juliandyke.com:1522 >>/u01/app/oracle/admin/ADMIN/scripts/init.ora; • connect "SYS"/"&&sysPassword" as SYSDBA • set echo on create spfile='+DATA/ADMIN/spfileADMIN.ora' FROM pfile='/u01/app/oracle/admin/ADMIN/scripts/init.ora'; shutdown immediate; host /u01/app/oracle/product/11.2.0/db_1/bin/srvctl enable database -d ADMIN; host /u01/app/oracle/product/11.2.0/db_1/bin/srvctl start database -d ADMIN; connect "SYS"/"&&sysPassword" as SYSDBA alter user SYSMAN identified by "&&sysmanPassword" account unlock; alter user DBSNMP identified by "&&dbsnmpPassword" account unlock; host /u01/app/oracle/product/11.2.0/db_1/bin/emca -config dbcontrol db -silent -cluster -ASM_USER_ROLE SYSDBA -ASM_USER_NAME ASMSNMP -CLUSTER_NAME cluster3 -LOG_FILE /u01/app/oracle/admin/ADMIN/scripts/emConfig.log -DBSNMP_PWD &&dbsnmpPassword -SYS_PWD &&sysPassword -ASM_USER_PWD &&asmSysPassword -SID ADMIN -ASM_SID +ASM1 -DB_UNIQUE_NAME ADMIN -EM_HOME /u01/app/oracle/product/11.2.0/db_1 -SYSMAN_PWD &&sysmanPassword -SERVICE_NAME ADMIN -ASM_PORT 1521 -PORT 1521 -LISTENER_OH /u01/app/11.2.0/grid -LISTENER LISTENER -ORACLE_HOME /u01/app/oracle/product/11.2.0/db_1 -HOST server14 -ASM_OH /u01/app/11.2.0/grid; spool off exit;
The preceding script recompiles any packages that have not previously been compiled. It also recompiles any packages that do not depend on objects that have been modified during database creation.
The database is shutdown and mounted, archive logging is enabled, and the database is fully opened. The database still only has one redo thread, so at this point the remaining three redo threads are created and enabled. Creating the additional redo threads means that instances can be started on the other nodes.
You will remember that two parameters were commented out in the init.ora file; CLUSTER_DATABASE and REMOTE_LISTENER. Values for these parameters are now appended to the init.ora file because the database can now be started in cluster mode.
Note
If you wish to rebuild the database using these scripts, you will need to remove the entries for these parameters from the init.ora file.
The next step is to create SPFILE based on the init.ora file. In this case, SPFILE will be stored in an ASM diskgroup. After SPFILE has been created, the instance is shutdown again.
In the OCR, the database is currently disabled, so the SRVCTL command is called first to enable the database and then to start the database. It will not be possible to start the instances on the remote nodes until the password file, init.ora file, and administrative directories have been created on those nodes.
Next, the SYSMAN and DBSNMP accounts are unlocked, and then the Enterprise Manager Configuration Assistant (EMCA) is executed to configure the Enterprise Manager Grid control.
For our example, we created a database named POLICY. Next, DBCA generated the following scripts on each node:
The files generated by DBCA for a Policy-Managed database are similar to those for an Admin-Managed database. There are but three exceptions, which are:
POLICY_<InstanceNumber>.shPOLICY_<InstanceNumber>.sqlinit.ora
We will discuss these exceptions in more detail in the following sections.
The top-level shell script for a Policy-Managed database is similar to that for an Admin-Managed database, as in this example:
#!/bin/sh
OLD_UMASK=`umask`
umask 0027
mkdir -p /u01/app/oracle/admin/POLICY/adump
mkdir -p /u01/app/oracle/admin/POLICY/dpdump
mkdir -p /u01/app/oracle/admin/POLICY/hdump
mkdir -p /u01/app/oracle/admin/POLICY/pfile
mkdir -p /u01/app/oracle/cfgtoollogs/dbca/POLICY
umask ${OLD_UMASK}
ORACLE_SID=POLICY_1; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH; export PATH
echo You should Add this entry in the /etc/oratab:
POLICY:/u01/app/oracle/product/11.2.0/dbhome_1:Y
/u01/app/oracle/product/11.2.0/dbhome_1/bin/sqlplus /nolog
@/u01/app/oracle/admin/POLICY/scripts/POLICY_1.sqlThe only significant change in a Policy-Managed database is that the $ORACLE_SID environment variable contains an underscore between the database name and the instance number. This convention provides more flexibility when choosing a name for a database.
The POLICY_1.sh shell script invokes the POLICY_1.sql script. Here is the content of the POLICY_1.sql script that we generated for this chapter's example:
set verify off ACCEPT sysPassword CHAR PROMPT 'Enter new password for SYS: ' HIDE ACCEPT systemPassword CHAR PROMPT 'Enter new password for SYSTEM: ' HIDE ACCEPT sysmanPassword CHAR PROMPT 'Enter new password for SYSMAN: ' HIDE ACCEPT dbsnmpPassword CHAR PROMPT 'Enter new password for DBSNMP: ' HIDE ACCEPT asmSysPassword CHAR PROMPT 'Enter ASM SYS user password: ' HIDE host /u01/app/oracle/product/11.2.0/dbhome_1/bin/orapwd file=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwPOLICY force=y host /u01/app/11.2.0/grid/bin/setasmgidwrap o=/u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle host /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvctl add srvpool -g POOL1 -l 0 -u 4 host /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvctl add database -d POLICY -o /u01/app/oracle/product/11.2.0/dbhome_1 -p +DATA/POLICY/spfilePOLICY.ora -n POLICY -g POOL1 -a DATA,REDO host /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvctl disable database -d POLICY @/u01/app/oracle/admin/POLICY/scripts/CreateDB.sql @/u01/app/oracle/admin/POLICY/scripts/CreateDBFiles.sql @/u01/app/oracle/admin/POLICY/scripts/CreateDBCatalog.sql @/u01/app/oracle/admin/POLICY/scripts/JServer.sql @/u01/app/oracle/admin/POLICY/scripts/context.sql @/u01/app/oracle/admin/POLICY/scripts/xdb_protocol.sql @/u01/app/oracle/admin/POLICY/scripts/ordinst.sql @/u01/app/oracle/admin/POLICY/scripts/interMedia.sql @/u01/app/oracle/admin/POLICY/scripts/cwmlite.sql @/u01/app/oracle/admin/POLICY/scripts/spatial.sql @/u01/app/oracle/admin/POLICY/scripts/emRepository.sql @/u01/app/oracle/admin/POLICY/scripts/apex.sql @/u01/app/oracle/admin/POLICY/scripts/owb.sql @/u01/app/oracle/admin/POLICY/scripts/CreateClustDBViews.sql host echo "SPFILE='+DATA/POLICY/spfilePOLICY.ora'" > /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initPOLICY_1.ora @/u01/app/oracle/admin/POLICY/scripts/lockAccount.sql @/u01/app/oracle/admin/POLICY/scripts/postDBCreation.sql
The most significant difference for this policy-managed database is the use of the SRVCTL command to create a new server pool for the database. Alternatively we could have used an existing server pool if a suitable one was available.
Note also that the SRVCTL add database also specifies the name of the server pool to which the database will be added.
The initial init.ora file for a Policy-Managed database differs significantly from an init.ora file for an Admin-Managed database. We've reformatted the following example to improve readability:
audit_file_dest=/u01/app/oracle/admin/POLICY/adump audit_trail=db compatible=11.2.0.0.0 #cluster_database=true
db_block_size=8192 db_create_file_dest=+DATA db_domain="" db_name=POLICY db_recovery_file_dest=+REDO db_recovery_file_dest_size=4294967296 diagnostic_dest=/u01/app/oracle dispatchers="(PROTOCOL=TCP) (SERVICE=POLICYXDB)" log_archive_format=%t_%s_%r.dbf memory_target=845152256 open_cursors=300 processes=150 #remote_listener=cluster1-scan.example.com:1521 remote_login_passwordfile=exclusive
The most obvious difference between the two versions of the file is that the instance-specific parameters for INSTANCE_NAME, THREAD, and UNDO_TABLESPACE are no longer required. In a Policy-Managed database, the management goal is to be able to add and drop instances automatically. Therefore, there are no fixed values for redo threads and undo tablespaces; these are assigned dynamically based on demand.
As with the Admin-Managed database, the CLUSTER_DATABASE and REMOTE_LISTENER parameters are commented out initially. These are updated by the postDBCreation.sql script when the database is capable of running in cluster mode.
Deinstalling the RDBMS has some of the same caveats and issues as when deinstalling the Grid Infrastructure software. For example, there is also no facility to deinstall software within the OUI. Instead, you should use the deinstall script within the deinstall directory of the RDBMS home directory. As with the Grid Infrastructure software, the deinstallation script runs a check operation, and you are prompted to confirm the deinstallation before it performs a clean operation. As shown in the following listing, the deinstallation operation will remove both the software from your RDBMS Oracle home and any configured database environments:
oracle@london1 deinstall]$ ./deinstall Checking for required files and bootstrapping ... Please wait ... Location of logs /u01/app/oraInventory/logs/ ############ ORACLE DEINSTALL & DECONFIG TOOL START ############ ######################## CHECK OPERATION START ######################## Install check configuration START Specify a valid location of central inventory for the Oracle home '/u01/app/oracle/product/11.2.0/dbhome_1' that you want to de-install: The cluster node(s) on which the Oracle home exists are: (Please input nodes separated by ",", eg: node1,node2,...)london1,london2,london3, london4 Checking for existence of the Oracle home location
/u01/app/oracle/product/11.2.0/dbhome_1 Oracle Home type selected for de-install is: RACDB Oracle Base selected for de-install is: /u01/app/oracle Checking for existence of central inventory location Checking for existence of the Oracle Grid Infrastructure home /u01/app/11.2.0/grid The following nodes are part of this cluster: london1,london2,london3,london4 Install check configuration END Network Configuration check config START Network de-configuration trace file location: /u01/app/oraInventory/logs/netdc_check385890921743793248.log Network Configuration check config END Database Check Configuration START Database de-configuration trace file location: /u01/app/oraInventory/logs/databasedc_check7918207744133071457.log Use comma as separator when specifying list of values as input Specify the list of database names that are configured in this Oracle home [PROD]: ###### For Database 'PROD' ###### RAC Database The nodes on which this database has instances: [london1, london2, london3, london4] The instance names: [PROD1, PROD2, PROD3, PROD4] The local instance name on node: PROD1 The diagnostic destination location of the database: /u01/app/oracle/diag/rdbms/prod Storage type used by the Database: ASM The details of database(s) PROD have been discovered automatically. Do you still want to modify the details of PROD database(s)? [n]: Database Check Configuration END Enterprise Manager Configuration Assistant START EMCA de-configuration trace file location: /u01/app/oraInventory/logs/emcadc_check.log Checking configuration for database PROD Enterprise Manager Configuration Assistant END Oracle Configuration Manager check START
OCM check log file location : /u01/app/oraInventory/logs//ocm_check1251.log Oracle Configuration Manager check END ######################### CHECK OPERATION END ######################### ####################### CHECK OPERATION SUMMARY ####################### Oracle Grid Infrastructure Home is: /u01/app/11.2.0/grid The cluster node(s) on which the Oracle home exists are: (Please input nodes seperated by ",", eg: node1,node2,...) london1,london2,london3,london4 Oracle Home selected for de-install is: /u01/app/oracle/product/11.2.0/dbhome_1 Inventory Location where the Oracle home registered is: The following databases were selected for de-configuration : PROD Database unique name : PROD Storage used : ASM Will update the Enterprise Manager configuration for the following database(s): PROD No Enterprise Manager ASM targets to update No Enterprise Manager listener targets to migrate Checking the config status for CCR london1 : Oracle Home exists with CCR directory, but CCR is not configured london2 : Oracle Home exists with CCR directory, but CCR is not configured london3 : Oracle Home exists with CCR directory, but CCR is not configured london4 : Oracle Home exists with CCR directory, but CCR is not configured CCR check is finished Do you want to continue (y - yes, n - no)? [n]: y
In this chapter, we have explained how to install the RDBMS software in a RAC environment. We have also explained how to create and configure a RAC database using DBCA. In the process of explaining how to create a database with DBCA, we introduced the concepts behind and use of Admin-Managed and Policy-Managed databases. In the next chapter, we will look at practical examples of these concepts in the wider context of Workload Management.