The way to change the SYS
password without breaking the redo transport service includes copying the primary database's password file to the standby server after changing the password. The following steps show how this can be done:
DEFER
command to defer the log destination with the ALTER SYSTEM
statement:SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = 'DEFER'; System altered.
If the Data Guard broker is being used, we can use the following statement:
DGMGRL> EDIT DATABASE TURKEY_UN SET STATE = 'LOG-TRANSPORT-OFF';
SYS
user's password in the primary database:SQL> ALTER USER SYS IDENTIFIED BY newpassword; User altered.
$ cd $ORACLE_HOME/dbs $ scp orapwTURKEY standbyhost:/u01/app/oracle/product/11.2.0/ dbhome_1/dbs/orapwINDIAPS
SYS
password:$ sqlplus sys/newpassword as sysdba
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = 'ENABLE'; System altered.
If the Data Guard broker is being used, we can use the following statement:
DGMGRL> EDIT DATABASE TURKEY_UN SET STATE = 'ONLINE';
SQL> ALTER SYSTEM SWITCH LOGFILE; System altered.
Check the standby database's processes or the alert log file to see redo transport service status:
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY ; PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS --------- ------------ ---------- ---------- ---------- ---------- ARCH CLOSING 1 3232 1 275 ARCH CLOSING 1 3229 1 47 ARCH CONNECTED 0 0 0 0 ARCH CLOSING 1 3220 2049 1164 RFS IDLE 0 0 0 0 RFS IDLE 0 0 0 0 RFS IDLE 0 0 0 0 MRP0 APPLYING_LOG 1 3233 122 102400 RFS IDLE 1 3233 122 1
Suppose we have an RAC primary database, and all instances successfully transmit redo to the standby database except one. One of the primary instances shows an authentication error in the alert log file. What do we need to do to fix this issue?
We've now changed the SYS
user's password in a Data Guard environment without causing any errors in the redo transport service. Database administrators have to consider standby databases when changing a SYS
password in the primary database of a Data Guard configuration. Otherwise, the redo transport will fail, and if it is not noticed quickly, this may cause data loss in case of any failover.
If we often need to change the SYS
user's password in the primary database, it may be troublesome to copy the password file to the standby site every time, especially when there's more than one standby destination. In this case, the REDO_TRANSPORT_USER
parameter comes to our rescue. It's possible to change the default redo transport user from SYS
to another database user by setting this parameter.