How to create a database in Oracle 19c

In this oracle tutorial, we will learn how to create a database in Oracle 19c. Also, we will cover these topics.

• How to Create a Database in Oracle 19c
• How to Create a Pluggable Database in Oracle 19c
• How to Create a container Database in Oracle 19c
• How to Create Standby Database in Oracle 19c

How to Create a Database in Oracle 19c

In this section, we will learn how to create a database in oracle 19c step by step manually oracle 19c.

• Database in oracle 19c can be created using GUI (Graphical user interface) and command prompt (cmd)
• After successful installation of oracle database 19c you will be able to see Database Configuration Assistant app installed on your windows machine.
• Using Database configuration Assistant (DBCA) app we can create database in Oracle 19c using Graphical User Interface (GUI).
• In this section, we will show the steps to create oracle database using Database Configuration Assistant which is a GUI based app . We have created separate section for Oracle create a database using cmd prompt.

Create a Database in Oracle 19c step by step using DBCA

Here are the steps to create oracle database 19c using Graphical user interface DBCA. Database Configuration Assistant (DBCA) is available after installing the oracle 19c software on the windows 10 operating system.

Step 1: Start Database configuration Assistant app

Open start menu -> type: Database Configuration Assistant or you can open the application using run command, windows + R -> DBCA.

Step 2: Select Database Operation

• Immediately after stating the app you will see a ‘select database operation’ wizard.
• This wizard provides options to create a database, configure an existing database, delete database, manage templates, etc.
• Each option does what it says, we will select the first option to create a database and click on the Next button.

Step 3: Select Database Creation Mode

• Third step is select database creation mode, in this you can either go with the default configuration using global database name and settings or use can create your own.
• In Typical Configuration mode, Oracle pre-built templates will be used. whereas in Advanced Configuration mode, you have to customize every option manually.
• We are going with Advance Configuration to show how to configure manual setting.

Step 4: Select Database Deployment Type

• In this step select the type of database you want to create from the given drop-down list. The available options are:
  • Oracle Single Instance Database
  • Oracle RAC (Real Application Cluster) Database
  • Oracle RAC One Node Database
• Since we want to create a single instance so we have selected Oracle Single Instance Database from the database type drop-down menu.
• Once database type is selected now its time to select the template for your database. The available options are:
  • Data Warehouse
  • General Purpose or Transaction Processing
  • Custom Database
• We have selected General Purpose or Transaction Processing option because neither we want to create a data warehouse on our system nor we want create custom database.
• Click on the Next button once selected database type and template.

Step 5: Specify Database Identification Details

• This is very important step please keep a note of everything you enter here.
• Create a unique global database name and provide the same to the SID. It is not necessary to give same name to SID but do it stay in the safe side.
• Check the Create as Container database check box and select Create a Container database with one or more PDBs.
• Provide any PDB name in our case we have provided lower case pdb, click on the Next button.

Step 6: Select Database Storage Option

• In this step you have to select database storage. There are two options available:
  • Use template file for database storage attributes
  • Use following for the database storage attributes
• On selecting the first option, “Use template file for database storage attributes” storage type and location for database files will be picked up from the specified template.
• On selecting the second option, “Use the following for the database storage attributes” to provide your customized name and location for each datafile in the subsequent screen.
• We recommend you to use template file for database storage attribute if you are a beginner or intermediate in oracle database.
• Click on the Next button after providing necessary input.

Step 7: Select Fast Recovery Option

• Seventh step in the process of creating database in oracle is “Select Fast Recovery Option”. This step is crucial because assures recovery incase something went wrong.
• Hardware & Software’s are prone to faults due to which you may loose your crucial asset stored on the device. In order to dodge this situation oracle provides Recovery option to backup and restore the data.
• Check the Specify Fast Recovery Area checkbox -> Select File System from the dropdown specify the Fast Recovery Area and Size.
• Check the checkbox for Enable Archiving and click on Next button.

Step 8: Specify Network Configuration Details

• In this step, we will specify network configuration details for creating database in oracle.
• Configuring a listener is mandatory if you want to access the database remotely.
• A listener manages the traffic of requests and gets new clients connection requests in the oracle database server.
• Select the checkbox as an automated listener provided by Oracle home or you can create a new listener with a new port number then click Next button to continue.

