Jeff Moss

Subscribe to Jeff Moss feed Jeff Moss
Information Warriors
Updated: 3 hours 39 min ago

Installing Hortonworks Data Platform 2.5 on Microsoft Azure

Fri, 2017-04-28 16:04

I presented this topic to the Big Data Meetup in Nottingham on Thursday but sometimes people prefer a blog to a presentation, so I’ve fashioned this article from the slides…

This article assumes the following:

Start by navigating to the Azure login page and enter your details. If you have never visited before your screen will look like this:

If you’ve logged in before the page will show your login and you can just click it:

After you login, you’ll arrive at the Dashboard:

Choose the “Marketplace” link at the bottom right, which leads to the following screen where you can type “HDP” and it will show you the options for Hortonworks Data Platform. There are currently two options 2.4 and 2.5 – I chose 2.5:

When you choose 2.5 it will bring up this screen which shows the details of the option you have chosen and offers you the “Create” button to go ahead and start the creation process – click on Create:

After clicking on Create, the process moves on to a five step wizard, the first step of which allows you to choose “Basic options” for the VM. I set the following options:

Name: oramosshdp25sandbox

VM Disk Type: SSD

User name: jeff

SSH Public key: my public SSH key

Subscription: Leave  set to Free Trial (if that’s what you are using, as per screenshot, or your Corporate/Pay As You Go subscription if you have one)

Resource Group: Create New called hdp25sandbox_rg

Location: UK West

A screenshot of these options looks like this:

Click on OK and move on to the 2nd step in the wizard for choosing the size of the VM. I chose the DS3_V2 size which seemed to work OK – you might be able to get away with something smaller, perhaps.

Click on Select and move on to step 3 of the wizard which is about configuring optional features. For this step I set the following:

Use managed disks: Yes

Leaving all other options as defaults this looks like:

Click on OK and move on to step 4 which is just a summary of the configuration:

If you’re happy, click on OK and move on to step 5 where you accept the terms of use and “buy” the VM:

If you’re happy, click on Purchase and that’s the end of the wizard. Azure then goes off to deploy the VM, which can take a few minutes. You’ll be returned to the dashboard screen where you’ll see the VM at the top right with the word Deploying on it:

As I say, it takes a few minutes to complete, but when it does, you’ll see a popup notification in the top right of the screen and the VM tile will change to look as below:

So, you now have the Sandbox VM up and running.

The VM by default only has inbound SSH access enabled and can only be accessed by IP address so we’ll make some changes to these next. First we’ll give the VM a DNS name which allows you to access it on the internet via a name rather than an IP address. From the dashboard screen (above) click on the VM and it takes you to this screen:

You’ll notice the Public IP address which is a hyperlink…click on that link and it takes you to the following screen where you can specify the DNS Name which means the machine will have a Fully Qualified Domain Name that you can access via the internet. I set my DNS Name to oramosshdp25sandbox and given I’d previously chosen to use UK West as the location, the Fully Qualified Domain Name is thus as per the screenshot below:

Now, navigate to the Inbound Security Rules page which is under the Network Security Group page (access from the Resource List on the dashboard). Notice that the only rule existing is one to allow inbound SSH communication:

In order to facilitate additional capabilities you should open up a few more ports, as follows:

  • 8888 – HDP
  • 8080 – Ambari
  • 4200 – Web SSH access
  • 50070 – Default Node Name
  • 21000 – Atlas
  • 9995 – Zeppelin
  • 15000 – Falcon
  • 6080 – Ranger

Click on Inbound Security Rule which takes you to the page for maintaining these rules and enter the details for the 8888 port. I specified the name as default-allow-8888 and the port as 8888 as shown below:

Click on OK to create the rule. Carry out the same process for the other ports.

Now that we’ve undertaken these additional activities we can access the VM using an SSH terminal logging onto as the user you have created (jeff in my case) and the private SSH key:

Whilst you are in the SSH terminal you can reset the Ambari password. This is not strictly necessary unless you want to login to Ambari as admin, but I’ll describe it anyway.

First become root with:

sudo su - root

Now SSH into the Docker Image as root:

ssh root@

You will be prompted to change the password for root on this first login – the current password is hadoop.

After changing the password run the Ambari password reset process:


Follow the instructions to reset the password and after that it will start the Ambari server process.

Once all that is done, exit out of the sessions and the original SSH terminal.

Now go into HDP via the web interface by logging on to the following URL:

The first time you access this URL you’ll be given a welcome (marketing) page which asks for your details:

Fill out the details and hit Submit which will take you to the main entry page for HDP:

Choose the Launch Dashboard option on the left, which brings up a pair of browser windows that use the entire desktop and show the Ambari login page on the left hand browser and the Tutorials website on the right hand browser like this:

You can use either the admin user that you just reset the password for or the predefined user raj_ops (password raj_ops) to access Ambari. Click on Sign In on the left hand browser once you entered the credentials and it takes you into the main Ambari homepage:

This is the main systems management environment for Hortonworks – more documentation here.

If we close this pair of browsers now and go back to the main HDP entry page and choose the Quick Links option on the right we get this page:

From here you can choose to use any of these specific components.

NOTE – I couldn’t get Atlas and Falcon to work – they need more configuration/setup to get them functional. Ranger, Zeppelin and the Web SSH client work fine though.

Just a basic introduction but I hope you find it useful.

12cR2 tightens up ORA-01841 for zero year ANSI dates, but not for Oracle SQL syntax

Thu, 2017-03-23 01:00

In moving some more code from an 11gR2 database to a 12cR2 database, I found another change where a piece of code that works in 11gR2 doesn’t compile in 12cR2.

In this instance a view was being created with a projected date column which used the ANSI DATE syntax. Here is a simplified test script:

SELECT date '0000-01-01' date_col
FROM dual

SELECT TO_DATE('0000-01-01','YYYY-MM-DD') date_col
FROM dual

Running this on 11gR2 gives:

  2  SELECT date '0000-01-01' date_col
  3  FROM   dual
  4  /

View created.

  2  SELECT TO_DATE('0000-01-01','YYYY-MM-DD') date_col
  3  FROM   dual
  4  /

View created.

Now running this on 12cR2 gives:

  2  SELECT date '0000-01-01' date_col
  3  FROM   dual
  4  /
SELECT date '0000-01-01' date_col
ERROR at line 2:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

  2  SELECT TO_DATE('0000-01-01','YYYY-MM-DD') date_col
  3  FROM   dual
  4  /

View created.

The date is zero and thus the error message is correct in 12cR2 for the ANSI DATE syntax.

ORA-54002 when trying to create Virtual Column using REGEXP_REPLACE on Oracle 12cR2

Thu, 2017-03-16 17:50

I encountered an issue today trying to create a table in an Oracle 12cR2 database, the DDL for which, I extracted from an Oracle 11gR2 database. The error returned when trying to create the table was:

ORA-54002: only pure functions can be specified in a virtual column expression

The definition of the table included a Virtual Column which used a REGEXP_REPLACE call to derive a value from another column on the table.

Here is a simplified test case illustrating the scenario (Thanks Tim for the REGEXP_REPLACE example code):

