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.
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
The Flashblade reporting throughput at ~1.09GB/s
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.
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)
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
The parameters used in my oranfstab are described below:
The NFS server name, this can be anything and is used for information rather than any network lookups.
Up to four paths on the database host, specified by IP address or by name.
Up to four network paths to the NFS server, specified either by IP address, or by name.
The exported path from the NFS server.
The corresponding local mount point for the exported volume.
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.
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:
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  path [192.168.4.100] to filer [flashblade] via local [192.168.4.2] is UP
Direct NFS: channel id  path [192.168.5.100] to filer [flashblade] via local [192.168.5.2] is UP
Direct NFS: channel id  path [192.168.6.100] to filer [flashblade] via local [192.168.6.2] is UP
Direct NFS: channel id  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.
You can then need to select the required Business Area e.g. ‘Database’ and click the red ‘Continue’ button, you will then be presented with the ‘Oracle Standard Terms and Restrictions’ agreement, review and if acceptable click ‘Accept’ and the bottom of the page.
You will then be returned to the ‘Explorer’ and notice that the Oracle Standard Terms and Restrictions shows you acceptance, this is valid for 8 hours only.
Note: you will not be able to pull and image until you have accepted Oracle Standard Terms and Restrictions.
You are now ready to pull the required image by clicking the repository e.g. ‘enterprise’ and copying & pasting the provided pull command e.g.
The Repository Detail page provides set-up and usage information for the selected image.
Ok, now I have outlined the process let me walk you through it.
Your Oracle two images are now available for use locally, you can check this with the docker image ls command. e.g.
ronsmac:~ ronekins$ docker image ls
REPOSITORY TAG IMAGE ID CREATED SIZE
container-registry.oracle.com/os/oraclelinux latest 6c33a25f4a29 5 weeks ago 229MB
container-registry.oracle.com/database/enterprise latest 12a359cd0528 2 months ago 3.44GB
Within the ‘OS’ area Oracle provides a registry image which you can use to deploy a local registry so you can pull images and customise as required, the local registry also avoids the need the to authenticate every 8 hours, I will detail all of this in a follow-up post.
ronsmac:~ ronekins$ docker pull container-registry.oracle.com/os/registry
Using default tag: latest
latest: Pulling from os/registry
a3ed95caeb02: Pull complete
89937cfc6593: Pull complete
bd07ebf08156: Pull complete
Status: Downloaded newer image for container-registry.oracle.com/os/registry:latest
Last year Tim aka ‘ORACLE-BASE’ launched the the first OTN (Oracle Technology Network) Appreciation Day, after the success of last year this is being repeated again this year under the new moniker of ODC.
As today is Tuesday 10th October 2017 expect some great content from fellow Oracle ACE’s and Oracle community folk coming out throughout the day as the different timezones come online.
It’s my pleasure to contribute to this year’s ODC Appreciation Day (Oracle Developer Community) and my favourite reason feature is the Oracle 12c Online database move datafile feature.
This feature is great for fixing naming or directory structure errors, re-organising your database layout, adopting new standards or migrating between storage systems. It supports Oracle ASM and filesystems with the only restriction being you need to be on Oracle 12c, so upgrade it your still on Oracle 11gR2 and embrace online database move datafile.
The Open Group is pleased to announce the piloting of our Open Badge program for the professional certification programs. We are partnering with Acclaim, a division of Pearson VUE, an exam delivery company, to provide digital versions of our credentials. As a consequence, this means over the coming months, we will be issuing badges for all of The Open Group Certification Programs.
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.
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.
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.
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)
At the end of each run semaphore provides a Task Log which is retained and accessible from the dashboard for historic review.
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.
TNSLSNR for Linux: Version 18.104.22.168.0 – Production
System parameter file is /u01/install/APPS/12.1.0/network/admin/EBSDB_apps/listener.ora
Log messages written to /u01/install/APPS/12.1.0/admin/EBSDB_apps/diag/tnslsnr/apps/ebsdb/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=apps.example.com)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=apps.example.com)(PORT=1521)))
STATUS of the LISTENER
Version TNSLSNR for Linux: Version 22.214.171.124.0 – Production
Start Date 15-AUG-2017 07:42:19
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
Listener Parameter File /u01/install/APPS/12.1.0/network/admin/EBSDB_apps/listener.ora
Listener Log File /u01/install/APPS/12.1.0/admin/EBSDB_apps/diag/tnslsnr/apps/ebsdb/alert/log.xml
Listening Endpoints Summary…
Service “EBSDB” has 1 instance(s).
Instance “EBSDB”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
addlnctl.sh: exiting with status 0
addlnctl.sh: check the logfile /u01/install/APPS/12.1.0/appsutil/log/EBSDB_apps/addlnctl.txt for more information …
You are running addbctl.sh version 120.1
Starting the database EBSDB …
SQL*Plus: Release 126.96.36.199.0 Production on Tue Aug 15 07:42:19 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2926472 bytes
Variable Size 1308625016 bytes
Database Buffers 822083584 bytes
Redo Buffers 13848576 bytes
Disconnected from Oracle Database 12c Enterprise Edition Release 188.8.131.52.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
addbctl.sh: exiting with status 0
[oracle@apps scripts]$ ./startapps.sh
Starting the Oracle E-Business Suite Application Tier Services
You are running adstrtal.sh version 120.24.12020000.11
All enabled services for this node are started.
adstrtal.sh: Exiting with status 0
adstrtal.sh: check the logfile /u01/install/APPS/fs1/inst/apps/EBSDB_apps/logs/appl/admin/log/adstrtal.log for more information …
Before you can logon you will need to enable SYSADMIN and the Demo accounts, Oracle have provided scripts to do this e.g.
[oracle@apps scripts]$ ./enableSYSADMIN.sh
This script will let you set a new password for the SYSADMIN EBS Applications User, the default password most likely was “sysadmin”, so pick a different one now.
Enter new password for SYSADMIN:
Re-enter password for SYSADMIN:
Changing password for SYSADMIN
Log filename : L7583909.log
Report filename : O7583909.out
[oracle@apps scripts]$ ./enableDEMOusers.sh
Enter a new password for the EBS Applications Users, the default password
most likely was “welcome”, so pick a different one now…
Enter new password for DEMO users:
Re-enter password for DEMO users:
Setting password for DEMO users…
Log filename : L7583911.log
Oracle EBS R12 Dashboard
You should now be able to logon as ‘SYSADMIN’ or explore your Oracle EBS R12.2.6 VISION sandbox environment using the Demo users (see below) and the password defined above at: http://apps.example.com:8000/
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.
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.
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 184.108.40.206.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.
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.
The distribution includes the entire Oracle EBS Technology and Apps stack including the example Vision database.
If you have read my previous posts on Docker you may have already installed Docker and have an Oracle 12c database running in a Docker container, if not you may want to read my first post on Docker – Running Oracle 12.2.01 on Docker
However, since I wrote the above post Oracle have published Docker images for many of their products, these can be found at the Docker Store. The Oracle images on the Docker Store are not just a great resource and help accelerate adoption, but are also certified and supported by Oracle.
In this post I will create a Docker container running an Oracle 12c database called PSTG using 4 mount points /u01, /u02, /u03 & /u04 mounted from a Pure Storage FlashArray.
Database environment Layout
/u01 – OraInventory and Oracle 12c product directories /u02 – Oracle Datafile /u03 – Fast Recovery Area /u04 – RedoLog files.
The 4 mount points will all be using persistent All Flash Storage providing low latency performance, space efficient crash consistent snapshots and requiring no local storage.
First, confirm that you have installed the Oracle 12c image using the the following:
$ docker images list
You should be able see the store/oracle/database-enterprise 220.127.116.11 Docker Image, if missing you need to read my previous post.
Ok, copy the db_env.dat file created in the previous post to a new environment file $ORACLE_SID_env.dat and update database name and password e.g.
## Copyright(c) Oracle Corporation 1998,2016. All rights reserved.##
## Docker OL7 db12c dat file ##
## Specify the basic DB parameters
## db sid (name)
## default : ORCL
## cannot be longer than 8 characters
## db passwd
## default : Oracle
## db domain
## default : localdomain
## db bundle
## default : basic
## valid : basic / high / extreme
## (high and extreme are only available for enterprise edition)
We will now create the 4 volumes using the docker volume create command and Pure Storage Docker plug-in providing name, size and label.
We can use the docker volume ls command with a filter to list the 4 database volumes we are interested in using the following syntax
$ docker volume ls -f name=environment
The Pure Storage Docker Plugin also supports docker volume inspect, from here we can see the labels which we defined earlier, great way to see where and what the mount is being used for.
$ docker volume inspect volume name
Now that we have installed the Oracle 12c Docker image and created the 4 volumes we are ready to create our Oracle 12c container using the docker run command with -v to present our newly volumes to our container.