Category Archives: Oracle tips

Oracle on OpenVMS – revival


Can it be true?

Will there be Oracle on OpenVMS again? Meaning the “regular” (sorry) Oracle (12c) RDBMS on a revived VMS?

As many who have ever lived on OpenVMS have always known:

OpenVMS will never die!

OpenVMS can never die, becOpenVMSause it is still running way to many hidden, hyper-mission-critical environments.
The fact that these environments are hidden, combined with the fact nobody ever spent any marketing budget on OpenVMS at all, created a super solution nobody knows about. And you cannot love what you don’t know.

A lot has been happening around this tormented operating system. OpenVMS is indefinitely bound to Digital Equipment Corporation (DEC) which was acquired by Compaq in June of 1998 and then merged into Hewlett Packard in May 2002.

DIGITAL-logo
Personally I have lost access to OpenVMS, and Oracle op OpenVMS, around 1995, when these systems were replace by HP Unix. I never fully recovered 😉

Until a few years ago I was introduced to a Alpha emulator, which creates a virtual machine with (obviously) an Alpha processor, which allows you to run OpenVMS. This was one step closer (back) to Oracle on OpenVMS.

Recently (like the day before yesterday, recently) I learned a number of new things! One is that the ongoing development OpenVMS will be take up by VMS Software Inc. (VSI).
But, more importantly, they will be creating new versions for mainstream hardware (such as x86)!! Wilm Boerhout of VX Company wrote an announcement about this not too long ago (article in Dutch)!

And now these rumors…

A porting of Oracle on OpenVMS!

Will we once again see the day that systems just won’t go down? Oracle environments with an uptime with like a dozen or two ‘nines’ behind the decimal-mark? Wouldn’t that be something?
Your own VMS server running an Oracle database with Oracle Application Express (APEX)? Wouldn’t that be something else? High time to clear some of your calendar and get (re)acquainted with this super OS!

A very special “thank you” goes to my dear friend Gerrit WoertmanOpenVMS Ambassador, who never seized to remain a link to the VMS World!!

If you are from The Netherlands, please also join Interexperience, to stay close to the game.

Live free or die


Big Data: Hadoop and Oracle technologies explained

MarkRittmanUnder the title “Hadoop and Oracle technologies on BI projects” Mark Rittman flew to The Netherlands on the 14th of July to visit the Oracle Usergroup Holland.

As I had obviously heard a lot about Hadoop, I never really did anything further with it and left it to a synaptic link to Gwen Shapira. This lack of action created a kind of threshold in the understanding of the technology. When I heard about this session I realized this would be the moment to take a step further. It turned out the be the  first real talk that puts “Big Data” in the perspective it needs to be consumable and realistic.

In these current times where “The Internet of Things”, more and more social media and ever further digitization we are heading to a Big Data Disruption. This is both a conceptual as a very real thing if you take a moment to think about it. According to real world experience it is also not something “which will once be”, it is something which is actually here today!

On the technical side of thhadoopings, data is captured in something that is called a “data reservoir” (or “data lake” or “data dump (yard)”). Compared with “regular” data storage, you can conclude that data-governance, or a data-structure, in a Big Data system is applied later  We are used to apply this structure, this governance, beforehand, by applying data definition. Using Hadoop in combination with noSQL give you “schema on read” capabilities making quering of the Hadoop data reservoir possible.

Adding this structure later is harder! This leads to the following:

  • Data is much easier to get into Hadoop then into a star-schema
  • Data is much easier to get out of a star-schema then out of Hadoop

This could be one of the essential things to consider when thinking about engaging in a Big Data project!

As Tanel Poder concluded: “High value, high density data will remain in the Oracle database” which I think is a very true conclusion. In the end, the high value conclusions (or the engineering of Big Data results) will also happen within the Oracle database.

On the horizon is “Oracle Big Data Discovery” which will help with the time consuming and tedious work of sorting and interpreting raw data in the data reservoir. The use of ‘R’, as the data exploration tool of duty, is expected to be replaced by this discovery tooling, over time…

To sum up the concept of the first half of the presentation, to my taste:

  • Hadoop changes business
  • NoSQL scales business
  • Oracle runs business

It takes eons to list all names of the Buddha” nicely sums up the number of different applications that make up and are needed to execute a successful Big Data project.
Plus, “You’d better keep the 13 rules for relational databases close at hand“!

