Create an Oracle database in an Azure VM

In this tutorial, we will learn how to set up an Oracle database on a remote server and access it by using our browser.

7 min read

Oracle Database is a multi-model database management system produced and marketed by Oracle Corporation. It is a database commonly used for running online transaction processing, data warehousing and mixed database workloads.

When it comes to set up and running the Oracle database on a virtual machine or server then it could be a little tricky and tough sometimes.

In this tutorial, we will learn how to set up an Oracle database on a remote server and access it by using our browser.



Create virtual machine in azure

To create a virtual machine in Microsoft azure you should have an Azure account. After log into the account click on the Create a resource button.

Create a resource in Azure
Click on Create a resource button in Azure Portal

After going inside the marketplace search for Oracle. You will see many options for many editions of the Oracle database. We will create an Oracle Database 12.1.0.2 Enterprise Edition.

Oracle Database 12.1.0.2 Enterprise Edition
Select Oracle Database 12.1.0.2 Enterprise Edition

After selecting the option you will see a new window. Simply click on create button and let the process begin.

create oracle database vm
Click on create button 

After clicking on create, fill all the necessary fields and click on Review+Create

Review and create the database
Fill all the fields and click on Review + create

After the validation click on the create button again. Now we have our oracle database virtual machine in the deployment process. After successful deployment, we will access our VM using our CLI and make some configuration to make it usable and accessible.

After the successful deployment, we will see the Go to resource option.

azure vm deployed
Click on Go to resource

After clicking on the Go to resources option you will see a window that contains all details about the oracle database virtual machine including a Public IP Address.

Cheers! We have completed the first stage of the database deployment. Now we will make some necessary configurations in our database VM to make it accessible.



Connect to VM using CLI

$ ssh azureuser@<publicIpAddress>
After executing the command you will be logged into VM


Create the database

The Oracle software is already installed on the Marketplace image. Create a sample database as follows.

  1. Switch to the oracle user, then start the Oracle listener:
$ sudo -su oracle
$ lsnrctl start

The output is similar to the following:

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-NOV-2020 08:27:42

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/12.1.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Log messages written to /u01/app/oracle/diag/tnslsnr/thelovekesh/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=thelovekesh.wqkfrhyq2wsuxdjccolz0wd3ee.bx.internal.cloudapp.net)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                07-NOV-2020 08:27:43
Uptime                    0 days 0 hr. 0 min. 1 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/diag/tnslsnr/thelovekesh/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=thelovekesh.wqkfrhyq2wsuxdjccolz0wd3ee.bx.internal.cloudapp.net)(PORT=1521)))
The listener supports no services
The command completed successfully

2.  Create a data directory for the Oracle data files

mkdir /u01/app/oracle/oradata

3.  Create the database:

dbca -silent \
       -createDatabase \
       -templateName General_Purpose.dbc \
       -gdbname cdb1 \
       -sid cdb1 \
       -responseFile NO_VALUE \
       -characterSet AL32UTF8 \
       -sysPassword OraPasswd1 \
       -systemPassword OraPasswd1 \
       -createAsContainerDatabase true \
       -numberOfPDBs 1 \
       -pdbName pdb1 \
       -pdbAdminPassword OraPasswd1 \
       -databaseType MULTIPURPOSE \
       -automaticMemoryManagement false \
       -storageType FS \
       -datafileDestination "/u01/app/oracle/oradata/" \
       -ignorePreReqs

It takes a few minutes to create the database.

You will see output that looks similar to the following:

    Copying database files
    1% complete
    2% complete
    8% complete
    13% complete
    19% complete
    27% complete
    Creating and starting Oracle instance
    29% complete
    32% complete
    33% complete
    34% complete
    38% complete
    42% complete
    43% complete
    45% complete
    Completing Database Creation
    48% complete
    51% complete
    53% complete
    62% complete
    70% complete
    72% complete
    Creating Pluggable Databases
    78% complete
    100% complete
    Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/cdb1/cdb1.log" for further details.

4.  Set Oracle variables

Before you connect, you need to set two environment variables: ORACLE_HOME and ORACLE_SID.

    ORACLE_SID=cdb1; export ORACLE_SID

