PG some process is restart ,oracle_fdw is can't connect to ORACLE. about oracle_fdw HOT 21 CLOSED

Can you explain the problem in more detail?

Can you tell me which SQL statements you are running, the messages you get and – if relevant – what is in the PostgreSQL server log?

from oracle_fdw.

log detail:
ERROR: connection for foreign table "t_btssetinfo" cannot be established
DETAIL: ORA-06413: Connection not open.

I used pgadmin3 execute SQL:select * from t_btssetinfo;

used this SQL run command is OK.

oracle client Release 10.2.0.1.0 server Release 10.2.0.1.0 .

from oracle_fdw.

select * from t_rbgsetinfo;
ERROR: error connecting to Oracle: OCIEnvCreate failed to create environment handle
DETAIL:
@laurenz

from oracle_fdw.

Do I understand correctly that when using "t_btssetinfo" you get the first error message, while with "t_rbgsetinfo" you get the second one?

Could you tell me the following:

• Output from SELECT oracle_diag();
• Type and version of operating system (32-bit or 64-bit?).
• The CREATE SERVER and CREATE FOREIGN TABLE statements used to create the objects.
• The environment variables of the PostgreSQL server process (these are often connected to OCIEnvCreate failures).

from oracle_fdw.

I have a question.
compile in oracle client.
if run PostgreSQL ,need oracle client?
I copy libclntsh.so.11.1 libnnz11.so to /lib64 is OK?
I used oracle 11g client is OK. if I compile in oracle 10g client ,I can't used pgadmin3 select foreign table.
but in pg command ,sometime is OK.
system is 64-bit.

`postgres=# SELECT oracle_diag();

oracle_diag

oracle_fdw 1.3.0, PostgreSQL 9.4.4, Oracle client 10.2.0.1.0, ORACLE_HOME=/opt/apps/oracle/product/10.2.0/db_1
(1 row)
`

CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw
          OPTIONS (dbserver '//192.168.1.149:1521/orcl');


          CREATE USER MAPPING FOR postgres SERVER oradb
          OPTIONS (user 'sangli', password 'sangli');

          CREATE FOREIGN TABLE test (
          id        int          ,
          name  text

       ) SERVER oradb OPTIONS (schema 'SANGLI', table 'TEST'); ```

from oracle_fdw.

export ORACLE_BASE=/opt/apps/oracle
export ORACLE_HOME=/opt/apps/oracle/product/10.2.0/db_1
export LD_LIBRARY_PATH=$PGHOME/lib:$ORACLE_HOME/lib:$LD_LIBRARY_PATH
MANPATH=$MANPATH:/usr/local/pipeline/man

from oracle_fdw.

Yes, you need an Oracle client to run oracle_fdw, and no, copying libclntsh.so.11.1 and libnnz11.so to /lib64 is not enough.

Make sure that these variables are actually set in the environment of your PostgreSQL server process. On Linux (are you using Linux?) this can be done as follows:

cat /proc/<pid>/environ | xargs -0 -n 1

where <pid> is the process ID of the PostgreSQL server process (postmaster).

A good way to test if you have setup Oracle client correctly is to connect with sqlplus as PostgreSQL user.

from oracle_fdw.

you can try used oracle 10g client compile oracle_fdw ,PostgreSQL version is 9.4.4 . used pgadmin3 select this table. you can find this error.

from oracle_fdw.

I have used oracle_fdw with Oracle client 10.2, and I had no problem.

However, I'd recommend that if you really have to use Oracle 10.2 (why?), you use the latest release 10.2.0.5 and not 10.2.0.1, which is full of bugs.

from oracle_fdw.

I can't execute command sqlplus in user postgres.
"-bash: sqlplus: command not found"
system is CentOS 7.x

[pipeline@localhost ~]$ cat /proc/12130/environ | xargs -0 -n 1
HOSTNAME=localhost.localdomain
TERM=xterm
SHELL=/bin/bash
HISTSIZE=1000
PG_GRANDPARENT_PID=5299
USER=pipeline
LS_COLORS=no=00:fi=00:di=00;34:ln=00;36:pi=40;33:so=00;35:bd=40;33;01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;41:ex=00;32:.cmd=00;32:.exe=00;32:.com=00;32:.btm=00;32:.bat=00;32:.sh=00;32:.csh=00;32:.tar=00;31:.tgz=00;31:.arj=00;31:.taz=00;31:.lzh=00;31:.zip=00;31:.z=00;31:.Z=00;31:.gz=00;31:.bz2=00;31:.bz=00;31:.tz=00;31:.rpm=00;31:.cpio=00;31:.jpg=00;35:.gif=00;35:.bmp=00;35:.xbm=00;35:.xpm=00;35:.png=00;35:.tif=00;35:
LD_LIBRARY_PATH=/usr/local/pipeline/lib:/opt/apps/oracle/product/10.2.0/db_1/lib:
ORACLE_BASE=/opt/apps/oracle
PATH=/usr/kerberos/bin:/usr/local/pipeline/bin:/usr/local/bin:/bin:/usr/bin:/home/pipeline/bin:/usr/local/pipeline/bin
MAIL=/var/spool/mail/pipeline
_=/usr/local/pipelinedb/bin/pipeline-server
PWD=/home/pipeline
INPUTRC=/etc/inputrc
LANG=en_US.UTF-8
PGSYSCONFDIR=/usr/local/pipelinedb/etc/pipelinedb
PGHOME=/usr/local/pipeline
SSH_ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass
HOME=/home/pipeline
SHLVL=2
LOGNAME=pipeline
CVS_RSH=ssh
PGDATA=db083
LESSOPEN=|/usr/bin/lesspipe.sh %s
ORACLE_HOME=/opt/apps/oracle/product/10.2.0/db_1
G_BROKEN_FILENAMES=1
[pipeline@localhost ~]$

from oracle_fdw.

yes,I know 10.2.0.1 is so much bugs.
but,my client used this version .
I well download 10.2.0.5 version test it .
thinks.
but now ,I well go to supper.you can give me some info about this bug.
very thinks for you .

from oracle_fdw.

Your environment looks ok from here.

Did you get rid of the copies of the shared libraries in /lib64? You should.

Is this Instant Client or the regular client installed with Oracle Universal Installer?
Install sqlplus, it is really useful for testing and debugging.

I had no specific 10.2.0.1 bug in mind (except maybe 3807408 which causes errors if the path containing Oracle has space characters in it), that was general advice.

I don't know what your specific problem is (trying with sqlplus would narrow that down), but I suspect it has to do with your Oracle configuration.

from oracle_fdw.

I'll be on vacation for the next two weeks, so don't expect regular updates.
I'll try to peek in every now and then.

from oracle_fdw.

OK @laurenz have you a good holiday.
I used oracle 11gR2 client compile oracle_fdw,everything is OK.
I will gdb oracle_fdw ,debug this code,if i find some questions,i will send detail log to you .

from oracle_fdw.

It is fine to connect with Oracle client 11.2 to Oracle server 10.2, in fact I would recommend that.

Thanks for the offer to explore this with a debugger, but since Oracle is not open source, you won't get very far that way.
I still believe that there is something wrong with your Oracle 10.2 client setup; perhaps it accidentally tries to use some files or libraries from your Oracle client 11.2, which would cause problems like what you observe.

If sqlplus is not on the PATH, you'd have to access it using the absolute path.

from oracle_fdw.

maybe,because I add some extension in PG, this extension is my team dev. i think some problem cause oracle_fdw error. I will check .

Why used pgadmin3 can't select foreign table?
error info is connect not open. but i used command is OK.

from oracle_fdw.

If you suspect that some extension causes oracle_fdw to fail, try without and see if that makes a difference.

Are you saying that with Oracle 10.2, you get a different message with psql and with pgAdmin III?
If yes, that is probably the same problem as #19. This has been reported a number of times, but I could never reproduce it, and nobody has ever sent me a trace of the problem.
If you could create a trace as described in #19 and send it to me per e-mail, I would be grateful.

from oracle_fdw.

this problem is oracle client bugs? Oh, my god ... is very bad .

from oracle_fdw.

I am not certain that it is an Oracle bug, but the fact that it only appears in Oracle 10.2 points in that direction.

I'd really like a trace of such a connection attempt so that I can investigate it.

from oracle_fdw.

No need to keep this open, we can reopen it once I get a trace.

from oracle_fdw.

OK

from oracle_fdw.