Managing Password file in Oracle Database 12c R1 ASM Disk Group

Until Oracle Database 11g password file of Oracle Database and ASM instance had to be stored in regular filesystem of windows or unix. However, starting from Oracle Database 12c R1 Oracle Database and ASM instance password file can be stored in ASM storage. This gives a great advantage in terms of ease of management of password file in Real Application Clusters (RAC) environment. When using ASM, in earlier versions of RAC, password files had to be stored in individual servers where RAC instances are running. For RAC environments password file can be stored in shared ASM disk group. For this, the compatible.asm disk group attribute must be set to 12.1 or higher for the disk group where the password is to be stored. The SYSASM or SYSDBA privilege is required to manage the Oracle ASM and database password files.

Now we will look in various commands used to manage password file in ASM disk group. Starting from grid infrastructure (GI) 12c R1, during the installation when ASM disk group is created GI creates the ASM password file in ASM disk group. I created +DATA_DISK01 ASM disk group so the ASM password file has been created in +DATA_DISK01/ASM/PASSWORD/pwdasm.256.824761009 and the corresponding alias has been created in +DATA_DISK01/orapwasm.

Check the location of password file in ASM disk group:

[grid@db12c-1 ~]$ id -a

uid=64322(grid) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54328(asmdba),54329(asmoper),54330(asmadmin) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

[grid@db12c-1 ~]$ asmcmd

ASMCMD> ls -l

State    Type    Rebal  Name

MOUNTED  NORMAL  N      DATA_DISK01/

MOUNTED  EXTERN  N      DATA_DISK02/

MOUNTED  EXTERN  N      FRA_DISK01/

ASMCMD> cd DATA_DISK01

ASMCMD> pwd

+DATA_DISK01

ASMCMD> ls -l

Type      Redund  Striped  Time             Sys  Name

                                            Y    ASM/

PASSWORD  HIGH    COARSE   AUG 29 20:00:00  N    orapwasm => +DATA_DISK01/ASM/PASSWORD/pwdasm.256.824761009

ASMCMD>

Let’s discuss about the following commands to manage password file in ASM disk group.

ASMCMD commands to manage password file

1. lspwusr

Lists the users from an Oracle ASM password file. Not valid for regular Oracle database.

Syntax:

--**********************************************

lspwusr [--suppressheader]

Example:

--**********************************************

[grid@db12c-1 ~]$ id -a

uid=64322(grid) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54328(asmdba),54329(asmoper),54330(asmadmin) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

[grid@db12c-1 ~]$

[grid@db12c-1 ~]$ asmcmd

ASMCMD> lspwusr

Username sysdba sysoper sysasm

     SYS   TRUE    TRUE   TRUE

 ASMSNMP   TRUE   FALSE  FALSE

ASMCMD>

ASMCMD> lspwusr --suppressheader

     SYS   TRUE    TRUE  FALSE

 ASMSNMP  FALSE   FALSE   TRUE 

2. orapwusr

Adds, drops, or changes an Oracle ASM password user. Not valid for regular Oracle database.

Syntax:

--**********************************************

orapwusr { { { --add | --modify [--password] } [--privilege {sysasm|sysdba|sysoper} ] } | --delete } user

Example:

--**********************************************

--Add user asmsysdba with sysasm privilege in password file.

[grid@db12c-1 ~]$ id -a

uid=64322(grid) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54328(asmdba),54329(asmoper),54330(asmadmin) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

[grid@db12c-1 ~]$ asmcmd

ASMCMD>

ASMCMD>

ASMCMD> orapwusr --add --privilege sysasm asmsysdba

Enter password: *********

ASMCMD>

--Check if the user has been added in the password file.

ASMCMD>

ASMCMD> lspwusr

 Username sysdba sysoper sysasm

      SYS   TRUE    TRUE  FALSE

  ASMSNMP   TRUE   FALSE  FALSE

ASMSYSDBA  FALSE   FALSE   TRUE

ASMCMD>

--Delete the added user from password file.

ASMCMD>

ASMCMD> orapwusr --delete asmsysdba

ASMCMD> lspwusr

Username sysdba sysoper sysasm

     SYS   TRUE    TRUE  FALSE

 ASMSNMP   TRUE   FALSE  FALSE

ASMCMD>

--Modify the password of asmsnmp user.

ASMCMD>

ASMCMD> orapwusr --modify --password asmsnmp

