Kamran Agayev's Oracle Blog

Oracle Certified Master

Archive for December 5th, 2016

Step by step guide to create an Oracle Database in the Cloud

Posted by Kamran Agayev A. on 5th December 2016

In this blog post I will share the steps to create an Oracle Database in Cloud. We will create a database service, create a second database in the same machine in a silent mode, access to the cloud machine with SSH and monitor the database with OEM.

First of all, make sure you have an Oracle account. Open oracle.com and click on Register link to get a free oracle account. Next, open cloud.oracle.com, select “Compute” from Infrastructure menu and click “Try It” button to get a free trial account for 1 month. Next, login with your oracle account and register for cloud account. Make sure you don’t apply with generic email addresses like hotmail, gmail and etc. Instead, use your company address.

After successfully registering you will get an email with your credential information. Click on the link specified in the email, provide username/password and login to your cloud account. Click Oracle Database Cloud Service link.

 

image-1

 

 

Click on the link under “My Service URL” to access list of cloud services you have.

 

image-2

 

 

 

 

 

 

 

 

 

 

 

 

If you haven’t signed in yet, sign in page will appear. Use the username and password provided in the email you got during the registration and login

image-3

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Next, Oracle Cloud Services dashboard will appear.

 

image-4

 

 

 

 

 

 

 

 

 

 

 

 

 

To create and manage database instances, click on the “Open Service Console” menu of the Database tab as shown below:

 

image-5

 

 

 

 

 

 

 

 

 

In this page you will get list of database services. Here, I have two database services (myfirstdb, myseconddb). I have 7.5G memory and 150gb of storage provided for each service.

image-6

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Click Delete on the drop down menu for each service and delete both services.

 

image-7

 

 

 

 

 

 

 

 

 

 

 

 

 

After successfully deleting available instances, click on “Create Service” button to create a new database instance:

image-8

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Select “Oracle Database Cloud Service” option to create a database using a wizard. For the billing frequency you have two options: Hourly and Monthly. It doesn’t make sense when you use a trial account. So select any of them and click Next.

 

image-9

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

For the software release you have 3 options: 11.2, 12.1 and 12.2. Choose any of them and click Next.

 

image-10

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

As a software edition, you have 4 options:

Standard Edition

Enterprise Edition

Enterprise Edition – High Performance

Enterprise Edition – Extreme Performance

To get more information and features that each edition provides, check the following documentation:

 

Home / Cloud / Oracle Database Cloud Service/ Using Oracle Database Cloud Service/ About Database Cloud Service Database Deployments/ Oracle Database Software Package

https://docs.oracle.com/cloud/latest/dbcs_dbaas/CSDBI/GUID-660363B8-0E2F-4A4F-A9BD-70A43F332A16.htm#GUID-6C0B1E17-98A5-4522-A3B9-36EFA05E64F9

Select “Enterprise Edition” option and click Next

 

image-11

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

In the next page you will be asked to provide a SSH Public Key for the cloud service. For this, use Putty Key Generator executable to generate a SSH public key. Click on Generate button, move your mouse over the blank are to generate some action.

 

image-12

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The SSH key will be generated as follows. Provide a password and click on “Save private key” button to save this key as a private key

 

image-13

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

To save a key as a public key, copy the text and save it.

 

image-14

 

 

 

 

 

 

 

 

 

 

 

 

 

image-15

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Provide this file for the “SSH Public Key” field of the database service creation wizard.

image-16

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Provide the service name, database storage, select necessary compute shape and click Next.

image-17

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Review the information and click Create button to create a service

image-18

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Click “In Progress” link to check the service creation progress.

image-19

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

After a while, the service will be created successfully.

 

image-20

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Click on proddb link to open the service.

 

image-21

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Before trying to connect to the database in the cloud from outside, you should enable dblistener security rule. Open “Oracle Database Cloud Service” dashboard.

 

image-22

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Click on Network tab

image-23

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

When you click on Network tab, you will get list of security roles.

 

image-24

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Click on the menu icon for ora_p2_dblistener role and select Update.

 

image-25

 

 

 

 

 

 

 

Enable the status of this security role

 

image-26

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Enable the ora_p2_dbconsole security role to get access to OEM.

 

image-27

 

 

 

 

 

 

