LATEST TOPICS

Oracle database 12c (12.1) : Password file mystery

I was trying to implement password file authentication for my Oracle 12c database. However, seems like Oracle database was not recognizing the password file at all.

As shown below, I had created a password file ‘orapwprodcdb’ for my 12c Oracle database ‘prodcdb’.

[oracle@labserver ~]$ echo $ORACLE_SID
prodcdb

[oracle@labserver ~]$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle098 entries=20

[oracle@labserver ~]$ ls -lrt $ORACLE_HOME/dbs/orapw$ORACLE_SID
-rw-r----- 1 oracle dba 12800 Mar 27 23:39 /app/oracle/db/12.1.0.1/dbs/orapwprodcdb

Following is the CONNECT IDENTIFIER (TNS) for my database ‘prodcdb’.

[oracle@labserver ~]$ tnsping prodcdb

TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 27-MAR-2015 23:40:11

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
/app/oracle/db/12.1.0.1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = labserver.home.com)(PORT = 1525)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prodcdb)))
OK (0 msec)

However, when I try to use the password defined in the password file, it is throwing INVALID USERNAME/PASSWORD; LOGON DENIED error.

[oracle@labserver ~]$ sqlplus

SQL*Plus: Release 12.1.0.1.0 Production on Fri Mar 27 23:40:25 2015

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

Enter user-name: sys/oracle098@prodcdb as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:

To confirm the CONNECT IDENTIFIER is working fine, I tried to login as a different user and that worked fine.

[oracle@labserver ~]$ sqlplus

SQL*Plus: Release 12.1.0.1.0 Production on Fri Mar 27 23:41:05 2015

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

Enter user-name: c##abbas/abbas@prodcdb

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

c##abbas@PRODCDB> show con_name

CON_NAME
------------------------------
CDB$ROOT

After trying few times without any success, I had decided to take a look at the database configuration defined at the Clusterware level.

[oracle@labserver ~]$ srvctl config database -db prodcdb
Database unique name: prodcdb
Database name: prodcdb
Oracle home: /app/oracle/db/12.1.0.1
Oracle user: oracle
Spfile: +DATA/prodcdb/spfileprodcdb.ora
Password file: +DATA/PRODCDB/PASSWORD/pwdprodcdb.315.875489565
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Database instance: prodcdb
Disk Groups: DATA,FRA
Services:

So, here was the mystery behind the password file not being recognized by the database. As we can see from the database configuration, the database password file was defined at the the clusterware level as ‘+DATA/PRODCDB/PASSWORD/pwdprodcdb.315.875489565’ (I had created this password file during the exercise for one of my earlier post to demonstrate password file creation in ASM diskgroups) . This is why, the database was not considering the password file ‘orapwprodcdb’ created under $ORACLE_HOME/dbs location.

However, we can modify this database configuration at the Clusterware level to recognize the password located in the OS file system; by using SRVCTL MODIFY DATABASE command as follows.

[oracle@labserver ~]$ srvctl modify database -db prodcdb -pwfile '/app/oracle/db/12.1.0.1/dbs/orapwprodcdb'
[oracle@labserver ~]$ srvctl config database -db prodcdb
Database unique name: prodcdb
Database name: prodcdb
Oracle home: /app/oracle/db/12.1.0.1
Oracle user: oracle
Spfile: +DATA/prodcdb/spfileprodcdb.ora
Password file: /app/oracle/db/12.1.0.1/dbs/orapwprodcdb
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Database instance: prodcdb
Disk Groups: DATA,FRA
Services:

The parameter ‘pwfile’ for database configuration is a new parameter introduced in Oracle database 12c and it controls the password file to be used by a particular database.

Once, I had modified the configuration parameter at the Clusterware level; I was able to use the password file created in the OS file system.

[oracle@labserver ~]$ sqlplus

SQL*Plus: Release 12.1.0.1.0 Production on Fri Mar 27 23:44:23 2015

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

Enter user-name: sys/oracle098@prodcdb as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

sys@PRODCDB> show con_name

CON_NAME
------------------------------
CDB$ROOT

Just to check, if I need to adjust the setting for a new password file created under ASM diskgroup; I had again created a password file for the same database in ASM diskgroup ‘+FRA’

[oracle@labserver ~]$ orapwd file=+FRA dbuniquename=prodcdb password=oracle123 entries=20
[oracle@labserver ~]$ srvctl config database -db prodcdb
Database unique name: prodcdb
Database name: prodcdb
Oracle home: /app/oracle/db/12.1.0.1
Oracle user: oracle
Spfile: +DATA/prodcdb/spfileprodcdb.ora
Password file: +FRA/PRODCDB/PASSWORD/pwdprodcdb.424.875490331
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Database instance: prodcdb
Disk Groups: DATA,FRA
Services:

As we can see, we need not adjust the database setting at Clusterware level for a new password file created in ASM diskgroup. Password file from a ASM diskgroup automatically gets mapped to the database configuration at the Clusterware level due to the ‘dbuniquename’ parameter of the ‘orapwd’ utiltity, which is a mandatory parameter for creating password file in ASM diskgroup.

Since, the password file was automatically mapped to the database configuration at Clusterware level, I was able to use the new password file from ASM diskgroup for database authentication.

[oracle@labserver ~]$ sqlplus

SQL*Plus: Release 12.1.0.1.0 Production on Fri Mar 27 23:45:46 2015

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

Enter user-name: sys/oracle123@prodcdb as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

sys@PRODCDB> show con_name

CON_NAME
-------
CDB$ROOT

Conclusion

Oracle database 12c would lookup for password file under $ORACLE_HOME/dbs by default. If Clusterware is in place, we need to cross-check the value of parameter ‘Password file’ from database configuration to ensure the use of correct password file. If the value for parameter ‘Password file’ is not defined, default password file lookup under $ORACLE_HOME/dbs would be performed.

%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed