How To Create A Database Manually in Oracle 19c

How To Create A Database Manually in Oracle 19c

by

How we can Create A Database Manually in Oracle 19c.: During this article we are going to see the way to create a standalone database manually in Oracle 19c step by step.

You can create an database either using Manually or using DBCA( GUI Method) in Oracle. But few organizations recommend you to use manual method for creating database. Make sure oracle database software is installed before creating an oracle database.

Steps to create install Oracle 19c software. Click Here

Steps:

Prerequisites for create a database manually:

Step-1 First, create a profile with a correct environment variable.

[oracle@vm-1 ~]$
[oracle@vm-1 ~]$ vi .ORCL.env
[oracle@vm-1 ~]$
[oracle@vm-1 ~]$ cat .ORCL.env

# Oracle Settings
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_SID=ORCL
export ORACLE_UNQNAME=ORCL
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/19.0.0/dbhome_1
export ORA_INVENTORY=/u01/app/oraInventory
export PATH=/usr/sbin:/usr/local/bin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

Step-2 Create a password file:

[oracle@vm-1 ~]$ orapwd file=$ORACLE_HOME/dbs/orapwORCL password=Welcome_123 entries=10 force=y ignorecase=y

Step-3 Now create the Pfile(init.ora) file( in $ORACLE_HOME/dbs location)

After we start the instance, it’ll read this file and set values accordingly.

[oracle@vm-1 ~]$
[oracle@vm-1 ~]$ mkdir -p /u01/app/oracle/admin/ORCL/adump
[oracle@vm-1 ~]$
[oracle@vm-1 ~]$ mkdir -p /u01/app/oracle/oradata/ORCL
[oracle@vm-1 ~]$
[oracle@vm-1 ~]$ mkdir -p /u01/app/oracle/fast_recovery_area
[oracle@vm-1 ~]$
[oracle@vm-1 ~]$ . .ORCL.env
[oracle@vm-1 ~]$
[oracle@vm-1 ~]$ cd $ORACLE_HOME/dbs
[oracle@vm-1 dbs]$
[oracle@vm-1 dbs]$ pwd
/u01/app/oracle/product/19.0.0/dbhome_1/dbs
[oracle@vm-1 dbs]$
[oracle@vm-1 dbs]$ vi initORCL.ora
[oracle@vm-1 dbs]$
[oracle@vm-1 dbs]$ cat initORCL.ora

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

Step-4 Create a database creation script:

During this script or SQL file making you’d wish to specify about the datafiles, tempfiles, redolog files, and default user details(sys, system).

[oracle@vm-1 ~]$
[oracle@vm-1 ~]$ vi create_database.sql
[oracle@vm-1 ~]$
[oracle@vm-1 ~]$ cat create_database.sql

create database ORCL
user SYS IDENTIFIED by Oracle123
user SYSTEM IDENTIFIED by Oracle123
LOGFILE
group 1 ('/u01/app/oracle/oradata/ORCL/redo01.log') SIZE 50M,
group 2 ('/u01/app/oracle/oradata/ORCL/redo02.log') SIZE 50M,
group 3 ('/u01/app/oracle/oradata/ORCL/redo03.log') SIZE 50M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 50
MAXDATAFILES 100
MAXINSTANCES 1
datafile '/u01/app/oracle/oradata/ORCL/system01.dbf' SIZE 500M autoextend on
SYSAUX datafile '/u01/app/oracle/oradata/ORCL/sysaux01.dbf' SIZE 500M autoextend on
DEFAULT TABLESPACE USERS datafile '/u01/app/oracle/oradata/ORCL/users01.dbf' size 500M autoextend on
DEFAULT TEMPORARY TABLESPACE temp
tempfile '/u01/app/oracle/oradata/ORCL/temp01.dbf' SIZE 100M autoextend on
UNDO TABLESPACE undotbs1
datafile '/u01/app/oracle/oradata/ORCL/undotbs01.dbf' SIZE 500M autoextend on
CHARACTER SET AL32UTF8;

Now we are going to create a database

Step-1 Firstly, run to set environment variable:

[oracle@vm-1 ~]$
[oracle@vm-1 ~]$ . .ORCL.env
[oracle@vm-1 ~]$

