首页 » ORACLE 9i-23ai » Troubleshooting to connect DB failed with ORA-12154 due to User Password have “@” (at mark) char

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

  • 2022/05/06
  • ORACLE 9i-23ai
  • 228 views
  • Troubleshooting to 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

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

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

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

Note:
可见使用相同的ezconnect可以链接, 下面修改密码，密码中带@符号

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

Oracle 密码要求

An Oracle password must adhere to the following rules:

o Passwords must be 1 to 30 characters long.
o Passwords cannot contain quotation marks. The quotation marks will be used to enclose those passwords that use special characters.
o Passwords CAN be case sensitive ( as of RDBMS 11gR2)
o A Password must begin with an alphabetic character. Otherwise the passwords starting with numbers or special characters should be enclosed in double quotation marks.
o 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.

打赏

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：