CI/CD Tools for Database Developers

Before You Begin

This 4-hour lab walks you through the steps to

Create an Oracle Cloud account
Create an Autonomous Transaction Processing (ATP) Database
Create an Oracle Compute instance and install/configure
1. Git
2. Java
3. Liquibase
4. SQLcl
5. utPLSQL
6. Jenkins
Setup a Jenkins project that will keep your Database schema current and tested
Use Liquibase to make changes to your Database schema
Use utPLSQL to unit test your Database PL/SQL code

What Do You Need?

Internet Browser
GitHub Account
If you do not already have a GitHub account, create one now

Create an Oracle Always-Free Cloud Account

Go to https://www.oracle.com/cloud/free/
Click "Start for free"
Populate the forms and create an account.
Once your account is created, log in and go to the dashboard.

Create a compartment

Compartments are useful when you want to organize and isolate your cloud resources. Create a compartment for the objects used in this lab.

Click the menu icon in the upper left corner.
Scroll to the bottom, under Identity, click "Compartments".
Click "Create Compartment".
Populate the Name and Description.
Leave the parent compartment set to (root).
Click "Create Compartment"
Click the "Oracle Cloud" logo to return to the dashboard.

Create an ATP instance

You will need a database to complete the exercises. An Oracle Autonomous Database handles a lot of the background admin tasks for you so you can focus on your project.

Click "Create an ATP database" in the Autonomous Transaction Processing box.
Choose your new compartment.
Enter MyAtpDb in Display name
Enter MyAtpDb in Database name
Make sure "Transaction Processing" is selected.
Make sure "Shared Infrastructure" is selected.
Scroll down to "Create administrator credentials". Enter and confirm the ADMIN password.
Note: the Admin account is the top level user for your new database. Create a strong password and keep it secure.
Scroll to the bottom and click "Create Autonomous Database".

You will receive an email when your new ATP Database instance has been provisioned.
Locate your new database's OCID and click Copy.

Cloud Shell

Click on the Cloud Shell icon.

This will open a preconfigured VM that you will use to access and setup your project.

Working in the Oracle Cloud Shell

The instructions in this lab use nano to edit files. (You are welcome to use another editor if you prefer.)
To close and save a file in nano do the following.
1. Ctrl-X
2. Y
3. Enter
When using nano in the cloud shell use Ctrl-Shift-V or Shift-Insert to paste, rather than Ctrl-V.
At times you will see the following instruction link

Git add/commit/push

You may click the link to return to the below instructions, if you would like to copy and paste the commands.
You may also hover the link to see the commands displayed in the popup text.

Git Add Commit Push

Run the following in your Cloud Shell(ssh)
```
cd ~/db-devops-tools
git add .
git commit -m"your commit message"
git push
```

Create an environment variable for your Database OCID

Once the Cloud Shell is running, create an environment variable for your Database OCID you copied above.

export DB_OCID=<pasteYourOCIDhere>

The Oracle Autonomous Database uses an extra level of security in the form of a wallet containing access keys for your new Database.

Once your ATP Database status is Available (the yellow box turns green) you can download the wallet inside the Cloud Shell using the pre-configured OCI-CLI.

You should change the password value in this command to something more secure.
Note: This password is for the .zip file, not your database.

In your Cloud Shell
Enter the following.

oci db autonomous-database generate-wallet --autonomous-database-id ${DB_OCID} --password Pw4ZipFile --file ~/Wallet_MyAtpDb.zip

Open SQLDeveloper Web

Later, after everything is setup, you will use SQLDeveloper Web to access your database.

Click Tools
In the SQL Developer Web box, click the "Open SQL Developer Web" button

This will open SQL Developer Web in a new browser tab.
Log in as admin using the admin password you created for your Database.
Switch back to the Oracle Cloud browser tab.

Click the "Oracle Cloud" logo on the left of the menu bar to return to the dashboard.

Create a Compute instance

An Oracle Compute instance is a Cloud VM that you will use to install and run all of the software for the lab.

Click "Create a VM instance" in the Compute box.
Populate the name or keep the default.
Scroll down the the "Add SSH keys" section.
Select "Paste SSH keys".

In your Cloud Shell

Generate a new RSA key pair.

ssh-keygen -t rsa -N "" -b 2048 -C "cloud_shell" -f ~/.ssh/id_rsa

Display the public key and copy it.
```
cat ~/.ssh/id_rsa.pub
```

In the Create Compute form, paste the public key in the SSH KEYS box. If you intend to SSH into your compute instance from any other machine, you may click the "+ Another Key" button and enter the public key for that machine.
(you may also want to save a copy of the Cloud Shell private key '~/.ssh/id_rsa' on your local machine.)
DO NOT SHARE your private key. This key allows access to your compute instance.
Click "Create".
Once the Compute instance is Running, locate the Public IP Address and click Copy.
Keep this IP address handy, it will be used throughout the lab and referred to as <YourPublicIP>.
In your Cloud Shell
Create an environment variable to store the IP.
```
export COMPUTE_IP=<YourPublicIP>
```
Next, you will open ports 8080 and 8000 in your cloud Virtual Network (VNIC).
1. Click "Public Subnet"
2. Click the Security List name.
3. Click Add Ingress Rule.
4. In the SOURCE CIDR box enter
```
0.0.0.0/0
```
5. In the DESTINATION PORT RANGE box enter
```
8080
```
6. Click Add Ingress Rule.
7. Repeat for 'DESTINATION PORT RANGE' 8000.
Be Aware
This will open ports 8080 8000 for any instance using the default security list
In your Cloud Shell
Use SCP to upload the wallet .zip file (downloaded earlier) to new Compute instance.
```
scp Wallet_MyAtpDb.zip opc@${COMPUTE_IP}:/home/opc/
```
Maximize the Cloud Shell.
Use SSH to access your Compute instance from the Cloud Shell.
```
ssh opc@${COMPUTE_IP}
```
Note: After this, any steps to be completed in your Cloud Shell, Cloud Shell (ssh) or in your Compute Instance, will be completed in the ssh session. You can tell which instance you are connected to by looking at the prompt.

<user>@cloudshell: is in the cloud shell but not the ssh session.
[opc@<yourComputeName> <dir>] is in the ssh session.
If you lose your ssh connection, return to the above step and reconnect.
Feel free to use your own ssh client if you prefer.

Setup your Compute Instance

Add the software needed for the lab.

In your Cloud Shell (ssh)

Install Git

sudo yum install -y git
git --version

Setup the Database Wallet

sudo mkdir -p /opt/oracle/wallet
sudo mv Wallet_MyAtpDb.zip /opt/oracle/wallet/
sudo unzip /opt/oracle/wallet/Wallet_MyAtpDb.zip -d /opt/oracle/wallet/
echo 'export TNS_ADMIN=/opt/oracle/wallet/' >> ~/.bashrc
source ~/.bashrc

Newer versions of Oracles ojdbc driver make it much easier to access a database using the extra wallet security. To enable these features, edit the wallet/ojdbc.properties file.

sudo nano /opt/oracle/wallet/ojdbc.properties

Comment line 2
Un-comment the last 4 lines that start with '#javax.net.ssl'

Replace <password_from_console> with the password you used when you downloaded the wallet .zip file.

# Connection property while using Oracle wallets.
# oracle.net.wallet_location=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=${TNS_ADMIN})))
# FOLLOW THESE STEPS FOR USING JKS
# (1) Uncomment the following properties to use JKS.
# (2) Comment out the oracle.net.wallet_location property above
# (3) Set the correct password for both trustStorePassword and keyStorePassword.
# It's the password you specified when downloading the wallet from OCI Console or the Service Console.
javax.net.ssl.trustStore=${TNS_ADMIN}/truststore.jks
javax.net.ssl.trustStorePassword=Pw4ZipFile
javax.net.ssl.keyStore=${TNS_ADMIN}/keystore.jks
javax.net.ssl.keyStorePassword=Pw4ZipFile

Save the file

Download the Oracle Database Driver ojdbc8.jar

sudo wget https://repo1.maven.org/maven2/com/oracle/ojdbc/ojdbc8/19.3.0.0/ojdbc8-19.3.0.0.jar -O /opt/oracle/ojdbc8.jar

Install Java 8

sudo yum install -y --enablerepo=ol7_ociyum_config oci-included-release-el7
sudo yum install -y jdk1.8
java -version

Install SQLcl

You will use SQLcl to execute some setup scripts from your Compute instance.

sudo yum install -y sqlcl
alias sql="/opt/oracle/sqlcl/bin/sql"
sql -v

Install utPLSQL

Download the utPLSQL testing framework

