설문조사
PostgreSQL/PPAS 관련 듣고 싶은 교육은


Powered by EnterpriseDB
총 게시물 59건, 최근 0 건
   

홍이-2) Oracle to PPAS dblink 사용하기

글쓴이 : 홍이 날짜 : 2014-12-09 (화) 19:16 조회 : 4130
웹 검색하다가 찾은 Tip 입니다. ^^;
PPAS 에서  Oracle/PPAS/PostgreSQL로의 DBLink는 해 봤지만,
Oracle에서 PPAS로의 dblink는 다음과 같이하네요 ^^
 
We can create a dblink in oracle to fetch data from PPAS by using the following procedure, 

Add below lines in /etc/odbc.ini 

1. vi /etc/odbc.ini 

[EnterpriseDB] 

Description = EnterpriseDB 

Driver = EnterpriseDB 8.3 

Trace = yes 

TraceFile = /tmp/odbc.log 

Database = edb 

Servername = <Host IP address> 

UserName = enterprisedb 

Password = edb 

Port = 5444 


2. Add the below parameters in the odbcinst.ini file: 

Vi /etc/odbcinst.ini 

[EnterpriseDB 8.3] 
Description=EnterpriseDB ODBC driver for Linux and Windows 
Driver=/opt/PostgresPlus/8.3AS/connectors/odbc/lib/edb-odbc.so 
Debug=0 
CommLog=1 


3. Set the values to the below params in initSID.ora 

Vi initEnterpriseDB.ora 

HS_FDS_CONNECT_INFO = EnterpriseDB #<odbc data_source_name> 
HS_FDS_TRACE_LEVEL = off #<trace_level> 
HS_FDS_SHAREABLE_NAME = /usr/lib/libodbc.so # ODBC specific environment variables 
set ODBCINI= /etc/odbc.ini #<full path name of the odbc initilization file> 

# 
# Environment variables required for the non-Oracle system 
# 
#set <envvar>=<value> #<full path name of odbc driver manager or driver> 

Comment out the “set <envvar>=<value>” 


4. Edit listener.ora and add 

SID_LIST_LISTENER= 
(SID_LIST= 
(SID_DESC= 
(GLOBAL_DBNAME=EnterpriseDB) 
(ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server) 
(SID_NAME=EnterpriseDB)) 
(SID_DESC= 
(SID_NAME=EnterpriseDB) 
(ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server) 
(PROGRAM=hsodbc))) 

5. At last edit the tnsnames.ora and add as follows: 

EnterpriseDB = 
(DEscRIPTION = 
(ADDRESS = (PROTOCOL = TCP)(HOST = <IP address>)(PORT = 1521)) 
(CONNECT_DATA = 
(SID = EnterpriseDB) 
)(HS = OK) 
) 

6. Stop the oracle listener and start again: 

Lisnrctl stop 

Lisnctl start <SID_NAME> 

7. Try to ping the SID with the tnsping by using the following command: 

-bash-3.1$ tnsping EnterpriseDB 

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 03-JUN-2009 07:16:26 

Copyright (c) 1997, 2005, Oracle. All rights reserved. 

Used parameter files: 

Used TNSNAMES adapter to resolve the alias 
Attempting to contact (DEscRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.23.17)(PORT = 1521)) (CONNECT_DATA = (SID = EnterpriseDB))(HS = OK)) 
OK (0 msec) 

8. Now connect to Oracle database and create the dblink by using following syntax: 

SQL>
create public database link <Link name> connect to "<EDB User >" identified by "<DB name>” using '<Data source Name>'; 

Example: 

SQL>
create public database link edb_link connect to "enterprisedb" identified by "edb" using 'EnterpriseDB'; 

Now test the dblink: 


SQL>
select *from "temp"@edb_link; 
a 
---------- 
1 
2 
3 

   

postgresdba.com