select * from v$version
create table test_ora54002_12c(
 ,virtual_column1 VARCHAR2(4000 CHAR) GENERATED ALWAYS AS(REGEXP_REPLACE(col1, '([A-Z])', ' \1', 2)) VIRTUAL VISIBLE
drop table test_ora54002_12c purge

Running this on 11gR2 gives:

SQL> select * from v$version
 2 /

Oracle Database 11g Enterprise Edition Release - 64bit Production
PL/SQL Release - Production
CORE Production
TNS for Linux: Version - Production
NLSRTL Version - Production

5 rows selected.

Elapsed: 00:00:00.40
SQL> create table test_ora54002_12c(
 3 ,virtual_column1 VARCHAR2(4000 CHAR) GENERATED ALWAYS AS(REGEXP_REPLACE(col1, '([A-Z])', ' \1', 2)) VIRTUAL VISIBLE
 4 )
 5 /

Table created.

Elapsed: 00:00:00.24
SQL> drop table test_ora54002_12c purge
 2 /

Table dropped.

Running this on 12cR2 gives:

SQL> select * from v$version
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release - 64bit Production 0
PL/SQL Release - Production 0
CORE Production 0
TNS for Linux: Version - Production 0
NLSRTL Version - Production 0

SQL> create table test_ora54002_12c(
 ,virtual_column1 VARCHAR2(4000 CHAR) GENERATED ALWAYS AS(REGEXP_REPLACE(col1, '([A-Z])', ' \1', 2)) VIRTUAL VISIBLE
 2 3 4 5 ,virtual_column1 VARCHAR2(4000 CHAR) GENERATED ALWAYS AS(REGEXP_REPLACE(col1, '([A-Z])', ' \1', 2)) VIRTUAL VISIBLE
ERROR at line 3:
ORA-54002: only pure functions can be specified in a virtual column expression

SQL> drop table test_ora54002_12c purge
 2 drop table test_ora54002_12c purge
ERROR at line 1:
ORA-00942: table or view does not exist

As you can see, 12cR2 gives the ORA-54002 error.

Looking on MOS, highlights this article, which suggests that you shouldn’t have been able to do this in 11gR2, i.e. it was a bug and that 12cR2 has fixed this bug and thus you can no longer create such a virtual column (the article refers to functional index and check constraint use cases as well).

In my case, I was able to rewrite the virtual column to use simple string functions such as SUBSTR, TRANSLATE and INSTR to achieve what I wanted and the virtual column was allowed to be created with these – problem solved – a shame really as the REGEXP_REPLACE approach was far neater.

Create Jupyterhub Container on Centos 7 on Proxmox

Fri, 2017-03-03 18:01

These instructions show how to create a Centos 7 container on Proxmox running JupyterHub.

Note – the instructions are just a guide and for use on my environment – you may need/wish to adjust for your own environment as necessary.

root@billy:~# pveversion
pve-manager/4.4-12/e71b7a74 (running kernel: 4.4.40-1-pve)
The version of the notebook server is 4.4.1
Python 3.4.5 (default, Nov  9 2016, 16:24:59) 
[GCC 4.8.5 20150623 (Red Hat 4.8.5-11)]
Create Container
pct create 153 u01:vztmpl/centos-7-default_20160205_amd64.tar.xz -rootfs 10 -hostname jupyterhub -memory 2048 -nameserver -searchdomain -net0 name=eth0,bridge=vmbr0,gw=,ip= -swap 2048 -cpulimit 2 -storage u01
yum update -y
yum install epel-release -y
yum install wget -y
cd ~
wget --no-cookies --no-check-certificate --header "Cookie:; oraclelicense=accept-securebackup-cookie" ""
yum localinstall jdk-8u111-linux-x64.rpm -y
rm -f ~/jdk-8u111-linux-x64.rpm
vi /etc/environment
export JAVA_HOME=/usr/java/jdk1.8.0_111/jre
vi ~/.bash_profile
export PATH=${JAVA_HOME}/bin:$PATH
. ~/.bash_profile
java -version
yum install pdsh -y
yum -y install yum-cron
chkconfig yum-cron on
vi /etc/yum/yum-cron.conf
update_messages = no
apply_updates = yes
email_to =
email_host =
service yum-cron start
cd /etc/yum.repos.d
yum install python34 -y
curl -O
yum install npm nodejs-legacy -y --nogpgcheck
yum install anaconda -y --nogpgcheck
python3 -m pip install jupyterhub
npm config set strict-ssl false
npm install -g configurable-http-proxy
python3 -m pip install notebook
wget -O /etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
yum install oracle-instantclient12.1-basic- -y
yum install oracle-instantclient12.1-devel- -y
yum install oracle-instantclient12.1-sqlplus- -y
vi ~/.bash_profile
export ORACLE_HOME=/usr/lib/oracle/12.1/client64
vi /etc/environment
export ORACLE_HOME=/usr/lib/oracle/12.1/client64
. ~/.bash_profile
yum install gcc -y
yum install python-devel -y
yum install python34-devel -y
pip install cx_Oracle
pip install ipython-sql
jupyterhub --generate-config
vi /root/ # ensure the following are set:
c.Spawner.env_keep = ['LD_LIBRARY_PATH']
c.Spawner.environment = dict(LD_LIBRARY_PATH='/usr/lib/oracle/12.1/client64/lib:$LD_LIBRARY_PATH')
systemctl stop firewalld
systemctl disable firewalld
vi /lib/systemd/system/jupyterhub.service
ExecStart=/usr/bin/jupyterhub --ip=
systemctl enable jupyterhub
systemctl start jupyterhub
systemctl status jupyterhub

That should be it…navigate to and login with a unix user on that node.

Installing Oracle 12c Release 2 Database on a Proxmox Container

Thu, 2017-03-02 13:19

Obviously nobody could beat Tim to getting the comprehensive installation instructions out first, but here are my notes for installing it on a proxmox container environment which is what I use as my research platform. Some of the calls used are from or based on Tim’s prior 12cR1 installation article – thanks Tim.

NOTE – this post is just a guide and is based on my environment – you will likely need to make changes to suit your own environment.

root@billy:~# pveversion
pve-manager/4.4-12/e71b7a74 (running kernel: 4.4.40-1-pve)
Host Preparation

Some of the activities required involve changing linux parameters but these can’t be applied inside a proxmox container – you’ll see errors like these if you try:

[root@db12cr2 ~]# sysctl -p
sysctl: setting key "fs.file-max": Read-only file system

Instead you have to do these at the host level – and only if you think they are relevant and that those settings wouldn’t upset all of your other environments running on that host. I haven’t tried but you could potentially just tell the GUI installer to ignore the warnings relating to these entries and not make these changes at all especially if you’re only using it for small scale research purposes.

As root on the proxmox host, run the following:

echo "fs.file-max = 6815744" >>/etc/sysctl.d/98-oracle.conf
echo "kernel.panic_on_oops = 1" >>/etc/sysctl.d/98-oracle.conf
echo "net.ipv4.conf.default.rp_filter = 2" >>/etc/sysctl.d/98-oracle.conf
/sbin/sysctl -p
Create And Prepare The Container

I use Centos 7 as the template for most of my activities and these notes are based around that.

pct create 130 u01:vztmpl/centos-7-default_20160205_amd64.tar.xz -rootfs 60 -hostname db12cr2 -memory 10240 -nameserver -searchdomain -net0 name=eth0,bridge=vmbr0,gw=,ip= -swap 10240 -cpulimit 4 -storage local

You’ll have your own way of getting the installation files to be available to the container but I do it by adding a mount point so I can access the area where all my software is:

vi /etc/pve/nodes/${HOSTNAME}/lxc/130.conf

…and add this:

mp0: /mnt/backups/common_share,mp=/mnt/common_share
Start And Enter The Container
pct start 130
pct enter 130
Install Additional Packages

I’m going to use the Oracle Preinstall package but there are still a few things to add:

yum install gcc-c++ wget openssh-server -y

gcc-c++ is not necessary according to the 12cR2 installation manuals, but the GUI installer complains during the prerequisite checks if it’s not there.

wget is needed to download some files and it’s not on the Centos 7 template.

openssh server is to allow me to login remotely via SSH for the GUI install later.

Get OpenSSH To Autostart
systemctl enable sshd.service
systemctl start sshd.service
systemctl status sshd.service
Install Oracle Preinstall Package
#Get the Oracle Linux 7 repo - this works for Centos 7.
cd /etc/yum.repos.d/ 
#The following stops GPG Key errors:
wget -O /etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
#Update everything
yum update -y
#Install the preinstall package
yum install oracle-database-server-12cR2-preinstall -y
Configure System Limits
echo "oracle soft nofile 1024" >>/etc/security/limits.d/oracle-rdbms-server-12cR2-preinstall.conf
echo "oracle hard nofile 65536" >>/etc/security/limits.d/oracle-rdbms-server-12cR2-preinstall.conf
echo "oracle soft nproc 16384" >>/etc/security/limits.d/oracle-rdbms-server-12cR2-preinstall.conf
echo "oracle hard nproc 16384" >>/etc/security/limits.d/oracle-rdbms-server-12cR2-preinstall.conf
echo "oracle soft stack 10240" >>/etc/security/limits.d/oracle-rdbms-server-12cR2-preinstall.conf
echo "oracle hard stack 32768" >>/etc/security/limits.d/oracle-rdbms-server-12cR2-preinstall.conf
echo "oracle hard memlock 134217728" >>/etc/security/limits.d/oracle-rdbms-server-12cR2-preinstall.conf
echo "oracle soft memlock 134217728" >>/etc/security/limits.d/oracle-rdbms-server-12cR2-preinstall.conf
Change Password For “oracle” User
passwd oracle
   <<set a password>>
Create Oracle Home Directory
mkdir -p /u01/app/oracle/product/
chown -R oracle:oinstall /u01
chmod -R 775 /u01
Modify The Profile Of “oracle” User
echo "# Oracle Settings" >>/home/oracle/.bash_profile
echo "export TMP=/tmp" >>/home/oracle/.bash_profile
echo "export TMPDIR=\$TMP" >>/home/oracle/.bash_profile
echo "export" >>/home/oracle/.bash_profile
echo "export ORACLE_UNQNAME=cdb1" >>/home/oracle/.bash_profile
echo "export ORACLE_BASE=/u01/app/oracle" >>/home/oracle/.bash_profile
echo "export ORACLE_HOME=\$ORACLE_BASE/product/" >>/home/oracle/.bash_profile
echo "export ORACLE_SID=cdb1" >>/home/oracle/.bash_profile
echo "export PATH=/usr/sbin:\$PATH" >>/home/oracle/.bash_profile
echo "export PATH=\$ORACLE_HOME/bin:\$PATH" >>/home/oracle/.bash_profile
echo "export LD_LIBRARY_PATH=\$ORACLE_HOME/lib:/lib:/usr/lib" >>/home/oracle/.bash_profile
echo "export CLASSPATH=\$ORACLE_HOME/jlib:\$ORACLE_HOME/rdbms/jlib" >>/home/oracle/.bash_profile
Create Software Directory And Copy Files Over
mkdir -p /u01/software
cp /mnt/common_share/ /u01/software
rm /u01/software/
Run The Installer

Log in as the “oracle” user

cd /u01/software/database

Install the software and a database by running through the GUI screens and following the instructions. The installer complains on the prerequisite checks screen about some of the kernel memory parameters (rmem%, wmem%) which you can ignore.

Configure Auto Start

Follow these instructions from Tim to setup auto start using the runuser method – make sure you change the ORACLE_HOME to be not that is mentioned.

Now reboot the container and it should return with the database automatically started.

Check Oracle Database Auto Starts
[oracle@db12cr2 ~]$ sqlplus /nolog

SQL*Plus: Release Production on Thu Mar 2 14:16:53 2017

Copyright (c) 1982, 2016, Oracle. All rights reserved.

SQL> conn sys/Password01 as sysdba
 SQL> show sga

Total System Global Area 3221225472 bytes
 Fixed Size 8797928 bytes
 Variable Size 687866136 bytes
 Database Buffers 2516582400 bytes
 Redo Buffers 7979008 bytes

All pretty painless and relatively quick. I’ll take a dump of the container next in order to use it as a template for building future containers.

Fixing blank charts on ambari home page (Hortonworks Data Platform)

Thu, 2017-02-09 15:54

I created a 4 node container based (Proxmox LXC) Hortonworks Data Platform 2.5 Hadoop cluster recently and all went well apart from all the charts on the Ambari homepage were blank or showing “N/A”, like this:

An outline of the environment:

  • 4 node cluster of LXC containers on Proxmox host
  • Centos 7 Linux OS
  • Nodes are called bishdp0[1-4], all created from same template and identical configuration
  • All containers are on network
  • DNS Server also available on same network and all hosts can resolve each other via DNS
  • Hortonworks Data Platform version 2.5
  • Proxmox host sits on a corporate network and the host has iptables set to allow the containers on to reach the internet via the corporate proxy server, e.g. for yum access
  • Other than the blank charts everything appears to be working fine

After much reading around it turns out that I hadn’t quite set up the proxy serving correctly, specifically that I hadn’t told Ambari to ignore some hosts, namely the bishdp0[1-4] hosts on the network, when proxying. I can’t find a 2.5 HDP version of the document for setting up the proxy serving for Ambari but the 2.2 instructions worked.

Steps I took to fix the problem:

First stop the services on the cluster. Log on to the node with the Ambari Server where I have a script called which I created based on part of this article. Thanks slm.

Run the script:


Now stop the Ambari agent on all the servers:

pdsh -w bishdp0[1-4] service ambari-agent stop

Now stop the Ambari Server:

service ambari-server stop

Now edit the Ambari environment script:

vi /var/lib/ambari-server/

Look for the line that begins “export AMBARI_JVM_ARGS” and ensure it has entries for the following parameters:

  • http.proxyHost
  • http.proxyPort
  • http.proxyUser
  • http.proxyPassword
  • http.nonProxyHosts

It’s the last one that was missing in my case, which meant that Ambari was trying to go to the proxy server even for these containers on the network.

After editing, the line looked like this (I’ve redacted the specifics – just replace the entries with values suited to your environment):

export AMBARI_JVM_ARGS=$AMBARI_JVM_ARGS’ -Xms512m -Xmx2048m -XX:MaxPermSize=128m -Dhttp.proxyHost=<proxy IP> -Dhttp.proxyPort=<proxy port> -Dhttp.proxyUser=<user> -Dhttp.proxyPassword=<password> -Dhttp.nonProxyHosts=<*.domain>$ROOT/etc/ambari-server/conf/krb5JAASLogin.conf’

Now restart everything, Ambari server first:

service ambari-server start

…then the agents on all nodes (pdsh is great – thanks Robin Moffatt for your notes!)

pdsh -w bishdp0[1-4] service ambari-agent start

And finally start the services on the cluster using the script.


After I did this, the charts started showing details:

Transferring default domains for SQL Developer Data Modeller

Wed, 2016-11-16 08:46
*{-webkit-box-sizing: border-box;-moz-box-sizing: border-box;box-sizing: border-box}div{display: block}.kc-container{width:100%;max-width:1170px;margin:0 auto;padding-left:15px;padding-right:15px;box-sizing:border-box}.kc-row-container:not(.kc-container){padding-left:0;padding-right:0;width:100%;max-width:100%}.kc-elm{float: left;width: 100%;}.kc_wrap-video-bg{height:100%;left:0;overflow:hidden;pointer-events:none;position:absolute;top:0;width:100%;z-index:0}.kc_single_image img{max-width:100%}.kc-video-bg .kc_column{position:relative}.kc-infinite-loop{text-align:center;padding:50px;font-size:18px;color:red;width:100%;display:inline-block}.kc_row:not(.kc_row_inner){clear:both;display:block;width:100%}.kc-wrap-columns,.kc_row_inner{margin-left:-15px;margin-right:-15px;clear:both}.kc_row.kc_row_inner{width: calc(100% + 30px);}.kc_tab_content>.kc_row_inner{width:100%;margin:0}.kc_column,.kc_column_inner{min-height:1px;position:relative;padding-right:15px;padding-left:15px;width:100%;float:left}div.kc_column,div.kc_column_inner{clear:none}div[data-kc-fullheight]{min-height:100vh}html body div[data-kc-parallax=true]{background-position:50% 0;background-size:100%!important;background-repeat:no-repeat!important;background-attachment:fixed!important}div[data-kc-fullwidth]{margin-left:0!important;margin-right:0!important;position:relative;box-sizing:content-box}.kc_text_block{display:inline-block;clear:both;width:100%}@media screen and (min-width:999px){body div[data-kc-equalheight=true],body div[data-kc-equalheight=true]>.kc-container{display:-webkit-flex!important;display:-ms-flexbox!important;display:flex!important}body div[data-kc-equalheight-align=middle]>.kc-container>.kc-wrap-columns>.kc_column>.kc-col-container{display:-webkit-flex!important;display:-ms-flexbox!important;display:flex!important;align-items:center;flex-wrap:wrap;justify-content:center;height:100%}body div[data-kc-equalheight-align=bottom]>.kc-container>.kc-wrap-columns>.kc_column>.kc-col-container{display:-webkit-flex!important;display:-ms-flexbox!important;display:flex!important;align-items:flex-end;flex-wrap:wrap;justify-content:center;height:100%}body div[data-kc-fullheight=middle-content],body div[data-kc-fullheight=middle-content]>.kc-container{display:-webkit-flex;display:-ms-flexbox;display:flex;align-items:center}.kc-wrap-columns,.kc_row_inner{display:-webkit-flex;display:-ms-flexbox;display:flex}.kc_row_inner, .kc-row-container.kc-container .kc-wrap-columns{width: calc(100% + 30px)}}@media screen and (max-width: 767px){html body .kc_column,html body .kc_column_inner{width: 100%}div.kc_row{display: block}}@media screen and (max-width: 999px){.kc_col-sm-3, div.kc_col-of-5{width: 50%}}.kc_col-sm-1{width: 8.33333%}.kc_col-sm-2{width: 16.6667%}div.kc_col-of-5{width: 20%;float: left}.kc_col-sm-3{width: 25%}.kc_col-sm-4{width: 33.3333%}.kc_col-sm-5{width: 41.6667%}.kc_col-sm-6{width: 50%}.kc_col-sm-7{width: 58.3333%}.kc_col-sm-8{width: 66.6667%}.kc_col-sm-9{width: 75%}.kc_col-sm-10{width: 83.3333%}.kc_col-sm-11{width: 91.6667%}.kc_col-sm-12{width: 100%}.kc-off-notice{display:none;}

Notice: You are using wrong way to display KC Content, Correct It Now

I got a new laptop the other day and installed all the software, including SQL Developer Data Modeller all fine. I then opened a model which had a bunch of tables with columns based off Domains…the columns did not have Domains against them but had been replaced with Logical data types instead.

After some research, the fix, in this case, involved copying the file “defaultdomains.xml” from the following directory on my old laptop, to the same place on the new laptop:

%SQL Developer Home%\sqldeveloper\extensions\oracle.datamodeler\types

After restarting and reopening the model all was back to normal.

What I probably could have done in the first place was to have created my own Domains file for the Design, saved in the Design folder and then when I transferred the Design by copying across the Design folder the domains would have come with it. I could have then just opened the Domain file on the new laptop. I guess it depends on whether I would want these domains to be Design specific or part of the defaults for all designs.

SGMB_URL = ""; jQuery(".dropdownWrapper").hide();

Creating Oracle Big Data Lite VM on Proxmox

Wed, 2016-11-16 02:42

The Oracle Big Data Lite VM available on Oracle technet, provides a pre built environment for learning about a number of key Oracle products, including Oracle 12c database, Big Data Discovery and Data integrator as well as Cloudera Distribution – Apache Hadoop (CDH 5.8.0).

The download ultimately delivers an OVA “appliance” file for use with Oracle VirtualBox, but there isn’t anything to stop you running this as a VM on proxmox 4, with a bit of effort, as follows.

NOTE – Things to read which can help with this process:

  1. Oracle Big Data Lite Deployment Guide.
  2. How to upload an OVA to proxmox guide by James Coyle:
  3. Converting to RAW and pushing to a raw lvm partition:
  • Firstly download the files that make up the OVA from here.
  • Follow the instructions on the download page to convert the multiple files into one single OVA file.
  • For Oracle Virtualbox, simple follow the rest of the instructions in the Deployment Guide.
  • For Proxmox, where I was running LVM storage for the virtual machines, first rename the single OVA file to .ISO, then upload that file (BigDataLite460.iso) to a storage area on your proxmox host, in this case, mine was called “data”. You can upload the file through the Proxmox GUI, or manually via the command line. My files were uploaded through the GUI and end up in “/mnt/pve-data/template/iso”.
  • Now, bring up a shell and navigate to the ISO directory and then unpack the ISO file by running “tar xvf BigDataLite460.iso”. This should create five files which include one OVF file (Open Virtualisation Format) and four VMDK files (Virtual Machine Disk).
root@HP20052433:/mnt/pve-data/template/iso# ls -l
total 204127600
-rw------- 1 root root   8680527872 Oct 25 02:43 BigDataLite460-disk1.vmdk
-rw------- 1 root root   1696855040 Oct 25 02:45 BigDataLite460-disk2.vmdk
-rw------- 1 root root  23999689216 Oct 25 03:11 BigDataLite460-disk3.vmdk
-rw------- 1 root root       220160 Oct 25 03:11 BigDataLite460-disk4.vmdk
-rw-r--r-- 1 root root  34377315328 Nov 14 10:59 BigDataLite460.iso
-rw------- 1 root root        20056 Oct 25 02:31 BigDataLite460.ovf
  • Now, create a new VM in proxmox via the GUI or manually. The VM I created had the required memory and CPUs as per the deployment guide, together with four Hard Disks – mine were all on the SCSI interface and were set to be 10G in size initially – this will change later.
  • The hard disks were using a storage area on Proxmox that was defined as type LVM.
  • Now convert the VMDK files to RAW files which we’ll then push to the LVM Hard Disks as follows:
qemu-img convert -f vmdk BigDataLite460-disk1.vmdk -O raw BigDataLite460-disk1.raw
qemu-img convert -f vmdk BigDataLite460-disk2.vmdk -O raw BigDataLite460-disk2.raw
qemu-img convert -f vmdk BigDataLite460-disk3.vmdk -O raw BigDataLite460-disk3.raw
qemu-img convert -f vmdk BigDataLite460-disk4.vmdk -O raw BigDataLite460-disk4.raw
  • Now list those raw files, so we can see their sizes:
root@HP20052433:/mnt/pve-data/template/iso# ls -l *.raw
-rw-r--r-- 1 root root 104857600000 Nov 16 07:58 BigDataLite460-disk1.raw
-rw-r--r-- 1 root root 214748364800 Nov 16 08:01 BigDataLite460-disk2.raw
-rw-r--r-- 1 root root 128849018880 Nov 16 08:27 BigDataLite460-disk3.raw
-rw-r--r-- 1 root root  32212254720 Nov 16 08:27 BigDataLite460-disk4.raw
  • Now resize the lvm hard disks to the corresponding sizes (the ID of my proxmox VM was 106 and my hard disks were scsi):
qm resize 106 scsi0 104857600000
qm resize 106 scsi1 214748364800
qm resize 106 scsi2 128849018880
qm resize 106 scsi3 32212254720
  • Now copy over the content of the raw files to the corresponding lvm hard disks:
dd if=BigDataLite460-disk1.raw of=/dev/vm_storage_group/vm-106-disk-1
dd if=BigDataLite460-disk2.raw of=/dev/vm_storage_group/vm-106-disk-2
dd if=BigDataLite460-disk3.raw of=/dev/vm_storage_group/vm-106-disk-3
dd if=BigDataLite460-disk4.raw of=/dev/vm_storage_group/vm-106-disk-4
  • Now start the VM and hey presto there it is.
  • You could stop there as it’s a self contained environment, but obviously you can also do a whole bunch of networking stuff to make it visible on your network as well.
SGMB_URL = ""; jQuery(".dropdownWrapper").hide();

Microsoft Accounts Fail To Log In To Windows 10 with “User Profile Service failed the login” Error.

Wed, 2016-10-19 01:05

My kids are getting to the age where they can’t keep away from the laptop, various pads or the Smart TV to go online…time for some protection I thought.

I figured, for the Windows 10 laptops, that I’d use the Microsoft Accounts approach and use the “big brother” features there to stop the kids watching things they shouldn’t and restrict their access time.

First step was to convert my local account into a Microsoft one – simple enough and worked fine.

Next step was to create additional Microsoft accounts and then have them linked up as part of the “Family” – again, fine.

Then tell the PC to add those users – again all fine and simple to do.

All going well up until now, but then when I try to logout of my working Microsoft account on the laptop and login to one of the Family Microsoft accounts, it fails with the “User profile Service failed the login:

Image result for user profile service service logon fail windows 10


After much googling and trying various things, the one which worked for me was to copy the directory C:\Users\Default from a working Windows 7 Ultimate machine onto the laptop with the problem (where the directory did not exist at all). The advice I found actually referred to copying from another Windows 10 machine, but I didn’t have one of those – only a Windows 7 one.

I then added the family Microsoft accounts back in and after logging out and trying to login as one of these added accounts then worked fine!

I can’t be certain what the issue was, but various reading suggested an issue where the machine was upgraded from Windows 7/8 to 10 and where the local profile (C:\Users\Default) was either missing or corrupted. Copying in a working one from another machine fixed the issue in my case.

SGMB_URL = ""; jQuery(".dropdownWrapper").hide();

OTN Appreciation Day: Automatic Storage Management (ASM)

Tue, 2016-10-11 04:48

Big shout out to Tim for kicking this off!

Automatic Storage Management (ASM) provides optimised volume management and filesystem capabilities for Oracle databases, whether they be single or multi instance (RAC) implementations.

Although introduced with Oracle 10g Release 1 in 2004, I first used it in a production scenario around 2008, when upgrading a hardware platform for a Data Warehouse. It seemed like a logical choice for myself and the DBAs at the time, although the storage team were less pleased at losing some control. Ultimately it proved a big success on that project and is still in stable, reliable use today.

Things I like about ASM include:

  • Simplifies storage management
  • Automatic rebalancing when capacity is added
  • Visibility within Enterprise Manager for monitoring
  • Availability of detailed metrics within the database
  • Reliable, balanced and consistent performance
  • Works with RAC
  • Rolling upgrades and patching
  • Provides a reliable cluster filesystem (ACFS)
  • Even more cool features coming in 12c such as Flex ASM


Some useful links:

ASM Administrators Guide 12cR1 (Oracle Docs)

The Mother Of All ASM Scripts (John Hallas)

Technical overview of new features for ASM in 12c (Whitepaper)

SGMB_URL = ""; jQuery(".dropdownWrapper").hide();

Transferring default domains for SQL Developer Data Modeler

Mon, 2016-10-03 10:27

I got a new laptop the other day and installed all the software, including SQL Developer Data Modeler all fine. I then opened a model which had a bunch of tables with columns based off Domains…the columns did not have Domains against them but had been replaced with Logical data types instead.

After some research, the fix, in this case, involved copying the file “defaultdomains.xml” from the following directory on my old laptop, to the same place on the new laptop:

%SQL Developer Home%\sqldeveloper\extensions\oracle.datamodeler\types

After restarting and reopening the model all was back to normal.

What I probably could have done in the first place was to have created my own Domains file for the Design, saved in the Design folder and then when I transferred the Design by copying across the Design folder the domains would have come with it. I could have then just opened the Domain file on the new laptop. I guess it depends on whether I would want these domains to Design specific or part of the defaults.

SGMB_URL = ""; jQuery(".dropdownWrapper").hide();

Oracle Virtual Columns – Can’t use plain column or duplicate expressions

Thu, 2016-09-08 16:38

I had a scenario today where I was loading a table and a particular column is known by multiple names in different source systems and thus to different people. In order to make everyone happy on this occasion, I wondered if I could create a normal column for one of the multiple names and then use virtual columns pointing at the normal column, for the other names.

I’m aware there are several ways of skinning this cat and that virtual columns was probably not the best choice in the first place, but I was just playing with an idea and it didn’t quite end up where I thought…so it was interesting in that respect.

The test code:

     column1    INTEGER NOT NULL 
    ,virtual_column1 INTEGER AS ( column1 ) VIRTUAL NOT NULL 
     column1    INTEGER NOT NULL 
    ,virtual_column1 INTEGER AS ( column1 + 0 ) VIRTUAL NOT NULL 
     column1    INTEGER NOT NULL 
    ,virtual_column1 INTEGER AS ( column1 + 0 ) VIRTUAL NOT NULL 
    ,virtual_column2 INTEGER AS ( column1 + 0 ) VIRTUAL NOT NULL 
     column1    INTEGER NOT NULL 
    ,virtual_column1 INTEGER AS ( column1 + 0 ) VIRTUAL NOT NULL 
    ,virtual_column2 INTEGER AS ( column1 + 1 - 1 ) VIRTUAL NOT NULL 

And the results:

    ,virtual_column1 INTEGER AS ( column1 ) VIRTUAL NOT NULL
ERROR at line 4:
ORA-54016: Invalid column expression was specified

Table created.

Table dropped.

    ,virtual_column2 INTEGER AS ( column1 + 0 ) VIRTUAL NOT NULL
ERROR at line 5:
ORA-54015: Duplicate column expression was specified

Table created.

Table dropped.

I expected it to just work, but I clearly ran in to two problems which scuppered my idea. Firstly, the virtual column can’t simply refer to a normal column without any changes to it, otherwise it fails with ORA-54016. The error isn’t particularly helpful, but eventually I worked out that it was because the column is simply a mapping to a non virtual column. Working around that by adding zero to the numeric column gets it to work, but it’s an ugly hack.

In my scenario there are three different names for this column, depending on the users involved, which then leads on to the next issue, which is that I’d then need two virtual columns pointing at the same source column. Unfortunately if I use the same hack twice, it fails with ORA-54015, because you can’t have two virtual columns with exactly the same expression! A slight variant to the hack and it works, but it’s getting uglier and uglier!

Time to seek out plan B!

SGMB_URL = ""; jQuery(".dropdownWrapper").hide();

Assertions in a future Oracle release

Fri, 2016-06-10 14:34

I just found this link on OTN to vote for including assertions in a future release of the Oracle database.

A great idea – please vote for it.

One of the most important votes this month…well, I do live in England! Smile

“Unstructured Data” – No such thing!

Fri, 2016-06-10 06:58

I keep hearing this term lately and I dislike it.

There is no such thing as Unstructured Data. All data has structure. If it didn’t have structure we wouldn’t be able to use it.

What about free text? Well, that’s just a single column value (stored in a CLOB in Oracle, for example) and the free text is, more often than not, on a row with other columns, such as identifiers and timestamps, i.e. yet more structure.

I think what people mean when they use this “marketing foam”TM term is “data that we have not yet defined the structure for”, but in order to use it at some later stage, the structure will need to be defined – that definition process doesn’t actually give the data structure in and of itself, it simply defines what that structure is, in order to be able to use it.

Interestingly, the Wikipedia article for Unstructured Data calls out the imprecise nature of the term:

The term is imprecise for several reasons:

  1. Structure, while not formally defined, can still be implied.
  2. Data with some form of structure may still be characterized as unstructured if its structure is not helpful for the processing task at hand.
  3. Unstructured information might have some structure (semi-structured) or even be highly structured but in ways that are unanticipated or unannounced.

In other words, it does have structure, but maybe we’ve not written it down, or the structure isn’t helpful to processing or is structured in ways we were not expecting – so what?…it’s still structured!

All of the above seem to me to support the view that all data does indeed have structure.

Temporary Post Used For Theme Detection (4a0f3a36-664d-47d9-93c7-24f2096e004d – 3bfe001a-32de-4114-a6b4-4005b770f6d7)

Fri, 2016-06-10 02:18

This is a temporary post that was not deleted. Please delete this manually. (31074fcb-66af-42a8-a620-ab6d94cd18a0 – 3bfe001a-32de-4114-a6b4-4005b770f6d7)

UNPIVOT Examples

Fri, 2016-05-20 08:47

Remove Linux package with apt

Wed, 2016-05-18 16:40

Resize filesystem

Wed, 2016-05-18 11:16