3

I'm want use Postgresql 9.3 in CentOS 7 and add extention oracle_fdw. but when use this extenstion i get error

OCIEnvCreate failed to create environment handle after installing oracle_fdw.

my roadmap:

  • install postgresql 9.3 from postgres rpm.
  • install oracle instant client 11.2g with rpm files from oracle site.
  • install oracle-instant client11.2-basic & oracle-instantclient11.2-sqlplus & oracle-instantclient11.2-devel.

I use this question : OCIEnvCreate failed to create environment handle after installing oracle_fdw

my variables :

/etc/profile :
export PG_HOME=/usr/pgsql-9.3
export ORACLE_HOME=/usr/lib/oracle/11.2/client64
export TNS_ADMIN=$ORACLE_HOME/network/admin
export PATH=$ORACLE_HOME/bin:$PG_HOME/bin/:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME:$PG_HOME/lib:$LD_LIBRARY_PATH

tnsnames.ora :

I created /usr/lib/oracle/11.2/client64/network/admin/tnsnames.ora:

BEH =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.102.107)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
    )
  )

When I use the sqlplus command line, I can connect using sqlplus user/pass@BEH and can select from tables.

Using psql in my Postgres database:

CREATE EXTENSION oracle_fdw; 
      resutlt : Query returned successfully.

CREATE SERVER beh FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver 'AMI_BEH');
   OR
   CREATE SERVER beh FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//172.18.102.107:1521/ORCL');  
      result : Query returned successfully.

CREATE USER MAPPING FOR postgres SERVER beh OPTIONS (user 'ORACLE_USER',password 'ORACLE_PASS');
      result : Query returned successfully.

CREATE FOREIGN TABLE bl
   ("OLD_DYDJ" character varying(20) ,
    "DYDJ" character varying(20) ,
    "MC" character varying(128) )
   SERVER beh
   OPTIONS (table 'DYDJ_DY');
      result : Query returned successfully.

SELECT * FROM bl;
      result : error connecting to Oracle: OCIEnvCreate failed to create environment handle.

I checked that I can use sqlplus as the postgres user (su - postgres) and I can connect to oracle with the sqlplus command.

I found this link http://permalink.gmane.org/gmane.comp.db.postgresql.general/166914 , so I checked:

SELECT pg_backend_pid(); result : 3854
ps -p3854 -oppid=        result : 1902
cat /proc/1902/environ | xargs -0 -n1
   result : 
      PG_GRANDPARENT_PID=1
      USER=postgres
      PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin
      PWD=/
      PGLOCALEDIR=/usr/pgsql-9.3/share/locale
      LANG=en_US.UTF-8
      PGSYSCONFDIR=/etc/sysconfig/pgsql
      SHLVL=1
      HOME=/var/lib/pgsql
      LOGNAME=postgres
      PGDATA=/var/lib/pgsql/9.3/data
      _=/usr/pgsql-9.3/bin/postgres
  • try to execute lsof -p 3854. Theoretically instant client's libclntsh.so should work even without ORACLE_HOME set, but at least you should have LD_LIBRARY_PATH set. Is it possible that your postresql was started via init.d/rc.d script and necessary env variables were not exported? Try to stop postgresql and start it manually from command line. – ibre5041 Mar 2 '15 at 14:59
  • my centos install on minimal mode. i use lsof -p 3854 | grep libcln then my reult is postgres 3854 postgres mem REG 253,1 52761218 67151515 /usr/lib/oracle/11.2/client64/lib/libclntsh.so.11.1 . next i check again my LD_LIBRARY_PATH with echo this my result is : /usr/lib/oracle/11.2/client64/lib:/usr/lib/oracle/11.2/client64:/usr/pgsql-9.3/lib: <br> in centos 7 services start at /etc/systemd/system/multi-user.target.wants/postgresql-9.3.service . @ibre5041 – Mohammad Dastpak Mar 3 '15 at 3:28
1

If you install from an rpm then it's installed as a service.

If you start postgresql from a custom script as user postgres then the process will catch the environment variables but if you start postgresql as a service it won't.

You should look for how to pass environment variables to systemd (or sysv) services.

I used this example for systemd services. CentOS 7 uses systemd.

In /etc/systemd/system create postgresql.service.d directory and place inside a file orafdw.conf with your environment variables and restart (not sure about only reload). Make sure the directory name match your postgresql service name.

Something like...

[Service]
Environment=ORACLE_HOME=/path/to/home
Environment=TNS_ADMIN=/path/to/tnsadmin
...
(All the variables you want)

Regards,

Walter

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.