This Manual Database Creation Script Works for Me. It Can Work for you, Too !

Manual Database Creation - DBA's are still using this approach for creating databases. The reason is, you need not any GUI access like DBCA to create a database. In process of creating database manually Database Administrator has to execute few commands and database is ready to use. Though DBCA is an easy option to make database but mostly GUI of production environments not accessible. So, DBA has to rely on scripts for creating database manually.

 In this post, I will explain How I create a script for manual database creations and this can also works for you. Below are the main steps:


1. Specify an Instance Identifier (SID)
2. Ensure Required Environment Variables Are Set.
3. Choose a Database Administrator Authentication Method.
4. Create the Initialization Parameter File.
5. Connect to the Instance & Create a Server Parameter File.
6. Start the Instance.
7. Issue the CREATE DATABASE Statement.
8. Create Additional Tablespaces.
9. Run Scripts to Build Data Dictionary Views.
10. (Optional) Run Scripts to Install Additional Options.

In this post, I will use Unix as Operating system and Oracle 11g as database version for creating database manually, using All these options I am going to make a single instance database.


1. Specify an Instance Identifier (SID): SID is the name of the database, which we are going to make on database server. ORACLE_SID is used to distinguish this instance from other instances that you may create later and run concurrently on the same host computer. Below is how to set SID in unix env.

[oracle@database]$ export ORACLE_SID=newdb

Here, We are making a database by name "newdb". So, we have set ORACLE_SID as newdb.


2. Ensure Required Environment Variables Are Set: Make sure ORACLE_HOME variable has correct oracle Software path and PATH variable includes ORACLE_HOME/bin directory. To set Environment variables see How to set Oracle Environment Variable in Unix ? 


3. Choose a Database Administrator Authentication Method: There are two kind of authentication methods Password file and Operating system. We will use OS authentication method but will also create a password for future use. To use OS authentication, user manually creating database must be a member of dba group on the OS. We are using Oracle as user which is part of dba group. Here is the command to create password file.

syntax: ORAPWD FILE=filename [ENTRIES=numusers] [FORCE={Y|N}] [IGNORECASE={Y|N}]

[oracle@database]$ orapwd file=$ORACLE_HOME/dbs/orapwnewdb entries=20 
Enter password for SYS: 

