Oracle RDBMS

How to build OCI Infrastructure Environments with Ansible

The Oracle provided Ansible module gives us the opportunity to provision and configure Oracle Cloud Infrastructure resources on an automated base. The Ansible basic setup is very easy and the Oracle provided example playbooks in Git are a good base to start with your infrastructure automation project. Oracle provides Ansible example playbooks for

  • Block Volumes
  • Compute 
  • Database
  • File Storage
  • IAM
  • Load Balancer
  • Private Subnets with VPN
  • Delete Objects
  • etc.

In this blog post, I will show you how easy it is to bring Ansible and the Oracle Cloud Infrastructure together. 

Requirements

  • A local machine to install Ansible and the required software and modules, in my case it’s an Oracle Linux 7 virtual machine with Internet access.
  • An Oracle Cloud Infrastructure Account with permissons to create new resources.

Steps to configure Ansible and OCI

  1. Install and configure the Oracle Cloud Infrastructure Python SDK
  2. Install and configure Ansible
  3. Download and configure the OCI modules for Ansible
  4. OCI Test Run

Install and configure the Oracle Cloud Infrastructure Python SDK

In this step, the OCI Python SDK will be installed an configured. The new created SSH public key has to be added in the OCI console for further actions.  As OS user root we create a new operating system user called oci for Oracle Cllud Infrastrcuture actions and give him sudo privileges.

Create a User and SSH Keys

Add this line in /etc/sudoers.

Login as user oci, create a new SSH key and download an configure the OCI SDK. Protect your keys.

Show the public key and add it in the OCI console to your cloud account user.

 

The OCI Configuration File

As user oci, create the Oracle Cloud Infrastructure configuration file

Content of the file – for example in region Frankfurt and with the created SSH key file from above.

Change the file permissions.

Install the Oracle Cloud Infrastructure Python SDK

Test

Command to list all instances in the selected compartment.

Install and configure Ansible

As user oci, download and install Ansible and Git.

Set up the module directory.

Install additional packages.

This upgrade step is required, otherwise the public key creation in the OCI Ansible module fails (for example when you want to launch a new Compute instance).

Download and configure the OCI modules for Ansible

As user oci, download the Ansible modules from Git.

Show the content.

Change into the new created directory and execute the configuration script install.py.

OCI Test Run

We copy the example playbook to launch a Compute cloud instance into the local folder and run the playbook. The Oracle provided playbook needs three variables:

SAMPLE_AD_NAME Availability Domain, e.g. EUZg:EU-FRANKFURT-1-AD-1
SAMPLE_IMAGE_OCID OCID of the selected OS – see https://docs.cloud.oracle.com/iaas/images/ to list all available images
SAMPLE_COMPARTMENT_OCID OCID of your compartment – OCI > Identity > Compartments

 

Create a working directory and copy the example playbook.

Set variables.

Run the playbook

Attention: All OCI resources are created and afterwards terminated immediately. If you don’t want to terminate them, comment out this line in file sample.yaml.

Execute the Ansible playbook. The infrastructure will be created step by step. Key generation, network configuration, firewall rule setup, instance creation etc. is all automated.

Ready to Use

After a few minutes, a complete infrastructure for an OCI compute instance is created and the instance is ready to connect. 

The required SSH keys for the terminal connection were generated in a subdirectory of /tmp with the prefix ansible. In my example, the private and the public SSH key are located in /tmp/ansible.v6ckX0cert.

Links

Summary

The Oracle provided Ansible playbooks are a good entry point to start with OCI automation. I am already working at the next tasks to make my work easier with more variables and simplified playbooks. And finally I want to integrate it in Ansible AWX. Well done Oracle!

Oracle Autonomous Transaction Processing – Move your Data with Oracle Data Pump – Part 3

In this blog post serie which has three parts, I want to describe how data will be uploaded from an on-premises environment into the Oracle Autonomous Transaction Processing database using Oracle Data Pump. 

