Skip to Main Content

Infrastructure Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to use Terraform with Oracle Linux and Oracle OpenStack 4.0

SHayler-OracleMar 16 2018 — edited Oct 10 2018

Introduction

We will discuss the use of infrastructure as a code software to deploy Oracle Linux within an Oracle OpenStack 4.0 private cloud. Infrastructure as a code is a process where datacenter computing can be provisioned using machine-readable definition files. Infrastructure as a code can replace traditional tools and techniques such as manual systems administration and interactive UI and command line. Terraform is open-source software that allows users to define complex datacenter infrastructure in a high-level configuration language used within a supported private or public cloud using API’s.

Oracle Linux provisioning with Oracle OpenStack 4.0 and Terraform

Using Oracle Linux 7, installing Terraform is easy; simply enable ol7_developer yum channel, then run yum install terraform. Once you have installed Terraform, users can create configuration files to suit their end configuration using examples and documentation here. Once the files are completed and then checked via Terraform utilities, the software talks to the Oracle OpenStack 4.0 via APIs and can test or dry run the desired configuration before building based upon the configuration files. Terraform keeps track of what it deploys and users can re-create entities for example removed by hand. Terraform can also remove the end configuration. For this example, I have an Oracle OpenStack 4.0 private cloud setup with a project, users and Oracle Linux images. I have a separate server running Oracle Linux 7 where I will install Terraform, create the configuration files and drive the building of entities within the Oracle OpenStack 4.0 Cloud. The setup of the Oracle OpenStack 4.0 private cloud is outside the scope of this paper. Oracle OpenStack 4.0 documentation is available here.

Example Oracle Linux provisioning flow with Oracle Cloud Infrastructure and Terraform

Terraform Client Installation

For Oracle Linux 7 we simply perform the following to install Terraform:

As the root user or using sudo edit /etc/yum.repos.d/public-yum-ol7.repo and if not present add the following to the file:

[ol7_developer]

     name=Oracle Linux $releasever Development Packages ($basearch)

     baseurl=http://yum.oracle.com/repo/OracleLinux/OL7/developer/$basearch/

     gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle

     gpgcheck=1

     enabled=1

    If the entry does exist then change enabled=1 as per the example above.

Run: sudo yum install terraform

There are frequent updates of Terraform packages; therefore, we recommend regular yum updates to enable any new features.Terraform uses the concept of providers and holds a list of these here. The provider is a method of interacting with the end “provider” using the terraform utilities. The OpenStack provider is here which contains the provider components.

Create an SSH key to access the Oracle Linux instances

We will create and use a private key in our Terraform configuration files allowing us to access our Oracle Linux instances running within the Oracle OpenStack 4.0 cloud. For further information, reference the Oracle Linux 7 documentation. Using a passphrase encrypts the key making it impossible to use even if someone were to obtain the private key file.

Firstly, we need to create a directory to store the keys:

mkdir ~/.key

cd ~/.key

Next, we create a key using ssh-keygen:

ssh-keygen -t rsa -f openstack.key

This will generate a private and public key pair:

ls

openstack.key  openstack.key.pub

Creating the Terraform configuration files

We recommend that you create directory areas within your home directory for each single or group of instances you wish to create.

mkdir ~/terraform

Terraform has the concept of a variables file where you can store common items. For this exercise I will not use a variables file but will use an RC script (OracleOpenStackRC.sh), that when sourced provides all the access details needed to drive the Oracle OpenStack cloud using the API.

Within this example file, we have the following:

#!/usr/bin/env bash

# To use an OpenStack cloud you need to authenticate against the Identity

# service named keystone, which returns a **Token** and **Service Catalog**.

# The catalog contains the endpoints for all services the user/tenant has

# access to - such as Compute, Image Service, Identity, Object Storage, Block

# Storage, and Networking (code-named nova, glance, keystone, swift,

# cinder, and neutron).

#

# *NOTE*: Using the 3 *Identity API* does not necessarily mean any other

# OpenStack API is version 3. For example, your cloud provider may implement

# Image API v1.1, Block Storage API v2, and Compute API v2.0. OS_AUTH_URL is

# only for the Identity API served through keystone.

export OS_AUTH_URL=http://10.3.12.30:5000/v3

# With the addition of Keystone we have standardized on the term **project**

# as the entity that owns the resources.

export OS_PROJECT_ID=57cb144888e74657911e20eee11b235b

export OS_PROJECT_NAME="My_Project"

export OS_USER_DOMAIN_NAME="Default"

if [ -z "$OS_USER_DOMAIN_NAME" ]; then unset OS_USER_DOMAIN_NAME; fi

# unset v2.0 items in case set

unset OS_TENANT_ID

unset OS_TENANT_NAME

# In addition to the owning entity (tenant), OpenStack stores the entity

# performing the action as the **user**.

export OS_USERNAME="myuser"

# With Keystone you pass the keystone password.

echo "Please enter your OpenStack Password for project $OS_PROJECT_NAME as user $OS_USERNAME: "

read -sr OS_PASSWORD_INPUT

export OS_PASSWORD=$OS_PASSWORD_INPUT

# If your configuration has multiple regions, we set that information here.

# OS_REGION_NAME is optional and only valid in certain environments.

export OS_REGION_NAME="RegionOne"

# Don't leave a blank variable, unset it if it was empty

if [ -z "$OS_REGION_NAME" ]; then unset OS_REGION_NAME; fi

export OS_INTERFACE=public

export OS_IDENTITY_API_VERSION=3

When downloaded from the Oracle OpenStack UI this file provides all the access details for the private cloud, also when sourced captures the password for the Oracle OpenStack user for Terraform to drive the API. Further details on the RC scripts are available from the OpenStack documentation.

We now need to create some *.tf files which are the code which will build our infrastructure within the Oracle OpenStack private cloud. These files are specific to the entity or entities that are required. For example, you could have one to create the Virtual Cloud Network and another to create the Oracle Linux 7 instance. You can number them for ease of reference; for example, I have the following *.tf files:

01_key_pair.tf 02_create_sec_group.tf 03_create_network.tf create_instance.tf

If we look at 01_key_pair.tf:

#

# Resource - KeyPair

# Creates a new keypair in our openstack tenant.

# Will show up in OpenStack as "tf-keypair-1"

# Can be referenced elsewhere in terraform configuration as "keypair1"

#

resource "openstack_compute_keypair_v2" "keypair1" {

    name = "tf-keypair-1"

    public_key = "ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQC+4Rq9aCHyZs+y+soDd8jAcGwFlT+pLgvuYmlp4qRBvJfWXXuJQ9s6YPPJozkBqFTXQ1L0pWLnY7DMy7LAiJLvmpW/1gP2B4pPCAe4lrTFvK+sIL9Yulazv+S2GniG8lBvLgjezJppaouL9GiAhZA9nltLYrGh/vLJC6xiCaLv2+ydiHM3sWoVh5P6kXRZh5h3ZWAz232vbLvWiaa1aDFTonASwbARhiwSeIm/AQTyhd3+zpDtwIBr3yydUmC1cDj+z7Dpy8p0U3WeCilV0aL4k1YHzpoxLEGhC8rcKN8Sp7bsrRliZvRjR1e8lTT2lsOQYq1p/51RpQO8iRGdWLgp simon@ol7"

}

The 01_key_pair.tf file creates a key pair for the Oracle OpenStack private cloud tenant. This key pair is used to access any instances we create. Further details on this resource are available here. The required fields are name and public_key. Name should be a unique name for the key pair and the public_key should be a pre-generated OpenSSH-formatted public key.  We need to copy the key we previously created and insert into the script.

The 02_create_sec_group.tf file creates an example security group, which allows access for SSH, and SSL.  Rules are added or removed as required to fit your local security requirements.

#

# Create a security group

#

