Who's online

There are currently 0 users and 31 guests online.

Recent comments

Oakies Blog Aggregator

Difference in time - a plsql solution to human readable date/timestamp diff

While preparing to get the comments section done for my codemonth site, I needed something that could tell how old a comment was. Not just showing the date, but maybe showing in a bit more readable way, when the comment was from. So if you look at sites like Twitter and Facebook, comments are "dated" with strings like "1 hrs" or "1 day old" etc. So I wanted to make a small package that could do that for me.

AIOUG Sangam15 : Day 1

I was up at about 04:00 on Saturday and waiting for breakfast to open at 06:30. Pretty much as soon as I ate something I felt really tired again. That after a day of sleeping… :)

I headed on down to the conference and instantly saw a bunch of friendly faces, including lots of people I had met on the OTN Yathra 2014 Tour.

After the keynotes, I got to have a quick chat about User Groups with Mary Lou Dopart from Oracle, then it was off to my first session about database consolidation.

I’m always more nervous about giving introductory/overview sessions than I am about full-on technical sessions. My database consolidation session is a little bit like a history lesson of consolidation, including old and new. The response was very good. I had a lot of people wanting to speak to me afterward, which meant I missed the next two sessions. I don’t mind that, as I feel my purpose here is to interact with as many people as possible. As long as people want to speak to me I’m willing to speak. :)

After lunch I had a major energy crash again. Whenever I was speaking to people I felt really up-beat, but as soon as the conversation stopped I felt like I wanted to collapse on the floor and sleep. To play it safe I went back to my room to sleep for a couple of hours.

Next up I went to see Debaditya Chatterjee and Giridhar Ravipati speaking about “Oracle Multitenant Best Practices”. Some points that came out of that were.

  • Always use the AL32UTF8 character set for the CDB. Most other character sets can be converted to that to allow them to become PDBs and in a future version, we might be able to plug in a PDB with any character set, provided it is a subset of the CDB character set.
  • Use OMF, as it simplifies file management substantially.
  • Consider the impact of operations, like patching. Are you patching all PDBs, or not? If not, you will be using the unplug/plug approach to patching.
  • Standardise as much as possible.
  • Size SGA, redo and undo correctly to allow for the combined needs of the consolidated workload.
  • Always use Resource Manager to control resource usage of each PDB.
  • Don’t modify PDB$SEED. Create a new PDB with the modifications, then use that as the clone source, for new PDBs, instead of the seed.
  • Stagger maintenance windows within a CDB so all PDBs aren’t doing maintenance tasks at the same time.

Some points about possible features in 12.2 were also mentioned, including:

  • Maximum number of PDBs increased from 252 to 4096.
  • PDB memory management via resource manager.
  • Ability to set CPU_COUNT at the PDB level to allow the equivalent of instance caging for the PDB.
  • Hot cloning of PDBs and incremental PDB refresh.
  • Application containers, to allow common definitions across all similar PDBs. Useful for ISVs who require many copies of the same PDB, like one for each customer.
  • Online relocate of PDB from on-premise to cloud, as demoed by Larry Ellison at OpenWorld 2015

Remember, all talk of 12.2 is covered by the safe harbour slide, so those features might not end up in the final release. :)

After speaking to Debaditya and Giridhar, we headed off to a panel session to close the day. As most people will know, putting me on a panel can be a dangerous thing. I ended up having something to say about almost everything. Panels are a mix of scary and fun. :) As usual, the session was followed by additional questions and conversations which lasted about an hour. :)

After saying a few quick goodbyes, it was off to grab some food, then back to my room to sleep some more. Are you starting to sense a pattern here? :)

Overall, day 1 was a really fun experience. Let’s hope day 2 does not come with quite so much jet-lag! :)



AIOUG Sangam15 : Day 1 was first posted on November 22, 2015 at 5:38 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

The power of combining API's (1+1 > 2)

Integrating plsql with different web applications, is extending the functionality of the database beyond just data management. But it is when we combine these API integrations, that we can really see the benefit of them. In this entry I will show how integrating just two different API's, we can create an easy monitoring solution, that can rival more expensive traditional choices.

What I will do, is I will combine the Airbrake API and the Pagerduty API, to not only track errors, but also implement a monitoring solution that can alert my DBA that something is wrong. I will show how we can use those API's to setup team based alerting and reporting on errors, and hint on how you could further imporve or extend the usage of this.

Docker Data Containers


photo by

One challenge with Docker is having persistent storage for a container especially when that container gets restarted on another VM host and we want it to point to the same data. If we add in Delphix with Docker we can easily move persistent data and a docker container to a new host.

For the docker container we will use wordpress that leverages a MySQL database for it’s persistent datastore.

In this example I will be using Delphix Express version and the Landshark source and target environment and will be using a wordpress docker container that points to a persistent MySQL data store..

The source and target machines already have docker and MySQL on them, so all we need to do is start MySQL, create a wordpress schema, start docker, get the docker wordpress container, then start a docker container with wordpress and point to the MySQL database.

On the Source
ssh  delphix@source
# password is delphix
su –
# password is delphix
vi /etc/my.cnf
#add line “user=root” then save file
service mysqld start
mysql -u root -p
CREATE USER wordpressuser;
SET PASSWORD FOR wordpressuser= PASSWORD(“password”);
GRANT ALL PRIVILEGES ON wordpress.* TO wordpressuser IDENTIFIED BY ‘password';

Then start docker and download the “wordpress” container

service docker start
docker pull wordpress

Start docker wordpress container

docker run -p 80:80 –name wordpress  \
           -e WORDPRESS_DB_HOST= \
           -e WORDPRESS_DB_USER=wordpressuser \
           -e WORDPRESS_DB_PASSWORD=password \
           -d wordpress

Now, on the source machine, we have a docker wordpress container using a persistent MySQL database for storage.

Now to move this docker container to another host, we can link in the MySQL database to Delphix. Once linkedin, we can provision a copy out to any registered host.
In Delphix, I just click + in the top left to add a datasource. The MySQL database should show up in the top of source.
Screen Shot 2015-11-19 at 4.49.43 PM
Screen Shot 2015-11-19 at 4.50.17 PM
Once linking is finished we can provision the MySQL out to another target machine and startup a new docker container pointing to this new virtual MySQL database (VDB).
I just click on the dSource on the left, then on the right click on provision and choose the target machine and a port. I’ll use 3306 for the port.

Now I have a thin clone of the MySQL database, a virtual database (VDB), running on the target machine. I just need to create a docker wordpress container to use it.

service docker start
docker pull wordpress

docker run -p 80:80 –name wordpress \

           -e WORDPRESS_DB_HOST= \
           -e WORDPRESS_DB_USER=wordpressuser \
           -e WORDPRESS_DB_PASSWORD=password \
           -d wordpress

Now I can access my wordpress blog on my target machine and modify it separately from the source. If the target machine goes down, I can migrate the VDB to another host and startup the container there and point the wordpress container to the same VDB now running on a new host.

One other change I made on the target VDB is changing the siteurl and home to be the new IP of the target machine:

mysql -u wordpressuser -ppassword -h -P 3306 wordpress << EOF

update wp_options set option_value=’′ where option_id<3;

select option_value,option_id from wp_options where option_id < 4;



From here we can set up architectures where the source is hosted directly on Delphix and Delphix can manage version controlling the source MySQL database and WordPress application.

We can spin out multiple VDBs in minutes for almost no storage to give to multiple developers to try modifications and merges of changes on.


CLOBS, from 11g to 12c

If you are a regular user of LOB’s in the database, take care when you switch from 11g to 12c.  Notice the subtle difference:

In 11g, you are permitted to, and recommended to, use SECUREFILE lobs, but they are not the default

SQL> select name, value
  2  from v$parameter
  3  where  name = 'db_securefile';

NAME                                     VALUE
---------------------------------------- ---------------
db_securefile                            PERMITTED

In12c, SECUREFILE lobs will be the default.

SQL> select name, value
  2  from v$parameter
  3  where  name = 'db_securefile';

NAME                                     VALUE
---------------------------------------- ---------------
db_securefile                            PREFERRED

This change should be beneficial, because SECUREFILE lobs are the newer and more functional implementation, but just be aware that the default has changed between versions.