presentation

Part two of the evening was spent on mapping these concepts on actually tools, disclosing data through Hadoop to Oracle SQL and making actual use of Big Data. The exercise was completed by demos and illustrated by screenshots from the slides (link below).
A special word of warning goes out to the security aspect of Big Data, which is something to really pay close attention to. Kerberos authentication and apache Sentry are imperative things to implement in your Big Data environment.

All in all, this evening turned out to be 110% more informative and necessary as I expected when I embarked on the journey to Utrecht! Thank you for sharing, Mark!

Thanks to Piet de Visser for the nice quotes! And a great “hi there” to Klaas-jan Jongsma, René Kuipers and Marti Koppelmans.

If you want to work with Big Data on your Smal(ler) Device, please download the Big data light VM from OTN.

The link to the slides for anyone who wants to review the “extended remix”!

Share your passion

What I have encountered over the past few years, as I got  more and more involved in the Oracle User Community, is the passion to share.
As I have stated in previous posts, it is unbelievable how many smart people are willing to invest private time AND money to travel the world and to share knowledge and experience they have accumulated over the years.

Typically, the annual Oracle User Group events are the place to be to learn from these people. And most often many will submit abstracts of their stories to be elected to be part of these events.

Only, to be able to submit these abstracts, you need to know:
– when these events are
and
– when the so-called CFP (Call for Papers)
is open.

As all Oracle User Groups tend to be independent, this information is hard to come by if you’re not part of the in-crowd, which if, of course, a pity!!
My goal is to collect this data and make it centrally (well, as central as this website is, then) available to whomever is searching for it.

I have made a start.

It’s a small start yet, so:

  • If there are errors, correct me!
  • If information is missing, tell me!

Hope this helps!!

A new form of on-line data protection

In the last few years I have been active with data replication solutions in the Oracle realm as you may know. This data replication field is one that has many angels, so there is something new to learn every day and sometimes there even are really new possibilities!

Take heed…

The first and most familiar form of the data replication forms is ‘physical data replication’, also known as ‘Standby Database‘.
In this form of replication, both source and target database are binary identical. Changes are propagated by copying the archived redo logfile from the source database to the environment for the standby database lives. Most often this is another server, preferably in another building in another town, far enough away to not be struck by the same havoc.

There are basically 3 ways to accomplish this;

  1. Use Oracle Data Guard (in Enterprise Edition Oracle database)
  2. Use Dbvisit Standby (in all Oracle database Editions)
  3. Write your own scripting (not recommended in any case)

The second and more emerging form of data replication is ‘Logical Data Replication’.
In this form of replication, there is not real relationship between the source and the target database, other than that the target database houses data coming from the source database. They can live on different systems, be from different database version, a different operating system or even be from a different vendor.
Data is harvested from the source database, converted and copied over to the target database / system. On the target system this data is being applied, in the native speech of the the target database.

There are a few ways to accomplish this, but basically every vendor has the same technique. It is more a matter of pricing, basically.

  1. Oracle Golden Gate (expensive, complex)
  2. Dell Shareplex (somewhat expensive)
  3. IBM Infosphere (ComPlex, expensive)
  4. Dbvisit Replicate (easy, affordable)

So, having discussed this, as this is not new, why this blogpost?

Well…

A Standby database is more or less closed. You can open it occasionally to query some data, but that interrupts the apply-process.
On-line data replication does what it says, you have an active database, where data is continuously added. This way you can, for example query, the same data on two sources to spread load.

The case I mean to discuss is the following:

“I have 10 source database and I want one target database (ah, presto, on-line data replication) and I want to backup 5 tables from each source to the target database (again, on-line data replication, but wait, backup?) so I can easily copy back specific data to the source (eeeuhm, yes…) whenever a user messes up the source tables (aï…) and I want the target to be update each day at 23:00 (so… okay!)

This reeks after somewhat of a hybrid approach!

We cannot do regular on-line data replication, for this is aimed at being real-time.
And we cannot leverage Standby database, since it needs to be centralized in one database and not 10. Next to that it would take some administration to open up the standby database in read-only mode, take the copy, and close the database again.

Working with Dbvisit, we came up with “Pause Apply” and “Resume Apply”, which we combine to form “Delayed Apply“.
This delayed apply would neatly answer the question posed.

  • By “delaying” the application of changes to the data, we could make sure the requested tables are only updated from 23:00 on;
  • We can combine the 50 tables (10 databases x 5 tables) in one single target database, since it is a logical approach to the matter;
  • We can easily restore or copy back corrupted data, since both the source and the target database remain continuously open.

Using Dbvisit Replicate, having this kind of protection for your “logical test-cases”, what this company was doing to require this solution, is really affordable.
It can help in dynamically and quickly resetting specific data-sets or test-cases while remaining much more flexible than creating scripts to reset a specific data-set or test-case! And, of course, there are many more ways to use this neat feature…

Printing directly with APEX

When looking for a print solution with APEX you will find .PDF

You will find a lot of .PDF

And .PDF is good. There is nothing wrong with .PDF. In fact, .PDF looks cool and you can do a lot of neat stuff with it. With toolkits like pl/pdf you can create .PDF’s directly from PL/SQL.

But sometimes there is the need to be able to print directly.
For instance with batch-processing or with nightly print-runs or whatever. And this is where you would find yourself locked out with .PDF and, glancing Google, you would guess you’d be out of luck!
Since we had:

  • created a web based solution
  • the need to print directly
  • print in nightly-runs

plus we had:

  • about 400 reports (.rdf files) which we need to reuse (without having the opportunity to rebuild them in something like pl/pdf)
  • combine different output / distribution mechanisms

we needed to tackle this challenge!

So we did !!

It was fixed by using some old and new technology mixed together:

Oracle reports builder
and
Oracle Fusion Middleware, more specifically, Oracle Reports Server, aka WLS_Reports

By using this combination of products, you can create a printing solutions which is capable of printing directly to your network printer, create HTML or PDF reports.
Schedule them, e-mail them, and all this by URL-control!

http://<your-reports-server-node>:8888/reports/rwservlet?command=argument&command=argument&and-so-on

Use the following (much used, but far from a complete list of) control-commands:

  • report=<name of your .rdf>
  • userid=<userid/password@database>
  • desformat=HTML/PDF
  • destype=type of output of the report
  • desname=name of your output (device, file, whatever)

More commands in the link to the documentation on the bottom of this post!!

Notes:

  • You can post these parameters to the Reports Server without calling them in the original URL!
  • You can set a “local” on your Reports Server for omitting <@database> in ‘userid’ for your default database
  • Actually you can set all environment variables, like TNS_ADMIN, NLS_LANG, REPORTS_PATH, etc.

What we found is we needed to run Oracle Reports Server on Windows, just to take advantage of the Windows Printing System which is quite stable and easy to configure. (So, yes, okay, there you have it, a good thing about Windoze!)

Basically you can create a simple solution, but you can easily expand it quite a bit, making a printing and reporting solutions worthy of and enterprise environment, with distributing reports via e-mail, creating reports in file-systems, embedding reports in websites, and basically anything you want or would need.

And, you get a nice Management Console for free with this installation!

08-forms-em
Oracle Enterprise Manager Console

From this management console you can administer your print-jobs, set all kinds of parameters, which is quite neat!!

But, wait… the catch… It’s gonna cost you!

Or, can you keep it under control?

But of course!

Printing is mostly a half-on-line thing, and for a lot of stuff, it’s not extremely performance / time critical… So what can we do?

Oracle Reports Server is licensed as “Oracle Forms & Reports Server” and it will set you back € 370 per Named User or € 18.200 per CPU (being Oracle CPU’s according to the Core Factor Table!)
It’s still a whole lot of money, but would you really need more than 2 cores? If you give the machine enough memory and fast disks? Probably not.

Is it worth considering taking another node in your environment? Perhaps. This print-solutions could be a viable reason to do so. It brings you quite a bit of functionality straight from the box. But, as always, do your math and make educated choices.

The documentation link promised:
https://docs.oracle.com/cd/E16764_01/bi.1111/b32121/toc.htm

If you would like more info, please just drop me a line!

Upgrading 11.1 to 11.2 and the time it takes

As many know, Oracle 12 has not reached each and every corner of many production sites. Okay, running Oracle 7 or 8 is becoming tricky…
Moving from anywhere to Oracle 11.2.0.4.0 is still a valid action as it is still the latest and rock-solid stable release out there.