Now, let’s connect to the database from SSH. Provide the private key that was saved above

image-28

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Provide the IP of the virtual machine that is provided in the main page of the database cloud service and click Open.

 

image-29

 

 

 

 

 

 

 

Provide username as “oracle” and password that was provided when generating a private key using PuTTy Key Generator tool and login to the server where the database is running.  Connect to SQL*Plus and run SQL commands:

image-31

 

 

 

 

 

 

 

 

 

 

 

 

 

image-32

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

No open tnsnames.ora file and add the following entry:

 

tnsnames.ora

PRODDB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 141.144.32.70)(PORT = 1521))

(CONNECT_DATA =

(SID = PRODDB)

)

)

 

Open a command prompt, login to the database in the cloud and run SQL commands:

 

image-33

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

To open an OEM, click on the menu icon on the Database Cloud Service home page and select “Open EM Console”

 

image-34

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Provide the username and password and login

 

image-35

 

 

 

 

 

 

 

 

 

 

 

image-36

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

image-37

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

After creating and configuring a database using a wizard, I decided to create a new database in a silent mode in the same machine as follows:

 

 

[oracle@proddb dbhome_1]$ dbca -silent -createdatabase -gdbname mydb -templatename /u01/app/oracle/product/11.2.0/dbhome_1/assistants/dbca/templates/General_Purpose.dbc -sid mydb -syspassword oracle -systempassword oracle -emConfiguration none -datafileDestination /u02/app/oracle/oradata/mydb -memoryPercentage 40

 

Copying database files

1% complete

3% complete

11% complete

18% complete

26% complete

37% complete

Creating and starting Oracle instance

40% complete

45% complete

50% complete

55% complete

56% complete

60% complete

62% complete

Completing Database Creation

66% complete

70% complete

73% complete

74% complete

75% complete

76% complete

77% complete

88% complete

99% complete

100% complete

Look at the log file “/u01/app/oracle/cfgtoollogs/dbca/mydb/mydb.log” for further details.

[oracle@proddb dbhome_1]$

 

Check if the database is up and running and connect to it:

 

[oracle@proddb dbhome_1]$ ps -ef | grep smon

oracle    7040     1  0 11:40 ?        00:00:00 ora_smon_mydb

oracle    7226  2625  0 11:40 pts/1    00:00:00 grep smon

oracle   11837     1  0 10:30 ?        00:00:00 ora_smon_PRODDB

[oracle@proddb dbhome_1]$ export ORACLE_SID=mydb

[oracle@proddb dbhome_1]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Sat Dec 3 11:40:39 2016

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

 

Run free command to check the free space of the machine. We have 2 databases running on this machine, so we have only 1g free memory.

[oracle@proddb dbhome_1]$ free

total       used       free     shared    buffers     cached

Mem:       7397060    6295684    1101376     266948      66356    1364664

-/+ buffers/cache:    4864664    2532396

Swap:      4194300      29916    4164384

[oracle@proddb dbhome_1]$

After having a database service with the specific parameters, you can change the parameters anytime. Let’s add 2gb free space to the machine. Switch to the home page of the database cloud service, click on the menu icon and choose “Scale Up/Down” link

image-38

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Provide size of the new space and click “Yes, Scale Up/Down Service” button

image-39

 

 

 

 

 

 

 

 

 

 

The host will reboot and the required space will be added :

image-40

 

 

 

 

 

 

 

 

 

The new space is mounted to the new mount point (u05):

 

[oracle@proddb ~]$ df -kh

Filesystem            Size  Used Avail Use% Mounted on

/dev/xvdb3             25G   12G   12G  51% /

tmpfs                 3.6G     0  3.6G   0% /dev/shm

/dev/xvdb1            477M  148M  300M  34% /boot

/dev/xvde1             59G  7.4G   49G  14% /u01

/dev/mapper/dataVolGroup-lvol0

25G  3.8G   20G  17% /u02

/dev/mapper/fraVolGroup-lvol0

6.8G  2.1G  4.4G  32% /u03

/dev/mapper/redoVolGroup-lvol0

26G  3.1G   22G  13% /u04

/dev/xvdg1            2.0G  3.1M  1.9G   1% /u05

[oracle@proddb ~]$

Posted in Uncategorized | 3 Comments »