Quick Manual Database Creation

Using Oracle Managed Files you can quickly create a database manually with very little upfront effort. Oracle Managed Files greatly simplifies the database creation process because all that is needed is the location in which to write the database files.

Three initialization parameters are used to enable Oracle Managed Files:

DB_CREATE_FILE_DEST– The location where the database is to create data, temp and undo files. This location is used for default location for redo logs and control files if DB_CREATE_ONLINE_LOG_DEST_n are not specified.

DB_CREATE_ONLINE_LOG_DEST_n– The location where the database creates control files and online redo logs. If not set the control files and on line redo logs will be created in the location provided in DB_CREATE_FILE_DEST.

DB_RECOVERY_FILE_DEST– The location of the flashback recovery area, multiplexed redo log files and control files, archive logs and backups.

For more information on Oracle Managed Files see the Oracle Administrators guide 17 Using Oracle Managed Files.

This document will detail the steps to manually create the database MANDB using Oracle Managed Files. This example was made using Oracle 11gR2 on Oracle Enterprise Linux 5.5. The process can be used with Oracle 9i and 10g as well.

Create a basic init.ora parameter file

For the init.ora only a few parameters are needed for 9i and 10g: DB_NAME, DB_CREATE_FILE_DEST. For 11g one additional parameter, DIAGNOSTIC_DEST is required. Create the parameter file in the ORACLE_HOME/dbs directory.

[oracle@oelinux dbs]$ cat initMANDB.ora 
DB_NAME='MANDB'
DB_CREATE_FILE_DEST='/u01/app/oracle/oradata'
DIAGNOSTIC_DEST='/u01/app/oracle'
[oracle@oelinux dbs]$ 

Create the database

With the initialization parameter file created it is now possible to connect to and startup the instance.

[oracle@oelinux dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 28 10:40:29 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL>

At this point the instance needs to be started but we do not want to mount the database. Issue the STARTUP NOMOUNT command.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  146472960 bytes
Fixed Size                  1335080 bytes
Variable Size              92274904 bytes
Database Buffers           50331648 bytes
Redo Buffers                2531328 bytes
SQL>

Prior to creating the database create a spfile from the initMAND.ora parameter file. This is done now so the value set for CONTROL_FILES parameter during the database creation process will be saved.

SQL> create spfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileMANDB.ora'
  2  from 
  3  pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initMANDB.ora';

File created.

SQL> 

Now we can create the database using the CREATE DATABASE command.

SQL> create database MANDB;

Database created.

SQL>   

Since Oracle Managed Files were used the following directory structure was created.

[oracle@oelinux dbs]$ ls /u01/app/oracle/oradata/MANDB/
controlfile  datafile  onlinelog
[oracle@oelinux dbs]$

Once control file and two redo log groups each with one 100MB member were created along with the SYSTEM, SYSAUXand UNDOtablespaces. No temporary or user data tablespaces were created. Create a user data and temporary tablespace and set them as the defaults for the database. Since Oracle Managed Files are being used no file name is necessary for either.

SQL> create tablespace data datafile  size 100m;

Tablespace created.

SQL> create temporary tablespace temp tempfile  size 100m;

Tablespace created.

SQL> alter database default tablespace data;

Database altered.

SQL> alter database default temporary tablespace temp;

Database altered.

SQL>

The last thing to do to complete the creation of the database is to create the database catalog and procedures. These can be accomplished by executing ORACLE_HOME/rdbms/admin/catalog.sql and ORACLE_HOME/rdbms/admin/catprocsql.

SQL> @?/rdbms/admin/catalog.sql;

SQL> @?/rdbms/admin/catproc.sql;

Once the catalog scripts complete the databases is ready for use. Again keep in mind that only the defaults values for the database parameter are used in this database so you should not use this database for production until after properly sizing the database.