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

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.

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.

Step 7) Now we need to run the db.sql script to create the database.

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.

Related Articles

x11 variable not set in Oracle 19c

How to deinstall Oracle 19c binaries on Linux

How to install Oracle 19c binaries on Linux