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.
[oracle@dbnode1 ~]$ 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).
[oracle@dbnode1 ~]$ export ORACLE_SID=DEMTEST
Verify ORACLE_SID.
[oracle@dbnode1 ~]$ 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.
[oracle@dbnode1 ~]$ 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.
[oracle@dbnode1 ~]$ 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 🙂