I wanted to share what I ran into, upgrading 11.1.0.7.0 to 11.2.0.4.0 with in-place upgrades (catupgr.sql). As 11.1 to 11.2 is a relatively small step, in quite a few instances we have chosen an in-place upgrade over ‘ye old fashioned export/import with the main reason; saving time and reducing chances on error as you factually stay in the same database.

On the topic of “saving time”… this is what I found…

At some point during the upgrade, tailing the log, I noticed that an unreasonable and unexplainable amount of time (50% of the entire upgrade duration in my example) was spent on just this one statement:

— revoke grant with grant option privs

Note: Contact me for the actual statement…

This got me puzzled to such a state, and since The Internet didn’t hold any of the answers, I decided to turn to MOS and raise an SR.

And after just a few messages to and fro, the issue was found!

The time this bit of program spends, is spend on ORDIM and SDO, better known as Oracle Intermedia and Oracle Spatial. Bringing me to the task (which I knew that answer on already, as a matter of fact) of finding out if both of these technologies were used, and that was easy enough:

connect / as sysdba
— ORDIM
select owner, table_name, column_name
from dba_tab_cols
where data_type in (‘ORDAUDIO’,’ORDDOC’,’ORDIMAGE’,’ORDSOURCE’,’ORDVIDEO’)
order by 1,2,3;
— SDO
select owner,index_name from dba_indexes
where ityp_name = ‘SPATIAL_INDEX’;
select owner, table_name, column_name
from dba_tab_columns
where data_type = ‘SDO_GEOMETRY’
and owner != ‘MDSYS’
order by 1,2,3;

Which in my case returned “no rows selected” as expected.

With this knowledge “in pocket”, it was a matter of removing the unwanted matter

  1. Deinstall Oracle Spatial (SDO) following the steps listed in Note.179472.1.
  2. Deinstall ORDIM per $ORACLE_HOME/ord/im/admin/README.txt, see Note.337415.1.

And do the upgrade in half the time.

Well, hope this helps to save you some time.

Oracle in perspective

A brief overview of alternatives…

This document focuses on the perception of the Oracle database related to ‘Small and Medium businesses’, European Style.
First we will take a quick look at Enterprise licensing and give a ballpark idea of prizes en possibilities. Next I will put this in perspective with more detail and will highlight possibilities to get ‘high end results’ with what is branded as ‘entry level’ investments. Everywhere I say Oracle, I mean the Oracle database.

Oracle is investment intensive
Oracle Enterprise Edition licenses are price-listed for over € 35.000 per processor. These CPU’s actually are not ‘real CPU’S’ but units which are defined according to Oracle’s Core Factor Table.
An Oracle Enterprise Edition license allows you to a) install and use the Oracle Enterprise Edition software and b) buy additional tooling to complete the Enterprise software stack. In this setting there is Oracle Active Data Guard, Oracle Database Vault, Partitioning, etc. to consider.
With Oracle Enterprise Edition it is possible to create a high performance, high available and ‘disaster resistant’ environment. Where it needs to be remarked that this program-set comes with an according price tag.

Oracle Standard Edition environment
A special exception in the Oracle license politics is the Oracle Standard Edition database. This installation uses the exact same database-software (binary compatible) as the Enterprise Edition edition but comprises a significantly reduced set of features and options that can be found in this global overview. The most important question is if these features and options are really needed to realize a high performance, high available and ‘disaster resistant’ environment.
Let’s first quickly zoom into a practical example the indicate an investment-perspective.
Based on a HP Proliant DL380 Gen8 E5-2690v2 Server with 2 processors with each 10 cores.

— Oracle Enterprise Edition:
2 x 10 cores x 0,5 core factor = 10 licenses x € 37,492 = € 374,920 excluding maintenance.
— Oracle Standard Edition:
2 x 1 processor = 2 licenses x € 13,813 = € 27,626 excluding maintenance.
— Oracle Standard Edition One:
2 x 1 processor = 2 licenses x € 4,578 = € 9,156 excluding maintenance.

In this setting we can save up to € 365,764 by leveraging Standard Edition. The reason is that the Standard Edition software is significantly cheaper but mainly because of the fact that the Standard Edition software is licensed per processor socket in stead of by the units defined by the ‘Core Factor Table’!
The limitation is that Standard Edition has a limit of 4 sockets per server and Standard Edition One is limited to 2 sockets per server. This is an important fact!

