oracle怎么连接pg的端口,PG用oracle_fdw连接oracle报错

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

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

[antdb@db104 ~]$ psql -p 5432

psql (12.3)

Type "help" for help.

antdb=# select * from guqi_test_ftb ;

ERROR: error connecting to Oracle: OCIEnvCreate failed to create environment handle

DETAIL:

antdb=# select pg_backend_pid();

pg_backend_pid

----------------

40562

(1 row)

antdb=# select * from guqi_test_ftb ;

ERROR: error connecting to Oracle: OCIEnvCreate failed to create environment handle

DETAIL:

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

[root@db104 lib]# strace -p 40562

strace: Process 40562 attached

epoll_wait(3, [{EPOLLIN, {u32=0, u64=0}}], 1, -1) = 1

recvfrom(10, "Q\0\0\0\"select * from guqi_test_ftb"..., 8192, 0, NULL, NULL) = 35

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)

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)

getrlimit(RLIMIT_STACK, {rlim_cur=10240*1024, rlim_max=10240*1024}) = 0

...

close(63) = 0

munmap(0x7f9370637000, 4096) = 0

getrlimit(RLIMIT_STACK, {rlim_cur=10240*1024, rlim_max=10240*1024}) = 0

open("/home/antdb/oracle/instantclient_12_2/rdbms/mesg/ocius.msb", O_RDONLY) = -1 ENOENT (No such file or directory)

open("/home/antdb/oracle/instantclient_12_2/rdbms/mesg/ocius.msb", O_RDONLY) = -1 ENOENT (No such file or directory)

brk(NULL) = 0x23a7000

brk(0x23cc000) = 0x23cc000

times({tms_utime=1, tms_stime=1, tms_cutime=0, tms_cstime=0}) = 2884253683

rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0

write(2, "\0\0\376\0r\236\0\0T2021-03-22 19:05:27.716"..., 263) = 263

sendto(10, "E\0\0\0\215SERROR\0VERROR\0CHV00N\0Merror"..., 142, 0, NULL, 0) = 142

rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0

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

sendto(10, "Z\0\0\0\5I", 6, 0, NULL, 0) = 6

recvfrom(10, 0xdf20a0, 8192, 0, NULL, NULL) = -1 EAGAIN (Resource temporarily unavailable)

epoll_wait(3, ^Cstrace: Process 40562 detached

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

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

instantclient-basic-linux.x64-11.2.0.4.0.zip

instantclient-sdk-linux.x64-11.2.0.4.0.zip

instantclient-sqlplus-linux.x64-11.2.0.4.0.zip

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