Migrate to the Oracle Database Cloud Service

On the 6th of October the Oracle Cloud Day 2015 takes place in The Netherlands. iAdvise is proud to be a bronze sponsor on what promises to be a day filled with interesting sessions.

One of the main topics will be the Oracle Database Cloud service. In this blog post, which was published in the OGh Visie earlier this year, you can read what the Oracle Database Cloud Service is, which configuration possibilities there are and what steps are needed to migrate.

Oracle Database Cloud Service

Suppose you want to start with the Oracle Database Cloud Service tomorrow, what options are there? What services are there, and what do they do? Below you will find the 3 services available. In the future, Managed Database as a Service will be added to these services.

Database Schema Service

Interesting to know is that, already since 2012, you can use the Oracle Cloud, namely through the Oracle Database Cloud Schema Service (ODCSS).  It was – and still is- possible to get a database schema in the Oracle Cloud, on an 11g database that is fully managed by Oracle. You can connect to this schema through: SQL Developer, RESTful Web Services, Oracle Java Cloud or
Oracle APEX.oracle_database_cloud_scheme_service

However, there are some limitations. You have no SQL*Net access, so you can only use SQL Developer (or APEX) to connect to your cloud database schema. You can do this through a so-called “Cloud Connection”, but need to know that you can only browse and load/unload data. So, not really convenient for development purposes. In addition, you cannot “connect” multiple schemas with each other, and packages such as: utl_smtp, utl_ftp and features as VPD (Virtual Private Database) are not available.

If you don’t mind these limitations, ODCSS is a very budget-friendly solution. Starting at $175 per month, you have an Oracle database in the cloud with 5GB storage available

Virtual image

Are you looking for a cloud environment where you can install and manage your databases yourself, then the virtual image option might be something for you. You get a dedicated VM on which you can install an 11g or 12c database, either manually or through the Database Configuration Assistant (DBCA). Unlike with the ODCSS, you have SQL*net access and full access to the VM.

You get the choice whether you want to pay per hour or per month. From $400/month or $0,672/hour you can get started with SE 1.

Database as a Service

If you choose the Database as a Service, you have the same possibilities as with the virtual image option, but in addition you have a number of nice tools at your disposal. So you can create a database instance in 4 easy steps thanks to Oracle’s automatic provisioning system. In addition, you have a number of cloud tools to maintain and monitor your database very easily.

You can start with the Database as a Service for a SE1 database from $600/month or $1,008/hour.

Setting Up the Oracle Cloud Database as a Service

Database instance

For the creation of an Oracle database you have a simple wizard. This initiates the automatic provisioning of your database.  The provisioning ensures that your database is installed with cloud tooling technology and it configures the backup facilities.


The 4 steps that you must follow are:

  1. Choose your service: Virtual image or Oracle Database Cloud Service
    How do you want to pay: hourly or monthly?
  2. Choose your database version: 11gR2 or 12c?
  3. Choose your edition: SE1, EE, EE High performance, EE Extreme performance
  4. Choose the name of your instance, your compute shape (number of CPUs) and connect an SSH key to access to your VM.  Set a number of database configurations: how much storage you need, SID, and – in the future also a possible – a fail over database. If you also choose for Backup and Recovery, you select the storage container here.
    Then simply confirm, and you’re done! Fifteen minutes later you have a fully functioning database instance in the Oracle Cloud and access to a dedicated VM.


Backup & Recovery

When you choose the Oracle Cloud Database as a Service, you also have the necessary cloud tooling technology to simply set up the desired backup options.

If you’re only interested in a local backup, then the block store only option will do. During the provisioning of the VM, some additional disk space will be configured for the backup, by default 1.7 times the required database data volume. Through a combination of the familiar Oracle RMAN and classic system management tools, nicely packaged in a command line API, not only your database but also important system files are added to the local backup.

If you want full protection for your VM, then your best option is to choose the block store and cloud storage option. This option presupposes that Oracle Cloud Storage as a Service is purchased and configured. With a few simple commands, you will then be able to restore your full VM instance, including system files and configured Oracle database, into a new VM.

