Tạo Dblink Từ Oracle Tới PostgreSQL Database

Trang chủ » Db Link Là Gì » Tạo Dblink Từ Oracle Tới PostgreSQL Database

Có thể bạn quan tâm

DB Test:

PostgreSQL DBIP : 192.168.10.10OS : Centos 7 x64Service : postgresql.service

Oracle DBIP : 192.168.10.20OS : OEL 6 x64

dblinkoracletopostgresql

PostgreSQL

psql# create user dblinkuser encrypted password ‘dblinkuserpass’;

psql# grant usage on schema “testschema” to dblinkuser;

psql# grant select on “testschema”.tbl_test to dblinkuser;

Add new line to pg_hba.conf

vi /pgdir/data/pg_hba.conf

host testdb dblinkuser 192.168.10.20/32 md5

systemctl reload postgresql.service

Oracle

yum install postgresql-odbc

vi /etc/odbc.ini

[PG]Description = PGDriver = /usr/lib64/psqlodbc.soServerName = 192.168.10.10Username = dblinkuserPassword = dblinkuserpassPort = 5432Database = testdb[Default]Driver = /usr/lib64/liboplodbcS.so

vi $ORACLE_HOME/hs/admin/initPG.ora

# 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 = PGHS_FDS_TRACE_LEVEL = 0HS_FDS_SHAREABLE_NAME = /usr/lib64/psqlodbc.soHS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P9## ODBC specific environment variables#set ODBCINI=/etc/odbc.ini

## ODBC specific environment variables#

## Environment variables required for the non-Oracle system##set =

vi $ORACLE_HOME/network/admin/tnsnames.ora

PG =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.20)(PORT = 1521))(CONNECT_DATA =(SID = PG))(HS = OK))

vi $GRID_HOME/network/admin/listener.ora

SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(SID_NAME=PG)(ORACLE_HOME=/oracle/db/11.2.0/db_home)(PROGRAM=dg4odbc)))

lsnrctl reload

Listener reload can be adacuate to apply changes;but, sometimes not.

lsnrctl stoplsnrctl startlsnrctl status

Tạo Database Link trên Oracle

CREATE DATABASE LINK PGCONNECT TO “dblinkuser”IDENTIFIED BY ‘dblinkuserpass’USING ‘PG’;

Test

select * from “testschema”.”tbl_test”@PG

Từ khóa » Db Link Là Gì