Home Music Tech Info Tech Blog About Us Contact Us

Information Technology

How To Create An Oracle 12c Database

There are two ways to create a database in Oracle and that is by using SQL scripts or by using the GUI tool dbca (Database Configuration Assistant).

Since my intention for this guide are for beginners in the world of Oracle database, my demonstration will focused on the use of dbca. The database we are creating is meant for a monitoring application.

Hopefully my step-by-step guidelines will help give you an idea how the Oracle database is created in particular to a Solaris UNIX environment.

TABLE OF CONTENTS

Requirements

Prepare The Database Disk Storage

Setup Required Operating Environment Variables

Enable the Oracle Net Listener

Create The Database Using The GUI Tool DBCA

A Brief Overview Of The Database

How To Startup An Oracle Database

How To Shutdown An Oracle Database

Oracle Enterprise Manager Database Express Configuration

REQUIREMENTS

Here are the following assumptions prior to creating a database.

An Oracle database software has already been installed.

If you haven’t done this yet, please check my article - How To Install Oracle 12c Database Software

You need to enable X11 forwarding in your SSH terminal.

If you are not familiar on how this works, check my article - X11 Forwarding In Solaris 11

You need to know the hostname to use for your database. Usually, the same server hostname is used for database access. But on certain implementations it is preferred by Systems Engineers to employ Virtual IPs (VIP) for each database created. This hostname or VIP will be used by your database listener for which all users connect to. In this exercise, we’ll simply just use the server host s111oem since there are no VIPs created in this computer.

PREPARE THE DATABASE DISK STORAGE

A disk storage is required to house your database datafiles. By default the datafiles will be created at the ORACLE_BASE/oradata and optionally ORACLE_BASE/fast_recovery_area for your flashback backup. You can however assign a different storage and present this as a separate mount point. But for this demonstration, we’ll use the default path.

SETUP REQUIRED OPERATING ENVIRONMENT VARIABLES

As discussed in my previous article on installing a database software, there are environment variables that are needed to be source-in prior to installing/updating a database software, creating a database, and starting/stopping a database.

In this demo, the following are the environment variables set in a script. You should use an editor such as vi to create the script oraenv_oemdb.sh with the contents specified below.

oraenv_oemdb.sh

ORACLE_SID=OMRDB ; export ORACLE_SID

ORACLE_UNQNAME=OMRDB ; export ORACLE_UNQNAME

ORACLE_BASE=/oem/app/oraemdb ; export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/12.1.0.1/db ; export ORACLE_HOME

ORAINST=$ORACLE_HOME/oraInst.loc ; export ORAINST

TNS_ADMIN=$ORACLE_HOME/network/admin ; export TNS_ADMIN

PATH=$PATH:$ORACLE_HOME/bin ; export PATH

EDITOR=vi ; export EDITOR

TMPDIR=$ORACLE_BASE/tmp ; export TMPDIR

if [ ! -f $TMPDIR ];

then

mkdir -p $TMPDIR

echo ------- DATABASE ENV -------

echo ORACLE_BASE=$ORACLE_BASE

echo ORACLE_HOME=$ORACLE_HOME

echo ORACLE_SID=$ORACLE_SID

echo ORACLE_UNQNAME=$ORACLE_UNQNAME

echo ORAINST=$ORAINST

echo TNS_ADMIN=$TNS_ADMIN

echo TMPDIR=$TMPDIR

echo

Now, let’s source-in the environment settings by doing:

s111oem:~/.env$ . ./oraenv_oemdb.sh

------- DATABASE ENV -------

ORACLE_BASE=/oem/app/oraemdb

ORACLE_HOME=/oem/app/oraemdb/product/12.1.0.1/db

ORACLE_SID=OMRDB

ORACLE_UNQNAME=OMRDB

ORAINST=/oem/app/oraemdb/product/12.1.0.1/db/oraInst.loc

TNS_ADMIN=/oem/app/oraemdb/product/12.1.0.1/db/network/admin

TMPDIR=/oem/app/oraemdb/tmp

s111oem:~/.env$

Check if the source-in was successful and that the environment variables are in memory.

s111oem:~/.env$ env | grep ORA

ORACLE_UNQNAME=OMRDB

ORACLE_SID=OMRDB

ORACLE_BASE=/oem/app/oraemdb

ORAINST=/oem/app/oraemdb/product/12.1.0.1/db/oraInst.loc

ORACLE_HOME=/oem/app/oraemdb/product/12.1.0.1/db

s111oem:~/.env$

ENABLE ORACLE DATABASE LISTENER

If you haven’t created a database listener, please read my article Database Listener Configuration to create a new one for the database.

Now, let’s start the listener if you haven’t done so. In my case, my listener name is OMRDB.

s111oem:~/.env$ lsnrctl start OMRDB

LSNRCTL for Solaris: Version 12.1.0.1.0 - Production on 28-JUL-2014 06:11:28

Starting /oem/app/oraemdb/product/12.1.0.1/db/bin/tnslsnr: please wait...

TNSLSNR for Solaris: Version 12.1.0.1.0 - Production

System parameter file is /oem/app/oraemdb/product/12.1.0.1/db/network/admin/listener.ora

Log messages written to /oem/app/oraemdb/diag/tnslsnr/s111oem/omrdb/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=s111oem)(PORT=1601)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1601)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=s111oem)(PORT=1601)))

STATUS of the LISTENER

------------------------

Alias OMRDB

Version TNSLSNR for Solaris: Version 12.1.0.1.0 - Production

Start Date 28-JUL-2014 06:11:28

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /oem/app/oraemdb/product/12.1.0.1/db/network/admin/listener.ora

Listener Log File /oem/app/oraemdb/diag/tnslsnr/s111oem/omrdb/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=s111oem)(PORT=1601)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1601)))

The listener supports no services

The command completed successfully

s111oem:~/.env$

CREATE THE DATABASE USING THE GUI TOOL DBCA

STEP 1. Run the dbca from the command prompt.

Example:

s111oem:~$ dbca

The first screen that pops up will ask you what you want. Select - Create Database - and click next.

1 2 3 4 >Next

STEP 2. Select - Advanced Mode - as this will give you more options to create the database.

STEP 3. Choose your preferred template. In my case, I select - General Purpose database.

STEP 4. Enter the global database name usually in a form of <dbname>.<Domain name>.