웹 검색하다가 찾은 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