Room for investment
In our example it is possible to decide in favor of Standard Edition One. What we can subsequently deduce is that we have a theoretical budget of about € 350,000 available to make sure we have a sufficient high performance, high available and ‘disaster resistant’ installation. Even if we were to consume all of this budget, which is not very likely, the return on this investment remains high because the year-by-year support-cost for this environment is ((10 x € 8,248.19) -/- (2 x € 1,007.15)) € 79,467.60 per year cheaper.
In this calculation possible discounts have not been included. Looking at the volume of the investment differences any discounts will have to terminating influence. The year-by-year support-cost will remain based on the original price of the software.

Virtualization
One of the most significant hurdles with leveraging the Oracle software is virtualization, where technical considerations are not the toughest to deal with; the license consequences are!
As we concluded, Oracle Standard Edition is applicable on max 4 processors. In case of virtualization, it is true that all processors of all hardware, where the Oracle database can migrate to, either automatically or with live migration.
With this rule it is nearly impossible to leverage Standard Edition licenses and will is it be nearly impossible to use virtualization in a ‘small to medium business’ setting… Unless a smart alternative is chosen.

Alternatives
1. The abstraction layer
By leveraging virtualization-software as a abstraction layer, a server installation can be separated from the physical hardware configuration on which it runs. By using this alternative it is possible to recover from hardware failure more efficiently.
2. 2 x 2 sockets
By using a limited virtualization-cluster of 2 nodes with 2 sockets each having the maximum possible number of processor cores, the complete advantage of virtualization can be created using the maximum advantage of Standard Edition. Please note that we would need a Standard Edition license. Alternatively you could create a cluster with 2 x 1 socket to facilitate the usage of a Standard Edition One license.
3. ESL
In the case software from a third party is used, this software development party can agree on using a Embedded Software License; from Oracle. This form of licensing is quite specific and is therefor not further discussed here.
4. What will virtualization not solve
Virtualization is not replacement for Backup and it is no alternative for disaster proofing an Oracle database. These specific tasks are resolved by using backup of standby database tooling.

Tooling
In the beginning of this article it is indicated that the Oracle Enterprise Edition software give you the right to buy additional tooling to complete the Enterprise Software installation.
Alternatives for this tooling are also available for Standard Edition installations. Please consider:

  • Dbvisit as an alternative for Oracle Data Guard or Oracle Golden Gate
  • OraSash as an alternative for Oracle Active Session History
  • Nagios or SPS GenSys as alternatives to Oracle Enterprise Manager

Conclusion
Based on the information above we can conclude there are good possibilities to leverage the Oracle Database in a ‘Small and Medium Business’ environment. The information above is no complete and ultimate description of all possibilities, but this quick overview gives enough to work with to zoom into any specific challenge.

OUGF Harmony 2014 #ougf14

ougf

I flew out today to Finland. And I ended up having the opportunity to share my flight with Björn Rost.
The reason this time was to participate in the (meanwhile legendary) Oracle User Group Finland presentation.

After arriving I got picked up by Ann Sjökvist and we travelled to Oracle Finland HQ for a meeting in preparation for the World Premiere of the Oracle Standard Edition Round Table. After this meeting Ann dropped me of at GLO art Hotel, which looks like an ancient castle for her to proceed in the preparations for OUGF14.
A little later I went out on the town with Alex Nuijten to find the rest of the international crew, and we did! We joined forces with some of the biggest names in the Oracle community today like:

we took the legendary Helsinki City Sight Seeing tour by Heli which led us to the Speaker Dinner, where the crew was complete, adding:

and many more!!

Day two, the start of OUGF Harmony 2014, started with a nice stroll down to Helsinki City Center where we met up with all the speakers and participants of the convention to travel down to Haltia Finnish Nature Center.
At this greatest of all venues, you could be for a tech convention, we had breakfast to kickoff the convention by a keynote presentation by Heli Helskyaho.
And this day got to be even better than it started.
The keynote by James Morle  made it clear that opinions matter where IT is concerned. Arguably the best keynote seen in many years! Mindless following of Marketing-ized ideas, best practice and Google will turn you into an idiot instead of a genius.
There were a lot of good session, more than anyone could attend…

