8.3. Creating an Oracle Database

Once the Oracle software is installed on a server, you can create one or more database instances using a single copy of the Oracle software. The Database Configuration Assistant (DBCA) is Oracle's GUI tool for creating, modifying, and deleting databases.

Database Configuration Assistant (DBCA)

A multiplatform GUI tool that allows a DBA to easily create, modify, and delete databases, as well as manage database templates.

8.3.1. Disk and Memory Requirements

While the software code is shared among instances, the instances themselves each must have a minimum amount of system memory and disk space for adequate performance.

For the Microsoft Windows platform, each Oracle instance requires at least 256MB of memory, plus 8GB of disk space for a fairly complete installation of Oracle Enterprise Edition. Oracle strongly recommends at least 512MB of memory. The amount of disk space needed for the datafiles depends on the application's data needs, but one of Oracle's starter databases uses approximately 1.5GB of disk space.

8.3.2. Using the Database Configuration Assistant

The DBA, Janice, has two big tasks ahead of her for the week. Now that the widget company is over a year old, the boss, King, wants to offload some of the analysis tasks to a second database to minimize the impact on the primary database. He suggests that this new database be designed for data warehouse use. Janice will use the Oracle DBCA to create a new instance to support the data warehousing effort.

To create a new database instance, Janice starts up DBCA by selecting Start All Programs Oracle - OraHome10g2 Configuration and Migration Tools Database Configuration Assistant. The Welcome screen is shown below.

Janice clicks Next. DBCA asks for the type of operation to perform. Janice selects the first option, Create A Database, and clicks Next.

Since the boss wants a database to be used as a data warehouse, she selects Data Warehouse in the Database Templates screen, which appears below, and clicks Next.

In the next step, Janice needs to label the instance. Janice gets the Global Database Name's suffix from the system administrator, but she specifies the SID as wh10g. The SID, or system identifier, is a unique name for the Oracle instance. This is the same as the connect descriptor that a database user uses when connecting to the database with SQL*Plus. When Janice types in the fully qualified name of the database, wh10g.widgetsRus.com, the SID is automatically extracted from the Global Database Name and placed in the SID text box.

SID

A system identifier, which is a unique name assigned to an Oracle instance. A user must supply a SID to connect to an Oracle instance.

On the next screen, Janice accepts the option to use Enterprise Manager Database Control to manage her database. She also has the option to send all e-mail alerts to her e-mail account, so she specifies the name of the company's e-mail server and her e-mail address.

In Step 5 of the DBCA, Janice specifies the same initial password for all of the privileged user accounts in the database and clicks Next.

In Step 6, Janice specifies how the database files will be stored. Since she does not have many databases to manage and does not have any Unix experts in-house to configure raw devices, she chooses the default, File System, to hold the database files.

The default database file locations from the Data Warehouse template are fine for Janice's needs, so she accepts the default in Step 7. If she wants to change the template defaults, she can click the File Location Variables button. In this case, the template specifies that the database files will be stored in the same directory structure that contains the Oracle software, F:Oracle10g2.

In Step 8, Janice decides that a Flash Recovery Area will help her manage backups and accepts the default. A Flash Recovery Area is a central location on disk used by Oracle for backup and recovery operations.

Flash Recovery Area

A central location on disk used by Oracle to contain files for backup and recovery operations.

In Step 9, Janice decides to install the sample schemas. They will help her test out the new features of Oracle 10g. However, once the new database is put into production, she will remove the sample schemas, because they could pose a security risk in addition to putting a drain on system performance if users are training with this database.

The next screen allows Janice to further refine the memory parameters that Oracle suggests in a data warehouse environment given the server resources, but she accepts the defaults for now. She will perform some advanced tuning once the data warehouse queries have been designed and tested. She does decrease the percentage of memory allocated for this instance from 40 percent down to 25 percent, however, since there is already another Oracle instance on this server.

On the same screen, Janice selects the Connection Mode tab. Oracle can accept connections in one of two modes: Dedicated or Shared. Dedicated mode gives the best response time for users who run queries constantly, and Shared mode works best for users who run infrequent queries on a server that may have limited memory resources. Only a handful of users will be using this data warehouse, so Janice selects Dedicated Server Mode.

After clicking Next, Janice has the option to tweak the datafile names and locations, but she once again accepts the defaults for all file locations and clicks Next.

The next screen gives Janice two options. She can either create the database immediately or save everything up to this point as a template. If Janice thought that she might create many databases with identical or very similar characteristics to this one, then she would save these settings as a template for future DBCA sessions. In this case, she decides that there will not be any other databases like this one, so she leaves the default Create Database option checked and clicks Finish to start the process of creating the database.

A Confirmation screen is displayed before the actual database creation begins. It allows a final review of the parameters, with the added option of saving the entire set of database characteristics as an HTML file for documentation purposes. Janice clicks OK to continue.

The DBCA provides the status and percentage complete while the database is being created.

Janice clicks Exit after she reviews the summary screen. The database is ready to use.

In the future, Janice can use OEM to manage both Oracle instances within the same Navigation pane. As shown here, Janice's new OEM session shows connections to both the or92 and wh10g database instances.