top of page

Oracle Database Creation Made Easy: A Step-by-Step Guide

Updated: Aug 21, 2023

Step By Step Guide on How to create Oracle Database Manually



Step1: Export Oracle SID and create pfile for new database creation.



export ORACLE_SID=prod


pwd


cd /u01/app/oracle/product/19c/db_1/dbs


vi initprod.ora


*.audit_file_dest='/u01/app/oracle/admin/prod/adump'

*.audit_trail='db'

*.compatible='19.0.0'

*.control_files='/u01/app/oracle/oradata/prod/controlfile/control_01.ctl','/u01/app/FRA/prod/controlfile/control_02.ctl'

*.db_block_size=8192

*.db_create_file_dest='/u01/app/oracle/oradata'

*.db_name='prod'

*.db_recovery_file_dest='/u01/app/FRA'

*.db_recovery_file_dest_size=8256m

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)'

*.log_archive_dest_1='LOCATION=/u01/app/ARCHIVE'

*.log_archive_format='%t_%s_%r.dbf'

*.nls_language='AMERICAN'

*.nls_territory='AMERICA'

*.open_cursors=300

*.pga_aggregate_target=980m

*.processes=300

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=2938m

*.undo_tablespace='UNDOTBS1'



Step2: Create all required directories.



Create following directory on server and give permission to oracle user and oinstall group.


mkdir -p /u01/oradata/prod/datafile/

mkdir -p /u01/app/oracle/admin/prod/adump

mkdir -p /u01/app/oracle/oradata/prod/controlfile/

mkdir -p /u01/app/FRA/prod/controlfile/

mkdir -p /u01/app/FRA

mkdir -p /u01/app/ARCHIVE




Step3: Startup the database in nomount stage from the newly created pfile.



export ORACLE_SID=prod


startup nomount pfile='/u01/app/oracle/product/19c/db_1/dbs/initprod.ora';



Step4: create database creation script and run in sqlplus prompt.



vi create_database.sql


CREATE DATABASE prod

USER SYS IDENTIFIED BY sys

USER SYSTEM IDENTIFIED BY sys

LOGFILE GROUP 1 ('/u01/oradata/prod/datafile/redo01_01.log','/u01/oradata/prod/datafile/redo01_02.log') SIZE 100M,

GROUP 2 ('/u01/oradata/prod/datafile/redo02_01.log','/u01/oradata/prod/datafile/redo02_02.log') SIZE 100M,

GROUP 3 ('/u01/oradata/prod/datafile/redo03_01.log','/u01/oradata/prod/datafile/redo03_02.log') SIZE 100M

MAXLOGFILES 10

MAXLOGMEMBERS 5

MAXLOGHISTORY 1

MAXDATAFILES 100

MAXINSTANCES 5

CHARACTER SET US7ASCII

NATIONAL CHARACTER SET AL16UTF16

DATAFILE '/u01/oradata/prod/datafile/system01.dbf' SIZE 1800M REUSE

EXTENT MANAGEMENT LOCAL

DEFAULT TEMPORARY TABLESPACE temp

tempfile '/u01/oradata/prod/datafile/temp01.dbf' SIZE 200M REUSE

UNDO TABLESPACE UNDOTBS1 DATAFILE '/u01/oradata/prod/datafile/undotbs01.dbf' SIZE 500M

SYSAUX DATAFILE '/u01/oradata/prod/datafile/sysaux01.dbf' SIZE 550M REUSE AUTOEXTEND ON

DEFAULT TABLESPACE users DATAFILE '/u01/oradata/prod/datafile/users01.dbf' SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;



sqlplus / as sysdba


@create_database.sql


select name,open_mode,log_mode from v$database;



Step5: Create a dictionary view by running below sql files.



---> Run below script for build database scripts


@?/rdbms/admin/catalog.sql;


(Creates the views of the data dictionary tables, the dynamic performance views, And public synonyms for many of the views. Grants PUBLIC access to the synonyms)


@?/rdbms/admin/catproc.sql;


(Runs all scripts required for or used with PL/SQL.)


@?/rdbms/admin/utlrp.sql



Step6: create spfile from pfile



create spfile from pfile;



Step7: Restart database



shutdown immediate

startup


select name,open_mode,log_mode from v$database;



Step8: Verification of files



col name for a60

select NAME from v$datafile;


col member for a60

select member from v$logfile;


show parameter spfile

show parameter control_files



Step9: Create entry of database SID in /etc/oratab file.



vi /etc/oratab

prod:/u01/app/oracle/product/19c/db_1:Y



Done...!!!


Thanks for Reading.



Learn More @ our YouTube Channel:





Comments


bottom of page