2.2. Using the Oracle Database Configuration Assistant

The Oracle Database Configuration Assistant (DBCA) is a Java-based tool used to create Oracle databases. If you've been a DBA for a few years, you probably remember the days of writing and maintaining scripts to create databases. Although it is still possible to manually create a database, the DBCA provides a flexible and robust environment in which you can not only create databases but also generate templates containing the definitions of the databases created. This provides you the ease of using a GUI-based interface with the flexibility of Oracle-generated XML-based templates that you can use to maintain a library of database definitions.

You can also use the DBCA to add options to an existing database or to remove a database. We recommend using the DBCA facility to create your databases, even if you are a die-hard command-line DBA.

You can use the DBCA to create a database when the Oracle software is installed, or you can invoke the DBCA later to manually create a database. In the following sections, we will show you the steps necessary to create an Oracle database using the DBCA tool.

2.2.1. Creating an Oracle Database Using the DBCA

You can invoke the DBCA from a command line in the Unix environment or as an application choice in a Windows 2000 environment. If you are using the Windows 2000 environment, choose Start Programs Oracle Oracle Home Configuration And Migration Tools Database Configuration Assistant.

If you are in a Unix environment or would prefer to work from the command line in Windows, type dbca from the $ORACLE_HOME/bin location.

After you open the DBCA, you should see the Welcome screen, as shown in Figure 2.5.

Click Next to open the Operations screen, as shown in Figure 2.6. You can create a database, configure database options, delete a database, or manage templates.

Table 2.1 lists and describes the DBCA database management options.

Figure 2.5. The DBCA Welcome screen

Figure 2.6. The DBCA Operations screen

Click Next to open the Database Templates screen.

In the next two sections, we'll explore the various kinds of database templates and how to assign your database a name.

2.2.1.1. Database Templates

The DBCA comes with several preconfigured database templates. These XML-based documents contain the information necessary to create the Oracle database. You can choose one of these predefined templates, or you can build a custom database definition. The predefined database templates are Data Warehouse, General Purpose, and Transaction Processing (see Figure 2.7). These templates were designed to create databases that are optimized for a particular type of workload.

To display the configuration definitions for these preconfigured databases, click Show Details. Table 2.2 displays information about what is contained in template definition. Each section of the page gives further information about the template. For example, under the Common Options section, you will see a list of each of the database options that gets installed for the template definition you have chosen.

After you have chosen the appropriate template to use, click Next. You will then be presented with the Database Identification Screen.

2.2.1.2. Database Identification

The Database Identification screen (see Figure 2.8) allows you to enter the global database name and Oracle system identification name, commonly referred to as the Oracle SID.

The global database name is the fully qualified name of the database in the enterprise. It is composed of a database name and a database domain and takes the format database_name.database_domain, for example, marketing.us.aceinc.com.

In this example, the first part of the global database name, marketing, is the name of your database. The second part of the global database name—in this example, us.aceinc.com—is the domain. Normally, the database domain is the same as the network domain within the enterprise. A global database name must be unique within a given network domain.

Figure 2.7. The DBCA Database Templates screen

Figure 2.8. The DBCA Database Identification screen

The Oracle system identification (SID) name is the name of the instance associated with the database. The Oracle SID was of greater significance in earlier releases of Oracle, but is still required when creating a database in Oracle 10g. The Oracle SID can be a maximum of eight characters and must be unique on the server. For example, you cannot have two Oracle SIDs called PROD on a single server.

2.2.1.3. Management Options

After you choose the database name, you can configure Enterprise Manager to monitor and manage your database using the DBCA Management Options screen (see Figure 2.9).

You can choose from two options: You can centrally manage all your databases from a single management console if the Management Agent is installed on the database server, or you can manage each database individually.

If the Oracle Management Agent is installed, the DBCA detects its presence and lists the name of the agent service. You can select this name if you want this existing agent to manage this database. Your new database becomes one of the managed targets for the existing agent.

If you don't have an agent installed or are not doing centralized database management, you can still use Enterprise Manager to monitor and maintain the database. Choose Use Database Control For Database Management if you want to install EM installed and configure it locally.

Figure 2.9. The DBCA Management Options screen

From the Management Options screen, you can also configure e-mail notifications from Enterprise Manager. E-mail notifications are generated when certain database thresholds are reached, for example, the maximum number of database sessions. After installing Enterprise Manager, you can configure notification of these database thresholds. To get these e-mail notifications, you'll need the name of your SMTP mail server and the e-mail address to which you want e-mail notifications sent.

Finally, using the Management Options screen, you can configure backups of your database. If you select Enable Daily Backup, Enterprise Manager backs up your database based on the start time you enter. The database is backed up to a designated area on your system that is specified later in the configuration process. You have to supply an operating system username and password that Enterprise Manager will log in as to perform the backup. This user should have the proper write authorization to the area of the disk where you want the backup stored.

2.2.1.4. Database Credentials

You use the Database Credentials screen (see Figure 2.10) to configure passwords for the various user accounts that are set up automatically when the database is configured. You can select the same password for all the critical accounts, or you can elect to have a different password for each of the preconfigured accounts. How you elect to set your passwords may depend on the policies of your particular organization. Typically, the same critical passwords are set for these accounts, and the accounts that you won't need to access are selectively locked.

Figure 2.10. The DBCA Database Credentials screen

Four accounts are preconfigured when you set up your database:

SYS The SYS user owns all the internal Oracle tables that constitute the data dictionary. Normally, you should not perform any actions as the SYS user and should ensure that this account is locked down. Also, don't manually modify the underlying objects owned by the SYS user.

SYSTEM SYSTEM is an additional support user that contains additional administrative tables and views. This account should also be locked down to prevent unauthorized use of it.

DBSNMP DBSNMP is a login used by the Enterprise Manager facility to monitor and gather performance statistics about the database.

SYSMAN SYSMAN is the equivalent of the SYS user for the Enterprise Manager facility. This Enterprise Manager administrator can create and modify other Enterprise Manager administrator accounts, as well as administer the database instance itself.

Once you have completed the Database Credentials page, click Next. You will now be presented the Storage Options screen.

2.2.1.5. Storage Options

The Storage Options screen (see Figure 2.11) is used to define how you want to configure the disk storage areas used by the database. You have three choices:

• File System

• Automatic Storage Management (ASM)

• Raw Devices

Let's take a look at these options in more detail.

Figure 2.11. The DBCA Storage Options screen

2.2.1.5.1. File System Storage

File system storage is the most common type of storage configuration for pre–Oracle 10g databases. This type of storage definition relies on the underlying operating system to maintain and manage the actual files that you as the DBA define. When you choose this option, the DBCA suggests a set of datafile names and directory locations for those files. You can modify this information at a later step in the database creation process.

The DBCA uses the Optimal Flexible Architecture (OFA) directory design for laying out the suggested file locations. The OFA is an Oracle-recommended method for designing a flexible directory structure and naming convention for your Oracle database files.

2.2.1.5.2. ASM Storage

Automated Storage Management (ASM) is a new type of storage mechanism available in Oracle 10g. ASM is designed to relieve the burden of disk and storage management and relies on Oracle to maintain your database storage. Instead of your managing many individual database files, ASM allows you to define disk groups for file management.

Using disk groups, you can define one or more groups of disks as a logical unit that Oracle views as a single unit of storage. This concept is similar in nature to the way that some operating systems, including various flavors of Unix, define volume groups.

Oracle manages the storage definitions of the database within a second database used exclusively by ASM to keep track of the disk group allocations. When you create a database and select the ASM option in the Storage Options screen, a series of screens guides you through the process of defining the secondary ASM database instance.

2.2.1.5.3. Raw Devices

You can also select a raw device as your storage definition. Raw devices are disks that are not managed by the underlying operating system. Instead of the underlying operating system controlling disk reading and writing activities, Oracle performs the actions directly to the underlying hardware without handing the responsibilities off to the operating system.

Typically, the systems administrator predefines the raw disk partitions that will constitute the specific raw devices. Then, you as the DBA map the raw devices to specific datafiles and redo log files.

2.2.1.6. Database File Locations