You also can add ORACLE_HOME and ORACLE_SID variables to the .bashrc file. This would save the environment variables for future sign-ins. Confirm the following statements have been added to the ~/.bashrc file using editor of your choice.

# Add ORACLE_SID. 
export ORACLE_SID=cdb1 


Oracle EM Express connectivity

For a GUI management tool that you can use to explore the database, set up Oracle EM Express. To connect to Oracle EM Express, you must first set up the port in Oracle.

  1. Connect to your database using sqlplus:
sqlplus / as sysdba

2.  Once connected, set the port 5502 for EM Express

exec DBMS_XDB_CONFIG.SETHTTPSPORT(5502);

3.  Open the container PDB1 if not already opened, but first check the status:

select con_id, name, open_mode from v$pdbs;

The output is similar to the following:

  CON_ID NAME                           OPEN_MODE 
  ----------- ------------------------- ---------- 
  2           PDB$SEED                  READ ONLY 
  3           PDB1                      READ WRITE

4.  If the OPEN_MODE for PDB1 is not READ WRITE, then run the followings commands to open PDB1:

 alter session set container=pdb1;
 alter database open;

You need to type quit to end the sqlplus session and type exit to logout of the oracle user.



Automate database startup and shutdown

The Oracle database by default doesn't automatically start when you restart the VM. To set up the Oracle database to start automatically, first sign in as root. Then, create and update some system files.

  1. Sign on as root
sudo su -

2.  Using your favorite editor, edit the file /etc/oratab and change the default N to Y:

cdb1:/u01/app/oracle/product/12.1.0/dbhome_1:Y
How to edit the /etc/oratab file in Azure VM for Oracle database
In this tutorial we will learn how to edit /etc/oratab file to automate the startup/shutdown of an Oracle database.
Learn more about editing etc/oratab

3.  Create a file named /etc/init.d/dbora and paste the following contents:

#!/bin/sh
# chkconfig: 345 99 10
# Description: Oracle auto start-stop script.
#
# Set ORA_HOME to be equivalent to $ORACLE_HOME.
ORA_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
ORA_OWNER=oracle

case "$1" in
'start')
    # Start the Oracle databases:
    # The following command assumes that the Oracle sign-in
    # will not prompt the user for any values.
    # Remove "&" if you don't want startup as a background process.
    su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME" &
    touch /var/lock/subsys/dbora
    ;;

'stop')
    # Stop the Oracle databases:
    # The following command assumes that the Oracle sign-in
    # will not prompt the user for any values.
    su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME" &
    rm -f /var/lock/subsys/dbora
    ;;
esac
How to create /etc/init.d/dbora file in Azure VM for Oracle database
In this tutorial we will learn how to create and edit /etc/init.d/dbora file to automate the startup/shutdown of an Oracle database.
Learn more about creating /etc/init.d/dbora

4.  Change permissions on files with chmod as follows:

chgrp dba /etc/init.d/dbora
chmod 750 /etc/init.d/dbora

5.  Create symbolic links for startup and shutdown as follows:

ln -s /etc/init.d/dbora /etc/rc.d/rc0.d/K01dbora
ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/S99dbora
ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/S99dbora

6.  To test your changes, restart the VM:

reboot


Open ports for connectivity

The final task is to configure some external endpoints.

1.  Click on networking from the options available.

azure vm networking
Click on networking options

2.  Now click on Add inbound port rule and add the following rules in your networking as follows:



Connect to EM Express from your browser

Make sure your browser is compatible with EM Express (Flash install is required):

https://<VM ip address or hostname>:5502/em

You can log in by using the SYS account, and check the as sysdba checkbox. Use the password OraPasswd1 that you set during installation.

Screenshot of the Oracle OEM Express login page

After successfully login you will see a screen like this:

oracle database
Oracle Running Database

We have successfully setup Oracle Database in Azure VM. If you face any problem feel free to reach me on twitter.


Featured Oracle

Connect Oracle Database to Datagrip IDE

In this tutorial, we will connect the Oracle database to the DataGrip IDE.

2 min read
Featured Oracle

How to install Oracle database in Microsoft Azure VM

In this tutorial, we will learn how to set up an Oracle database on a remote server and access it by using our browser.

7 min read