Monthly Archives: October 2014

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.


Post #OOW14

Back at San Francisco International. Unfortunately in the knowledge that Alex Nuijten and I (probably amongst others) found out to be standby for this flight. –> During the writing of this post we found out we will be home in time, which is great news in any case.

This time around Oracle Open World has indeed been different. Usually I always like to post a list of people I had, until now, just met on-line. Each and every gathering I went to, there would be a bunch of new people.
I am going to skip this habit because the post would just get too long 🙂 There is a big difference attending a conference of the magnitude of Open World, knowing people or being here as a solitaire visitor, which I did in 2010.

As I wasn’t just a visitor at Open World, I actually had the chance to actively contribute to this technology fest. #RepAttack, as an opportunity to share knowledge about logical data replication. Together with a fine crew of Dbvisit replication specialists!

DataTitansMy colleague Vit Spinka spoke about the evolution of redo logs, which gives a great background to this technological solution. Vit spoke, as did other members of the Dbvisit crew, like Arjen Visser and Mike Donovan.

The major highlight for me personally was to have the ability to host #RepAttack at the Oak Table location. Together with the champions of Delphix and Confio, we occupied the Children’s Creativity Museum Community Lab and explained and taught about different technologies. Our subject being Logical Data Replication (of course).

RepAttack CCM

Un-very-fortunately, I attended Oracle Open World on an exhibitor pass. This meant I got to see no session at all (Okay, except the on or two I snuck into). All the more time to stroll around the exhibition terrain, see the demo-grounds and have lunch with Martin Nash in the sun (which was a very nice experience), especially since Martin persuaded my to go out and hand out the left-over lunch to some homeless people around the Moscone Center. Thus heading the call of Connor Mc’Donald.

Okay, so before I forget… A big thank you to Björn Rost and Henning Voss of Portrix Systems for making the Appreciation Event a night worthy of remembrance! And to my friends at Dbvisit of course, for getting me to San Francisco in the first plance!!

Time flies when you’re having none…

A saying all too true! Having spent too little time with too few of my Oracle friends in the great city of San Francisco, it was again time to fly home… As you saw, in the beginning of this post.

Next stop… #DOAG14!!