Oracle DBLink
Use Oracle DBLink to connect to Canner Enterprise
Before: Using DBLink to connect single source, without optimization
After: Optimized with MPP computing architecture
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.12Username
: cannerPassword
: 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. 7432Database
: 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.
- Use the SQL Name from Canner Enterprise Workspace (e.g.,
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.12Once 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.
5. Establish DB Link and test the connection result
Create DB Link
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.
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.