Password Oracle 12c SEC_CASE_SENSITIVE_LOGON and ORA-01017... 12c and Later Releases

Error: Oracle 12c SEC_CASE_SENSITIVE_LOGON and ORA-01017

Cause:

This issue is caused by the default setting for allowed logon version in the 12 database.
Note that the SQLNET.ALLOWED_LOGON_VERSION parameter has been deprecated in 12c.
That parameter has been replaced by these:
SQLNET.ALLOWED_LOGON_VERSION_SERVER=n
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=n
Version 12.1:The default setting for the new parameters is 11.  Any client that attempts to connect must
be at version 11 or higher unless these parameters are explicitly set in the server side sqlnet.ora file.
Version 12.2 note:
The default for the SQLNET.ALLOWED_LOGON_VERSION_SERVER setting has changed in 12.2 from 11 to 12.
See:  https://docs.oracle.com/database/122/DBSEG/configuring-authentication.htm#DBSEG33223
Important note for 12.2:  If your client is not at least 11.2.0.3 or includes the CPUOCT2012 patch you will not be able
to use the 12 setting.
Typically, the sqlnet.ora file that would be referenced by the database is located in RDBMS_HOME/network/admin.

Solution

The init.ora/spfile parameter SEC_CASE_SENSITIVE_LOGON got deprecated since Oracle Database 12.1.0.1
This means, we don’t do any further developments to it, you shouldn’t change it from its default TRUE and if you still do you’ll receive a nice warning during STARTUP of your database:
SQL> alter system set sec_case_sensitive_logon=false scope=spfile;
System altered.
SQL> startup force
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Recently a user asked me if we’d changed the behavior of this parameter in Oracle Database 12c Release 2 as he receives now an ORA-01017: Invalid username or password error when having SEC_CASE_SENSITIVE_LOGON=FALSE with every user except SYSDBAs
In which situations may you receive an ORA-01017?
This is outlined in the Oracle 12.1 documentation already:
•Ensure that the SEC_CASE_SENSITIVE_LOGON parameter is not set to FALSE if the SQLNET.ALLOWED_LOGON_VERSION_SERVER parameter is set to 12 or 12a
This is because the more secure password versions used for this mode only support case-sensitive password checking.
For compatibility reasons, Oracle Database does not prevent the use of FALSE for SEC_CASE_SENSITIVE_LOGON when SQLNET.ALLOWED_LOGON_VERSION_SERVER is set to 12 or 12a
Setting SEC_CASE_SENSITIVE_LOGON to FALSE when SQLNET.ALLOWED_LOGON_VERSION_SERVER is set to 12 or 12a causes all accounts to become inaccessible.
The key is the sqlnet.ora parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER and here’s the difference between Oracle Database 12.1 and Oracle Database 12.2:
•Oracle Database 12.1: SQLNET.ALLOWED_LOGON_VERSION_SERVER defaults to 11 out of the box
•Oracle Database 12.2: SQLNET.ALLOWED_LOGON_VERSION_SERVER defaults to 12 out of the box
Behavior difference Oracle 12.1 vs Oracle 12.2
See this simple example after switching SEC_CASE_SENSITIVE_LOGON=FALSE in both databases (as shown above):
•Oracle Database 12.1.0.2:
SQL> alter user system identified by oracle;
User altered.
SQL> connect system/oracle
Connected.
•Oracle Database 12.2.0.1:
SQL> alter user system identified by oracle;
User altered.
SQL> connect system/oracle
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE
How to workaround the ORA-01017?
First of all you need to edit your sqlnet.ora adding (or lowering) the parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER to a value below 12
But if you try to connect directly after restarting your listener you will receive the same ORA-01017 again
The secret is mentioned in the above documentation link as well, you will have to recreate the user’s password if you need the logon process to work as it did work before Oracle Database 12.2
sqlnet.ora
# sqlnet.ora Network Configuration File: /d01/app/oracle/product/12.2.0.1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

-bash-4.4$ cd $ORACLE_HOME/network/admin/
-bash-4.4$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /oraclehome/app/oracle/product/12.2.0.1/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
SQLNET.ALLOWED_LOGON_VERSION_SERVER=11

-bash-4.4$

Check in DBA_USERS
SQL> select username, password_versions
from DBA_USERS
where username='SYSTEM';
USERNAME      PASSWORD_VERSIONS
------------- ---------------------
SYSTEM        11G 12C
There’s no “10G” mentioned. This will prevent the connection.
Solution: You will have to specify the password again respective ALTER the user(s):

