Nisarg Upadhyay
Database creation is in process

Creating databases in Oracle 19c using the Database Configuration Assistant

October 19, 2021 by

In this article, we understand how to create a new database in Oracle 19c using a database configuration assistant. The database configuration assistant allows us to create a new database using a pre-defined template or create a database with various configuration options. In this article, we are going to learn both methods.

The database configuration assistant is used to perform any of the following tasks:

  1. Create a new database
  2. Configure the existing database
  3. Delete database
  4. Manage database templates
  5. Manage pluggable databases
  6. Oracle RAC database instance management

We are going to use the database configuration assistant to create a new database. You can open it by running dbca.exe from the command prompt.

On the Select Database Operation screen, you can choose the operation that you want to perform. You can create, manage, and drop the pluggable and regular database. In our case, we want to create a new database; therefore, select Create a database option.

Select Database Operation

You can choose to configure the database with typical settings or with advanced settings on the Select Database Creation Mode screen.

If you want to create a database using the typical configuration, you can specify the following configuration parameters, and DBCA will create a database for you.

Typical Settings

  1. Global Database Name: Specify the global database name. This name is used to identify the database. In our case, I have provided Eltech as a global database name
  2. Storage Type: Specify the storage type that you want to use the Oracle database files. You can choose:
    1. Automatic Storage Management (ASM)
    2. File system

    I have chosen the file system as the storage type

  3. Database files location: Specify the location where you want to store the database files. If we do not specify the location, the database configuration assistant will save the files {ORACLE_BASE}\oradata\{DB_UNIQUE_NAME} location. To save the database files, I have created a separate directory on my workstation so our database file location will be D:\Oracle\EltechDB\DatabaseFiles
  4. Fast recovery area: When we enable the fast recovery area, the Oracle will store the RMAN backups, archive logs, and control file backups to the location specified in the Fast recovery area. I have created a separate directory that is used as the fast recovery area, so in our case, the location will be D:\Oracle\EltechDB\FastecoveryArea
  5. Database character set: Specify the database character set
  6. Administrative password: Specify the administrative password to access the database
  7. Create as a container database: Select this option if you want to create a database that can function as a multi-tenant container database. You can read Multitenant : Overview of Container Databases (CDB) and Pluggable Databases (PDB) to learn more about the container database. In our case, the database is a non-CDB database; hence, I have disabled the option

The database configuration screen looks like the following screenshot.

Select Database creation options of database configuration assistant

On the Summary screen, you can view the details of database configuration and click on Finish to set up a database named Eltech.

Database configuration summary generated by database configuration assistant

Now, let us see how we can configure various parameters by using an advanced setup. To use the advance option, select Advanced configuration on the Creation mode screen.

Advance option to create database

Advanced configuration

On the deployment type screen, we can choose the database type. The values can be any of the following:

  1. Oracle Single instance database
  2. Oracle real application cluster (RAC) database
  3. Oracle RAC one node database

Secondly, based on the requirement, you can choose the pre-configured template to create the new database. I have selected Oracle Single instance database as database type and chosen General Purpose or transactional processing as a database template.

Select type of database

You can specify the global database name and SID to identify the database on Specify database identification screen. Moreover, if you want to use the multi-tenant architecture for the database, you can select the Create as Container database option. In our setup, I have used Eltech as a global database name and SID.

Specify database identification details

On the database storage option screen, we can configure the storage option. You can choose any of the following options.

  1. Use template file: The installer will use the storage type and database file location from the pre-configured template
  2. Specify the storage type and database file location: you can select the storage type from the Database files storage type drop-down box and specify the database files location in the database file location textbox. I have selected File system as a storage type and D:\Oracle\EltechDB\DatabaseFiles\{DB_UNIQUE_NAME}\ as a database file location
  3. Use Oracle-Managed Files: If you want Oracle to determine the best way to store the database files, you can select Use Oracle-Managed files (OMF) option

Specify database storage option in database configuration assistant

