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
Comments