pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow I do from PostgreSQL to Oracle Db Link
3+ messages / 3 participants
[nested] [flat]
* How I do from PostgreSQL to Oracle Db Link
@ 2011-06-04 01:05 ÃÖÀç¿ø <jwchoi@modernlimes.com>
0 siblings, 0 replies; 3+ messages in thread
From: ÃÖÀç¿ø @ 2011-06-04 01:05 UTC (permalink / raw)
To: pgsql-admin
Hi,
I hope creating that Db Link from PostgreSQL to Oracle.
I waw installed unixODBC and ODBC Link.
And I test comleted unixODBC of isql.
See.
----------------------------------------------------------------------------
--------------------------------------
[oracle@localhost ~]$ isql oracle
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
----------------------------------------------------------------------------
--------------------------------------
But PostgreSQL was not connected oracle.
And I wat got this problem..
----------------------------------------------------------------------------
--------------------------------------
opennms=# select odbclink.connect('DSN=ORACLE');
ERROR: odbclink: unsuccessful SQLConnect call: [S1000] [12514]
[[unixODBC][Oracle][ODBC][Ora]ORA-12514: TNS:listener does not currently
know of service requested in connect descripto
----------------------------------------------------------------------------
--------------------------------------
odbc.ini and odbcinst.ini Configuration is that.
[odbc.ini]
----------------------------------------------------------------------------
--------------------------------------
[ORACLE]
Decription=Oracle ODBC
Driver=OracleDriver
Trace=yes
TraceFile=/tmp/odbc_oracle.log
ServerPost=192.168.10.25:1521
UserID=vsmon
Password=dusrnth
----------------------------------------------------------------------------
--------------------------------------
[odbcinst.ini]
----------------------------------------------------------------------------
--------------------------------------
[OracleDriver]
Description = Oracle ODBC Connection Driver
Driver=/opt/instantclient_10_2/libsqora.so.10.1
Debug=0
CommLog=1
----------------------------------------------------------------------------
--------------------------------------
¨ª Oracle Tns Listener Status
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 04-JUN-2011 10:11:39
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=PNPKEY)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 03-JUN-2011 16:31:14
Uptime 0 days 17 hr. 40 min. 24 sec
Trace Level off
Security ON: Local OS Authentication
SNMP ON
Listener Parameter File /home/oracle/ora10g/network/admin/listener.ora
Listener Log File /home/oracle/ora10g/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521))
)
Services Summary...
Service "ORACLE" has 1 instance(s).
Instance "ORACLE", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this
service...
Service "SNIOV2" has 1 instance(s).
Instance "SNIOV2", status READY, has 1 handler(s) for this service...
Service "SNIOV2XDB" has 1 instance(s).
Instance "SNIOV2", status READY, has 1 handler(s) for this service...
Service "SNIOV2_XPT" has 1 instance(s).
Instance "SNIOV2", status READY, has 1 handler(s) for this service...
The command completed successfully
My Personal Computer connected oracle server.
This mean is TNS Listener status is normal.
My problem is what??
Please help me.
Thanks, Jaewon Choi.
Attachments:
image001.jpg (image/jpeg, 9.1K) download
view image
* Re: How I do from PostgreSQL to Oracle Db Link
@ 2011-06-06 12:33 Ray Stell <stellr@cns.vt.edu>
2011-06-06 14:02 ` Re: How I do from PostgreSQL to Oracle Db Link Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Ray Stell @ 2011-06-06 12:33 UTC (permalink / raw)
To: ?????? <jwchoi@modernlimes.com>; +Cc: pgsql-admin
On Sat, Jun 04, 2011 at 10:05:46AM +0900, ?????? wrote:
>
> ERROR: odbclink: unsuccessful SQLConnect call: [S1000] [12514]
>
> [[unixODBC][Oracle][ODBC][Ora]ORA-12514: TNS:listener does not currently
> know of service requested in connect descripto
>
$ oerr ora 12514
12514, 00000, "TNS:listener does not currently know of service requested in connect descriptor"
// *Cause: The listener received a request to establish a connection to a
// database or other service. The connect descriptor received by the listener
// specified a service name for a service (usually a database service)
// that either has not yet dynamically registered with the listener or has
// not been statically configured for the listener. This may be a temporary
// condition such as after the listener has started, but before the database
// instance has registered with the listener.
// *Action:
// - Wait a moment and try to connect a second time.
// - Check which services are currently known by the listener by executing:
// lsnrctl services <listener name>
// - Check that the SERVICE_NAME parameter in the connect descriptor of the
// net service name used specifies a service known by the listener.
// - If an easy connect naming connect identifier was used, check that
// the service name specified is a service known by the listener.
// - Check for an event in the listener.log file.
sounds like you got to the listener, but didn't tell it the correct service name in
the connect string. Did you spell it correctly? You can use the oracle cmd
lsnrctl status on the oracle server to get the service names.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How I do from PostgreSQL to Oracle Db Link
2011-06-06 12:33 Re: How I do from PostgreSQL to Oracle Db Link Ray Stell <stellr@cns.vt.edu>
@ 2011-06-06 14:02 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2011-06-06 14:02 UTC (permalink / raw)
To: Ray Stell <stellr@cns.vt.edu>; +Cc: ?????? <jwchoi@modernlimes.com>; pgsql-admin
Ray Stell <stellr@cns.vt.edu> writes:
> On Sat, Jun 04, 2011 at 10:05:46AM +0900, ?????? wrote:
>> ERROR: odbclink: unsuccessful SQLConnect call: [S1000] [12514]
>>
>> [[unixODBC][Oracle][ODBC][Ora]ORA-12514: TNS:listener does not currently
>> know of service requested in connect descripto
> sounds like you got to the listener, but didn't tell it the correct service name in
> the connect string. Did you spell it correctly? You can use the oracle cmd
> lsnrctl status on the oracle server to get the service names.
I wonder if the OP set up the service description in his personal
~/.odbc.ini file. If so, it would work for him from the command line,
but odbclink running in the postgres server wouldn't see it (unless the
server happened to be running as his userid, which wouldn't be a good
setup). The fix for that would be to put the info in the system-wide
/etc/odbc.ini file.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-06-06 14:02 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-06-04 01:05 How I do from PostgreSQL to Oracle Db Link ÃÖÀç¿ø <jwchoi@modernlimes.com>
2011-06-06 12:33 Re: How I do from PostgreSQL to Oracle Db Link Ray Stell <stellr@cns.vt.edu>
2011-06-06 14:02 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox