Darwin IT

Subscribe to Darwin IT feed
Darwin-IT professionals do ICT-projects based on a broad range of Oracle products and technologies. We write about our experiences and share our thoughts and tips.Martien van den Akkerhttps://plus.google.com/110503432901891966671noreply@blogger.comBlogger369125
Updated: 11 hours 12 min ago

The Medrec Datamodel DDL

Tue, 2018-06-12 06:57
Next week I deliver the training 'Weblogic 12c Tuning and Troubleshooting' . One of the labs is to have the sample application MedRec generate Stuck Threads, so that the students can investigate and try to solve that. Or actually configure the server so that it will cause a automatic restart.

To do so I have to deliberately break the application and so I need the source. I have an earlier version of the application, but not the sources. So I have to go to the latest MedRec. I actually like that, because it looks more modern.

The MedRec application is available if you install WebLogic with samples.

You can run the script demo_oracle.ddl  from against the database:

The medrec.ear can be found at:

I ran in quite some confusion and frustration, but I found that this combination, although from the same samples folder, does not work. Not only this medrec.ear expects the tables in plural (PRESCRIPTIONS) where the script creates them in singular (PRESCRIPTION), it expects a separate DRUGS table with a foreign key column DRUG_ID in PRESCRIPTIONS.  And a few other changes.

I had a version of the scripts from earlier versions of WebLogic's MedRec. Based the exceptions in the server log, I refactored/reverse engineered the scripts.

Using those I could succesfully login and view the Patient records of fred@golf.com:

First we need to create a a schema using createDBUserMedrec.sql:
prompt Create user medrec with connect, resource roles;
grant connect, resource to medrec identified by welcome1;
alter user medrec
default tablespace users
temporary tablespace temp;
alter user medrec quota unlimited on users;

Drop tables (if needed) using medrec_dropall.sql:









Create the tables using medrec_tables.sql:









Insert data using medrec_data.sql:




TIMESTAMP '1972-03-18 00:00:00','MALE','888888888','APPROVED',3,
'Page','Trout','A','Ponte Verde','United States','FL',
'235 Montgomery St','Suite 15','32301'
TIMESTAMP '1965-04-26 00:00:00','MALE','123456789','APPROVED',3,
'Fred','Winner','I','San Francisco','United States','CA',
'1224 Post St','Suite 100','94115'
TIMESTAMP '1971-09-17 00:00:00','MALE','333333333','APPROVED',3,
'Gabrielle','Spiker','H','San Francisco','United States','CA',
'1224 Post St','Suite 100','94115'
TIMESTAMP '1973-11-29 00:00:00','MALE','444444444','REGISTERED',3,
'Charlie','Florida','E','Ponte Verde','United States','FL',
'235 Montgomery St','Suite 15','32301'
TIMESTAMP '1959-03-13 00:00:00','MALE','777777777','APPROVED',3,
'Larry','Parrot','J','San Francisco','United States','CA',
'1224 Post St','Suite 100','94115'




Insert into "MEDREC"."DRUGS" (ID,NAME,FREQUENCY,PRICE,VERSION) values (101,'Advil','1/4hrs',1.0, 2);
Insert into "MEDREC"."DRUGS" (ID,NAME,FREQUENCY,PRICE,VERSION) values (102,'Codeine','1/6hrs',2.5,2);
Insert into "MEDREC"."DRUGS" (ID,NAME,FREQUENCY,PRICE,VERSION) values (103,'Drixoral','1tspn/4hrs',3.75,2);




151,TIMESTAMP '1991-05-01 00:00:00',TIMESTAMP '1991-05-01 00:00:00','Allergic to coffee. Drink tea.',
'','Drowsy all day.',2,51,1,85,70,75,125,98,180
152,TIMESTAMP '1991-05-01 00:00:00',TIMESTAMP '1991-05-01 00:00:00','Light cast needed.',
'At least 20 sprained ankles since 15.','Sprained ankle.',
153,TIMESTAMP '1989-08-05 00:00:00',TIMESTAMP '1989-08-05 00:00:00','Severely sprained interior ligament. Surgery required.','Cast will be necessary before and after.','Twisted knee while playing soccer.',2,52,1,85,70,75,125,98,180
154,TIMESTAMP '1993-06-30 00:00:00',TIMESTAMP '1993-06-30 00:00:00','Common cold. Prescribed codiene cough syrup.','Call back if not better in 10 days.','Sneezing, coughing, stuffy head.',2,52,1,85,70,75,125,98,180
155,TIMESTAMP '1999-07-18 00:00:00',TIMESTAMP '1999-07-18 00:00:00','Mild stroke. Aspirin advised.','Patient needs to stop smoking.','Complains about chest pain.',2,52,1,85,70,75,125,98,180
156,TIMESTAMP '1991-05-01 00:00:00',TIMESTAMP '1991-05-01 00:00:00','Patient is crazy. Recommend politics.','','Overjoyed with everything.',2,55,1,85,70,75,125,98,180



To install the datasource you can use this wlst script, createDataSource.py:
# Create DataSource for WLS 12c Tuning & Troubleshooting workshop
# @author Martien van den Akker, Darwin-IT Professionals
# @version 1.1, 2018-01-22
# Modify these values as necessary
import os,sys, traceback
scriptName = sys.argv[0]
admServerUrl = 't3://'+adminHost+':'+adminPort
dsName = 'MedRecGlobalDataSourceXA'
dsJNDIName = 'jdbc/MedRecGlobalDataSourceXA'
initialCapacity = 5
maxCapacity = 10
capacityIncrement = 1
driverName = 'oracle.jdbc.xa.client.OracleXADataSource'
dbUrl = 'jdbc:oracle:thin:@darlin-vce.darwin-it.local:1521:orcl'
dbUser = 'medrec'
dbPassword = 'welcome1'
def createDataSource(dsName, dsJNDIName, initialCapacity, maxCapacity, capacityIncrement, dbUser, dbPassword, dbUrl, targetSvrName):
# Check if data source already exists
cd('/JDBCSystemResources/' + dsName)
print 'The JDBC Data Source ' + dsName + ' already exists.'
except WLSTException:
print 'Creating new JDBC Data Source named ' + dsName + '.'
# Create data source
jdbcSystemResource = create(dsName, 'JDBCSystemResource')
jdbcResource = jdbcSystemResource.getJDBCResource()
# Set JNDI name
jdbcResourceParameters = jdbcResource.getJDBCDataSourceParams()
# Create connection pool
connectionPool = jdbcResource.getJDBCConnectionPoolParams()
# Create driver settings
driver = jdbcResource.getJDBCDriverParams()
driverProperties = driver.getProperties()
userProperty = driverProperties.createProperty('user')
# Set data source target
targetServer = getMBean('/Servers/' + targetSvrName)
# Activate changes
print 'Data Source created successfully.'
return jdbcSystemResource

def main():
# Connect to administration server
connect(adminUser, adminPwd, admServerUrl)
createDataSource(dsName, dsJNDIName, initialCapacity, maxCapacity, capacityIncrement, dbUser, dbPassword, dbUrl,ttServerName)
apply(traceback.print_exception, sys.exc_info())
#call main()

Also Medrec needs an administrative user, createUser.py:
print 'starting the script ....'
admServerUrl = 't3://'+adminHost+':'+adminPort
realmName = 'myrealm'
def addUser(realm,username,password,description):
print 'Prepare User',username,'...'
if realm is not None:
authenticator = realm.lookupAuthenticationProvider("DefaultAuthenticator")
if authenticator.userExists(username)==1:
print '[Warning]User',username,'has been existed.'
print '[INFO]User',username,'has been created successfully'


addUser(realm,'administrator','administrator123','MedRec Administrator')


Deploy the medrec application, deployMedRec.py:
# Deploy MedRec for WLS 12c Tuning & Troubleshooting workshop
# @author Martien van den Akker, Darwin-IT Professionals
# @version 1.0, 2018-01-22
# Modify these values as necessary
import os,sys, traceback
scriptName = sys.argv[0]
admServerUrl = 't3://'+adminHost+':'+adminPort
appName = 'medrec'
appSource = '../ear/medrec.ear'
# Deploy the application
def deployApplication(appName, appSource, targetServerName):
print 'Deploying application ' + appName + '.'
progress = deploy(appName=appName,path=appSource,targets=targetServerName)
# Wait for deploy to complete
while progress.isRunning():
print 'Application ' + appName + ' deployed.'
def main():
# Connect to administration server
connect(adminUser, adminPwd, admServerUrl)
deployApplication(appName, appSource, ttServerName)
apply(traceback.print_exception, sys.exc_info())
#call main()

Add Weblogic12c Vagrant project

Mon, 2018-06-11 07:53
Last week I proudly presented a talk on  how to create, provision and maintain VMs with Vagrant, including installing Oracle software (Database, FusionMiddlware,etc.). It was during the nlOUG Tech Experience 18.  I've written about it in the last few posts.

Today I added my WLS12c Vagrant project to Github. Based on further insights I refactored my database12c installation a bit and pushed an updated Database12c Vagrant project to Github as well.

Main updates are that I disliked the 'Zipped' subfolder in the stage folder paths for the installations. And I wanted all the installations extracted into the same main folder. Then I can clean that up more easily.

You should check the references to the stage folder. Download the appropriate Oracle installer zip files and place them in the appropriate folder:

Coming up: a SOA 12c (including SOA, BPM and OSB) complete with creation of the domain (based on my scripts from 2016 and my TechExperience talk of last year), and configuration of nodemanager. Ready to start up after 'up'.

I also uploaded my slides of my talk to slideshare:

Enhance Vagrant provisioning: install java and database

Fri, 2018-05-04 10:37
In my previous blog posts (here and here), I wrote about how to create a base box and a create and start a virtual machine out of it. I started with provisioning, to have the vagrant user adapt the kernel settings, add a install user/owner and create a filesystem on an added disk.

Now let's make the provisioning a bit more interesting and install actual software in it.
Prepare new Vagrant project For this article I copied the project created from the previous blog. I called it ol75_db12c, since the goal is database 12c. But we'll also add java.
Now edit the Vagrantfile, since we want a new VM with another name:
So adapt the VM_NAME variable to something like "OL75U5_DB12c". You see how convenient it is to have those properties set as a global variable?

You could already try to do vagrant up to try it out. Remember, you can just do vagrant destroy to recreate it.

Also remove (or don't copy) the .vagrant subfolder, otherwise Vagrant would probably assume the box is already provisioned.  If that is the case, then either do a vagrant destroy to destroy the VM altogether, or vagrant provision to just re-provision the box.
JavaIn the copied project, lets start with Java. There are several possibilities to install java, you could download the RPM from OTN. But one of the recommended practice I found in installing Java on a server is to put it in a path that hasn't got the java version (especially  the update) in it. When using it to install Weblogic, for instance, this path ends up in several places in scripts. Although it's a handfull, it's more than once.
Upgrading java is then just bringing down the servers/services using it, backup the version and unzip/untar the new version in the same folder. And there you have it: I like a java distribution that comes in an archive.

In Oracle Support you can find it by searching for the document All Java SE Downloads on MOS (Doc ID 1439822.1). There you find the current versions of the Java SE pacakges. For this article I used the public version JDK 8 Update 172, that you can download as patch 27412872:
 This contains the rpm as well as a .tar.gz package, that we'll use. Make sure that you download the x86_64 version:
And copy the download in the Stages folder in your vagrant main project folder (see previous blog).

To install it, I have the following script installJava.sh:
#Download a zip with tar.gz containing complete JDK
#On MOS: Search for Doc ID 1439822.1
#Download latest 1.8 (public) patch, eg.:
#27412872 Oracle JDK 8 Update 172 (complete JDK, incl. jmc, jvisualvm)
SCRIPTPATH=$(dirname $0)
. $SCRIPTPATH/fmw12c_env.sh

echo "Checking Java Home: "$JAVA_HOME
if [ ! -f "$JAVA_HOME/bin/java" ]; then
#Unzip Java
if [ -f "$JAVA_ZIP_HOME/$JAVA_INSTALL_ZIP" ]; then
echo JAVA Zip File $JAVA_ZIP_HOME/$JAVA_INSTALL_ZIP does not exist!
echo $JAVA_INSTALL_TAR already unzipped
# Install jdk
echo Install jdk
echo create folder $JAVA_INSTALL_TMP
echo create JAVA_HOME $JAVA_HOME
mkdir -p $JAVA_HOME
echo jdk 1.8 already installed

That uses the fmw12c_env.sh script:
echo set Fusion MiddleWare 12cR2 environment
export ORACLE_BASE=/app/oracle
export INVENTORY_DIRECTORY=/app/oraInventory
export JAVA_HOME=$ORACLE_BASE/product/jdk

The script checks java already exists. If not then it checks if the tar or zip file exist in /media/sf_Stage/Java. If the tar file does not exist it will unzip the zip file. If the tar file does exist, then it will create a temp folder to extract the tar file into. Then it will create the java-home folder and move the extracted jdk folder to it.

I put these files in the scripts/fmw folder of my project:
Call script from provisioningNow we're at the point that took me a lot of time to figure out last winter. How do I call this scripts form the provisioning. Simple question, but let me try to explain the difficulty.
The provisioning is done using the vagrant user. The vagrant user is in the sudoers list, so it's able to run a script using the permissions of another user, usually the super user (root). But it is still the running vagrant user who owns the resulting files and folders. I could do something like sudo su - oracle -c "script"... However, it still results in all the files owned by vagrant. So, if I run the Java install script, the complete java tree is owned by vagrant. But I want oracle to own it. Now,  I could create another base box and replace vagrant by oracle as the install user. But that is not the idea.

It took me some time to finally find the great utility runuser. This allows me to run the script as another substitute user. The runuser utility must be run as root, but that's no problem since vagrant is in the sudoers list.

So add the following lines to your provisioning part of the Vagrantfile:
    echo _______________________________________________________________________________
echo 3. Java SDK 8
sudo runuser -l oracle -c '/vagrant/scripts/fmw/installJava.sh'

With the -l argument I denote the user and with -c the command to run.
First tryHaving done that, you could do a first try of the provisioning by upping the box.
Open a command window and start it the first time with vagrant up. Then if all goes well the provisioning ends with:
    darwin: _______________________________________________________________________________
darwin: 3. Java SDK 8
darwin: set Fusion MiddleWare 12cR2 environment
darwin: Checking Java Home: /app/oracle/product/jdk
darwin: Unzip /media/sf_Stage/Java/p27412872_180172_Linux-x86-64.zip to /media/sf_Stage/Extracted/Java/jdk-8u172-linux-x64.tar.gz
darwin: Archive: /media/sf_Stage/Java/p27412872_180172_Linux-x86-64.zip
darwin: inflating: /media/sf_Stage/Extracted/Java/jdk-8u172-linux-x64.rpm
darwin: inflating: /media/sf_Stage/Extracted/Java/jdk-8u172-linux-x64.tar.gz
darwin: inflating: /media/sf_Stage/Extracted/Java/readme.txt
darwin: Install jdk
darwin: create folder /media/sf_Stage/Extracted/jdk
darwin: create JAVA_HOME /app/oracle/product/jdk
darwin: Untar /media/sf_Stage/Java/jdk-8u172-linux-x64.tar.gz to /media/sf_Stage/Extracted/jdk
darwin: tar: jdk1.8.0_172/bin/ControlPanel: Cannot create symlink to `jcontrol': Protocol error
darwin: tar: jdk1.8.0_172/man/ja: Cannot create symlink to `ja_JP.UTF-8': Protocol error
darwin: tar: jdk1.8.0_172/jre/bin/ControlPanel: Cannot create symlink to `jcontrol': Protocol error
darwin: tar: jdk1.8.0_172/jre/lib/amd64/server/libjsig.so: Cannot create symlink to `../libjsig.so': Protocol error
darwin: tar: Exiting with failure status due to previous errors
darwin: Move /media/sf_Stage/Extracted/jdk/jdk1.8.0_172/bin /media/sf_Stage/Extracted/jdk/jdk1.8.0_172/COPYRIGHT /media/sf_Stage/Extracted/jdk/jdk1.8.0_172/db /media/sf_Stage/Extracted/jdk/jdk1.8.0_172/include /media/sf_Stage/Extracted/jdk/jdk1.8.0_172/javafx-src.zip /media/sf_Stage/Extracted/jdk/jdk1.8.0_172/jre /media/sf_Stage/Extracted/jdk/jdk1.8.0_172/lib /media/sf_Stage/Extracted/jdk/jdk1.8.0_172/LICENSE /media/sf_Stage/Extracted/jdk/jdk1.8.0_172/man /media/sf_Stage/Extracted/jdk/jdk1.8.0_172/README.html /media/sf_Stage/Extracted/jdk/jdk1.8.0_172/release /media/sf_Stage/Extracted/jdk/jdk1.8.0_172/src.zip /media/sf_Stage/Extracted/jdk/jdk1.8.0_172/THIRDPARTYLICENSEREADME-JAVAFX.txt /media/sf_Stage/Extracted/jdk/jdk1.8.0_172/THIRDPARTYLICENSEREADME.txt to /app/oracle/product/jdk

It it all went well you can just destroy it:
d:\Projects\vagrant\ol75_db12c>vagrant destroy
darwin: Are you sure you want to destroy the 'darwin' VM? [y/N] y
==> darwin: Forcing shutdown of VM...
==> darwin: Destroying VM and associated drives...

Install databaseInstalling the database is a bit more complex. I'll not post every file, the code can be found here in github, as in fact the rest of my project files.
It expects the database installation as two zip files, V46095-01_2of2.zip and V46095-01_1of2.zip in the folder /media/sf_Stage/DBInstallation/ Like for the rest it works much like my earlier install Fusion Middleware scripts. It unzips the files, installs the database based on the template response files. And it runs the database creation utility.

I also added scripts to install/unzip sqldeveloper and sql commandline.

To install the database and possibly SQLDeveloper and/or SQLcl, just add the following lines to your vagrant file:
    echo _______________________________________________________________________________
echo 4. Database 12c
sudo runuser -l oracle -c '/vagrant/scripts/database/installDB.sh'
echo _______________________________________________________________________________
echo 5.1 SQLCL and SQLDeveloper
sudo runuser -l oracle -c '/vagrant/scripts/database/installSqlcl.sh'
echo _______________________________________________________________________________
echo 5.2 SQLDeveloper
sudo runuser -l oracle -c '/vagrant/scripts/database/installSqlDeveloper.sh'

And run vagrant up again.

ConclusionI didn't get much into the install of the database. I feel that I wrote quite a lot in the past on installing Oracle software. In the upcoming period I'll wrap these lasts blogs into a presentation on creating boxes with Vagrant. This an input to my talk on this subject at the NLOUG Tech Experience 2018.

By now you should be able to replicate my findings and create other boxes as well. In the next blogs I might write about transforming my other FMW install scripts into vagrant project. We should now be able to create Vagrant projects to install OSB, SOA/BPM Suite, SOA/BPM Quickstart, etc.

Lately I did a Docker install in an Ubuntu 16 base box. Ubuntu has some peculiarities in creating users and logical volume management as opposed to RedHat/Oracle Linux. I might also write about that. But feel free to leave a comment if you have particular wishes. However, please have a bit of patience with me, since I first need to get my talk on the Tech Experience ready.

Installing haveged from Oracle repositories

Tue, 2018-05-01 10:14
In my previous blog, I mentioned the installation of the utility Haveged that is used to increase your entropy on non-gui systems. Since you can download it from the Oracle yum repository, I figured that I could install it from yum too, instead of using rpm. This prevents me from having to download it myself, and ensures I have the one that is applicable to my version of Linux.

Too bad it isn't in the default ol7_latest repo. So you have to either add the yum repo above or enable it.

To add it, you can do:
[vagrant@darlin-vce ~]$ sudo yum-config-manager --add-repo https://yum.oracle.com/repo/OracleLinux/OL7/developer_EPEL/x86_64

To remove it, you can remove the corresponding file from /etc/yum.repos.d:
[vagrant@darlin-vce ~]$ cd /etc/yum.repos.d/
[vagrant@darlin-vce yum.repos.d]$ ls
[vagrant@darlin-vce yum.repos.d]$ sudo rm yum.oracle.com_repo_OracleLinux_OL7_developer_EPEL_x86_64.repo
[vagrant@darlin-vce yum.repos.d]$ ls

But it appears already registered in the repository list, but disabled:

[vagrant@darlin-vce yum.repos.d]$ sudo yum repolist all
Loaded plugins: langpacks, ulninfo
repo id repo name status
ol7_MODRHCK/x86_64 Latest RHCK with fixes from Oracle for Oracle Linux 7Server (x86_64) disabled
ol7_MySQL55/x86_64 MySQL 5.5 for Oracle Linux 7 (x86_64) disabled
ol7_MySQL56/x86_64 MySQL 5.6 for Oracle Linux 7 (x86_64) disabled
ol7_MySQL57/x86_64 MySQL 5.7 for Oracle Linux 7 (x86_64) disabled
ol7_UEKR3/x86_64 Latest Unbreakable Enterprise Kernel Release 3 for Oracle Linux 7Server (x86_64) disabled
ol7_UEKR3_OFED20/x86_64 OFED supporting tool packages for Unbreakable Enterprise Kernel on Oracle Linux 7 (x86_64) disabled
ol7_UEKR4/x86_64 Latest Unbreakable Enterprise Kernel Release 4 for Oracle Linux 7Server (x86_64) enabled: 641
ol7_UEKR4_OFED/x86_64 OFED supporting tool packages for Unbreakable Enterprise Kernel Release 4 on Oracle Linux 7 (x86_64) disabled
ol7_addons/x86_64 Oracle Linux 7Server Add ons (x86_64) disabled
ol7_ceph/x86_64 Ceph Storage for Oracle Linux Release 2.0 - Oracle Linux 7.2 or later (x86_64) disabled
ol7_ceph10/x86_64 Ceph Storage for Oracle Linux Release 1.0 - Oracle Linux 7.1 or later (x86_64) disabled
ol7_developer/x86_64 Oracle Linux 7Server Development Packages (x86_64) disabled
ol7_developer_EPEL/x86_64 Oracle Linux 7Server Development Packages (x86_64) disabled
ol7_developer_gluster310/x86_64 Oracle Linux 7Server Gluster 3.10 Packages for Development and test (x86_64) disabled
ol7_developer_gluster312/x86_64 Oracle Linux 7Server Gluster 3.12 Packages for Development and test (x86_64) disabled
ol7_developer_nodejs4/x86_64 Oracle Linux 7Server Node.js 4 Packages for Development and test (x86_64) disabled
ol7_developer_nodejs6/x86_64 Oracle Linux 7Server Node.js 6 Packages for Development and test (x86_64) disabled
ol7_developer_nodejs8/x86_64 Oracle Linux 7Server Node.js 8 Packages for Development and test (x86_64) disabled
ol7_developer_php70/x86_64 Oracle Linux 7Server PHP 7.0 Packages for Development and test (x86_64) disabled
ol7_developer_php71/x86_64 Oracle Linux 7Server PHP 7.1 Packages for Development and test (x86_64) disabled
ol7_developer_php72/x86_64 Oracle Linux 7Server PHP 7.2 Packages for Development and test (x86_64) disabled
ol7_latest/x86_64 Oracle Linux 7Server Latest (x86_64) enabled: 26,602

To enable it just:
sudo yum-config-manager --enable ol7_developer_EPEL

As a response it will display the current configuration.
Then install haveged by:
sudo yum -q -y install haveged

To check if haveged is installed, do a yum -list:
[vagrant@darlin-vce ~]$ sudo yum list haveged
Loaded plugins: langpacks, ulninfo
Installed Packages

This is drawn by combining this RHEL Tech-Doc with the repo content of my freshly installed OL7U5 box.

And that solves the note I had left in my previous blog post.

Base box ready? Let's create a box!

Tue, 2018-05-01 08:04
Last week I wrote about creating our own Vagrant base box, based on the new and fresh Oracle Linux 7 Update 5. As a reaction on my article I got noted that Oracle also keeps base boxes for their latest linuxes at http://yum.oracle.com/boxes. They're also a great start.

To begin, I created a project structure for all my vagrant projects:
As you can see, I have a vagrant main projects folder on my D: drive, D:\Projects\vagrant. Besides my actual vagrant projects, like oel74, oel74_wls12c etc., I have a boxes folder and a Stage folder. The boxes folder, as you can guess, contains my base boxes:
The Stage folder contains all my installation-binaries, for database, Weblogic, Java, FusionMiddleware and so on.

Today I want to create a basic VM that will be a base for further VM's, like database, weblogic, etc.
I want the VM to have:
  • Linux prepared with correct kernel settings for database, FusionMiddleware, etc.
  • Filesystem created on a second disk. I did not add a second disk to the base box, only a root disk. Thus we need to extend the VM with one.
  • Create an oracle user that can sudo.
Initialize a vagrant project Begin with creating a folder, like ol75 in the structure, for this project. Open a command window and navigate to it:
Microsoft Windows [Version 10.0.16299.371]
(c) 2017 Microsoft Corporation. All rights reserved.


D:\>cd d:\Projects\vagrant\ol75\

d:\Projects\vagrant\ol75>vagrant help init
==> vagrant: A new version of Vagrant is available: 2.0.4!
==> vagrant: To upgrade visit: https://www.vagrantup.com/downloads.html

Usage: vagrant init [options] [name [url]]


--box-version VERSION Version of the box to add
-f, --force Overwrite existing Vagrantfile
-m, --minimal Use minimal Vagrantfile template (no help comments). Ignored with --template
--output FILE Output path for the box. '-' for stdout
--template FILE Path to custom Vagrantfile template
-h, --help Print this help


The vagrant command init creates a new vagrant project, with a so called Vagrantfile in it. By default, you'll get a Vagrantfile with the most common settings and comments explaining  the most common additional settings. But using the -m or --minimal setting a just enough vagrant file is created, without comments. I do like a file with the most common settings in the comments, as it allows me to quickly extend it without having to lookup every thing. So I create a basic file:
d:\Projects\vagrant\ol75>vagrant init
A `Vagrantfile` has been placed in this directory. You are now
ready to `vagrant up` your first virtual environment! Please read
the comments in the Vagrantfile as well as documentation on
`vagrantup.com` for more information on using Vagrant.

d:\Projects\vagrant\ol75>dir /w
Volume in drive D is DATA
Volume Serial Number is 62D7-9456

Directory of d:\Projects\vagrant\ol75

[.] [..] Vagrantfile
1 File(s) 3,081 bytes
2 Dir(s) 651,847,397,376 bytes free

Now, let's expand the file bit by bit. So, open it in your favorite ASCII editor, like Notepad++, for instance.
# -*- mode: ruby -*-
# vi: set ft=ruby :

# All Vagrant configuration is done below. The "2" in Vagrant.configure
# configures the configuration version (we support older styles for
# backwards compatibility). Please don't change it unless you know what
# you're doing.
Vagrant.configure("2") do |config|
# The most common configuration options are documented and commented below.
# For a complete reference, please see the online documentation at
# https://docs.vagrantup.com.

# Every Vagrant development environment requires a box. You can search for
# boxes at https://vagrantcloud.com/search.
config.vm.box = "base"

As with many other scripted languages, I find it convenient to have all the configurable values declared as global variables at the top of the file. So let's declare some:
# -*- mode: ruby -*-
# vi: set ft=ruby :
VM_MEMORY = 12288 # 12*1024 MB
VM_DISK2_SIZE=1024 * 512
# Stage folders
Then find the line ‘Vagrant.configure("2") do |config|’. This starts the block that configures the box. All the following configuration is done between that line and the corresponnding closing end line.

Start with renaming the box based on the global variable:
  config.vm.box = BOX_NAME

And add the following lines:
config.vm.define "darwin"

The config.vm.box_url directive refers to the base box used. Often this is just the name of the box that is automatically downloaded from the Hashicorp/Vagrant repository. You can also use a URL to a box on internet, it will be downloaded automatically. But we created our own box, so that we exactly know and control what's in it. It does not need to be downloaded, it's available right away.
Side note: manage your boxes At start up this box is added to your local Vagrant box repository. You can see the boxes in your repository with the command box list:
d:\Projects\vagrant\ol75>vagrant box list
OL7U4 (virtualbox, 0)
OL7U4-1.1b (virtualbox, 0)
Ubuntu16.0.4LTS (virtualbox, 0)


These boxes are found in the .vagrant.d/boxes folder your user profile:
My new ol75 box is not added yet, as you can see. It will be done at first up. Since they're eating up costly space on my SSD drive, it's sensible to remove unused boxes. For instance, the  OL7U4 one is superseded by the OL7U4-1.1b. The Ubuntu one I still use, although the Ubuntu version is a bit old. So, I should at least remove the OL7U4 one. It can be done with the command box remove ${name}:

d:\Projects\vagrant\ol75>vagrant box remove OL7U4
Removing box 'OL7U4' (v0) with provider 'virtualbox'...

d:\Projects\vagrant\ol75>vagrant box list
OL7U4-1.1b (virtualbox, 0)
Ubuntu16.0.4LTS (virtualbox, 0)

Support for multi-machine boxesThe other line I added was the config.vm.define directive. This one allows to define multi-machine definitions in one Vagrant project. It can come in handy when you have a project where one VM is servicing your database, while another is doing your front-end application. You can have them started and provisioned automatically, where the provisioning can be done in stages. Or you can switch off auto-start for certain machines and start those explicitly. It's a nice-to-know, but I'll leave it for now. I use it for noting the machine in the provisioning logs.
Read more about multi-machine configs here.
SSH Vagrant UserBelow the config.vm.* lines, within the config block, you can add some config lines for the vagrant username/password:

The password line should be optional, since we injected a key for the vagrant user. You'll see that Vagrant will replace that key. The ssh.port will direct Vagrant to create a port-forwarding for the local port 2222 to the ssh port on the vm.
Provider config
Within the config block, below the line config.fm.define add the following block:
  config.vm.provider :virtualbox do |vb|
vb.name = VM_NAME
vb.gui = true
vb.memory = VM_MEMORY
vb.cpus = VM_CPUS
# Set clipboard and drag&drop bidirectional
vb.customize ["modifyvm", :id, "--clipboard", "bidirectional"]
vb.customize ["modifyvm", :id, "--draganddrop", "bidirectional"]
# Create a disk
unless File.exist?(VM_DISK2)
vb.customize [ "createmedium", "disk", "--filename", VM_DISK2, "--format", "vdi", "--size", VM_DISK2_SIZE , "--variant", "Standard" ]
# Add it to the VM.
vb.customize [ "storageattach", :id , "--storagectl", "SATA", "--port", "2", "--device", "0", "--type", "hdd", "--medium", VM_DISK2]

This block begins setting the following properties:
vb.name Name of the VM to create. This is how the VM will appear in VirtualBox. vb.gui This toggles the appearance of the UI of the VM. If false, it is started in the background. It is then only reachable through the network settings. Using the VirtualBox manager the GUI can then be brought to appear using the show button. vb.memory This sets the memory available to the VM. In the global variables I have set it to 12GB (12*1024 MB) vb.cpus Number of CPU cores availabe to the VM. In the globals I have set it to 4.
Using the customize provider command you can change the VM's configuration. It is in fact an API to the VboxManage utility of VirtualBox.

With modifyvm we set both the properties --clipboard and --draganddrop to bidirectional. When the GUI is shown (vb.gui = true) then it allows us to copy and paste into the VM for instance.

Then using  the createmedium command a standard vdi disk is created. The name VM_DISK2 is based on the variables VMS_HOME and VM_NAME. See the top of the file. It's convenient to have the file created in the same folder as the VM is created in. So check the VirtualBox preferences:

The Default Machine Folder preference is used to create the VM in, in a subfolder indicated by the name of the VM. So make sure that the VMS_HOME variable matches the value in that preference.

The value for --format the createmedium command I used is vdi. This is the default Virtual Disk Image format of VirtualBox. When exporting a VM into an OVA ( Open Virtual Appliance package, which is a tar archive file) the VDI disks are converted to the VMDK  (Virtual Machine Disk).
The size is set with the --size parameter, in my example set to the VM_DISK2_SIZE that I created in the top of the file as 512GB (1024 * 512).
The --variant Standard indicates a dynamically allocated file, that grows with the filling of it. So, you won't loose the complete filesize on diskspace. The 512GB limits the growth of the disk.

I want the disk to persist and not recreated at every startup, so I surrounded that command with the
unless File.exist?(VM_DISK2) block.

Using the storageattach I add it to my SATA controller using the --storagectl SATA parameters. It's added to --port 2 and --device 0, since it is my second drive. It needs to appear secondly in Linux. Then ofcourse the --type is hdd and the --medium is VM_DISK2.

You see a special variable :id. This refers to the VM that is created in VirtualBox. Of course I want the disk attached to the proper VM.
Shared/Synced foldersVagrant by default creates a folder-link, a so-called Synced Folder, the wrapper around VirtualBox’s Shared Folder functionality. The default refers to the Vagrant project folder from which the VM is created and provisioned. Thus, in fact the folder where the Vagrantfile resides. That folder is mounted in the VM as /vagrant. So, navigating to the /vagrant folder in the VM will show you the files from the vagrant-project folder and child folders. This is convenient, because subfolders in that folder, for instance a scripts folder with provisioning scripts, are immediately available at startup.

Since we want to install software from the Stage folder on our host, we need a mapping to that.
So find the following line:
 # config.vm.synced_folder "../data", "/vagrant_data"

This is an example line for configuring additional synced folders. Add a line below it as follows:
  config.vm.synced_folder STAGE_HOST_FOLDER, STAGE_GUEST_FOLDER

This maps the folder on the host as denoted in the global variable STAGE_HOST_FOLDER, and then mount that as the value from the global STAGE_GUEST_FOLDER. Notice that I doing so I map the folder /media/sf_Stage in the VM to the folder d:/Projects/vagrant/Stage on the host. Wich is a sub-folder in my main vagrant project folder.
ProvisioningHaving the VM configured, the provisioning part is to be configured. Vagrant allows for several provisioners like Puppet, Chef, Ansible, Salt, and Docker. But a Shell snippet is provided in our Vagrantfile. I'll expand that one. At the bottom of the file, right above the closing end of our configure block, we'll find the snippet:
# config.vm.provision "shell", inline: <<-SHELL
# apt-get update
# apt-get install -y apache2
Replace it with the following block:
  config.vm.provision "shell", inline: <<-SHELL
export SCRIPT_HOME=/vagrant/scripts
echo _______________________________________________________________________________
echo 0. Prepare Oracle Linux
echo _______________________________________________________________________________
echo 1. Create Filesystem
echo _______________________________________________________________________________
echo 2. Create Oracle User
This provides an inline script. I like that because it allows me to see directly what happens in helicopter view. But, I do not like to have all the detailed steps in here, so I only call sub-scripts within this block. You can also uses external scripts, even remote ones, as described in the doc.

As can be seen the scripts I'll describe below have to be placed in the scripts folder as part of the vagrant project folder. Remember that this folder is mapped as a synched folder to /vagrant in the VM.

I  have the following script, called 0.PrepOEL.sh, to  update the Oracle Linux installation in the VM:
SCRIPTPATH=$(dirname $0)
. $SCRIPTPATH/install_env.sh
echo Installing packages required by the software
sudo yum -q -y install compat-libcap1* compat-libstdc* libstdc* gcc-c++* ksh libaio-devel* dos2unix system-storage-manager
echo install Haveged
sudo rpm -ihv $STAGE_HOME/Linux/haveged-1.9.1-1.el7.x86_64.rpm
echo 'Adding entries into /etc/security/limits.conf for oracle user'
if grep -Fq oracle /etc/security/limits.conf
echo 'WARNING: Skipping, please verify!'
echo 'Adding'
sudo sh -c "sed -i '/End of file/i # Oracle account settings\noracle soft core unlimited\noracle hard core unlimited\noracle soft data unlimited\noracle hard data unlimited\noracle soft memlock 3500000\noracle hard memlock 3500000\noracle soft nofile 1048576\noracle hard nofile 1048576\noracle soft rss unlimited\noracle hard rss unlimited\noracle soft stack unlimited\noracle hard stack unlimited\noracle soft cpu unlimited\noracle hard cpu unlimited\noracle soft nproc unlimited\noracle hard nproc unlimited\n' /etc/security/limits.conf"

echo 'Changing /etc/sysctl.conf'
if grep -Fq net.core.rmem_max /etc/sysctl.conf
echo 'WARNING: Skipping, please verify!'
echo 'Adding'
sudo sh -c "echo '
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 4294967295
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 4194304
/sbin/sysctl -p
It uses the following script, install_env.sh:
echo set Install environment
export STAGE_HOME=/media/sf_Stage
export SCRIPT_HOME=/vagrant/scripts
to set a few HOME variables.
It first install required packages using sudo yum . These packages are required for most of the Oracle software setup, like database and/or FusionMiddleware.
Then from the Linux folder in the STAGE_HOME folder, the tool Haveged is installed. You can download it from the Oracle yum repository. So, I should be able to have it installed with yum as well. One improvement point noted.

Why Haveged you ask? On non-gui, terminal only virtualized systems, the entropy maybe low, which causes slow encryption/decryptions. Installing and configuring FusionMiddleware, for instance, or starting WebLogic maybe very slow.

Then it set some limits for the oracle  user and kernel configs. These can be found in the install guides of the Oracle software.

To create the file system I use the script 1.FileSystem.sh:
echo Create folder for mountpoint /app
sudo mkdir /app
echo Create a Logical Volume group and Volume on sdb
sudo ssm create -s 511GB -n disk01 --fstype xfs -p pool01 /dev/sdb /app
sudo ssm list
sudo sh -c "echo \"/dev/mapper/pool01-disk01 /app xfs defaults 0 0\" >> /etc/fstab"

This one creates a folder  /appfor the file mount. Then it uses ssm (System Storage Manager) to create a 511GB (because of overhead I can't create a filesystem of 512GB) filesystem using a Logical Volume in a Logical Volume Group on the sdb device in Linux. For more info on how this works, read my blog article on it.

That leaves us to create an oracle user. For that I use the script 2.MakeOracleUser.sh:
# Script to create a OS group and user
# The script is using the file /home/root/oracle.properties to read the properties you can set, such as the password
SCRIPTPATH=$(dirname $0)


function prop {
grep "${1}" $SCRIPTPATH/makeOracleUser.properties|cut -d'=' -f2

# As we are using the database as well, we need a group named dba
echo Creating group dba
sudo /usr/sbin/groupadd -g 2001 dba

# We also need a group named oinstall as Oracle Inventory group
echo create group oinstall
sudo /usr/sbin/groupadd -g 2000 oinstall

# Create the Oracle user
echo Create the oracle user
sudo /usr/sbin/useradd -u 2000 -g oinstall -G dba oracle
echo Setting the oracle password to...
sudo sh -c "echo $(prop 'oracle.password') |passwd oracle --stdin"
sudo chown oracle:oinstall /app
# Add Oracle to sudoers so he can perform admin tasks
echo Adding oracle user to sudo-ers.
sudo sh -c "echo 'oracle ALL=NOPASSWD: ALL' >> /etc/sudoers"
# Create oraInst.loc and grant to Oracle
echo Create oraInventory folder
sudo chown -R oracle:oinstall /app
sudo mkdir -p /app/oracle/oraInventory
sudo chown -R oracle:oinstall /app/oracle
echo Create oraInst.loc and grant to Oracle
sudo sh -c "echo \"inventory_loc=/app/oracle/oraInventory\" > /etc/oraInst.loc"
#sudo sh -c "echo \"\" > /etc/oraInst.loc"
sudo sh -c "echo \"inst_group=oinstall\" >> /etc/oraInst.loc"
sudo chown oracle:oinstall /etc/oraInst.loc

It uses a property file makeOracleUser.properties for the oracle password:

Using the prop function this property is read.

The groups dba and oinstall are created. Then the oracle user is created and it's password set. The filesystem mounted on /app is assigned to the oracle user to own. And then the user is added to tue /etc/sudoers file.

Lastly the oraInventory and the oraInst.loc file are created.
Up, up and up it goes…!If everything went alright, you’re now ready to fire up your VM.
So open a command window and navigate to your vagrant project folder, if not done already.
Then simply issue the following command:
d:\Projects\vagrant\ol75>vagrant up

And then you wait…. And watch carefully to see that Vagrant imports the box, creates the VM, and provisions it.

Some other helpfull commandsI'll finish with some other helpfull commands:
vagrant up Start a VM, and provision it the first time. vagrant halt Stop the VM. vagrant suspend Remove the VM. vagrant destroy Number of CPU cores availabe to the VM. In the globals I have set it to 4. vagrant box list Lists the base boxes in your repository. vagrant box remove Remove a listed base box from your repository. vagrant package --base <VM Name> --output <box filename> Package the VM <VM Name> from the provider (VirtualBox) into a base box with given <box filename>.
Next stop: installing software as an oracle (or othernon-vagrant) user  user.

Oracle Linux 7 Update 5 is out: time to create a new Vagrant Base Box

Tue, 2018-04-24 09:31
It's been busy, so unfortunately it's already been almost two weeks I wrote my introductory story on Vagrant. Today I happen to have an afternoon off, and I noticed that Oracle Linux 7 Update 5 is out. I based my first boxes on 7.4, so nice moment to start with creating a new Base Box.

De essentials on creating a Vagrant base box can be read here. But I'm going to guide you trough the process step by step, so I hope you will be able to repeat this yourself, using this guide-through.

First of, Vagrant recommends Packer to automate the creation of base boxes. But I'm a bit confused, because in this guide it is apparently stated that this is deprecated by march 2018. I haven't tried Packer yet, but I feel that over the years I created a base VM only a few times. I used to create a base VM that I import/clone to create new VMs over and over again. And often, I start of with a VM that already contains a pre-installed database for instance.

Vagrant has a built in command to create a base box out of an existing VM. That is what I use.
Base box requirementsWhat is a Base Box actually? Well, it's in fact sort of a template that is used by Vagrant to create and configure a new VM and provision that. It should contain the following
  • An OS: I use Oracle Linux 7 Update 5 for this story. I also have a base box with Ubuntu. Ubuntu has some peculiarities I want to discuss later on in this series. For this base box I'll install a server-with-gui. But further as basic as possible.
  • A vagrant user. The vagrant user is used for provisioning the box. We'll place a public insecure key in it, that will be replaced by Vagrant at first startup. We'll add vagrant to the sudoers list, so the user can sudo without passwords.
  • A started ssh daemon:  Vagrant connects via ssh using the vagrant-user to do the provisioning.
  • A NAT (Network Address Translation) Adapter as the first one: needed to do kernel/package updates without further network configuration.
  • VirtualBox GuestAdditions installed: Vagrant makes use of shared folders to map the project folder to get to the scripts. Also it's convenient to add an extra stage folder mapping. 
  • Password of root: not a requirement, but apparently it's a bit of a standard to set the root password to vagrant as ease of sharing. But at least note down the passwords.
That's about it. Maybe I forget something, but since it's digital, I can edit it later... So let's get started.

Download  Oracle LinuxAll the serious enterprise stuff of Oracle can be downloaded at edelivery. Search for Oracle Linux:
Then add the 7.5 version to the Cart by clicking it:

Follow the wizard instructions and you'll get to:
I downloaded V975367-01.iso        Oracle Linux Release 7 Update 5 for x86 (64 bit), 4.1 GB.

Create the VMThe ISO is downloading, so let's create a VM in VirtualBox. I assume VirtualBox with VirtualBox Extension Pack is installed. And for later on Vagrant of course.

From the Oracle VM VirtualBox Manager, create a new VM, I called it OL75, for Oracle Linux 64 bit:
I followed the wizard and gave it 10240 MB memory and a 128GB dynamically allocated virtual disk:

In the VM Settings, I set the number of processors to 4 and for now I kept everything to the default.

In the meantime my download is ready, so in the VM Settings, under Storage I added the disk by clicking the disk icon next to the IDE controller:

Then navigate to your downloaded iso:
and select it. Now the VM is ready to kick-off:

It will startup automatically after a minute, but let's not wait that long.

I don't need much, but in the Sofware Selection I do want Server with GUI:
But with out selecting other packages. What I might need later on, I'll install at provisioning.

I do not like default local domain networknames. So I changed the network hostname to darlin-vce.darwin-it.local:
Hostname darlin stands for Darwin Linux and vce for Virtual Course Environment.

Then hit Begin Installation:

Soon in the installation the installer asks for the Root password:
And the password is as said: vagrant.
Then I add also a vagrant with the same password:
Having done that, we need to wait for the installer to finish. At the end of the Install, do a reboot:

This leads to 2 questions to be answered. One is about accepting the licensing. I assume that can be answered without guidance. The other is about connecting the network.

You need to switch on the network adapter, but to have it done automatically you need to configure it and check the box Automatically connect to this network when it is available on the General tab. You'll need to have this done, otherwise Vagrant will have difficulties in connecting to the box.
Then finish the configuration:
Install guest additionsTo be able to install the guest additions, we need to add some kernel packages. We could have done that by installing additional kernel packages. But I wanted to have a as basic as possible installation. And the following is more fun...

So open a terminal and switch to the super user:

[vagrant@darlin-vce ~]$ su -
Last login: Tue Apr 24 09:41:21 EDT 2018 on pts/0

Then stop package kit, because it will probably hold a lock pausing yum:
[root@darlin-vce ~]# systemctl stop packagekit

And then install the packages kernel-uek-devel kernel-uek-devel-4.1.12-112.16.4.el7uek.x86_64, that are suggested by the GuestAdditions installer, by the way:
[root@darlin-vce ~]# yum -q -y install kernel-uek-devel kernel-uek-devel-4.1.12-112.16.4.el7uek.x86_64
No Presto metadata available for ol7_UEKR4
warning: /var/cache/yum/x86_64/7Server/ol7_latest/packages/cpp-4.8.5-28.0.1.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
Public key for cpp-4.8.5-28.0.1.el7.x86_64.rpm is not installed
Public key for kernel-uek-devel-4.1.12-124.14.1.el7uek.x86_64.rpm is not installed
Importing GPG key 0xEC551F03:
Userid : "Oracle OSS group (Open Source Software group) "
Fingerprint: 4214 4123 fecf c55b 9086 313d 72f9 7b74 ec55 1f03
Package : 7:oraclelinux-release-7.5-1.0.3.el7.x86_64 (@anaconda/7.5)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-oracle

Having done that, insert the GuestAdditions CD:
It brings the following pop-up, click Run:

And provide the Administrator password:

In my case the script ran and during that the display got messed up. But after a reset of the VM (I waited until I got the impression it was done), the VM got up with a Hi-res display, indicating that the install went ok. Also the bi-directional clipboard worked.

Configure vagrant userAgain in a terminal switch to super user and add the following line to the /etc/sudoers file:

Exit and as vagrant user create a .ssh folder in the vagrant home folder, cd to it and create the file authorized_keys:
[vagrant@darlin-vce ~]$ mkdir .ssh
[vagrant@darlin-vce ~]$ cd .ssh
[vagrant@darlin-vce .ssh]$ vi authorized_keys

Insert the following content:
ssh-rsa AAAAB3NzaC1yc2EAAAABIwAAAQEA6NF8iallvQVp22WDkTkyrtvp9eWW6A8YVr+kz4TjGYe7gHzIw+niNltGEFHzD8+v1I2YJ6oXevct1YeS0o9HZyN1Q9qgCgzUFtdOKLv6IedplqoPkcmF0aYet2PkEDo3MlTBckFXPITAMzF8dJSIFo9D8HfdOV0IAdx4O7PtixWKn5y2hMNG0zQPyUecp4pzC6kivAIhyfHilFR61RGL+GPXQ2MWZWFYbAGjyiYJnAmCP3NOTd0jMZEnDkbUvxhMmBYSdETk1rRgm+R4LOzFUGaHqHDLKLX+FIPKcF96hrucXzcWyLbIbEgE98OHlnVYCzRdK8jlqm8tehUc9c9WhQ== vagrant insecure public key

This is the insecure key of vagrant that can be downloaded here.
It will be replaced by Vagrant at first startup.

Package the boxSo, now we have a base install that can function as a base box for Vagrant. Thus we can now shut it down to export it to an OVA (just as a backup for VirtualBox) and then create are base box out of it.

After creating your export of the OVA, that I skip describing here, you just open a command window. I assume you have Vagrant installed.

To package the box, you use the package subcommand of vagrant:

Microsoft Windows [Version 10.0.16299.371]
(c) 2017 Microsoft Corporation. All rights reserved.

d:\Projects\vagrant>vagrant package --base OL75 --output d:\Projects\vagrant\boxes\OL75v1.0.box
==> OL75: Exporting VM...
==> OL75: Compressing package to: d:/Projects/vagrant/boxes/OL75v1.0.box


Conclusion Well, that concludes this part of the series. We have our own base box and it's barely 3GB. Next: create a VM with it. Stay tuned.

Garbage First in JDeveloper

Thu, 2018-04-19 01:07
At my current customer we work with VDI's: Virtual Desktop Images, that at several times a day very, very slow. Even so slow that it more or less stalls for a minute or two.

JDeveloper is not known as a Ferrari under the IDE's. One of the causes is that by default heap settings is very poor: 128M-800M. Especially when you use it in  SOA or BPM Quickstart then at startup it will need to grow several times. But very soon working in it you'll get out of memory errors.

Because of the VDI's I did several changes to try to improve performance.
Main thing is set Xms and Xmx both at 2048M. I haven't found needing more up to this day.

But I found using the Garbage First collector gives me a slightly better performance.

To set it, together with the heap, add/change the following options in the ide.conf in ${JDEV_HOME}\jdeveloper\ide\bin\:
# Set the default memory options for the Java VM which apply to both 32 and 64-bit VM's.
# These values can be overridden in the user .conf file, see the comment at the top of this file.
#AddVMOption -Xms128M
#AddVMOption -Xmx800M
AddVMOption -Xms2048M
AddVMOption -Xmx2048M
AddVMOption -XX:+UseG1GC
AddVMOption -XX:MaxGCPauseMillis=200

Find more on the command line options in this G1GC tutorial.

You can also use the ParNew incombination with the ParOld or ConcMarkSeep collector, as suggested in this blog. But from Java9 onwards G1GC is the default, and I expect that it better fits the behavior of JDeveloper, as in SOASuite and OSB installations.

The vagrant way of provisioning - an introduction

Wed, 2018-04-11 09:16
About 2003, I guess, I was introduced into VMWare by a colleague. I was hooked about right away.
Since then I made numerous VMs for as many purposes. I played around with several VMWare products, but since Oracle acquired Sun, I stuck with VirtualBox.

A few years ago the tool Vagrant was mentioned to me. But I did not get the advantage of it, since all I needed to do I could do using VirtualBox.

However, over the years I found that maintaining VM's is a tedious job. And often I create and use a VM, but shut it down for months. And when I need it again, I don't the state anymore. Although you can use snapshots, it's nice to be able to start with a fresh install again.

In between, Oracle can have come up with another (minor) version of Fusion Middleware. Oracle Linux can have an new upgrade. There's a new patch set. Then you want to do a re-install of the software. And I find it nice that I can drop a VM and recreate it from scratch again.

For those purposes Vagrant comes in handy. It allows you to define a Box, based on a template, a base box, and configure it by configure CPU and memory settings, add disks and then after first boot, provision it.
So if I want to adapt a VM with a slightly different setting, or I need an extra disk, I destroy my box, adapt my Vagrant project file, and boot my box up again.

So, let's see what Vagrant is, actually. Then in a follow-up, I'll explain how I setup my Vagrant Project.

Vagrant is an open-source software product for building and maintaining portable virtual software development environments. So it helps you in creating and building Virtual Machines, especially in situations where you need to do that regularly and distribute those.

It simplifies software configuration management of virtualizations, to increase development productivity. Vagrant automates both the creation of VM’s and the provisioning of created VM's.  It does this by abstracting the configuration of the virtualization component and the installation/setup of the software within the VM, via a project file.

The architecture distinguishes two building blocks: Providers and Provisioners.
Providers are services to set up and create VMs, for instance:
  • VirtualBox
  • Docker
  • Vmware
  • AWS
Provisioners are tools to customize the configuration of VM, for example, configure the guest OS and install software within the VM. Possible provisioners are: 
  • Shell
  • Ansible
  • Puppet
  • Chef
I haven't made my self familiar with Ansible or Puppet, yet, (still on my list), so I work with the default provisioner: Shell.

A Vagrant project is in fact a folder with the Vagrant file in it. The vagrant file contains all the configuration of the resulting Vagrant box, the actual created VM.

A Vagrant project is always based on  a base box. Often, a downloadable box from a Vagrant repository is used. In fact, if you don't specify an url, but only a name, it will try to find it from the Vagrant repository. A popular one is the hashicorp/precise64, used in many examples. However, I prefer to use my own local box. For two main reasons:
  • I then know what's init.
  • It's local, so I don't have to download it.
To be able to be used by Vagrant, a box has the following requirements:
  • It contains an actual VM, with OS installed in it.
  • A vagrant user is defined, with sudo rights, and an insecure key (downloadable from vagrant's github, but it will be replaced by a generated secure key at first startup), but  you can specify a password (as I do).
  • NAT network adapter as a first NIC.
  • SSH deamon running.
There is a tool, called Packer, that is  able to create a box, with an OS installed. I haven't tried it, but actually, I created a very simple VM in VirtualBox, installed Oracle Linux 7 Update 4, with the server with gui option as a next-next-finish install in it, defined the vagrant user as mentioned in it. And then with the vagrant package command I got the particular box. I had a few iterations to get it as I wanted it. But once you get it right, you should not need to touch it. Unless another Linux update comes along.

Now I have only one simple base box, and I only need to define different vagrant projects and a stage folder with the latest greatest on the software downloaded. And a simple vagrant up command will create my VM a new, and install all the software in it.

Last year, on the NLOUG's Tech Experience '17, together with my colleague Rob, I spoke about how to script a complete Oracle Fusion MiddleWare environment. It was a result of a series of projects we did up to the event, where we tried to automate the environment creation as much as possible. See my series of blogs on the matter. In the upcoming period, I plan to write about how to leverage these scripts with Vagrant to set up a complete VM, with the latest greatest FMW in it.

So stay tuned.

PaaSForum and the talk with the two ladies...

Tue, 2018-04-10 07:13
It's already been a week or three that I've been to the excellent PaaSForum '18 in Budapest.
Much is already said and written about it. About the talks and breakout sessions.
To see and hear about the state of art of the Oracle PaaS products: every year I'm having a good time with Oracle Friends around Europe and beyond.

It was nice to play around with API Management, Dynamic Processes, Oracle JET and ChatBots. And, ..., to do a few runs to the Donau and back again. I hadn't run for about half a year because of my relocation and remodelling of our new home.

But besides the great talks with Product Management and other Oracle Friends, the thing that maybe made me most exciting was the talk with two ladies: Mary Beth and Liza... A review session about the User Experience of the Oracle PaaS products.

My major concern about the PaaS products, or maybe the Oracle Cloud products in general, is that they're very 'siloed'. Looking at ICS, PCS, VBCS (now bundled in OIC), API Management, Chatbots, CX, etc., they all have a very different history of birth.
Created in different teams. And all have a different User Experience, although Oracle did work in creating a uniform UI definition.
If I want to start with a project with different services, then I need to create and provision my different services. Those all have different URLs, etc. And if I want to move my artifacts to PreProduction or Production, I need to create new services, with their own authentication and authorisation schemas.
And I need to do the release/install of those artifacts to the different environments, myself.

In the feedback session, Liza presented me two personas (Mary Beth was taking notes): a Development Manager and a Developer.
The Development manager will be able to login to the PaaS environment landing page and create a new project. He will be able to select the components by himself or base the project on a template. This will then select the particular components, or project features, so to speak. You could compare it with creating an application in JDeveloper where you get to choose between a Java application, ServiceBus, SOA Suite or BPM Suite, and one or more appropriate projects. Creating such a PaaS Project will provision the necessary Cloud services, as indicated by the chosen components. The Development Manager can also invite project members, that get an invite with URL via email, or Slack, etc.

The Developer, can follow the link in the invite and log on to the Unified PaaS environment, or One PaaS, and from a palette can select a component he/she wants to create and work upon. I suggested that the palette should be restricted by the components selected on creation by the Development Manager. Cloud Services cost subscription fees, pressing on the budget. So, when a developer finds he needs to be able to create a certain component, the Development Manager should approve the addition of that component type, to solve the particular problem. Maybe the tiles of component types for which the project does not have a Cloud Service could be grayed out.

Another suggested addition is the environments management. They foresee a kind of devops administration page, where you can see the dev, test, pre-prod and production environments, and the artifact-versions mapped over that. So that you can see what version of which artifacts are on which environment/services. I suggested that it would be nice, in my opinion, to define releases or configurations of artifacts. Some artifacts are related to each other, for instance a certain version of a VBCS screen is dependent on a version of a REST service in OIC and/or API Mgt. So you want to combine those in a release, to make sure that they're released/installed to the next environment together.

Of course I can't show you any screens or inside information. Not lastly because I only saw mock-up screens myself. 

I got a nice present, a small Bluetooth speaker with a surprisingly great sound.  But, and be assured: I don't have Oracle shares (anymore), the biggest takeaway for me, that made me enthusiastic was the knowledge and the assurance that Oracle is really putting much effort in this. It is important and I believe this is going to make the big difference in the PaaS offering. Although the different offerings on their own are promising, a unified UI and development and management experience is going to make it actually usable. As a developer I do want to create UI's or Processes or Integrations, but I do not want to bother about the URLs to use for which environment. And I want to be helped by promoting my artifacts on a uniform way to the next environment. I should not export artifacts in different ways and import those one by one in a target cloud service.

The other day I also had an introductory meeting with one of the directors for UI/UX design. And that stressed the importance of UX on the unified PaaS UX initiatives.

As you'll understand: I'm very curious and looking forward to see new developments. If they reach me, I'll keep you posted (as far as I'm allowed of course).

SQLDeveloper: User Defined Extensions and ForeignKey query revised

Thu, 2018-03-22 02:42
It was so fun: yesterday I wrote  a small article on creating a query on Foreign Keys refering a certain table. A post with content that I made up dozens of times in my Oracle carreer. And right away I got 2 good comments. One was on the blog itself.

And of course Anonymous is absolutely right. So I added 'U' as a constraint type option.

The other comment was from my much appreciated colleague Erik. He brought this to another level, by pointing me out how to add this as a User Defined Extension in SQL Developer.

I must say I was already quite pleased with the Snippets in SQLDeveloper. So I already added the query as a snippet:
But the tip of Erik is much cooler.
He refered to a tip by Sue Harper that explains this (What, it's been in there since 2007?!).

Now what to do? First create an xml file, for instance referred_by_fks.xml,  with the following content:
<item type="editor" node="TableNode" vertical="true">
<title><![CDATA[FK References]]></title>
<![CDATA[select fk.owner,
from all_constraints fk
join all_constraints rpk on rpk.constraint_name = fk.r_constraint_name
where fk.constraint_type='R'
and rpk.constraint_type in('P','U')
and rpk.table_name = :OBJECT_NAME
and rpk.owner = :OBJECT_OWNER
order by fk.table_name, fk.constraint_name;]]>

Note that I updated my query a bit.

Then to add the extension to SQL Developer:
  • Open the prefereces via: Tools > Preferences
  • Navigate to Database > User Defined Extensions
  • Click "Add Row" button
  • In Type choose "EDITOR", Location is where you saved the xml file above
  • Click "Ok" then restart SQL Developer

Now, if you click on a table in the navigater, you will have an extra tab on your table editor:

Cool stuff! And it's been there for ages!

Which tables have foreign keys refering to a particular table?

Wed, 2018-03-21 02:44
Ok, this time a quick not so exciting post. Actually, I find my self recreating a query again, that I created many times in my carreer. So, why not post it?

Last year, I published my Darwin Object Type Accelerator (Dotacc). It allows you to generate objects from a datamodel. What it also does is create collection types for tables that refer to the tabel you want to generate an object for. For some you want that, but for others you don't. Simply because you don't need them to be queried along. Therefor, I added functionality to disable those.

But then comes the question: which are the tables with their foreignkey constraints that refer to this particular table?

The answer is in the ALL_CONSTRAINTS view (with the variants of DBA_% and USER_%).
There are several types of constraints:
  • C: Check constraints
  • R: Referential -> the particular foreign keys
  • P: Primary Key
  • U: Unique Key
I'm interested in the Foreign keys, thus those where constraint_type='R'. But those refer not to a table but to another constraint. So, I need to get the primary key, constraint_type='P', of the table that I want to query and join those together.

That get's me:
select fk.* 
from all_constraints fk
join all_constraints rpk on rpk.constraint_name = fk.r_constraint_name
where fk.constraint_type='R'
and rpk.constraint_type='P'
and rpk.table_name = 'DWN_MY_TABLE';

Set the minimum password length on your default authenticator in Weblogic

Thu, 2018-03-08 05:55
End of last year I wrote how to create a demo community of users in your Weblogic using wlst.
Using these scripts I wanted to do the same at my current customer: creating test users in the DefaultAuthenticator. However, I faced that the minimum password length was 8, while one of the user failed creation, because the password was the same as the user, and only 5 characters long.

So I need to change the password validator. And preferably using WLST (of course). Now, the password validator of de authenticator can also be found through the console. However, the Weblogic realm also has a system password validator. Both have a default length of 8.

Let me show you some snippets (that you can add to the create users script, or your own purpose), on how to change the minimum password length.

First a method to get the default realm:
def getRealm(name=None):
if name == None:
realm = cmo.getSecurityConfiguration().getDefaultRealm()
realm = cmo.getSecurityConfiguration().lookupRealm(name)
return realm

With that you can get the authenticator:
def getAuthenticator(realm, name=None):
if name == None:
authenticator = realm.lookupAuthenticationProvider("DefaultAuthenticator")
authenticator = realm.lookupAuthenticationProvider(name)
return authenticator

With a realm an an authenticator, we can change the password length:
def setMinPasswordLengthOnDftAuth(minPasswordLength):
# Get Realm and Authenticator
realm = getRealm()
authenticator = getAuthenticator(realm)
print('Succesfully set minimum password length to '+minPasswordLength+ ' on '+authenticator.getRealm().getName()+'.')
print('For '+ authenticator.getName() +': '+str(authenticator.getMinimumPasswordLength()))
print('For SystemPasswordValidator of '+getRealm().getName()+': '+ str(passwordValidator.getMinPasswordLength()))
except WLSTException:
message="Failed to update minimum password length!"
print (message)
raise Exception(message)

The minimum password length from the authenticator can be set directly. From the realm this function looks up the SystemPasswordValidator. And on that it set the minimum password length.

This function goes to edit mode, saves and activates the changes. But if you want to add users, you need to get wlst into domainConfig() mode.

Other password validator property setters are:
  • setMinPasswordLength()
  • setMaxPasswordLength()
  • setMaxConsecutiveCharacters()
  • setMaxInstancesOfAnyCharacter()
  • setMinAlphabeticCharacters()
  • setMinNumericCharacters()
  • setMinLowercaseCharacters()
  • setMinUppercaseCharacters()
  • setMinNonAlphanumericCharacters()
  • setMinNumericOrSpecialCharacters()
  • setRejectEqualOrContainUsername(true)
  • setRejectEqualOrContainReverseUsername(true) 
See the docs for more.

Weblogic 12c + SAML2: publish your metadata over an URL

Fri, 2018-02-09 13:09
This week I got to do a SAML2 implementation again for APEX against ADFS. Actually the same setup as last year. One pitfall I fell into with open eyes, was the Redirect URI on the 'Web SSO Partner Provider'. I entered /ords/f*, but it had to be with out the wild-card: /ords/f. But that aside.

At one step in the setup of a SAML2 configuration is that you have to publish the metadata, by clicking a button. Some SAML2 capabable middleware solutions can publish the metadata over an URL. ADFS does support a URL to get the metadata from the Service Provider, being Weblogic12c servicing your application. This prevents that you need to hand over the xml file every time you change/update your configuration. For instance because of expired certificates. How nice would it be if Weblogic supported this?

Well, actually, you can! Sort of... Weblogic does support to service a document-folder, like the htdocs folder of Apache. To do so, you need to create a war file, with only a weblogic.xml file that couples a context-root to a certain folder. And apparently Glassfish can do so too!

When you install ORDS on Weblogic, following the steps, you generate an i.war that is actually the example for this post. You could extract that file and adapt it for this purpose. But I wanted to be able to generate it. Doing so I could reuse this for several other purposes if I would need to.

So I started with a new Saml2MetaData project folder and created a src folder, with a WEB-INF folder beneath it.
Then I copied the three deployment descriptors:
  • sun-web.xml
  • web.xml
  • weblogic.xml
 The sun-web.xml (not being the travel company):
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sun-web-app PUBLIC "-//Sun Microsystems, Inc.//DTD GlassFish Application Server 3.0 Servlet 3.0//EN" "http://www.sun.com/software/appserver/dtds/sun-web-app_3_0-0.dtd">
<!-- This element specifies the context path the static resources are served from -->
<!-- This element specifies the location on disk where the static resources are located -->
<property name="alternatedocroot_1" value="from=/* dir=${samlMetaData.home}"/>

As you can see I placed the ${samlMetaData.contextRoot} the property in the context-root-tag and the property named alternatedocroot_1 got the directory reference containing the ${samlMetaData.home}.

The web.xml is there for completeness, but does not contain a directory reference:
<?xml version="1.0" encoding="UTF-8"?>
"-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
<!-- This Web-App leverages the alternate doc-root functionality in WebLogic and GlassFish to serve static content
For WebLogic refer to the weblogic.xml file in this folder
For GlassFish refer to the sun-web.xml file in this folder

And then the weblogic.xml: including the same properties referencing the context-root and folder:
<weblogic-web-app xmlns="http://www.bea.com/ns/weblogic/weblogic-web-app">
<!-- This element specifies the context path the static resources are served from -->
<!-- This element specifies the location on disk where the static resources are located -->

Then I need an ANT build file that copies these files replacing the properties. I would have done it with WLST if I had found a way to wrap the lot into a war file, that quickly. But ANT does the job well. First I need a build.properties file, that denotes the properties values:

And then the ANT build.xml file:
<?xml version="1.0" encoding="UTF-8"?>
<project name="SamlMetaData" basedir="." default="build">
<property file="build.properties" />
<!-- Clean & Init -->
<target name="clean">
<echo>Delete build and dist folder</echo>
<delete dir="${build.dir}" />
<delete dir="${dist.dir}" />
<target name="init" depends="clean">
<echo>Create build and dist folder</echo>
<mkdir dir="${build.dir}" />
<mkdir dir="${dist.dir}" />
<!-- war the project -->
<target name="war">
<property name="war.dir" value="${dist.dir}/${ant.project.name}" />
<property name="war.file" value="${war.dir}/${ant.project.name}.war" />
<echo>Create war file ${war.file} from ${build.dir}</echo>
<mkdir dir="${war.dir}" />
<jar destfile="${war.file}" basedir="${build.dir}">
<manifest />
<!-- Build the war file -->
<target name="build" depends="init">
<echo>Copy ${src.dir} to ${build.dir}, expanding properties</echo>
<copy todir="${build.dir}">
<fileset dir="${src.dir}" />
<expandproperties />
<ant target="war" />

Run this with ANT andit will create a build and a dist  folder with the war file.
This can be deployed to Weblogic that results in a context root as configured in the build.properties. Everything placed in the folder as configured in the samlMetaData.home folder can be fetched through Weblogic.

So just publish your metadata to that folder and the IdentityProvider can get it auto-magically.

How to install the Notepad++ 64-bit plugin manager

Fri, 2018-02-09 06:08
I'm a Notepad++ fan for years. And as soon as a 64-bit version arose I adopted it.
But since a few months I have a new laptop, and I apparently didn't get the plugin manager with the latest new install.  And only now I took the opportunity to sort it out and write about it.

I found that the plugin manager is available since April 2017 on GitHub, version 1.49. I downloaded the zip from the mentioned location, I choose the _x64 version:
Then unzipped it into my Notepad++ folder:

Then started Notepad++ and the plugin manager appears:

So now I can format my XML files again...

SoapUI: validate a date field in response with current date

Tue, 2018-01-23 09:30
Once in a while you need to validate a service that has dates in the response. Although SoapUI has xpath and xquery match assertions, validate against strings is quite difficult. How to do a date comparison against for instance the current date?

You can do it with a script assertion:
And the content of this can be:
def groovyUtils = new com.eviware.soapui.support.GroovyUtils(context)
// Set Namespaces
def holder = groovyUtils.getXmlHolder(messageExchange.responseContent)
//holder.namespaces["soapenv"] = "http://schemas.xmlsoap.org/soap/envelope/"
def dateFoundStr = holder.getNodeValue("/Results/ResultSet/Row[1]/DATE_FOUND")
def dateFound = new Date().parse("yyyy-MM-dd hh:mm:ss", dateFoundStr)
dateFoundStr = dateFound.format("yyyy-MM-dd")
//Current Date
def date = new Date()
def currentDate=date.format("yyyy-MM-dd")
assert dateFoundStr == currentDate

First we need to fetch and parse the response content using the holder variable, parsing the messageExchange.responseContent using groovyUtils.getXmlHolder.

Second, the particular date is found, here dateFound as a field from a JDBC response. A JDBC response does not have namespaces, but from a SOAP response it helps to declare namespaces. For an example see the commented line for holder.namespaces["soapenv"].

Third, I parse the found date, which is a string as fetched from the xml, to a date time, then format it to a string to get only the date part. This could be done simply using substring methods, but I wanted to try this. And get and formatthe currentDate as a string.

In the end just do assert with a comparison of both values.

There you go.

Modify your nodemanager.properties in wlst

Mon, 2018-01-22 09:12
In 2016 I did several posts on automatic installs of Fusion MiddleWare, including domain creation using wlst.

With weblogic 12c you automatically get a pre-configured per-domain nodemanager. But you might find the configuration not completely suiting your whishes.

It would be nice to update the nodemanager.properties file to with your properties in the same script.

Today I started with upgrading our Weblogic Tuning and Troubleshooting training to 12c, and one of the steps is to adapt the domain creation script. In the old script, the AdminServer is started right way, to add the managed server to the domain. In my before mentioned script, I do that offline. But since I like to be able to update the nodemanager.properties file I figured that out.

Earlier, I created  a function to just write a new property file:
# Create a NodeManager properties file.
def createNodeManagerPropertiesFile(javaHome, nodeMgrHome, nodeMgrType, nodeMgrListenAddress, nodeMgrListenPort):
print ('Create Nodemanager Properties File for home: '+nodeMgrHome)
print (lineSeperator)
fileNew=open(nmProps, 'w')
fileNew.write('#Node manager properties\n')
fileNew.write('#%s\n' % str(datetime.now()))
fileNew.write('DomainsFile=%s/%s\n' % (nodeMgrHome,'nodemanager.domains'))
fileNew.write('NodeManagerHome=%s\n' % nodeMgrHome)
fileNew.write('JavaHome=%s\n' % javaHome)
fileNew.write('ListenAddress=%s\n' % nodeMgrListenAddress)
fileNew.write('ListenPort=%s\n' % nodeMgrListenPort)
if nodeMgrType == 'ssl':
fileNew.write('LogFile=%s/%s\n' % (nodeMgrHome,'nodemanager.log'))

But this one just rewrites the file, and so I need to determine the values for properties like DomainsFile, JavaHome, etc., which are already set correctly in the original file. I only want to update the ListenAddress, and ListenPort, and possibly the SecureListener property based on the nodemanager type. Besides that, I want to backup the original file as well.

So, I adapted this  function to:
# Update the Nodemanager Properties
def updateNMProps(nmPropertyFile, nodeMgrListenAddress, nodeMgrListenPort, nodeMgrType):
nmProps = ''
print ('Read Nodemanager properties file%s: ' % nmPropertyFile)
f = open(nmPropertyFile)
for line in f.readlines():
if line.strip().startswith('ListenPort'):
line = 'ListenPort=%s\n' % nodeMgrListenPort
elif line.strip().startswith('ListenAddress'):
line = 'ListenAddress=%s\n' % nodeMgrListenAddress
elif line.strip().startswith('SecureListener'):
if nodeMgrType == 'ssl':
line = 'SecureListener=true\n'
line = 'SecureListener=false\n'
# making sure these properties are set to true:
elif line.strip().startswith('QuitEnabled'):
line = 'QuitEnabled=%s\n' % 'true'
elif line.strip().startswith('CrashRecoveryEnabled'):
line = 'CrashRecoveryEnabled=%s\n' % 'true'
elif line.strip().startswith('weblogic.StartScriptEnabled'):
line = 'weblogic.StartScriptEnabled=%s\n' % 'true'
elif line.strip().startswith('weblogic.StopScriptEnabled'):
line = 'weblogic.StopScriptEnabled=%s\n' % 'true'
nmProps = nmProps + line
# Backup file
print nmProps
print ('Rename File %s to %s ' % (nmPropertyFile, nmPropertyFileOrg))
os.rename(nmPropertyFile, nmPropertyFileOrg)
# Save New File
print ('\nNow save the changed property file to %s' % nmPropertyFile)
fileNew=open(nmPropertyFile, 'w')
It first reads the property file, denoted with nmPropertyFile line by line.
If a line starts with a particular property that I want to set specifically, then the line is replaced. Each line is then added to the nmProps  variable. For completeness and validation I print the resulting variable.
Then I rename the original file to nmPropertyFile+'.org' using os.rename(). And lastly, I write the contents of the nmProps to the original file in one go.

This brings me again one step further to a completely scripted domain.

Run SQLcl from ANT

Thu, 2017-12-21 04:29
I think since last year ORacle released SQLcl which could be seen as the commandline variant of SQL Developer. But even better: a replacement of SQL Plus.

A few years ago I created what I called a InfraPatch framework, to do preparations on an infrastructure as a pre-requisite for the deployment of services and/or applications. It can run WLST scripts for creating datasouces, jms-queues, etc.  It also supported the running of database scripts, but it required an sqlplus installation, for instance using the instant client. Since it was part of a release/deploy toolset, where the created release is to be deployed by an IT admin on a test, acceptance or production environment, I had to rely on a correct Oracle/instant client installation on an agreed location.

I'm in the process of revamping that framework and renamed to InfraPrep,  since preparing an infrastructural environment makes it more clear what it does. (It does not patch a system with Oracle patches...).

Now I'm at the point that I have to implement the support of running database scripts. The framework uses ANT, which in fact is Java. And SQLcl has two big advantages that makes it ideal for me to use in my InfraPrep framework:
  • It is incredibly small: it's only 19MB! And that includes the ojdbc and xmlparser jars. Since i used ANT from a FusionMiddleWare home, I could make it even smaller! 
  • It is Java, so I can leverage the java ant task.
 So, how to call SQLcl from ANT? I need a few ingredients:
  • Download and unzip SQLcl into my Ant project and add a sqlcl.home property:
  • The actual sqlcl jar file and add the sqlcl.jar property for that:
  • The main class file = oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli
These ingredients can be found in the sql.bat in the bin folder of the SQLcl download.

Then of course in my environment property file I need the user name, password and database url.
Something like:

I used a TNS-style database URL, since it is the same as used in the creation of the corresponding DataSource. And it can be reused to connect with SQLcl.

Now, to make it easier to use and to abstract the plumbing in a sort of  ANT task, I crated a macrodef:

 <!-- Create Add Outbound connection pool to DB adapter-->
<macrodef name="runDbScript">
<attribute name="dbuser"/>
<attribute name="dbpassword"/>
<attribute name="dburl"/>
<attribute name="dbscript"/>
<logMessage message="DatabaseUrl: @{dburl}" level="info"/>
<logMessage message="DatabaseUser: @{dbuser}" level="info"/>
<logMessage message="DatabasePassword: ****" level="info"/>
<property name="dbConnectStr" value='@{dbuser}/@{dbpassword}@"@{dburl}"'/>
<property name="dbScript.absPath" location="@{dbscript}"/>
<property name="dbScriptArg" value="@${dbScript.absPath}"/>
<logMessage message="Run Database script: ${dbScriptArg}" level="info"/>
<record name="${log.file}" action="start" append="true"/>
<java classname="oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli" failonerror="true" fork="true">
<arg value="${dbConnectStr}"/>
<arg value="${dbScriptArg}"/>
<pathelement location="${sqlcl.home}/lib/${sqlcl.jar}"/>
<record name="${log.file}" action="stop"/>

In this macrodefinition, I first build up a database connect string using the username, password and database url:
      <property name="dbConnectStr" value='@{dbuser}/@{dbpassword}@"@{dburl}"'/>
Then I use a little trick to create an absolute path of the dbscript path:
      <property name="dbScript.absPath" location="@{dbscript}"/>
The trick is in the location attribute of the property.
And since that now is a property instead of an attribute, I circumvented the need for escaping the @ character:
      <property name="dbScriptArg" value="@${dbScript.absPath}"/>
The logmessage task you see is another macrodef I use:
      <macrodef name="logMessage">
<attribute name="message" default=""/>
<attribute name="level" default="debug"/>
<echo message="@{message}" level="@{level}"></echo>
<echo file="${log.file}" append="true"
message="@{message}${line.separator}" level="@{level}"></echo>

It both echo's the output to the console and to a log file.
Since I want the output of the java task into the same log file, I enclosed the java task with record tasks to start and stop the appending of the output-stream to the log file.

The java task is pretty simple, referencing the jar file in the classpath and providing the connect string and the script run argument as two separate arguments.
There are however two important properties:
  • failonerror="true": I want to quit my ANT scripting when the database script fails.
  • fork="true": when providing the exit statement in the sql script, SQLcl tries to quit the JVM. This is not allowed, because it runs by default in the same JVMas ANT. Not providing the exit statement in the script will keep the thread in SQLcl, which is not acceptable. So, forking the JVM will allow SQLcl to quit properly.
Now, the macro can be called as follows:
    <propertycopy name="dbUser" from="${database}.dbUserName"/>
<propertycopy name="dbUrl" from="${database}.dbUrl"/>
<propertycopy name="dbPassword" from="${database}.dbPassword"/>
<runDbScript dbuser="${dbUser}" dbpassword="${dbPassword}" dburl="${dbUrl}" dbscript="${prep.folder}/${dbScript}"/>

Where these properties are used:

Ant the sample.sql file:
select * from global_name;

And this works like a charm:
[echo] Script voor uitvoeren van database script.
[echo] Environment:
[echo] Prep folder: ../../infraPreps/BpmDbS0004
[echo] Load prep property file ../../infraPreps/BpmDbS0004/BpmDbS0004.properties
[echo] Run Script
[echo] DatabaseUrl: (description=(address=(host=darlin-vce-db.org.darwinit.local)(protocol=tcp)(port=1521))(connect_data=(service_name=orcl)))
[echo] DatabaseUser: dwn_owner
[echo] DatabasePassword: ****
[echo] Run Database script: @c:\temp\FMWReleaseAll\DWN\1.0.0\infraPreps\BpmDbS0004\sample.sql
[java] SQLcl: Release 17.3.0 Production on do dec 21 11:18:50 2017
[java] Copyright (c) 1982, 2017, Oracle. All rights reserved.
[java] Connected to:
[java] Oracle Database 11g Enterprise Edition Release - 64bit Production
[java] With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
[java] Data Mining and Real Application Testing options
[java] --------------------------------------------------------------------------------
[java] ORCL
[java] Disconnected from Oracle Database 11g Enterprise Edition Release - 64bit Production
[java] With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
[java] Data Mining and Real Application Testing options
[echo] Done running preperations.

Total time: 12 seconds

One thing to be arranged though is the fetch of the username/password from the commandline, instead of properties. This can be as follows:
    <input message="Enter database user for environment ${database}: " addproperty="db.user"/>
<input message="Enter password for user ${db.user}: " addproperty="db.password">
<handler classname="org.apache.tools.ant.input.SecureInputHandler"/>

ConclusionSQLcl is great, since it is small and in java. So it turns out incredibly easy to distribute it within your own framework.

OSB 12c Customization in WLST, some new insights: use the right jar for the job!

Wed, 2017-12-20 04:41
Problem setting  and investigationYears ago I created a Release & Deploy framework for Fusion Middleware, also supporting Oracle Service Bus. Recently revamped it to use 12c. It uses WLST to import the OSB service to the Service Bus, including the execution the customization file.

There are lots of examples to do this, but I want to zoom in on the execution of the customization file.

The WLST function that does this, that I use is as follows:
# Function to execute the customization file.
def executeCustomization(ALSBConfigurationMBean, createdRefList, customizationFile):
if customizationFile!=None:
print 'Loading customization File', customizationFile
inputStream = FileInputStream(customizationFile)
if inputStream != None:
customizationList = Customization.fromXML(inputStream)
if customizationList != None:
filteredCustomizationList = ArrayList()
setRef = HashSet(createdRefList)
print 'Filter to remove None customizations'
print "-----"
# Apply a filter to all the customizations to narrow the target to the created resources
print 'Number of customizations in list: ', customizationList.size()
for customization in customizationList:
print "Add customization to list: "
if customization != None:
print 'Customization: ', customization, " - ", customization.getDescription()
newCustomization = customization.clone(setRef)
print "Customization is None!"
print "-----"
print 'Number of resulting customizations in list: ', filteredCustomizationList.size()
print 'CustomizationList is null!'
print 'Input Stream for customization file is null!'
print 'No customization File provided, skip customization.'

The parameter ALSBConfigurationMBean can be fetched with:
sessionName = createSessionName()
print 'Created session', sessionName
SessionMBean = getSessionManagementMBean(sessionName)
print 'SessionMBean started session'
ALSBConfigurationMBean = findService(String("ALSBConfiguration.").concat(sessionName), "com.bea.wli.sb.management.configuration.ALSBConfigurationMBean")

The other parameter is the createdRefList, that is build up from the default ImportPlan during import of the config jar:
print 'ÒSB project', project, 'will get updated'
osbJarInfo = ALSBConfigurationMBean.getImportJarInfo()
osbImportPlan = osbJarInfo.getDefaultImportPlan()
operationMap = osbImportPlan.getOperations()
print 'Default importPlan'
set = operationMap.entrySet()


abort = false
#list of created artifact refences
createdRefList = ArrayList()
for entry in set:
ref = entry.getKey()
op = entry.getValue()
#set different logic based on the resource type
type = ref.getTypeId
if op.getOperation() == ALSBImportOperation.Operation.Create:
print 'Unable to import a service account or a service provider on a target system', ref
abort = true
#keep the list of created resources
print 'ref: ',ref
if abort == true :
print 'This jar must be imported manually to resolve the service account and service provider dependencies'
print 'Modified importPlan'
importResult = ALSBConfigurationMBean.importUploaded(osbImportPlan)
if importResult.getFailed().isEmpty() == false:
print 'One or more resources could not be imported properly'

The meaning is to build up a set of references of created artefact, to narrow down the customizations to only execute them on the artefacts that are actually imported.

Now, back to the executeCustomization function. It first creates an InputStream on the customization file:
inputStream = FileInputStream(customizationFile)

on which it builds a list of customizations using the .fromXML method of the Customization object:
        customizationList = Customization.fromXML(inputStream)

These customizations are interpreted from the Customization file. If you open that you can find several customization elements:
 <cus:customization xsi:type="cus:EnvValueActionsCustomizationType">
<cus:customization xsi:type="cus:FindAndReplaceCustomizationType">
<cus:customization xsi:type="cus:ReferenceCustomizationType">

These all are mapped to subclasses of the Customization. And now the reason that I write this blogpost is that I ran into a problem with my import tooling. In the EnvValueActionsCustomizationType the endpoint replacements for the target environments is done. And the weren't executed. In fact these customizations were in the customizationList, but as a None/Null object. Thus, executing this complete list using ALSBConfigurationMBean.customize(filteredCustomizationList) would run in an exception, refering to a null object in the customization list. That's why they're filtered out. But why weren't these interpreted by the .fromXml() method?

Strangely enough in the javaAPI docs of 12.2.1 the EnvValueActionsCustomization does not exist, but the EnvValueCustomization does. But searching My Oracle Support shows in Note 1679528.2: 'A new customization type EnvValueActionsCustomizationType is available in 12c which is used when creating a configuration plan file.' and here in the Java API doc (click on com.bea.wli.config.customization) it is stated that EnvValueCustomization is deprecated and EnvValueActionsCustomization should be used in stead.
Apparently the docs is not updated completely....
And it seems that I used a wrong jar file: The customization file was created using the Console, and executing the customization file using the console did execute the endpoint replacements. So I figured that I must be using a wrong version of the jar file.
So I searched on my BPM quickstart installation ( for the class EnvValueCustomization:
Jar files containing EnvValueCustomization
  • C:\Oracle\JDeveloper\12210_BPMQS\osb\lib\modules\oracle.servicebus.configfwk.jar/com\bea\wli\config\customization\EnvValueCustomization.class
  • C:\Oracle\JDeveloper\12210_BPMQS\oep\spark\lib\spark-osa.jar/com\bea\wli\config\customization\EnvValueCustomization.class
  • C:\Oracle\JDeveloper\12210_BPMQS\oep\common\modules\com.bea.common.configfwk_1.3.0.0.jar/com\bea\wli\config\customization\EnvValueCustomization.class
And then I did a search with EnvValueActionsCustomization.
Jar files containing EnvValueActionsCustomization:
  • C:\Oracle\JDeveloper\12210_BPMQS\osb\lib\modules\oracle.servicebus.configfwk.jar/com\bea\wli\config\customization\EnvValueActionsCustomization.class
SolutionIt turns out that in my ANT script I used:
<path id="library.osb">
<fileset dir="${fmw.home}/oep/common/modules">
<include name="com.bea.common.configfwk_1.3.0.0.jar"/>
<fileset dir="${weblogic.home}/server/lib">
<include name="weblogic.jar"/>
<include name="wls-api.jar"/>
<fileset dir="${osb.home}/lib">
<include name="alsb.jar"/>

Where I should use:
<path id="library.osb">
<fileset dir="${fmw.home}/osb/lib/modules">
<include name="oracle.servicebus.configfwk.jar"/>
<fileset dir="${weblogic.home}/server/lib">
<include name="weblogic.jar"/>
<include name="wls-api.jar"/>
<fileset dir="${osb.home}/lib">
<include name="alsb.jar"/>
ConclusionIt took me quite some time to debug this. But learned how the customization works. I found quite some examples that use com.bea.common.configfwk_1.X.0.0.jar. And apparently during my revamping, I updated this class path (actually I had 1.7, and found only 1.3 in my environment).  But, somehow Oracle found it sensible to replace it with oracle.servicebus.configfwk.jar while keeping the old jar files.
So use the right Jar for the job!

Create the SOA/BPM Demo User Community, with just WLST.

Mon, 2017-12-18 06:31
As said in my previous post (I've learned somewhere you should not post twice on the same day, but spread it out over time), I'm delivering a BPM 12c training. And based it on the BPM Quickstart. Although nice for UnitTests and development, the integrated weblogic lacks a  proficient set of users to test your task definitions.

Oracle has a demo community and a set of ANT and Servlet based scripts to povision your SOA or BPMSuite environment with a set of American literature writers, to be used in demo's and trainings. I some how found this years ago and had it debugged to be used in 12.1.3 a few years ago. However, I did not know where I got it and if it was free to be delivered.

Apparently it is, and you can find it at my oracle support. Our friends with Avio Consulting also  did a good job in making it available and working with 12c. However, I could not make it work smoothly end 2 end. I got it seeded, but figured that I would not need ANT and a Servlet.

Last year, in 2016, I created a bit of WLST scripting to create users for OSB and have them assigned to OSB Application roles. You can read about that here for the user creation, and here for the app-role assignment.

One thing that's missing in those scripts is the setting of the user attributes. So I googled around and found a means to add those too.

First, I had to transform the demo community seeding xml file to a property file. Like this:

cdickens.description=Demo User

The complete, usersAndGroups.properties file is available here.
In an earlier blog, I wrote about how to read a property file. But my prefered method, does not allow me to determine the property to be fetched dynamically. That's why I split my basic createDemoUsers.properties file, that refers to the usersAndGroups.properties file, and contains the properties refering the Oracle/Jdeveloper home and the connection details for the AdminServer. This property file also contains comma separated lists of users, groups and AppRoles to be created or  granted.

The actual createDemoUsers.py file loops over the three lists and creates the particular users and groups, and grants the AppRoles.

To set the attributes, the setUserAttributeValue of the authenticatorMBean can be used as follows:
    #Set Properties
displayName=nvl(firstName, " ")+" "+nvl(lastName, " ")

I published the complete set of scripts on the GitHub repo I shared with my colleague.
You can download them all, adapt the createDemoUsers.sh, to refer the correct MW_HOME to your JDeveloper environment. For Windows you might translate it to a .bat/.cmd file.

And of course you can use it for your own set of users.

I think I covered near to all of the Demo User community. Except for management-chains: I could not find how to register a manager for a user in Weblogic. Neither in the console, nor inWLST. So, I currently I conclude it cannot be done. But, if you have a tip, please be so good to leave a comment. I would highly appreciate it.

BPM Exception when deploying BPM project with Human tasks

Mon, 2017-12-18 04:25
This week I deliver a BPM 12c Workshop, that I based on the BPM QuickStart. When the students worked on the lab on Human Workflow, they hit an error deploying the Composite, where in the log you can find something like:

Caused By: oracle.fabric.common.FabricException: Error occurred during deployment of component: RequestHolidayTask to service engine: implementation.workflow, for composite: HolidayRequestProcess: ORABPEL-30257

exception.type: ERROR
exception.severity: 2
exception.name: Error while Querying workflow task metadata.
exception.description: Error while Querying workflow task metadata.
exception.fix: Check the underlying exception and the database connection information. If the error persists, contact Oracle Support Services.
: exception.code:30257
exception.type: ERROR
exception.severity: 2
exception.name: Error while Querying workflow task metadata.
exception.description: Error while Querying workflow task metadata.
exception.fix: Check the underlying exception and the database connection information. If the error persists, contact Oracle Support Services.

Caused By: oracle.fabric.common.FabricDeploymentException: ORABPEL-30257

Caused By: java.sql.SQLSyntaxErrorException: Column 'WFTM.PACKAGENAME' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'WFTM.PACKAGENAME' is not a column in the target table.

Apparently in the repository of a column is missing in the Workflow Metadata table.

Luckily, I stumbled upon a question in the community.oracle.com forum that hit this 'bug' as well; and provided a solution. You need to do an alter table to resolve this:

The smart guy that provided the answer, used a separate Database UI tool. But fortunately, JDeveloper is perfectly capable to provide you de means as well.

First open the Resource Pallette in JDeveloper. Make sure that you have started your Integrated WebLogic already (since that will run the DerbyDB.

Then in the Resource Pallette, create a new Database Connection:

Provide the following details:
Give it a name, like soainfraDB, as a Connection Type select 'Java DB / Apache Derby'. You can leave Username and Password empty. Then as a Driver Class, choose the 'org.apache.derby.jdbc.ClientDriver' (not the default). Then as a Host Name provide localhost, as a JDBC Port enter 1527 en as  a Database Name enter soainfra.

You can  Test Connection  and then, if Successfull, hit OK.

Then from the IDE Connections pallette  right click your newly created database connection, and choose 'Open in Databases Window':

And from that right click on the database connection and choose 'Open SQL Worksheet':

There you can enter and execute the alter statement:
After this, deployment should succeed. Since it is persisted in the DerbyDB it will survive restarts.

This might apply to the SOA QuickStart as well (did not try).