TheBonsai's Blog

About the days and nights of TheBonsai

A UNIX pathname is a UNIX pathname…or not? ORACLE fooled me again!

May 5th, 2009 by TheBonsai

For a new system on SLES10SP2 I wanted to operate 3 separate instances (ASM, 2 databases) from 3 separate ORACLE_HOMEs. The theory isn’t that complex, so I simply installed and patched up the 3 different ORACLE_HOMEs.

After configuring the ASM instance with the creation assistant and enabling the instance in /etc/oratab, I did a reboot. Finally I wrote a small shell function db_change() to change ORACLE_HOME, ORACLE_SID and PATH on the fly to operate on the different instances.

Nothing. I wasn’t able to connect with sqlplus. I verified the instance “+ASM” still was running, but I couldn’t connect. I checked my ORACLE_SID and ORACLE_HOME, but I couldn’t connect. I even called a friend of mine (thanks Martin!) to make sure I have no conceptual mistake. Nothing. Sqlplus always said “connected to an idle instance” – as if no ASM instance was running at all (or as if I had a typo in the SID).

Since the +ASM instance started fine during system startup, and started fine using the orarun-initscript from SuSE, something had to smell in my setup. After 30 minutes of checking all corners, I decided to compare my current process environment to the process environment of the started ASM PMON (thanks to Linux’ /proc/ filesystem and some shell vodoo, it’s not a big deal to get a good diff).

After eliminating all the unimportant differences, one was left:

ORACLE_HOME=/opt/oracle/product/10.2/asm_1
ORACLE_HOME=/opt/oracle/product/10.2/asm_1/

See it? Believe it or not, that was it! My shell function set an ORACLE_HOME with a trailing slash, and that drove sqlplus crazy! This may be documented somewhere, I didn’t look, but it’s definitely not something intuitive. Finally, that automatically means that things may fail if you put pathes with trailing slash in your /etc/oratab, too.

O.R.A.C.L.E!!!

Applies to the setup:

  • SLES10SP2
  • Oracle 10.2.0.4 w/ recommendec patchset 4

Have a nice day, and better don’t trust your common sense! Comments welcome, ’cause I’m sure this is a well known behaviour and I’m just too stupid :)

(and yes, I’ll rewrite my small shell function to actually parse /etc/oratab!)

This entry was posted on Tuesday, May 5th, 2009 at 18:26 and is filed under english, Oracle. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 responses about “A UNIX pathname is a UNIX pathname…or not? ORACLE fooled me again!”

  1. Martin Klier said:

    Another thing you may not want to be fooled: A domain name is not a domain name! I’m talking about DB_DOMAIN:

    If it contains a “-” (dash), you might experience lots of issues in connection with database links. The DB_DOMAIN becomes part of the DB link name (but you are allowed to skip it within the same DB).

    Ok, now the dash is not allowed to be part of a database name, but the database link’s name is checked against the same rules sometimes! In my case, some APEX select list messed around with it, while all reports accepted it flawlessly.

    Take care
    Usn

  2. TheBonsai said:

    Ha, the issue was explained to me, and it’s reasonable.

    Oracle seems to calculate the shared segment hash from exactly ORACLE_HOME and ORACLE_SID. This is why the exact string matters.

    Though, in my opinion, they could normalize the path string before doing so, to avoid this side effect.

    This mechanism also is the key to run different instances from the same OH using the same SID, if you really want to make a DBA crazy :-)

    Thanks to @martinbergx!

Leave a Reply