Changing SYS or DB User Password in RAC Database Environment

Below are the procedures for changing SYS or DB USER password in RAC Environment.

Step-1: At first view which users password will expiry soon.

Q-1: Following query will find user expiry date.

SQL> select profile,username, to_char(expiry_date, 'DD-MM-YYYY') EXP_DATE from dba_users where username in ('SYS','SYSTEM','DBSNMP','SYSMAN','MGMT_VIEW');

PROFILE                        USERNAME                       EXP_DATE

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

DEFAULT                        MGMT_VIEW                     01-08-2016

MONITORING_PROFILE    DBSNMP                           01-08-2016

DEFAULT                        SYSMAN                           01-08-2016

DEFAULT                        SYS                                  01-08-2016

DEFAULT                        SYSTEM                            01-08-2016

Q-2: Following query will profile default limit.

SQL> select profile,limit from dba_profiles where RESOURCE_NAME LIKE 'PASSWORD_LIFE_TIME';

PROFILE                        LIMIT

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

DEFAULT                        180

MONITORING_PROFILE             DEFAULT

Q-3: Following query will find password life time.

SQL> select * from user_password_limits;

RESOURCE_NAME                    LIMIT

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

FAILED_LOGIN_ATTEMPTS            10

PASSWORD_LIFE_TIME                  180

PASSWORD_REUSE_TIME               UNLIMITED

PASSWORD_REUSE_MAX                UNLIMITED

PASSWORD_VERIFY_FUNCTION     NULL

PASSWORD_LOCK_TIME                1

PASSWORD_GRACE_TIME              7

Step-2: Changing USERS password.

On each node, replace the word password with your desired password.

Task-1: On node1, do the following...

oracle@node1 [/home/oracle]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 19 12:43:02 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL>

SQL> alter user SYS identified by test123 account unlock;

User altered.

SQL> alter user SYSTEM identified by test123 account unlock;

User altered.

SQL> alter user SYSMAN identified by test123 account unlock;

User altered.

SQL> alter user MGMT_VIEW identified by test123 account unlock;

User altered.

SQL> alter user DBSNMP identified by test123 account unlock;

User altered.

Task-2: On node2, do the following...

oracle@node2 [/home/oracle]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 19 12:43:02 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL>

SQL> alter user SYS identified by test123 account unlock;

User altered.

SQL> alter user SYSTEM identified by test123 account unlock;

User altered.

SQL> alter user SYSMAN identified by test123 account unlock;

User altered.

SQL> alter user MGMT_VIEW identified by test123 account unlock;

User altered.

SQL> alter user DBSNMP identified by test123 account unlock;

User altered.