Using the Virtual Image Service Level of Oracle Database Cloud Service

Introduction

This post details the procedures needed to create a database using the Virtual Image Service Level of Oracle Database Cloud Service. Also, it provides verification steps for each procedure.

It draws extensively from both the A-team post Getting Started with Oracle Database Cloud Service – Virtual Image – Creating a database and Oracle’s documentation Creating a Database using the Virtual Image Service Level.

The following topics are covered:

Creating a Database Cloud Service Instance
Adding Storage to the Database Cloud Service Instance
Connecting to the Database Cloud Service Instance
Performing Operating System Set-Up Tasks
Preparing the Oracle Database Software
Creating the Oracle Database and Listener
Enabling Network Access to the Database Ports
Connecting to the Enterprise Manager Database Control
Connecting from another Cloud Service – BICS

Links to all pages referenced are also in the References section at the end of the post.

Note: These procedures are for demonstration purposes only and are not intended for enterprise production use.

Creating a Database Cloud Service Instance

This section describes the steps needed to create a Database Cloud Service Instance using the Virtual Image Service Level.

1. Log into the My Services page using the My Services URL link provided to you and your credentials. See Accessing the Oracle Database Cloud Service Console for more detail.

2. From the Welcome page, click Create Instance. See Creating a Database using the Virtual Image Service Level for more detail.

p01

3. From the available services, click Database Create.

p02

4. From the Services tab on the Oracle Database Cloud Service page, click Create Service. See Creating a Database Deployment for more detail.

p03

5. From the Create Service > Service page enter the basic information, ensure the Region is correct, Note the Service Level and click Next. The following details are used in this example.

p04

6. From the Create Service > Service Details page enter the Compute Shape. For the SSH Public Key, click Edit to have the wizard create a public/private key pair for you (or upload a public key already created). You must associate an SSH public key with the compute infrastructure supporting the deployment. An SSH public key is used for authentication when you use an SSH client to connect to a compute node associated with the deployment. When you connect, you must provide the private key that matches the public key. See Generating a Secure Shell (SSH) Public/Private Key Pair for more detail.

p05

6.a. From the SSH Public Key for VM access page Select Create a New Key and click Enter. 

p06

6.b. From the Download Keys page, Click Download. Save the zip file and click Done. Note: Remember the location as the private key is used later.

p07

7. From the Create Service > Confirm page, confirm the responses and click Create.

p08

8. From the Services tab on the Oracle Database Cloud Service page, the new instance has a status of Creating Service. Check back with the refresh button until the service is ready.

p09

 

Adding Storage to the Database Cloud Service Instance

This section describes the steps needed to add (scale-up) storage to the instance. 32 GB of storage is initially allocated to the instance for the operating system. See Storage Volumes and File System Layout for more detail. Additional storage is required for the database software, database data, and optional backups. In this example, 30 GB is allocated for database software and 2 TB for database data. See Scaling a Database Deployment for more detail. Note: Each scale-up results in a unique block storage device (volume). Also Note: “the storage volume exists until you delete the database deployment, at which time the storage volume is also deleted.”. If temporary storage is desired that can later be detached, see Adding Temporary Storage to a Database Deployment for more detail.

Repeat for each scale-up.

1. From the Services tab on the Oracle Database Cloud Service page, click on the Service Name.

p10

2. Make a note of the Public IP as this is needed later to access the instance. Click on the BarMenu icon.

p11

3. Click Scale Up/Down.

p12

4. Enter the amount of Additional Storage in GB. Note: 1024 GB is 1 TB. Then click Yes, Scale Up/Down Service.

p13

5. Repeat for additional scale-ups that are required.

Connecting to the Database Cloud Service Instance

This section describes the use of an SSH-enabled application e.g. PuTTY, to access the instance using the IP address noted earlier, e.g. 144.21.65.177. Note: the application requires the private key also noted earlier. See Connecting to a Compute Node Through Secure Shell (SSH) for more detail.

1. Launch the application and when prompted to login, use the user “opc” which is created with the instance. See Linux User Accounts for more detail.

p14

2. Optionally enter the passphrase (if there is one) associated with the public / private key pair.

p15

Performing Operating System Set-Up Tasks

These tasks are performed by the root OS user. A complete list of tasks may be viewed here.

