Category Archives: Oracle tips

Introducing FETCHER in a running replication process


This is no regular bit of work and it will probably (and hopefully) never hit you in a production setup…

The prerequisite is that you know how on-line data replication in general, and Dbvisit Replicate specifically, work.

The following case is true:
I had half of a replication pair running.
It means that the MINE process was running, converting REDO-log in PLOG-format. The APPLY process had not yet started because the target database was still being prepared.

dbvisit-replicate-logical-replication-made-easy-18-638-300x225The reason for this is that we needed to start converting redo-log information to PLOG information while we were setting up the target environment. The reason for that was that the setup (exporting source, copying dump to target and importing) was taking quite a bit of time, which would impact redo-log storage to heavily in this specific situation.

It was my suspicion that the MINE process was unable to get enough CPU-cycles from the production server to actually MINE more redo-log seconds than wall-clock seconds passed. In effect, for every second of redo-log information that was mined, between 1 and 6 seconds passed.

This means that the replication is lagging behind and will never be able to catch up.

To resolve this, the plan was to take the MINE process of the production server and placed on an extra server. On the production server, a process called FETCHER would be introduced. The task of this process is to act as a broker between the database and the MIN process, forwarding the requested on-line an archived redo log files.

Normally (!) you would use the nifty opportunities that Replicate offers with the setup wizard and just create a new setup. And actually, this is what I used to figure out this setup. And, if you can, please do use this…

Why didn’t I then, you would rightfully ask?

Well… The instantiation process would take to long, and did I say we were under time-pressure?

  • Setup wizard, 5 minutes
  • The famous *-all.sh script, ~ 1 hr.
  • Datapump Export, ~ 10 hrs.
  • Copy from DC old to DC new,  ~ 36 hrs.
  • Datapump Import, ~ 10 hrs.

So, totally we could spend 57:05 hrs. to try to fix this on the go…

Okay, here we go:

Note: cst-migration is the name of the replication project as you specified it in setup wizard when setting up Replication.

TIP: When setting up on-line replication, it is worth your effort to create separate tnsnames.ora entries for your project, like ‘repl-source’ and ‘repl-target’ acros all nodes.
It can get hellishly confusing if you have, as in this case, a database that is called <cst> and is called the same on the source and target server!

1. Step one:
We obviously had the ./cst-migration/config directory from our basic setup with just MINE & APPLY. This directory holds (among others) the ./cst-migration/config/cst-migration-ontime.ddc file. This file holds the Dbvisit Replicate Repository contents that is needed to run the processes.

From this setup, MINE is actually running. We actually concluded the fact that we were not catching up from this process.

2. Step two:
Now we run dbvrep -> setup wizard again and create a Replicate setup directory with FETCHER and isolate the ./cst-migration+fetcher/config/cst-migration+fetcher-onetime.ddc.

By comparing the two files, I was able to note the differences and therewith conclude the changes necessary to introduce a FETCHER process. It is a meticulous job to make sure all the paths on all the three servers are correct, that port numbers are correct and that all the individual steps are take in the right order. This is the overview.

Having these changes, it is all downhill from now.

3. Step three:
Using the Dbvisit Replicate console, the new entries and the changes were made to the DDC-information stored in the Replicate repository. You can enter these manually or execute your change-file by executing @<change-file-name> inside the console.

4. Step four:
Create the ./cst-migration directory on the system you will use for the relocated MINE process and copy the cst-migration-MINE.ddc and cst-migration-run-source-node.sh in this directory.
Rename the cst-migration-run-source-node.sh to cst-migration-run-mine-node.sh to reduce confusion.
Make sure that the paths mentioned in the cst-migration-MINE.ddc are correct for the system you are starting it on!

NOTE: Please make sure that you can reach both the source and the target database from this node using the tnsnames-entries you have created for the replication setup.

5. Step five:
Rename the cst-migration-MINE.ddc on the source node (!) to cst-migration-FETCHER.ddc and change the cst-migration-run-source-node.sh file to start the FETCHER process in stead of MINE process.

You are now ready to start your new replication processes!

NOTE: If you are running APPLY already, there are some additional things you need to be aware of.

Although it was not the case when I came across this challenge, I am happy to say that Dbvisit have verified and accepted this solutions as a supported action.

Hope this helps.


Oracle Standard Edition 2, a bright new future

Okay, it is not very much more than smoke, since Ludovico Caldara found MOS note 2027072.1 about the support of Standard Edition 12.1.0.2.0 and blogged and tweeted about it.

Despite Ludovico’s disclaimer, there is, nevertheless, some smoke… And Twitter quite quickly filled up (at least the early where I take interest). Dominic Giles stated: “More to come soon!” And Ann Sjökvist urged calmness by saying: “let’s wait for facts!” And of course she is right.

Why then this blogpost?

As one of the founders of the Oracle Standard Edition Round Table (#orclSERT), this interests me. Standard Edition One comprises the most cost effective software stack around. For a deeper dive on that statement, please visit an orclSERT session at an Oracle Usergroup event near you, or drop me a line.
Obviously some kind of news of this category has been long expected as the development of “ever more cores per socket” kept increasing. We have been eagerly awaiting this for a few years actually, hesitant to speak about it… For obvious reasons 😉

Where there is smoke, there is fire, especially when PM’s speak. And as the note states that the release of SE2 is foreseen for Q3 of 2015 (which coincidently is THIS quarter) I would like to prepare myself…

This is what we have to go by for now:

Beginning with the release of Oracle Database 12.1.0.2 Standard Edition 2 (SE2), Standard Edition (SE) and Standard Edition One (SE1) are replaced by Standard Edition 2. SE2 will run on systems with up to 2 sockets and will have the ability to support a two node RAC cluster. 12.1.0.1 was the final edition that we will produce for SE and SE1. Customers running SE or SE1 will need to migrate their licenses to SE2 to be able to upgrade to 12.1.0.2.

1. SE2 will run on systems with up to 2 sockets.
– This is not different from the current SE1 rule;
– This means that a 4 socket SE installation will have to be revised, either migrate up to EE or revamp to 2 sockets;

2. SE2 will have the ability to run a 2 node cluster.
– RAC will become available across the board in the Standard Edition realm.
– How many sockets will a full SE2 cluster be able to support? 2 sockets, if you would follow current rules, 4 sockets if the license would be optimal!

And it is always good to speculate about price… And mind you, this is smoke! The best educated guess so far, 3/4 between SE1 and SE, which could (hopefully) bring the price to round about 10k euro per socket, but… who knows? Perhaps, as Ludovico also stated, socket licensing could become history?

The great news is, Oracle Standard Edition will remain available as the alternative to the Enterprise Edition installations. For more information we will just have to hold our breath a little longer.
But, be assured, during the next session of #orclSERT we will be able to tell you (much) more!

Meanwhile I will keep preparing (and talking about it), for this change will have some impact yet…

Using a terminal emulator on Mac

Dumb title for a blog post? No, not really I guess…

ZOC Terminal emulatorI have been using a terminal emulator, basically ever since I got away from the VT100 terminal:

  • ICE.TCP Pro
  • KEAVT
  • Reflection ‘X’

And a few other obscure applications that I cannot even recall anymore.
Currently, and over the last 6 to 8 years, I have been using ZOC.

The background of this story is: In the beginning these were the first DOS PC’s and later some Windows based machines that needed to interface with (in my case) VAX VMS, and later with the other UNIXes.

But why use a terminal emulator on MAC, for crying out loud? I hear you think… OS X is a Unix, so it should be all native, right?
Wrong! Well, kind of…

There are so many small (and bigger) differences when using various systems that it pays off to have a program that allows you to tune into these differences. Nothing more annoying than a backspace key that does not work or key-combinations that act different than you would expect.
This is even more true when you work with a mix of different operating systems, Solaris, HP UX, Oracle Linux, perhaps even some IBM OS’s.
And for when you would like to have further tune-ability of you toolkit, ranging from colors, to sizes, from fonts to layout.  Frivolities? Perhaps, but if you spent a lot of your time everyday in such a  tool, it does make a difference.

Printer terminal, also a terminal emulatorMore importantly are configurable logging, for documentation and troubleshooting. You can regard this as the modern variation to the old school print terminal (who can remember those?)
Configuring transfer types, modem and commutation settings and keeping these organized. As well as password storage and administrative support.

Well, basically, this is why I use a terminal emulator on my MAC!
And I think I found a valid tool in ZOC, by Markus Schmidt. Please check it out ZOC

Well, I hope you get to enjoy your terminal work as much as I do!

Updating SQL Developer to use newer Java version

I was being teased by SQL Developer.

Everytime I started it came nagging about that it was being forced to live in an old Java version called jdk1.7.0_45 and that is was not feeling happy about it.
So, I should remedy this, I thought to myself.

First visit was, inspired by some search-work on the WWW, a file called product.conf. Which offered two possiblities:

java

SetJavaHome to some logical location
or
SetJavaHome to nothing, and then SQL Developer would kindly ask me to point it to somwhere to live.

Well… no. My SQL Developer refused it all and just started with this jdk 1.7.

Same hack done in another file on another location, a file called sqldeveloper.conf.
Same result.

Freshly downloaded SQL Developer, put in place… No help!

Erm…

Rename
drwxr-xr-x  3 root  wheel  102 Jan  6  2014 jdk1.7.0_45.jdk
in /Library/Java/JavaVirtualMachines
to
drwxr-xr-x  3 root  wheel  102 Jan  6  2014 xxx-jdk1.7.0_45.jdk

Nope! Still the same nagging…

What now?

In the end, I wound up with one of Jeff Smits’s helpers.
This guy aksed me to “start SQL Developer from the commandline”. Right, but how?

So I finally found:
/Applications/SQLDeveloper.app/Contents/MacOS/sqldeveloper.sh

And that did start SQL Developer from the command-line…

But… wait… an .sh-file!! Interesting!!

And, behold… in this .sh-file lies the answer:sqldev_startup1

So the file reads:
export JAVA_HOME=`/usr/libexec/java_home -v 1.7`
Which I hacked to:
export JAVA_HOME=`/usr/libexec/java_home -v 1.8`

And, presto, error-message gone and SQL Developer now happily lives in Java 8.

Hope this helps somebody out!!

Setting up SQL Instant Client on MAC

In doing more work directly from my Macbook Air, I ran into a situation where native connectivity to an Oracle environment was needed.Connectivity over Oracle Instant Client
From experience I have always been a big fan of the Full Oracle client, just because it comes with a lot of tools and utilities for troubleshooting, which makes the actual experience a bit more pleasant.
Looking & asking around, though, I learned fairly quickly that this client is just not available for Mac OSX… Thanks to Osama Mustafa for confirming.

So, a fact, although quite a number of IT pro’s are working with Mac!

This leaves no other choise than to divert to the Oracle Instant Client 11, which then, indeed, is just an 11g Instant Client (11.2.0.4)!
It would humor me if Oracle were to bring out a 12c Full Client for Mac, as well as an instant client, if someone would so desire.
To have some more tooling around the client, I downloaded all the packages including at least SQL*Plus.

Though the install process is relatively straight forward (download the archives and unzip them in place) getting SQL*Plus to actually run is a somewhat different ballgame!
As usual, when you start a tool, you’re bombarded by messages about unfound dynamic libraries. This set me (very briefly) on a path to place these files where they were expected on my Mac.

In a place like:

/ade/b/2649109290/oracle/sqlplus/lib

for instance, you would need to place a number of these libraries.
This leaves you with the option to populate your system with all these specific libraries, which is of course just fine, but not my choice (think of the mess in ever having to clean up) and especially not when it can be avoided.

A quick search pointed me to this excellent blogpost by Casey Lucas about this exact same issue. With a tool called ‘otool’* applied as suggested, I am now able to run SQL*Plus natively on my Mac without error messages.

* otool – object file displaying tool
If you need it, call it from the command line. It will install this and other development tools on your Mac.

That is nice, but it’s just only over halfway there.

manneke stopt de stekker erin

 

Now I want something where I can just run:
sqlplus <username>@<database>
without intricate connect-strings.

 

 

This leaves one minor “hack”, or rather “edit” required, your .bash_profile needs a bit of a path addition and an environment setting:
alias ll="ls -l"
export TNS_ADMIN=/Applications/instantclient_11_2
export PATH=/Applications/instantclient_11_2:$PATH

Note: the alias was already in there 😉

To top it off, I created a small tnsnames.ora in the directory with the instant client (keeping all related files neatly tucked away together)

xesource =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = 192.168.56.66)
(PORT = 1521)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = xe)
)
)

And voila, goal acquired.

sqlplus usera@xesource
Never specify the password on the command line. Not only will it be shown, it will also be sent (most probably) unencrypted over SQL*Net

OUGN15, The “boat conference” revisited

Jan at shipsport
Reflections on OUGN

Sometimes things in life can change quickly! It is only two years ago that I came to Oslo for the first time to join the Scandinavian Oracle crew on a boat trip to Kiel.
At that time I had never actually participated in this kind of experience and I wasn’t into presenting either. Together with my good friend Philippe Fierens I discovered a whole new world back then. You could have read about these experiences in some blogpost, but this was lost in the move to my own site, sorry!

And this trip couldn’t have been more different though! With three presentations accepted the two days at sea will be a reunion with the friends I made over the last years, as well as a way to contribute to one of the most tight knit tech communities I know. And this will be in a scene that I remember vividly from being a newbie… And this is somewhat strange, believe me

After a quick and pleasant flight I touched down in Oslo, flying from Amsterdam with a decent sized crew of Dutch Oracle enthusiasts, including my good friends Patrick Barel and Alex Nuijten. Waiting in the Oslo airport for Luís Marques, I catches up with Gurcan Orhan, which was a great surprise.
Later that day we found ourselves in the Oslo harbor for the speakers dinner. You can imagine the collective amount of Oracle knowledge packed into that one restaurant!

frits
Enkitek’s Frits Hoogland on Ansible

After a somewhat restless night we arrived, on Thursday morning, at the ship Color Fantasy with the Heli Helskyaho-company, just in time for the keynotes. It was good to see Mark Rittman and James Morle made it on board too. Especially as James was up for the delivery of version 2.0 of his vibrant keynote! Next we proceeded to bring our luggage to our cabins and grab a spot of lunch on the exhibition floor down in the belly of the ship. The setup of the exhibition was quite nice and gave a good opportunity to mix and mingle.
The afternoon was spent on sessions, where I visited Frits Hoogland with the Ansible talk, and preparation for my own session at 18:00. This is the last run of this APEX presentation, as I have retired it after OUGN15. The slides will be archived here.
After finalizing the preparation for the third edition of the Standard Edition Round Table (aka “slide polishing”) with the #orclSERT team, comprising of Ann Sjökvist, Philippe and myself, it was time for the souree and for diner in the grand restaurant on board. It has been a good first day!

Diner
Dinner with the international crew on board the Color Fantasy.
Gin-tonic
Warm reception at Kiel port.

The second day of OUGN15 started with a multitude of sessions including the third edition of the Oracle Standard Edition Round Table, which was actually quite busy and interactive. We had some good discussions, and that at 09:00, so thank you, everybody.
Of course, as was declared a tradition, Björn Rost was present in the Kiel harbor. With the famous “Basil smash Gin & tonic” and sandwiches we were welcomed on German soil.
My afternoon comprised 3 sessions, starting with my own called “Okay, and now my database server crashed…” which was quite nicely received. Next Alex Nuijten on 12c new features for developer, topped off with Tim Gorman who taught us to be CSI people, in finding issues in the database.
After an enjoyable evening in the various bars and discotheques of the ship we retired the official part of the Oracle User Group Norway Vårseminar 2015, thanking the board and of course especial Øyvind Isene, for their hard work.

If you want to catch up further on the unconference communications surrounding this event, please do checkout the Twitter hashtag #OUGN15. This will also include a great set of snapshots and pictures taken along the way…

Oslo, until the next time!

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…