oracle 64位客户端_PG连接Oracle报错解决

在postgresql中通过dblink查询oracle表报错：

postgres=# select * from t1; ERROR: error connecting to Oracle: OCIEnvCreate failed to create environment handle

这个问题需要通过strace才能发现，首先执行PG_BACKEND_PID()函数，可以返当前会话的服务器进程PID。

postgres=# select * from t1; ERROR: error connecting to Oracle: OCIEnvCreate failed to create environment handle DETAIL: postgres=# postgres=# SELECT pg_backend_pid(); pg_backend_pid ---------------- 97376

通过strace另开启一个会话追踪97376。

epoll_wait(3, [{EPOLLIN, {u32=41936840, u64=41936840}}], 1, -1) = 1 recvfrom(10, "Q\0\0\0\26select * from t1;\0", 8192, 0, NULL, NULL) = 23 openat(AT_FDCWD, "/app/instantclient/oracore/zoneinfo", O_RDONLY|O_NONBLOCK|O_DIRECTORY|O_CLOEXEC) = -1 ENOENT (No such file or directory) openat(AT_FDCWD, "/app/instantclient/oracore/zoneinfo", O_RDONLY|O_NONBLOCK|O_DIRECTORY|O_CLOEXEC) = -1 ENOENT (No such file or directory) get_mempolicy(NULL, NULL, 0, NULL, 0)   = 0 open("/proc/self/status", O_RDONLY)     = 65 fstat(65, {st_mode=S_IFREG|0444, st_size=0, ...}) = 0 mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2acdea8c6000 read(65, "Name:\tpostgres\nUmask:\t0077\nState"..., 1024) = 1024 read(65, ",00000000,00000000,00000000,0000"..., 1024) = 208 close(65)                              = 0 munmap(0x2acdea8c6000, 4096)            = 0 getrlimit(RLIMIT_STACK, {rlim_cur=RLIM64_INFINITY, rlim_max=RLIM64_INFINITY}) = 0 open("/proc/sys/kernel/shmmax", O_RDONLY) = 65 fstat(65, {st_mode=S_IFREG|0644, st_size=0, ...}) = 0 mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2acdea8c6000 read(65, "2147483648\n", 1024)          = 11 close(65)                              = 0 munmap(0x2acdea8c6000, 4096)            = 0 get_mempolicy(NULL, NULL, 0, NULL, 0)   = 0 open("/proc/self/status", O_RDONLY)     = 65 fstat(65, {st_mode=S_IFREG|0444, st_size=0, ...}) = 0 mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2acdea8c6000 read(65, "Name:\tpostgres\nUmask:\t0077\nState"..., 1024) = 1024 read(65, ",00000000,00000000,00000000,0000"..., 1024) = 209 close(65)                              = 0 munmap(0x2acdea8c6000, 4096)            = 0 open("/proc/meminfo", O_RDONLY)         = 65 fstat(65, {st_mode=S_IFREG|0444, st_size=0, ...}) = 0 mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2acdea8c6000 read(65, "MemTotal:       527439000 kB\nMem"..., 1024) = 1024 read(65, "ages_Total:       0\nHugePages_Fr"..., 1024) = 208 close(65)                              = 0 munmap(0x2acdea8c6000, 4096)            = 0 open("/app/instantclient/rdbms/mesg/ocius.msb", O_RDONLY) = -1 ENOENT (No such file or directory) open("/app/instantclient/rdbms/mesg/ocius.msb", O_RDONLY) = -1 ENOENT (No such file or directory) mmap(NULL, 176128, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2ad6398e7000 brk(NULL)                              = 0x298c000 brk(0x29ad000)                         = 0x29ad000 times(NULL)                            = 524110648 rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0 write(2, "\0\0p\1`|\1\0t2020-09-21 15:00:44.604"..., 377) = 377 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\0275\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, 0xd21740, 8192, 0, NULL, NULL) = -1 EAGAIN (Resource temporarily unavailable)

通过追踪发现，它需要读取文件。

/app/instantclient/rdbms/mesg/ocius.msb

查询安装的客户端，发现该Oracle客户端根本没有mesg等相关目录。

通过再次安装完整版的客户端解决，这里直接通过yum安装oracle18c rpm包。

yum -y localinstall compat-libstdc++-33-3.2.3-72.el7.x86_64.rpm yum -y localinstall oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm yum -y localinstall oracle-database-ee-18c-1.0-1.x86_64.rpm

安装完成客户端之后，一定要注意下列步骤

1.加载新的Oracle客户端环境变量，主要是ORACLE_HOME和LD_LIBRARY_PATH。

export ORACLE_HOME=/opt/oracle/product/18c/dbhome_1 export LD_LIBRARY_PATH="$LD_LIBRARY_PATH:$POSTGRES_HOME/lib:$POSTGRES_HOME/lib/postgresql:$POSTGRES_HOME/plugin:$ORACLE_HOME/lib"

2.重启Postgresql数据库，这一点很重要。

pg_ctl stop -D /app/xxx/data pg_ctl start -D /app/xxx/data

3.使用正确的环境变量，再次安装oracle_fdw。

su - postgres unzip oracle_fdw-2.2.1.zip cd oracle_fdw-2.2.1 make make install psql create extension oracle_fdw

上述三个步骤操作完成之后再次查询就能够使用dblink访问Oracle数据库。

该问题主要参考了oracle_fdw作者的github相关文章

https://github.com/laurenz/oracle_fdw/issues/307

而插件作者已指出，有很多可能的原因导致此问题。基本上都是Oracle配置问题。

• Oracle环境变量的问题 https://github.com/laurenz/oracle_fdw/issues/305

• Oracle安装混乱导致的问题

https://github.com/laurenz/oracle_fdw/issues/263

• 版本兼容性问题，在postgresql中使用了不兼容的oracle_fdw二进制文件

https://github.com/laurenz/oracle_fdw/issues/218

• 文件系统权限问题

https://github.com/laurenz/oracle_fdw/issues/133