Manual method to create database instead of using DBCA tool but after creating database using Create Database Statement additional procedures must be completed before using database,

These procedures include building Views on Data Dictionary and you can perform these procedures by running the provided scripts.

Here are the steps to create database manually:

Specify an Instance Identifier (SID):

The environment variable (ORACLE_SID) is used to distinguish this current Instance from other Instances running on the same server.

• Specify the Oracle System Identifier (SID) for your Instance.
• Open the command window.
• Set the ORACLE_SID environment variable. It is very common to set the SID to be equal to the database name DB_NAME. It should also be noted that the maximum number of characters for a database name is eight.

The SID is set in Windows as follows (set ORACLE_SID=mynewdb)

As for Linux (export ORACLE_SID=mynewdb)

Ensure That the Required Environment Variables Are Set: 

Depending on your operating system, before you can run SQL*Plus you may need to set environment variables or at least verify that they are set correctly

For example, on most operating systems, ORACLE_SID & ORACLE_HOME should be set. Additionally, it is recommended that you set the PATH variable to include ORACLE_HOME\bin.

On UNIX & Linux operating systems, you must set these variables manually. On Windows, the OUI automatically sets the ORACLE_SID & ORACLE_HOME values ​​in the Windows registry.

If you do not create the database during installation, the OUI does not set the ORACLE_SID in the Windows registry and you will need to set the ORACLE_SID variable when you create your database later.

Choose a Database Administrator Authentication Method:

You must be authenticated and granted the appropriate system privileges in order to create a database. You can be authenticated as an administrator with the required privileges in the following ways:

password file

operating system authentication

Create the Initialization Parameter File:

When Oracle Instance starts, it reads the initialization parameter file, this file can be a text file that can be created and modified by a text editor (PFILE),

or a binary file, which is created and modified dynamically by the database and is preferred and is called Server Parameter File (SPFILE).

In this step, we will create a text initialization parameter file (PFILE) and in a later step we will create a server parameter file (SPFILE).

One way to create a PFILE file is to edit the sample file of the PFILE file (init.ora) which is usually located in the path $ORACLE_HOME/dbs on Linux or you can create a new text file and make sure that it includes the following variables, while the rest of the variables have default values:

• DB_NAME: This variable must be defined in the variables file and is the database identifier and must be consistent with the value used in creating the database CREATE DATABASE statement.
• CONTROL_FILES: It is highly recommended to specify the name of the Control File. If no values ​​are specified for this variable, Oracle creates a single Control File in the same path as the variables file, but specifying values ​​for this variable gives you the option to have the database run with more than one Control File.
• MEMORY_TARGET: Specifies the total amount of memory used by the instance.

It is preferable that the variables file be stored in the default path and named with the default name so that you do not need to specify the name of the variables file when running the database, as Oracle reads the variables file automatically from the default path.

(Windows Only) Create an Instance:

In Windows before you connect to an instance you must create it manually using the ORADIM command.

To create an instance:

Type the command

oradim -NEW -SID sid -STARTMODE MANUAL -PFILE file 

Connect to the Instance:

Run SQL*Plus to connect to the instance with SYSDBA privilege.

To verify through the password file type the command

Sqlplus /nolog

Connect sys as sysdba

To verify through the operating system

Sqlplus /nolog

Connect / as sysdba

You will see the following message

Connected to an idle instance.

Create a Server Parameter File:

The Server Parameter File (SPFILE) allows you to change the variables through the ALTER SYSTEM command and you can create the SPFILE variables file from the PFILE variables file.

CREATE SPFILE FROM PFILE; 

This command reads the PFILE variable file with the default name in the default path and creates an SPFILE from the PFILE variable file and saves it with the default name in the default path.

You can also specify the file name and path for both (PFILE & SPFILE) if you do not want to use the default names and paths.

You must restart the database before the SPFILE variable file takes effect.

Although creating the SPFILE variable file is optional at this stage during database creation, it is recommended, but if the SPFILE variable file is not created,

INSTANCE continues reading from the PFILE variable file.

Start the Instance:

Running the Instance in NOMOUNT mode, the Instance is started in this mode during the creation of the database or during maintenance of the database, and because we store the variables file in the default path and with the default name, we do not need to specify the file name while running the Instance.

At this moment, memory is allocated to the Instance and its operations begin, even though the database itself has not been created yet

Issue the CREATE DATABASE Statement:

To create the database use the CREATE DATABASE Statement.

For example

CREATE DATABASE mynewdb

USER SYS IDENTIFIED BY sys_password

USER SYSTEM IDENTIFIED BY system_password

LOGFILE GROUP 1 ('/u01/logs/my/redo01a.log','/u02/logs/my/redo01b.log') SIZE 100M BLOCKSIZE 512,

GROUP 2 ('/u01/logs/my/redo02a.log','/u02/logs/my/redo02b.log') SIZE 100M BLOCKSIZE 512,

GROUP 3 ('/u01/logs/my/redo03a.log','/u02/logs/my/redo03b.log') SIZE 100M BLOCKSIZE 512

MAXLOGHISTORY 1

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 1024