On the Select Fast Recovery Option screen, you can provide the location and size of the recovery area used to store the recovery files. Moreover, you can enable archiving. I have chosen file system as storage type, D:\Oracle\EltechDB\FastecoveryArea as a recovery area, and allocated 8256 MB to store the recovery files. I have also enabled archiving.

Specify the Fast Recovery option

On the Specify Network Configuration Details screen, you can specify the LISTENER details of the Oracle database. The LISTENER is an important component of the database. It accepts the incoming client connection and redirects them to their respective database. We can create multiple LISTENER for their respective database or use the existing listener to connect to the database. I have chosen the pre-configured listener. You can read Configuring and Administering Oracle Net Listener to learn more about the Oracle listeners.

Specify the listener details

On Specify configuration options screen, you can configure the following options.

Memory Management

You can manage the memory component of the database. Here you can allocate the memory to the individual component. You can choose any of the following options:

  1. Automatic memory management: You can allocate the memory to the SGA and PGA. The memory unit will be in MB
  2. Use Manual shared Memory Management: You can allocate the memory to the various component of memory. You can specify the memory for:
    1. Shared Pool
    2. Buffer cache
    3. Java pool
    4. Large Pool
    5. PGA Size
  3. Use Automatic Memory management: You can use this option when you want Oracle to manage the memory allocation. In this option, all you have to do is provide the total memory size you want to allocate to the Oracle database.

I have chosen the Use Automatic Shared Memory management option.

Specify the memory allocation

Sizing

In the Sizing tab, we can specify:

  1. The block size of storage that is used in the Oracle database
  2. Specify the number of the user process that can be connected to the Oracle database. The value entered in this parameter includes the user processes and operating system background processes

I have used the default values.

Specify the number of user processes and block size

Character Set

Under the character set tab, you can specify the character set you want to use to store the database. The valid options are

  1. Unicode
  2. Use OS character set
  3. Select a specific character set from the list

I have selected the Unicode character set.

Specify the character set

Connection Mode

Under the connection mode tab, you can choose any of the following options.

  1. Dedicated Server Mode: When we select this option, the Oracle will allocate the dedicated resources to the user connection. This option is efficient when the number of user connections is less
  2. Shared Server Mode: When we select this option, the Oracle will use a shared pool to allocate the resources to the user connections. This option is efficient when a number of user connection are high

I have selected the default option, which is dedicated server mode.

Specify configuration option in database configuration assistant

Sample Schemas

Under the sample schema tab, you can choose to install the same schema in the database. We want to install the sample schema, so I have selected Add sample schemas to the database options.

Install sample schema

In Specify Management Options screen, you can enable the Enterprise Management database express tool. It’s a GUI web console that is used to manage the Oracle database and server. You can specify the port that is used to connect to the URL of the enterprise manager.

In our case, I have changed the EM database express port to 5600.

Configure the enterprise manager database express

In Specify Database User Credential screen, you can specify the password for SYS and SYSTEM users. If you do not want to specify the different passwords for SYS and SYSTEM users, you can create a common password for all accounts.

I have created a common password for both users.

Specify the administrative accounts

In select the database creation options, you can perform any of the following tasks.

  1. Create a database and run the post DB creation script
  2. Save the configuration as a template. We can use the template to create another database
  3. Generate the database creation script

I have used the default options.

Graphical user interface, text, application, email

On the summary screen, you can view all configured options. Review the configurations and click on Finish to begin the database creation process.

Database configuration summary generated by database configuration assistant

The database creation process begins. You can view the progress in progress page.

Database creation is in process

Once the database has been created, the database configuration assistant provides the details of it.

Database has been created

As you can see, the Eltech database has been created. Now, let us connect it by using SQL*Plus utility. To connect, launch SQL*Plus and connect to the database by using sys user.

Connect to Eltech Database

Once connected, run the following query to view the details of the database.

SQL> select dbid,name,created from v$database;

Output:

Database details

As you can see, the ELTECH database has been created.

Summary

In this article, we learned how we can create an Oracle 19c database by using the database configuration assistant.

Nisarg Upadhyay
Installation, setup and configuration, Oracle

About Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

413 Views