After you define the type of storage that you want to use for your database, you define where you want to put the files that will constitute the database (see Figure 2.12). Depending on the type of storage option you choose, you may have more or fewer location options available. For example, if you are using raw devices, you or your systems administrator created raw device definitions before creating the database. You use these raw devices as the locations for you database files.

• Use Database File Locations From Template

• Use Common Location For All Database Files

• Use Oracle-Managed Files

Let's take a look at these options.

2.2.1.6.1. Use Database File Locations from Template

If you chose one of the predefined database templates or a previously defined database template to use for this database, Oracle uses the previously defined locations from the template as the basis for the database file locations. You still have the opportunity later in the database definition process to review and modify the filenames and locations even if you choose this option.

Figure 2.12. The DBCA Database File Locations screen

2.2.1.6.2. Use Common Location for all Database Files

If you choose this option, you can specify a new directory for all your database files. Again, even if you choose this option, you can change the filenames and locations later in the database definition process.

2.2.1.6.3. Use Oracle-Managed Files

If you chose Automated Storage Management (ASM) on the Storage Options screen, you choose Use Oracle-Managed Files and select the appropriate directory path to the area of the disk that will be used to store the database. This area of disk will be fully managed by a secondary ASM Oracle database. If you choose this option, you will not be able to review or make changes later in the database definition process.

Once you have chosen the appropriate storage option for your database, click Next. The next screen you are presented with is the Recovery Configuration screen.

2.2.1.7. Recovery Configuration

You use the Recovery Configuration screen, as shown in Figure 2.13, to set up your database backup and recovery strategy. Oracle provides robust mechanisms for full point of failure recovery. As a DBA, it is critical to understand the backup and recovery requirements of your application so that you can choose the appropriate backup strategy.

You can configure several options on this screen, the Flash Recovery options—new in Oracle 10g—and your archive log options. Let's take a look at each of these options.

Figure 2.13. The DBCA Recovery Configuration screen

2.2.1.7.1. Flash Recovery

Oracle Flash Recovery is a new option available in Oracle 10g. It is the foundation of the new Automated Disk-Based recovery feature. Flash Recovery is designed to simplify your life in terms of Oracle backups by providing a centralized location to maintain and manage all the files related to database backups.

The Flash Recovery area is an area of the disk dedicated to the storage and management of files needed for recovering an Oracle database. This area is completely separate from the other components of the Oracle database such as the datafiles, redo logs, and control files.

Oracle uses the Flash Recovery Area to store and manage the archive logs. Enterprise Manager can store its backups in the Flash Recovery area and uses it when restoring files during media recovery. The Oracle Recovery Manager (RMAN) uses the Flash Recovery area and ensures that the database is recoverable based on the files being stored in the Flash Recovery area. All files necessary to recover the database following a media failure are part of the Flash Recovery area.

You can specify the directory location and the size of the disk area that you want to dedicate to the Flash Recovery area. The default location of the directory is ORACLE_BASEflash_recovery_area. For example, if your ORACLE_BASE location in a Unix environment is /u01/app/oracle/oradata/ORCL, your Flash Recovery area is a directory called /u01/app/oracle/oradata/ORCL/flash_recovery_area.

You can click File Location Variables on the Recovery Configuration screen to display a summary of the Oracle file location parameters, including the current setting of the ORACLE_BASE parameter (see Figure 2.14).

Figure 2.14. The DBCA File Location Variables screen

The size of the Flash Recovery area defaults to 2048MB and can be set larger or smaller by changing the Flash Recovery Size setting.

2.2.1.7.2. Enabling Archive Logging

From the Recovery Configuration screen, you also have the ability to enable the Oracle archive logging feature. Archive logging is the mechanism Oracle uses to enable you to perform pointof-failure recovery of a database. To enable Archive Logging, mark the Enable Archiving check box. Once you do so, the button Edit Archive Mode Parameters will be enabled. If you click this button, you are presented with a screen that enables you to set the various parameters that are used to configure archive logging (see Figure 2.15).

After completing the Recovery Configuration screen, click Next. You will then be presented with the Database Content screen.

2.2.1.8. Database Content