Step 9: Specify Configuration Options

Memory Allocations enable you to control and how the database manages its memory. It includes:

• Automatic Shared Memory Management if you want to give specific amount of memory to SGA and PGA size.
• Manual Shared Memory Management if you want to give certain amount of memory to SGA and itz component and aggregate PGA.
• Automatic Memory Management if you want Oracle automatic give memory component to SGA and allocate memory to individual PGA as per needed.

Block size and Processes: This tab will allow setting database block size and max number of users can be connected simultaneously to the database.

• It contributes is saving server from overloading or crashing by limiting number of users simultaneously.
• The value you have selected for processes should allow all background processes and user processes.
• A low value of a maximum number of processes will not allow the database to run.

Character Sets: This tab is used to determine character data is stored in the database. Use Unicode AL32UTF8 as a character set.

Connection Mode: You can switch between Dedicated server mode and Shared server mode using this tab.

• In Dedicated Server Mode, each user is associated with a dedicated server process. This option is available when the number of users/clients are small.
• In Shared Server Mode, several clients connections share dedicated resources as of allocation. This mode is the best option when the client excepted a strain on the memory and other resources. If you select this mode, you need to provide a small number of shared servers.

Sample Schemas: This tab allows you to include Human Resources (HR) schema. Select “Add sample schemas to the database” as you want to use later.

Step 10: Specify Management Options

• Enterprise Management (EM) provides management tools for the Oracle database.
• Check the checkbox for “Configure Enterprise Management (EM) database express“ and provide express port numbers and click on the NEXT button.
• If Enterprise Management (EM) Cloud Control is installed on your host computer then click “Register with Enterprise Management (EM)Cloud Control” and provide the connection details such as( OMS host, OMS port, admin username, admin PWD).

Step 11: Specify Database User Credentials

• Provide different administrative passwords for SYS, SYSTEM, PDBADMIN and Oracle home user passwords. Y
• Specify the same administrative passwords for all accounts and we recommend to use different administrative passwords.
• Loss of these password can create problems later so please make a note of it with user name. click on the Next button once done.

Step 12: Select Database Creation Option

• There are 3 checkboxes available for creating the database:
  • “Create database” in order to create database right now,
  • “Save as a database template” to save database as a template and use it as later.
  • “Generate database creation scripts” to create database scripts and use it later during run time.
• For advanced configuration, you can create your initialization parameters and customize storage locations (control files, redo logs, etc).

Step 13: Oracle database Creation Summary

• This step will summarize information as a review. If you want to change, just click the “Back” button for the previous correction.
• We recommend to save the response file on your system so that you can refer it later if something went wrong or when you want to navigate to specific file/folder.
• Click the “Next” button to start the creation of the database and with this all the steps are completed.

• DBCA (Database Configuration Assistant) will start the Oracle database creation and progress bar like below will display the status of creation.
• The entire process take 10-15 minutes plus-minus depending upon the system configuration.

Before closing the window, please make sure you copy the “Enterprise Manager Database Express URL”. You can monitor the activity using this link on the portal.

Step 14. Test the Database

To be sure if your database is running properly. You can start a new session on SQLPlus session with an administrative account on the command prompt.

Read: Alter Table Add Column Oracle

How to Create a Pluggable Database in Oracle 19c

In this section, we will learn how to create a pluggable database in oracle 19c.

• A pluggable database or PDB is a portable collection of different schema and non-schemas objects.
• These objects appear to an oracle net as non Container Database (CDB).
• PDBs can be plugged into CDBs and single CDB can contain multiple PDBs.
• Each PDBs inside CDB appear on the network as a separate database.
• Pluggable Database in Oracle 19c can be created either by using Database Configuration Assistant (DBCA) or by using command prompt.
• We have demonstrated both the ways below. Please follow the steps to create a pluggable database in oracle 19c step by step.

Create a Pluggable Database in Oracle 19c using DBCA tool

In this section, we will learn how to create a pluggable database in oracle 19c using DBCA tool.

Step 1: Start the Database Configuration Assistant app.

Step 2: Select Manage Pluggable Databases option from the “Manage Pluggable databases” wizard and click on the Next button.

