Re: Unable to create oracle_fdw (foreign data wrapper) extension

On Thu, Sep 15, 2016 at 3:25 PM, Arun Rangarajan <arunrangarajan(at)gmail(dot)com>
wrote:

> I am following the instructions here:
> http://blog.dbi-services.com/connecting-your-postgresql-
> instance-to-an-oracle-database/
> to install Oracle foreign data wrapper, oracle_fdw, on a PostgreSQL server.
>
> ---
> Oracle version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
> - 64bit Production, running on Red Hat Linux 7.2
>
> PostgreSQL version: PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled
> by gcc (Debian 4.7.2-5) 4.7.2, 64-bit, running on Debian 7 (wheezy).
> ---
>
> I was able to install sqlplus and connect from PostgreSQL server to Oracle
> server using sqlplus successfully, so connectivity is not a problem.
>
> But when I try to create the extension, I get the following error:
>
>
---
> postgres=# create extension oracle_fdw;
> server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
> The connection to the server was lost. Attempting reset: Failed.
>

Hmm, odd that it's causing a crash.

Which version of the oracle_fdw and which version of the oracle libraries
are you linked to? Make sure to check 'ldd oracle_fdw.so'

--Scott

> ---
>
> Then I took clues from https://github.com/dalibo/pg_qualstats/issues/1
> and added oracle_fdw to shared_preload_libraries in postgresql.conf like
> this:
>
> shared_preload_libraries = 'oracle_fdw'
>
> but now I can't restart Postgres:
>
> ---
> # service postgresql restart
> [....] Restarting PostgreSQL 9.4 database server: main[....] The
> PostgreSQL server failed to start. Please check the log output:
> t=2016-09-15 11:05:42 PDT d= h= p=23300 a=FATAL: XX000: invalid cache
> ID[FAILt=2016-09-15 11:05:42 PDT d= h= p=23300 a=LOCATION:
> SearchSysCacheList, syscache.c:1219 ... failed!
> failed!
> ---
>
> Looking into /var/log/postgresql/postgresql-9.4-main.log I only see these
> two lines:
>
> t=2016-09-15 11:05:42 PDT d= h= p=23300 a=FATAL: XX000: invalid cache ID:
> 41
> t=2016-09-15 11:05:42 PDT d= h= p=23300 a=LOCATION: SearchSysCacheList,
> syscache.c:1219
>
> Removing oracle_fdw from shared_preload_libraries allows postgres to be
> restarted, so this is the one causing restart to fail.
>
> How to fix this and get the foreign data wrapper working?
>
> Thank you.
>
> ​
>

--
--
Scott Mead
Sr. Architect
*OpenSCG <http://openscg.com>*
http://openscg.com