Обсуждение: [ADMIN] oracle_fdw issues
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 connection for foreign table "oratab1" cannot be established
DETAIL: ORA-12154: TNS:could not resolve the connect identifier specified
Thoughts?
Thanks in advance
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 > > > >
PropAAS DBA wrote:
> 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
Since you are setting ORACLE_SID, I assume that you want a local ("bequeath")
connection to an Oracle server running on the same machine.
For that you need to set ORACLE_HOME and ORACLE_SID in the environment
of the PostgreSQL server process.
In addition you have to do the following (as stated in the README):
Foreign server options
----------------------
- dbserver (required)
The Oracle database connection string for the remote database.
This can be in any of the forms that Oracle supports as long as your
Oracle client is configured accordingly.
Set this to an empty string for local ("BEQUEATH") connections.
Yours,
Laurenz Albe