[oracle@database sampleXMLs]$ ls $ORACLE_HOME/dbs/*newdb
/etc/oracle/oracle/dbs/orapwnewdb


To know more about How to create and maintain password file.


4.Create Initialization Parameter File: Each time when database starts, it read initialization parameter file for database parameter values. This file could be text file called as pfile or binary file called as spfile. Here, We will start with creating pfile and will create database with this file and later on. There are few mendatory parameters to start database which we will provided in this file. Before making pfile make sure you have required directories like /etc/oracle/admin/newdb/adump, /etc/oracle/fast_recovery_area/newdb and /etc/oracle/oradata/newdb.

db_name='newdb'
memory_target=1G
processes = 150
audit_file_dest='/etc/oracle/admin/newdb/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/etc/oracle/fast_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/etc/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = ('/etc/oracle/oradata/newdb/newdb_control1.ctl', '/etc/oracle/oradata/newdb/newdb_control2.ctl')
compatible ='11.2.0'

DBA has to change this file values according to their requirements like db_name, memory_target, audit_file_dest control_files location and compatible parameter etc. Make a new pfile by name 'initnewdb.ora' at $ORACLE_HOME/dbs and copy all these values in this file.


5. Connect to the Instance and Create Server Parameter File: Next step in configuring manual database is starting database idle instance. Database admin has to start the idle instance for creating spfile from pfile.

[oracle@database dbs]$  sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Thu Jul 25 22:23:11 2013
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
SQL> 

SQL> CONNECT / AS SYSDBA
Connected.

SQL> create spfile from pfile='$ORACLE_HOME/dbs/initnewdb.ora';
File created.
Since, Database Administrator has created spfile then he need not to specify parameter file name during database instance startup. Database will by default use spfile for strtup. See more operations on spfiles.


6. Start the Instance: Now DBA has to start database instance in nomount mode. Nomount mode is used for database creation or maintenance operations.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  263639040 bytes
Fixed Size                  1343548 bytes
Variable Size             192942020 bytes
Database Buffers           67108864 bytes
Redo Buffers                2244608 bytes

In next step, we will create the database using create database script.


7. Issue the CREATE DATABASE Statement: This is main command for database creations. In this command We have to provide values like database name, sys user password, system user password, Logfile path, datafiles path and all other values. You should modify this file according to your requirements. Now, Execute this command on sql prompt.

SQL> CREATE DATABASE newdb
   USER SYS IDENTIFIED BY sys
   USER SYSTEM IDENTIFIED BY sys
   LOGFILE GROUP 1 ('/etc/oracle/oradata/newdb/redo01a.log','/etc/oracle/oradata/newdb/redo01b.log') SIZE 100M BLOCKSIZE 512,
           GROUP 2 ('/etc/oracle/oradata/newdb/redo02a.log','/etc/oracle/oradata/newdb/redo02b.log') SIZE 100M BLOCKSIZE 512,
           GROUP 3 ('/etc/oracle/oradata/newdb/redo03a.log','/etc/oracle/oradata/newdb/redo03b.log') SIZE 100M BLOCKSIZE 512
   MAXLOGFILES 5
   MAXLOGMEMBERS 5
   MAXLOGHISTORY 1
   MAXDATAFILES 100
   CHARACTER SET US7ASCII
   NATIONAL CHARACTER SET AL16UTF16
   EXTENT MANAGEMENT LOCAL
   DATAFILE '/etc/oracle/oradata/newdb/system01.dbf' SIZE 100M REUSE
   SYSAUX DATAFILE '/etc/oracle/oradata/newdb/sysaux01.dbf' SIZE 100M REUSE
   DEFAULT TABLESPACE users
      DATAFILE '/etc/oracle/oradata/newdb/users01.dbf'
      SIZE 10M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
   DEFAULT TEMPORARY TABLESPACE tempts1
      TEMPFILE '/etc/oracle/oradata/newdb/temp01.dbf'
      SIZE 10M REUSE
   UNDO TABLESPACE undotbs1
      DATAFILE '/etc/oracle/oradata/newdb/undotbs01.dbf'
      SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;  

Database created.


 When I execute this command I face below error and database creation was aborted.

ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01501: CREATE DATABASE failed
ORA-01519: error while processing file '?/rdbms/admin/dtxnspc.bsq' near line 5
ORA-00604: error occurred at recursive SQL level 1
ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type
Process ID: 8309
Session ID: 1 Serial number: 5

This is because of undo tablespaces name is different in create database statement and initnewdb.ora file. Make sure undo tablesapces name is same in parameter file and create database command.

About script has created mandatory tablespaces for database creation, but to work on database DBA has to make some additional tablespaces.


8. Create Additional Tablespaces: You may create additional tablespaces as per your requirement. Below is the command for tablespace creation.

SQL> CREATE TABLESPACE apps_tbs LOGGING
     DATAFILE '/etc/oracle/oradata/newdb/apps01.dbf'
     SIZE 100M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED
     EXTENT MANAGEMENT LOCAL;  

Tablespace created.


9. Run Scripts to Build Data Dictionary Views: Run below necessary scripts  to build data dictionary views, synonyms, and PL/SQL packages, and to support proper functioning of SQL*Plus.

SQL> @?/rdbms/admin/catalog.sql
-------------------------------
-------------------------------
Grant succeeded.
PL/SQL procedure successfully completed.

SQL> @?/rdbms/admin/catproc.sql
-------------------------------
------------------------------
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATALOG    2013-07-25 23:19:56
 
SQL> @?/sqlplus/admin/pupbld.sql

SQL> CREATE PUBLIC SYNONYM PRODUCT_USER_PROFILE FOR SYSTEM.PRODUCT_PRIVS;
Synonym created.
SQL> 
SQL> -- End of pupbld.sql
 
Here, Manually Created database is ready to use, I would suggest to Restart the database and check alert log for any error messages and act accordingly. Once DBA has created Database Next step is to configure Tnsnames.ora and Listener.ora for database connection. Please see Quick Steps to Configure Tnsnames.ora and Listener.ora file. 

If you want to use GUI base tool DBCA to create database then Please see

Step by Step Process to Create Single Instance Database using DBCA.
Create Real Application Cluster (RAC) Database using DBCA

Please share your feedback about this article.

2 comments: