升级12C注意事项： 连接失败 ORA-28040 ORA-1017

当数据库升级到12c后，有些功能的改变可能影响到原应用的可用性，如数据库升级12c后原来的10G或更老的客户端或10g或更老的JDBC 驱动连接数据库时失败，因为安全认证协议不符合，导致ORA-28040错误的发生，提示下面的错误信息：

•  ORA-28040: No matching authentication protocol error


• ORA-03134: Connections to this server version are no longer supported

这是因为ORACLE 12C R2中默认的认证协议是12, 即便是sqlnet.ora文件不存在默认值同样生效，12是一种区分大小写密码的认证，是一种新的认证协议，解决办法是创建或在现有的sqlnet.ora文件中增加参数sqlnet.allowed_logon_version_server，在12c以前的版本中解决方法同样也是在sqlnet.ora文件增加参数SQLNET.ALLOWED_LOGON_VERSION, 该参数在12c中以被sqlnet.allowed_logon_version_server 和sqlnet.allowed_logon_version_client替代， 如果在12c中配置了SQLNET.ALLOWED_LOGON_VERSION,会在DB ALERT 中出现“Using deprecated SQLNET.ALLOWED_LOGON_VERSION parameter”提示。
数据库的认证协议并不是数据库软件的版本， 对应的是dba_users.password_versions的值， 使用下面的sql确认:

SELECT USERNAME, PASSWORD_VERSIONS FROM DBA_USERS ORDER BY 1;

在11g中的密码版本10g和11g组合， 12c中密码版本是11g, 12c的组合，如果想使用10g的密码版本登录12.2数据库需要修改sqlnet.allowed_logon_version_server 到11或更低的值。sqlnet.allowed_logon_version_server 和sqlnet.allowed_logon_version_client配置的值是最小认证版本的值，如数据库是12c, sqlnet.allowed_logon_version_server=10, 那10g,11g,12c的client都可以访问，包含10g对应的JDBC驱动包。 sqlnet.allowed_logon_version_server是配置在数据库服务器(被访问端）的DB $ORACLE_HOME的sqlnet.ora（不是GI HOME），在12.2版本有效值应该是8，9，10，11，12，12A，当ALLOWED_LOGON_VERSION_SERVER的值设为12时，只有已经应用了关键补丁更新CPUOct2012或更高版本的客户端，或者具有等同更新的11.2.0.3客户端版本才能连接到服务器。
解决方法
如果遇到该类问题解决方法是配置SQLNET.ALLOWED_LOGON_VERSION_SERVER的值，根据客户端版本设置一个比默认值更宽松的值。例如11或者更低：
SQLNET.ALLOWED_LOGON_VERSION_SERVER=11，或下载12c对应的JDBC驱动包或直接从oracle db software中复制给应用。
如果数据库做为一种CLIENT 使用DB LINK时同要需要在创建dblink数据库端sqlnet.ora中配置sqlnet.allowed_logon_version_client参数。
如果已完成了上面的修改，但是登录提示密码错误ORA-1017，同常还需要与SEC_CASE_SENSITIVE_LOGON参数配合。另外如果在12.2数据库中创建的用户密码版本默认为‘11G 12C’，这是一种排除了10g密码认证的版本，因为登陆用户没有10G的密码版本，所以使用10g或更老client导致报错ORA-1017。这类问题的解决方法是重新修改一次密码，使用

SELECT USERNAME FROM DBA_USERS WHERE TRIM(PASSWORD_VERSIONS) = '11G 12C';

密码更改时，除了'11G 12C'密码版本之外，数据库还会为其帐户生成缺少的10G版本。
更多信息参考http://docs.oracle.com/database/122/DBSEG/configuring-authentication.htm#DBSEG003。
Oracle online DOC.

If the following conditions are true, then you may have accounts that are prevented from logging into the database after upgrading to 12.2:

• You are upgrading a server that has user accounts created in an earlier Oracle Database release.


• User accounts created in the earlier release use a case-insensitive password version from an earlier release authentication protocol, such as the 10G password version.


• Earlier release user accounts have not reset passwords.


• The server has been configured with SEC_CASE_SENSITIVE_LOGON set to FALSE, so that it can only authenticate users who have a 10G case-insensitive password version.

If you have accounts that require 10G password versions, then to prevent accounts using that password version from being locked out of the database, you can change from an Exclusive Mode to a more permissive authentication protocol.

ORACLE DOC

The user accounts from Release 10g use the 10G password version. Therefore, you should find the user accounts that use the 10G password version, and then reset the passwords for these accounts. This generates the appropriate password version based on the setting of the SQLNET.ALLOWED_LOGON_VERSION_SERVER parameter, as follows:

• SQLNET.ALLOWED_LOGON_VERSION_SERVER=8 generates all three password versions 10G, 11G, and 12C.


• SQLNET.ALLOWED_LOGON_VERSION_SERVER=12 generates both 11G and 12C password versions, and removes the 10G password version.


• SQLNET.ALLOWED_LOGON_VERSION_SERVER=12a generates only the 12C password version.

更多配置的信息 http://docs.oracle.com/database/122/NETRF/parameters-for-the-sqlnet-ora-file.htm#NETRF198

Value of the ALLOWED_LOGON_VERSION_SERVER Parameter	Generated Password Version	Ability Required of the Client	Meaning for Clients
12a*1	12C	O7L_MR	Only Oracle Database 12c release 12.1.0.2 or later clients can connect to the server.
12*2	11G, 12C	O5L_NP	Only clients which have applied critical patch update CPUOct2012 or later, or release 11.2.0.3 clients with an equivalent update can connect to the server.
11	10G, 11G, 12C	O5L	Clients using Oracle Database 10g and later can connect to the server. Clients using releases earlier than Oracle Database release 11.2.0.3 that have not applied critical patch update CPUOct2012 or later patches must use the 10G password version.
10	10G, 11G, 12C	O5L	Clients using Oracle Database 10g and later can connect to the server. Clients using releases earlier than Oracle Database release 11.2.0.3 that have not applied critical patch update CPUOct2012 or later patches must use the 10G password version.
9	10G, 11G, 12C	O4L	Oracle9i Database or later clients can connect to the server.
8	10G, 11G, 12C	O3L	Oracle8i Database and later clients can connect to the server.

2017-12-26 -- updated --