Connection to Oracle DB from windows

Minerva · Post by **Minerva** » Mon Aug 01, 2005 6:16 am

Hi,

We are going to migrate DS from HP-UX to Windows 2003.

In the old environment, when creating a Oracle stage we only needed to set a SID parameter because the DB and DS were in the same server.

But now we have a new Windows server who doesn't understand which DB has to connect to.

I guess we have to set another parameter somewhere to define the DB.

Am I right?

Oracle net manager and tnsnames seem to be correct.

Another possibility would be using ODBC but I've heard that OCI is faster. If that is true, why DS only gives a Oracle7 stage? Is any better way to read or write to an Oracle 9 DB?

Thanks a lot for your help!

Minerva

chulett · Post by **chulett** » Mon Aug 01, 2005 7:02 am

As far as I know (not really all that familiar with Windows servers) you still need to supply all of the 'normal' connection information in the stage - SID, user, password. The difference between UNIX and Windows is the latter has no 'dsenv' file to tell it which Oracle client to use. I believe DS takes the default Oracle Home setup on the Server.

Can you 'tnsping' the instance you are trying to connect to from the Windows server?

I'm a little lost on the Oracle7 comment. In your version of DataStage, there is no OCI7 stage as support was dropped for it. You should find both an OCI8 and an OCI9 stage - unless you are up to 7.5.x in which case there is an OCI stage which handles both 9i and 10g.

And yes, stick with native stages like the OCI stages rather than ODBC if at all possible. IMHO.

elavenil · Post by **elavenil** » Mon Aug 01, 2005 7:39 am

SID (Service Name) , the server IP address and port have to be mentioned in the TNSNAMES.ORA file, which would be one of the subfolders of the folder, where oracle installed. This would be sufficient to connect to Oracle using OCI stage. ODBC DSN has to be created if you access Oracle DB using ODBC.

HTWH.

Regards
Saravanan

lstsaur · Post by **lstsaur** » Mon Aug 01, 2005 8:43 am

Hi Minerva,
All you need to do is to install the Oracle Client software on the same DS server resides. After the Oracle Client installed, try to use SQL Plus to connect to the DB. If you cannot connect to the DB, then fix your tnsnames.ora file.

Minerva · Post by **Minerva** » Tue Aug 02, 2005 1:04 am

Thanks to all.

My tnsnames is correct. The problem was that I have several connections with different DBs, different names, different IPs, but the same SID: they are all named "ORCL" !!

In the OCI stage, I only used to define the "Database source name" with the SID name, so I couldn't distinguish between the different connections.

In order to uniquely identify the connection now I used the name of the connection, and it works! Obvious, isn't it? I don't know why we used the SID instead of the conn. name...

Thanks again

Minerva