Change SYS password in Data Guard environment

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 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.