Secure ODBC and OCI in Oracle 9i

Archive of postings to DataStageUsers@Oliver.com. This forum intended only as a reference and cannot be posted to.

Moderators: chulett, rschirm

Locked
admin
Posts: 8720
Joined: Sun Jan 12, 2003 11:26 pm

Post by admin »

Try changing the port number in TNSNAMES.ORA, if that is how you resolve
Oracle names.


-----Original Message-----
From: Shivprasad Rao [mailto:shiv_rao@hotmail.com]
Sent: Friday, 2 May 2003 0:27
To: datastage-users@oliver.com
Subject: Secure ODBC and OCI in Oracle 9i

Hello All,

Secure ODBC feature of oracle 9i makes use of port 2484 instead of 1521. All
the ODBC stages for Oracle works fine when you specify the port numbers in
.odbc.ini, does anyone know how to configure OCI stages? Im getting an
error "unable to open file" when I try to browse the data in one of OCI
stage. We are using oracle 9i on Sun Solaris.

Thanks,
Shiv






**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************
admin
Posts: 8720
Joined: Sun Jan 12, 2003 11:26 pm

Post by admin »

Thanks David, Since Oracle 9i and Datastage resides in the same box i did
change both tnsnames.ora and listner.ora. But still it does not work.

Shiv

>From: David Barham
>Reply-To:
>To: "datastage-users@oliver.com"
>Subject: RE: Secure ODBC and OCI in Oracle 9i
>Date: Fri, 2 May 2003 15:23:59 +1000
>
>Try changing the port number in TNSNAMES.ORA, if that is how you resolve
>Oracle names.
>
>
>-----Original Message-----
>From: Shivprasad Rao [mailto:shiv_rao@hotmail.com]
>Sent: Friday, 2 May 2003 0:27
>To: datastage-users@oliver.com
>Subject: Secure ODBC and OCI in Oracle 9i
>
>Hello All,
>
>Secure ODBC feature of oracle 9i makes use of port 2484 instead of 1521.
>All
>the ODBC stages for Oracle works fine when you specify the port numbers in
>.odbc.ini, does anyone know how to configure OCI stages? Im getting an
>error "unable to open file" when I try to browse the data in one of OCI
>stage. We are using oracle 9i on Sun Solaris.
>
>Thanks,
>Shiv
>
>
>
>
>
>
>**********************************************************************
>This email and any files transmitted with it are confidential and
>intended solely for the use of the individual or entity to whom they
>are addressed. If you have received this email in error please notify
>the system manager.
>
>This footnote also confirms that this email message has been swept by
>MIMEsweeper for the presence of computer viruses.
>
>www.mimesweeper.com
>**********************************************************************
>


_________________________________________________________________
The new MSN 8: smart spam protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail
admin
Posts: 8720
Joined: Sun Jan 12, 2003 11:26 pm

Post by admin »

To be honest, I have never connected to Oracle using TCP with SSL.

To help any further, Id need to see:
* tnsnames.ora
* sqlnet.ora
* listener.ora

and the actual Oracle error message as reported in the DataStage log.

Did you remember to restart the listener (or at least tell it to
reinitialise) after changing the listener.ora?

Can you connect to the database using SQL*Plus on the server?

Im not familiar with DataStage on Unix (yet), so Im not sure where you set
the Oracle environment for it. Someone else might be able to tell you how
to check this.

-----Original Message-----
From: Shivprasad Rao [mailto:shiv_rao@hotmail.com]
Sent: Friday, 2 May 2003 4:26 PM
To: datastage-users@oliver.com
Subject: RE: Secure ODBC and OCI in Oracle 9i

Thanks David, Since Oracle 9i and Datastage resides in the same box i did
change both tnsnames.ora and listner.ora. But still it does not work.

Shiv

>From: David Barham
>Reply-To:
>To: "datastage-users@oliver.com"
>Subject: RE: Secure ODBC and OCI in Oracle 9i
>Date: Fri, 2 May 2003 15:23:59 +1000
>
>Try changing the port number in TNSNAMES.ORA, if that is how you resolve
>Oracle names.
>
>
>-----Original Message-----
>From: Shivprasad Rao [mailto:shiv_rao@hotmail.com]
>Sent: Friday, 2 May 2003 0:27
>To: datastage-users@oliver.com
>Subject: Secure ODBC and OCI in Oracle 9i
>
>Hello All,
>
>Secure ODBC feature of oracle 9i makes use of port 2484 instead of 1521.
>All
>the ODBC stages for Oracle works fine when you specify the port numbers in
>.odbc.ini, does anyone know how to configure OCI stages? Im getting an
>error "unable to open file" when I try to browse the data in one of OCI
>stage. We are using oracle 9i on Sun Solaris.
>
>Thanks,
>Shiv
>
>
>
>
>
>
>**********************************************************************
>This email and any files transmitted with it are confidential and
>intended solely for the use of the individual or entity to whom they
>are addressed. If you have received this email in error please notify
>the system manager.
>
>This footnote also confirms that this email message has been swept by
>MIMEsweeper for the presence of computer viruses.
>
>www.mimesweeper.com
>**********************************************************************
>


