在sqlplus中使用EZCONNECT尝试数据库时总时提示ora-12154、检查监听上无public IP，但是监听进程正常，同样存在service和VIP

穷苦书生_万事愁

于 2024-07-26 08:30:00 发布

阅读量521

点赞数 19

分类专栏：数据库文章标签：数据库 tcp/ip 网络协议

本文链接：https://blog.csdn.net/weixin_tank88921/article/details/140584184

版权

数据库专栏收录该内容

63 篇文章 0 订阅

订阅专栏

1、connect DB failed with ORA-12154 due to User Password have “@” (at mark) char

对于ORA-12nnn错误并不陌生，ORA-12154也是如配置tsnnames.ora 的alias错误常见，但是今天这个案例“若不是亲眼所见，我是万万不敢相信”，在sqlplus中使用EZCONNECT尝试数据库时总时提示ora-12154, 而部分用户或jdbc应用使用相同用户可以正常链接。

ORA-12154: TNS:could not resolve the connect identifier specified

分析思路

tnsping tnsnames.ora中配置的alias name或ezconnect串如tnsping ip:port
服务器登录排除client version或tnsnames.ora配置错误
不使用tns排除监听问题
临时创建个用户测试，给create session权限

下面记录一下这个案例环境oracle 11.2.0.4 rac on linux

在检查了EZCONNECT串配置没有问题后，发现密码中包含@符号，而@符号就是使用tns链接时的字符，即使使用了“”引号括起来一样失败，于是我创建了个新用户test验证一下@这个字符

[oracle@testos:/home/oracle]$sqlplus hr/"hr"@192.168.1.59:1521/testdb

SQL*Plus: Release 12.2.0.1.0 Production on Sat Sep 16 17:12:54 2023

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> 

#Note:可见使用相同的ezconnect可以链接, 下面修改密码，密码中带@符号
SQL> conn / as sysdba
Connected.
sys@testdb(47)> alter user hr identified by "test@1234";

User altered.


#EZCONNECT方式连接
[oracle@testos:/home/oracle]$sqlplus hr/"test@1234"@192.168.1.59:1521/testdb

SQL*Plus: Release 12.2.0.1.0 Production on Sat Sep 16 17:14:55 2023

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

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


Enter user-name: 

#sqlplus连接也报错
[oracle@testos:/home/oracle]$sqlplus hr/"test@1234"

SQL*Plus: Release 12.2.0.1.0 Production on Sat Sep 16 17:19:05 2023

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

ERROR:
ORA-12543: TNS:destination host unreachable


Enter user-name: 

#本地使用如下方式连接可以
[oracle@testos:/home/oracle]$sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Sat Sep 16 17:18:20 2023

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

SQL> conn hr/"test@1234"
Connected.
SQL>

Note:
发现出现了相同的报错ORA-12154

Oracle 密码要求

An Oracle password must adhere to the following rules:

Passwords must be 1 to 30 characters long.
Passwords cannot contain quotation marks. The quotation marks will be used to enclose those passwords that use special characters.
Passwords CAN be case sensitive ( as of RDBMS 11gR2)
A Password must begin with an alphabetic character. Otherwise the passwords starting with numbers or special characters should be enclosed in double quotation marks.
Passwords can contain only alphanumeric characters and the underscore (_), dollar sign ($), and pound sign (#). Oracle recommends the enclosure of the special characters within double quotation marks because some scripting environments /Operating Systems use these for environment variables. For example: The ‘#’ can be used to denote the beginning of a comment in some scripting / shell environments.

For all the other special characters the usage of the double quotation marks is mandatory. For example: The ‘@’ can be used to denote the beginning of TNS-alias in a sql connect command.

要求中也只是提到当使用其它字符时需要使用引号，但这个案例使用了引号一样失败，在MOS ORA-12154: Cannot Connect to SQLPLUS When Username or Password Contains “@” Special Character (Doc ID 2761789.1) 中提到的bug影响19.5以后的版本到23.1修复。看来在11.2.0.4中也可能引入了这个问题，所以建议密码中避免使用@符号。除了@符号问题在12c版本以前还有别的问题，如密码前后有空格时，Pro*Cobol会自动截取密码的前后空格而导致密码错误的问题。

Prior to version 12c it is not possible to create a database password with leading or trailing space characters – they are simply stripped from the string before it is saved. Therefore prior to 12c, Pro*Cobol automatically removes any leading or trailing spaces from password strings before sending them to the database.

From 12c the database allows passwords to contain spaces anywhere. Thus Pro*Cobol 12c no longer automatically strips leading and trailing spaces from password strings.
and change the database user’s password so as not to have any “@” character.

2、connect DB failed with Ora-12541 due to adump trace Inode usage 100%

ora-12541 No listener同样是个连接时问题，前端反馈使用public ip连接数据库时提示Ora-12541, 检查监听上无public IP，但是监听进程正常，同样存在service和VIP。这里简单记录这个问题。

环境11.2.0.4 RAC on linux, PUBLIC ip 100， Vip 101

分析思路

1，检查监听
2，检查IP和网卡状态
3，检查crs状态

检查监听

$lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 05-5月 -2022 14:26:06

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                08-9月 -2018 05:38:08
Uptime                    341 days 3 hr. 52 min. 12 sec  
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/app/11.2.0.4/grid/network/admin/listener.ora
Listener Log File         /oracle/app/grid/diag/tnslsnr/anbob2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.*.*.101)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "anbob" has 1 instance(s).
  Instance "anbob2", status READY, has 1 handler(s) for this service...
