From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
To: pgsql-admin@postgresql.org
Subject: Re: oracle_fdw issues
Date: Wed, 2 Aug 2017 15:17:44 +1200
Message-ID: <18e8c399-7782-e6f8-4c93-0a5d1d8d8e71@catalyst.net.nz> (raw)
In-Reply-To: <341df494-f543-d4fa-a6b7-e1288b56d66b@propaas.com>
References: <341df494-f543-d4fa-a6b7-e1288b56d66b@propaas.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Hi,
The ORA 12154 makes me suspect that VLCDB is being considered as a TNS
alais - and was not found in any tnsnames.ora that your client env
variables pointed to. So you might need to:
- make a tnsnames.ora if not done so already
- set TNS_ADMIN to point the the dir it is in
Alternatively I think you can use the JDBC style connect descriptor e.g
://hostname/SID in your CREATE SERVER command.
regards
Mark
On 02/08/17 13:58, PropAAS DBA wrote:
>
> Hi All;
>
>
> I'm trying to setup oracle_fdw and I think I'm mostly there but I have
> an issue. Here's what I've done.
>
>
> - both PostgreSQL 9.6 and Oracle v10 running on the same server
>
> As the postgres user I can connect to the Oracle instance like so:
>
> 1) export ORACLE_HOME
>
> 2) export ORACLE_SID
>
> Note: the oracle sid = VLCDB
>
> 3) run sqlplus and when prompted enter system for the user and then
> the passwd
>
>
> So based on the fact I can connect, I did this:
>
>
> 1) downloaded, compiled and installed oracle_fdw
>
> 2) connected to psql and ran this:
>
> - CREATE EXTENSION oracle_fdw;
>
> - CREATE SERVER oradb1 FOREIGN DATA WRAPPER oracle_fdw
> OPTIONS (dbserver 'VLCDB');
>
> -GRANT USAGE ON FOREIGN SERVER oradb1 TO postgres;
>
> - CREATE USER MAPPING FOR postgres SERVER oradb1
> OPTIONS (user 'system', password 'orapwd');
>
>
>
> All of the above commands completed successfully, then I created a
> foreign table which also succeeded:
>
> CREATE FOREIGN TABLE oratab1 (pid int, cname varchar(30), cstatus
> varchar(30), c_ts timestamp with time zone)
>
> SERVER oradb1 options (schema 'CLD', table 'cust_ord_process_status');
>
>
> Now if I run:
>
> IMPORT FOREIGN SCHEMA CLD FROM SERVER oradb1 INTO local_cld_sch;
>
> it hangs for a long time and eventually returns this error
>
> *
> **ERROR: cannot connect to foreign Oracle server**
> **DETAIL: ORA-12154: TNS:could not resolve the connect identifier
> specified*
>
>
>
> Also If I run this (based on the foreign table above)
>
> select * from oratab1 limit 10;
>
>
> it also hangs for a long time, then I get this error
>
> *
> **ERROR: cannot connect**ion for foreign table "oratab1" cannot be
> established
> **DETAIL: ORA-12154: TNS:could not resolve the connect identifier
> specified*
>
> *
> *
>
>
> Thoughts?
>
> Thanks in advance
>
>
>
>
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: mark.kirkwood@catalyst.net.nz
Subject: Re: oracle_fdw issues
In-Reply-To: <18e8c399-7782-e6f8-4c93-0a5d1d8d8e71@catalyst.net.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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