Oracle Database Automation with Ansible AWX

In the video below I demonstrate how a simple Ansible playbook can be used to automate the cloning of multiple Oracle databases in parallel.

In the demo I use the AWX Web UI to run the playbook, the AWX project is an Open Source community project sponsored by RedHat, it can be considered the ‘bleed-in edge’ of RedHat Ansible Tower.

AWX is designed to be fast moving and is where the development takes place before being hardened and making it’s way into the enterprise ready RedHat supported Ansible Tower solution.

AWX is great from lab work, but for all Production workloads I would strongly recommend that Ansible Tower is considered.

Pure Code Developer Community

Visit the Pure Code Developer Community and click on the ‘Ansible Playbook Examples’ card to be directed to the public GitHub repository, you will find this and other Oracle playbook examples.

Advertisements

Getting started with the Oracle ZFS Storage VMware Simulator (Part II)

In this Blog post I will share how your newly created Oracle ZFS Storage VMware Simulator can be used to deliver storage for an Oracle 12c database via dNFS and also support HCC (Hybrid Columnar Compression).

Hopefully you have followed Part I of my Blog post on Getting Started with the Oracle ZFS Storage VMware Simulator and already have a working Virtual Machine, if not probably a good time to visit Part I and try to build the simulator.

Oracle dNFS

If you have not set-up or used Oracle dNFS before, you may want to check-out a couple of my previous posts on Oracle dNFS, Using Oracle dNFS Multi-Path and also Oracle dNFS Throughput Testing as these provide some useful background and some additional information.

On my Oracle 12c Linux database server I have created an a directory for the mount point and an oranfstab file in $ORACLE_HOM/dbs thus:

server: zfsa
local: 192.168.56.10  path: 172.16.148.133
nfs_version: nfsv3
export: /export/oradata   mount: /u02/app/oracle/oradata

$ORACLE_HOME/dbs/oranfstab

The parameters used in my oranfstab are described below:

server
The NFS server name, this can be anything and is used for information rather than any network lookups.

local
Up to four paths on the database host, specified by IP address or by name.

path
Up to four network paths to the NFS server, specified either by IP address, or by name.

export
The exported path from the NFS server.

mount
The corresponding local mount point for the exported volume.

nfs_version
Specifies the NFS protocol version used by Direct NFS Client. Possible values are NFSv3NFSv4NFSv4.1, and pNFS. The default version is NFSv3. If you select NFSv4.x, then you must configure the value in oranfstab for nfs_version. Specify nfs_version as pNFS, if you want to use Direct NFS with Parallel NFS.

Verify Oracle dNFS Usage

Restart your Oracle 12c database and view the Oracle Alert file, if you have successfully  configured your environment you should see that Oracle is reporting that the Oracle dNFS library is in use as it’s version.

Oracle instance running with ODM: Oracle Direct NFS ODM Library Version 4.0

If you are not seeing any reference to the use of Oracle Direct NFS you may need to enable it using the steps detailed here.

We should also be able to see that we are using a single channel, IP addresses and the optional server name from our oranfstab.

Direct NFS: channel id [0] path [172.16.148.133] to filer [zfsa] via local [192.168.56.10] is UP

From the alert log file we can also confirm thatOracle is using our oranfstab file rather than the /etc/mtab, if your oranfstab is not correct your will see defined in mtab rather than defined in oranfstab, time to go back and fix-it.

Direct NFS: attempting to mount /export/oradata on filer zfsa defined in oranfstab

Additionaly, you can query the database using the v$dnfs_servers view to confirm the configuration.

SQL> COLUMN svrname FORMAT A20
SQL> COLUMN dirname FORMAT A50
SQL> SELECT svrname, dirname, nfsversion FROM v$dnfs_servers;

SVRNAME  DIRNAME           NFSVERSION
————— ———————-     ———-
zfsa            /export/oradata NFSv3.0

SNMP (Simple Network Management Protocol)

Finally, before we can start to use Oracle HCC (Hybrid Columnar Compression) within our Oracle 12c database we need to check that our simulator has been configured to use SNMP.

From our Oracle ZFS Storage VMware simulator check the status of services.

SNMP3

And if green, use snmpget to check the response from our simulator, providing the IP address and the Oracle MIB (Management Information Bases) below.

snmpget -v1 -c public 172.16.148.133 1.3.6.1.4.1.42.2.225.1.4.2.0

If all OK, snmpget should return a Sun Storage string, this is required to allow us to use the HCC features which are limited to Oracle Exadata / storage platforms.

SNMPv2-SMI::enterprises.42.2.225.1.4.2.0 = STRING: “Sun Storage 7000”

If you have successfully built your simulator and configured your Oracle 12c environment you are now ready to try to try out some of the HCC features.

In the next Blog post we will attempt to create database objects within and outside of our Oracle ZFS Storage VMware simulator to check our Oracle 12c database is able to use HCC data.

Oracle 12c Multi-database refresh with Ansible

In this Blog post I will share another short video demonstrating how storage data services can be used to perform many common data management tasks using simply scripting, DevOPS automation tools and Pure Storage REST API’s .

Oracle 12c multiple databases clones with Ansible

Before we jump into the demonstration let me detail my architecture and explain what you are going to see, first the Architecture.

The Architecture

z-oracle will be used as my Ansible control machine
z-oracle1 is where my production Oracle 12c database is hosted
z-oracle2 thru to z-oracle7 are my development / test database servers.
FlashArray will be used to deliver the storage data services.Ansible_Demo

The Ansible Playbook

The database_clone Ansible playbook performs the following steps:

  1. Performs crash-consistent storage snapshot of production database using REST API.
    1. The storage snapshot is instant, has no impact to the running production database, and does not require any changes to production server or database configuration.

  2. Shutdown non-production database(s) and unmount database filesystems.
  3. Refresh non-production volume(s) from latest storage snapshot.
  4. Mount database filesystems and start database(s).
    1. At this point the non-production database(s) are exact copies of the production database with the same name as production but consuming no space.

  5. Rename non-Production database, datafiles and directories.

If you have watched the video you will have seen that the 6 non-production databases have all been refreshed from production in less than 2 1/2 minutes from a single Ansible playbook.

Visit code.purestorage.com to see more Ansible examples and also find examples for other DevOPs tools and languages including Python, PowerShell, Puppet…

Part 3: Oracle 12c Point-in-Time-Recovery

In this Blog post I will share another short video demonstrating how storage data services can be used to perform many common data management and CDM (Copy Data Management) tasks using simply scripting, DevOPS automation tools and Pure Storage REST API’s .

Oracle 12c Point-in-Time-Recovery

This video starts the same as a Part 1: Oracle 12c database reset by refreshing an Oracle 12c database, and then simulates a typical development workflow process, where development databases are refreshed from production and then updated with:

  • New application configuration and setup
  • Additional synthetic and manufactured data supplement production data sets
  • Data masking and cleansing scripts.
  • Housekeeping routines e.g. clean-up workflow / staging tables

The video shows me taking a crash-consistent snapshot using a python script I have developed (available at code.purestorage.com) using the Pure Storage REST API’s.

The video then proceeds to perform further updates and changes, before simulating a the loss of datafile.

I then use another python script to determine the last snapshot before shutting the database down and rolling-back to the recovery point. Once the datafiles have been restored RMAN is used to roll-forward the database using the database log files performing a PITR (Point-in-Time-Recovery) with no data-loss.

Part 2: Oracle 12c database rewind

In this Blog post I will share another short video demonstrating how storage data services can be used to perform many common data management and CDM (Copy Data Management) tasks using simply scripting, DevOPS automation tools and Pure Storage REST API’s .

Oracle 12c database rewind