Enter password: *********

ASMCMD>

--Change the privilege of asmsnmp user from sysdba to sysasm.

ASMCMD>

ASMCMD> orapwusr --modify --privilege  sysasm asmsnmp

ASMCMD> lspwusr

Username sysdba sysoper sysasm

     SYS   TRUE    TRUE  FALSE

 ASMSNMP  FALSE   FALSE   TRUE

ASMCMD>

3. pwcopy

Copies a password file to the specified location.

Syntax:

--**********************************************

pwcopy {--asm |--dbuniquename string} source destination

For ASM:

Copying ASM password file from ASM disk group to operating system’s file system.

[grid@db12c-1 ~]$ id -a

uid=64322(grid) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54328(asmdba),54329(asmoper),54330(asmadmin) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

[grid@db12c-1 ~]$ asmcmd

ASMCMD>

ASMCMD> pwcopy --asm +DATA_DISK01/orapwasm /u01/app/grid/product/12.1.0.1/grid/dbs/orapwasm

copying +DATA_DISK01/orapwasm -> /u01/app/grid/product/12.1.0.1/grid/dbs/orapwasm

ASMCMD-9456: password file should be located on an ASM disk group

ASMCMD>

Copying ASM password file from operating system’s file system to ASM disk group.

[grid@db12c-1 ~]$ id -a

uid=64322(grid) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54328(asmdba),54329(asmoper),54330(asmadmin) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

[grid@db12c-1 ~]$ asmcmd

ASMCMD>

ASMCMD> pwcopy --asm /u01/app/grid/product/12.1.0.1/grid/dbs/orapwasm +DATA_DISK01/orapwasm

copying /u01/app/grid/product/12.1.0.1/grid/dbs/orapwasm -> +DATA_DISK01/orapwasm

ASMCMD>

For regular database:

Copying ASM password file from ASM disk group to operating system’s file system.

[grid@db12c-1 ~]$ id -a

uid=64322(grid) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54328(asmdba),54329(asmoper),54330(asmadmin) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

[grid@db12c-1 ~]$ asmcmd

ASMCMD>

ASMCMD> pwcopy --dbuniquename noncdb +DATA_DISK01/orapwnoncdb /u01/app/oracle/product/12.1.0.1/db_1/dbs/orapwnoncdb

copying +DATA_DISK01/orapwnoncdb -> /u01/app/oracle/product/12.1.0.1/db_1/dbs/orapwnoncdb

ASMCMD-9456: password file should be located on an ASM disk group

ASMCMD>

Copying ASM password file from ASM disk group to operating system’s file system.

[grid@db12c-1 ~]$ id -a

uid=64322(grid) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54328(asmdba),54329(asmoper),54330(asmadmin) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

[grid@db12c-1 ~]$ asmcmd

ASMCMD>

ASMCMD> pwcopy --dbuniquename noncdb /u01/app/oracle/product/12.1.0.1/db_1/dbs/orapwnoncdb +DATA_DISK01/orapwnoncdb

copying /u01/app/oracle/product/12.1.0.1/db_1/dbs/orapwnoncdb -> +DATA_DISK01/orapwnoncdb

ASMCMD>

NOTE:

In case the permission of directory $ORACLE_HOME/dbs has not been set properly then following error could occur.

ASMCMD> pwcopy --dbuniquename noncdb +DATA_DISK01/orapwnoncdb /u01/app/oracle/product/12.1.0.1/db_1/dbs/orapwnoncdb

ASMCMD-9463: operation failed due to lack of write permissions

ASMCMD>

In order to resolve the issue login as oracle user and change the permission of $ORACLE_HOME/dbs folder. Grant write access to the group:

[oracle@db12c-1 db_1]$cd $ORACLE_HOME/dbs

[oracle@db12c-1 db_1]$ pwd

/u01/app/oracle/product/12.1.0.1/db_1

[oracle@db12c-1 db_1]$

[oracle@db12c-1 db_1]$ chmod g+w dbs

[oracle@db12c-1 db_1]$ ls –l

4. pwcreate

Creates a password file at the specified location.

Syntax:

--**********************************************

pwcreate { --asm |--dbuniquename string } file_path sys_password

The compatible.asm disk group attribute must be set to 12.1 or higher for the disk group where the password is to be located. The SYSASM or SYSDBA privilege is required to manage the Oracle ASM and database password files.