curl -LOk $(curl --silent https://api.github.com/repos/utPLSQL/utPLSQL/releases/latest | awk '/browser_download_url/ { print $2 }' | grep ".tar.gz\"" | sed 's/"//g')

Extract the downloaded "tar.gz" file

tar xvzf utPLSQL.tar.gz

Use SQLcl to install utPLSQL
('XNtxj8eEgA6X6b6f' is the default utPLSQL password. You should change it.)

sql admin/n0tMyPassword@MyAtpDb_TP @utPLSQL/source/install_headless_with_trigger.sql ut3 XNtxj8eEgA6X6b6f DATA

Install utPLSQL-cli

utPLSQL-cli is a Java command-line client for utPLSQL v3

curl -LOk $(curl --silent https://api.github.com/repos/utPLSQL/utPLSQL-cli/releases/latest | awk '/browser_download_url/ { print $2 }' | grep ".zip\"" | sed 's/"//g')
sudo unzip utPLSQL-cli.zip -d /opt/ && sudo chmod -R u+x /opt/utPLSQL-cli
sudo cp /opt/oracle/ojdbc8.jar /opt/utPLSQL-cli/lib

Install Liquibase

Liquibase is a schema migration tool you will use to make changes to your database

wget https://github.com/liquibase/liquibase/releases/download/liquibase-parent-3.6.3/liquibase-3.6.3-bin.tar.gz
sudo mkdir /opt/liquibase
sudo tar xvzf liquibase-3.6.3-bin.tar.gz -C /opt/liquibase/
echo 'export PATH=$PATH:/opt/liquibase' >> ~/.bashrc
source ~/.bashrc
liquibase --version

Install Jenkins

Jenkins is an automation server you will use to build and deploy your project

sudo wget -O /etc/yum.repos.d/jenkins.repo https://pkg.jenkins.io/redhat/jenkins.repo
sudo rpm --import https://pkg.jenkins.io/redhat/jenkins.io.key

sudo wget -O /etc/yum.repos.d/jenkins.repo https://pkg.jenkins.io/redhat/jenkins.repo
sudo rpm --import https://pkg.jenkins.io/redhat/jenkins.io.key

sudo yum install -y jenkins

sudo systemctl start jenkins
sudo systemctl status jenkins
sudo systemctl enable jenkins

Open ports 8080 and 8000 in your compute instance's internal firewall

sudo firewall-cmd --permanent --zone=public --add-port=8080/tcp
sudo firewall-cmd --permanent --zone=public --add-port=8000/tcp
sudo firewall-cmd --reload

Generate an rsa key pair in your compute instance

This rsa key pair will be used to access your GitHub repository from the compute instance.
(This is a different key than the one used in your Cloud Shell to access this compute instance.)

Generate a new RSA key pair

ssh-keygen -t rsa -N "" -b 2048 -C "CiCd-Compute-Instance" -f ~/.ssh/id_rsa

Display the public key, copy it and save it for the GitHub step below.
```
cat ~/.ssh/id_rsa.pub
```

Setup GitHub repository

In your browser
Go to https://github.com/OsBlaineOra/db-devops-tools
Click the 'Fork' button
In your new repository
Click Settings
Add your public key
1. On the left, click 'Deploy keys'
2. Click the 'Add deploy key' button
3. Enter a title for your key 'HoL Compute Instance'
4. In the 'Key' field, past the public key you generated for this compute instance.
5. Check 'Allow write access'
6. Click 'Add key'
Add a Webhook
1. On the left, click 'Webhooks'
2. Click the Add webhook button
  
  (You may be asked to re-enter your GitHub password)
3. Use your Compute instance public IP to populate the Payload URL
```
http://<YourPublicIP>:8080/github-webhook/
```
4. Click the Add webhook button. (Ignore the error for now)
Click the 'Code' tab
Click the 'Clone or download' button
If it doesn't say 'Clone with SSH' click the 'Use SSH' link
Click the button with a clipboard icon next to the clone string to copy it

In your Cloud Shell(ssh)
Clone your new Git repository

git clone <The SSH string copied above>
cd db-devops-tools

Use SQLcl to create the database schemas

sql admin/n0tMyPassword@MyAtpDb_TP @create_schema.sql

Continue through the following sections

Setup Jenkins
Schema Migration with Liquibase
Unit test your Database code with utPLSQL

r2-lf/db-devops-tools