This video starts the same as a Part 1: Oracle 12c database reset by refreshing an Oracle 12c database, and then simulates a typical development workflow process, where development databases are refreshed from production and then updated with:

  • New application configuration and setup
  • Additional synthetic and manufactured data supplement production data sets
  • Data masking and cleansing scripts.
  • Housekeeping routines e.g. clean-up workflow / staging tables

The video shows me taking a crash-consistent snapshot using a python script I have developed (available at code.purestorage.com) using the Pure Storage REST API’s.

The video then proceeds to perform further updates and changes, before rewinding the database back to the last crash-consistent snapshot enabling rapid, iterative testing.

Part 1: Oracle 12c database reset

In this series of Blog posts I will share short videos demonstrating how storage data services can be used to perform many common data management and CDM (Copy Data Management) tasks using simply scripting, DevOPS automation tools and Pure Storage REST API’s .

Oracle 12c Database Reset

The video starts by refreshing an Oracle 12c database, and then simulates a typical development workflow process, where development databases are refreshed from production and then updated with:

  • New application configuration and setup
  • Additional synthetic and manufactured data supplement production data sets
  • Data masking and cleansing scripts.
  • Housekeeping routines e.g. clean-up workflow / staging tables

The video shows me taking a crash-consistent snapshot using a python script I have developed (available at code.purestorage.com) using the Pure Storage REST API’s.

The video then proceeds to perform further updates and changes, before resetting the database back to the initial clone simulating an application test issue due to problems with the initial set-up and configuration.

Oracle dNFS Throughput testing

Oracle Direct NFS

In my previous Oracle dNFS Blog post I detailed how to configure Oracle 12cR2 to use Oracle dNFS multi-path, in this Blog I will share some test results that I achieved in my Oracle 12cR2 Pure Storage FlashBlade lab.

Oracle dNFS Flashblade configuration

Theoretical Limits

A Gigabit Ethernet network can transmit 1,000,000,000 bits per second, as we normally talk about X Bytes per second we need to divide this by 8 as there are 8 bits in a byte.

1GbE = (1,000,000,000 / 8) = 125,000,000 bytes/second
=125,000KB/s
=125MB/s
=0.125GB/s

Unfortunately we can not use all 125,000,000 bytes/second to send data as we have multiple layers of overhead,  when using a default MTU of 1500 we can expect to experience ~6% overhead, this gives us:

1GbE= (1,000,000,000 / 8) less 6% overhead = 117,500,000 bytes/second
=117,500KB/s
=117.5MB/s
=0.118GB/s

1 x  1GbE =   0.118GB/s
1 x 10GbE = 1.175GB/s
4 x 10GbE = 4.7GB/s
8 x 10GbE = 9.4GB/s  (2 Node RAC Cluster)

The dNFS Throughput Test

In the tests below I used Kevin Closson’s excellent SLOB with a custom configuration to produce Oracle FTS (Full Table Scans), many thanks to Frits Hoogland for sharing. This set-up allowed me to generate sufficient load to utilise the available bandwidth and see how close I can get to the ‘theoretical limits’.

Test 1 – Oracle 12cR2 dNFS disabled

OK, during this test we can see that Oracle EM Express is showing a single instance driving ~1.08GB/s IO Throughput

Oracle Express 12c
The Flashblade reporting throughput at ~1.09GB/s

FB_PSTG1

Test 2 – Oracle 12cR2 dNFS enabled

For out next test, I have enabled dNFS with make -f ins_rdbms.mk dnfs_on but have not activated multipath, this was confirmed with the SQL below.

SELECT svrname, dirname, wtmax, rtmax FROM v$dnfs_servers;

SVRNAME        DIRNAME             WTMAX  RTMAX
-------        ------------------  ------ ------
192.168.4.100 /z-fbhosts_oradata   524288 524288
192.168.4.100 /z-fbhosts_orafra    524288 524288
192.168.4.100 /z-fbhosts_oraconfig 524288 524288

