Home > tips > Defining database link by using Oracle Gateway

Defining database link by using Oracle Gateway


For configuring a remote non-Oracle database connections on Oracle gateway,
I followed below steps, hope this helps.

1) Create below file in the gateway, with appropriate values like the example below:
  Beware of the value of X in the file name: init<X>.ora
  X value will later be used in listener.ora file SID_NAME parameter.
  Beware of connect string line: HS_FDS_CONNECT_INFO=10.1.1.1:4100/mysydb1
  Ip,port and database name triple belongs to the remote sybase machine.

Server: Gateway
File name: /u01/app/oracle/product/11.1.0/dg4sybs/admin/initMYSYDB1.ora
  
# This is a sample agent init file that contains the HS parameters that are
# needed for the Transparent Gateway for Sybase
#HS_FDS_TRACE_LEVEL=OFF
HS_FDS_CONNECT_INFO=10.1.1.1:4100/mysydb1
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
HS_FDS_CONNECT_STRING=”EnableSPColumnTypes=2″
HS_FDS_SUPPORT_STATISTICS=FALSE
HS_FDS_TRACE_LEVEL=DEBUG
HS_FDS_TRANSACTION_MODEL=COMMIT_CONFIRM
HS_FDS_TRACE_FILE_NAME = /tmp/dg4sybs_mysydb.trc
#
# Environment variables required for Sybase
#
set SYBASE=/u01/app/sybase
set LIBPATH=/u01/app/sybase/OCS-12_5/lib:/u01/app/sybase:/u01/app/oracle/product/11.1.0/lib
HS_FDS_QUOTE_IDENTIFIER=FALSE

 

2) In the gateway machine (in my example host ip: 10.1.1.200), Enter lines in listener.ora file:
Server: Gateway
File name: /u01/app/oracle/product/11.1.0/network/admin/listener.ora

MYSYDB_1522 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.200) (PORT = 1522))
     )
   )
  )
SID_LIST_MYSYDB_1522 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = MYSYDB1)
      (ORACLE_HOME = /u01/app/oracle/product/11.1.0)
      (PROGRAM = dg4sybs)
      (ENVS=LIBPATH=/u01/app/oracle/product/11.1.0/dg4sybs/driver/lib:/u01/app/oracle/product/11.1.0/lib)
    )
  )

3) Start the service by issueing below command:

lnsrctl start MYSYDB_1522

4) Enter TNS entry to Oracle server where you created dblink.
If it is RAC you must re-enter this information in all nodes.
10.1.1.200 ip belongs to gateway server.

Server: Local DB(s)
File name: /u01/app/product/DB/network/admin/tnsnames.ora

REMOTEDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = tcp)(HOST = 10.1.1.200)(PORT = 1522))
    (CONNECT_DATA =
      (SID = MYSYDB1)
    )
    (HS = OK)
  )
  
 
5) Create dblink in the database side, using the tns entry in previous step:

CREATE PUBLIC DATABASE LINK MYDBLINK
 CONNECT TO “user1”
 IDENTIFIED BY <PWD>
 USING ‘REMOTEDB’;
  
 
Troubleshooting :
1- 
 While testing with select * from dual@MYDBLINK;
 it was giving below error. This was because of a sybase side logon trigger.

 ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
 [Oracle][ODBC Sybase Wire Protocol driver]Socket closed. {08S01}[Oracle][ODBC Sybase Wire Protocol driver]Sybase Network Connection has terminated. If a transaction was in progress it has been aborted. {08S01}[Oracle][ODBC Sybase Wire Protocol driver][Sybase ASE]Execution of login script ‘”sp_adm_check_int_users”‘ failed with last error = 11039. See server errorlog for details.
 {HY000,NativeErr = 1638}[Oracle][ODBC Sybase Wire Protocol driver]File not found ‘.odbc.ini’. {HY000,NativeErr = 1509}
 ORA-02063: preceding 3 lines from MYDBLINK

  
 sp_adm_check_int_users is a logon checking procedure, which authenticates conenctions according to source host ip/hostname.
 
 
2- You can check for any error logs in the following directory.
 
  /u01/app/oracle/product/11.1.0/dg4sybs/log
  
  -rw-r–r–    1 oracle   dba           11012 May 08 11:37 MYSYDB1_agt_65733070.trc
  -rw-r–r–    1 oracle   dba           11012 May 08 11:39 MYSYDB1_agt_59441834.trc
  -rw-r–r–    1 oracle   dba           11012 May 08 15:38 MYSYDB1_agt_54329962.trc
  -rw-r–r–    1 oracle   dba         2113496 May 09 19:05 MYSYDB1_agt_22414304.trc
  -rw-r–r–    1 oracle   dba           18585 May 10 15:46 MYSYDB1_agt_5701974.trc

3- You may get TNS error: ORA-12154: TNS:could not resolve the connect identifier specified
  In my case, it was I forgot to enter the tns entry in other node of the RAC.

Advertisements
  1. June 9, 2014 at 7:18 pm

    I’m really enjoying the design and layout of your website.
    It’s a very easy on the eyes which makes it much more enjoyable for me to come here and visit more often. Did
    you hire out a developer to create your theme? Superb work!

  2. December 24, 2014 at 2:11 am

    Thanks for sharing your thoughts. I truly appreciate your
    efforts and I will be waiting for your next write ups thanks once
    again.

  1. September 18, 2012 at 8:19 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Don Charisma

because anything is possible with Charisma

Carol no Mundo!

Aventuras de uma intercambista a trabalho pelo mundo!

Blog do PHP

Oracle DBA for rookies!

nimaidba

Welcome to the world of Oracle with me....

Tech

News and reviews from the world of gadgets, gear, apps and the web

WordPress.com

WordPress.com is the best place for your personal blog or business site.

Gurcan Orhan's ODI and DWH Blog

Some words about Oracle Data Integrator and Data Warehousing.

%d bloggers like this: