Configuring multiple ORACLE_HOME directory

visvacfirvin · Post by **visvacfirvin** » Fri Oct 31, 2008 8:47 am

Hi,
Is there a way to point Datastage server to multiple ORACLE_HOME. The ORACLE path is currently set in dsenv. We've oracle 9i and 10G. We want two projects to configure two different oracle versions for testing. It is basically migration of Oracle 9i to 10G and we want to test the performace and tune the queries used in jobs accordingly.

Thanks,
Firvin

ray.wurlod · Post by **ray.wurlod** » Fri Oct 31, 2008 9:16 am

Make ORACLE_HOME (and the other Oracle environment variables) project-specific by setting them up in Administrator. You can then add them to jobs as job parameters and override on a per-job basis. You can never have two different values of ORACLE_HOME simultaneously; there is only one ORACLE_HOME variable, after all.

visvacfirvin · Post by **visvacfirvin** » Fri Oct 31, 2008 9:55 am

Thanks a lot Ray. I'll try that.

chulett · Post by **chulett** » Fri Oct 31, 2008 10:00 am

You'll need to setup both ORACLE_HOME and whatever your SHLIB_PATH equivalent is. Lastly, include TNS_ADMIN if you are using that and it's different for the two homes.

visvacfirvin · Post by **visvacfirvin** » Fri Oct 31, 2008 10:05 am

Following are the 5 variables defined in dsenv.

ORACLE_HOME
ORAHOME
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib32....
ORACLE_SID
ORASID

When a job starts, all these will be set by dsenv. Now when I override with project specific settings, what will happen to variables like LD_LIBRARY_PATH. LD_LIBRARY_PATH will already have 9i bin and lib path added. Will the LD_LIBRARY_PATH point to both 9i and 10G lib??

Please clarify.

Thanks,
Firvin

chulett · Post by **chulett** » Fri Oct 31, 2008 10:39 am

It will point to whatever you tell it to point to. Make sure the one you want to use is listed first if you can't arrange for only one or the other to appear.

ps. Never seen a need for ORAHOME, ORACLE_SID or ORASID being set. YMMV, of course.

visvacfirvin · Post by **visvacfirvin** » Fri Oct 31, 2008 1:11 pm

No I'm confused now..

What is meant is..

say the dsenv has

ORACLE_HOME="/oracle/product/9.2.0"
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib32

Now I goto datastage administrator and set the values as

ORACLE_HOME="/oracle/product/10.2.0"
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib32

will the value of LD_LIBRARY_PATH become

$LD_LIBRARY_PATH:/oracle/product/9.2.0/lib32:oracle/product/10.2.0/lib32

Will the 9i as well as 10G lib path be included in the LD_LIBRARY_PATH???

And regarding ORAHOME, ORACLE_SID or ORASID I don't know why they were set. I was also wondering before.

chulett · Post by **chulett** » Fri Oct 31, 2008 2:45 pm

visvacfirvin wrote:Now I goto datastage administrator and set the values as

ORACLE_HOME="/oracle/product/10.2.0"
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib32

will the value of LD_LIBRARY_PATH become

$LD_LIBRARY_PATH:/oracle/product/9.2.0/lib32:oracle/product/10.2.0/lib32

Will the 9i as well as 10G lib path be included in the LD_LIBRARY_PATH???

Basically yes, with a couple of caveats. First, as with all "path" variables, the directories listed there are searched "left to right" and it runs whatever it is looking for from the first directory in the path that it finds it in. So if the 9i libs are first, 10i will use the 9i versions, which doesn't help you. You need to specify one or the other, not both, or be prepared to 'flip them around' as needed. I'd suggest the former.

Secondly, I don't think the Administrator environment variables support multiple levels of expansion. Meaning, I'd suggest you fully expand all environment variables in the environment variable, so no $Anything inside. Besides, it's better that way - echo your existing $LD_LIBRARY_PATH from the command line and set that as the value for ORACLE_HOME_9I variable. Create another for ORACLE_HOME_10I and replace the 9i home location with the 10i location. Then there will be no confusion over what's what or where the what is.

ray.wurlod · Post by **ray.wurlod** » Fri Oct 31, 2008 2:46 pm

Make that "beginning to end". Some languages work right to left.

chulett · Post by **chulett** » Fri Oct 31, 2008 2:56 pm

This doesn't, but point taken.

Left to right, beginning to end, front to back, wind to water, ying to yang - however you want to phrase it.

visvacfirvin · Post by **visvacfirvin** » Fri Oct 31, 2008 3:20 pm

Thanks a lot Craig & Ray. I'll try those.