{"id":1527,"date":"2016-08-23T14:36:03","date_gmt":"2016-08-23T11:36:03","guid":{"rendered":"http:\/\/www.fatihacar.com\/blog\/?p=1527"},"modified":"2016-10-13T10:28:54","modified_gmt":"2016-10-13T07:28:54","slug":"create-dblink-from-oracle-to-postgresql-database","status":"publish","type":"post","link":"http:\/\/www.fatihacar.com\/blog\/create-dblink-from-oracle-to-postgresql-database\/","title":{"rendered":"Create Dblink From Oracle to PostgreSQL Database"},"content":{"rendered":"

Since fetching data from PostgreSQL database to Oracle database, you can create database link between two database systems. For that you can apply below operations.<\/p>\n

Test Systems<\/strong><\/p>\n

PostgreSQL DB
\nIP : 192.168.10.10
\nOS : Centos 7 x64
\nService : postgresql.service<\/p>\n

Oracle DB
\nIP : 192.168.10.20
\nOS : OEL 6 x64<\/p><\/blockquote>\n

\"dblinkoracletopostgresql\"<\/p>\n

PostgreSQL Side<\/strong><\/p>\n

psql# create user dblinkuser encrypted password ‘dblinkuserpass’;<\/p>\n

psql# grant usage on schema “testschema” to dblinkuser;<\/p>\n

psql# grant select on “testschema”.tbl_test to dblinkuser;<\/p>\n

Add new line to pg_hba.conf<\/p>\n

vi \/pgdir\/data\/pg_hba.conf<\/p>\n

host testdb dblinkuser\t192.168.10.20\/32 md5<\/p>\n

systemctl reload postgresql.service<\/p><\/blockquote>\n

Oracle Side<\/strong><\/p>\n

yum install postgresql-odbc<\/p>\n

vi \/etc\/odbc.ini<\/p>\n

[PG]
\nDescription = PG
\nDriver = \/usr\/lib64\/psqlodbc.so
\nServerName = 192.168.10.10
\nUsername = dblinkuser
\nPassword = dblinkuserpass
\nPort = 5432
\nDatabase = testdb
\n[Default]
\nDriver = \/usr\/lib64\/liboplodbcS.so<\/p>\n

vi $ORACLE_HOME\/hs\/admin\/initPG.ora<\/p>\n

# This is a sample agent init file that contains the HS parameters that are
\n# needed for the Database Gateway for ODBC<\/p>\n

#
\n# HS init parameters
\n#
\nHS_FDS_CONNECT_INFO = PG
\nHS_FDS_TRACE_LEVEL = 0
\nHS_FDS_SHAREABLE_NAME = \/usr\/lib64\/psqlodbc.so
\nHS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P9
\n#
\n# ODBC specific environment variables
\n#
\nset ODBCINI=\/etc\/odbc.ini<\/p>\n

#
\n# ODBC specific environment variables
\n#<\/p>\n

#
\n# Environment variables required for the non-Oracle system
\n#
\n#set =<\/p>\n

vi $ORACLE_HOME\/network\/admin\/tnsnames.ora<\/p>\n

PG =
\n (DESCRIPTION =
\n (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.20)(PORT = 1521))
\n (CONNECT_DATA =
\n (SID = PG)
\n )
\n (HS = OK)
\n )<\/p>\n

vi $GRID_HOME\/network\/admin\/listener.ora<\/p>\n

SID_LIST_LISTENER =
\n (SID_LIST =
\n (SID_DESC =
\n (SID_NAME=PG)
\n (ORACLE_HOME=\/oracle\/db\/11.2.0\/db_home)
\n (PROGRAM=dg4odbc)
\n )
\n )<\/p>\n

lsnrctl reload<\/p>\n

Listener reload can be adacuate to apply changes;but, sometimes not.<\/p>\n

lsnrctl stop
\nlsnrctl start
\nlsnrctl status<\/p><\/blockquote>\n

Create Database Link on Oracle<\/strong><\/p>\n

CREATE DATABASE LINK PG
\n CONNECT TO “dblinkuser”
\n IDENTIFIED BY ‘dblinkuserpass’
\n USING ‘PG’;<\/p><\/blockquote>\n

Test <\/strong><\/p>\n

select * from “testschema”.”tbl_test”@PG<\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"

Since fetching data from PostgreSQL database to Oracle database, you can create database link between two database systems. For that you can apply below operations. Test Systems PostgreSQL DB IP : 192.168.10.10 OS : Centos 7 x64 Service : postgresql.service Oracle DB IP : 192.168.10.20 OS : OEL 6 x64 PostgreSQL Side psql# create user…<\/p>\n","protected":false},"author":37,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":true,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[9,10],"tags":[14,32,88],"class_list":["post-1527","post","type-post","status-publish","format-standard","hentry","category-administration","category-administration-postgresql","tag-database-administration","tag-oracle-administration","tag-postgresql-administration"],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p39NFI-oD","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts\/1527","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/users\/37"}],"replies":[{"embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/comments?post=1527"}],"version-history":[{"count":6,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts\/1527\/revisions"}],"predecessor-version":[{"id":1623,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/posts\/1527\/revisions\/1623"}],"wp:attachment":[{"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/media?parent=1527"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/categories?post=1527"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.fatihacar.com\/blog\/wp-json\/wp\/v2\/tags?post=1527"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}