resource "openstack_compute_secgroup_v2" "tf_sec_1" {

    region = ""

    name = "tf_sec_1"

    description = "Security Group Via Terraform"

    rule {

        from_port = 22

        to_port = 22

        ip_protocol = "tcp"

        cidr = "0.0.0.0/0"

    }

    rule {

        from_port = 1

        to_port = 65535

        ip_protocol = "tcp"

        self = true

    }

    rule {

        from_port = 443

        to_port = 443

        ip_protocol = "tcp"

        cidr = "0.0.0.0/0"

    }

}

The 03_create_network.tf file creates the Virtual Cloud Network. The key parts are as follows:

•    Create a new Virtual Cloud Network with CIDR and bring to an UP state

•    Create a new Internet Gateway using the external_network_id field which can be discovered using the OpenStack UI or CLI tools

•    Create and attach new Router to the Virtual Cloud Network

•    Create a Subnet

•    Create two floating IP’s from the external pool (already configured which can be discovered using the OpenStack UI or CLI)

#

# Create a Network

#

resource "openstack_networking_network_v2" "tf_network" {

    name = "tf_network"

    admin_state_up = "true"

}

#

# Create a subnet in our new network

# Notice here we use a TF variable for the name of our network above.

#

resource "openstack_networking_subnet_v2" "tf_net_sub1" {

    name = "tf_net_sub1"

    network_id = "${openstack_networking_network_v2.tf_network.id}"

    cidr = "192.168.1.0/24"

    ip_version = 4

}

#

# Create a router for our network

#

resource "openstack_networking_router_v2" "tf_router1" {

    name = "tf_router1"

    admin_state_up = "true"

    external_network_id = "eaed9ac5-aace-464f-969c-881bccf75544"

}

##

# Attach the Router to our Network via an Interface

#

resource "openstack_networking_router_interface_v2" "tf_rtr_if_1" {

    router_id = "${openstack_networking_router_v2.tf_router1.id}"

    subnet_id = "${openstack_networking_subnet_v2.tf_net_sub1.id}"

}

#

# Create some Openstack Floating IP's for our VM's

#

resource "openstack_compute_floatingip_v2" "fip_1" {

    pool = "external"

}

resource "openstack_compute_floatingip_v2" "fip_2" {

    pool = "external"

}

The create_instance.tf file creates an Oracle Linux 7.4 instance. The key parts are as follows:

•    Create a 1GB block volume

•    Create an OL7.4 instance using the ol74 image

•    Create a medium flavor instance (already configured)

•    Attach our key_pair to the instance to allow SSH access

•    Assign our security group to the instance

•    Attach our instance to the new Virtual Cloud Network

•    Attach the 1GB volume to the instance

•    Attaches a floating IP to the instance

#

# Create a VM Instance on Ol74 with an attached volume

#

resource "openstack_blockstorage_volume_v2" "tf_vol" {

  name = "tf_vol"

  size = 1

}

resource "openstack_compute_instance_v2" "tf_ol74" {

    name = "tf_ol74"

    image_name = "ol74"

    flavor_name = "medium"

    key_pair = "tf-keypair-1"

    security_groups = ["tf_sec_1"]

    metadata {

        demo = "metadata"

    }

    network {

        name = "tf_network"

    }

}

resource "openstack_compute_volume_attach_v2" "attached" {

  instance_id = "${openstack_compute_instance_v2.tf_ol74.id}"

  volume_id = "${openstack_blockstorage_volume_v2.tf_vol.id}"

}

resource "openstack_networking_floatingip_v2" "fip_1" {

  pool = "external"

}

resource "openstack_compute_floatingip_associate_v2" "fip_1" {

  floating_ip = "${openstack_networking_floatingip_v2.fip_1.address}"

  instance_id = "${openstack_compute_instance_v2.tf_ol74.id}"

}

There are multiple options for the creation and manipulation of components such as:

•    Obtaining information regarding data sources such as DNS, images, flavors and networks

•    Creating and attaching block storage

•    Creating and configuring compute instances

•    Creating and configuring databases

•    Creating and configuring DNS

•    Creating and configuring Identity services

•    Creating and configuring images

•    Creating and configuring Networking

