sqlplus执行错误的问题探究

Bertha 。 2023-03-01 10:17 13阅读 0赞

今天碰到个很诡异的问题，在装了Oracle Clinet的机器，不同路径下执行指令sqlplus，回显不同。

Oracle Client路径是/opt/app/oracle/instantclient\_11\_2，包含如下内容，

![format_png][]

bash\_profile相关配置，

export ORACLE_HOME=/opt/app/oracle/instantclient_11_2
    export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME
    export TNS_ADMIN=$ORACLE_HOME
    export CLASSPATH=$ORACLE_HOME/ojdbc6.jar:./
    export NLS_LANG="AMERICAN_AMERICA.ZHS16GBK"
    export PATH=$PATH:$HOME/.local/bin:$HOME/bin:$SELF_ORACLE_HOME

如果在$ORACLE\_HOME，执行sqlplus，提示错误，再次执行，会出core，

[test@localhost instantclient_11_2]$ cd /opt/app/oracle/instantclient_11_2
    [test@localhost instantclient_11_2]$ sqlplus
    Error 6 initializing SQL*Plus
    SP2-0667: Message file sp1<lang>.msb not found
    SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

如果在其他路径(非$ORACLE\_HOME)，执行sqlplus，回显正常，

[test@localhost instantclient_11_2]$ sqlplus
    SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 22 17:48:07 2020
    Copyright (c) 1982, 2013, Oracle. All rights reserved.
    Enter user-name:

这是为什么？

通过strace发现了其中一些区别，sqlplus执行异常的strace片段，

[test@localhost instantclient_11_2]$ strace sqlplus
    execve("/opt/app/oracle/instantclient_11_2/sqlplus", ["sqlplus"], [/* 29 vars */]) = 0
    brk(NULL) = 0x1b20000
    mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f9cb847c000
    access("/etc/ld.so.preload", R_OK) = -1 ENOENT (No such file or directory)
    open("tls/x86_64/libsqlplus.so", O_RDONLY|O_CLOEXEC) = -1 ENOENT (No such file or directory)
    open("tls/libsqlplus.so", O_RDONLY|O_CLOEXEC) = -1 ENOENT (No such file or directory)
    open("x86_64/libsqlplus.so", O_RDONLY|O_CLOEXEC) = -1 ENOENT (No such file or directory)
    open("libsqlplus.so", O_RDONLY|O_CLOEXEC) = 3
    read(3, "\177ELF\2\1\1\0\0\0\0\0\0\0\0\0\3\0>\0\1\0\0\0@\370\1\0\0\0\0\0"..., 832) = 832
    fstat(3, {st_mode=S_IFREG|0555, st_size=1469542, ...}) = 0
    getcwd("/opt/app/oracle/instantclient_11_2", 128) = 54

sqlplus执行正常的strace片段，

[test@localhost instantclient_11_2]$ strace sqlplus
    execve("/opt/app/oracle/instantclient_11_2/sqlplus", ["sqlplus"], [/* 28 vars */]) = 0
    brk(NULL) = 0x16ec000
    mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7fcd72b26000
    access("/etc/ld.so.preload", R_OK) = -1 ENOENT (No such file or directory)
    open("tls/x86_64/libsqlplus.so", O_RDONLY|O_CLOEXEC) = -1 ENOENT (No such file or directory)
    open("tls/libsqlplus.so", O_RDONLY|O_CLOEXEC) = -1 ENOENT (No such file or directory)
    open("x86_64/libsqlplus.so", O_RDONLY|O_CLOEXEC) = -1 ENOENT (No such file or directory)
    open("libsqlplus.so", O_RDONLY|O_CLOEXEC) = -1 ENOENT (No such file or directory)
    open("/opt/app/oracle/instantclient_11_2/tls/x86_64/libsqlplus.so", O_RDONLY|O_CLOEXEC) = -1 ENOENT (No such file or directory)
    stat("/opt/app/oracle/instantclient_11_2/tls/x86_64", 0x7fff93de3060) = -1 ENOENT (No such file or directory)
    open("/opt/app/oracle/instantclient_11_2/tls/libsqlplus.so", O_RDONLY|O_CLOEXEC) = -1 ENOENT (No such file or directory)
    stat("/opt/app/oracle/instantclient_11_2/tls", 0x7fff93de3060) = -1 ENOENT (No such file or directory)
    open("/opt/app/oracle/instantclient_11_2/x86_64/libsqlplus.so", O_RDONLY|O_CLOEXEC) = -1 ENOENT (No such file or directory)
    stat("/opt/app/oracle/instantclient_11_2/x86_64", 0x7fff93de3060) = -1 ENOENT (No such file or directory)
    open("/opt/app/oracle/instantclient_11_2/libsqlplus.so", O_RDONLY|O_CLOEXEC) = 3
    read(3, "\177ELF\2\1\1\0\0\0\0\0\0\0\0\0\3\0>\0\1\0\0\0@\370\1\0\0\0\0\0"..., 832) = 832
    fstat(3, {st_mode=S_IFREG|0555, st_size=1469542, ...}) = 0
    mmap(NULL, 1985056, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 3, 0) = 0x7fcd72941000
    mprotect(0x7fcd72a17000, 1048576, PROT_NONE) = 0
    mmap(0x7fcd72b17000, 57344, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 3, 0xd6000) = 0x7fcd72b17000
    mmap(0x7fcd72b25000, 2592, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_ANONYMOUS, -1, 0) = 0x7fcd72b25000
    close(3) = 0

对比一下，可以看到，sqlplus执行异常的strace，打开libsqlplus.so成功，

open("libsqlplus.so", O_RDONLY|O_CLOEXEC) = 3

sqlplus执行正常的strace，打开libsqlplus.so失败，然后打开了带路径的，

open("libsqlplus.so", O_RDONLY|O_CLOEXEC) = -1 ENOENT (No such file or directory)
    ...
    open("/opt/app/oracle/instantclient_11_2/libsqlplus.so", O_RDONLY|O_CLOEXEC) = 3

如果说是解决，在LD\_LIBRARY\_PATH中开始处增加"."，就是当前路径，此时无论在不在$ORACLE\_HOME，sqlplus都可以正常执行，

export LD_LIBRARY_PATH=.:$LD_LIBRARY_PATH:$ORACLE_HOME

但是，原因是什么？

众所周知，LD\_LIBRARY\_PATH是Linux环境变量名，该环境变量主要用于指定查找共享库(动态链接库，例如此处的libsqlplus.so)时除了默认路径之外的其他路径。

当执行函数动态链接.so时，如果此文件不在缺省目录下/lib和/usr/lib，那么就需要指定环境变量LD\_LIBRARY\_PATH，有时候我们安装的软件，因为没root权限，不会放到这些系统路径下，因此要改LD\_LIBRARY\_PATH，指定查找的路径。因此，运行时动态库的搜索路径就需要先后顺序，  
1. 编译目标代码时指定的动态库搜索路径。  
2. 环境变量LD\_LIBRARY\_PATH指定的动态库搜索路径。  
3 .配置文件/etc/ld.so.conf中指定的动态库搜索路径。  
4. 默认的动态库搜索路径/lib和/usr/lib。

再回到这个问题，同样在$ORACLE\_HOME路径下，执行sqlplus，strace的回显还是一样，但此时执行成功，说明sqlplus的执行路径是没问题的，关键还是LD\_LIBRARY\_PATH将"."当前路径加入了其中，

