Subterfuge: 02 - Oracle Database Creation

Before you can create a new database, the following prerequisites must be met:

The Oracle software must be installed: This includes setting various environment variables unique to your operating system and establishing the directory structure for software and database files.
You must have the operating system privileges associated with a fully operational database administrator. Your Oracle username should be included in the DBA group by the system administrator, if you are working on a UNIX or a Linux server.

You can create an Oracle Database in two ways:

Use the CREATE DATABASE statement.
Use the Database Configuration Assistant (DBCA).

2.0 Planning the Database

The database must be created with proper planning and research. Below is the list of some recommended actions:

Plan the Logical Storage Structure: The number of tablespces, tables and indexes to use and estimate the amount of space they will require.
Plan Physical Storage Structure: The underlying operating system files your database will comprise. Proper distribution of files can improve database performance dramatically by distributing the I/O during file access. You can distribute I/O in several ways when you install Oracle software and create your database. For example, you can place redo log files on separate disks or use striping.
Use Oracle Managed Files: Consider using Oracle-managed files and Automatic Storage Management to create and manage the operating system files that make up your database storage.
Use Server Parameter File: A server parameter file lets you store and manage your initialization parameters persistently in a server-side disk file.
Use Time Zones: Consider what time zones your database must support.
Block Size: Select the standard database block size. This is specified at database creation by the DB_BLOCK_SIZE initialization parameter and cannot be changed after the database is created.

2.1 Using the CREATE DATABASE Statement

Decide on a directory structure for the database; it is recommended that you comply with Oracle's Flexible Architecture as shown below:

Decide on a unique Oracle system identifier (SID) for your instance and set the ORACLE_SID environment variable accordingly. This identifier is used to distinguish this instance from other Oracle Database instances that you may create later and run concurrently on your system.

Open your favorite text editor and type the following minimum initialization parameters.

Store your initialization parameter file with name initdb1.ora in the /u01/app/oracle/admin/db1/pfile

directory.

Start SQL*Plus and connect to your Oracle Database instance AS SYSDBA.

Create a Server Parameter File. The server parameter file enables you to change initialization parameters with database commands and persist the changes across a shutdown and startup.

Start an instance without mounting a database. Typically, you do this only during database creation or while performing maintenance on the database.

To create the new database, use the CREATE DATABASE statement. The following statement creates database db1, note we are using Oracle-Managed Files.

To make the database functional, you need to create additional user tablespace.

Run the scripts necessary to build the database. CATALOG.SQL creates the views of the data dictionary tables, the dynamic performance views, and public synonyms for many of the views. CATPROC.SQL Runs all scripts required for or used with PL/SQL.

The following graphics shows the database files.

2.2 Using the Database Configuration Assistant

The Database Configuration Assistant (DBCA) tool creates a database from Oracle-supplied templates. Templates contain settings optimized for workload. Choose the template suited to the type of workload your database will support. Alternatively, you can create your own database and templates.

Start DBCA on UNIX or Linux, or at the command-line prompt on the Windows operating system, enter the following command:

The dbca utility is typically located in the $ORACLE_HOME/bin directory.

Click Next to bypass with Welcome window.
Select Create Database from the wizard and click next.

The next screen shows pre-defined templates. If you are not sure which to choose, select the default General Purpose template. You can check the initialization parameters by clicking the Show Details button. Click next.

In the Global Database Name box, enter the database name in the form database_name.domain_name. In the SID box, enter the Oracle system identifier. The SID defaults to the database name and uniquely identifies the instance that runs the database. Click next.

Set up your database so it can be managed with Oracle Enterprise Manager.

Specify the passwords for the administrative accounts such as SYS and SYSTEM.

Select storage mechanism do you want your database to use.

File System option creates database files that are managed by your operating system's file system. Automatic Storage Management enables you to define a pool of storage (called a disk group) in which

Oracle automatically manages database file placement and naming. Raw Devices option enables you to manage your storage devices outside of the operating system file system by providing Oracle Database with unformatted physical disk space called raw devices.

If you are not certain of which option to use, choose the default selection of File System.

Specify file locations for the database files. For now select the Use Database File Location from Template, we will change it at the end according to our requirements.

Specify Flash Recovery Area option to specify a backup and recovery area and specify its directory location and size. Enable Archiving option to enable archiving of database redo logs, which can be used to recover a database.

Load database with data. Check Sample Schemas to include the Sample Schemas tablespace in your database. In the Custom Scripts tab, you can specify one or more SQL scripts to run after your database is created. Scripts are useful for performing post-installation tasks, such as loading custom schemas.

The initialization parameters window is used to set the initialization parameters that control how the database manages its memory usage.

Sizing tab specify the smallest block size and the maximum number of operating system user processes that can simultaneously connect to the database. Character Sets tab define the character sets used by your database. Character sets are the encoding schemes used to display characters on your computer screen. National character set is an alternative character set that enables you to store Unicode characters in a database that does not have a Unicode database character set. Connection Mode can be shared or dedicated.

Next a navigation tree displays the storage structure of your database (control files, datafiles, redo log groups, and so forth). Here we will change the files location as shown below: