CHAPTER 2

Creating an Oracle Database Using DBCA

Exam Objectives

In this chapter, you will learn to

• 062.2.5.1    Create a Database by Using the Database Configuration Assistant (DBCA)

• 062.2.5.2    Generate Database Creation Scripts by Using DBCA

• 062.2.5.3    Manage Database Design Templates by Using DBCA

• 062.2.5.4    Configure Database Options by Using DBCA

This chapter goes through the theory and practice of creating a database—through the mechanics of creation using both the graphical user interface (GUI) and the command line. This chapter also describes how to create database templates. However, one objective that must be dealt with immediately is to demystify the process: creating a database is no big deal. Furthermore, you really do not have to worry about getting it right. Hardly anything is fixed at database-creation time. It certainly makes sense to think about how your database will be structured, its purpose, and its environment at creation time, but with one exception: everything can be changed afterward (although some changes may be awkward). As a general rule, keep things as simple as possible at this stage.

Create a Database by Using the Database Configuration Assistant

Creating a database is not difficult. The process can be quick and simple: It may take less than ten minutes, and a single two-word command will do it. However, there are some concepts you should understand before proceeding with the creation process, namely, the instance, the database, and the data dictionary.

The Instance, the Database, and the Data Dictionary

An Oracle server is an instance and a database; the two are separate but connected. The instance is composed of memory structures and processes in your random access memory (RAM) and on your central processing units (CPUs). Its existence is transient; it can be started and stopped. The database is composed of files on disk; once created, it persists until it is deleted. Creating an instance is nothing more than building the memory structures and starting the processes. Creating a database is done by the instance as a once-off operation, and the instance can then subsequently open and close the database many times. The database is inaccessible without the instance.

Within the database is a set of tables and other objects called the data dictionary. The data dictionary describes all the logical and physical structures in the database, including all the segments that store user data. The database creation process involves creating the bare minimum of physical structures needed to support the data dictionary and then creating the data dictionary within them.

An instance is defined by an instance parameter file. The parameter file contains directives that define (among other things) how the instance should be built in memory, including the size of the memory structures and the behavior of the background processes. After the instance has been built, it is said to be in no mount mode. In no mount mode, the instance exists but has not connected to a database. Indeed, the database may not have been created at this point.

All parameters, either specified by the parameter file or set implicitly, have defaults, except for the DB_NAME parameter. This parameter names the database to which the instance will connect. This name is also embedded in the controlfile. One parameter, CONTROL_FILES, tells the instance the location of the controlfile. This parameter defines the connection between the instance and the database. When the instance reads the controlfile (which it will find by reading the CONTROL_FILES parameter or by relying on the default value), if there is a mismatch in database names, the database will not mount. In mount mode, the instance has successfully connected to the controlfile. If the controlfile is damaged or nonexistent, it will be impossible to mount the database. The controlfile is small but vital.

Within the controlfile are pointers to the other files (the online redo log files and the datafiles) that make up the rest of the database. Having mounted the database, the instance can open the database by locating and opening these other files. An open database is a database where the instance has opened all the available online redo log files and datafiles. Also within the controlfile is a mapping of datafiles to tablespaces. This lets the instance identify the datafiles that make up the SYSTEM tablespace. In the SYSTEM tablespace, it will find the data dictionary. The data dictionary lets the instance resolve references to objects referred to in Structured Query Language (SQL) code to the segments in which they reside and work out where, physically, the objects are.

The creation of a database server must therefore involve these steps:

1.  Create the instance.

2.  Create the database.

3.  Create the data dictionary.

In practice, though, there is a fourth step.

4.  Make the database usable.

The data dictionary, as initially created with the database, is fully functional but unusable. It has the capability for defining and managing user data but cannot be used by normal human beings because its structure is too abstruse. Before users (or database administrators) can actually use the database, a set of views must be created on top of the data dictionary that will present it in a human-understandable form. Also, many PL/SQL packages are required to add functionality.

The data dictionary itself is created by running a set of SQL scripts that exist in the ORACLE_HOME/rdbms/admin directory. These are called by the CREATE DATABASE command. The first is sql.bsq, which then calls several other scripts. These scripts issue a series of commands that create all the tables and other objects that make up the data dictionary.

The views and other objects that make the database usable are generated with more scripts in the ORACLE_HOME/rdbms/admin directory and have a cat prefix. Examples of these are catalog.sql and catproc.sql, which should always be run immediately after database creation. There are many other optional “cat” scripts that will enable certain features—some of these can be run at creation time; others might be run subsequently to install the features at a later date.

