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>sqlplussys/abcd@shardcatassysdbaSQL*Plus:Release11.2.0.1.0ProductiononSatDec2022:22:072014Copyright(c)1982,2010,Oracle.Allrightsreserved.ERROR:ORA-28040:Nomatchingauthenticationprotocol

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

[oracle@shard1admin]$oerrora2804028040,0000,"Nomatchingauthenticationprotocol"//*Cause:Therewasnoacceptableauthenticationprotocolfor//eitherclientorserver.//*Action:Theadministratorshouldsetthevaluesofthe//SQLNET.ALLOWED_LOGON_VERSION_SERVERand//SQLNET.ALLOWED_LOGON_VERSION_CLIENTparameters,onboththe//clientandontheserver,tovaluesthatmatchtheminimum//versionsoftwaresupportedinthesystem.//Thiserrorisalsoraisedwhentheclientisauthenticatingto//auseraccountwhichwascreatedwithoutaverifiersuitablefor//theclientsoftwareversion.Inthissituation,thataccount's//passwordmustbereset,inorderfortherequiredverifierto//begeneratedandallowauthenticationtoproceedsuccessfully.

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

SQL>selectusername,account_status,password_versionsfromdba_userswhereaccount_status='OPEN';USERNAMEACCOUNT_STATUSPASSWORD_VERSIONS--------------------------------------------------------------------------------------------------------------------------------SYSOPEN11G12CSYSTEMOPEN11G12CGSMCATUSEROPEN11G12CJYOPEN11G12CMYGDSADMINOPEN11G12CAPP_SCHEMAOPEN11G12C6rowsselected.

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

[oracle@shard1admin]$ls-lrt总用量12-rw-r--r--.1oracleoinstall14418月282015shrept.lstdrwxr-xr-x.2oracleoinstall6110月122017samples-rw-r-----.1oracleoinstall9601月182018tnsnames.ora-rw-r--r--1oracleoinstall5042月1712:58listener.ora[oracle@shard1admin]$visqlnet.oraSQLNET.ALLOWED_LOGON_VERSION_SERVER=11SQLNET.ALLOWED_LOGON_VERSION_CLIENT=11

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

C:\Users\Administrator>sqlplussys/abcd@shardcatassysdbaSQL*Plus:Release11.2.0.1.0ProductiononSatDec2022:16:062014Copyright(c)1982,2010,Oracle.Allrightsreserved.ERROR:ORA-01017:invalidusername/password;logondenied

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

[oracle@shard1~]$sqlplussys/abcd@shardcatassysdbaSQL*Plus:Release12.2.0.1.0ProductiononMonFeb1713:07:282020Copyright(c)1982,2016,Oracle.Allrightsreserved.Connectedto:OracleDatabase12cEnterpriseEditionRelease12.2.0.1.0-64bitProductionSQL>alterusersysidentifiedby"abcd";Useraltered.SQL>selectusername,account_status,password_versionsfromdba_userswhereaccount_status='OPEN';USERNAMEACCOUNT_STATUSPASSWORD_VERSIONS--------------------------------------------------------------------------------------------------------------------------------SYSOPEN10G11G12CSYSTEMOPEN11G12CGSMCATUSEROPEN11G12CJYOPEN11G12CMYGDSADMINOPEN11G12CAPP_SCHEMAOPEN11G12C6rowsselected.

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

C:\Users\Administrator>sqlplussys/abcd@shardcatassysdbaSQL*Plus:Release11.2.0.1.0ProductiononSatDec2022:22:392014Copyright(c)1982,2010,Oracle.Allrightsreserved.Connectedto:OracleDatabase12cEnterpriseEditionRelease12.2.0.1.0-64bitProductionSQL>

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