How to Change the Password for SYSASM User

    Nov 7, 2017 12:04:06 PM by Skant Gupta

    In the first 11g release, it is still possible to logon as sysdba as well as sysasm. This will definitely be changed with the next coming release, 11gR2. At present, when Oracle 11g is installed a question will come up about the operating system group, which permits logon to an ASM instance without needing a password in addition to that of the OS group’s for logon as sysdba or as sysoper. The access to remote ASM instances is managed through the password file of the ASM instance. This password is case sensitive, as all passwords are in an 11g database.

    By logging on as sysasm the user can maintain the ASM instance. The main idea is to separate storage administrator and database administrator responsibilities. We cannot change the password for ASM databases via the alter user command.

    Let’s demonstrate.

    First we can try changing the password using the normal method:    

    SQL> select INSTANCE_NAME from v$instance;
    INSTANCE_NAME
    —————-
    +ASM
    SQL> ALTER USER sys IDENTIFIED BY <new_password> REPLACE <old_password>;
    ALTER USER sys IDENTIFIED BY <new_password> REPLACE <old_password>
    *
    ERROR at line 1:
    ORA-01109: database not open

    We cannot change the password for any user in ASM databases via the alter user command because passwords are managed through the password file in ASM Instance. The password should be the one provided when the password file was created; also the REMOTE_LOGIN_PASSWORDFILE should be set to EXCLUSIVE on all instances.

    The only way to change the password would be to recreate the password file using the orapwd utility or to create another ASM DBA user to manage the ASM instance. Remember, only one default user [sys] will be present in ASM instance. Now, what if a DBA decided to create another user to manage an ASM instance? Here are my tries:

    SQL> create user asmdba identified by test01;
    create user asmdba identified by test01
    *
    ERROR at line 1:
    ORA-15306: ASM password file update failed on at least one node
    SQL> create user asmdba identified by test01;
    create user asmdba identified by test01
    *
    ERROR at line 1:
    ORA-15306: ASM password file update failed on at least one node

    The ORA-15306 error demonstrates that the ASM password file is corrupt. Now remove all the ASM password files from each node. Create the ASM password file manually on the first node and copy it to other nodes.

    To reset sysasm user password with asmcmd utility, do the following:

    [oracle@r1n1 ~]$ . oraenv
    ORACLE_SID = [orcl1] ? +ASM1
    The Oracle base for ORACLE_HOME=/u01/app/11.2.0/grid is /u01/app/oracle
    [oracle@r1n1 ~]$ asmcmd
    ASMCMD> orapwusr –modify –password sys
    Enter password: ********

    If you want to change the password, then you would need to recreate the password file using the orapwd utility.

    Recreate the password file for the ASM instance as follows:

    Unix:

    orapwd file=<ORACLE_HOME>/dbs/PWD<SID> password=<sys_password>

    Windows:

    orapwd file=<ORACLE_HOME>/database/PWD<SID>.ora password=<sys_password>

    Now the sys password is reset, we are ready to use sys for ASM management. I decided to create another user. ASMDBA, as I tried above.

    SQL> create user ASMDBA identified by test01;
     
    User created.
     
    SQL> grant SYSASM, SYSOPER to ASMDBA;
     
    Grant succeeded.
     
    SQL> select * from v$pwfile_users;
     
    USERNAME SYSDBA SYSOPE SYSASM
    —————————— —— —— ——
    SYS TRUE TRUE TRUE
    ASMDBA FALSE TRUE TRUE
    SQL> create user ASMDBA identified by test01;
     
    User created.
     
    SQL> grant SYSASM, SYSOPER to ASMDBA;
     
    Grant succeeded.
     
    SQL> select * from v$pwfile_users;
     
    USERNAME SYSDBA SYSOPE SYSASM
    —————————— —— —— ——
    SYS TRUE TRUE TRUE
    ASMDBA FALSE TRUE TRUE

    Remember there are no DBA views you can access in ASM and only way to change the password is through the orapwd utility.

    Tags: Oracle

    Skant Gupta

    Written by Skant Gupta

    Skant Gupta is an Oracle Certified Cloud Professional in Oracle Database 12c, an Oracle Certified Expert in Oracle Real Application Clusters (Oracle RAC) in Oracle Database 11g and 12c, and an Oracle Exadata Certified and an Oracle Certified Professional in Oracle Database 10g, 11g, and 12c. He works at Vodafone Technology in the UK and formerly worked as a senior DBA at Etisalat in Dubai. He has six years of experience with various Oracle technologies, focusing mainly on Cloud, database, and high availability solutions, Oracle WebLogic Suite, Oracle Exadata and Oracle GoldenGate. He has presented at several Oracle user groups worldwide, most recently in the US, the United Arab Emirates, and the India. He is also Technical Writer on http://www.oracle.com/technetwork/es/articles/index.html and http://www.oracle.com/technetwork/pt/articles/index.html with more than 30 article published in Oracle Technical Network.