Step by Step manually creation of an Oracle 19c Database

Step by Step manually creation of an Oracle 19c Database on Linux 7

In the previous article, we have demonstrated Step by step silent installation of oracle 19c on Linux 7,  now here we are going to create  Oracle 19c standalone database. We can create an Oracle database by using the below methods.

1. DBCA(GUI)

2. Manually

Basically, some organizations, recommend using a manual method for creating a database, or due to less budget or not having GUI option available, we have to perform manual creation.

Here we are going to illustrate, how to create a database manually in oracle 19c on a Linux server.

Below are the pre-requisite to creating a database:

1. Create require directories 

Here we are creating require directories that are required to match exactly as pfile and database creation command.

mkdir -p /u01/app/oracle/admin/test/adump

mkdir -p /u02/oradata/test/

mkdir -p /u01/app/oracle/fast_recovery_area

chmod 655 /u01/app/oracle/admin/test/adump

chmod 655 /u02/oradata/test/

chmod 655 /u01/app/oracle/fast_recovery_area

2. Pfile(init.ora) file

Whenever we start the instance, It will read the pfile(init.ora)/spfile and set the parameter value which is mention in the file, and then mount the instance accordingly.

Below are the example of pfile(init.ora) and same has been created in default location $ORACLE_HOME/dbs

*.audit_file_dest='/u01/app/oracle/admin/test/adump'
*.audit_sys_operations=TRUE
*.audit_trail='db'
*.compatible='19.3.0.0.0'
*.control_files='/u02/oradata/test/control01.ctl','/u01/app/oracle/control02.ctl'
*.db_block_size=8192
*.db_domain='localdomain'
*.db_name='test'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4560m
*.diagnostic_dest='/u01/app/oracle'
*.log_archive_format='%t_%s_%r.arc'
*.memory_max_target=754974720
*.memory_target=754974720
*.open_cursors=300
*.os_roles=FALSE
*.processes=300
*.recyclebin='OFF'
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

Note: You can modify/add parameters according to your requirements along with required directories.

3. Create a database creation script.

In the database create a script, we are going to specify the datafile ,redo log files, temp files, and undo tablespace.

Below is the database creation script and made one SQL file dbcreate.sql.

CREATE DATABASE test
USER SYS IDENTIFIED BY manager
USER SYSTEM IDENTIFIED BY manager
LOGFILE GROUP 1 ('/u02/oradata/test/redo01.log') SIZE 50M,
GROUP 2 ('/u02/oradata/test/redo02.log') SIZE 50M,
GROUP 3 ('/u02/oradata/test/redo03.log') SIZE 50M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 50
MAXDATAFILES 100
MAXINSTANCES 1
DATAFILE '/u02/oradata/test/system01.dbf' SIZE 100M autoextend on
SYSAUX DATAFILE '/u02/oradata/test/sysaux01.dbf' SIZE 100M autoextend on
DEFAULT TABLESPACE users datafile '/u02/oradata/test/users01.dbf' size 100m autoextend on
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE '/u02/oradata/test/temp01.dbf' SIZE 50m
UNDO TABLESPACE undotbs1
DATAFILE '/u02/oradata/test/undotbs01.dbf'
SIZE 200M;

Now, we moving ahead to create a database. Below the steps that we followed to create a manual database in oracle 19c

Step1:-specify  oracle environment 

In this step, we specify ORACLE_SID and ORACLE_HOME

[oracle@localhost ~]$ . oraenv
ORACLE_SID = [test] ? test
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/19.0.0/dbhome_1
The Oracle base remains unchanged with value /u01/app/oracle

Step2:- Start database.

Here we are starting the database in the nomount stage from pfile that we created in pre-requisite above in step2.

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 10 02:27:08 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount      
ORACLE instance started.

Total System Global Area  754971128 bytes
Fixed Size		    8901112 bytes
Variable Size		  478150656 bytes
Database Buffers	  264241152 bytes
Redo Buffers		    3678208 bytes

Step3:- Create database

After start the database in nomount mode, Now we run dbcreate.sql to create the database.

SQL> set echo on

SQL> @dbcreate.sql

SQL> CREATE DATABASE test
  2  USER SYS IDENTIFIED BY manager
  3  USER SYSTEM IDENTIFIED BY manager
  4  LOGFILE GROUP 1 ('/u02/oradata/test/redo01.log') SIZE 50M,
  5  GROUP 2 ('/u02/oradata/test/redo02.log') SIZE 50M,
  6  GROUP 3 ('/u02/oradata/test/redo03.log') SIZE 50M
  7  MAXLOGFILES 5
  8  MAXLOGMEMBERS 5
  9  MAXLOGHISTORY 50
 10  MAXDATAFILES 100
 11  MAXINSTANCES 1
 12  DATAFILE '/u02/oradata/test/system01.dbf' SIZE 100M autoextend on
 13  SYSAUX DATAFILE '/u02/oradata/test/sysaux01.dbf' SIZE 100M autoextend on
 14  DEFAULT TABLESPACE users datafile '/u02/oradata/test/users01.dbf' size 100m autoextend on
 15  DEFAULT TEMPORARY TABLESPACE temp
 16  TEMPFILE '/u02/oradata/test/temp01.dbf' SIZE 50m
 17  UNDO TABLESPACE undotbs1
 18  DATAFILE '/u02/oradata/test/undotbs01.dbf'
 19  SIZE 200M;

Database created.

Step4:- Verify.

After completion of the database creation script in step3, Now we have to verify the database is ready for use.

SQL> select name,open_mode from v$database;

NAME	  OPEN_MODE
--------- --------------------
TEST	  READ WRITE

SQL> select instance_name,status from v$instance;

INSTANCE_NAME	 STATUS
---------------- ------------
test		 OPEN

