foreign table(oracle link) 조회 시 OCIEnvCreate failed 에러 발생

postgres 에서 oracle 테이블 조회를 위해 foregin table 생성 후 조회 시 "error connecting to Oracle: OCIEnvCreate failed to create environment handle" 에러 발생하며 실패


oracle_fdw extension 설치 확인

postgres=# select * from pg_catalog.pg_available_extensions where name = 'oracle_fdw' ;
    name    | default_version | installed_version |                comment                 
------------+-----------------+-------------------+----------------------------------------
 oracle_fdw | 1.1             | 1.1               | foreign data wrapper for Oracle access
(1 row)

FOREIGN TABLE 생성을 위한 사전 작업

-- server 생성
postgres=# create server ora11g foreign data wrapper oracle_fdw options(dbserver '//192.168.56.51:1521/ORADB');
CREATE SERVER
-- server 사용 권한 부여
postgres=# grant usage on foreign server ora11g to sk; 
GRANT
-- postres 유저와 oracle 유저 mapping
postgres=# create user mapping for sk server ora11g options(user 'skdba', password 'oracle');
CREATE USER MAPPING

sk 유저로 접속 후 foreign table 생성

postgres=> create foreign table ora11g_emp (
empno numeric,
ename text,
job text,
mgr numeric,
hiredate date,
sal numeric,
comm numeric,
deptno numeric
) server ora11g options(schema 'SKDBA', table 'EMP');
CREATE FOREIGN TABLE

FOREIGN TABLE 생성 확인

postgres=# select * from information_schema.tables
where table_type like 'FOREIGN%'
;
 table_catalog | table_schema | table_name | table_type | self_referencing_column_name | reference_generation | user_defined_type_catalog | user_defined_type_schema | user_defined_type_name | is_insertable_int
o | is_typed | commit_action 
---------------+--------------+------------+------------+------------------------------+----------------------+---------------------------+--------------------------+------------------------+------------------
--+----------+---------------
 postgres      | public       | ora11g_emp | FOREIGN    |                              |                      |                           |                          |                        | YES              
  | NO       | 
(1 row)

조회 시 에러 발생

postgres=> select * from ora11g_emp ;
ERROR:  error connecting to Oracle: OCIEnvCreate failed to create environment handle
DETAIL:

 

• 조치 사항

.bash_profile 에 TNS_ADMIN 환경변수 추가

export ORACLE_HOME=/ORA19/app/oracle/product/19.0.0/db_1
export LD_LIBRARY_PATH=LD_LIBRARY_PATH:/ORA19/app/oracle/product/19.0.0/db_1/lib:/lib:/usr/lib
export TNS_ADMIN=$ORACLE_HOME/network/admin
export PATH=$PATH:$HOME/bin:/usr/pgsql-11/bin:$ORACLE_HOME/bin

tnsnames.ora 설정 확인

[postgres@svr1:/ORA19/app/oracle/product/19.0.0/db_1/network/admin]$ cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /ORA19/app/oracle/product/19.0.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORADB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.51)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oradb)
    )
  )

sqlplus 사용하여 오라클 접속 및 테이블 조회 테스트

[postgres@svr1:/home/postgres]$ sqlplus skdba@oradb

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Mar 20 16:34:28 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from skdba.emp ;

     EMPNO ENAME      JOB        MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT     17-NOV-81    5000     10
      7698 BLAKE      MANAGER       7839 01-MAY-81    2850     30
      7782 CLARK      MANAGER       7839 09-JUN-81    2450     10
      7566 JONES      MANAGER       7839 02-APR-81    2975     20
      7902 FORD       ANALYST       7566 03-DEC-81    3000     20
      7369 SMITH      CLERK       7902 17-DEC-80     800     20
      7499 ALLEN      SALESMAN       7698 20-FEB-81    1600        300    30
      7521 WARD       SALESMAN       7698 22-FEB-81    1250        500    30
      7654 MARTIN     SALESMAN       7698 28-SEP-81    1250       1400    30
      7844 TURNER     SALESMAN       7698 08-SEP-81    1500   0    30
      7900 JAMES      CLERK       7698 03-DEC-81     950     30
      7934 MILLER     CLERK       7782 23-JAN-82    1300     10

12 rows selected.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[postgres@svr1:/home/postgres]$

postgresql-pgdg-libs.conf 파일에 $ORACLE_HOME/lib 경로 추가

[root@svr1 admin]# cat /etc/ld.so.conf.d/postgresql-pgdg-libs.conf 
/usr/pgsql-11/lib/
/ORA19/app/oracle/product/19.0.0/db_1/lib

ldconfig 명령 실행 및 postgres 재기동
: /etc/ld.so.conf.d/ 에 .conf 파일을 추가, 수정하거나 LD_LIBRARY_PATH 를 변경하게 된다면 ldconfig를 통해 라이브러리를 다시 설정해야 한다.

[root@svr1 admin]# ldconfig
ldconfig: /ORA19/app/oracle/product/19.0.0/db_1/lib/libsrvm19.so is not a symbolic link

ldconfig: /ORA19/app/oracle/product/19.0.0/db_1/lib/libexpat.so.1 is not a symbolic link

-- postgres stop
[postgres@svr1:/home/postgres]$ pg_ctl stop
waiting for server to shut down.... done
server stopped

-- postgres start
[postgres@svr1:/home/postgres]$ pg_ctl start
waiting for server to start....2022-03-20 16:22:35.611 KST [22042] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2022-03-20 16:22:35.611 KST [22042] LOG:  listening on IPv6 address "::", port 5432
2022-03-20 16:22:35.612 KST [22042] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2022-03-20 16:22:35.614 KST [22042] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-03-20 16:22:35.624 KST [22042] LOG:  redirecting log output to logging collector process
2022-03-20 16:22:35.624 KST [22042] HINT:  Future log output will appear in directory "pg_log".
 done
server started
[postgres@svr1:/home/postgres]$

sk 계정으로 접속하여 오라클 테이블 조회(성공)

[postgres@svr1:/home/postgres]$ psql -U sk -d postgres
Password for user sk: 
psql (11.11)
Type "help" for help.

postgres=> 
postgres=> 
postgres=> 
postgres=> select * from ora11g_emp ;
 empno | ename  |    job    | mgr  |  hiredate  | sal  | comm | deptno 
-------+--------+-----------+------+------------+------+------+--------
  7839 | KING   | PRESIDENT |      | 1981-11-17 | 5000 |      |     10
  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850 |      |     30
  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450 |      |     10
  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975 |      |     20
  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000 |      |     20
  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800 |      |     20
  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600 |  300 |     30
  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250 |  500 |     30
  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250 | 1400 |     30
  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500 |    0 |     30
  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950 |      |     30
  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300 |      |     10
(12 rows)

postgres=>