Step 3: Select the first option ” Create a Pluggable database” and click on the Next button.

Step 4: Select the container database within which you want to create pluggable database. Also, provide username & password for OS based authentication.

Step 5: Select the first option “Create a new Pluggable database from another PDB” and choose PDB$SEED from the dropdown menu.

Step 6: Enter the name of new pluggable database and create new administrative username and password. This administrative account will help you connect to this pluggable database.

Step 7: On the Pluggable Database Options wizard select File System from the dropdown menu for Storage type and provide the location for database. You can go with the default location already mentioned there.

Step 8: This is the summary of all the configurations you selected so far. Moving forward these configurations will be implemented on the machine to create oracle pluggable database. You won’t be able to make changes once clicked on the Next button.

Step 9: Oracle database creation is in progress once completed click on the finish button below. Incase close button appear instead of finish button and successful creation of pluggable database message is displayed then click on that close button.

Step 10: This is the final step the message says Pluggable database “newpdb” plugged successfully. Click on the close button to exit the wizard.

Read How to Get List all Tables in Oracle Database

Create a Pluggable Database in Oracle 19c using Command Prompt (cmd)

In this section, we will demonstrate how to create a pluggable database using command prompt (cmd) on windows & terminal on macOS and Linux.

Step 1: Check available container databases

# login as sys user
sqlplus / as sys

# command to view the available container databases
select name, open_mode cdb from v$database;

The below output tells that we have one container database with the name ‘orcl’. So we will create a pluggable database within orcl container database.

Step 2: Create pluggable database

• Pluggable database requires a name , admin name and password for admin. Admin account is required to connect with pluggable database.
• In the below code, pdb1 is the name of the pluggable database, adminuser is the username for administration account and adminpwd is the password for that user.

create pluggable database pdb1
admin user adminuser identified by adminpwd
role=(dba) 
create_file_dest='C:\Oracle_19c\oradata\ORCL\';

In the below output, you can see that pluggable database is created.

Step 3: Open the Pluggable database

• New pluggable databases are by default mounted or in closed state.
• It is important to open them before we can use them. Once they are open then ‘OPEN MODE’ will change from Mounted to READ WRITE.

show pdbs

• Use the below command to open the pdbs mode in oracle database.
• In the given output, you can see that PDB1 is now in Read and Write mode.

alter pluggable database pdb1 open;

I this section, we have learned how to create pluggable database in oracle 19c using dbca tool and command line.

Read How to Check Oracle Database Version

How to Create a container Database in Oracle 19c

Container database or CDB is the important part of multitenant database. It contains all the things which are shared across all the pluggable databases (PDBs), data dictionary of objects that are owned by root container & PDBs.

In our first section of this blog: “How to Create a Database in Oracle 19c” we have shared how to create a container database in oracle 19c in the 5th step. we were creating a container database using the GUI tool Database configuration Assistant app.

Please refer to How to Create a Database in Oracle 19c section to learn how to Create a Container Database in Oracle 19c step by step manually.

Read: Oracle Add Row Number

How to Create Standby Database in Oracle 19c

In this section, we will learn about oracle create a standby database in Oracle 19c.

• Standby database is a physical database that one can use if the primary database stopped working.
• Standby database has the same configuration files as primary database has & it works as a perfect substitute for primary key. In case of primary database stopped working then developer can switch to standby database and continue the work.

ALTER TABLE FORCE LOGGING;

Output as ” Database altered.”

The FORCE LOGGING option is the best option to ensure all the changes are made in the database will be captured and available for recovery in the redo logs.

if backup memory forces you shut down the primary database, then follow this statement on Sqlplus to start the primary database.

STARTUP MOUNT;

This will show Output as if “ORA-001 cannot start already-running ORACLE – shut it down first”.

You may like the following oracle tutorials:

• Connect Excel to Oracle Database
• How to Fetch Data from Oracle Database in Python
• Connect to Oracle Database using Python

In this oracle tutorial, we have learned how to create a database using oracle 19c. Also, we have covered these topics.

• How to Create a Database in Oracle 19c
• How to Create a Pluggable Database in Oracle 19c
• How to Create a container Database in Oracle 19c
• How to Create Standby Database in Oracle 19c