Oracle Express EM reports almost identical results with and without dNFS
FB_PSTG1_dnfs
And the Flashblade dashboard reports a similar result.

FB_dnfs
So lets enable multi-path and see the impact.

Test 3 – Oracle 12cR2 dNFS multi-path

So, lets install our oranfstab file and bounce the database and check again using the previous SQL script.

SELECT svrname, dirname, wtmax, rtmax FROM v$dnfs_servers; 
SVRNAME        DIRNAME          WTMAX  RTMAX 
-------        ---------------- ------ ------ 
flashblade /z-fbhosts_oradata   524288 524288 
flashblade /z-fbhosts_orafra    524288 524288 
flashblade /z-fbhosts_oraconfig 524288 524288

We can see the Server Name has been picked up from the oranfstab rather than the mtab, we can also check that we are using multipath with the v$dnfs_channels view.

SELECT distinct svrname, path FROM v$dnfs_channels;
SVRNAME              PATH                                            
-------------------- -------------
flashblade           192.168.7.100
flashblade           192.168.4.100
flashblade           192.168.6.100
flashblade           192.168.5.100

Oracle EM Express is now showing we are using all 4 x 10GbE interfaces as expected and driving 4.03GB/s, also note the increase in Host CPU utilisation.
FB_PSTG1_MP

FB_MP
Ok, our final test will be to see if we get similar results with Oracle RAC

Test 4 – Oracle 12cR2 2 node RAC with dNFS multi-path

For this Oracle RAC test the slob.conf SQLNET_SERVICE_MAX parameter has been changed to 2 and to distribute the workload across the 2 nodes.

FB_PSTG_RAC
Oracle EM Express is showing that the workload has been evenly balanced across the 2 RAC nodes, achieving impressive throughput figures exceeding 8.3GB/s.

FB_RACThe Flashblade dashboard show that the 7 blades were able to deliver a consitent bandwidth of over 8.8GB/s for the Oracle workload utilising all 8 x 10GbE interfaces getting close to wire speed.

In my next Blog Post I plan to enable Jumbo Frames (JF) end-to-end and repeat the above tests.  With JF (MTU 9000) configured, I am hoping to see a ~5% improvement in bandwidth utilisation.


Using Oracle dNFS Multi-path

Oracle Direct NFS (dNFS) Multi-path

If you have multiple interfaces on your database server and your NFS server also supports multiple interfaces you should consider using Oracle dNFS multi-path.

Oracle dNFS multi-path removes the need to distribute and manage datafiles over multiple NFS mount points, Oracle dNFS multi-path can also improve throughput if you were not previously using all your available network interfaces and bandwidth.

In Oracle dNFS (Direct NFS) is disabled by default but can be easily enabled or disabled by following the steps below.

However, before we go through the Oracle dNFS configuration I will describe my lab set-up. My Database Server is configured with 4 x 10GbE all all on different sub-nets, and my  Pure Storage FlashBlade is configured with 4 NFS interfaces exporting 3 NFS mounts for Oracle data, Oracle FRA and shared configuration for my Oracle 12cR2 RAC cluster.

For further details see MOS Note: How to configure DNFS to use multiple IPs (Doc ID 1552831.1). It’s also possible to use the same subnet if required, checkout MOS Note: How to Setup Direct NFS client multipaths in same subnet (Doc ID 822481.1)

dNFS

The oranfstab file

To use Oracle dNFS MultiPath you will need an oranfstab file, Oracle will look in the following order and locations for the configuration file.

  • /etc/oranfstab – Server wide
  • $ORACLE_HOME/dbs – Oracle Home specific

If no oranfstab file is found Oracle will use the /etc/mtab file to enable dNFS but you will have no MultiPath

Below is a copy of my $ORACLE_HOME/dbs/oranfstab

server: flashblade
local: 192.168.4.2 path: 192.168.4.100
local: 192.168.5.2 path: 192.168.5.100
local: 192.168.6.2 path: 192.168.6.100
local: 192.168.7.2 path: 192.168.7.100
nfs_version: nfsv3
export: /z-fbhosts_oradata  mount: /u01/oradata
export: /z-fbhosts_orafra   mount: /u01/app/oracle/fast_recovery_area
export: /z-fbhosts_oraconfig mount: /u01/shared_config

The parameters used in my oranfstab are described below:

server
The NFS server name, this can be anything and is used for information rather than any network lookups.

local
Up to four paths on the database host, specified by IP address or by name.

path
Up to four network paths to the NFS server, specified either by IP address, or by name.

export
The exported path from the NFS server.

mount
The corresponding local mount point for the exported volume.

nfs_version
Specifies the NFS protocol version used by Direct NFS Client. Possible values are NFSv3, NFSv4, NFSv4.1, and pNFS. The default version is NFSv3. If you select NFSv4.x, then you must configure the value in oranfstab for nfs_version. Specify nfs_version as pNFS, if you want to use Direct NFS with Parallel NFS.

Enabling dNFS

Change dir to $ORACLE_HOME/rdbms/lib and use the make command e.g
 cd $ORACLE_HOME/rdbms/lib
  make -f ins_rdbms.mk dnfs_on

You should see something similar to this response:

rm -f /u01/app/oracle/product/12.2.0.1/db_1/rdbms/lib/odm/libnfsodm12.so; \
  cp /u01/app/oracle/product/12.2.0.1/db_1/lib/libnfsodm12.so /u01/app/oracle/product/12.2.0.1/db_1/rdbms/lib/odm/libnfsodm12.so

Verify Oracle dNFS Usage

After applying changes restart the database and confirm your configuration change have had the desired impact.

The Oracle Alert file should now report the Oracle dNFS library is in use as it’s version.

Oracle instance running with ODM: Oracle Direct NFS ODM Library Version 4.0

We can also see that Oracle is using multiple paths e.g.

Direct NFS: channel id [0] path [192.168.4.100] to filer [flashblade] via local [192.168.4.2] is UP
Direct NFS: channel id [1] path [192.168.5.100] to filer [flashblade] via local [192.168.5.2] is UP
Direct NFS: channel id [2] path [192.168.6.100] to filer [flashblade] via local [192.168.6.2] is UP
Direct NFS: channel id [3] path [192.168.7.100] to filer [flashblade] via local [192.168.7.2] is UP

have had the desired impact by querying the v$dnfs_servers view.

Check to see if Enabled

SQL> COLUMN svrname FORMAT A20
SQL> COLUMN dirname FORMAT A50
SQL> SELECT svrname, dirname, nfsversion FROM v$dnfs_servers;

SVRNAME     DIRNAME                                     NFSVERSION
-------------------- -----------------------------------------------
flashblade     /z-fbhosts_oraconfig                       NFSv3.0
flashblade     /z-fbhosts_oradata                         NFSv3.0
flashblade     /z-fbhosts_orafra                          NFSv3.0

dNFS views

  • v$dnfs_stats          – shows dNFS performance statistics
  • v$dnfs_channels  – shows network channels is use by dNFS
  • v$dnfs_servers     – shows dNFS servers
  • v$dnfs_files           – shows files open for dNFS

Disabling dNFS

To disable dNFS change directory to $ORACLE_HOME/rdbms/lib and enter the following commands:

cd $ORACLE_HOME/rdbms/lib
  make -f ins_rdbms.mk dnfs_off

You should see something similar to this response:

rm -f /u01/app/oracle/product/12.2.0.1/db_1/rdbms/lib/odm/libnfsodm12.so

You can also now remove the previously created oranfstab file

Check to see if Disabled