The command completed successfully

检查网卡

$ ip addr
...
21: bond1: <BROADCAST,MULTICAST,MASTER,UP,LOWER_UP> mtu 1500 qdisc noqueue state UP 
    link/ether 34:6a:c2:b9:4e:07 brd ff:ff:ff:ff:ff:ff
    inet 10.*.*.100/26 brd 10.*.*.127 scope global bond1 <<<<<
    inet 10.*.*.101/26 brd 10.*.*.127 scope global secondary bond1:1
    inet 10.*.*.120/26 brd 10.*.*.127 scope global secondary bond1:3

检查CRS

grid@anbob2:/home/grid>crsctl stat res -t
CRS-4535: 无法与集群就绪服务通信
CRS-4000: 命令 Status 失败, 或已完成但出现错误。


#检查crs进程,为offline的
grid@anbob2:/home/grid>crsctl stat res -t -init
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.asm
      1        ONLINE  ONLINE       anbob2                 Started             
ora.cluster_interconnect.haip
      1        ONLINE  ONLINE       anbob2                                     
ora.crf
      1        ONLINE  ONLINE       anbob2                                     
ora.crsd
      1        ONLINE  OFFLINE                    <<<<<                               
ora.cssd
      1        ONLINE  ONLINE       anbob2                                     
ora.cssdmonitor
      1        ONLINE  ONLINE       anbob2                                     
ora.ctssd
      1        ONLINE  ONLINE       anbob2                 OBSERVER            
ora.diskmon
      1        OFFLINE OFFLINE                                                   
ora.evmd
      1        ONLINE  ONLINE       anbob2                                     
ora.gipcd
      1        ONLINE  ONLINE       anbob2                                     
ora.gpnpd
      1        ONLINE  ONLINE       anbob2                                     
ora.mdnsd
      1        ONLINE  ONLINE       anbob2

Note:
CRSD资源已offline.

检查CRSd log

2022-05-02 13:23:15.828: 
[ohasd(32935)]CRS-10000:CLSU-00100: Operating System function: mkdir failed with error data: 28
CLSU-00101: Operating System error message: No space left on device
CLSU-00103: error location: authprep6
CLSU-00104: additional error information: failed to make dir /oracle/app/11.2.0.4/grid/auth/ohasd/anbob2/A8421321

2022-05-02 13:23:29.018: 
[crsd(42907)]CRS-1013:The OCR location in an ASM disk group is inaccessible. Details in /oracle/app/11.2.0.4/grid/log/anbob2/crsd/crsd.log.
2022-05-02 13:23:29.047: 
[crsd(42907)]CRS-0804:Cluster Ready Service aborted due to Oracle Cluster Registry error [PROC-26: Error while accessing the physical storage
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 28: No space left on device
Additional information: 9925
]. Details at (:CRSD00111:) in /oracle/app/11.2.0.4/grid/log/anbob2/crsd/crsd.log.
2022-05-02 13:23:29.157: 
...
2022-05-02 13:25:20.344: 
[ohasd(32935)]CRS-2765:Resource 'ora.crsd' has failed on server 'anbob2'.
2022-05-02 13:25:20.344: 
[ohasd(32935)]CRS-2771:Maximum restart attempts reached for resource 'ora.crsd'; will not restart.

Note:
因为OS 磁盘资源crash.

检查文件系统

$ df
$ df -i

Note:
显示ORACLE_BASE目录文件系统的INODE 100%, 关于INODE介绍可以参考这里

Linux uses inodes (index nodes) to keep track of all files in the system whether it is images, videos, emails, spams, website content, backups. But every system has a limit on number of inodes allowed, depending on the system memory,Find Directory with Most Inode Usage

for i in /*; do echo $i; find $i |wc -l; done

为什么CRSD.BIN会影响PUBLIC ip

这个可以在测试环境还原问题，在kill crsd.bin后，listener上的public ip会立即自动消失，当crsd.bin 自动启动后，public ip又会再次注册到listener上，以上工作均有oraagent完成，无需任何操作，当 crsd 和 oraagent 进程自动重新启动时，应在 1 分钟左右注册丢失的 endpoint。当然影响的不只是listener还有scan listener. 因为listener的动态ENDPOINTS是被oraagent进程完成，当crsd.bin挂掉时oragent也会自动挂掉，这时因为vip 的endpoint被db instance使用，所以vip保留，而public ip消失。

Listener dynamic endpoints are registered by the oraagent process. When crsd.bin dies, oraagent process will also die. Then the listener dynamic endpoints will not be available until oraagent process is restarted and registers those dynamic endpoints again. This is expected behavior.

However listener wil not drop the dynamic endpoint if the endpoint is in use by instance even after the oraagent process is terminated. This can be seen from the lsnrctl status output above, the host VIP endpoint remains after crsd.bin crashes.