How to Create database on Oracle Database

SQL statement is a more manual approach to creating a database than using Oracle Database Configuration Assistant (DBCA). One advantage of using this statement over using DBCA is that you can create databases from within scripts.

Hi guys ! In this write up, we will learn, how to create database on oracle database using the manual approach (CMD)

When you use the CREATE DATABASE statement, you must complete additional actions before you have an operational database. These actions include building views on the data dictionary tables and installing standard PL/SQL packages. You perform these actions by running the supplied scripts.

To Create the database, we have to work through step by step, we will discuss about these below. Seat tight and hold your breath !

Instance Identifier Specification (SID)

The ORACLE_SID environment variable used to distinguish this instance from other Oracle Database instances that you may create later and run concurrently on the same host computer.

Decide on a unique Oracle system identifier (SID) for your instance.

Open a command window & Set the ORACLE_SID environment variable.

Restrictions related to the valid characters in an ORACLE_SID are platform-specific. On some platforms, the SID is case-sensitive.
The following example for UNIX and Linux operating systems sets the SID for the instance that you will connect to in Step 6: Connect to the Instance:

ORACLE_SID=AAA
export ORACLE_SID

Required Environment Variables

On most platforms, ORACLE_SID and ORACLE_HOME must be set. In addition, it is advisable to set the PATH variable to include the ORACLE_HOME/bin directory. On the Linux platforms, you must set these environment variables manually. If you did not create a database upon installation, OUI does not set ORACLE_SID in the registry, and you will have to set the ORACLE_SID environment variable when you create your database later.

For more, you can find my previous tutorial about how to install Oracle Database here https://unixcop.com/how-to-install-oracle-database-21c-on-centos-8/

Database Administrator Authentication Method Selection

User must be and granted appropriate system privileges in order to create a database.

Administrators can_be authenticated in the following two ways. You have to decide either one

Initialization Parameter File

At the time of starting the oracle instances, it reads an initialization parameter file. This file can be a text file, which can be created and modified with a text editor, or a binary file, which is created and dynamically modified by the database.

The binary file, which is preferred, is called a server parameter file. In this step, you create a text initialization parameter file. In a later step, you create a server parameter file from the text file.

Connect to the Instance

Start SQL*Plus and connect to your Oracle Database instance with the SYSDBA administrative privilege.

To authenticate with a password file, enter the following commands, and then enter the SYS password when prompted:

sqlplus /nolog
SQL> CONNECT SYS AS SYSDBA

To authenticate with operating system authentication, enter the following commands:

sqlplus /nolog
SQL> CONNECT / AS SYSDBA

Create a Server Parameter File

The server parameter file enables you to change initialization parameters with the ALTER SYSTEM command and persist the changes across a database shutdown and startup. You create the server parameter file from your edited text initialization file.

Run the following SQL*Plus command:

CREATE SPFILE FROM PFILE;

This SQL*Plus command reads the text initialization parameter file (PFILE) with the default name from the default location, creates a server parameter file (SPFILE) from the text initialization parameter file, and writes the SPFILE to the default location with the default SPFILE name.

You can also supply the file name and path for both the PFILE and SPFILE if you are not using default names and locations.

Start the Instance

Typically, you do this only during database creation or while performing maintenance on the database. In this example, because the initialization parameter file or server parameter file is stored in the default location, you are not required to specify the PFILE clause:

STARTUP NOMOUNT

At this point, the instance memory is allocated and its processes are started. The database itself does not yet exist.

Issue the CREATE DATABASE Statement

The following statement creates a database AAA. This database name must agree with the DB_NAME parameter in the initialization parameter file. This example assumes the following:

CREATE DATABASE AAA
   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/AAA/system01.dbf'
     SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
   SYSAUX DATAFILE '/u01/app/oracle/oradata/AAA/sysaux01.dbf'
     SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
   DEFAULT TABLESPACE users
      DATAFILE '/u01/app/oracle/oradata/AAA/users01.dbf'
      SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
   DEFAULT TEMPORARY TABLESPACE tempts1
      TEMPFILE '/u01/app/oracle/oradata/AAA/temp01.dbf'
      SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
   UNDO TABLESPACE undotbs1
      DATAFILE '/u01/app/oracle/oradata/AAA/undotbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
   USER_DATA TABLESPACE usertbs
      DATAFILE '/u01/app/oracle/oradata/AAA/usertbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

That’s all for today, hope will see you in the next write up, meanwhile, if you have questions/concerns, let me know in the comment section. If you are loving this write up, don’t forget to share with your loved ones !