Code: Select all
-- |-----------------------------------------------------------------------------|
-- | Changing SYS Password |
-- | In A RAC/DG Environment |
-- |-----------------------------------------------------------------------------|
-- | DATABASE : Oracle Database 11gR2 |
-- | PURPOSE : This guide will help you on how to change the password for |
-- | user SYS in a RAC/DG environment |
-- | NOTE : As with any manual, ensure to test properly in a development |
-- | environment before attempting to perform it in PRODUCTION. |
-- +-----------------------------------------------------------------------------+
Primary: orcldb1@hostq01, orcldb2@hostq02, orcldb3@hostq24
Standby: dgorcldb1@hostq03, dgorcldb2@hostq04
1. Login to the Master Node of a Primary Database with your personal account.
2. Swith user to "oracle".
[dba00@hostq01 ~]$ sudo su - oracle
[sudo] password for dba00:
3. Check the member nodes of a Cluster for a certain database.
[oracle@hostq01 ~]$ srvctl status database -d orcldb
Note: From the output above, the are three (3) nodes for orcldb on a primary site.Instance orcldb1 is running on node hostq01
Instance orcldb2 is running on node hostq02
Instance orcldb3 is running on node hostq24
4. Login to each node and switch user to "oracle". Login to instance as sysdba and then alter SYS user for its new password.
[oracle@hostq01 ~]$ sqlplus '/as sysdba'
SQL> alter user sys identified by "[newpassword]";
[oracle@hostq02 ~]$ sqlplus '/as sysdba'
SQL> alter user sys identified by "[newpassword]";
[oracle@hostq24 ~]$ sqlplus '/as sysdba'
SQL> alter user sys identified by "[newpassword]";
Note: a) At this point, SYS password is already altered at DB level.
Take note that in a Data Guard environment, password file on Primary and all its Standby
databases must be the same with the parameter remote_login_passwordfile=EXCLUSIVE or SHARED.
SQL> show parameter remote_login_passwordfile
Code: Select all
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVERecreating it using the orapwd tool on another node in Primary or in any of the nodes in Standby
will NOT work because the encryption does not match.
5. From Master Node of the Primary site, copy the password file to all member nodes in a cluster
with the filename format: $ORACLE_HOME/dbs/orapw<SID>
[oracle@hostq01 ~]$ cd $ORACLE_HOME/dbs
[oracle@hostq01 dbs]$ ls -ltrh orapworcldb1
Code: Select all
-rw-r----- 1 oracle oinstall 2.0K Feb 1 01:50 orapworcldb1[oracle@hostq01 dbs]$ scp orapworcldb1 oracle@hostq24:/appl/oracle/product/11.2.0/dbs/orapworcldb3
Note: At this point, the new password for SYS for all nodes on Primary site is now the same, including the password file.
6. From the Master Node of the Standby site, check the member nodes of a Cluster.
Login on hostq03 and switch user to "oracle".
[dba00@hostq03 ~]$ sudo su - oracle
[sudo] password for dba00:
[oracle@hostq03 ~]$ srvctl status database -d dgorcldb
Note: a) From above, we have two (2) nodes in a cluster for our Standby site.Instance dgorcldb1 is running on node hostq03
Instance dgorcldb2 is running on node hostq04
b) We cannot alter SYS password via SQL*Plus on Standby site as it is in MOUNTED or READ ONLY status.
Update to the password file on the primary database is not automatically propagated
to the standby database(s). To do this, proceed to Step 7.
c) As mentioned in 4-b, re-creating the password file on the standby database is not the solution.
The reason for this is the way the passwords are encrypted in the password file. Even if the
passwords are the same, the result of the encryption is not.
7. Go back to Master Node of the Primary site. Copy the password file to all member nodes in a cluster
in your Standby site. The filename format for the destination file would be: $ORACLE_HOME/dbs/orapw<SID>
[oracle@hostq01 ~]$ cd $ORACLE_HOME/dbs
[oracle@hostq01 dbs]$ ls -ltrh orapworcldb1
[oracle@hostq01 dbs]$ scp orapworcldb1 oracle@hostq03:$ORACLE_HOME/dbs/orapwdgorcldb1
[oracle@hostq01 dbs]$ scp orapworcldb1 oracle@hostq04:$ORACLE_HOME/dbs/orapwdgorcldb2
Note: At this point, we now have the same password file across all nodes in Primary and Standby Sites.
8. Verify that redo logshipping is working.
a) From Primary site, LOG_ARCHIVE_DEST_* should be valid after manually performing a log switch.
SQL> alter system switch logfile;
SET lines 999 col DEST_NAME FOR a20
SELECT dest_name,
status,
error
FROM v$archive_dest
WHERE status <> 'INACTIVE';
Code: Select all
DEST_NAME STATUS ERROR
-------------------- --------- --------------------------
LOG_ARCHIVE_DEST_1 VALID
LOG_ARCHIVE_DEST_2 VALIDhas the error: "ORA-01031: insufficient privileges"
then, there is something wrong with the password file. Review the actions taken.
b) On Primary, verify the latest log sequence generated.
SQL>
SELECT thread#,
MAX(sequence#) "Last Primary Seq Generated"
FROM v$archived_log
WHERE first_time BETWEEN (sysdate-1) AND (sysdate+1)
GROUP BY thread#
ORDER BY 1;
Code: Select all
THREAD# Last Primary Seq Generated
---------- --------------------------
1 1842
2 1750
3 1669c.) From STANDBY SITE:
SQL>
SELECT a.thread#,
a.Last_Seq_Received,
b.Last_Seq_Applied,
a.Last_Seq_Received-b.Last_Seq_Applied "Difference"
FROM
(SELECT thread#,
MAX(sequence#) Last_Seq_Received
FROM v$archived_log
GROUP BY thread#
ORDER BY 1
) a ,
(SELECT thread#,
MAX(sequence#) Last_Seq_Applied
FROM v$archived_log
WHERE applied in ('YES','IN-MEMORY')
GROUP BY thread#
ORDER BY 1
) b
WHERE a.thread#=b.thread# ;
Code: Select all
THREAD# LAST_SEQ_RECEIVED LAST_SEQ_APPLIED Difference
---------- ----------------- ---------------- ----------
1 1842 1842 0
2 1750 1750 0
3 1669 1669 0
-- END -- END -- END -- END -- END -- END -- END -- END -- END -- END -- END -- END -- END -- END --