To avoid having to use the sudo command repeatedly, issue this command to switch to the root user:

sudo –s

The following steps are used to set up the file systems needed to access the storage “scaled up” previously. In this example, u01 is used for the database software and u02 for the database data. See Creating a Database on a Virtual Image Database Deployment: Example for more detail.

Listing Block Devices

1. Use the lsblk command to display the list of block devices, noting the presence of the three volumes you created, xvdc and xvdd:

lsblk

NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
xvdb 202:16 0 32G 0 disk
├─xvdb1 202:17 0 500M 0 part /boot
├─xvdb2 202:18 0 10G 0 part
│ ├─vg_main-lv_swap (dm-0) 251:0 0 4G 0 lvm [SWAP]
│ └─vg_main-lv_root (dm-1) 251:1 0 18.7G 0 lvm /
└─xvdb3 202:19 0 19.5G 0 part
 └─vg_main-lv_root (dm-1) 251:1 0 18.7G 0 lvm /
xvdc 202:32 0 30G 0 disk
└─xvdc1 202:33 0 30G 0 part /u01
xvdd 202:48 0 2T 0 disk

Partitioning Block Devices

1. Partition the block devices using the fdisk command. Note: these block devices are stored in the /dev directory.

Repeat for each block device:

Use the fdisk command to create a single, primary partition that occupies the entire volume.

In the following use of fdisk, the responses are highlighted. These responses are:

and then u to switch off DOS-compatible mode and change display units to sectors, respectively.

and then p and then 1 to create a new partition, make it the primary partition, and make it partition number 1, respectively.

Pressing Enter twice to accept the first available sector as the start of the partition and then to accept the last available sector as the end of the partition.

to print the partition table. (Note the name of the new partition as displayed in the Device column.)

to write the partition table to disk and exit fdisk.

fdisk /dev/xvdd

Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0x497249a3.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)
WARNING: The size of this disk is 2.2 TB (2199023255552 bytes).
DOS partition table format can not be used on drives for volumes
larger than (2199023255040 bytes) for 512-byte sectors. Use parted(1) and GUID
partition table format (GPT).
WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
 switch off the mode (command 'c') and change display units to
 sectors (command 'u').
 Command (m for help): c
DOS Compatibility flag is not set
Command (m for help): u
Changing display/entry units to sectors
Command (m for help): n
Command action
 e extended
 p primary partition (1-4)
p
Partition number (1-4): 1
First sector (2048-4294967295, default 2048): (Press Enter)
Using default value 2048
Last sector, +sectors or +size{K,M,G} (2048-4294967294, default 4294967294): (Press Enter)
Using default value 4294967294
Command (m for help): p
Disk /dev/xvdd: 2199.0 GB, 2199023255552 bytes
255 heads, 63 sectors/track, 267349 cylinders, total 4294967296 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x72bc70df

 Device Boot Start End Blocks Id System
/dev/xvdd1 2048 4294967294 2147482623+ 83 Linux

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

 

2. Repeat the fdisk command for the other block device.

Creating File Systems

1. Use the mkfs command to make a filesystem of type ext4 on block device partitions.

Repeat the mkfs command for each block device partition.

mkfs -t ext4 /dev/xvdd1

mke2fs 1.43-WIP (20-Jun-2013)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
134217728 inodes, 536870655 blocks
26843532 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=4294967296
16384 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks:
 32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
 4096000, 7962624, 11239424, 20480000, 23887872, 71663616, 78675968,
 102400000, 214990848, 512000000
Allocating group tables: done
Writing inode tables: done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done

 

2. Repeat the mkfs command for the other block device partition.

Creating Directories for Mount Points

1. Use the mkdir command to create three directories: /u01 and /u02. Note: /u01 may already exist. The ls –l command confirms it is empty.

mkdir /u01

