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.

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 128MB of memory, plus 8GB of disk space for a fairly complete installation of Oracle Enterprise Edition. Oracle strongly recommends at least 256MB 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.4GB of disk space.

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 - OraHome 92 > 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 leaves the default value selected in the Database Templates screen, which appears next, 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 wh92. 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, wh92.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.

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 the Dedicated server mode.

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 70% down to 30%, however, since there is already another instance on this server.

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

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 checked and clicks Finish to start the process of creating the database.

One more summary 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.

At the end of the database creation operation, Janice is prompted to enter the passwords for the SYS and SYSTEM accounts. SYS is the user who owns all of the internal database tables, and SYSTEM is the account that the DBA uses to create other DBA accounts in addition to owning other tables used by various Oracle tools.

Janice clicks Exit when she is done setting the passwords, and the database creation process completes. 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 wh92 database instances.