Example:

--**********************************************

For ASM:

[grid@db12c-1 ~]$ id -a

uid=64322(grid) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54328(asmdba),54329(asmoper),54330(asmadmin) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

[grid@db12c-1 ~]$ asmcmd

ASMCMD>

ASMCMD>  pwcreate --asm +DATA_DISK01/orapwasm oracle_4U

ASMCMD>

For regular database:

[grid@db12c-1 ~]$ id -a

uid=64322(grid) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54328(asmdba),54329(asmoper),54330(asmadmin) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

[grid@db12c-1 ~]$ asmcmd

ASMCMD>

ASMCMD> pwcreate --dbuniquename noncdb +DATA_DISK01/orapwnoncdb oracle_4U

ASMCMD>

5. pwdelete

Deletes a password file at the specified location.

Syntax:

--**********************************************

pwdelete { --asm |--dbuniquename string | file_path }

pwdelete deletes the specified password file. Either –-asm or --dbuniquename is required to identify a CRSD resource and to remove the password location from the CRSD resource.

The SYSASM or SYSDBA privilege is required to manage the Oracle ASM and database password files.

For ASM:

Example:

--**********************************************

Specify only the ASM database type to delete password file.

[grid@db12c-1 ~]$ id -a

uid=64322(grid) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54328(asmdba),54329(asmoper),54330(asmadmin) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

[grid@db12c-1 ~]$ asmcmd

ASMCMD>

ASMCMD> pwdelete –asm

ASMCMD>

Specify only the location of the password file to delete.

[grid@db12c-1 ~]$ id -a

uid=64322(grid) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54328(asmdba),54329(asmoper),54330(asmadmin) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

[grid@db12c-1 ~]$ asmcmd

ASMCMD>

ASMCMD> pwdelete +DATA_DISK01/orapwasm

ASMCMD>

For regular database:

Specify only the database unique name for regular database to delete password file.

[grid@db12c-1 ~]$ id -a

uid=64322(grid) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54328(asmdba),54329(asmoper),54330(asmadmin) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

[grid@db12c-1 ~]$ asmcmd

ASMCMD>

ASMCMD> pwdelete --dbuniquename noncdb

ASMCMD>

Specify only the location of the password file to delete.

[grid@db12c-1 ~]$ id -a

uid=64322(grid) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54328(asmdba),54329(asmoper),54330(asmadmin) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

[grid@db12c-1 ~]$ asmcmd

ASMCMD>

ASMCMD>  pwdelete +DATA_DISK01/orapwnoncdb

ASMCMD>

6. pwget

Returns the location of the password file.

pwget returns the location of the password file for the Oracle ASM instance identified by –-asm or the database instance identified by --dbuniquename.

The SYSASM or SYSDBA privilege is required to manage the Oracle ASM and database password files.

Syntax:

--**********************************************

pwget { --asm | --dbuniquename string }

Example:

--**********************************************

For ASM:

[grid@db12c-1 ~]$ id -a

uid=64322(grid) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54328(asmdba),54329(asmoper),54330(asmadmin) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

[grid@db12c-1 ~]$ asmcmd

ASMCMD>

ASMCMD> pwget --asm       

+DATA_DISK01/orapwasm

ASMCMD>

For regular database:

[grid@db12c-1 ~]$ id -a

uid=64322(grid) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54328(asmdba),54329(asmoper),54330(asmadmin) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

[grid@db12c-1 ~]$ asmcmd

ASMCMD>

ASMCMD> pwget --dbuniquename noncdb

+DATA_DISK01/orapwnoncdb

ASMCMD>

7. pwmove

Moves the location of the password file.

pwmove moves a password file from one disk group to another, from the operating system to a disk group, or from a disk group to the operating system.

Either –-asm or --dbuniquename is required to identify a CRSD resource. The compatible.asm disk group attribute must be set to 12.1 or higher for the disk group where the password is to be moved.

The SYSASM or SYSDBA privilege is required to manage the Oracle ASM and database password files.

Syntax:

--**********************************************

pwmove { --asm | --dbuniquename string } source destination

Example:

--**********************************************

For ASM:

Move password file from ASM disk group to operating system file system.

[grid@db12c-1 ~]$ id -a

uid=64322(grid) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54328(asmdba),54329(asmoper),54330(asmadmin) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

[grid@db12c-1 ~]$ asmcmd

