Quickly set up an Oracle environment on GCP
Tags: oracle, gpc, database, gce
Published: 2020-01-02 13:53:20

This quick-start guide is part of a series that shows how to set up databases on Google Cloud Platform, for developing and testing purposes.

This guide will show you how to create an Oracle environment running inside your Google Cloud Project.

## Create a Compute Engine VM

Using Cloud Shell:
```
# Create the Oracle GCE instance
gcloud compute instances create oracle \
  --zone=us-central1-c \
  --machine-type=n1-standard-1 \
  --image-project=rhel-cloud --boot-disk-size=20GB \
  --image=rhel-7-v20190618 \
  --boot-disk-type=pd-standard \
  --boot-disk-device-name=oracle \
  --scopes=cloud-platform
```

>Note: The oracle express edition has a free license for demonstrating and testing purposes, but the user needs to accept it, so you must download the binary at: https://www.oracle.com/database/technologies/appdev/xe/quickstart.html
Click on the link for Oracle Linux: Download oracle-database-xe-18c-1.0–1.x86_64.rpm

## Configure your VM with Oracle

Using Cloud Shell:
```
# Connect to the Oracle VM
gcloud compute ssh --zone=us-central1-c oracle

# Copy the oracle binary, and use the binary that you downloaded at 
# this step, change it to your bucket, you can also upload it        
# manually using cloud shell
gsutil cp gs://oracle_xe_binaries/oracle-database-xe-18c-1.0-1.x86_64.rpm .

# Get the oracle pre install binary
curl -o oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm

# Install required binary
sudo yum -y localinstall oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm

# Install oracle
sudo rpm -ivh oracle-database-xe-18c-1.0-1.x86_64.rpm

# Create initial config
sudo /etc/init.d/oracle-xe-18c configure

# You will be prompted with the message: Specify a password to be   
# used for database accounts.

# Choose your password and save it
# Wait for the message: 
# Connect to Oracle Database using one of the connect strings
```

## Load your Oracle database with data

Using Cloud Shell:
```
# Connect to the Oracle VM
gcloud compute ssh --zone=us-central1-c oracle

# Set up Oracle environment
export ORACLE_SID=XE 
export ORAENV_ASK=NO 
. /opt/oracle/product/18c/dbhomeXE/bin/oraenv

# You should receive the message: The Oracle base has been set to   
# /opt/oracle/product/18c/dbhomeXE

# Install git, press y when prompted
sudo yum install git

# Go to your home directory
cd ~

# Clone schema Repo
git clone https://github.com/oracle/db-sample-schemas.git

# Go to customers schema directory
cd ~/db-sample-schemas/customer_orders

# Start the Oracle sqlplus Session, change to your password
sqlplus system/YOURPASS@oracle/XEPDB1

# Run create schema and populate procedures
@co_main copw oracle/XEPDB1 users temp

# You should be presented with some query results, look for the     
# value: 433 rows selected
# Quit sqlplus
quit
```


### Connect to the Oracle instance using a client

#### Set Oracle client for Linux (Cloud Shell)

Download the zip file and send to your gcs bucket
https://oracle.github.io/odpi/doc/installation.html#linux

```
# Get the zip from your bucket
gsutil cp gs://oracle_xe_binaries/instantclient-basic-linux.x64-19.5.0.0.0dbru.zip .

# Unzip it
unzip instantclient-basic-linux.x64-19.5.0.0.0dbru.zip

# Set Oracle library ENV Var on the unzip dir
export LD_LIBRARY_PATH=/oracleclient/bin/instantclient_19_5
```

#### Set Oracle client for Mac

Download the zip file and send to your gcs bucket
https://oracle.github.io/odpi/doc/installation.html#macos

```
# Get the zip from your bucket
gsutil cp gs://oracle_xe_binaries/instantclient-basic-macos.x64-19.3.0.0.0dbru.zip .

# Unzip it
unzip instantclient-basic-macos.x64-19.3.0.0.0dbru.zip

# Set Oracle library ENV Var on the unzip dir
export LD_LIBRARY_PATH=/oracleclient/bin/instantclient_19_3
```

## Booting the Compute Engine VM gracefully

```
# Start the Compute Engine VM
gcloud compute instances start oracle --zone=us-central1-c

# Connect to the Oracle VM
gcloud compute ssh --zone=us-central1-c oracle

# Start the oracle listener
sudo /etc/init.d/oracle-xe-18c start

# Wait for the message: Oracle Database instance XE started.
```

And that's it!

> Be careful, you pay Red Hat Entreprise Linux (rhel) licence every day that the VM run (about $0.55 every day). Of course, in addition of the GCP VM cost.

If you have difficulties, don’t hesitate reaching out. I would love to help you!