Post Steps

Step1:- Post Scripts.

Below post scripts create database data dictionary views which is require 100% database functional.

catproc.sql

catalog.sql

pupbld.sql

SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql

Session altered.


Session altered.

DOC>######################################################################
DOC>######################################################################
DOC>	The following PL/SQL block will cause an ORA-20000 error and
DOC>	terminate the current SQLPLUS session if the user is not SYS.
DOC>	Disconnect and reconnect with AS SYSDBA.
DOC>######################################################################
DOC>######################################################################
DOC>#

PL/SQL procedure successfully completed.
----- trimmed Data
SQL> @?/rdbms/admin/sqlsessend.sql
SQL> Rem
SQL> Rem $Header: rdbms/admin/sqlsessend.sql /main/3 2018/07/25 13:50:02 surman Exp $
SQL> Rem
SQL> Rem sqlsessend.sql
SQL> Rem
SQL> Rem Copyright (c) 2013, 2018, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem      sqlsessend.sql - SQL session end
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem      Any commands which should be run at the end of all oracle
SQL> Rem      supplied scripts.
SQL> Rem
SQL> Rem    NOTES
SQL> Rem      See sqlsessstart.sql for the corresponding start script.
SQL> Rem
SQL> Rem    BEGIN SQL_FILE_METADATA
SQL> Rem    SQL_SOURCE_FILE: rdbms/admin/sqlsessend.sql
SQL> Rem    SQL_SHIPPED_FILE: rdbms/admin/sqlsessend.sql
SQL> Rem    SQL_PHASE: MISC
SQL> Rem    SQL_STARTUP_MODE: NORMAL
SQL> Rem    SQL_IGNORABLE_ERRORS: NONE
SQL> Rem    END SQL_FILE_METADATA
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    surman      05/04/18 - 27464252: Update SQL_PHASE
SQL> Rem    surman      03/08/13 - 16462837: Common start and end scripts
SQL> Rem    surman      03/08/13 - Created
SQL> Rem
SQL> 
SQL> alter session set "_ORACLE_SCRIPT" = false;

Session altered.

SQL> 
SQL> 
SQL> 
SQL> Rem *********************************************************************
SQL> Rem END catproc.sql
SQL> Rem *********************************************************************


SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql

Session altered.


Session altered.

DOC>######################################################################
DOC>######################################################################
DOC>	The following statement will cause an "ORA-01722: invalid number"
DOC>	error and terminate the SQLPLUS session if the user is not SYS.
DOC>	Disconnect and reconnect with AS SYSDBA.
DOC>######################################################################
DOC>######################################################################
DOC>#

no rows selected


Session altered.

-------trimmed Data
TIMESTAMP                                                                       
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATALOG    2020-12-11 02:07:05                                   


Session altered.


Session altered.

SQL> spool off


SQL> $ORACLE_HOME/sqlplus/admin/pupbld.sql
Session altered.

DROP SYNONYM PRODUCT_USER_PROFILE
             *
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist 



PL/SQL procedure successfully completed.

DROP VIEW PRODUCT_PRIVS
*
ERROR at line 1:
ORA-00942: table or view does not exist 



View created.


Grant succeeded.

DROP PUBLIC SYNONYM PRODUCT_PROFILE
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist 



Synonym created.

DROP SYNONYM PRODUCT_USER_PROFILE
             *
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist 



Synonym created.

DROP PUBLIC SYNONYM PRODUCT_USER_PROFILE
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist 



Synonym created.


Session altered.

Step2:- Password file (orapwd)

Create a password file under ($ORACLE_HOME/dbs)

oracle@localhost u02]$ cd $ORACLE_HOME/dbs
[oracle@localhost dbs]$ orapwd file=orapwdtest password=test force=y entries=10
[oracle@localhost dbs]$ pwd
/u01/app/oracle/product/19.0.0/dbhome_1/dbs
[oracle@localhost dbs]$ ls -lrt orapwdtest
-rw-r-----. 1 oracle oinstall 6144 Nov 28 09:29 orapwdtest

Step3:- create spfile.

Currently, the database is running from a pfile, Now we are creating spfile from pfile and start a database with spfile.

SQL> show parameter spfile;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
spfile				     string

SQL> create spfile from pfile;

File created.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

Now starting database from spfile.

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 10 02:32:26 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup 
ORACLE instance started.

Total System Global Area  754971128 bytes
Fixed Size		    8901112 bytes
Variable Size		  478150656 bytes
Database Buffers	  264241152 bytes
Redo Buffers		    3678208 bytes
Database mounted.
Database opened.
SQL> show parameter spfile;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
spfile				     string	 /u01/app/oracle/product/19.0.0
						 /dbhome_1/dbs/spfiletest.ora
SQL> select instance_name,status from v$instance;

INSTANCE_NAME	 STATUS
---------------- ------------
test		 OPEN

SQL> select name,open_mode from v$database;

NAME	  OPEN_MODE
--------- --------------------
TEST	  READ WRITE

So finally congratulation, 19c database to ready to rock.

This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Click here for Step by step silent installation of oracle 19c on Linux 7
Click here for Step by Step Install Oracle Enterprise Linux (OEL) 7

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below:

Registration Link
Telegram Technical Group

Whatsapp Technical Group

Facebook Page
Linkedin Group
Linkedin Page

 1,014 total views,  4 views today

DBsGuru

Hello and welcome to DBsGuru,
DBsGuru is a group of experienced DBA professionals and serves databases and its related community by providing technical blogs, projects, training.
Technical blogs are the source of vast information not about databases but its related product like middleware, PL/SQL, replication methodology, and so on.

Thanks for the visits!

Share Learn Grow!

dbsguru.com/