_________________________________________________________________
The new MSN 8: smart spam protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail
admin
Posts: 8720
Joined: Sun Jan 12, 2003 11:26 pm

Post by admin »

The Oracle environment in Unix? Its set in your dsenv file. Its all
documented in the readme, but you need to add two things to the dsenv file
and then bounce DataStage:

Your $ORACLE_HOME environment variable.

Add $ORACLE_HOME/lib to your current $LIBRARY_PATH. Note that the exact
name of the library path varies from o/s to o/s. For example, on my Tru64
box it is LD_LIBRARY_PATH.

Shiv may actually have this set correctly if he has been using OCI stages
before this point, and these settings wouldnt effect which port Oracle is
using.

-craig

David Barham wrote:
> To be honest, I have never connected to Oracle using TCP with SSL.
>
> To help any further, Id need to see:
> * tnsnames.ora
> * sqlnet.ora
> * listener.ora
>
> and the actual Oracle error message as reported in the DataStage log.
>
> Did you remember to restart the listener (or at least tell it to
> reinitialise) after changing the listener.ora?
>
> Can you connect to the database using SQL*Plus on the server?
>
> Im not familiar with DataStage on Unix (yet), so Im not sure where
> you set the Oracle environment for it. Someone else might be able to
> tell you how to check this.
>
> -----Original Message-----
> From: Shivprasad Rao [mailto:shiv_rao@hotmail.com]
> Sent: Friday, 2 May 2003 4:26 PM
> To: datastage-users@oliver.com
> Subject: RE: Secure ODBC and OCI in Oracle 9i
>
> Thanks David, Since Oracle 9i and Datastage resides in the same box i
> did change both tnsnames.ora and listner.ora. But still it does not
> work.
>
> Shiv
>
>> From: David Barham
>> Reply-To:
>> To: "datastage-users@oliver.com"
>> Subject: RE: Secure ODBC and OCI in Oracle 9i
>> Date: Fri, 2 May 2003 15:23:59 +1000
>>
>> Try changing the port number in TNSNAMES.ORA, if that is how you
>> resolve Oracle names.
>>
>>
>> -----Original Message-----
>> From: Shivprasad Rao [mailto:shiv_rao@hotmail.com]
>> Sent: Friday, 2 May 2003 0:27
>> To: datastage-users@oliver.com
>> Subject: Secure ODBC and OCI in Oracle 9i
>>
>> Hello All,
>>
>> Secure ODBC feature of oracle 9i makes use of port 2484 instead of
>> 1521. All
>> the ODBC stages for Oracle works fine when you specify the port
>> numbers in .odbc.ini, does anyone know how to configure OCI stages?
>> Im getting an error "unable to open file" when I try to browse the
>> data in one of OCI stage. We are using oracle 9i on Sun Solaris.
>>
>> Thanks,
>> Shiv
admin
Posts: 8720
Joined: Sun Jan 12, 2003 11:26 pm

Post by admin »

Hello David,

Following are the information you needed,

tnsnames.ora
-------------
BIT1_TST.XX.XXX =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCPS)(HOST = XX.XX.XXX)(PORT = 2484))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bit1_tst.XX.XXX)
)
)

SqlNet.ora
------------
# SQLNET.ORA Network Configuration File:
/u01/dworacle/OraHome1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /etc/ORACLE/WALLETS/dworacle)
)
)

SSL_CIPHER_SUITES= (SSL_RSA_EXPORT_WITH_DES40_CBC_SHA,
SSL_RSA_EXPORT_WITH_RC4_40_MD5, SSL_RSA_WITH_NULL_SHA,
SSL_RSA_WITH_NULL_MD5, SSL_DH_anon_EXPORT_WITH_RC4_40_MD5,
SSL_DH_anon_EXPORT_WITH_DES40_CBC_SHA)

TRACE_DIRECTORY_CLIENT = /u01/dworacle/OraHome1/

SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER= (MD5, SHA1)

NAMES.DEFAULT_DOMAIN = XX.XXX

TRACE_DIRECTORY_SERVER = /u01/dworacle/OraHome1/

TRACE_FILE_CLIENT = clientadmin.trc

TRACE_FILE_SERVER = admin.trc

TRACE_LEVEL_CLIENT = ADMIN

SQLNET.RADIUS_AUTHENTICATION = XX.XX.XXX

SQLNET.RADIUS_SEND_ACCOUNTING = ON

# Added 4/16/03 DAC

TRACE_LEVEL_SERVER = ADMIN

SQLNET.ENCRYPTION_TYPES_SERVER= (AES256, RC4_256, AES192, 3DES168, AES128,
RC4_128, 3DES112, RC4_56, RC4_40, DES, DES40)

