SQL> select INSTANCE_NAME from v$instance;
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 can not change the password for ASM databases via alter user command.Because passwords are managed through password file in ASM. The password should be the one provided when the password file was created,also REMOTE_LOGIN_PASSWORDFILE should be set to EXCLUSIVE on all instances.
So only way to change password would be recreating the password file using orapwd utility or create another ASM DBA user to manage ASM instance. Remember only one default user [sys] will be present in ASM instance. Now if DBA decided to create another user to manage 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
The ORA-15306 error demonstrates that the ASM password file is corrupt. Now remove all the ASM password file from each node. Create the ASM password file manually on the first node and copy to other nodes
Recreate the password file for the ASM instance as follows:
orapwd file=<ORACLE_HOME>/dbs/PWD<SID> password=<sys_password>
orapwd file=<ORACLE_HOME>/database/PWD<SID>.ora password=<sys_password>
Now 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;
SQL> grant SYSASM, SYSOPER to ASMDBA;
SQL> select * from v$pwfile_users;
USERNAME SYSDBA SYSOPE SYSASM
—————————— —— —— ——
SYS TRUE TRUE TRUE
ASMDBA FALSE TRUE TRUE
Remember there is no DBA views you can access in ASM and only way to change password is through orapwd utility.