SQL> alter user SYSTEM identified by said;
User altered.

SQL> select username, password_versions
from DBA_USERS
where username='SYSTEM';
USERNAME       PASSWORD_VERSIONS
-------------- ----------------------
SYSTEM         10G 11G 12C
SQL> connect system/said

Connected.

clean up the asm disk header of all the disks in the diskgroup

-bash-4.1$ asmcmd lsdg --discovery
State       Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED     NORMAL  N         512   4096  1048576   6752416   447285           306928           70178              0             N  DATA/
MOUNTED     EXTERN  N         512   4096  1048576   1227964  1216729                0         1216729              0             N  UFRA/
DISMOUNTED          N           0      0        0         0        0                0               0              0             N  ULOG1/
DISMOUNTED          N           0      0        0         0        0                0               0              0             N  ULOG2/
DISMOUNTED          N           0      0        0         0        0                0               0              0             N  ULOG3/
-bash-4.1$

SQL> select name ,state from v$asm_diskgroup;

NAME                           STATE
------------------------------ -----------
ULOG2                          DISMOUNTED
ULOG3                          DISMOUNTED
DATA                           MOUNTED
ULOG1                          DISMOUNTED
UFRA                           MOUNTED

SQL>


dd if=/dev/zero of=/dev/oracle/dg/c11t7d0 bs=8192 count=100

dd if=/dev/zero of=/dev/rdsk/c0t600601607E734D001E78DB5D1E6BE161d0s0 bs=8192 count=100

SQL> select name ,state from v$asm_diskgroup;

NAME                           STATE
------------------------------ -----------
ULOG2                          DISMOUNTED
ULOG3                          DISMOUNTED
DATA                           MOUNTED
UFRA                           MOUNTED

SQL>

dd if=/dev/zero of=/dev/rdsk/c0t600601607E734D001E78DB5D99FC3B3Cd0s0 bs=8192 count=100
dd if=/dev/zero of=/dev/rdsk/c0t600601607E734D001E78DB5DFF0A0E8Ed0s0 bs=8192 count=100

root@badb1-t7-2:~# dd if=/dev/zero of=/dev/rdsk/c0t600601607E734D001E78DB5D99FC3B3Cd0s0 bs=8192 count=100
100+0 records in
100+0 records out
root@badb1-t7-2:~# dd if=/dev/zero of=/dev/rdsk/c0t600601607E734D001E78DB5DFF0A0E8Ed0s0 bs=8192 count=100
100+0 records in
100+0 records out
root@badb1-t7-2:~#

SQL> select name ,state from v$asm_diskgroup;

NAME                           STATE
------------------------------ -----------
DATA                           MOUNTED
UFRA                           MOUNTED

Rename Existing Diskgroup and create new disk group in Oracle ASM

Rename Existing Diskgroup and create new disk group in Oracle ASM

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 12/18/2019 10:25:39
ORA-00349: failure obtaining block size for '+LOG1/bacdb/redo01a.rdo'
ORA-15001: diskgroup "LOG1" does not exist or is not mounted
ORA-15001: diskgroup "LOG1" does not exist or is not mounted
ORA-15001: diskgroup "LOG1" does not exist or is not mounted

RMAN>


ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  NORMAL  N         512   4096  1048576   6752416   447285           306928           70178              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  1048576   1227964  1216729                0         1216729              0             N  UFRA/
MOUNTED  EXTERN  N         512   4096  1048576     20267     8929                0            8929              0             N  ULOG1/
MOUNTED  EXTERN  N         512   4096  1048576     20267     8929                0            8929              0             N  ULOG2/
MOUNTED  EXTERN  N         512   4096  1048576     20267     8929                0            8929              0             N  ULOG3/
ASMCMD>

-bash-4.1$ sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 18 10:32:17 2019

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

Enter user-name: /as sysasm

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Automatic Storage Management option

SQL> alter diskgroup ulog1 dismount;

Diskgroup altered.

SQL> alter diskgroup ulog2 dismount;

Diskgroup altered.

SQL> alter diskgroup ulog3 dismount;

Diskgroup altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Automatic Storage Management option
-bash-4.1$ asmcmd
ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  NORMAL  N         512   4096  1048576   6752416   447285           306928           70178              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  1048576   1227964  1216729                0         1216729              0             N  UFRA/
ASMCMD>