The sessions I attended (besides the keynotes) were:

  • Gurcan Orhan: How to handle DEV & TEST & PROD for ODI – with a perfect example of how quickly a smart implementation can become utterly intricated for nothing
  • Björn Rost: The Ins and Outs of Flashback Data archives – explaining the use of flashback even within a Standard Edition environment
  • Keynote: Bryn Llewellyn: PL/ SQL Enhancements in Oracle Database 12c

At 14:00 I had the opportunity to host my own talk on APEX for Electronic Patients Records (if you are interested in the slides, please drop me a line), having a good attendance. The day ended with a nice musical performance of a band formed by Oracle employees while some preferred the sauna experience and a dive in the lake near the venue.

SaunaJumpMost of the participants returned to Helsinki while a small group of heroes stayed on site for a camp close to the venue.

Day three, the second day of the convention started with a keynote by Graham Wood of the Real World Performance Group while the twitter-stream on #ougf14 was revived showing camping and jumping pictures (a tradition initiated by Yuri Velikanov) from the day before!
In preparation of the Oracle Standard Edition awareness campaign I followed the well attended session from Ann Sjökvist talking about the Standard Edition DBA, a different approach to the trade.

SE-RT-OUGF
At 12:00 we kicked off the first ever Oracle Standard Edition Round Table which was quite a succes! In a very lively discussion we got to discuss CPU’s, sockets, tooling, investments, bridging gaps and creating awareness to leverage the #1 database in the world for even many more situations and projects as we are all used to!! We even got to launch a brand new three letter IT acronym “TCL” which represents the Total Cost of (data) Loss for an organization.

With many unique get togethers this day also slowly drawn to a close. On request of Björn Rost the entire remaining gathering of Oracle enthusiasts made The Jump which, for me, was a unique experience, captured by Philippe (we had to jump a few times, yes :-))

OUGF Jump
As often, the best is kept for last! By ways of being the cherry on the cake, during the “hunt for food” by The Pragmas, downtown Helsinki, we stumbled on the Helskyaho-company at Yume (or even Twitter), possibly the best restaurant of Helsinki and they were so good to invite us to join them. This way we got to spend a dinner in the company of the legendary Chris Date which will possibly be a once in a lifetime experience.

Legendary impomptu get togetherWith OUGF Harmony 2014, the standards for Oracle Usergroup conferences again was raised by a magnitude! A great job by our Finnish friends and a promise for next year.

How #Twitter influenced my life.

It was the 31st of August 2009 at exactly 1 PM that I sent out my first tweet and it read “hmmm… Twitter…”
Today we are nearly 5 years on and we are 7,303* tweets along. I have 417 people who are willing to more or less read what I send out, which is just amazing to me!

* As per April 10, 2014 10:54 AM

I just wanted to step back for a second and reflect on what Twitter did for me, and share this reflection with you.
I remember being very hesitant about starting to tweet. The usual arguments like; “Who ever would wanna know that I was taking a coffee” or “Why would anyone want to know what I saw or did”… I decided to give it a little bit of a go, inspired and with a little peer-pressure by @Tom_Schepers.

The beginning was rusty with not much tweeting going on. Finding my way, looking for inspiration on what to communicate.
Somehow it really took off with my visit to the Norwegian Oracle User Group #ougn13. During this event I took it upon me to share a little something of every session I visited or inspiring meetings I encountered.
From that point on, I gradually started tweeting some more, staying in touch with the people that inspired me personally, and somehow, most of them appeared to be in the field of #Oracle.

From here one, I started to “TKnow” a number of people, rather than really know them. This means that there are folks out there I have talked with before I have ever met them in real life. Nice examples include @oraclebase, @ultan or even @pfierens, my partner in crime in many events and fellow member of the #Pragmas.
There are even people I value highly whom I have never even met, like for instance @MathijsBruggink or @annsjokvist.

Getting to the point… how #Twitter influenced my life.
Twitter has given me a way to communicate and stay in touch with friends and peers all over the world. Share serious contemplations, joke around or just socialize.
It has played a part in enabling me to startup an international career, in connecting people and visions in and around IT in general and #Oracle specifically.
Such a simple tool, and for any non believers, please give it a try, it could also change your life.