If you chose to create a custom database in the Database Templates screen, you will be presented with the Database Content screen as shown in Figure 2.16.

Figure 2.15. The DBCA Edit Archive Mode Parameters screen

Figure 2.16. The DBCA Database Content screen

You use the options on this screen to specify which Oracle database components you want to install. Table 2.3 lists and describes the components that can be included and configured automatically by the DBCA.

Click the Standard Database Components button to display any additional standard features that Oracle will automatically configure for you and recommend as part of a standard database installation (see Figure 2.17). These features are the Oracle JVM, Oracle Intermedia, and Oracle XML DB.

2.2.1.9. Sample Schemas and Custom Scripts

The DBCA also lets you install examples of actual working databases. Oracle provides a set of example schemas and applications that use these schemas. You can install these sample schemas now or later by running a series of SQL scripts.

These sample schemas include the following:

• Human Resources

• Order Entry

• Product Media

• Sales History

• Queued Shipping

To select these schemas, click the Sample Schemas tab in the DBCA Database Content screen, mark the Sample Schemas check box, and click Next (see Figure 2.18).

Figure 2.17. The DBCA Standard Database Components box

Figure 2.18. DBCA Database Content Sample Schemas Tab

These schemas are designed to provide you with working examples of how to use and implement a variety of features within Oracle. For example, the Product Media schema shows how to use the Oracle Intermedia option, which is used to manage binary large objects (BLOBs) such as images and sound clips.

If you choose to create the sample schemas, Oracle creates a tablespace called EXAMPLES and stores all the necessary tables within that tablespace. Be aware that this adds about 130MB to your database definition.

You can also run custom scripts as part of the database creation process. Click the Custom Scripts tab in the Database Content screen to enter the names and locations of the custom scripts that you want to run at database creation (see Figure 2.19).

For example, you might want the DBCA to automatically create the schema and define the tables that you will use for this database. You can create a script that performs all the necessary work and have the DBCA run the script as part of the database creation process. The custom scripts are run using the command-line utility SQL*Plus, so you will have to define a user ID and password within the body of the script. For example, your script might contain the line:

connect some_userid/some_password

This line directs Oracle to connect to the current Oracle database, which is determined by your ORACLE_SID environment variable using the supplied user ID and password.

After completing the Database Content screen, click Next. You will then be presented with the Initialization Parameters screen.

Figure 2.19. The DBCA Database Content Custom Scripts tab

2.2.1.10. Initialization Parameters

You use the Initialization Parameters screen to define the various initialization parameter settings used to configure size and setup characteristics of the Oracle instance. The following four tabs are categorized according to the parameters used to manage the Oracle instance:

• Memory

• Sizing

• Character Sets

• Connection Mode

Let's take a look at each of these tabs and what settings you can manage under each tab.

2.2.1.10.1. The Memory Tab

You use the options on the Memory tab to control the size of the database parameters that configure the overall memory footprint of the Oracle instance (see Figure 2.20). There are two general approaches to managing the memory database parameters: Oracle can set and manage most of the parameters for you, or you can customize each of the initialization parameters for your specific database.

If you choose the Typical setting, Oracle allocates memory to the various components within the Oracle System Global Area (SGA) and Process Global Area (PGA). This memory allocation is automatic and is a percentage of the overall physical memory available on the server. The default is 40 percent of total memory available, but you can change this setting. If you choose this setting, click the Show Memory Distribution button to see how Oracle will allocate the memory between the SGA and the PGA.

Figure 2.20. The Memory tab in the Initialization Parameters screen

If you choose the Custom option, you have full control over how much each of the specific areas of the SGA will take. The main areas that you will configure are the Shared Pool, Buffer Cache, Java Pool, Large Pool, and PGA size. Each of the settings maps to a specific Oracle parameter.

2.2.1.10.2. The Sizing Tab

You use the options on the Sizing tab (see Figure 2.21) to configure the block size of your database and the number of processes that can connect to this database. The Block Size setting corresponds to the smallest unit of storage within the Oracle database. All storage of database objects (tables, indexes, and so on) are governed by the block size. The block size defaults to 8KB, but you can modify it. Once the database is created, you cannot modify this setting.

The maximum and minimum size of an Oracle block depends on the operating system. Generally, 8KB is sufficient for most transaction-oriented applications, and larger block sizes such as 16KB and higher are used in data warehouse–type applications.

The Processes setting specifies the maximum number of simultaneous operating system processes that can be connected to this Oracle database. You must include at least six processes for each of the Oracle background processes. You can increase this number on this screen.

Figure 2.21. The Sizing tab in the Initialization Parameters screen

2.2.1.10.3. The Character Sets Tab

You use the options on the Character Sets tab to configure the character sets you will use within your database (see Figure 2.22). You will determine the database character set, the national character set, the default language, and the default date format.

Specifying a database character set defines the type of encoding scheme that Oracle uses to determine how characters are displayed and stored within your Oracle environment. The character set you choose determines the languages that can be represented in your environment. It also controls other nuances such as how your database interacts with your operating system and how much storage is required for your data. The default character set is based on the language setting of the operating system.

Specifying a national character set defines how your database represents unicode characters in a database that does not use a Unicode-enabled character set.

You use the Default Language setting to manage certain aspects of how your database represents information pertaining to different locales. For example, this setting determines how your database displays time and monetary values.

You use the Default Date setting to specify how Oracle displays dates by default. For example, the AMERICA setting shows dates in the DD-MON-YYYY format by default.

2.2.1.10.4. The Connection Mode Tab

You use the options on the Connection Mode tab to specify the type of connections to use for this database (see Figure 2.23). You can choose dedicated or shared server mode. The default connection mode is dedicated server.

Figure 2.22. The Character Sets tab in the Initialization Parameters screen

Figure 2.23. The Connection Mode tab on the Initialization Parameters screen

After completing the Initialization Parameters screen, click Next. You will then be presented with the Database Storage screen.

2.2.1.11. Database Storage

The Database Storage screen provides you with the opportunity to review and change the locations of the actual objects that compose the Oracle database, namely, the datafiles, control files, and redo logs (see Figure 2.24).

This screen displays a tree structure in the left pane. You can click the various elements within the tree to expand and display the details of each component. Selecting an element displays details about the element in the pane on the right. For example, clicking Controlfile displays a summary of the control filenames and locations in the right pane. You can make manual changes to the names and locations of the control files in the right pane.

If you are creating a custom database definition that does not use a template, you can add new objects to a particular group. For example, clicking the Redo Logs folder and then clicking Create lets you add redo log groups to your database definition. If you selected a database template that included datafile definitions, you cannot add or remove datafiles, tablespaces, or rollback segments, but you can modify the location of the datafiles, control files, and redo log groups. As with many of the other screens in the DBCA, you can click the File Location Variables button to display the settings for the various Oracle file location parameters, such as the ORACLE_BASE and ORACLE_HOME settings.

After completing the Database Storage screen, click Next to create your database.

Figure 2.24. The DBCA Database Storage screen

2.2.1.12. Creation Options

The Creation Options screen (see Figure 2.25) provides you with two options:

Create Database Use this option to have the DBCA immediately create your database.

Save as a Database Template You actually have two choices with this option. You can elect to save your database definition to a template and create the database at a later time or you can have the DBCA create the template and immediately create your database.

If you elect to create your database immediately, the DBCA uses the information you have provided in the previous screens to create all the necessary components of your database, populate the database with sample schemas if they were chosen, start your database, and allow you to configure the network components of your database such as the Oracle Net listener.

If you elect to save your database to a template definition, this definition is added to the list of database definitions that you can select on subsequent executions of the DBCA.

You can also let the DBCA create a set of scripts that you can run manually to create the database.

Figure 2.25. The DBCA Creation Options screen

You can choose a location to store your scripts and then you can run the scripts manually to generate your database. If you choose a manual creation process, you will also have to manually configure several items, including the Oracle Internet Directory Service if you elect to use centralized naming and your listener. Also, depending on your operating system, you will have to configure or modify the ORATAB file on Unix or create a service in the Windows environment.

If you elect to have the DBCA create the database immediately, click Finish. You will see the Confirmation screen that summarizes the database configuration options that you chose for this database (see Figure 2.26). You can scroll down the window to examine the following:

• Options to install into the database

• The initialization parameter settings

• Character set settings

• Datafilenames and locations

• Redo log filenames and locations

• Control filenames and locations

You can save this summary screen as an HTML file for later reference.

Figure 2.26. The DBCA Confirmation screen

Once you start the database creation process, Oracle creates the database as you have specified. It starts the instance, creates all the necessary database components, and configures all the database options that you specified. Depending on how large a database you create and how many options you are installing, the process can take anywhere from several minutes to an hour or more.

When the creation process is complete, connect to the database with one of the tools such as SQL*Plus, iSQL*Plus, or Enterprise Manager to ensure that all the database options and components were installed properly.

2.2.2. Configuring an Oracle Database Using DBCA

The DBCA lets you change various aspects of an existing database. To change the database configuration, select Configure Database Options in the DBCA Operations screen (shown earlier in this chapter in Figure 2.6). If the database is not started, the DBCA starts it for you automatically. You must connect to the database as a user that has DBA authority.

Once you have selected and started the database, you can add options that may not have been previously included in the database. You can also change the server type.

2.2.3. Deleting an Oracle Database Using DBCA

You can also delete a database using the DBCA. In the Operations screen (shown earlier in this chapter in Figure 2.6), choose Delete A Database and click Next to open the Database screen, as shown in Figure 2.27. The DBCA lists all the databases available for deletion.

If you click Finish, the DBCA removes all files on the disk associated with this database you have chosen. If you are using Windows, the DBCA also removes the service associated with the database.

Figure 2.27. The DBCA Database screen

/u01/app/oracle>sqlplus sys/oracle as sysdba

Startup mount;

Drop database;

2.2.4. Managing Database Templates Using DBCA

As we explained earlier in this chapter, the DBCA can store and use XML-based templates to create your Oracle database. As the DBA, you can manage these database definition templates. Saving a definition of your database in a template format makes it easier to perform various tasks. For example, you can copy a preexisting template to modify new database definitions. The template definition is normally stored in the $ORACLE_HOME/assistants/dbca/templates directory on Unix and in the %ORACLE_HOME%assistantsdbca emplates directory on Windows systems.

The DBCA can use two types of templates: seed and nonseed. Seed templates are template definitions that contain database definition information and the actual datafiles and redo log files. The advantage of a seed template is that the DBCA makes a copy of the datafiles and redo logs included in the definition file. These prebuilt datafiles include all schema information, which makes for a faster database creation process. The seed templates carry a .dbc extension. The associated predefined redo logs and datafiles are stored as files having a .djf extension. When you use a seed template, you can change the database name, the datafile locations, the number of control files and redo log groups, and the initialization parameters.

Nonseed templates contain custom-defined database definitions. Unlike seed templates, they do not come with preconfigured datafiles and redo logs. Nonseed templates carry a .dbt extension.

Now we'll explore the various options we have to manage templates.

2.2.4.1. Creating Template Definitions Using the DBCA

You can use the DBCA interface to create new database templates. When you connect to the DBCA, select Manage Templates in the Operations screen (see Figure 2.6, shown earlier in this chapter) and click Next to open the Template Management screen (Figure 2.28).

You have three choices for creating templates. Table 2.4 summarizes your options.

Figure 2.28. The DBCA Template Management Screen

Depending on the option selected, you are presented with a set of forms to save your template definition. If you elected to create a template from an existing database, you will have to connect to the database so that the DBCA can obtain information about the database. You must connect to the database as a user that has DBA credentials to perform this task.

If you are copying a definition from an existing template, you can configure the template by following a series of screens that are similar to those used to create a database. These screens allow you to configure the various aspects of the template, including initialization parameters and datafile and redo log locations.

2.2.4.2. Deleting Template Definitions Using the DBCA

You can also delete an existing template definition. In the Operations screen (see Figure 2.6, shown earlier in this chapter), click Manage Templates. You will be presented with the Template Management screen (see Figure 2.28, shown earlier in this chapter). Select the option Delete A Database Template. You can then choose the template to delete. When you remove the template, the DBCA removes the XML file from the system.