Manually Database Creation on Oracle Database 12cR2 with ASM

Creating test Database with name DEMTEST manually on Oracle Database 12cR2 with Automatic Storage Management (ASM).

You can create your database using GUI utility of Oracle called DBCA as well by pretty easy Next – Next wizard, However approach of creating database manually is industrial practice and the reason is that you have more control over your choices. Here we’re creating database manually but with very minimal parameters in create database script. you’ll have lots of options to fully customize your Database while creating it manually. You can go through all parameters in Oracle Docs to better customize your Database.


So before creating Database we’ll need to create initialization parameter file (pfile), i.e. initDEMTEST.ora in $ORACLE_HOME/dbs directory.

1.  Create pfile using vi editor.

[[email protected] ~]$ vi $ORACLE_HOME/dbs/initDEMTEST.ora

Add below parameter entries then save and exit.

db_name = DEMTEST
db_block_size = 8192
DB_CREATE_FILE_DEST = +DATA1
DB_RECOVERY_FILE_DEST = +FRA1
DB_RECOVERY_FILE_DEST_SIZE = 10G
undo_management = auto
control_files = ('+DATA1/DEMTEST/CONTROLFILE/control1.ctl', '+FRA1/DEMTEST/CONTROLFILE/control2.ctl')
undo_management='auto'
undo_retention=3600
undo_tablespace='undotbs'
processes=1000

[ESC] > :wq > Enter (to save and exit)

2. Once we’re done creating pfile, export the ORACLE_SID=DEMTEST (better option is to add this in bash_profile).

[[email protected] ~]$ export ORACLE_SID=DEMTEST

Verify ORACLE_SID.

[[email protected] ~]$ echo $ORACLE_SID
DEMTEST

Then try startup the database in no mount. Though we haven’t created database yet, here we’re checking if database starts up on created pfile.

[[email protected] ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Mar 21 10:41:39 2018

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 268435456 bytes
Fixed Size 8619496 bytes
Variable Size 201329176 bytes
Database Buffers 50331648 bytes
Redo Buffers 8155136 bytes

Here we can verify the database is started up in nomount mode which means our created pfile is perfect.

Note : There are three basic startup modes of Database.
1. startup nomount (In this mode database reads pfile or spfile only).
2. startup mount ( In this mode database reads control files).
3. normal startup (open mode, in this mode database will verify remaining file like datafile and redo log files).

3. Now, we’ll create our database using below create database script.

SQL> !pwd
/home/oracle

SQL> !vi createDEMTEST.sql

Add entries,

create database DEMTEST
logfile group 1 ('+DATA1') size 100M,
 group 2 ('+DATA1') size 100M,
 group 3 ('+DATA1') size 100M
character set utf8
national character set utf8
user sys identified by demtestdba
user system identified by demtestdba
DATAFILE '+DATA1' SIZE 1g 
SYSAUX DATAFILE '+DATA1' SIZE 1g
undo tablespace undotbs
DATAFILE '+DATA1' SIZE 2g
default temporary tablespace temp
TEMPFILE '+DATA1' SIZE 2g;

[ESC] > :wq > Enter (to save and exit)

Run createDEMTEST.sql.

SQL> @createDEMTEST.sql

Database created.

4. Run catalog.sql and catproc.sql and / or pupbld.sql.

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

This script creates data dictionary views, dynamic views, and public synonym. And grants public access to those synonym.

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

This script makes all necessary changes in the database required by PL/SQL. This script will take a bit long to run.

We’ve to run below script as system user and not as a sys user.

SQL> conn system/demtestdba;
Connected.
SQL> @$ORACLE_HOME/sqlplus/admin/pupbld.sql

We can optionally run pupbld.sql script which will prevent sqlplus to print warning while login to database using sqlplus

5. Create spfile into ASM instance from pfile. Do not forget to switch back to SYS user.

SQL> conn / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> create spfile='+DATA1/DEMTEST/spfileDEMTEST.ora' from pfile;
File created.

Edit pfile (i.e. initDEMTEST.ora) we have in $ORACLE_HOME/dbs.

SQL> !vi $ORACLE_HOME/dbs/initDEMTEST.ora

And add below entry.

spfile='+DATA1/DEMTEST/spfileDEMTEST.ora'

And bounce the Database.

Verify database is running on spfile now located in ASM disk.

SQL> show parameter pfile

NAME     TYPE     VALUE
------   -------  ---------------------------------
spfile   string   +DATA1/DEMTEST/spfiledemtest.ora

6. Now create additional user’s tablespace.

SQL> create tablespace users DATAFILE '+DATA1' SIZE 2g;

Tablespace created.

7. Lastly we need to add entry of the created database into /etc/oratab file. Which gets created while running root.sh at the installation time of RDBMS Software.

[[email protected] ~]$ vi /etc/oratab

Add below entry.

DEMTEST:/u01/app/oracle/product/12.2.0/dbhome_1:Y

Cheers, we have successfully created database on Oracle 12cR2 Database with ASM.

How to install Oracle Database 12cR2, Oracle Grid Infrastructure 12cR2 and setting up ASM.

 

Peace 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *