How to Create Database in Oracle 19c

By DBAStack

Published on:

How to create database in Oracle 19c

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

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;

How to Create Database in Oracle 19c

We can create a database in two ways one is for a Container database and another is for Non container database.

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

Container Database
Container Database output

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

Non Container Database
Non Container Database Output

Note:- This method shows How to Create Database in Oracle 19c silently.

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.

Create Database

Step 2) Select a Database Creation mode, choose the Advanced Configuration option, and proceed further.

Advanced Configuration

Step 3) Select the database deployment type as per your requirement.

database deployment type

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.

Database Identification

Step 5) Select the Storage option, you can choose as per your requirements like File System or ASM (Automatic Storage Management)

Storage Option

Step 6) Select FRA (Fast Recovery Option), you can enable it now or do it later as well after the database creation.

Fast Recovery Option

Step 7) In Network configuration you can create a new Listener if it is not already created.

Network Configuration

Step 8) Leave the Data Vault option blank as it is not required in this configuration for now.

Data Vault Option

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.

Management Option

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.

User Credentails

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.

Database Creation option.
Summary

Step 13) Finally, Our database is created and it is ready to use.

Database Creation Complete

Note:- This method shows How to Create Database in Oracle 19c with the help of the DBCA utility.

Output

Database Status
Container Name 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.

x11 variable not set in Oracle 19c

How to deinstall Oracle 19c binaries on Linux

DBAStack

I have experience in database technology for more than 8 years. I hope with the help of this blog I will share knowledge and real-world experience with you all. You all can join me and together we will explore the world of database technology!

Related Post

X11 variable not set in Oracle 19c

How to deinstall Oracle 19c binaries on Linux

How to install Oracle 19c binaries on Linux

How to resolve long running queries in Oracle 19c

Leave a Comment