SQLNET.ENCRYPTION_TYPES_CLIENT= (AES256, RC4_256, AES192, 3DES168, AES128,
RC4_128, 3DES112, RC4_56, DES, RC4_40, DES40)

SSL_CLIENT_AUTHENTICATION = FALSE

SSL_VERSION = 0

SQLNET.CRYPTO_SEED = aaebcfca3f379bafb670be0


listner.ora
-----------
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /etc/ORACLE/WALLETS/dworacle)
)
)

TRACE_FILE_LISTENER = trace

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = XX.XX.XXX)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCPS)(HOST = XX.XX.XXX)(PORT = 2484))
)
)

SID_LIST_LISTENER =
(SID_DESC =
(GLOBAL_DBNAME = bit1_tst.XX.XXX)
(ORACLE_HOME = /u01/dworacle/OraHome1)
(SID_NAME = bittst)
)
)

TRACE_LEVEL_LISTENER = ADMIN

SSL_CLIENT_AUTHENTICATION = FALSE

TRACE_DIRECTORY_LISTENER = /u01/dworacle/OraHome1/network


The error i get is
-------------------
ORA-28759: Failed to open file

yes we did restart the listners, Yes we can connect using sql*plus,

Following is my envirnoment file
--------------------------------

ORACLE_HOME=/u01/dworacle/OraHome1
ORAHOME=/u01/dworacle/OraHome1
TNS_ADMIN=/u01/dstage/tns_admin
LD_LIBRARY_PATH=/.dslibs:$dshome/lib:$ORACLE_HOME/lib32:$ORACLE_HOME/rdbms/lib32:$LD_LIBRARY_PATH

export ORACLE_HOME ORAHOME TNS_ADMIN LD_LIBRARY_PATH


Thank you very much David, I really appreciate.

Shiv

>From: "David Barham"
>Reply-To:
>To:
>Subject: RE: Secure ODBC and OCI in Oracle 9i
>Date: Fri, 2 May 2003 20:03:26 +1000
>
>To be honest, I have never connected to Oracle using TCP with SSL.
>
>To help any further, Id need to see:
>* tnsnames.ora
>* sqlnet.ora
>* listener.ora
>
>and the actual Oracle error message as reported in the DataStage log.
>
>Did you remember to restart the listener (or at least tell it to
>reinitialise) after changing the listener.ora?
>
>Can you connect to the database using SQL*Plus on the server?
>
>Im not familiar with DataStage on Unix (yet), so Im not sure where you
>set
>the Oracle environment for it. Someone else might be able to tell you how
>to check this.
>
>-----Original Message-----
>From: Shivprasad Rao [mailto:shiv_rao@hotmail.com]
>Sent: Friday, 2 May 2003 4:26 PM
>To: datastage-users@oliver.com
>Subject: RE: Secure ODBC and OCI in Oracle 9i
>
>Thanks David, Since Oracle 9i and Datastage resides in the same box i did
>change both tnsnames.ora and listner.ora. But still it does not work.
>
>Shiv
>
> >From: David Barham
> >Reply-To:
> >To: "datastage-users@oliver.com"
> >Subject: RE: Secure ODBC and OCI in Oracle 9i
> >Date: Fri, 2 May 2003 15:23:59 +1000
> >
> >Try changing the port number in TNSNAMES.ORA, if that is how you resolve
> >Oracle names.
> >
> >
> >-----Original Message-----
> >From: Shivprasad Rao [mailto:shiv_rao@hotmail.com]
> >Sent: Friday, 2 May 2003 0:27
> >To: datastage-users@oliver.com
> >Subject: Secure ODBC and OCI in Oracle 9i
> >
> >Hello All,
> >
> >Secure ODBC feature of oracle 9i makes use of port 2484 instead of 1521.
> >All
> >the ODBC stages for Oracle works fine when you specify the port numbers
>in
> >.odbc.ini, does anyone know how to configure OCI stages? Im getting an
> >error "unable to open file" when I try to browse the data in one of OCI
> >stage. We are using oracle 9i on Sun Solaris.
> >
> >Thanks,
> >Shiv
> >
> >
> >
> >
> >
> >
> >**********************************************************************
> >This email and any files transmitted with it are confidential and
> >intended solely for the use of the individual or entity to whom they
> >are addressed. If you have received this email in error please notify
> >the system manager.
> >
> >This footnote also confirms that this email message has been swept by
> >MIMEsweeper for the presence of computer viruses.
> >
> >www.mimesweeper.com
> >**********************************************************************
> >
>
>
>_________________________________________________________________
>The new MSN 8: smart spam protection and 2 months FREE*
>http://join.msn.com/?page=features/junkmail
>


_________________________________________________________________
Help STOP SPAM with the new MSN 8 and get 2 months FREE*
http://join.msn.com/?page=features/junkmail
Locked