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

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

> Thanks, Scott.
>
> oracle_fdw version 1.5.0 from http://pgxn.org/dist/oracle_fdw/
>
> Oracle client version: instantclient 12.1
>

I've had problems using anything > instant client 10. Give it a shot.

--Scott

>
> /usr/lib/postgresql/9.4/lib# ldd oracle_fdw.so
> linux-vdso.so.1 => (0x00007fff50744000)
> libclntsh.so.12.1 => /usr/lib/oracle/12.1/client64/lib/libclntsh.so.12.1
> (0x00007f44769f1000)
> libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f4476666000)
> libnnz12.so => /usr/lib/oracle/12.1/client64/lib/libnnz12.so
> (0x00007f4475f4f000)
> libons.so => /usr/lib/oracle/12.1/client64/lib/libons.so
> (0x00007f4475d0b000)
> libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f4475b07000)
> libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f4475884000)
> libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0
> (0x00007f4475668000)
> libnsl.so.1 => /lib/x86_64-linux-gnu/libnsl.so.1 (0x00007f4475450000)
> librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x00007f4475247000)
> libaio.so.1 => /lib/x86_64-linux-gnu/libaio.so.1 (0x00007f4475045000)
> libclntshcore.so.12.1 => /usr/lib/oracle/12.1/client64/lib/libclntshcore.so.12.1
> (0x00007f4474af5000)
> /lib64/ld-linux-x86-64.so.2 (0x00007f447990c000)
>
>
>
> On Thu, Sep 15, 2016 at 1:10 PM, Scott Mead <scottm(at)openscg(dot)com> wrote:
>
>>
>>
>> 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-inst
>>> ance-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
>>
>
>

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