Oracle Database Configuration Assistant

The Oracle Database Configuration Assistant (DBCA) is a GUI used to configure and create databases. The DBCA automatically executes and creates the default database during Oracle server installation. Afterwards, The DBCA dialogue may be opened through the appropriate entry in the Windows Start menu (Start -> Programs -> Configuration and Migration Tools -> Oracle Database Configuration Assistant). The 'Create a Database' radio button should be checked and the 'Next' button clicked to create a new database or template. This option allows the user to create a database or database template. A non-seed template does not contain datafiles; this means that it is just a prototype that contains a generic database structure.

Fig 3a: DBCA Welcome Screen

 Log files and control files are automatically created by DBCA when a regular database is created with data files from a seed template. However, the user may add new control files and log groups or remove existing files. The user may also specify the location and names of the data files. The user is not allowed to add or remove datafiles, tablespaces, or rollback segments during creation. Oracle manages these automatically. These parameters may be altered later using SQL plus commands or by other means.

A typical database is a good choice for beginners; Oracle creates a database that uses one of many available templates and creates a database with minimal user input. Use the template 'General Purpose Database with datafiles' (ss3c) option to create our database. Just so you know, the 'Create Database' option may be used to create a custom database. Custom database creation allows advanced users and administrators to finetune creation parameters that have to do with tablespace and extent sizing, control and redo settings, database memory parameters, archive log formats and locations, trace file locations, character sets, and more.

Fig 3b: Change Default Selection to 'General Purpose'

 As you may remember from the installation, a global database name and a unique SID should be specified for each new database. We will use 'sampledb' for the name and SID of the sample database that we will create.

The next step involves selecting additional features for the databases. Just stick with the standard features (Oracle JVM, Oracle Text, Oracle interMedia , XDB Protocol) and click 'Next'.

The next step deals with parameters and definitions. Select the 'dedicated Server' mode. Oracle should set most of the initialization parameters for you as we have done with the typical database. However, do click on the File Locations tab and select a location for the datafiles.

Fig 3c: Other Options

The final step is to choose the 'Create Database Now' action rather than the 'Save the description as a database template' or 'Generate database creation scripts' options as we want our sampledb database right away. The scripts generated from the third option may be used to create the database at a later time without ever using the DBCA. The second option simply creates a database template that may be used over and over again. A database with the parameters we specified in the last few steps will appear as a choice of template in the template screen if the second option is picked.

A Note on Database templates

You may wonder what the database templates that come with the server installation have to offer. An Online Transaction Processing (OLTP) database is typically capable of processing many thousands of transactions (read, write, update, delete) from many concurrent users every day. The performance of an OLTP database is measured in terms of throughput of transactions and availability of data. A good example of an OLTP database is a bank's database. A Data Warehouse handles a wide variety of read-only queries. The queries may be simple and fetch just a few records or may consist of complex sets of commands that take a long time to execute. Response time is an indicator of a Data Warehouse's efficiency. An example of a Data Warehouse is an online yellow pages or phone book. A Multipurpose database supports both OLTP and Data Warehousing environments. This corresponds to the default database that we just created.

Other Uses of the DBCA

The DBCA may be used to Configure Database Options. Advanced Oracle options that have not been configured for use with a database may be configured using this option. For example, features such as Oracle Spatial, Oracle Ultra Search, and Oracle Label Security that we did not initially specify may be added later through the 'Configure Database Options' menu item. The DBCA may also be used to delete a database and its instance, control file(s), redo log files, and datafiles. Server parameter files (SPFILE) and initialization parameter files used by the database are also deleted.

Fig 3d: DBCA - Other Uses

 A DBCA template is an XML file that contains all the information required to create a database. DBCA may be used to create new databases modeled after a template. Such databases may be created with minimal user input or configuration OR cloned after existing databases. The information within templates include database options, initialization parameters, storage attributes for datafiles, tablespaces, control files and redo logs.

Templates are very useful - they save time by automating the configuration process and aid database copying. Templates may be used to clone existing databases - a copy of a database may be saved to a new location. Using templates to accomplish cloning is a lot faster than creating new databases and files. Templates are usually saved in the directory $ORACLE_HOME\assistants\dbca\templates (where $ORACLE_HOME corresponds to the directory (such as D:\Oracle\Ora92 and so on) where the Oracle application is installed. Also, templates are easy to modify and changing database parameters becomes correspondingly easy. Templates may also be ported from one computer to another by simply copying the template file.