Wednesday, September 4, 2013

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: 

Hope this helps :).





11 comments:

  1. Hi Ashish
    Thanks Much for the post!
    i got clear idea passwordfile management in 12c

    ReplyDelete
    Replies
    1. Insight Into Oracle: Managing Password File In Oracle Database 12C R1 Asm Disk Group >>>>> Download Now

      >>>>> Download Full

      Insight Into Oracle: Managing Password File In Oracle Database 12C R1 Asm Disk Group >>>>> Download LINK

      >>>>> Download Now

      Insight Into Oracle: Managing Password File In Oracle Database 12C R1 Asm Disk Group >>>>> Download Full

      >>>>> Download LINK A8

      Delete
  2. Hi
    Many thanks for your effort, but I think "Move password file from operating system file system to ASM disk group." example needs to modify source and destination.
    Thanks
    Mohamed Amin

    ReplyDelete
    Replies
    1. Hi Mohamed,

      Really appreciate your feedback, mentioned point has been updated.

      Best Regards,
      Ashish Man Baisyet

      Delete
  3. Problem occours only on node1, any idea how to resolve?

    Errors in file /dbprog/oracle/diag/asm/+asm/+ASM1/trace/+ASM1_ora_264657.trc:
    ORA-17503: ksfdopn:2 Failed to open file +DBFS_DG/orapwasm
    ORA-29701: unable to connect to Cluster Synchronization Service

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. Hi Akas,

      Check if your cluster services are running in node 1 and that the disk group is mounted.

      What does your alert log and the trace file say?

      Let me know if it helped.

      Best Regards,
      Ashish Man Baisyet

      Delete
  4. Can you help to solve this issue

    ERROR:
    ORA-01017: invalid username/password; logon denied
    ORA-17503: ksfdopn:2 Failed to open file
    +DB_ORCL_DAT0445/ETXA2/PASSWORD/pwdetxa2.564.979900909
    ORA-01017: invalid username/password; logon denied

    ReplyDelete
  5. Handle on your Oracle Internal Code Errors with Cognegic's Online Oracle DB Support
    In MS SQL Server you will discover one most ordinary and happening issue i.e. General Network Error or it is correspondingly called Communication interface thwarted expectation when an application identified with SQL Server. This sort of screw up happens unequivocally when running expansive database tries. To confine this issue you need to check the present number of TCP affiliations that the server right now has. In any case, on the off chance that you are not set up to research this by communicating by at that point contact to Cognegic's Microsoft SQL Server Support or Remote Infrastructure Management Support for Microsoft SQL Server System gives centrality strategy concerning this issue.
    For More Info: https://cognegicsystems.com
    Contact Number: 1-800-450-8670
    Email Address- info@cognegicsystems.com

    ReplyDelete
  6. Insight Into Oracle: Managing Password File In Oracle Database 12C R1 Asm Disk Group >>>>> Download Now

    >>>>> Download Full

    Insight Into Oracle: Managing Password File In Oracle Database 12C R1 Asm Disk Group >>>>> Download LINK

    >>>>> Download Now

    Insight Into Oracle: Managing Password File In Oracle Database 12C R1 Asm Disk Group >>>>> Download Full

    >>>>> Download LINK TJ

    ReplyDelete