•    Creating and configuring load balancing services

•    Creating and configuring firewalls

•    Creating and configuring object storage

Examples of usage for these components are available within the Terraform OpenStack provider documentation.

Build process

Once we create our files, we need to run a series of steps to create our entities. For my example, I have two directories to separate my network build and my instance build. For my network and compute build directory I use numbered *.tf files.

Firstly, we need to source the RC file described earlier to set the environment variables to access our Oracle OpenStack 4.0. This script is run: . ~/ OracleOpenStackRC.sh which will also ask for and store the password for the Oracle OpenStack user.

Each directory containing our *.tf files needs to run terraform init. This step only needs to run once per directory structure. This command prepares the working directory for use with Terraform and creates the .terraform directory. This command is safe to be run multiple times and used to bring the current working directory up to date with any configuration changes. If using a proxy then before running terraform init we need to export the proxy as follows: export HTTP_PROXY=http://my-proxy.com:80

Next, we run terraform plan, which creates an execution plan and advises which actions will apply and is a dry run of the end build. You can use the -out switch to create a file, which when used is an input to the terraform apply command. If you experience a hang with the terraform plan command and are using a proxy unset the proxy: unset HTTP_PROXY

Finally, we run terraform apply which will execute the actions and will provision our infrastructure. As explained above you can use this command with a file created by terraform plan, which can be useful for automation purposes.

When the instance is created, you can access the instance Oracle Linux OS via the floating IP with ssh and private key. For example:

ssh -i ~/.key/openstack.key -l cloud-user <floating_IP>

Rebuild and destroy process

If you either accidentally or purposefully change the Terraform built configuration via the Oracle OpenStack UI/CLI you can recover using Terraform. For example, if you delete a block volume or delete an instance created by Terraform you simply re-run the terraform plan command. The command will advise what needs to be rebuilt or changed; you then run terraform apply and the configuration that was previously there within the configuration files is recreated. Any changes to the configuration files will apply to the newly created entities.

The terraform destroy command will simply destroy all entities created by the terraform apply command based upon the Terraform execution plan. The destroy command will display what is to be destroyed and prompt for confirmation to proceed.

Comments

unknown-951199

spur230 wrote:

I  am using Oracle 11.2.0.3.   I  have a query similar to the one given below. It's estimated cardinality is  3 times off from actual.  I tried to create extended statistics but it is not helping.

Can't extended statistics be used  on columns  handling is null?

Is there any way to improve cardinality for this cases.

I have created random data in tmp.

col1 can have values  1 and 2.

col 2 can have values 1 and 2.

col3 is date and it is null mostly when  col1=1 and col2=1

I want to get good estimate for query (select * from tmp where col1=1 and col2 =1 and col3 is null)

  1. drop table tmp; 
  2.  
  3. create table tmp ( col1 number, col2 number, col3 date); 
  4.  
  5. insert  into tmp 
  6. select 1 ,1 ,sysdate from dual 
  7. union all 
  8. select 1, 2, sysdate  from dual 
  9. union all 
  10. select 1 ,1 ,NUll  from dual 
  11. union all 
  12. select 1, 1, NULL  from dual 
  13. union all 
  14. select 1, 1, sysdate  from dual 
  15. union all 
  16. select 2, 2, sysdate  from dual 
  17. union all 
  18. select 1, 1, NULL  from dual 
  19.  
  20. exec DBMS_STATS.GATHER_TABLE_STATS( user, 'TMP' , method_opt => 'FOR ALL COLUMNS '); 
  21.  
  22. select  count(*) from tmp where col1=1 and col2 =1 and col3 is null ;  
  23. -- gives 3 estimate is only 1 
  24.  
  25. Plan hash value: 3231217655 
  26. ---------------------------------------------------------------------------- 
  27. | Id  | Operation          | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | 
  28. ---------------------------------------------------------------------------- 
  29. |   0 | SELECT STATEMENT   |      |        |       |     4 (100)|          | 
  30. |   1 |  SORT AGGREGATE    |      |      1 |    11 |            |          | 
  31. |*  2 |   TABLE ACCESS FULL| TMP  |      1 |    11 |     4   (0)| 00:00:01 | 
  32. ---------------------------------------------------------------------------- 
  33.  
  34.  
  35. select dbms_stats.CREATE_EXTENDED_STATS ( user, 'TMP','(col1,col2,col3)') from dual; 
  36.  
  37.  
  38. exec DBMS_STATS.GATHER_TABLE_STATS(user, 'TMP', method_opt => 'for columns (col1,col2,col3) ' , degree=> 16 , estimate_percent => null); 
  39.  
  40.  
  41. select  count(*) from tmp where col1=1 and col2 =1 and col3 is null
  42. -- gives 3 estimate is only 1 

