Skip to main content
Version: v2

Oracle DBLink

5_Oracle_dblink_before

After: Optimized with MPP computing architecture​

5_Oracle_dblink_after

1. Install PostgreSQL ODBC Drivers on Oracle Server​

Please use the following command to install PostgreSQL ODBC Driver.

yum install postgresql-odbc

2. Edit odbc.ini file on Oracle Server and test DSN connectivity​

Create odbc.ini file in /etc directory.

vi /etc/odbc.ini

Add the following content.

[PG]
Description = PG
Driver = /usr/lib64/psqlodbc.so
ServerName = $server_name
Username = $username
Password = $password
Port = $port
Database = $database
[Default]
Driver = /usr/lib64/libodbcpsqlS.so
  • ServerName: use service domain or IP e.g. 192.168.0.12
  • Username: canner
  • Password: the password of the data source user
    • Create a Personal Access Token used in PostgreSQL wire protocol in Canner Enterprise, and use it as a password. About Personal Access Token reference document
  • Port: Datasource's port e.g. 7432
  • Database: database name
    • Use the SQL Name from Canner Enterprise Workspace (e.g., wire_protocol_test_84077). The SQL Name location is in the Workspace. Click the Share button and switch to the PostgreSQL Wire Protocol page. 1_oracle_dblink

Once complete, use the following command to verify DSN connectivity to the Database

isql -v pg

If the connection is successful, we will see the following results.

+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+

3. Create initPG.ora file​

Create initPG.ora under the $ORACLE_HOME/hs/admin directory, and add the following parameters.

cd $ORACLE_HOME/hs/admin
vi initPG.ora

Parameter content

# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
#
#HS init parameters
#
HS_FDS_CONNECT_INFO = PG
HS_FDS_TRACE_LEVEL = 4
HS_FDS_TRACE_FILE_NAME=/tmp/ora_hs_trace.log
HS_FDS_SHAREABLE_NAME = /usr/lib64/psqlodbc.so
HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P9
#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini
#
# Environment variables required for the non-Oracle system
#

4. Configure tnsnames.ora & listener.ora files​

Configure tnsnames.ora​

Execute the following command

vi $ORACLE_HOME/admin/ORCLCDB/tnsnames.ora

Add the following content.

PG =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = $your_oracle_host)(PORT = 1521))
     )
     (CONNECT_DATA =
       (SID = PG)
     )
     (HS = OK)
   )
  • HOST: Use your Service Domain or IP e.g., 192.168.0.12
  • Since we use PostgreSQL as the remote data source, set PG as SID

Configure listener.ora​

Before editing, we need to stop the Oracle Listener, and we can find the command in $ORACLE_HOME/bin

 lsnrctl stop

Add the following content.

SID_LIST_LISTENER=
(SID_LIST =
   (SID_DESC =
    (PROGRAM = dg4odbc)
    (SID_NAME = PG)
    (ORACLE_HOME = $oracle_home)
   )
)
LISTENER=
(DESCRIPTION_LIST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = $host)(PORT = 1521))
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
  )
)

  • ORACLE_HOME: use home path e.g /u01/app/oracle/product/19.3.0/db_1

  • Host: use Service Domain or IP e.g. 192.168.0.12

    Once complete, restart the Oracle Listener

lsnrctl start

After startup, we can see the following log.

Services Summary...
Service "PG" has 1 instance(s).
 Instance "PG", status UNKNOWN, has 1 handler(s) for this service...  
The command was completed successfully.

Execute the following command.

CREATE PUBLIC DATABASE LINK PG_DBLINK CONNECT TO $username IDENTIFIED BY $secret USING 'PG';
  • PG_DBLINK: Can replace or use another name
  • $username: canner
  • $secret: use the Personal Access Token created in the previous steps

Verify connection result​

Confirm that we have successfully read the data on Canner Enterprise Workspace through Oracle DB Link using the following command.

select * from "foo1"@PG_DBLINK;

Take the foo1 table on Canner Workspace as an example. This is the data content.

2_oracle_dblink

Use a familiar IDE to create a DB Link according to the above instructions, connect to the foo1 table on Canner Enterprise, and verify that the data is successfully connected.

4_oracle_dblink