AIOUG Sangam15 : The Journey Begins

The day started pretty early. I was packing at 00:00 and the alarm went off at 05:00. Nothing like last minute packing to focus the mind. :)

The taxi to the airport was good. The driver was interesting, speaking to me about his visits to India and Pakistan. It certainly helps pass the time when the driver is chatty.

I wasn’t able to check-in online, but there was no queue at the check-in desk, so that was quick. It’s such a short trip I’m only traveling with hand luggage, but I still checked it. I can’t be bothered to fight for overhead locker space with everyone. The queue for security was really long, but they had all the sections open, so it moved quickly. I had to get my laptop scanned because … reasons?

Once through security I bought myself a coffee and promptly threw it across the floor. Embarrassing much! I was hoping to find some floor to take a nap on, but after shaming myself with the coffee situation, I thought the last thing I needed was to shame myself further by sleeping on the floor. I try to limit myself to one shameful moment per airport visit…

The flight from Birmingham to Dubai was easy. It was meant to be 6.5 hours, but we arrived early. The plane was half empty, so there was free space next to me. Happy days. During the flight I watched:

After a 2.5 hour layover in Dubai, it was time for the next flight. The flight from Dubai to Hyderabad was really busy and some lucky people, including me, got upgrades to business class (see video). It was only a 3 hour flight, but I’m all about the business class these days! :) The important thing about upgrades is you have to pretend it wasn’t a free upgrade and you could afford it. :) During the flight I watched the second half of Mission Impossible : Rogue Nation. It was quite good.

After a very pleasant flight, I arrived at Hyderabad and bumped into Kamran Agayev at customs. We agreed to share a taxi to the hotel, then I made him wait for over an hour for my bag to arrive. It was really embarrassing. Sorry mate!

I’m now firmly ensconced in the hotel (see video). It’s 05:24 and I plan to spend the whole of today (Friday) in bed playing catch-up, before the conference starts on Saturday.



AIOUG Sangam15 : The Journey Begins was first posted on November 20, 2015 at 12:56 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

howto: github – talks branch

A couple of months ago I did a presentation and decided to put the files up in github ( just because I wanted to move away from dropbox for my scripts and resources. Well, that was very convenient because the audience can just download the master zip file and that’s it! But then I figured I don’t want to have the next presentation on another repo and it would look pretty messy on my git account. Ultimately I’d like all the presentations to be on one repo and only separated by folders but then by default if you just put it that way then the “download zip” will download all of the conference folders.

The howto below will show you how to create a branch for each presentation folder so that every folder will have its own “download zip” link. Another way to get a downloadable link is through releases but it doesn’t fit the requirement of having just the subset of files shown on the zip file.

Here we will be working on the following branches:

master – this is the default branch where all the files are shown
empty – an empty branch created from master. this is just used for creating the stage branch, more like a gold image copy
stage – this is where we initially commit the presentation files and later on will be deleted once the custom branch is created and merged to master
a custom branch (in this example: talks3) – this is the branch we create from the stage and then merged with the master

Click on the link below to get started.

howto: github – talks branch

Soon this URL will be repopulated with my previous conference files (demos/scripts/slides).

If the branching stuff is to much for you, then here’s some git learning references


Lightweight application performance monitoring

Monitoring application performance, is always difficult. Sometimes you want to monitor every aspect (with a minor latency impact) and sometimes you just want to monitor the basics. Like counting how many times a specific business process was called, or measuring how long it took. You also want it to be lightweight, and you want it to be really simple. As in really really simple.

OOUG RAC day presentation files and scripts

Thanks for coming to my presentations in RAC day at Dublin, Ohio. Please find the presentation files below. Hopefully, I will get video files and upload that here too.

OOUG presentation files and scripts

md5 checksum of the zip file is:


Oracle Midlands : Event #12

Just a quick reminder, Oracle Midlands Event #12 is just around the corner.

Update: The first talk is now “Why use PL/SQL?” by Bryn Llewellyn.

This is the day after I get back from India, right after my first day back at work. It’s going to be really hard to drag myself there, but I know it will be worth it!






Oracle Midlands : Event #12 was first posted on November 16, 2015 at 3:59 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.