renamedg dgname=ulog1 newdgname=log1 verbose=true check=true

-bash-4.1$ renamedg dgname=ulog1 newdgname=log1 verbose=true check=true

Parsing parameters..

Parameters in effect:

         Old DG name       : ULOG1
         New DG name          : LOG1
         Phases               :
                 Phase 1
                 Phase 2
         Discovery str        : (null)
         Check              : TRUE
         Clean              : TRUE
         Raw only           : TRUE
renamedg operation: dgname=ulog1 newdgname=log1 verbose=true check=true
Executing phase 1
Discovering the group
Performing discovery with string:
Identified disk UFS:/dev/rdsk/c0t600601607E734D001E78DB5D1E6BE161d0s0 with disk number:0 and timestamp (33091441 -1849335808)
Checking for hearbeat...
Re-discovering the group
Performing discovery with string:
Identified disk UFS:/dev/rdsk/c0t600601607E734D001E78DB5D1E6BE161d0s0 with disk number:0 and timestamp (33091441 -1849335808)
Checking if the diskgroup is mounted or used by CSS
Checking disk number:0
Generating configuration file..
Completed phase 1
Executing phase 2
Looking for /dev/rdsk/c0t600601607E734D001E78DB5D1E6BE161d0s0
Leaving the header unchanged
Completed phase 2
Terminating kgfd context 1001bdc90
-bash-4.1$



-bash-4.1$ renamedg dgname=ulog2 newdgname=log2 verbose=true check=true

Parsing parameters..

Parameters in effect:

         Old DG name       : ULOG2
         New DG name          : LOG2
         Phases               :
                 Phase 1
                 Phase 2
         Discovery str        : (null)
         Check              : TRUE
         Clean              : TRUE
         Raw only           : TRUE
renamedg operation: dgname=ulog2 newdgname=log2 verbose=true check=true
Executing phase 1
Discovering the group
Performing discovery with string:
Identified disk UFS:/dev/rdsk/c0t600601607E734D001E78DB5D99FC3B3Cd0s0 with disk number:0 and timestamp (33091441 -1832428544)
Checking for hearbeat...
Re-discovering the group
Performing discovery with string:
Identified disk UFS:/dev/rdsk/c0t600601607E734D001E78DB5D99FC3B3Cd0s0 with disk number:0 and timestamp (33091441 -1832428544)
Checking if the diskgroup is mounted or used by CSS
Checking disk number:0
Generating configuration file..
Completed phase 1
Executing phase 2
Looking for /dev/rdsk/c0t600601607E734D001E78DB5D99FC3B3Cd0s0
Leaving the header unchanged
Completed phase 2
Terminating kgfd context 1001bdc90
-bash-4.1$

-bash-4.1$ renamedg dgname=ulog3 newdgname=log3 verbose=true check=true

Parsing parameters..

Parameters in effect:

         Old DG name       : ULOG3
         New DG name          : LOG3
         Phases               :
                 Phase 1
                 Phase 2
         Discovery str        : (null)
         Check              : TRUE
         Clean              : TRUE
         Raw only           : TRUE
renamedg operation: dgname=ulog3 newdgname=log3 verbose=true check=true
Executing phase 1
Discovering the group
Performing discovery with string:
Identified disk UFS:/dev/rdsk/c0t600601607E734D001E78DB5DFF0A0E8Ed0s0 with disk number:0 and timestamp (33091441 -1809569792)
Checking for hearbeat...
Re-discovering the group
Performing discovery with string:
Identified disk UFS:/dev/rdsk/c0t600601607E734D001E78DB5DFF0A0E8Ed0s0 with disk number:0 and timestamp (33091441 -1809569792)
Checking if the diskgroup is mounted or used by CSS
Checking disk number:0
Generating configuration file..
Completed phase 1
Executing phase 2
Looking for /dev/rdsk/c0t600601607E734D001E78DB5DFF0A0E8Ed0s0
Leaving the header unchanged
Completed phase 2
Terminating kgfd context 1001bdc90
-bash-4.1$

-bash-4.1$ asmcmd lsdg -g ulog1
ASMCMD-8001: diskgroup 'ulog1' does not exist or is not mounted
-bash-4.1$ asmcmd lsdg -g log1
ASMCMD-8001: diskgroup 'log1' does not exist or is not mounted
-bash-4.1$

