Table of Contents
Introduction
In this article, we’ll walk through the steps on How to Create Database in Oracle 19c using the Manual, Silent and DBCA (Database Configuration Assistant) which is a Graphical User Interface (GUI) approach.
Before starting the steps, I am assuming that you have already downloaded the Oracle 19c Software from Oracle's official website and have also installed the Oracle 19c Binaries in your system.
How to Create Database in Oracle 19c
1. Manual Database Creation method
In this method, we need to perform each step manually.
Step 1) We need to create the bash profile to set the environment.
# .bash_profile
Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
User-specific environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin
export PATH
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_SID=ORA01234
export TNS_ADMIN=$ORACLE_HOME/network/admin
export PS1="\u@\h $ORACLE_SID \w$ "
PATH=$PATH:$HOME/.local/bin:$ORACLE_HOME/bin
export PATH
Note:- After creation, we need to execute . .bash_profile to set the environment variables.
Step 2) We need to create the respective directories
mkdir -p /u01/app/oracle/ORA01234/admin
mkdir -p /u01/app/oracle/ORA01234/admin/scripts
mkdir -p /u01/app/oracle/ORA01234/admin/diag
mkdir -p /u01/app/oracle/ORA01234/admin/arch
mkdir -p /u01/app/oracle/ORA01234/oradata
mkdir -p /u01/app/oracle/ORA01234/admin/adump
Step 3) We need to create the pfile inside the $ORACLE_HOME/dbs location which helps to put the database in the mounted state.
cd $ORACLE_HOME/dbs
vi initORA01234.ora ————–> You can use the database name as per your environment
*.audit_file_dest='/u01/app/oracle/ORA01234/admin/adump'
*.db_name=ORA01234
*.compatible='19.0.0.0'
*.db_block_size=8192
*.memory_target=300m
*.diagnostic_dest='/u01/app/oracle/ORA01234/admin/diag'
*.log_archive_dest='/u01/app/oracle/ORA01234/admin/arch'
*.log_archive_format='redo_%T_%R_%S.arc'
*.undo_tablespace=undotbs
*.undo_management=auto
*.remote_login_passwordfile='EXCLUSIVE'
*.processes=300
*.control_files='/u01/app/oracle/ORA01234/oradata/control01.ctl'
Step 4) Now make the script for the database creation.
cd /u01/app/oracle/ORA01234/admin/scripts
vi ORA01234.sql
create database ORA01234
datafile '/u01/app/oracle/ORA01234/oradata/system01.dbf' size 300m
sysaux datafile '/u01/app/oracle/ORA01234/oradata/sysaux01.dbf' size 300m
undo tablespace undotbs datafile '/u01/app/oracle/ORA01234/oradata/undotbs01.dbf' size 100m
logfile
group 1 '/u01/app/oracle/ORA01234/oradata/redo1a.log' size 20m,
group 2 '/u01/app/oracle/ORA01234/oradata/redo2a.log' size 20m
character set UTF8
Step 5) We need to create the password file for the database which we are going to create.
orapwd file=orapwORA01234 password=ORA1234# force=y
Step 6) Now make the database in the nomount state with the help of pfile.
oracle@practice ORA01234 /u01/app/oracle/product/19.0.0/dbhome_1/dbs$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 – Production on Tue Oct 22 01:12:55 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/u01/app/oracle/product/19.0.0/dbhome_1/dbs/initORA01234.ora';
ORACLE instance started.
Total System Global Area 314572352 bytes
Fixed Size 8896064 bytes
Variable Size 230686720 bytes
Database Buffers 67108864 bytes
Redo Buffers 7880704 bytes
Step 7) Now we need to run the db.sql script to create the database.
oracle@practice ORA01234 /u01/app/oracle/ORA01234/admin/scripts$ cd /u01/app/oracle/ORA01234/admin/scripts
oracle@practice ORA01234 /u01/app/oracle/ORA01234/admin/scripts$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 – Production on Tue Oct 22 01:17:28 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
SQL> @ORA01234.sql;
Database created.
Note: – This is a method that shows How to Create Database in Oracle 19c manually.
Step 8) We need to execute a few postscripts that help to create data dictionary tables, dynamic performance views, and public synonyms.
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/sqlplus/admin/pupbld.sql
Step 9) We need to create the entry of the new database in the /etc/oratab file.
ORA01234:/u01/app/oracle/product/19.0.0/dbhome_1:N
Step 10) Now connect to the database and check the status.
set lines 250 pages 250
col HOST_NAME for a15
col DB_Start_Time for a20
col name for a20
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
SELECT NAME as DB_NAME,instance_name,OPEN_MODE,HOST_NAME,PROTECTION_MODE,database_role,logins,to_char(startup_time,'DD-MON-YYYY HH24:MI') DB_Start_Time FROM
gV$INSTANCE,v$database;
2. Silent Database Creation method
We can create a database in two ways one is for a Container database and another is for Non container database.
Container Database Command
dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname ORA01234 -sid ORA01234 -characterSet AL32UTF8 -sysPassword ORA01234# -systemPassword ORA01234# -createAsContainerDatabase true -totalMemory 2048 -storageType FS -datafileDestination /u01/app/oracle/ORA01234/oradata -emConfiguration NONE -numberOfPDBs 1 -pdbName ORA01234_PDB -pdbAdminPassword ORA01234# -sampleSchema true -ignorePreReqs
Output
Prepare for db operation
8% complete
Copying database files
31% complete
Creating and starting Oracle instance
32% complete
36% complete40% complete
43% complete
46% complete
Completing Database Creation
51% complete
53% complete
54% complete
Creating Pluggable Databases
58% complete
77% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:
/u01/app/oracle/cfgtoollogs/dbca/ORA01234.
Database Information:
Global Database Name:ORA01234
System Identifier(SID):ORA01234
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/ORA01234/ORA01234.log" for further details.
Check the database status
Non-Container Database Command
dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbName ORA01234 -sid ORA01234 -sysPassword ORA01234# -systemPassword ORA01234#
-createAsContainerDatabase false -emConfiguration NONE -datafileDestination /u01/app/oracle/ORA01234/oradata -storageType FS -characterSet AL32UTF8 -totalMemory 2048 -recoveryAreaDestination /u01/app/oracle/ORA01234/admin/arch -sampleSchema true -ignorePreReqs
Output
Prepare for db operation
10% complete
Copying database files
40% complete
Creating and starting Oracle instance
42% complete
46% complete
50% complete
54% complete
58% complete
60% complete
Completing Database Creation
66% complete
69% complete
70% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:
/u01/app/oracle/cfgtoollogs/dbca/ORA01234.
Database Information:
Global Database Name:ORA01234
System Identifier(SID):ORA01234
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/ORA01234/ORA012340.log" for further details.
Check the database status
Note:- This method shows How to Create Database in Oracle 19c silently.
3. DBCA Database Creation method
In this method, we will use the DBCA (Database Configuration Assistant) utility with the help of a Graphical interface.
Step 1 ) Launch the DBCA utility and it will pop up a window like below. Select the Create a Database option.
Step 2) Select a Database Creation mode, choose the Advanced Configuration option, and proceed further.
Step 3) Select the database deployment type as per your requirement.
Step 4) Select Database identification details, here you can choose either container or non-container database option. I am going with the container database option.
Step 5) Select the Storage option, you can choose as per your requirements like File System or ASM (Automatic Storage Management)
Step 6) Select FRA (Fast Recovery Option), you can enable it now or do it later as well after the database creation.
Step 7) In Network configuration you can create a new Listener if it is not already created.
Step 8) Leave the Data Vault option blank as it is not required in this configuration for now.
Step 9) In the Configuration option you can give the Memory, Sizing, Character sets, Connection Mode, and Sample schemas options as per your requirement.
Note: – Please double-check the Character sets and DB Block Size option and then proceed further as it will not be changed after the creation of the database.
In my case, I have given the below parameter values.
a) Memory—————> 3 GB
b) Processes————-> 300
c) Character sets ——–> AL32UTF8
d) Connection Mode —–> Dedicated Server Mode
Step 10) Uncheck the OEM console option as we will set this up in the latest OEM 13c console.
Step 11) You need to set up the User Credentials for SYS, SYSTEM, and PDBADMIN (If Applicable). You can set a single password for all three users or you can set it differently for all the users, it depends upon your requirements.
Step 12) Finally You can click on the Create Database option to get our database ready for use. You can also look at the summary option as well if you want to make any changes.
Step 13) Finally, Our database is created and it is ready to use.
Note:- This method shows How to Create Database in Oracle 19c with the help of the DBCA utility.
Output
Conclusion
We have covered all the steps on How to Create Database in Oracle 19c using the Manual, Silent and DBCA (Database Configuration Assistant) which is a Graphical User Interface (GUI) approach.
If you enjoyed the article, please leave a comment and share it with your friends. Also, let me know which Oracle and MySQL topics you'd like to see covered in future articles.