Storing password file in ASM diskgroup

Starting with Oracle Database 12c, we can now store a password file directly into ASM Diskgroup. Also, Oracle has made it default for a password to be case sensitive in the password file.

As we know, ORAPWD is the utility to a create password file. Lets take a look at the syntax of ORAPWD in Oracle Database 12c. Syntax for creating password file:

orapwd file=<fname> entries=<users> force=<y/n> asm=<y/n> password=<SYS password> dbuniquename=<dbname> format=<legacy/12> sysbackup=<y/n> sysdg=<y/n> syskm=<y/n> delete=<y/n> input_file=<input-fname>

There are additional command-line options for ORAPWD when compared to the pre-12c database release. With the below steps, we can store password files in the ASM disk group for both the standalone and RAC database.

Storing the password file in ASM Diskgroup for Standalone Database

While creating a password file in the ASM disk group, we always need to mention the dbuniquename parameter of the ORAPWD utility. It is the database unique name by means of which Oracle maps a password file to a specific database.

$ . oraenv
$ ORACLE_SID = [XXX] ? orcldb
$ orapwd file='+DATA' entries=10 dbuniquename=orcldb password=sysorcldb

Now, let's take a look at the created password file. Oracle will create the password file with OMF standards and it will be used for validating logins through password files.

ASMCMD> cd +DATA
ASMCMD> cd ORCLDB
ASMCMD> ls -l
Type           Redund  Striped  Time             Sys  Name
                                                 Y    CONTROLFILE/
                                                 Y    DATAFILE/
                                                 Y    ONLINELOG/
                                                 Y    PARAMETERFILE/
                                                 Y    PASSWORD/
                                                 Y    TEMPFILE/
ASMCMD> cd PASSWORD
ASMCMD> ls -l                                             
PASSWORD       HIGH    COARSE   AUG 23 09:00:00  N    pwdorcldb.283.861404835

Now, check if we are able to connect using the password file.

$ sqlplus
Enter user-name: sys/sysorcldb@orcldb 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
 
SQL>

Storing the password file in ASM Diskgroup for RAC Database

Prior to 12c, we had to create and maintain individual password files for each of the RAC database instances. Adding a new user in the password file required either copy the password file from the instance where the user was being added to the remaining instances or adding the user in the password file from each RAC database instance.
Let's create a centralized password file for a two-node RAC database ‘racdb’ in the ASM disk group.

$ . oraenv
$ ORACLE_SID = [XXX] ? racdb1
$ orapwd file='+DATA' entries=10 dbuniquename=racdb password=sysproddb 

Let's validate the password file from ASMCMD

ASMCMD> cd +DATA
ASMCMD> cd racdb
ASMCMD> ls -l
Type           Redund  Striped  Time             Sys  Name
                                                 Y    CONTROLFILE/
                                                 Y    DATAFILE/
                                                 Y    ONLINELOG/
                                                 Y    PARAMETERFILE/
                                                 Y    PASSWORD/
                                                 Y    TEMPFILE/

ASMCMD> cd PASSWORD
ASMCMD> ls -l
PASSWORD      HIGH    COARSE    AUG 23 14:00:00  N    pwdracdb.294.862404783 

Let's query the password file users from the database.

SQL> select * from v$pwfile_users;
 
USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0

As of now, we have just the SYS user in the password file. Let's create a user and grant it a SYSDBA role from the first cluster instance.

SQL>  select instance_name,host_name from v$instance;

INSTANCE_NAME    HOST_NAME

---------------- --------------------

racdb1           rac-node1

SQL> create user jay identified by jay;

User created.

SQL> grant sysdba to jay;

Grant succeeded

Now validate if we can see the user ‘JAY’ in the password file from both the cluster instances.

From rac-node1
SQL>  select instance_name,host_name from v$instance;
 
INSTANCE_NAME    HOST_NAME
---------------- --------------------
racdb1           rac-node1
 
SQL> select * from v$pwfile_users;
 
USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0
JAY                            TRUE  FALSE FALSE FALSE FALSE FALSE          0
  
From  rac-node2
SQL>  select instance_name,host_name from v$instance;
 
INSTANCE_NAME    HOST_NAME
---------------- --------------------
racdb2           rac-node2 
 
SQL> select * from v$pwfile_users;
 
USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0
JAY                            TRUE  FALSE FALSE FALSE FALSE FALSE          0

CONCLUSION

Ø   The password file is in ASM Diskgroup; it is secured against unintentional and unauthorized access and file corruption.

Ø   For a Cluster (RAC) database, we can have a centralized copy of the password file for all the RAC instances without the need of maintaining individual password file for each RAC instance.

Ø   The dbuniquename parameter facilitates creating a single centralized password file for all the RAC instances.