[test@localhost instantclient_11_2]$ strace sqlplus
    execve("./sqlplus", ["sqlplus"], [/* 28 vars */]) = 0
    brk(0) = 0x22c0000
    mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f30b4903000
    access("/etc/ld.so.preload", R_OK) = -1 ENOENT (No such file or directory)
    open("./tls/x86_64/libsqlplus.so", O_RDONLY) = -1 ENOENT (No such file or directory)
    open("./tls/libsqlplus.so", O_RDONLY) = -1 ENOENT (No such file or directory)
    open("./x86_64/libsqlplus.so", O_RDONLY) = -1 ENOENT (No such file or directory)
    open("./libsqlplus.so", O_RDONLY) = 3
    read(3, "\177ELF\2\1\1\0\0\0\0\0\0\0\0\0\3\0>\0\1\0\0\0@\370\1\0\0\0\0\0"..., 832) = 832
    fstat(3, {st_mode=S_IFREG|0555, st_size=1469542, ...}) = 0
    getcwd("/opt/app/oracle/instantclient_11_2", 128) = 58

对这个问题原因，还是有些含糊，这种解释，有些牵强，

1.不在$ORACLE\_HOME执行，当前路径没libsqlplus.so，因此会到LD\_LIBRARY\_PATH定义的$ORACLE\_HOME中找到带全路径的libsqlplus.so。

2.在$ORACLE\_HOME执行，当前路径存在libsqlplus.so，但是LD\_LIBRARY\_PATH没定义"."当前路径，加载失败，当加入"."定义后，执行成功。

这个应该不是一个难题，还是考察的对环境变量的理解和运用，因此，还是希望各位路过的朋友、大神，能指点一二，找到合理的解释，在此谢过。

**近期的热文：**

《[如何查看JVM运行的堆内存情况][JVM]》

《[Centos能不能替换RHEL？][Centos_RHEL]》

《[Linux下如何快速删除大量碎小的文件？][Linux]》

《[YNWA，同样是我们普通人的鞭策][YNWA]》

《[海底的下面究竟有什么？][Link 1]》

《[几种去重的SQL写法][SQL]》

《[打造国产技术产品的必要性][Link 2]》

《[SQL查询总是先执行SELECT语句么？][SQL_SELECT]》

《[Oracle删除字段的方式和风险，你都了解么？][Oracle]》

《[登录缓慢的诡异问题][Link 3]》

《[Linux下的^M困惑][Linux_M]》

《[Oracle相关提问的智慧技巧][Oracle 1]》

《[很久以前的一篇对初学Oracle建议的文章][Oracle 2]》

《[PLSQL Developer几个可能的隐患][PLSQL Developer]》

《[从70万字SRE神作提炼出的7千字精华文章][70_SRE_7]》

《[从数据误删到全量恢复的惊险记录][Link 4]》

《[OpenJDK和Oracle JDK有什么区别和联系？][OpenJDK_Oracle JDK]》

《[公众号600篇文章分类和索引][600]》