mkdir: cannot create directory `/u01': File exists

 ls -l /u01

total 0

mkdir /u02

Mounting the Partitions to Directories

1. Use the mount command to mount the three directories

mount /dev/xvdc1 /u01
mount /dev/xvdd1 /u02

2. Use the lsblk command again with options to verify.

lsblk -o NAME,UUID,MOUNTPOINT,FSTYPE

NAME UUID MOUNTPOINT FSTYPE
xvdb
├─xvdb1 50885190-d640-495c-8089-39bd67c71c25 /boot ext4
├─xvdb2 uVlKOo-8M45-t0H6-pLZF-07Ln-haP7-JolAKr LVM2_member
│ ├─vg_main-lv_swap (dm-0) bab57b1e-6cf8-4a5f-8938-5c97b3e5e410 [SWAP] swap
│ └─vg_main-lv_root (dm-1) b79b0461-8d60-40f9-875e-3e1d0c974a32 / ext4
└─xvdb3 OyaJ0I-UULQ-VKZ8-2RA3-xXnc-ex7j-0nrvDF LVM2_member
 └─vg_main-lv_root (dm-1) b79b0461-8d60-40f9-875e-3e1d0c974a32 / ext4
xvdc
└─xvdc1 1e93d7ad-a404-4e31-9f69-1668de508008 /u01 ext4
xvdd
└─xvdd1 b46edac4-035d-4b7e-95f0-a20505012221 /u02 ext4 

Changing Ownership and Permissions for the Directories

1. Use the chown and chmod commands to change the ownership and permissions. Note: 755 denotes write access for the owner (oracle), and read access for the group (oinstall) and read access for the public.

chown oracle:oinstall /u01
chown oracle:oinstall /u02
chmod 755 /u01
chmod 755 /u02

2. Use the ls command to verify.

ls -l / |grep u0

drwxr-xr-x 4 oracle oinstall 4096 Jun 30 17:34 u01
drwxr-xr-x 3 oracle oinstall 4096 Jul 5 23:05 u02 

Labeling the File Systems

1. Use the e2label command to add a label for readability to the file systems.

e2label /dev/xvdc1 db_home
e2label /dev/xvdd1 db_data

2. Use the blkid command to verify the labels.

blkid -c /dev/null

/dev/xvdb1: LABEL="/boot" UUID="50885190-d640-495c-8089-39bd67c71c25" TYPE="ext4"
/dev/xvdb2: UUID="uVlKOo-8M45-t0H6-pLZF-07Ln-haP7-JolAKr" TYPE="LVM2_member"
/dev/xvdb3: UUID="OyaJ0I-UULQ-VKZ8-2RA3-xXnc-ex7j-0nrvDF" TYPE="LVM2_member"
/dev/xvdc1: LABEL="db_home" UUID="1e93d7ad-a404-4e31-9f69-1668de508008" TYPE="ext4"
/dev/xvdd1: LABEL="db_data" UUID="b46edac4-035d-4b7e-95f0-a20505012221" TYPE="ext4"
/dev/mapper/vg_main-lv_swap: UUID="bab57b1e-6cf8-4a5f-8938-5c97b3e5e410" TYPE="swap"
/dev/mapper/vg_main-lv_root: UUID="b79b0461-8d60-40f9-875e-3e1d0c974a32" TYPE="ext4"

Adding Reboot Entries for the Mount Points

1. Use the cp command to make a copy of the /etc/fstab file:

cp /etc/fstab /etc/fstab-orig

2. Edit or append to the /etc/fstab file entries for the mount points.

The entries are:

LABEL=db_home           /u01                    ext4    defaults        1 1

LABEL=db_data             /u02                    ext4    defaults        1 1

To edit, use an editor such as vi to add the entries.

To append, use the echo command:

echo LABEL=db_home   /u01                    ext4    defaults        1 1 >> /etc/fstab
echo LABEL=db_data   /u02                    ext4    defaults        1 1 >> /etc/fstab

3. Use the cat command to verify.

cat /etc/fstab | grep u0

LABEL=db_home /u01 ext4 defaults 1 1
LABEL=db_data /u02 ext4 defaults 1 1

4. Use the exit command to exit the root session.

exit

Preparing the Oracle Database Software

Using the oracle Account

These first tasks are performed by the oracle OS user. Issue this command to switch to the oracle user:

sudo su – oracle

The following steps are used to enable the installed Oracle database software. The software is zipped and stored in the /scratch/db directory.

1. Use the ls command to see the contents:

ls /scratch/db

db11204_bits.tar.gz  db11204_seonebits.tar.gz

The db11204_bits.tar.gz file is the one used.

2. Use the cd command to change to the /u01 (DB Software) directory:

cd /u01

3. Use the tar command to expand the zip file contents.

tar -xzpf /scratch/db/db11204_bits.tar.gz

4. Use the ls command to verify.

ls app/oracle

checkpoints diag product

5. Use the exit command to exit the oracle session.

exit

Using the root Account to run post-install scripts

These tasks are performed by the root OS user.

1. Issue this command to switch to the root user:

sudo –s

2. Run the orainstRoot.sh script: 

/u01/app/oraInventory/orainstRoot.sh

Changing permissions of /u01/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.
Changing groupname of /u01/app/oraInventory to oinstall.
The execution of the script is complete

3. Run the root.sh script:

/u01/app/oracle/product/11.2.0/dbhome_1/root.sh

Check /u01/app/oracle/product/11.2.0/dbhome_1/install/root_example-vi_timestamp.log for the output of 
root script
End the root user command shell and close your connection to the compute node:

4. Use the cp command to make a backup of the root.sh script

cp /u01/app/oracle/product/11.2.0/dbhome_1/root.sh /u01/app/oracle/product/11.2.0/dbhome_1/root.sh.orig

5. Use the exit command to exit the root session.

exit

Creating the Oracle Database and Listener

These tasks are performed by the oracle OS user. Issue this command to switch to the oracle user:

sudo su – oracle

The following steps are used to run the netca and dbca utilities to create the listener and database.

This post uses the silent (command line) method. This method allows for parameters in-line or in a response file. Response files are used here.

See Running Net Configuration Assistant Using a Response File and Running Database Configuration Assistant Using a Response File for more detail.

1. Set the environmental variables. Note: The ORACLE_BASE variable ensures the creation of the data files in /u02 (db_data).

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/OPatch:$ORACLE_HOME/bin:$PATH
export ORACLE_BASE=/u02/app/oracle

2. Run netca. The default netca response file shown here is used.

$ORACLE_HOME/bin/netca -silent -responsefile $ORACLE_HOME/assistants/netca/netca.rsp >DEV1netca.log

3. Use the cat command to verify.

cat DEV1netca.log

Mon Jul 03 12:30:21 EDT 2017 Oracle Net Configuration Assistant
Parsing command line arguments:
 Parameter "silent" = true
 Parameter "responsefile" = /u01/app/oracle/product/11.2.0/dbhome_1/assistants/netca/DEV1netca.rsp
 Parameter "log" = /oracle11gHome/network/tools/log/netca.log
Done parsing command line arguments.
Oracle Net Services Configuration:
Profile configuration complete.
Oracle Net Listener Startup:
 Running Listener Control:
 /u01/app/oracle/product/11.2.0/dbhome_1/bin/lsnrctl start LISTENER
 Listener Control complete.
 Listener started successfully.
Listener configuration complete.
Oracle Net Services configuration successful. The exit code is 0

4. Use the lsnrctl command to verify the listener is running. Note: It does not support any services until the database is created.

lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 05-JUL-2017 22:58:02

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 05-JUL-2017 22:57:57
Uptime 0 days 0 hr. 0 min. 5 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/dev1-vi1/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dev1-vi1.compute-<em>&lt;domain&gt;</em>.oraclecloud.internal)(PORT=1521)))
The listener supports no services
The command completed successfully

5. Run dbca. A modified dbca response file is used. The contents are here. The default dbca response file is shown here. The response file directs dbca to use the Data_Warehouse template. The template contents are here.

$ORACLE_HOME/bin/dbca -silent -responseFile $ORACLE_HOME/assistants/dbca/DEV1dbca.rsp >DEV1dbca.log

6. Use the cat command to verify.

cat DEV1dbca.log

The ORACLE_BASE value in your environment "/u02/app/oracle" is different from the one specified during installation "/u01/app/oracle". DBCA uses ORACLE_BASE value from environment variable to derive default file locations and initialization parameters for the database. Are you sure you want to use the new ORACLE_BASE setting?
Copying database files
DBCA_PROGRESS : 1%
DBCA_PROGRESS : 3%
DBCA_PROGRESS : 37%
Creating and starting Oracle instance
DBCA_PROGRESS : 40%
DBCA_PROGRESS : 45%
DBCA_PROGRESS : 50%
DBCA_PROGRESS : 55%
DBCA_PROGRESS : 56%
DBCA_PROGRESS : 57%
DBCA_PROGRESS : 60%
DBCA_PROGRESS : 62%
Completing Database Creation
DBCA_PROGRESS : 66%
DBCA_PROGRESS : 70%
DBCA_PROGRESS : 73%
DBCA_PROGRESS : 74%
DBCA_PROGRESS : 75%
DBCA_PROGRESS : 76%
DBCA_PROGRESS : 77%
DBCA_PROGRESS : 88%
DBCA_PROGRESS : 99%
DBCA_PROGRESS : 100%
Database creation complete. For details check the logfiles at:
 /u02/app/oracle/cfgtoollogs/dbca/DEV1DW.
Database Information:
Global Database Name:DEV1DW.compute-homeoffice3.oraclecloud.internal
System Identifier(SID):DEV1DW The Database Control URL is 
https://dev1-vi1.compute-homeoffice3.oraclecloud.internal:1158/em

Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted. 
The encryption key has been placed in the file: 
/u01/app/oracle/product/11.2.0/dbhome_1/dev1-vi1.compute-<em>&lt;domain&gt;</em>.oraclecloud.internal_DEV1DW/sysman/config/emkey.ora. Ensure this file is backed up as the encrypted data will become unusable if this file is lost.

7. Use sqlplus to confirm local access.

 

export ORACLE_SID=DEV1DW
sqlplus

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 5 23:58:37 2017

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

Enter user-name: system
Enter password: enter password
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> select * from dual;
D
-
X

SQL> exit

8. Use the exit command to exit the oracle session.

exit

Enabling Network Access to the Database Ports

By default, the database ports are closed to the public. They can be opened through the use of an existing or new rule. See Enabling Access to a Compute Node Port for more detail.

1. Enable Access Rules. From the Services tab on the Oracle Database Cloud Service page, click on the BarMenumenu.

p17

2. Click on Access Rules

p18

3. Enable two ports for access: ora_p2_dbconsole and ora_p2_dblistener.

For each, click on the BarMenu menu to the right of the rule. Then click Enable. And then Confirm the request.

p19

Connecting to the Enterprise Manager Database Control

This verifies Internet Access to port 1158. See Accessing Enterprise Manager 11g Database Control for more detail.

1. Using a browser, open the Database Control console using the URL https://<public ip address>:1158/em

2. Enter the User Name and the Password and click Login.

P20

3. View the Database Control Home Page showing the instance database is up.

P21

Connecting from another Cloud Service – BICS

Follow the steps in this guide Managing Database Connections.

1. Login into your Business Intelligence Cloud Service (BICS) domain

2. From the My Services page, click on the BICS deployment that needs the connection

3. From the Service Details page, click on Open Service Console
P22

4. From the Service Console, click on Database Connections
P23

5. From the Database Connections page, click on New Connection
p24

6. From the New Connection page, enter the details and click Test

p25

7. See the “Connection test successful” message. Click OK
P26

Summary

This post details the procedures necessary to create a database using the Virtual Image Service Level of Oracle Database Cloud Service. Also, it provides verification steps for each procedure.
In addition, procedures are included to access the Enterprise manager Database Control Console and to create a BICS Database Connection.
For more BICS, OAC and BI best practices, tips, tricks, and guidance that the A-Team members gain from real-world experiences working with customers and partners, visit Oracle A-Team Chronicles for BICS and Oracle A-Team Chronicles for Oracle Analytics Cloud.

References

Getting Started with Oracle Database Cloud Service – Virtual Image – Creating a database
Creating a Database using the Virtual Image Service Level
Accessing the Oracle Database Cloud Service Console
Creating a Database Deployment
Generating a Secure Shell (SSH) Public/Private Key Pair
Storage Volumes and File System Layout
Scaling a Database Deployment
Adding Temporary Storage to a Database Deployment
Connecting to a Compute Node Through Secure Shell (SSH)
Linux User Accounts
Creating a Database on a Virtual Image Database Deployment: Example
Running Net Configuration Assistant Using a Response File
Running Database Configuration Assistant Using a Response File
Enabling Access to a Compute Node Port
Accessing Enterprise Manager 11g Database Control
Managing Database Connections
Oracle A-Team Chronicles for BICS
Oracle A-Team Chronicles for Oracle Analytics Cloud

 

Add Your Comment