Step-2 Start the database in nomount stage:

[oracle@vm-1 ~]$
[oracle@vm-1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 16 13:43:21 2021
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 1073737800 bytes
Fixed Size                  8904776 bytes
Variable Size             616562688 bytes
Database Buffers          444596224 bytes
Redo Buffers                3674112 bytes
SQL>

Step-3 Create a Server Parameter File

SQL>
SQL> create spfile from pfile;

File created.

SQL>

Step-4 Run create database script to create the database:

SQL>
SQL> @create_database.sql

Database created.

SQL>

Step-5 Check and verify

After completion step-3 : You would like to test the database status.

SQL>
SQL> select name, open_mode, instance_name, status from v$database, v$instance;

NAME      OPEN_MODE            INSTANCE_NAME    STATUS
--------- -------------------- ---------------- ------------
ORCL      READ WRITE           ORCL             OPEN

SQL>

Post Creation Steps:

Step-1 Builds the data dictionary.

These three scripts you need to run must after creating a database. Below given three scripts will create the dictionary views and tables.

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 information dictionary.

SQL>
SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql
SQL>
SQL>
SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql
SQL>
SQL>
SQL> @$ORACLE_HOME/sqlplus/admin/catpcat.sql
SQL>

Step-2 In SQL*Plus, run the following script as a user with the SYSDBA privileges:

SQL>
SQL> @$ORACLE_HOME/sqlplus/admin/utlrp.sql
SQL>

Step-3 In SQL*Plus, run the following script as the SYSTEM user:

SQL>
SQL> @$ORACLE_HOME/sqlplus/admin/pupbld.sql
SQL>

Step-4 You can install the additional components depending upon the requirement. These are the optional components.

For Install JVM(JAVA VIRTUAL MACHINE) component:-----------

@$ORACLE_HOME/javavm/install/initjvm.sql;
@$ORACLE_HOME/xdk/admin/initxml.sql;
@$ORACLE_HOME/xdk/admin/xmlja.sql;
@$ORACLE_HOME/rdbms/admin/catjava.sql;
@$ORACLE_HOME/rdbms/admin/catxdbj.sql;


For Install CONTEXT component:----------------------

@$ORACLE_HOME/ctx/admin/catctx.sql change_on_install SYSAUX TEMP LOCK;
@$ORACLE_HOME/ctx/admin/defaults/dr0defin.sql "AMERICAN";
@$ORACLE_HOME/rdbms/admin/dbmsxdbt.sql;


For Install ORADIM component:---------------------------

@$ORACLE_HOME/ord/admin/ordinst.sql SYSAUX SYSAUX;


For Install MEDIA component:---------------------------

@$ORACLE_HOME/ord/im/admin/iminst.sql;


For Install OLAP component:----------------------------

@$ORACLE_HOME/olap/admin/olap.sql SYSAUX TEMP;


For Install SPATIAL component:---------------------------

@$ORACLE_HOME/md/admin/mdinst.sql;


For Install LABEL security component:----------------------

@$ORACLE_HOME/rdbms/admin/catols.sql;


For Install APEX component:-----------------------------

@$ORACLE_HOME/apex/catapx.sql change_on_install SYSAUX SYSAUX TEMP /i/ NONE;


For Install DATABASE VAULT component:----------------------

@$ORACLE_HOME/rdbms/admin/catmac.sql SYSAUX TEMP &&sysPassword;

You’ll be ready to check the installed components by running the below query.

select comp_id,status,comp_name from dba_registry;

Step-4 Create a listener file and start the listener.

[oracle@vm-1 admin]$
[oracle@vm-1 admin]$ vi listener.ora
[oracle@vm-1 admin]$
[oracle@vm-1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = vm-1.localdomain)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

[oracle@vm-1 admin]$
[oracle@vm-1 admin]$
[oracle@vm-1 admin]$ vi tnsnames.ora
[oracle@vm-1 admin]$
[oracle@vm-1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = vm-1.localdomain)(PORT = 1521))


ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vm-1.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

I hope my written content will helped you. Your suggestions/feedback are most welcome from orakldba.com.
Keep learning… Have a great day!!!
Please comment here for your any query related to above content. You can write to mail me through : orakldba@gmail.com.

Leave a Comment