what exactly do you expect & desire from here?

If you claim to have found a bug, then submit Bug Report to Oracle Support.

unknown-7404

I  am using Oracle 11.2.0.3.   I  have a query similar to the one given below. It's estimated cardinality is  3 times off from actual.  I tried to create extended statistics but it is not helping. 

Can't extended statistics be used  on columns  handling is null?

Is there any way to improve cardinality for this cases.

I have created random data in tmp.

col1 can have values  1 and 2.

col 2 can have values 1 and 2.

col3 is date and it is null mostly when  col1=1 and col2=1

I want to get good estimate for query (select * from tmp where col1=1 and col2 =1 and col3 is null)

You have a table with NO INDEXES.

Oracle will perform a FULL TABLE SCAN

It makes NO DIFFERENCE what cardinality or cost an estimate says - it will take as long as it takes.

AndrewSayer

Top of my head, you could create a virtual column case when col1=1 and col2=1 and col3 is null then 1 else null end. Gather stats to include the virtual column. Change your query to reference the virtual column. That's if this is a query where the user doesn't have much say in what the predicates are (I'm assuming this is the case as there's no bind variables)

JohnWatson2

This,

  1. exec DBMS_STATS.GATHER_TABLE_STATS(user, 'TMP', method_opt => 'for columns (col1,col2,col3) ' , degree=> 16 , estimate_percent => null); 

is not building a histogram on the extension that you created: it is building histograms in the columns individually. You need to build a histogram on the virtual column created by the extension. If you don't remember its name, you'll need to query dba_tab_cols to find it.

--update: sorry, I was wrong. Your syntax does build up stats on the extension. Indeed, it creates the extension if it doesn't already exist. Tested in 12.1.0.2.

JohnWatson2

I can't agree with this (which is unusual for anything you post) - accurate cardinality estimates are vital whether the table is indexed or not, to get the correct join order. In this trivial case, the CBO thinks there is only one row returned, when there are actually 3. So this table becomes a reasonable choice as the driving table for a query. Multiply that up to the real world, and it might expect ten rows and get ten thousand. This could seriously degrade everything else, as so much unexpected data is carried through the plan.

Jonathan Lewis

I had a quick look at the problem last night. It looks like you've found another limitation of column groups ( https://jonathanlewis.wordpress.com/2012/04/11/extended-stats/ ) - the presence of the "is null" predicate seems to block the optimizer's use of the column group. I'll write up a proper test in a few days' time, but in the meantime I'd pass your example to Oracle in an SR.


Regards

Jonathan Lewis

Jonathan Lewis

John,

The call will create column group stats, and by default it should create a histogram on that column group.

I've been caught out by that variation on the syntax too - the brackets around the list of column names are significant: https://jonathanlewis.wordpress.com/2013/09/25/extended-stats-2/

Regards

Jonathan Lewis

JohnWatson2

Yes, I've already done the test.

Jonathan Lewis
Answer

I've just published a modified version of your example with some supporting details of how the column group seems to be ignored if one of the underlying columns has an "is null" predicate: https://jonathanlewis.wordpress.com/2015/11/05/column-groups/

Regards

Jonathan Lewis

Marked as Answer by spur230 · Sep 27 2020
1 - 9

Post Details

Added on Mar 16 2018
4 comments
1,855 views