Looking for something? Try here..

Tuesday, June 9, 2020

PASSWORDFILE_METADATA_CACHE

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
[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 denied 
I 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/orapwasm

You 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 denied

Now 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.. 

No comments:

Post a Comment