How to create a database from command line?

Using the following steps one can create database from command line.

Place the following pfile in $ORACLE_HOME/dbs directory and set ORACLE_SID=TEST

control_files=/u02/oradata/TEST/control1.ora,/u03/oradata/TEST/control2.ora,/u04/oradata/TEST/control3.ora
undo_management=auto
db_name=TEST
db_block_size= 8192
background_dump_dest=/u01/oradata/admin/TEST/bdump
core_dump_dest=/u01/oradata/admin/TEST/cdump
user_dump_dest=/u01/oradata/admin/TEST/udump

$ sqlplus /as sysdba

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 2019320 bytes
Variable Size 113246216 bytes
Database Buffers 50331648 bytes
Redo Buffers 2174976 bytes

— SQL to create database, creates 3 groups of 2 redo log files per group
SQL> CREATE DATABASE TEST
LOGFILE GROUP 1 (‘/u01/oradata/TEST/redo1a.log’, ‘/u01/oradata/TEST/redo1b.log’) SIZE 10M,
GROUP 2 (‘/u01/oradata/TEST/redo2a.log’, ‘/u01/oradata/TEST/redo2b.log’) SIZE 10M,
GROUP 3 (‘/u01/oradata/TEST/redo3a.log’, ‘/u01/oradata/TEST/redo3b.log’) SIZe 10M
DATAFILE ‘/u01/oradata/TEST/system_01.dbf’
SIZE 50M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE ‘/u01/oradata/TEST/sysaux_01.dbf’
SIZE 50M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
UNDO TABLESPACE UNDO
DATAFILE ‘/u01/oradata/TEST/undo_01.dbf’ SIZE 50M
DEFAULT TEMPORARY TABLESPACE TEMP
TEMPFILE ‘/u01/oradata/TEST/temp_01.dbf’ SIZE 10M
CHARACTER SET WE8ISO8859P1
NATIONAL CHARACTER SET UTF8;

Database created.

— creates data dictionary
SQL> @?/rdbms/admin/catalog.sql
..
— creates PL/SQL store procedures
SQL> @?/rdbms/admin/catproc.sql

And you may want to run the following SQL script $ORACLE_HOME/sqlplus/admin/pupbld.sql to avoid getting the following message “Error accessing PRODUCT_USER_PROFILE Warning: Product user profile information not loaded! You may need to run PUPBLD.SQL as SYSTEM”, if not run the message is displayed when using SQL*Plus when not connecting as sys/system.

SQL> connect system/password
SQL> @?/sqlplus/admin/pupbld.sql

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s