SQL> COLUMN svrname FORMAT A20
SQL> COLUMN dirname FORMAT A50
SQL> SELECT svrname, dirname, nfsversion FROM v$dnfs_servers;
 
no rows selected

In my next post I will share some before and after test results.


One-Click Oracle 12c Database Clone

Background

Earlier in the year at the excellent OUgf Finnish Oracle User Group Harmony 17 conference, I performed a Live demo where I refreshed an Oracle 12c database using an Ansible Playbook in just over a minute, 1min 16sec to be more precise.

Timings

Once I had completed my presentation Frits Hoogland mentioned that he had recently used and Blogged on Ansible-Sempahore , an Open Source alternative to Ansible Tower which provides a Web UI and API’s for launching Ansible Tasks.

On my return to the UK I read Frits’ Blog – How to install semaphore UI for running Ansible and the installation instructions on the GitHub site, and was soon up and running.

Why this Blog

In this Blog post I am going to share how I used Ansible-Semaphore to launch my Ansible playbook to perform an Oracle 12c database refresh with a single click.

Once I had Semaphore installed, I configure the Inventory, Environment, Key Store and Playbook Repositories and added my Task Templates all through the Semaphore UI, all pretty straight forward, so no need to screenshot it.

semaphore Task Templates
semaphore Task Templates

A powerful feature of Semaphore is it’s integration with GitHub, this ensures every time a Playbook is run the last version is used as the code is pulled back from the repository.

Running my ‘Oracle Database Clone’ Ansible playbook is as now as simple as clicking ‘run!’ (no Playbook or Environment Overrides are required for this playbook)

Create_Task

At the end of each run semaphore provides a Task Log which is retained and accessible from the dashboard for historic review.

Task_Log

There we have it a One-Click Oracle 12c Database refresh in just under 1 minute 15 seconds.

If you want to see more, check out my YouTube channel to watch a demo of the above.

Oracle EBS 12.2.6 on VirtualBox

Back in May, Oracle announced the general availability of Oracle VM Virtual Appliance for E-Business Suite 12.2.6 for download from the Oracle Software Delivery Cloud.

In this series of Blog posts I will detail how you can build an Oracle EBS 12.2.6 environment with VirtualBox for use on a laptop / PC.

Getting Started

Logon to the Oracle Software Delivery Cloud using you SSO credentials and search for ‘Oracle Virtual Appliance‘ , select the Product as below and click ‘Continue’ to download the Oracle VM Virtual Appliance for Oracle E-Business Suite.

Filter

Compatible with VirtualBox

As you can see from above, the Oracle VM Virtual Appliance for Oracle E-Business Suite supports both Oracle VM and Oracle VirtualBox. Once download the Virtual Appliance can be imported into VirtualBox for running on a laptop / PC.

The Oracle VM Virtual Appliances for Oracle E-Business Suite 12.2.6.0.0 for x86 64bit is packaged in 19 files requiring 69.4 GB, so may take some considerable time to download depending upon your network / internet connection.

DownloadQueue

The Download

You can download the 19 Oracle VM Virtual Appliances files individually by clicking on the file name or them all use wget, if you have never used wget you can read the following post on how to use wget for downloading.

Virtual_Appliance_SWThe distribution includes the entire Oracle EBS Technology and Apps stack including the example Vision database.

COMPONENT VERSION
RDBMS Oracle Home 12.1.0.2
Application Code Level Oracle E-Business Suite 12.2.6 Release Update Pack (My Oracle Support Knowledge Document 2114016.1) + AD and TXK Delta 8 (My Oracle Support Knowledge Document 2159750.1)
Oracle Forms and Reports 10.1.2.3
WebLogic Server 10.3.6
Web Tier 11.1.1.9
JDK JDK 1.7 build 1.7.0_121-b15
Java Plugin J2SE 1.7
Critical Patch Update (CPU) October 2016

In my next post I will detail how build an Oracle E-BS 12.2.6 environment within VirtualBox.