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:\Users\Administrator>sqlplus sys/abcd@shardcat as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Sat Dec 20 22:22:07 2014Copyright (c) 1982, 2010, Oracle.  All rights reserved.ERROR:ORA-28040: No matching authentication protocol

查看关于错误的详细描述:

[oracle@shard1 admin]$ oerr ora 2804028040, 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 12CSYSTEM                         OPEN                                                             11G 12CGSMCATUSER                     OPEN                                                             11G 12CJY                             OPEN                                                             11G 12CMYGDSADMIN                     OPEN                                                             11G 12CAPP_SCHEMA                     OPEN                                                             11G 12C6 rows selected.

服务器端：修改 sqlnet.ora 配置：（配置修改后，不需要重启oracle服务器）我这里是新创建的sqlnet.ora，因为原来没有创建

[oracle@shard1 admin]$ ls -lrt总用量 12-rw-r--r--. 1 oracle oinstall 1441 8月  28 2015 shrept.lstdrwxr-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.oraSQLNET.ALLOWED_LOGON_VERSION_SERVER=11SQLNET.ALLOWED_LOGON_VERSION_CLIENT=11

客户端：再次尝试连接，提示用户名密码错误：

C:\Users\Administrator>sqlplus sys/abcd@shardcat as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Sat Dec 20 22:16:06 2014Copyright (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 sysdbaSQL*Plus: Release 12.2.0.1.0 Production on Mon Feb 17 13:07:28 2020Copyright (c) 1982, 2016, Oracle.  All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit ProductionSQL> 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 12CSYSTEM                         OPEN                                                             11G 12CGSMCATUSER                     OPEN                                                             11G 12CJY                             OPEN                                                             11G 12CMYGDSADMIN                     OPEN                                                             11G 12CAPP_SCHEMA                     OPEN                                                             11G 12C6 rows selected.

客户端：再次尝试登录oracle 12c，成功:

C:\Users\Administrator>sqlplus sys/abcd@shardcat as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Sat Dec 20 22:22:39 2014Copyright (c) 1982, 2010, Oracle.  All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit ProductionSQL>

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