Using the Database Configuration Assistant to Create a Database

These are the steps to follow to create a database:

1.  Create a parameter file and (optionally) a password file.

2.  Use the parameter file to build an instance in memory.

3.  Issue the CREATE DATABASE command. This will generate, at a minimum, a controlfile, two online redo log files, one datafile each for the SYSTEM and SYSAUX tablespaces, and a data dictionary. The syntax does allow a lot more to be done at this point.

4.  Run SQL scripts to generate the data dictionary views and the supplied PL/SQL packages.

5.  Run SQL scripts to generate the code that implements Enterprise Manager Database Express as well as any options (such as Java) the database will require.

6.  On Windows systems, there is an additional step because Oracle runs as a Windows service. Oracle provides a utility, oradim.exe, to assist you in creating this service.

These steps can be executed interactively from the SQL*Plus prompt or through a GUI tool, the Database Configuration Assistant (DBCA). Alternatively, you can automate the process by using scripts or DBCA with a response file.

Regardless of the platform you are running on, the easiest way to create a database is through DBCA. It creates a parameter file and a password file and then generates scripts that will start the instance, create the database, and generate the data dictionary and the data dictionary views. Alternatively, you can create the parameter file and password file by hand and then do the rest from a SQL*Plus session. Many database administrators (DBAs) combine the two techniques: They use DBCA to generate the files and scripts and then look at them and perhaps edit them before running them from SQL*Plus.

To launch DBCA on Windows, take the shortcut on the Start menu. This is an example of the navigation path: Start | Programs | Oracle – OraDB12Home1 | Configuration and Migration Tools | Database Configuration Assistant.

Note that the precise path will vary depending on the name given to the Oracle Home at install time. Alternatively, run the dbca.bat script from a CMD prompt. Following a standard installation, it will be included in your Windows search path.

To launch DBCA on Linux, first set the environment variables that should always be set for any Linux DBA session: DISPLAY, ORACLE_BASE, ORACLE_HOME, and PATH. Here is an example of a script that will do this:

Note that the Base and Home will vary according to choices made at install time. The variables also exist in a Windows environment but are usually defined as registry variables set by the Oracle Universal Installer (OUI) rather than shell variables. The DISPLAY variable must be set to the address of your X server. To launch DBCA, run the dbca shell script, which is located in the $ORACLE_HOME/bin directory.

Remember that (with one exception) every choice made at database creation time can be changed later but that some changes are awkward and will involve downtime. Therefore, it is not vital to get everything right—but the more you can get right, the better.

The DBCA Dialog

The DBCA dialog consists (depending on options) of about 13 steps.

Step 1: Database Operation

The radio buttons available in this first step allow you to select the type of operation you want to perform.

•  Create Database    You can launch a dialog that will prompt you to create an instance and a database.

•  Configure Database Options    You can adjust an existing database, perhaps by installing additional capabilities.

•  Delete Database    You can remove any existing instances and databases.

•  Manage Templates    A template is a stored, preconfigured database. Some templates are supplied, or you can create your own from an existing database.

•  Manage Pluggable Databases    Pluggable databases are a new feature in 12c and are beyond the scope of the Oracle Certified Associate (OCA) curriculum.

Step 2: Creation Mode

The Create A Database With Default Configuration option gives you access to a small subset of DBCA’s capabilities. The Advanced Mode option, on the other hand, gives you access to all the possibilities.

Step 3: Database Template

A template is a stored version of a preconfigured database from which you can generate a new database. Creating a database from a template is much faster than creating it from nothing, but not all options are configurable. Oracle supplies two demonstration templates: a general-purpose template, which is configured for nothing in particular, and a data warehouse template, which is supposedly optimized for query processing rather than transaction processing. The Custom Database option generates a dialog that will give you complete freedom in how the database is configured, with no predefined limits.

Step 4: Database Identification

