Обсуждение: How I do from PostgreSQL to Oracle Db Link

Поиск
Список
Период
Сортировка

How I do from PostgreSQL to Oracle Db Link

От
최재원
Дата:

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.

 

 

 

 



새로운 메일이 도착하면, 메신저로 바로 알려줍니다!
가비아 하이웍스 메일 - hiworks.co.kr
Вложения

Re: How I do from PostgreSQL to Oracle Db Link

От
Ray Stell
Дата:
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.

Re: How I do from PostgreSQL to Oracle Db Link

От
Tom Lane
Дата:
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