PLSQL通过Oracle 11g客户端连接Oracle 12c服务器错误 ORA-28040-阿里云开发者社区

开发者社区> 数据库> 正文

PLSQL通过Oracle 11g客户端连接Oracle 12c服务器错误 ORA-28040

简介:

PLSQL通过Oracle 11g客户端连接Oracle 12c服务器错误 ORA-28040
环境描述:
oracle服务器端版本:oracle 12.2.0.1.0
oracle客户端版本:oracle 11.2.0.1.0
PLSQL是11.4

因为PLSQL连接数据库也是要通过Oracle客户端,那么使用11g客户端访问oracle 12c应该也会得到如下错误:

C:UsersAdministrator>sqlplus sys/abcd@shardcat as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Dec 20 22:22:07 2014

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

ERROR:
ORA-28040: No matching authentication protocol
查看关于错误的详细描述:

[oracle@shard1 admin]$ oerr ora 28040
28040, 0000, "No matching authentication protocol"
// *Cause: There was no acceptable authentication protocol for
// either client or server.
// *Action: The administrator should set the values of the
// SQLNET.ALLOWED_LOGON_VERSION_SERVER and
// SQLNET.ALLOWED_LOGON_VERSION_CLIENT parameters, on both the
// client and on the server, to values that match the minimum
// version software supported in the system.
// This error is also raised when the client is authenticating to
// a user account which was created without a verifier suitable for
// the client software version. In this situation, that account's
// password must be reset, in order for the required verifier to
// be generated and allow authentication to proceed successfully.

在服务器端查看表:dba_users,PASSWORD_VERSIONS 列是:11G 12C

SQL> select username, account_status,password_versions from dba_users where account_status='OPEN';

USERNAME ACCOUNT_STATUS PASSWORD_VERSIONS
------------------------------ ---------------------------------------------------------------- ----------------------------------
SYS OPEN 11G 12C
SYSTEM OPEN 11G 12C
GSMCATUSER OPEN 11G 12C
JY OPEN 11G 12C
MYGDSADMIN OPEN 11G 12C
APP_SCHEMA OPEN 11G 12C

6 rows selected.
服务器端:修改 sqlnet.ora 配置:(配置修改后,不需要重启oracle服务器)我这里是新创建的sqlnet.ora,因为原来没有创建

[oracle@shard1 admin]$ ls -lrt
总用量 12
-rw-r--r--. 1 oracle oinstall 1441 8月 28 2015 shrept.lst
drwxr-xr-x. 2 oracle oinstall 61 10月 12 2017 samples
-rw-r-----. 1 oracle oinstall 960 1月 18 2018 tnsnames.ora
-rw-r--r-- 1 oracle oinstall 504 2月 17 12:58 listener.ora
[oracle@shard1 admin]$ vi sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION_SERVER=11
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=11
客户端:再次尝试连接,提示用户名密码错误:

C:UsersAdministrator>sqlplus sys/abcd@shardcat as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Dec 20 22:16:06 2014

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

ERROR:
ORA-01017: invalid username/password; logon denied

修改服务器端 sqlnet.ora 后,需要重新登录sqlplus,再修改用户密码,否则修改用户密码后,标记的密码版本仍然为11G 12C;重新登录sqlplus,修改scott用户密码,并查看 PASSWORD_VERSIONS,多了一个 10G

[oracle@shard1 ~]$ sqlplus sys/abcd@shardcat as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Feb 17 13:07:28 2020

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

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

SQL> alter user sys identified by "abcd";

User altered.

SQL> select username, account_status,password_versions from dba_users where account_status='OPEN';

USERNAME ACCOUNT_STATUS PASSWORD_VERSIONS
------------------------------ ---------------------------------------------------------------- ----------------------------------
SYS OPEN 10G 11G 12C
SYSTEM OPEN 11G 12C
GSMCATUSER OPEN 11G 12C
JY OPEN 11G 12C
MYGDSADMIN OPEN 11G 12C
APP_SCHEMA OPEN 11G 12C

6 rows selected.
客户端:再次尝试登录oracle 12c,成功:

C:UsersAdministrator>sqlplus sys/abcd@shardcat as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Dec 20 22:22:39 2014

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

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

SQL>
总结:oracle11g 客户端连接 oracle 12c服务器,需要在服务器端配置 sqlnet.ora,并重新修改用户密码。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章