-bash-4.1$ asmcmd lsdg --discovery
State       Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED     NORMAL  N         512   4096  1048576   6752416   447285           306928           70178              0             N  DATA/
MOUNTED     EXTERN  N         512   4096  1048576   1227964  1216729                0         1216729              0             N  UFRA/
DISMOUNTED          N           0      0        0         0        0                0               0              0             N  ULOG1/
DISMOUNTED          N           0      0        0         0        0                0               0              0             N  ULOG2/
DISMOUNTED          N           0      0        0         0        0                0               0              0             N  ULOG3/
-bash-4.1$

SQL> select name ,state from v$asm_diskgroup;

NAME                           STATE
------------------------------ -----------
ULOG2                          DISMOUNTED
ULOG3                          DISMOUNTED
DATA                           MOUNTED
ULOG1                          DISMOUNTED
UFRA                           MOUNTED

SQL>

 clean up the asm disk header of all the disks in the diskgroup

dd if=/dev/zero of=/dev/oracle/dg/c11t7d0 bs=8192 count=100

dd if=/dev/zero of=/dev/rdsk/c0t600601607E734D001E78DB5D1E6BE161d0s0 bs=8192 count=100

SQL> select name ,state from v$asm_diskgroup;

NAME                           STATE
------------------------------ -----------
ULOG2                          DISMOUNTED
ULOG3                          DISMOUNTED
DATA                           MOUNTED
UFRA                           MOUNTED

SQL>

 clean up the asm disk header of all the disks in the diskgroup

dd if=/dev/zero of=/dev/rdsk/c0t600601607E734D001E78DB5D99FC3B3Cd0s0 bs=8192 count=100
dd if=/dev/zero of=/dev/rdsk/c0t600601607E734D001E78DB5DFF0A0E8Ed0s0 bs=8192 count=100

root@badb1-t7-2:~# dd if=/dev/zero of=/dev/rdsk/c0t600601607E734D001E78DB5D99FC3B3Cd0s0 bs=8192 count=100
100+0 records in
100+0 records out
root@badb1-t7-2:~# dd if=/dev/zero of=/dev/rdsk/c0t600601607E734D001E78DB5DFF0A0E8Ed0s0 bs=8192 count=100
100+0 records in
100+0 records out
root@badb1-t7-2:~#

SQL> select name ,state from v$asm_diskgroup;

NAME                           STATE
------------------------------ -----------
DATA                           MOUNTED
UFRA                           MOUNTED

SQL>

CREATE DISKGROUP LOG1
EXTERNAL REDUNDANCY
  DISK '/dev/rdsk/c0t600601607E734D001E78DB5D1E6BE161d0s0' SIZE 20267M NOFORCE
ATTRIBUTE
  'AU_SIZE'='1048576',
  'COMPATIBLE.RDBMS'='11.1.0.0.0',
  'COMPATIBLE.ASM'='11.1.0.0.0';
ALTER DISKGROUP LOG1
  SET ATTRIBUTE 'DISK_REPAIR_TIME' = '3.6 H';
 
 
CREATE DISKGROUP LOG2
EXTERNAL REDUNDANCY
  DISK '/dev/rdsk/c0t600601607E734D001E78DB5D99FC3B3Cd0s0' SIZE 20267M NOFORCE
ATTRIBUTE
  'AU_SIZE'='1048576',
  'COMPATIBLE.RDBMS'='11.1.0.0.0',
  'COMPATIBLE.ASM'='11.1.0.0.0';
ALTER DISKGROUP LOG2
  SET ATTRIBUTE 'DISK_REPAIR_TIME' = '3.6 H'; 


CREATE DISKGROUP LOG3
EXTERNAL REDUNDANCY
  DISK '/dev/rdsk/c0t600601607E734D001E78DB5DFF0A0E8Ed0s0' SIZE 20267M NOFORCE
ATTRIBUTE
  'AU_SIZE'='1048576',
  'COMPATIBLE.RDBMS'='11.1.0.0.0',
  'COMPATIBLE.ASM'='11.1.0.0.0';
ALTER DISKGROUP LOG3
  SET ATTRIBUTE 'DISK_REPAIR_TIME' = '3.6 H'; 

  SQL> select name ,state from v$asm_diskgroup;

NAME                           STATE
------------------------------ -----------
LOG2                           MOUNTED
LOG1                           MOUNTED
LOG3                           MOUNTED
DATA                           MOUNTED
UFRA                           MOUNTED

SQL>