Here I have shown how to change SYS password in data guard environment. If you require to change the password many times in primary database for any security reason then Option 2 would be good choice.
Option 1:
After changing the SYS password in primary database copy the password to all standby location
Steps:
1. Change the sys password in primary database
2. Copy password file from primary database to all standby database
-- Primary
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
238
SQL> alter system switch logfile;
System altered.
-- Alert log file in Primary
Wed Aug 10 10:37:24 2016
Thread 1 advanced to log sequence 240 (LGWR switch)
Current log# 1 seq# 240 mem# 0: +REDO/stdb3_dg/onlinelog/group_1.267.908899489
Wed Aug 10 10:37:26 2016
LNS: Standby redo logfile selected for thread 1 sequence 240 for destination LOG_ARCHIVE_DEST_3
Wed Aug 10 10:37:40 2016
Archived Log entry 206 added for thread 1 sequence 239 ID 0xa806c167 dest 1:
SQL> select process, status from v$managed_standby;
PROCESS STATUS
--------------------------- ------------------------------------
ARCH CONNECTED
ARCH CONNECTED
ARCH CONNECTED
ARCH CLOSING
ARCH CLOSING
ARCH CLOSING
ARCH CLOSING
ARCH CLOSING
ARCH CLOSING
ARCH CLOSING
LNS WRITING
11 rows selected.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
239
-- Standby
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
239
-- Primary
SQL> alter user SYS identified by test123;
User altered.
SQL> alter system set log_archive_dest_state_3=DEFER scope=both;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system set log_archive_dest_state_3=enable scope=both;
System altered.
-- Primady database alert log file
Error 1031 received logging on to the standby
PING[ARC2]: Heartbeat failed to connect to standby 'STDB4_DG'. Error is 1031.
-- Standby database
-- Copy Password file from primary to Standby
scp oracle@stagedb3:/opt/oracle/product/11.2.0.3/dbs/orapwSTDB3_DG /opt/oracle/product/11.2.0.3/dbs/
-- change the password file name to orapw<SID> of standby
mv /opt/oracle/product/11.2.0.3/dbs/orapwSTDB3_DG /opt/oracle/product/11.2.0.3/dbs/orapwSTDB4_DG
-- Alert log in Primary Database
Wed Aug 10 12:14:48 2016
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_3
******************************************************************
Wed Aug 10 12:14:48 2016
ARC4: Standby redo logfile selected for thread 1 sequence 241 for destination LOG_ARCHIVE_DEST_3
LNS: Standby redo logfile selected for thread 1 sequence 242 for destination LOG_ARCHIVE_DEST_3
So we have seen here after copying the password file from primary to standby log shipping from primary to standby start to works again and “Heartbeat failed to connect to standby 'STDB4_DG'. Error is 1031” gone.
Option 2:
Sometimes for security reason we might need to change sys password several times in primary database but considering the number of Standby database it is tedious to copy password file from primary to standby database. To consider this problem oracle introduce a feature from 11.2.0.x or above Data Guard environment. Using REDO_TRANSPORT_USER parameter we have avoid copying password file to standby database after changing SYS password. Here I have shown how to do that.
Steps:
1. Create a user with and grant sysoper privilege to that user, or you can grant sysoper privilege to existing user
2. Change REDO_TRANSPORT_USER parameter in both primary and all standby database.
3. Copy the password file to all standby database
-- Primary database
SQL> column USERNAME format a30
SQL> column SYSDBA format a25
SQL> column SYSOPER format a25
SQL> column SYSASM format a25
SQL> select * from V$PWFILE_USERS;
USERNAME SYSDBA SYSOPER SYSASM
------------------------------ ------------------------- -------------------------
SYS TRUE TRUE FALSE
SQL> create user redotrans identified by 123;
User created.
SQL> grant sysoper to redotrans;
Grant succeeded.
SQL> select * from V$PWFILE_USERS;
USERNAME SYSDBA SYSOPER SYSASM
------------------------------ ------------------------- -------------------------
SYS TRUE TRUE FALSE
REDOTRANS FALSE TRUE FALSE
SQL> ALTER SYSTEM SET REDO_TRANSPORT_USER = REDOTRANS scope=both;
System altered.
-- Standby
SQL> ALTER SYSTEM SET REDO_TRANSPORT_USER = REDOTRANS scope=both;
System altered.
-- Now I have copied the password file from primary to standby database
-- Standby database
scp oracle@stagedb3:/opt/oracle/product/11.2.0.3/dbs/orapwSTDB3_DG /opt/oracle/product/11.2.0.3/dbs/
-- change the password file name to orapw<SID> of standby
mv /opt/oracle/product/11.2.0.3/dbs/orapwSTDB3_DG /opt/oracle/product/11.2.0.3/dbs/orapwSTDB4_DG
-- Lets Test Now
-- Primary
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
274
SQL> alter system switch logfile;
System altered.
-- Standby
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
275
-- Primary
SQL> alter user SYS identified by 456;
User altered.
SQL> alter system set log_archive_dest_state_3=DEFER scope=both;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
276
-- Standby
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
275
-- Primary
SQL> alter system set log_archive_dest_state_3=enable scope=both;
System altered
-- Alert log file in Primary Database
Wed Aug 10 16:17:58 2016
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_3
******************************************************************
Wed Aug 10 16:17:58 2016
ARC4: Standby redo logfile selected for thread 1 sequence 276 for destination LOG_ARCHIVE_DEST_3
LNS: Standby redo logfile selected for thread 1 sequence 277 for destination LOG_ARCHIVE_DEST_3
So you have seen here even I have change the SYS password log shipping from primary tp standby works well without any error.
Now you will be able to change SYS password as many times you want without copying the password file to all standby database but note that if you change the password of the user (e.g REDOTRANS user here) using in REDO_TRANSPORT_USER parameter then you will need to copy password file to every standby database.