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_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 22.214.171.124.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
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
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.