ASMCMD>

ASMCMD> pwmove --asm +DATA_DISK01/orapwasm /u01/app/grid/product/12.1.0.1/grid/dbs/orapwasm

moving +DATA_DISK01/orapwasm -> /u01/app/grid/product/12.1.0.1/grid/dbs/orapwasm

ASMCMD>

Move password file from operating system file system to ASM disk group.

[grid@db12c-1 ~]$ id -a

uid=64322(grid) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54328(asmdba),54329(asmoper),54330(asmadmin) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

[grid@db12c-1 ~]$ asmcmd

ASMCMD>

ASMCMD> pwmove --asm /u01/app/grid/product/12.1.0.1/grid/dbs/orapwasm +DATA_DISK01/orapwasm

moving /u01/app/grid/product/12.1.0.1/grid/dbs/orapwasm -> +DATA_DISK01/orapwasm

ASMCMD>

For regular database:

Move password file from ASM disk group to operating system file system.

[grid@db12c-1 ~]$ id -a

uid=64322(grid) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54328(asmdba),54329(asmoper),54330(asmadmin) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

[grid@db12c-1 ~]$ asmcmd

ASMCMD>

ASMCMD> pwmove --dbuniquename noncdb +DATA_DISK01/orapwnoncdb /u01/app/oracle/product/12.1.0.1/db_1/dbs/orapwnoncdb

moving +DATA_DISK01/orapwnoncdb -> /u01/app/oracle/product/12.1.0.1/db_1/dbs/orapwnoncdb

ASMCMD>

Move password file from operating system file system to ASM disk group.

[grid@db12c-1 ~]$ id -a

uid=64322(grid) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54328(asmdba),54329(asmoper),54330(asmadmin) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

[grid@db12c-1 ~]$ asmcmd

ASMCMD>

ASMCMD> pwmove --dbuniquename noncdb /u01/app/oracle/product/12.1.0.1/db_1/dbs/orapwnoncdb +DATA_DISK01/orapwnoncdb 

moving /u01/app/oracle/product/12.1.0.1/db_1/dbs/orapwnoncdb ->  +DATA_DISK01/orapwnoncdb

ASMCMD>

8.  pwset

Sets the location of the password file.

pwset sets the location of the password file for an Oracle ASM or database instance to the value specified by file_path. Either --dbuniquename or –-asm is required to identify a CRSD resource.

The SYSASM or SYSDBA privilege is required to manage the Oracle ASM and database password files.

Syntax:

--**********************************************

pwset { --asm | --dbuniquename string } file_path

Example:

--**********************************************

Set password file to operating system file system.

[grid@db12c-1 ~]$ id -a

uid=64322(grid) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54328(asmdba),54329(asmoper),54330(asmadmin) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

[grid@db12c-1 ~]$ asmcmd

ASMCMD>

ASMCMD> pwset --asm /u01/app/grid/product/12.1.0.1/grid/dbs/orapwasm

ASMCMD-9456: password file should be located on an ASM disk group

ASMCMD>

Set password file to ASM disk group.

[grid@db12c-1 ~]$ id -a

uid=64322(grid) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54328(asmdba),54329(asmoper),54330(asmadmin) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

[grid@db12c-1 ~]$ asmcmd

ASMCMD>

ASMCMD> pwset --asm +DATA_DISK01/orapwasm

ASMCMD>

For regular database:

Set password file to operating system file system.

[grid@db12c-1 ~]$ id -a

uid=64322(grid) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54328(asmdba),54329(asmoper),54330(asmadmin) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

[grid@db12c-1 ~]$ asmcmd

ASMCMD>

ASMCMD> pwset --dbuniquename noncdb /u01/app/oracle/product/12.1.0.1/db_1/dbs/orapwnoncdb

ASMCMD-9456: password file should be located on an ASM disk group

ASMCMD>

Set password file to ASM disk group.

[grid@db12c-1 ~]$ id -a

uid=64322(grid) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54328(asmdba),54329(asmoper),54330(asmadmin) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

[grid@db12c-1 ~]$ asmcmd

ASMCMD>

ASMCMD> pwset --dbuniquename noncdb +DATA_DISK01/orapwnoncdb

ASMCMD>

References: 

http://docs.oracle.com/cd/E16655_01/server.121/e17612/asm_util002.htm#CIHIBJCF

Hope this helps :).