Today we are going have look at changing the password of SYS in the Data Guard environment. Normally we have seen how to change the password of the normal user. That seems a game of kids, Now we must learn new things in Oracle RDBMS. Let’s have look at the steps we use to change the password of SYS.
Option 1: Normal case we have to change the password once but in “Data Guard environment” here we have some different ways with more efforts must be changed. After changing the SYS password in primary database copy the password to all standby location. Let’s have look on
1. We change the sys password in the primary database
2. Now copy a password file from the 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:
— 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.
— Primary 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 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
Option 2: In some cases for security reason we might need to change sys password many times in primary database but considering the number of Standby database it is tedious to copy a password file from primary to the standby database.
To consider this problem Oracle introduces a feature from 11.2.0.x or above Data Guard environment. Using REDO_TRANSPORT_USER parameter we have avoided copying the password file to the standby database after changing the 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 an 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 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 to change the SYS password log shipping from primary to 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 the password file to every standby database. Thanks for giving your valuable time to add some gems to oracle treasure.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: Joel Perez’s Profile
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
