ORA-01017 invalid username/password after 11g database upgraded to 12.2 pluggable database

Oracle database 11.2.0.3 was upgraded to 12.2.0.1 and plugged into CDB as pluggable database with 'create pluggable database ... using ...' command. The container database (CDB) was creted with 12.2 dbca and all built-in users' passwords (sys,system,dbsnmp, etc) were set to same ones as in original 11g database.


After open pluggable database in 12.2 environment, the client application got ORA-28040 as following

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Feb 19 20:02:15 2018

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn system/oracle@host01/CDB1
ERROR:
ORA-28040: No matching authentication protocol

Obviously, the client version (10.2.0.5) is too old for 12.2 database. However, there are hundreds of clients running 10g client software, and it is hard to upgrade so many clients. Therefore, the sqlnet parameter  SQLNET.ALLOWED_LOGON_VERSION_SERVER became a tentative workaround before the clients  are upgraded. The parameter is added to network file $ORACLE_HOME/network/admin/sqlnet.ora on  server host,

[oracle@host01]$ cd $ORACLE_HOME/network/admin
[oracle@host01]$ cat sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, LDAP, EZCONNECT)

[oracle@host01]$ echo "SQLNET.ALLOWED_LOGON_VERSION_SERVER = 10" >> sqlnet.ora

[oracle@host01]$  cat sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, LDAP, EZCONNECT)
SQLNET.ALLOWED_LOGON_VERSION_SERVER = 10

Test connectivity from client,

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Feb 19 20:12:15 2018

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn system/oracle@host01/CDB1
ERROR:
ORA-01017: invalid username/password; logon denied


SQL>  conn dbsnmp/oracle@host01/CDB1
ERROR:
ORA-01017: invalid username/password; logon denied


SQL> conn application_owner/password@host01/PDB1
Connected.

It works for application user (application_owner) in pluggable datbase, but bring up ORA-01017 instead of ORA-28040. Further connecting try on 12.2 client,

SQL*Plus: Release 12.2.0.1.0 Production on Mon Feb 19 20:15:54 2018

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

SQL> conn system/oracle@host01/CDB1
Connected.
SQL> conn dbsnmp/oracle@host01/CDB1
Connected.
SQL>conn appowner/oracle@host01/PDB1
Connected.
SQL>

Everything works on 12.2 client, but only pluggable database local users work on 10g client. Verify the password versions in the database with 12c client,

system@CDB1> select username,password_versions,common,con_id from cdb_users;

USERNAME                  PASSWORD_VERSIONS COM     CON_ID
------------------------- ----------------- --- ----------
APPLICATION_OWNER         10G 11G           NO       4
DBSNMP                    11G 12C           YES      1
DBSNMP                    10G 11G           YES      4
SYSTEM                    11G 12C           YES      1
SYSTEM                    10G 11G           YES      4
 <<Result truncated>>

Application user (applicaton_owner) is local user in pluggable database (container id is 4) with password  versions 10G & 11G. Therefore, it works with 10g, 11g and 12c client. Here, 12.2 client works because it is compatible with 11G pasword version. However, built-in users (system, dbsnmp, etc) were created as common users by 12c dbca in container database CDB1 with password versions 11G & 12C, which are not compatible with 10g client.

To let these common users be able to login with 10g client, the passwords have to be reset with 12c client,

system@CDB1> alter user system identified by oracle;

User altered.

system@CDB1> alter user dbsnmp identified by oracle;

User altered.

system@CDB1.CDB1.garuda> select username,password_versions,common,con_id from cdb_users;

USERNAME                  PASSWORD_VERSIONS COM     CON_ID
------------------------- ----------------- --- ----------
APPLICATION_OWNER         10G 11G           NO           4
DBSNMP                    10G 11G 12C       YES          1
DBSNMP                    11G 12C           YES          4
SYSTEM                    10G 11G 12C       YES          1
SYSTEM                    11G 12C           YES          4

10G version of password is created while the password is reset because value of parameter
SQLNET.ALLOWED_LOGON_VERSION_SERVER was set to 10. And SYSTEM and DBSNMP can also log into database with 10g client now.