[format_png]: /images/20230209/6ae610fe9d3e47969f517481481e3645.png
[JVM]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815b74ddf6d2621f3b547a49d4b6c8025612fb1ab0892928365b881ad2f76f01206ed68e45&idx=1&mid=2247491207&scene=21&sn=11ad7551928df895633f9b73f103eb0e#wechat_redirect
[Centos_RHEL]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815b9bddf6d28d64b3138c595259531133f6292579a80d64367f7c032e3ae0731c5b7ae100&idx=1&mid=2247491176&scene=21&sn=52abc41303124ee6bec4d1b968b0a3a5#wechat_redirect
[Linux]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815ba7ddf6d2b139b2ac9b349e6d4ae81ba5b16d9ea544458c70ae53ec29e68b6093ca013d&idx=1&mid=2247491156&scene=21&sn=8bb7f61a86275070f0ce4c7173e39906#wechat_redirect
[YNWA]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815be0ddf6d2f622e583dbc72030c024dec56c010a47ec3c2404c5f5285ecb66d15bfa0737&idx=1&mid=2247491091&scene=21&sn=b1ae149d97d4976db3c0b3d0d4333b4d#wechat_redirect
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815836ddf6d120e652e411141d079adf6fbb10440cf2a7c6933abb353f79904dd381ab4ab5&idx=1&mid=2247491013&scene=21&sn=f29818de443cdd373735e9ee016b3c12#wechat_redirect
[SQL]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815845ddf6d153831abb0fd05b9cea02bba60338e52796ea14f3603452f7bd1db0e5c5d813&idx=1&mid=2247490998&scene=21&sn=5fddb640acc05890e52a6efb5fb5d982#wechat_redirect
[Link 2]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea81588bddf6d19d016604aedd9ae6d523f5dcd74d1223d304a6c114d0e5d186b90381a3a5ba&idx=1&mid=2247490936&scene=21&sn=6d21bb9d94ad29ea7865a95b8e848b96#wechat_redirect
[SQL_SELECT]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815890ddf6d186f03e5c530de64f23f69d33d6a5491bc4b9ab3399314eefdfa48d46ee76aa&idx=1&mid=2247490915&scene=21&sn=ffffa05c638c23e45563590faddf0ede#wechat_redirect
[Oracle]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815939ddf6d02f97942e6dd5b5c60e60cd732d31c893f8879244f66d7d914b9f23fe7fc207&idx=1&mid=2247490762&scene=21&sn=67f5fdf567297ab86db77a57162f7a6a#wechat_redirect
[Link 3]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815955ddf6d0434a80b00ba0e31ba96198814d630a9a255ec7b427a210ab01ad5bba685038&idx=1&mid=2247490726&scene=21&sn=f8836d3e25f6496fab88258526f048a9#wechat_redirect
[Linux_M]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815e2bddf6d73dc84a0e5cb4b86c833ff08a5f33ed8bd75e0bb8b627fc527bf4534e69cccc&idx=1&mid=2247490520&scene=21&sn=e7e6847bb74b86e3b9940caf6401bdf1#wechat_redirect
[Oracle 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815e3dddf6d72b5ea8dcb6fed7e651a2ce3ca5dfa28cc5798f67574cd5a3a5af1ba93cfc58&idx=1&mid=2247490510&scene=21&sn=2a63496df90e069a65b9bc5e2183c396#wechat_redirect
[Oracle 2]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815e33ddf6d72525282435a52f229b74f300a1cd7a1b90e39130a7012297ca9c4dbd9d3032&idx=1&mid=2247490496&scene=21&sn=657586233bc766f0306219e25eafa428#wechat_redirect
[PLSQL Developer]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815e6eddf6d778b47635d215c83252f159f54abd9c4fde8b425d74b5ef12e435d251abdf50&idx=1&mid=2247490461&scene=21&sn=afe375247b34fb493404412667ca1f42#wechat_redirect
[70_SRE_7]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815e73ddf6d76569914615095c7e5efc66c9fa603d18dbad3c6370d75b718ab01fdc6e9459&idx=1&mid=2247490432&scene=21&sn=b0ffda7d19d9d36dc646f67734531684#wechat_redirect
[Link 4]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815e98ddf6d78ef9737abd251cda2ae79dbd0f22ec585306ede98c4f56d7bf2c3d8dbb07ef&idx=1&mid=2247490411&scene=21&sn=4220834703c8582d23eba29ecd25546d#wechat_redirect
[OpenJDK_Oracle JDK]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815f5fddf6d649abf25460bd2acfd5e03be5a2ec6d3a89c1a0b874ec65bf8805745ae46200&idx=1&mid=2247490220&scene=21&sn=b68569084722c00fa32fc5255f47912a#wechat_redirect
[600]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815fa3ddf6d6b521f9a1fccaeaf2b2ed2b547c3102b18c51397ef670985f477ee102d76fe8&idx=1&mid=2247490128&scene=21&sn=3e9b7f11a1bf04b4f302830fcdbed57d#wechat_redirect