Top 60 Oracle Blogs

Recent comments

June 2011

5 days left to vote for sessions at Oracle OpenWorld 2011 on Oracle Mix

Oracle has opened Suggest-a-Session on Oracle Mix. Suggest-a-Session is your opportunity to both submit your own presentation ideas as well as vote on the sessions that interest you. I’ve submitted 4 sessions. To vote on them simply click on the link:

Primary session:

others I posted

Also check out the presentations in Suggest-a-Session from Pythian

Sessions from illustrious Oaktable members

Riyaj Shamsudeen

Frits Hoogland

Randolf Geist

Marco Gralike

Alex Gorbachev

Richard Foote

Current top sessions can  be viewed with this impressive widget

for more information on the widget, check out

#E0E0E0;" src="" alt="" width="594" height="182" />

The “XFILES APEX Community Edition” (XACE) is available for download

For all those to see and learn what you can do when combining the power of Oracle XMLDB and Oracle APEX an alternative APEX XFILES application is now available for download via It is based on the combined efforts of Mark Drake and Carl Backstrom to convert the XMLDB XFILES demo application towards APEX. This “XFILES APEX Community Edition”, XACE for short to make a distinction with the more sophisticated official XFILES XMLDB demo application, demonstrates an implementation of versioning based on DBMS_XDB_VERSION and APEX as UI.

Also for Roel Hartman and me, its an exercise to demonstrate what you can learn while “standing on the shoulders of giants”. Also, in the spirit of Carl Backstrom, we want to share our knowledge with the comminity and give “it” back in the hope you will also get excited of these two very powerful options in the Oracle database.

We use this XACE application to help us with our presentation to demonstrate APEX versioning so if you are interested and have the chance see us (and ask questions afterwards) during Kaleidoscope 2011 or (shameless plug here) vote for us on Oracle mix so we are able to present these techniques on Oracle Open World this year as well (“XFILES, The APEX 4 version – The truth is in there“).

The more important below…

Download the XFILES XMLDB source via: (among others webservices, geo location app, version control and more)
You can also download the OTN Developer Days Virtualbox environment to play with a fully installed XMLDB XFILES appl. (example 3 of the “Oracle By Example” XMLDB series).

Last but not least…

If you like it, in the light of the community

  • share it
  • learn from it
  • participate (and help to make it better)

…and if you really like the effort done, by the community, donate some of your bucks on “Carl’s Memorial Fund” ! (more info here: or under the “donate” link of

Hope you have some fun with it.

On behalve of…


Block size

I knew that failing to have a db_Nk_cache_size setting for your database could cause a statement to crash when it tried to address an object (or tablespace) using a non-standard block size, reporting errors like:

    ORA-29339: tablespace block size 16384 does not match configured block sizes”
    ORA-00379: no free buffers available in buffer pool DEFAULT for block size 16K

Here’s an interesting variation on the theme, reported in a note on the OTN database forum. Note particularly the ORA-603 and ORA-604 that wrap the ORA-379; and that the user states that the problem cache is the standard block size for the database. Unfortunately we never saw a resolution to this thread – perhaps it was simply a case of a cache that was too small when the database got very busy.

Footnote: a database can fail to open if it needs to do recovery in a tablespace for which there is no buffer set. Of course this is only likely to happen if you’re running with an init.ora file and have created a non-standard cache with ‘alter system’ calls while the database was previously up. Here’s an extract from an alert log showing the type of report you get:

Fri May 20 17:58:38 2011
Beginning crash recovery of 1 threads
 parallel recovery started with 2 processes
Started redo scan
Completed redo scan
 374 redo blocks read, 98 data blocks need recovery
Fri May 20 17:58:40 2011
Slave exiting with ORA-379 exception
Errors in file c:\oracle\diag\rdbms\d11g\d11g\trace\d11g_p000_2056.trc:
ORA-00379: no free buffers available in buffer pool  for block size 16K
Aborting crash recovery due to slave death, attempting serial crash recovery
Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
 374 redo blocks read, 98 data blocks need recovery
Aborting crash recovery due to error 379
Errors in file c:\oracle\diag\rdbms\d11g\d11g\trace\d11g_ora_3536.trc:
ORA-00379: no free buffers available in buffer pool  for block size 16K
ORA-379 signalled during: ALTER DATABASE OPEN...

Real-World Performance Videos on YouTube – OLTP

In addition, here are some OLTP demos that demonstrate how much performance and throughput can be wasted by poor design and suboptimal database programming.

OLTP Performance – The Trouble with Parsing

width="640" height="510" src="" frameborder="0" allowfullscreen>

OLTP Performance – Concurrent Mid-Tier Connections

width="640" height="510" src="" frameborder="0" allowfullscreen>

Real-World Performance Videos on YouTube – Data Warehousing

Here are some videos of a data warehouse demo that the Real-World Performance Group has been running for a while now and we thought it was time to put them on YouTube. Hope you find them informative.

Migrate a 1TB Data warehouse in 20 Minutes (Part 1)

width="640" height="510" src="" frameborder="0" allowfullscreen>

Migrate a 1TB Data warehouse in 20 Minutes (Part 2)

width="640" height="510" src="" frameborder="0" allowfullscreen>

Migrate a 1TB Data warehouse in 20 Minutes (Part 3)

width="640" height="510" src="" frameborder="0" allowfullscreen>

Migrate a 1TB Data warehouse in 20 Minutes (Part 4)

width="640" height="510" src="" frameborder="0" allowfullscreen>

Open VPN for the Road Warrior

As a consultant it is important to have a test lab, something which is your own, where you can play with new versions and concepts to your heart’s delight without disturbing anyone else. Or worse, causing problems for the customer. For this reason I like to have an Internet facing machine which I can connect to from anywhere. In case the corporate network doesn’t let you out, consider getting mobile broadband on a PAYG basis-it works a dream!

I have blogged about my system a number of times, with special emphasis on RHEL 5.x and 6.x. Unlike many other Oracle scientists I do not use Virtual Box or VMWare for virtualisation, but rather Xen. When I started looking at para-virtualisation I looked at Oracle VM but at the time it was lacking features I wanted such as iSCSI provided storage. OpenSuSE is a great distribution which offers a dom0 kernel out of the box, and this is what I went for. My lab can support a four node cluster plus two Grid Control domUs, which is more than enough for me to work with. And although it’s busy, it doesn’t make working with the machine impossible.

For a long time I was very happy with my machine, and SSH access was all I needed. But when moving to Vista/Windows 7 a problem became apparent: I could no longer use port-forwarding to access my samba server on my backup domU. Microsoft added some other software to listen on the required port so I started looking at OpenVPN as a solution. This article assumes that you are familiar with OpenVPN-if you are not then you might want to have a look at the documentation. The howto is a a great starting point:

Actually, the OpenVPN tutorial is just fantastic, and it is all you need to set your clients and server up. What it failed to explain is how to access your machines behind the gateway. My dom0 has a public IP address to which OpenVPN binds. This public IP is assigned to my first network bridge, br0. I have three more virtual bridges in the system, not related to a physical interface. VMWare calls such a network a “host only” network. I am using br1 as the public interface for my domUs, br2 is used as a private interconnect. The final one, br3 is either  the storage network for my iSCSI nodes or-for my systems it serves as an alternative interface to the HAIP resource.

So all in all a reasonable setup I think. The trouble is that I didn’t know how to access my database servers in the br1 subnet. This is another one of those tips that look so obvious, but took literally hours to work out. I am not concerned with the private networks br2 and br3, after all those are not meant to be used anyway. The simplified view on my network is shown in this figure:

The workstation is my laptop-obviously.The figure is not 100% correct-the device openVPN connections go to is called tun0, but it’s related to my br0 device.

First of all, I needed to allow communication from device tun0, to which openVPN listens to, to be forwarded to br1. The following iptables rules do exactly that:

# iptables -I FORWARD -i tun0 -o br1 ACCEPT
# iptables -I FORWARD -i br1 -o tun0 -j ACCEPT

Translated into English these 2 lines instruct the firewall to ACCEPT traffic into the FORWARD chain from device tun0 to br1. In other words route the packet to the internal network. The second line allows traffic from the internal network to traverse the tunnel device tun0.

With this in place, you can instruct openVPN to push routes to the client. In my case this is done in the configuration file used to start the openVPN daemon in server mode. My config file, server.conf, contains this line:

push “route″

When a client connects to the OpenVPN server, this route is automatically added to the local routing table with the correct gateway. Note that on Windows with UAC enabled you have to start OpenVPN as administrator or otherwise it won’t be able to initialise the TAP device properly.

Are we there yet? I’m afraid not.

This is only half the story as I found out the hard way. After what seems like endless troubleshooting routing tables (which were correct all the time!) I found that I had to set a default gateway on the domUs, pointing to the dom0’s address of br1. This is as simple as updating /etc/sysconfig/network-scripts/ifcfg-ethx, and adding this line to it:


Either use your command line skills to add the default route or – like I did – bring the interface down and up again. Again, where gateway is the IP address of the br1 device on the dom0.

Hope this saves you a few hours of troubleshooting.

New Blogger – Andy Colvin

Here’s a link to brand new blog by a very experienced Exadata guy. Andy has done a bunch of Exadata projects over the last year and a half and has done more patching than anyone I know. Should be some interesting info coming from him. His first post is about an X2-8 that he is currently working on (with pictures). And here’s the link to the home page on his blog:

Andy Colvin’s Oracle Blog

Check him out.

Password file maintenance in a Data Guard environment

In a previous posting I wrote about password file maintenance in a clustered ASM and RAC environment. This article raised another question: Is there anything specific about password file maintenance in a Data Guard environment? Yes, updating a password file in a Data Guard environment isn’t as straight forward as one might think. In this […]

Calculate the Distance Between Two Latitude/Longitude Points using Plain SQL

June 13, 2011 (Modified June 14, 2011) A question recently appeared on the Usenet group that oddly made me recall a lesson from a mathematics class that I taught in the early 1990s.  A bit strange how a question related to Oracle Database would trigger such a memory, but it happened.  The question posed [...]

The Best Goal Ever !! (Fearless)

Australia (and Canberra specifically) had recently been suffering from two very long and difficult droughts. One had been a severe lack of rain, which left dams at record low levels. After many years, this ended earlier in the year with rain aplenty and with local dams at long last back at 100% capacity. The other drought however [...]