Oracle Import Prerequisites

Credentials

To get acccess to the dump file on the Oracle Object Storage, a credential has to be created in the Oracle Autonomous Transaction Processing database with the DBMS_CLOUD procedure. For more information about the package, see the blog post from Christian Antognini – DBMS_CLOUD Package – A Reference Guide.

The DBMS_CLOUD procedure needs a password value which is the token from the user account. If you don’t now your token, create a new one. Go to Identity – Users – your username and click on the left side on “Auth Tokens”. Create a new token by click on “Generate Token”. The random generated string is the value for the DBMS_CLOUD password.

Enter a name for the token and click on “Generate Token”.

Note your generated token and “Close” the window.

Login into the Autonomous Transaction Processing database as admin user and create a new credential called ATPCS_CRED.

A new Database User called HRATP

In the ATP, we create a new user called HRATP. The tablespace DATA is the default tablespace in an Autonomous Transaction Processing database and does not have to be defined.

Oracle Data Pump Import

The impdp tool is part of my Instant Client installation in Part 1 of this blog serie. Oracle recommends to set parameters like 

  • partition_options=merge
  • transform=segment_attributes:n
  • transform=dwcs_cvt_iots:y
  • transform=constraint_use_default_index:y
  • exclude=index, cluster, indextype, materialized_view, materialized_view_log, materialized_zonemap, db_link

Two new Oracle Data Pump parameters to work with the Oracle cloud databases are credential and and dumpfile.

  • credential: The DBM_CLOUD created credential
  • dumpfile: The URL where the dumpfile is located

Attention

The URL provided by the Object Storage menu cannot be accessed directly by impdp:

The URL has to be mapped from objectstorage to swiftobjectstorage:

to

Data Pump Import Execution with REMAP of Schema and Tablespace

Start of the Oracle Data Pump job from my Windows client:

The message about the existing user can be ignored. 

Data Pump Logfile

The logfile of the import process cannot be access directly, it has to be moved into the Object Storage with the DBMS_CLOUD package first.

Now the file can be access in the Object Storage menu:

Connect as new User HRATP to verify the Data

Summary of Part 3

If all prerequistes are met, the data transfer with Oracle Data Pump is easy to configure and easy to handle. Take care about the token, only 2 token per user can be generated. If you losed it, you have to delete a existing token, rebuild it and re-create the credentials before you can import data again.

Oracle Autonomous Transaction Processing – Move your Data with Oracle Data Pump – Part 2

In this blog post serie which has three parts, I want to describe how data will be uploaded from an on-premises environment into the Oracle Autonomous Transaction Processing database using Oracle Data Pump. 

 

Oracle Data Pump Export on On-Premises Database

Oracle recommends the following settings for the Oracle Data Pump export job. If you use already an 18c database, you have to set the version parameter to 12.2 to avoid this error during ATP import process: ORA-39358: Export dump file version 18.0.0 not compatible with target version 12.2.0.

My expdp export job for the schema HR:

The example schema contains these objects – output from expdp job

Transfer the Dump File Set into the Oracle Cloud

The export dump file has to be transferred into the Oracle Cloud Object Storage for later usage. First we have to create an Object Storage Bucket. Login into Object Storage menu and click on the “Create Bucket” button. 

Enter a Bucket Name and click on “Create Bucket”.

Enter the new created bucket. Click on the bucket name link or the “three bullets” on the right side to view the details.

Upload Data

Upload the export dump file, click on “Upload Object”.

“Browse” local for the Oracle Data Pump export file and click on “Upload Object”.

Object Details

Click on object “Details” to verify the object on the Object Storage. 

 

Note the URL. The URL will be used later for the import process.

Summary of Part 2

Upload objects into the Oracle Cloud Object Storage is very easy. If you don’t want to use the browser functionality, there are other possibilities to upload files like the API.

Now the export dump file is ready to import into the Autonomous Transaction Processing Database.