Environment details:
Oracle Database Version 19.6.0.0.0 running on EL7
Issue:
Connection to sys as sysdba via network is throwing invalid username/password error
I checked the password related parameter remote_login_passwordfile and it is set to EXCLUSIVE
Checked the password file whether it is present or not.
Cause:
This is the issue in my environment. We have recently changed the disk group names from +ABC_DATA_DG01 to +DATA as part of standardization. So the password file name didn't change after the disk name is changed. So we need to change the password file to take a new name with actual path. Use -f to force create the file otherwise the command will say already file exists as below
In 19c, Oracle introduced a new clause for the ALTER SYSTEM command FLUSH PASSWORDFILE_METADATA_CACHE. So according to this clause if the location or the name of the password file changes, you must notify the database that a change has occurred. The command ALTER SYSTEM FLUSH PASSWORDFILE_METADATA_CACHE flushes the password file metadata cache stored in the SGA and informs the database that a change has occurred.
You may read more on all the ALTER SYSTEM commands in this link.
So once we do the flush, we are able to login to the database without any issues.
[oracle@xxxx admin]$ sqlplus sys@abcdb:1522/+ASM as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 9 02:30:40 2020 Version 19.6.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Enter password: ERROR: ORA-01017: invalid username/password; logon deniedI have to tried changing the password and still the error ORA-01017 occur.
I checked the password related parameter remote_login_passwordfile and it is set to EXCLUSIVE
Checked the password file whether it is present or not.
[oracle@xxxx ~]$ asmcmd pwget --asm +ABC_DATA_DG01/orapwasm
Cause:
This is the issue in my environment. We have recently changed the disk group names from +ABC_DATA_DG01 to +DATA as part of standardization. So the password file name didn't change after the disk name is changed. So we need to change the password file to take a new name with actual path. Use -f to force create the file otherwise the command will say already file exists as below
[oracle@xxxx ~]$ asmcmd pwcreate --asm '+DATA/orapwasm' Enter password: ******** OPW-00010: Could not create the password file. A password file with this name is present. ASMCMD-9454: could not create new password file [oracle@xxxx ~]$ asmcmd pwcreate --asm '+DATA/orapwasm' -f Enter password: ******** [oracle@xxxx ~]$ [oracle@xxxx ~]$ asmcmd pwget --asm +DATA/orapwasmYou can now see that the password file is changed to the proper name. Still the connection fails as below.
[oracle@xxxx ~]$ sqlplus sys@abcdb:1522/+ASM as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 9 03:26:52 2020 Version 19.6.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Enter password: ERROR: ORA-01017: invalid username/password; logon deniedNow if we see the alert log, though the password file location is changed Oracle still looks for the old non-existent file. Snippet from alert log is as below
... ... 2020-06-09T03:30:14.571451-07:00 Errors in file /oracle/grid/diag/asm/+asm/+ASM/trace/+ASM_ora_23542.trc: ORA-17503: ksfdopn:2 Failed to open file +ABC_DATA_DG01/orapwasm ORA-15001: diskgroup "ABC_DATA_DG01" does not exist or is not mounted ORA-06512: at line 4 ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 679 ORA-06512: at line 2 ORA-01017: invalid username/password; logon denied ... ...Solution:
In 19c, Oracle introduced a new clause for the ALTER SYSTEM command FLUSH PASSWORDFILE_METADATA_CACHE. So according to this clause if the location or the name of the password file changes, you must notify the database that a change has occurred. The command ALTER SYSTEM FLUSH PASSWORDFILE_METADATA_CACHE flushes the password file metadata cache stored in the SGA and informs the database that a change has occurred.
You may read more on all the ALTER SYSTEM commands in this link.
So once we do the flush, we are able to login to the database without any issues.
[oracle@xxxx ~]$ sqlplus / as sysasm SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 9 07:39:36 2020 Version 19.6.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.6.0.0.0 SQL> alter system flush PASSWORDFILE_METADATA_CACHE; System altered. SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.6.0.0.0 [oracle@xxxx ~]$ sqlplus sys@abcdb:1522/+ASM as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 9 07:40:46 2020 Version 19.6.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Enter password: Last Successful login time: Tue Jun 09 2020 07:31:21 -07:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.6.0.0.0 SQL>Happy flushing..