CHARACTER SET AL32UTF8

NATIONAL CHARACTER SET AL16UTF16

EXTENT MANAGEMENT LOCAL

DATAFILE '/u01/app/oracle/oradata/mynewdb/system01.dbf'

SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED

SYSAUX DATAFILE '/u01/app/oracle/oradata/mynewdb/sysaux01.dbf'

SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED

DEFAULT TABLESPACE users

DATAFILE '/u01/app/oracle/oradata/mynewdb/users01.dbf'

SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

DEFAULT TEMPORARY TABLESPACE tempts1

TEMPFILE '/u01/app/oracle/oradata/mynewdb/temp01.dbf'

SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED

UNDO TABLESPACE undotbs1

DATAFILE '/u01/app/oracle/oradata/mynewdb/undotbs01.dbf'

SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED

USER_DATA TABLESPACE usertbs

DATAFILE '/u01/app/oracle/oradata/mynewdb/usertbs01.dbf'

SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED; 

• The database name is mynewdb, while the global database name will be us.example.com where (us.example.com) is the Domain name.
• Three Control Files will be created as specified in the CONTROL_FILES variable.
• The passwords for the user SYS and SYSTEM have been specified and they are sensitive passwords, it is not necessary to specify the passwords for the user SYS and SYSTEM here while creating the database in Oracle 19c version but if you specify the password it should be for both users SYS and SYSTEM.
• The database has three Redo Log File groups and each group has Two Members, some conditions have been specified to create the Redo Log which are (MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY), also the Block Size has been specified for the Redo Log File which is 512 Bytes, which is the same as the Physical Sectors on Disk, specifying the BLOCKSIZE is optional if it is the same as the Sector Size which is the default.
• The number of DATAFILES that can be opened in the database is also specified by the MAXDATAFILES variable, and this number affects the initial size of the Control File (you can set several limits while creating the database. Some of these limits are limited and affected by the operating system limits)
• AL32UTF8 is the Character Set used to store data in the database.
• AL16UTF16 is the Character Set specified as NATIONAL CHARACTER SET used to store data in fields defined as (NCHAR, NCLOB, or NVARCHAR2).
• SYSTEM Tablespace contains one DATA FILE located on the operating system as it was created and managed by the Locally Managed Tablespace.
• Similarly, the SYSAUX Tablespace was created in the operating system.
• The default Tablespace in the database was specified using the DEFAULT TABLESPACE command.
• DEFAULT TEMPORARY TABLESPACE This command is used to specify the default tablespace for storing temporary data.
• UNDO TABLESPACE To create the Undo Tablespace in which changes that occur in the database are stored.
• USER_DATA TABLESPACE To create a tablespace to store user data.
• It should be noted that the Create Database statement does not create directories or paths, so you must ensure that the correct paths exist.
• When creating the database using Oracle Managed Files (OMF), a value must be set for the DB_CREATE_FILE_DEST variable to specify database paths and automatic naming of files.

Create Additional Tablespaces:

To make the database work efficiently, you need to create other Tablespaces for application and user data, all you have to do is use the CREATE TABLESPACE command.

For example:

CREATE TABLESPACE appstbs LOGGING

DATAFILE '/u01/app/oracle/oradata/mynewdb/apps01.dbf'

SIZE 500M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED

EXTENT MANAGEMENT LOCAL; 

Run Scripts to Build Data Dictionary Views:

Execute the necessary scripts to build the Data Dictionary Views, Synonyms and PL/SQL Packages.

The following scripts must be executed after connecting with SYSDBA privileges.

@?/rdbms/admin/catalog.sql

@?/rdbms/admin/catproc.sql    

@?/rdbms/admin/utlrp.sql 

Execute the following script after connecting to the SYSTEM user.

@?/sqlplus/admin/pupbld.sql 

Run Scripts to Build Data Dictionary Views:

The table below shows a description of these scripts: catalog.sql Creates the views of the data dictionary tables, the dynamic performance views, and public synonyms for many of the views. Grants PUBLIC access to the synonyms.

catproc.sql Runs all scripts required for or used with PL/SQL.

utlrp.sql Recompiles all PL/SQL modules that are in an invalid state, including packages, procedures, and types.

pupbld.sql Required for SQL*Plus. Enables SQL*Plus to disable commands by user.

catpcat.sql Builds the data dictionary. This script runs using the catctl.pl program (and not using SQL*Plus) and internally runs the scripts catalog.sql and catproc.sql with parallel processes, thus improving the performance of building the data dictionary.

(Optional) Run Scripts to install additional Options:

You may need to run some other scripts, depending on the features and options you need.

If you plan to install some products that work with the database, some of these products need Additional Data Dictionary Tables.

Backup the Database: After you have created the database, make a backup of the database to ensure that you have a complete set of files that can be restored in case of a problem.

(Optional) Enable Automatic Instance Startup:

You may need to configure the Instance to start automatically after a server restart.

For example, when creating an instance on Windows, you need to use the option

ORADIM -EDIT -SID sid -STARTMODE AUTO -SRVCSTART SYSTEM [-SPFILE]