Manual Oracle Database Creation (DB Name:
mydb)
Author: Nagendrababu
Date: June 2025
Create Required Directories
Before creating the database, prepare filesystem directories for datafiles, control files, redo logs, and
recovery files. These directories should be owned by the oracle user and the oinstall group with
suitable permissions. For example, using bash commands:
$ mkdir -p /u01/app/oracle/oradata/mydb
$ mkdir -p /u01/app/oracle/admin/mydb/adump
$ mkdir -p /u01/app/oracle/fast_recovery_area/mydb
$ mkdir -p /u01/app/oracle/archivelog/mydb
$ chown -R oracle:oinstall /u01/app/oracle/oradata/mydb
$ chmod 775 /u01/app/oracle/oradata/mydb
This follows Oracle’s recommended directory structure (e.g. the default database file directory is
$ORACLE_BASE/oradata 1 ). Ensure each directory is owned by oracle and has 775 permissions 1 .
Page 2
Update File
Next, add an entry for the new SID to (or on some systems).
Open as root and append a line in the format:
Each line is 2 . Here, means do not auto-start at boot. By adding this
line, the script can recognize the new database name 3 . For example:
Page 3
1
Set Oracle Environment (oraenv)
Switch to the oracle user and set the and using the script
(Bourne/Bash/Korn shell). Run:
When prompted for the SID, enter . This loads the correct environment variables for 4 .
For example:
This ensures that the is set to and points to the directory of the
Oracle software.
Create a PFILE (Initialization Parameter File)
Create a text initialization file ( PFILE ) with essential parameters for the new database. In the
$ORACLE_HOME/dbs directory, edit initmydb.ora (replace mydb with your SID) and include entries such
as:
# Example initmydb.ora contents:
*.db_name='mydb'
*.control_files='/u01/app/oracle/oradata/mydb/control01.ctl','/u01/app/
oracle/oradata/mydb/control02.ctl'
*.db_block_size=8192
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=10G
*.undo_tablespace='UNDOTBS1'
At minimum, specify DB_NAME , one or more CONTROL_FILES , and UNDO_TABLESPACE . For example,
setting DB_NAME='mydb' and listing control file paths 5 . Adjust parameters such as memory sizes and
character sets as needed for your environment. Save and exit the file when done.
Page 4
Write the CREATE DATABASE SQL Script
Prepare an SQL script (for example, create_mydb.sql ) that issues the CREATE DATABASE statement.
Include clauses for SYS and SYSTEM users, redo log groups, tablespaces, and datafiles. Example content
(SQL):
USER SYS IDENTIFIED BY sys_password
USER SYSTEM IDENTIFIED BY sys_password
2
LOGFILE GROUP 1 (
'/u01/app/oracle/oradata/mydb/redo01a.log',
'/u01/app/oracle/oradata/mydb/redo01b.log'
) SIZE 50M,
GROUP 2 (
'/u01/app/oracle/oradata/mydb/redo02a.log',
'/u01/app/oracle/oradata/mydb/redo02b.log'
) SIZE 50M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/u01/app/oracle/oradata/mydb/system01.dbf' SIZE 500M REUSE
SYSAUX DATAFILE '/u01/app/oracle/oradata/mydb/sysaux01.dbf' SIZE 500M REUSE
DEFAULT TABLESPACE users
DATAFILE '/u01/app/oracle/oradata/mydb/users01.dbf' SIZE 100M AUTOEXTEND
ON
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/u01/app/oracle/oradata/mydb/temp01.dbf' SIZE 100M REUSE
UNDO TABLESPACE undotbs1
DATAFILE '/u01/app/oracle/oradata/mydb/undotbs01.dbf' SIZE 200M REUSE
AUTOEXTEND ON;
Be sure the DB_NAME matches your initialization file. Adjust file sizes, paths, and passwords as needed.
This SQL follows Oracle’s documented syntax for CREATE DATABASE 6.
Page 5
Startup Database in NOMOUNT
Start the new instance without mounting a database. From the oracle user shell, run SQL*Plus:
This allocates instance memory and starts background processes using the PFILE settings
7 . At the
NOMOUNT stage, the control files and datafiles are not yet created, but the instance is ready to execute
the CREATE DATABASE statement.
Monitor the Alert Log
Open a separate terminal or window to monitor the instance’s alert log. For example:
$ tail -f $ORACLE_BASE/diag/rdbms/mydb/mydb/trace/alert_mydb.log
3
Watch for messages as the database is created. If the creation fails, the alert log will contain error details
8. Reviewing this log is the quickest way to diagnose issues during startup or creation. (Adjust the alert
log path according to your Oracle version and platform.)
Page 6
Execute the Database Creation Script
In SQL*Plus (SYSDBA, at NOMOUNT), run the CREATE DATABASE script:
SQL> @/u01/app/oracle/admin/mydb/cmds/create_mydb.sql
SQL> SELECT name, open_mode, log_mode FROM V$DATABASE;
This executes the statements in your SQL file. After completion, query V$DATABASE to verify the new
database mydb is open and in the expected mode. For example, you should see OPEN and ARCHIVELOG
(or NOARCHIVELOG ) in the output 9 .
Run Post-Creation Scripts
Still in SQL*Plus, run the standard catalog and cataloger scripts to build the data dictionary and compile
PL/SQL objects. Execute:
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> @?/rdbms/admin/utlrp.sql
The first script creates data dictionary views, the second compiles PL/SQL, and utlrp.sql recompiles
any invalid objects 10 . Allow time for each script to finish. No news is good news here; at the end, most
objects should be valid.
Page 7
(Optional) Set the System Date
If your database or applications require a specific system date/time for testing or other purposes, adjust the
OS date now. For example, as the root or with sudo:
Verify with or . Then, restart any time-sensitive processes as needed. (This step is
optional and depends on your testing requirements.)
End of Manual
Page 8
