Sunday, March 26, 2017

Change SYS user password in RAC Dataguard Environment

I have changed the password of SYS user on primary database in RAC configuration.

Now below questions might come :-
How shall the password for SYS user be changed in the Standby database?
Can the password file be copied to standby while instances are up and running?

Let's assume following setup in the RAC Data Guard environment:
Primary: 2 node RAC, instance name: orcl1 and orcl2
Standby: 2 node RAC, instance name: orclp1 and orclp2

Key points to note :-

1. You must already know that for RAC instance, both password files on both nodes should be identical except the name, eg: orapworcl1 and orapworcl2.
They can be copied from 1 instance to the other, then rename the name to match the SID used on that node.

2. From 11g onwards, due to strong password authentication, when creating physical standby database, no matter it is single or RAC, password file needs to be physically copied from either of the primary instance, then renamed to match the SID for physical standby, eg:

On standby_host1:
$ scp oracle@<primary_host1>:<$ORACLE_HOME>/dbs/orapworcl1 <$ORACLE_HOME>/dbs
$ mv $ORACLE_HOME/dbs/orapworcl1 $ORACLE_HOME/dbs/orapworclp1

On standby_host2:
$ scp oracle@<primary_host1>:<$ORACLE_HOME>/dbs/orapworcl1 <$ORACLE_HOME>/dbs
$ mv $ORACLE_HOME/dbs/orapworcl1 $ORACLE_HOME/dbs/orapworclp2

Two cases might occur :-

1. SYS user password is altered on primary instance

2. Password file on primary has been recreated via orapwd command

alert log will report (on both primary instances):
Sun Mar 26 15:20:43 2010
Error 1031 received logging on to the standby
Errors in file /u01/diag/rdbms/orcl/orcl1/trace/tl1_arc10_97329.trc:
ORA-01031: insufficient privileges
FAL[server, ARC3]: FAL archive failed, see trace file.


1. Copy (scp) the orapw<SID> file from any of the primary instance to all standby instances:
$ scp $ORACLE_HOME/dbs/orapworcl1 oracle@<standby_host1>:<$ORACLE_HOME>/dbs/

$ scp $ORACLE_HOME/dbs/orapworcl1 oracle@<standby_host2>:<$ORACLE_HOME>/dbs/

2. Rename the password file on all standby nodes to match the standby SID.

on standby_host1:
mv $ORACLE_HOME/dbs/orapworcl1 $ORACLE_HOME/dbs/orapworclp1

on standby_host2:
mv $ORACLE_HOME/dbs/orapworcl1 $ORACLE_HOME/dbs/orapworclp2

3. It is not required to shutdown/restart any of the primary or standby instances.

4. Log shipping will resume automatically after this.
If not, alter parameter log_archive_dest_state_<n> to defer, then enable.

SQL> alter system set log_archive_dest_state_2=defer scope=both sid='*';

SQL> alter system set log_archive_dest_state_2=enable scope=both sid='*';

No comments:

Post a Comment