How to create a database in Oracle 19c

In this Oracle tutorial, we will learn how to create a database in Oracle 19c.

How to Create a Database in Oracle 19c

Let us discuss all the approaches individually.

Approach-1 Using Manual Approach

To create database manually in Oracle 19c, follow the below steps.

1. Database in Oracle 19c can be created using GUI (Graphical user interface) and command prompt (cmd)
2. After successfully installing Oracle Database 19c, you will be able to see the Database Configuration Assistant app installed on your Windows machine.
3. Using the Database Configuration Assistant (DBCA) app, we can create a database in Oracle 19c using a Graphic User Interface (GUI).
4. In this section, we will show the steps to create an Oracle database using the Database Configuration Assistant, a GUI-based app. We have created a separate section for Oracle database creation using the cmd prompt.

Approach-2: Using DBCA

Here, we will use the Graphic user interface DBCA. Database Configuration Assistant (DBCA) is available after installing the Oracle 19c software on the Windows 10 operating system.

To create a database in Oracle 19c, follow the steps below.

1. Start Database configuration Assistant app

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

2. Select Database Operation

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

3. Select Database Creation Mode

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

4. Select Database Deployment Type

• In this step, select the type of database you want to create from the 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, we selected Oracle Single Instance Database from the database type drop-down menu.
• Once the database type is selected, it’s time to choose the template for your database. The available options are:
  • Data Warehouse
  • General Purpose or Transaction Processing
  • Custom Database
• We have selected the General Purpose or Transaction Processing option because we do not want to create a data warehouse on our system or a custom database.
• Click the Next button once you have selected the database type and template.

5. Specify Database Identification Details

• This is a very important step. Please keep a note of everything you enter here.
• Create a unique global database name and provide it to the SID. It is not necessary to give the same name to the SID, but do it to stay on 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 a lower case pdb. Click on the Next button.

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 the following for the database storage attributes
• When you select the first option, “Use template file for database storage attributes,” the storage type and location for database files will be picked up from the specified template.
• When selecting the second option, “Use the following for the database storage attributes,” provide your customized name and location for each data file on the subsequent screen.
• If you are a beginner or intermediate user of the Oracle database, we recommend using a template file for the database storage attribute.
• Click on the Next button after providing the necessary input.

7. Select Fast Recovery Option

• The seventh step in creating a database in Oracle is to select the Fast Recovery Option.” This step is crucial because it assures recovery in case something goes wrong.
• Hardware and software are prone to faults, which can cause you to lose your crucial assets stored on the device. To avoid this situation, Oracle provides a Recovery option to backup and restore the data.
• Check the Specify Fast Recovery Area checkbox. Then, select File System from the dropdown menu and specify the Fast Recovery Area and Size.
• Check the checkbox for Enable Archiving and click on the Next button.

8. Specify Network Configuration Details

• This step will specify network configuration details for creating an Oracle database.
• Configuring a listener is mandatory if you want to access the database remotely.
• A listener manages the traffic of requests and gets new client 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 the Next button to continue.

9. Specify Configuration Options

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

• Automatic Shared Memory Management if you want to give a specific amount of memory to SGA and PGA size.
• Manual Shared Memory Management: If you want to give a certain amount of memory to SGA and its components, aggregate PGA.
• Automatic Memory Management: If you want Oracle to automatically give memory components to SGA and allocate memory to individual PGA as needed.

Block size and Processes: This tab allows you to set the database block size and the maximum number of users that can be connected simultaneously to the database.

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

Character Sets: This tab determines where character data is stored in the database. Use Unicode AL32UTF8 as a character set.

Connection Mode: Using this tab, you can switch between Dedicated and Shared server modes.

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

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

10. Specify Management Options

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

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 using different administrative passwords.
• Losing these passwords can create problems later, so please make a note of them with your user name. Once you’ve done this, click on the Next button.

12. Select Database Creation Option

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

13. Oracle Database Creation Summary

• This step will summarize information as a review. If you want to change, click the “Back” button for the previous correction.
• We recommend saving the response file on your system so that you can refer to it later if something goes wrong or when you want to navigate to a specific file/folder.
• Click the “Next” button to start creating the database. This completes all the steps.

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

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

14. Test the Database

To be sure your database is running correctly, 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 schema and non-schemas objects.
• These objects appear to an Oracle net as a non-container Database (CDB).
• PDBs can be plugged into CDB; a single CDB can contain multiple PDBs.
• Each PDB inside CDB appears on the network as a separate database.
• A Pluggable Database in Oracle 19c can be created either by using the Database Configuration Assistant (DBCA) or by using the command prompt.
• We have demonstrated both ways below. Please follow the steps to create a pluggable database in Oracle 19c.

Approach-1 Using the DBCA tool

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

1. Start the Database Configuration Assistant app.

2. From the “Manage Pluggable Databases” wizard, select the Manage Pluggable Databases option and click the Next button.

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

4. Select the container database where you want to create a pluggable database. Provide a username and password for OS-based authentication.

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

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

7. On the Pluggable Database Options wizard, select File System from the dropdown menu for Storage type and provide the database’s location. You can use the default location already mentioned there.

8. This is a summary of all the configurations you selected so far. From now on, these configurations will be implemented on the machine to create an Oracle pluggable database. Once you click on the Next button, you won’t be able to make changes.

9. Oracle database creation is in progress. Once completed, click on the finish button below. If a close button appears instead of the finish button and a successful creation of a pluggable database message is displayed, then click on that close button.

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

Read How to Get List all Tables in Oracle Database

Approach-2 Using Command Prompt (cmd)

This section will demonstrate how to create a pluggable database using the command prompt (cmd) on Windows & terminal on macOS and Linux.

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 output below shows that we have one container database named ‘orcl’. So, we will create a pluggable database within the Orcl container database.

2. Create a pluggable database

• A pluggable database requires a name, admin name, and password for admin. The admin account is required to connect with the pluggable database.
• In the below code, pdb1 is the name of the pluggable database, admin user is the username for the 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 a pluggable database is created.

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 the Oracle database.
• In the given output, you can see that PDB1 is now in Read and Write mode.

alter pluggable database pdb1 open;

In this section, we have learned how to create a pluggable database in Oracle 19c using the 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 an important part of a multitenant database. It contains all the things shared across all the pluggable databases (PDBs), including a data dictionary of objects owned by the root container and PDBs.

In the first section of this blog, “How to Create a Database in Oracle 19c,” we shared how to create a container database in Oracle 19c in the 5th step. We created a container database using the GUI tool Database Configuration Assistant app.

Please refer to the section on how to create a database in Oracle 19c to learn how to manually make a container database in Oracle 19c.

Read: Oracle Add Row Number

How to Create Standby Database in Oracle 19c

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

• A Standby database is a physical database that one can use if the primary database stops working.
• The standby database has the same configuration files as the primary database and works as a perfect substitute for the primary key. If the primary database stops working, the developer can switch to the 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 to shut down the primary database, follow this statement on Sqlplus to start it.

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.