The database must have a global database name. The global name consists of a database name and, optionally, a database domain. The database name is embedded in the controlfile, will be specified as the DB_NAME instance parameter, and must be unique on the server machine. The name may be up to eight characters long, including alphanumeric characters, underscores (_), number signs (#), and dollar signs ($), and the first character must be alphabetic. Case sensitivity may vary from one platform to another. The database name is an example of a value you want to get right; it can be changed later, but doing so requires downtime.

The database domain may be up to 128 characters long, including alphanumeric characters and the number sign (#). Dots can be used as separators for the different elements of the domain. The domain is used for the DB_DOMAIN instance parameter. Note that although it is possible to construct a global name that resembles the fully qualified domain names (FQDNs) used on Transmission Control Protocol (TCP) networks, there is no relationship between them.

The second required value is the system identifier (SID). This is the name of the Oracle instance to be created and is used for the ORACLE_SID parameter. There is no reason for this to be the same as the DB_NAME value, but for the sake of your sanity, you will usually keep it the same. It must be unique on the database server machine.

Step 5: Management Options

Use of Oracle Enterprise Manager (OEM) is entirely optional. If you want to use it, you have two possibilities: Enterprise Manager Database Express and Enterprise Manager Cloud Control. Enterprise Manager Database Express is a version of OEM that is configured within the one managed database. If you install a second database, you will require a separate instance of Database Express, configured within that database. Enterprise Manager Cloud Control is Oracle’s universal management system, which usually runs on a dedicated machine (or machines) with agents running on each target server. If you have a Cloud Control installation available, DBCA needs to know how to log on to it.

Step 6: Database Credentials

In this step you provide passwords for the SYS and SYSTEM schemas. SYS is the schema that owns the data dictionary itself, and the password is written out to the external password file. The SYS user is very special, and the password may be required for some critical functions (such as startup and shutdown). The SYSTEM schema is really just a user like any other, but it is given many powerful privileges.

Oracle best practice is that these passwords should be at least eight characters, including a mix of upper- and lowercase letters and at least one digit. The characters are any included in your database character set (detailed later in the chapter). If you specify a password that is considered too simple, DBCA will complain but will let you proceed nonetheless.

A Windows variation at this point is that you will be prompted for the password of the operating system account that owns the Oracle Home.

Step 7: Storage Locations

The database may be created on either an Automatic Storage Management disk group or in a file system directory. Either way, you can nominate the destination or let DBCA decide where to store the database files, based on its derived value for the ORACLE_BASE environment variable. Both storage types can also optionally use Oracle Managed Files (OMF). OMF lets Oracle take control of naming all your database files and constructing a suitable directory structure within which to store them.

The Recovery Related Files section of this step lets you nominate a Fast Recovery Area to be used as a default location for backups and other files related to recovery and also to enable the archive log mode of operation. Most (if not all) production databases will run in archive log mode, but it can be enabled at any time subsequent to database creation.

Step 8: Database Options

Various options are separately licensed and should be selected or deselected according to your license. The Sample Schemas tab will cause DBCA to install the demonstration schemas into their own tablespace.

Step 9: Initialization Parameters

There are more than 300 publicly visible initialization parameters and thousands more so-called hidden parameters. DBCA provides prompts for the bare minimum, with a link to an editor where you can see its defaults for the others and set any you want. Step 9 of the DBCA dialog prompts either for an overall total for memory to be used by the instance or to specify values for the System Global Area (SGA) and the Program Global Area (PGA). SGA is used by the instance and is accessible from all sessions; it is divided into many substructures, some of which can be individually tuned. PGA is divided into private memory areas for each connected session and process. This stage of the DBCA dialog sets some of the memory parameters.

The Sizing tab shows two parameters: DB_BLOCK_SIZE (although the prompt is just Block Size) and PROCESSES. DB_BLOCK_SIZE is critical; it is the one parameter that can never be changed after database creation. This is because it specifies the size of the blocks into which the SYSTEM tablespace datafile is formatted. The SYSTEM tablespace stores the data dictionary, and changing it would require re-creating the data dictionary, which would be equivalent to creating a new database. The default DB_BLOCK_SIZE is 8KB. The legal values are 2KB, 4KB, 8KB, 16KB, and (on some platforms) 32KB.

The Character Sets tab lets you choose the database character set and the alternative national character set. The database character set is used for the data dictionary and all character data types except NVARCHAR2, NCHAR, and NCLOB, which use the national character set. It is theoretically possible to change the character set at any time after database creation, but this is a potentially dangerous process and not one on which to embark lightly. Many DBAs believe that best practice is not to use the default (which is derived from your operating system) but to use Unicode—specifically, AL32UTF8.

The Connection Mode tab lets you configure the shared server. This topic is discussed in Chapter 14.

Step 10: Creation Options

In this step, the Create Database check box will launch the actual creation. The Save As A Database Template check box will generate a stored template based on the preceding dialog, which can subsequently be used many times (this is selected at step 3). Finally, the Generate Database Creation Scripts check box will generate and save a set of scripts that can be run manually to create the database.

Step 11: Prerequisite Checks

DBCA runs a few brief “sanity checks” to confirm that the creation will work. If there are any failures, they really should be addressed before proceeding. However, you can choose to ignore them and proceed regardless.

Step 12: Summary

The Summary window shows what DBCA intends to do. Scan through the report, and if you see anything you do not like, this is a good time to use the Back button and make changes.

Step 13: Progress Page

The Progress Page window shows the creation of the scripts (if they were requested) and then the various stages of database creation. This may take 10 minutes, or it may take an hour or more. The difference is largely dependent on whether a template was used, what options were selected, and the hardware specifications.

Exercise 2-1: Create a Database with DBCA    Using the graphical DBCA tool, create a database. Repeat the exercise as many times as you can, deleting any previously created databases first if you are short on RAM or disk space. It is important to become comfortable with the process and to experiment with the various options presented by the dialog.

Some of the responses will vary depending on whether the platform is Windows, Linux, or something else. The variations will be obvious, and most commonly have to do with directory naming conventions. Here are the steps to follow:

1.  Log on to the server machine.

Connect to the server as the operating system user who did the installation. The standard account name is oracle.

2.  Launch DBCA.

From a command shell, make sure you have the appropriate environment variables set. The following is a typical dialog on a Linux system using the bash shell:

The DISPLAY variable is set to point to the address of your X server (typically your PC). Test it by running any X program, such as the X-Clock. If you are running on the console or perhaps in a VNC desktop, this will not be necessary. Next, the ORACLE_BASE value is set and assumes an Optimal Flexible Architecture (OFA) installation (binaries owned by user oracle). ORACLE_HOME and PATH continue the OFA standard. Using the which utility confirms that dbca is on the search path, so launch it.

On Windows, it may be possible to find a link to DBCA in the Start menu and run it relying on variables set in the registry. Alternatively, you could control everything manually from a command prompt, like so:

3.  Respond to the prompts as follows:

A.  Select the Create Database radio button. Click Next.

B.  Select the Advanced Mode radio button. Click Next.

C.  Select the Custom Database radio button. Click Next.

Note that, at this point, depending on whether the Grid Infrastructure product has already been installed on the server, you may get a prompt regarding Oracle Restart. Ignore this and continue.

D.  Specify both the Global Database Name and SID values as orcl121. Click Next.

E.  Select the Configure Enterprise Manager (EM) Database Express check box. Click Next.

F.  Select the radio button Use The Same Administrative Password For All Accounts and enter Oracle121. Click Next.

G.  Select File System in the Storage Type drop-down box, and select the radio button Use Database File Locations From Template. Leave everything else at the default. This will cause DBCA to create both the database and the Fast Recovery Area beneath the ORACLE_BASE. Click Next.

H.  On the Database Components tab, leave everything at the default. Click the Sample Schemas tab and select the Sample Schemas check box. Click Next.

I.  Acceptable memory settings will depend on your environment, and the default may or may not be suitable. These values usually work, even on very low-spec systems. On the Memory tab, select the Typical radio button, set the value of Memory Size (SGA and PGA) to 640MB, and deselect the Use Automatic Memory Management check box.

On the Character Sets tab, select the Use Unicode (AL32UTF8) radio button. Click Next.

J.  Select the check boxes for both Create Database and Generate Database Creation Scripts. Note the directory where the scripts will be created. Click Next.

K.  This should not require any input.

L.  Study the summary. Click Finish.

M.  The scripts will be generated, followed by a small modal window telling you where they are (if you don’t see this window, make sure that it isn’t hiding beneath something else). Click OK.

As the wizards run, you will notice that the first two, which create the instance and the database, complete in just a few minutes. The others, which create the data dictionary views and the various components, will take much longer.

On completion, you will be presented with a success window that shows the uniform resource locator (URL) for accessing Enterprise Manager Database Express. It will resemble this:

http://db121a.example.com:5500/em

Note this URL for future use.

4.  After the installation, confirm that you can log on to the newly created database using SQL*Plus.

From an operating system prompt, set your ORACLE_SID environment variable to the name of the database instance, and then log on and off, as in this example for Windows:

Alternatively, use this example for Linux:

5.  Repeat ad infinitum.

Go through this exercise as often as you can, making your own variations, until you are happy with all the options. At Step 1: Database Operation, you may want to select the Delete Database radio button to remove the previous creation.

Generate Database Creation Scripts by Using DBCA

There is no reason not to create a database interactively with DBCA, but in many situations it is better to create a database from the command line. Why? Perhaps for one of the following reasons:

•  The server machine may not have graphics capability.

•  Change control procedures may require tested scripts.

•  You may have to create a dozen identical databases.

•  You do not want to sit in front of a screen responding to prompts.

Whatever the reason, it is straightforward to create a database manually, if you have the scripts. The easiest way to write the scripts is to let DBCA do it for you.

Generating the Scripts

To generate the scripts, launch DBCA and go through the dialog, taking whatever options are appropriate. At step 10, select the appropriate check box and choose a directory. The default location is beneath ORACLE_BASE. The typical location for a database named orcl121 on Windows would be as follows:

On Linux, the typical location would be as follows:

Whatever the platform, the scripts have the same structure: shell script named after the database (for example, orcl121.sh for Linux, orcl121.bat for Windows) that does some operating system (OS) work and then launches SQL*Plus to run a SQL script (called oracle121.sql). The SQL script drives the rest of the database creation. Studying these scripts and the scripts they call is instructive.

The Creation Scripts

The starting point is the shell script. Take a look at a Linux example:

This creates a few directories, using values calculated from the ORACLE_BASE and ORACLE_HOME environment variables, provided in the DBCA dialog, or from defaults with appropriate access permissions. It then concludes with the call to SQL*Plus to launch the SQL script orcl121.sql. A variation on Windows will be calls to the ORADIM utility that creates the Windows service under which the instance will run.

The driving SQL script will vary greatly, depending on the options taken in the DBCA dialog. Here is one example:

The ACCEPT commands prompt for passwords for the SYS and SYSTEM schemas. Then the script invokes a host shell to run the orapwd utility. This utility creates the external password file with a name that is platform specific. On Linux, it will be $ORACLE_HOME/dbs/orapw<DBNAME> (where <DBNAME> is the name of the database), and on Windows, it will be %ORACLE_HOME%databasePWD<DBNAME>.ora.

Following this is a set of calls to other SQL scripts, beginning with CreateDB.sql.

The second line connects as user SYS using the supplied password. The fifth line starts the database in no mount mode using a parameter file named init.ora. This file will be populated with parameters set by default or specified in the DBCA dialog. The next command (which continues to the end of the file) creates the database.

Following the CREATE DATABASE “orcl121” line are some settings for limits (such as MAXDATAFILES=100, meaning that this database will be restricted to 100 datafiles) and then clauses for four tablespaces:

•  The SYSTEM tablespace (where the data dictionary lives) will be in a datafile named system01.dbf with a size of 700MB.

•  The SYSAUX tablespace (objects that are not associated with the data dictionary but are closely related) will be in a datafile named sysaux01.dbf with a size of 550MB.

•  A default temporary tablespace (for temporary data—space needed by sessions for, one hopes, only a brief period) named TEMP will be in tempfile temp01.dbf, with a size of 20MB. An undo tablespace (used for undo segments, which are necessary to ensure transactional consistency) named UNDOTBS1 will use the datafile undotbs01.dbf, with a size of 200MB.

Then the character sets for the database and national language are specified. In the example, they are Unicode. The LOGFILE section specifies that the database should have three online log file groups, each consisting of one file (the log file member) that’s 50MB in size. Finally, the passwords for SYS and SYSTEM are set.

This database creation command takes only a couple minutes to run. All it does is create the minimal structures necessary for a database, most importantly the data dictionary. Control then returns to the calling SQL script, which (in the example) launches more scripts.

•  CreateDBFiles.sql creates a tablespace called USERS, to be used as the default tablespace for storing permanent objects (such as tables).

•  CreateDBCatalog.sql calls a set of scripts to generate the required views onto the data dictionary and the various supplied PL/SQL packages.

•  Several scripts (JServer.sql through apex.sql) then generate various options that were selected in the DBCA dialog.

•  postDBCreation.sql runs anything necessary immediately after creation, such as applying bundled patches and converting the pfile to an spfile.

•  lockAccount.sql locks all preseeded accounts (with a few exceptions) and finally restarts the database.

The scripts generated and their contents vary greatly, depending on the DBCA dialog. For example, if at step 3 you chose to create a database from a template, the whole process is much simpler because the database does not need to be created, and most of the work is done via calls to the RMAN Recovery Manager procedures, which in effect restore a database from a backup in the template.

The Initialization Parameter File

To start the database instance, DBCA must create an initialization parameter file. This is generated whether you select the option to create a database or to generate scripts. The file is generated in the same directory as the other scripts and is nominated on the STARTUP command in the CreateDB.sql script.

Here is the file generated by the dialog from Exercise 2-1 (comment lines removed for brevity):

All these parameters will be covered in later chapters. The file has just 16 parameters specified out of hundreds and is the bare minimum needed to get a typical database running. Many more parameters will usually be added subsequently according to the requirements for the environment, scale, and performance.

Exercise 2-2: Generate Database Creation Scripts by Using DBCA    Use DBCA to generate several sets of database creation scripts. Perform as many iterations of this as you want, supplying a different database name and SID each time and selecting different options. Here are the steps to follow:

1.  Launch DBCA. Respond to the prompts as follows:

A.  Select the Create Database radio button. Click Next.

B.  Select the radio button Advanced Mode. If you select Create A Database With Default Configuration, you do not get a prompt to generate scripts. Click Next.

C.  Select the radio button General Purpose Or Transaction Processing. Click Next.

D.  Specify the value gpdb for both Global Database Name and SID. Click Next.

E.  Deselect everything. Click Next.

F.  Enter the passwords as Oracle121. Click Next.

G.  Choose Storage Type: File System. Leave everything else at the default. Click Next.

H.  Leave everything at the default. Click Next.

I.  Leave everything at the default. Click Next.

J.  Deselect the check boxes Create Database and Save As A Database Template. Select the check box Generate Database Creation Scripts. Note the destination directory. Click Next.

K.  No input needed.

L.  Study the summary. Note that the install from a template will include all the options that were prompted for in the previous exercise and will also have values for various parameters. Click Finish.

M.  The scripts will be generated. Click OK and then Close.

2.  Study the scripts.

Attempt to reverse-engineer the creation process. Note that the process is much simpler when using a template. Compare the scripts generated by this exercise with those generated by the previous exercise.

3.  Repeat.

And repeat again, with variations. It is vital to become familiar with the DBCA dialog and with the scripts that it generates.

Manage Database Design Templates by Using DBCA

A template is a stored definition of a database. This definition can be subsequently used to create any number of databases, and it’s portable across platforms. Templates come in one of two forms:

•  Structure only    A structure-only template contains the structural information (database options selected, storage details, initialization parameters) but does not include the actual datafiles. When this template is used, the database will be created from nothing. It is not possible for any user-defined data to be included.

•  Structure and data    A structure-and-data template also includes the datafiles. Databases created from this template will be identical to the database from which the template was generated, as of the time of generation.

Templates are managed through DBCA. If at step 1 of the DBCA dialog you choose the radio button for Manage Templates, you will be prompted whether to delete a template or create one from one of three sources:

•  An already existing template

•  An existing database, structure only

•  An existing database, structure and data

The template or database must already exist on the machine from which DBCA is running. Note that the template creation from a running database will require a restart of the source database if the template includes datafiles, but not if it is structure only.

The DBCA dialog is perfectly straightforward. You are prompted for the source and type of the template and, if it includes datafiles, for the location of the file that will store the compressed database. Once the template has been created, it will be visible as a source for a new database at step 3 of the DBCA dialog.

The underlying storage for a template is from files in the ORACLE_HOME/assistants/dbca/templates directory. Copying these files to another Oracle Home will make the template available there.

Exercise 2-3: Manage Database Design Templates by Using DBCA    Use DBCA to create a template from the database created in this chapter’s first exercise. These are the steps to follow:

1.  Launch DBCA. Respond to the prompts as follows:

A.  Select the Manage Templates radio button. Click Next.

B.  Select the radio buttons Create A Database Template and From An Existing Database (Structure As Well As Data). Click Next.

C.  Select orcl121 from the Database Instance drop-down box. Click Next.

D.  Give the template a name and description. Note that the name is used for the name of the template datafile in ORACLE_HOME/assistants/dbca/templates. Click Next.

E.  Select the radio button for converting the file locations to use OFA structure. This is usually the best option because it makes it simpler to use the template on another machine with a different file system. Click Next.

F.  Note the summary information. Click Finish.

G.  The template will be created, restarting the source database if it is running. This is necessary for the copy of the datafiles to be consistent; the copy is made with the database in mount mode. Click OK and then Close.

2.  Use the template.

Use the template (if you want) to create a database. Launch DBCA and respond to the prompts as follows:

A.  Select the radio button Create Database. Click Next.

B.  Select the radio button Advanced Mode. Click Next.

C.  The list of templates presented will include the newly created template. Note that it does include datafiles. Select its radio button, and click the Show Details button. This will generate the equivalent of the summary information previously displayed when the source database was created. Study this and then dismiss the window.

3.  Exit DBCA. Click the Cancel button and then confirm to exit.

Configure Database Options by Using DBCA

The final topic on database creation is modifying databases subsequent to creation. The concept to hang onto is that a database option, generally speaking, is a combination of executable code in the Oracle Home and necessary objects (such as tables and PL/SQL procedures) in the database. The software installation will have installed the executable code. But for any one database running off that Home, the options will be enabled only if the objects have been created. The DBCA database creation prompts for which options to install, which determines the scripts called by the driving database creation script. It will by now be apparent why when using a template with datafiles it is not possible to control the options: They either will or will not have existed in the database from which the template was generated.

Using DBCA to configure options causes DBCA to generate calls to scripts that will install options in the database. It is not possible to uninstall options through DBCA. To see the installed options, log on to the database and query the DBA_REGISTRY view. This is an example:

Exercise 2-4: Configure Database Options by Using DBCA    In this exercise, you’ll use DBCA to add an option that was not selected for the original database creation. Here are the steps to follow:

1.  Confirm the list of options installed.

From an operating system prompt, log on to the database and run this query:

2.  Launch DBCA. Respond to the prompts as follows:

A.  Select the Configure Database Options radio button. Click Next.

B.  Select the radio button for the orcl21 database. Click Next.

C.  DBCA presents the list of components given at creation time with those previously selected grayed out and any remaining available for selection. Note that it is not possible to add the sample schemas with this method.

Select the check box Oracle Label Security. Click Next.

D.  Deselect the check box Oracle Database Vault. Click Next.

E.  Leave everything at the default. Click Next.

F.  The summary will show what is to be installed. Click Finish.

G.  The option will install. Click OK and Close.

3.  Confirm the installation.

Rerun the query against DBA_REGISTRY and note that Label Security is now an installed component.

Two-Minute Drill

Create a Database by Using the Database Configuration Assistant

•  DBCA is written in Java and requires a graphical display.

•  The dialog prompts for all necessary information to create an instance and a database.

•  At the conclusion of the creation, the database is ready for use.

Generate Database Creation Scripts by Using DBCA

•  The generation includes a shell script that calls a set of SQL scripts.

•  Also generated are a parameter file and a password file.

•  The scripts can optionally be edited and then run manually.

Manage Database Design Templates by Using DBCA

•  A template is a saved database definition from which more databases can be created.

•  Templates include structural information and, optionally, datafiles.

•  A structure-only template cannot include references to user objects.

•  A structure-and-data template permits only minimal changes at creation time.

Configure Database Options by Using DBCA

•  Options are installed by running scripts against an existing database.

•  It is not possible to uninstall options through DBCA.

Self Test

1.  Which of these operations can be accomplished with DBCA? (Choose all that apply.)

A.  Create a database

B.  Remove a database

C.  Upgrade a database

D.  Add database options

E.  Remove database options

2.  To create a database, in what mode must the instance be? (Choose the best answer.)

A.  Not started

B.  Started in NOMOUNT mode

C.  Started in MOUNT mode

D.  Started in OPEN mode

3.  Several actions are necessary to create a database. Place these in the correct order:

1.  Create the data dictionary views.

2.  Create the parameter file.

3.  Create the password file.

4.  Issue the CREATE DATABASE command.

5.  Issue the STARTUP command.

(Choose the best answer.)

A.  2, 3, 5, 4, 1

B.  3, 5, 2, 4, 1

C.  5, 3, 4, 2, 1

D.  2, 3, 1, 4, 5

4.  What instance parameter cannot be changed after database creation? (Choose the best answer.)

A.  All instance parameters can be changed after database creation.

B.  All instance parameters can be changed after database creation if it is done while the instance is in MOUNT mode.

C.  CONTROL_FILES.

D.  DB_BLOCK_SIZE.

5.  What files are created by the CREATE DATABASE command? (Choose all that apply.)

A.  The controlfile

B.  The server parameter file

C.  The online redo log files

D.  The password file

E.  The static initialization parameter file

F.  The SYSAUX tablespace datafile

G.  The SYSTEM tablespace datafile

6.  What will happen if you do not run the catalog.sql and catproc.sql scripts after creating a database? (Choose the best answer.)

A.  It will not be possible to open the database.

B.  It will not be possible to create any user tables.

C.  It will not be possible to use PL/SQL.

D.  It will not be possible to query the data dictionary views.

E.  It will not be possible to connect as any user other than SYS.

7.  What tools can be used to manage templates? (Choose the best answer.)

A.  The Database Configuration Assistant

B.  The Database Upgrade Assistant

C.  SQL*Plus

D.  Database Express

E.  The Oracle Universal Installer

8.  At what point can you not choose or change the database character set? (Choose the best answer.)

A.  At database creation time, if you are using a DBCA template

B.  At database creation time, if you are using a DBCA template that includes datafiles

C.  At database creation time, if you are not using a DBCA template

D.  After database creation, using DBCA to install options

9.  If there are several databases created off the same Oracle Home, how will Database Express be configured? (Choose the best answer.)

A.  Database Express will give access to all the databases created from the one Oracle Home through one URL.

B.  Database Express will give access to each database through different ports.

C.  Database Express needs to be configured in only one database and can then be used to connect to all of them.

D.  Database Express can manage only one database per Oracle Home.

10.  The SYSAUX tablespace is mandatory. What will happen if you attempt to issue a CREATE DATABASE command that does not specify a datafile for the SYSAUX tablespace? (Choose the best answer.)

A.  The command will fail.

B.  The command will succeed, but the database will be inoperable until the SYSAUX tablespace is created.

C.  A default SYSAUX tablespace and datafile will be created.

D.  The SYSAUX objects will be created in the SYSTEM tablespace.

11.  What files are generated when you choose the option Generate Database Creation Scripts in the Database Configuration Assistant? (Choose all that apply.)

A.  A shell script

B.  SQL scripts

C.  A parameter file

D.  A password file

E.  A response file

Self Test Answers

1.      A, B, and D. DBCA can create and remove databases and also install options into existing databases.
    C and E are incorrect. A database upgrade would require the Database Upgrade Assistant (DBUA), not DBCA. Removing options cannot be done through any wizard. It is a manual process.

2.      B. The instance must be running before you create a database.
    A, C, and D are incorrect. The instance must be started, but it cannot be mounted (because there is no controlfile) or opened (because there are no datafiles).

3.      A. This is the correct sequence (although 2 and 3 could be done the other way round).
    B, C, and D are incorrect. Those sequences are not possible.

4.      D. This is the one parameter that can never be changed after creation.
    A, B, and C are incorrect. A and B are incorrect because DB_BLOCK_SIZE cannot be changed no matter when you try to do it. C is incorrect because the CONTROL_FILES parameter can certainly be changed, although this will require a shutdown and restart.

5.      A, C, F, and G. All of these will always be created, by default, if they are not specified.
    B, D, and E are incorrect. B and D are incorrect because these should exist before the instance is started. E is incorrect because the conversion of the static parameter file to a dynamic parameter file occurs, optionally, only after the database is created.

6.      D. The database will function, but without the data dictionary views and PL/SQL packages created by these scripts, it will be unusable.
    A, B, C, and E are incorrect. A is incorrect because the database will open; in fact, it must be open to run the scripts. B is incorrect because tables and other objects can certainly be created. C is incorrect because PL/SQL will be available; it is the supplied packages that will be missing. E is incorrect because although the scripts need to be run by SYS, you can connect as other users.

7.      A. DBCA is the only tool that can manage templates.
    B, C, D, and E are incorrect. These are all incorrect because only DBCA offers template management.

8.      D. It is not possible to change character sets after database creation with DBCA. Character sets are not installed as options.
    A, B, and C are incorrect. A and B are incorrect because templates are not relevant. If the template includes datafiles, DBCA will change the character set behind the scenes. C is incorrect because creation without a template gives you complete control, including your choice of character set.

9.      B. Database Express can be used for each database and will be configured with a different port for each one.
    A, C, and D are incorrect. A is incorrect because this would require Cloud Control. C is incorrect because Database Express must be installed in every database that will use it. D is incorrect because although a Database Express instance is for only one database, every database can have its own.

10.      C. There are defaults for everything, including the SYSAUX tablespace and datafile definitions.
    A, B, and D are incorrect. A is incorrect because the command will succeed. B and D are incorrect because these are not the way the defaults work.

11.      A, B, C, and D. One shell script is generated that calls a set of SQL scripts. There is also a password file to allow SYSDBA connections and a parameter file to start the instance.
    E is incorrect because response files are generated by the Oracle Universal Installer, not by the Configuration Assistant.