Both systems follow a series of Oracle’s best-practice guidelines and can be expanded and adapted to suit your needs.

Do you want your on-premise Oracle database(s) or Oracle database(s) that were created in an Oracle Cloud Database – Virtual image instance, also to be backed up in the Oracle Cloud, then you can set this up via the Oracle Cloud Backup as a Service. This service only relates to the storage of Oracle database backups. Do you also want to back up other types of data in the Oracle Cloud – for example, important system files – then you can do the setup through the Oracle Cloud Storage as a Service. In that case you are responsible for setting up the necessary backup policy.

Access rules

Who has which access to the Oracle Cloud Database as a Service?

Each Compute Cloud Service VM, child to the Oracle Cloud Database as a Service, is shielded from the angry world through so-called access rules. These are firewall rules that check the access to the VM specific protocols, such as HTTP(S), SSH or SQL * Net access. In these rules you define what source groups of VMs (network groups) or lists of IP addresses on a specific protocol may connect within a certain target group of VM(s).

When creating an Oracle Cloud Database as a Service instance, by default, 7 Oracle Compute Cloud Service access rules are created. These access rules control access to SSH (port 22), Enterprise Manager Database Control (port 1158), Enterprise Manager Database Express 12c (port 5500), Database listener (port 1521), Glassfish Server admin console (port 4848), HTTP (port 80) and HTTPS (port 443). The last one is necessary for accessing the Oracle REST Data Services, Oracle APEX and Oracle Cloud on-instance database monitor.

Only the SSH access is standard enabled. The other access rules must be explicitly enabled after creation.


Migrate to the Oracle Cloud

Have you decided to use the Oracle Database Cloud Service? Nice, but now you probably want to know what steps are needed to get your current on-premise environment in the cloud. And that happens to be one of the strengths of the Oracle Cloud: because you have full access to your dedicated cloud VM, you can tackle the migration in different ways.

For the migration of OraEvents (an application we build for Oracle) we used the classical method: an Oracle Data Pump export (expdp) of the on-premise database and an import (impdp) into the cloud database. The steps we went through are:

  • Expdp of the database schema
  • Copy the expdp DATA_PUMP_DIR directory to the Oracle database
  • Preparing the database – create new tablespaces, user schemas and assign the necessary rights
  • Import the expdp into the new database schema
  • Export and import of APEX workspace and applications
  • Copy the APEX images in the default DocumentRoot of the Glassfish server running the ORDS
  • Ready for testing!


If you decide to migrate to the Oracle Cloud today, you have a range of options available to ensure that your cloud database is more than just a replacement for you on-premise database. The automatic provisioning, powerful cloud tooling and integrated systems offer a great extra value. Because migrating to the cloud is very similar to a classic migration, there are no major objections from a technical perspective. The Oracle Cloud is ready. Now, it’s up to us!

OGH APEX World 2014

Last week we attended the the 5th annual APEX World event in Zeist. As every year it was very nice to meet the growing APEX community in the Benelux, combined with some excellent APEX international and dutch presentations.
The  keynote was given  by Joel Kallman about APEX 5.0 followed by 18 very interesting sessions about customer business cases, technical developments and international presentations by APEX specialist from all over the world.

APEX 5.0

The key focus in the new APEX 5.0 is improved developer productivity.oracle apex page designer
The page builder is completely new. Through this interface developers will be able to do more in less time and most important, in fewer clicks. With a properties sidebar on the right side of the screen it will be possible to quickly change elements and regions on a page, even multiple elements at the same time!  Regions and items can be created through drag and drop which increases the development speed.

Other new features

Improved tab navigation. The current tab system isn’t user friendly enough, so it’s better to use lists. Now you can create new pages and define their hierarchy in the application. When this is done, an automatic tab will be created with dropdown submenus to display the hierarchy.

Interactive reports
Two important improvements for interactive reports. First and foremost it’s possible to have multiple interactive reports on one page, something we’ve all been waiting for since APEX 4.x. And secondly there is a new format function to pivot your report. Joel Kallman presented this feature: in a couple of clicks he created a nice pivoted table on the screen.

