Prerequisites Before Creating Database
a) Oracle software must be installed and ORACLE_HOME should
be configured.
b) Set environment variables:
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
export ORACLE_SID=ORCL
export PATH=$ORACLE_HOME/bin:$PATH
c) Create required directories:
mkdir -p /u01/app/oracle/oradata/ORCL
mkdir -p /u01/app/oracle/fast_recovery_area/ORCL
mkdir -p /u01/app/oracle/admin/ORCL/adump
Step 1 – Create Password File
Password file stores SYS user password for remote SYSDBA
connections.
Command:
orapwd file=$ORACLE_HOME/dbs/orapwORCL password=Oracle123 entries=10
Step 2 – Create PFILE (init.ora)
Create initialization parameter file.
Example:
db_name='ORCL'
memory_target=2G
processes=300
audit_file_dest='/u01/app/oracle/admin/ORCL/adump'
db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
db_recovery_file_dest_size=10G
control_files='/u01/app/oracle/oradata/ORCL/control01.ctl'
compatible='19.0.0'
Step 3 – Start Database in NOMOUNT Mode
Start Oracle instance using PFILE.
SQL> startup nomount pfile='$ORACLE_HOME/dbs/initORCL.ora';
Verify:
SQL> show parameter memory_target;
Step 4 – Run CREATE DATABASE Command
This step creates the database files, redo logs, undo
tablespace, SYSTEM/SYSAUX tablespaces.
Example:
CREATE DATABASE ORCL
USER SYS IDENTIFIED BY Oracle123
USER SYSTEM IDENTIFIED BY Oracle123
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/ORCL/redo01.log') SIZE 100M,
GROUP 2 ('/u01/app/oracle/oradata/ORCL/redo02.log') SIZE 100M,
GROUP 3 ('/u01/app/oracle/oradata/ORCL/redo03.log') SIZE 100M
MAXLOGFILES 16
MAXDATAFILES 200
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/u01/app/oracle/oradata/ORCL/system01.dbf' SIZE 700M REUSE
SYSAUX DATAFILE '/u01/app/oracle/oradata/ORCL/sysaux01.dbf' SIZE 500M REUSE
DEFAULT TABLESPACE users
DATAFILE '/u01/app/oracle/oradata/ORCL/users01.dbf' SIZE 100M
UNDO TABLESPACE undotbs1
DATAFILE '/u01/app/oracle/oradata/ORCL/undotbs01.dbf' SIZE 200M;
Step 5 – Run Catalog and Catproc Scripts
Execute Oracle dictionary and PL/SQL package creation
scripts.
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
Step 6 – Create SPFILE from PFILE
Convert PFILE to SPFILE.
SQL> create spfile from pfile;
Restart database:
SQL> shutdown immediate;
SQL> startup;
Step 7 – Configure Listener
Edit listener.ora and start listener.
lsnrctl start
lsnrctl status
Step 8 – Configure tnsnames.ora
Example tnsnames entry:
ORCL=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=ORCL)
)
)
Step 9 – Final Verification
Verify database status:
SQL> SELECT NAME, OPEN_MODE, DB_UNIQUE_NAME FROM V$DATABASE;
Check tablespaces:
SQL> SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES;
Check listener:
lsnrctl status
Best Practices & DBA Tips
- Always verify ORACLE_SID before starting database.
- Use AL32UTF8 character set for multilingual support.
- Keep control files multiplexed.
- Take RMAN backup immediately after database creation.
- Configure archive log mode for production databases.
- Monitor alert logs after startup.
