近日做一个POC测试的时候遇到的问题，记录一下。

    通过oracle_fdw连接oracle环境，前期创建extension，创建fdw server和user mapping均正常，但是在尝试查询外部表的时候，报了这个错误：

    1. [antdb@db104 ~]$ psql -p 5432
    2. psql (12.3)
    3. Type "help" for help.
    5. antdb=# select * from guqi_test_ftb ;
    6. ERROR:  error connecting to Oracle: OCIEnvCreate failed to create environment handle
    7. DETAIL:
    8. antdb=# select pg_backend_pid();
    9.  pg_backend_pid
    10. ----------------
    11.           40562
    12. (1 row)
    14. antdb=# select * from guqi_test_ftb ;
    15. ERROR:  error connecting to Oracle: OCIEnvCreate failed to create environment handle
    16. DETAIL:

    从错误消息的本身看，像是环境变量有问题，但是在外部通过sqlplus是能够正常访问Oracle server的。于是打算strace看下报错时的系统调用情况

    1. [root@db104 lib]# strace -p 40562
    2. strace: Process 40562 attached
    3. epoll_wait(3, [{EPOLLIN, {u32=0, u64=0}}], 1, -1) = 1
    4. recvfrom(10, "Q\0\0\0\"select * from guqi_test_ftb"..., 8192, 0, NULL, NULL) = 35
    5. openat(AT_FDCWD, "/home/antdb/oracle/instantclient_12_2/oracore/zoneinfo", O_RDONLY|O_NONBLOCK|O_DIRECTORY|O_CLOEXEC) = -1 ENOENT (No such file or directory)
    6. openat(AT_FDCWD, "/home/antdb/oracle/instantclient_12_2/oracore/zoneinfo", O_RDONLY|O_NONBLOCK|O_DIRECTORY|O_CLOEXEC) = -1 ENOENT (No such file or directory)
    7. getrlimit(RLIMIT_STACK, {rlim_cur=10240*1024, rlim_max=10240*1024}) = 0
    8. ...
    9. close(63)                               = 0
    10. munmap(0x7f9370637000, 4096)            = 0
    11. getrlimit(RLIMIT_STACK, {rlim_cur=10240*1024, rlim_max=10240*1024}) = 0
    12. open("/home/antdb/oracle/instantclient_12_2/rdbms/mesg/ocius.msb", O_RDONLY) = -1 ENOENT (No such file or directory)
    13. open("/home/antdb/oracle/instantclient_12_2/rdbms/mesg/ocius.msb", O_RDONLY) = -1 ENOENT (No such file or directory)
    14. brk(NULL)                               = 0x23a7000
    15. brk(0x23cc000)                          = 0x23cc000
    16. times({tms_utime=1, tms_stime=1, tms_cutime=0, tms_cstime=0}) = 2884253683
    17. rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
    18. write(2, "\0\0\376\0r\236\0\0T2021-03-22 19:05:27.716"..., 263) = 263
    19. sendto(10, "E\0\0\0\215SERROR\0VERROR\0CHV00N\0Merror"..., 142, 0, NULL, 0) = 142
    20. rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
    21. sendto(9, "\2\0\0\0(\0\0\0\10@\0\0\0\0\0\0\0\0\0\0\1\0\0\0\0\0\0\0\0\0\0\0"..., 40, 0, NULL, 0) = 40
    22. sendto(10, "Z\0\0\0\5I", 6, 0, NULL, 0) = 6
    23. recvfrom(10, 0xdf20a0, 8192, 0, NULL, NULL) = -1 EAGAIN (Resource temporarily unavailable)
    24. epoll_wait(3, ^Cstrace: Process 40562 detached
    25.  <detached ...>

    看了这个问题大概就清楚了，oracle客户端的安装路径里面似乎少了一些东西：zoneinfo和ocius.msb。

    这个客户端环境是我直接拷贝的现成的环境，并没有从头开始安装，偷了个懒。

    从oracle官网下载完整的客户端安装文件：http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html
    下载这三个包：

    1. instantclient-basic-linux.x64-11.2.0.4.0.zip
    2. instantclient-sdk-linux.x64-11.2.0.4.0.zip
    3. instantclient-sqlplus-linux.x64-11.2.0.4.0.zip

    解压到指定路径，然后配好.bashrc里的ORACLE_HOME环境变量，重启数据库，问题得解。