jQuery Mobile integration
With jQuery Mobile your SQL reports will have the possibility to be responsive. You have the option to:
a) only display the most important columns on a small screen, or
b) to switch to some kind of single record view. The result is something similar to what you can see here: http://elvery.net/demo/responsive-tables/

Modal popup
Instead of using a plugin to let your pages open in a modal window, users can now set this feature as a property of the page. Whenever the user navigates to this page, it will open in a modal window.

Be sure to take a look at the APEX early adaptor: apexea.oracle.com



After the APEX 5.0 demonstration, there were 3 parallel tracks, all with very different and interesting sessions.  Read our impressions …

Going public with your APEX application
FOEX brought this presentation very well. Their problem scenario was the following one: If you want to make a public APEX application, you are always stuck with the typical APEX URL like “apex/f?p=100:1:5039230103::::”. During the demo they showed how to create a nice and readable URL like “apex/demo/customers”. To accomplish this they used aliases, REST services, PL/SQL and a few lines of javascript.

The best of both worlds: going hybrid with your mobile APEX application
Roel Hartman gave a presentation about Phonegap in combination with APEX. He showed a nice demo on how to sync the contacts from a database with the ones from his cell phone through a Phonegap App. It was surprising how easily this could be setup without too much code and in-depth knowledge. He used REST services to sync the data between APEX and his cellphone.

Using AngularJS in oracle applications express
Dan McGhan of Enkitec (USA) brought a technical session about combining AngularJS and APEX. He showed us a single page application containing a to do list with advanced calendar features. The end result was very nice and the demo illustrated the power of AngularJS, but it certainly requires some time to understand this framework. Maybe an interesting idea is to include AngularJS natively in APEX 6.0?

A B2B weboracle apex b2b webshop - tuur hendrickxshop with APEX!
iAdvise did two presentations. The first one dealt with a B2B webshop we developed in APEX for Billiet. Justine Ghekiere gave a brief introduction about the core business of her company, Biliet. Our colleague Tuur Hendrickx showed a lot of features he implemented in the webshop with APEX. Topics he show-cased were:  special advertisements, restricted products for different customers, the use of a shopping cart and a stunning layout were demonstrated.

We also attended a nice presentation of Martin Giffy D’Souza about APEX and HTML5. He showed the advantages of HTML5 and the typical use cases in APEX. During a live demo he showed how to record a video within APEX and stream the feed to another frame in the same screen. Really impressive!  Also nice to see was how easily it is to implement voice recognition by using HTML5.

Dutch immigration services (IND) monitor xml messages with oracle apex
A department of the Dutch government has built an application which provides residence permits to immigrants or refugees. Before they could start building the APEX application there was a lot of effort necessary in the Oracle database for dealing with all the XML files. It was not just a problem with the size of the XML files, but there were also issues with differences between Oracle 10.2 and 11.2 in the way the database handles XML files.

Reporting solutions for oracle APEX – choose your weapons
During this session Dietmar Aust gave us an overview of possible reporting solutions  for APEX applications. Many solutions were covered in an objective way:  BI Publisher, Jasper Reports, Apache FOP, APEX PDF printing, PL/PDF, … Dietmar even demonstrated our own tool Doxxy (www.doxxy.eu). Nice to hear that he likes Doxxy! He also showed us his own solution for typical problems related to exporting data from interactive report to MS Excel, especially regarding the proper data types: OPAL:XP (for eXPorting to MS Excel).

Single-click deployment in APEX development
One of the last tracks we visited was about single-click deployment of APEX applications in OTAP areas. They talked about the use of bamboo, in combination with GIT and APEX. It was nice to see how they solved the problem of continuous integration with APEX.

A logistic data portal with APEX!oracle apex data portaal - menno hoogendijk
In the second iAdvise customer case Robert Esseling explained why Bas Logistics needed a data portal. Those requirements where then demonstrated by Menno Hoogendijk.
The portal has an admin module to manage the data import and mapping settings. In the very straight-forward  front-end, users drill down from dashboards to detailed data.


Thanks to the organization for hosting this great event, really one of the best conferences in the benelux!
See you at APEX World 2015!

Mobile Applications with APEX & jQuery Mobile

Mobile development is not the next big thing anymore, it is THE big thing today. And as you may already know, Oracle APEX is ready to take on the challenge of creating mobile business solutions.

We at iAdvise already started to experiment with mobile application development in APEX 4.0, but off course this was not the ideal environment yet. Since APEX 4.1 you can declaratively create mobile applications with jQuery mobile, and with the latest release (4.2) it’s really starting to lift off. You can even declaratively build responsive applications.

In May 2012 we gave a seminar about mobile development, and since then we successfully developed several mobile solutions for our clients. Below you can find a demo of an application we created for one of our most valued customers, New Holland Tractor Ltd.

New Holland Tractor Ltd

About New Holland: Since 1895, New Holland is committed to providing solutions that improve farming efficiency and productivity.
New Holland offers cash crop producers, livestock farmers, contractors, vineyards and groundcare professionals the largest choice of easy-to-operate tractors, harvesters and material handling equipment.

New Holland complements the widest agricultural equipment offering in the world with efficient Parts and Service support and a range of tailored financial services from a specialist in agriculture. Setting themselves the highest standards in quality and service, is part of the culture of New Holland, part of the DNA of New Holland. The plant in Antwerp (New Holland Tractor Limited) with some 1,000 employees, is a major manufacturing unit of tractor parts such as rear axles and drive systems.

Mobile application

New Holland Tractor requested a mobile solution to let their workers use checklists at the quality gates of the assembly lines in the plant.
In the past they had to manage all these checklists by hand. For each checklist, a paper form had to be filled out after which it had to be processed by hand. This created a lot of overhead and it was very easy to make errors.

The application had to be developed in Oracle APEX 4.1 and had to be super user-friendly. The result is a simple, yet elegant application.


At iAdvise we invest a lot to make our applications user-friendly. For this project we started off by designing mockups that were used as guidelines during the development phase, those helped us to create a very simple and efficient application.

For the workers of New Holland Tractor, this solution was a huge improvement. Now that the checklists are processed automatically, it’s very easy to analyze the production process and there’s less room for errors, hence it further increased both productivity and efficiency at the assembly lines.

We feel that mobile development with APEX is really kicking off and the APEX development team is on the right track to make APEX a mobile enabled development environment.

Exciting times are coming!

OGH APEX Dag 2012

This monday I gave a presentation at the OGH APEX Day about a Forms to APEX migration together with my colleague Ronny Boeykens.

Everything went smoothly, except when my laptop decided to install some windows updates and restarted out of the blue :).

Our presentation was kind of a follow-up on the ‘Forms Modernization’ presentation of my colleague Gert Poel, where they illustrated what the possibilities are when you want to modernize your Forms applications.

In our presentation we explained that APEX can be a worthy substitute for Forms, by showing some examples of a business case.

OGH APEX Dag - Can APEX replace forms

OGH APEX Dag - Can APEX replace forms

I also saw some interesting presentations during the day:

First off was the keynote by Patrick Wolf, where he gave a nice glance at the features we can expect in APEX 4.2.
The things that stood out the most for me were:

  • jQuery mobile will be fully integrated.
  • It will be possible to attach multiple themes to one application, so you can have a mobile and desktop theme in your application.
  • Subscription will be possible on theme level: Thank you!
  • The max. number of items on a page will be raised to 200. Offcourse Patrick noted that when you have more then 100 items on your page, you seriously have to consider if your page is still usable!
  • You will be able to download packaged applications from the Oracle Cloud.

Next up was the presentation of Roel Hartman, that showed some interesting things you can do with HTML5. Especially the websockets, where you can push data from your database to your webpage was really cool. Also the local storage caught my eye.

I also liked the presentation of John Scott, that demonstrated the possibilities of the error handling feature in APEX 4.1. I’m still waiting for the result of the last demo though :).

A big thank you to the organization, everything was perfect! I really enjoyed the day and saw some interesting things.