Time for action – changing the SYS password in a Data Guard environment

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:

  1. Stop redo transport from the primary database to the standby database. We can execute the 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';
  2. Change the SYS user's password in the primary database:
    SQL> ALTER USER SYS IDENTIFIED BY newpassword;

User altered.
  3. Copy the primary database's password file to the standby site:
    $ cd $ORACLE_HOME/dbs
$ scp orapwTURKEY standbyhost:/u01/app/oracle/product/11.2.0/ dbhome_1/dbs/orapwINDIAPS
  4. Try logging into the standby database from the standby server using the new SYS password:
    $ sqlplus sys/newpassword as sysdba
  5. Start redo transport from the primary database to the standby database:
    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';
  6. Check whether the redo transport service is running normally by switching the redo logs in the primary database:
    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

    Tip

    Also, if the password file of the standby database is somehow corrupted, or has been deleted, the redo transport service will raise an error and we can copy the primary password file to the standby site to fix this problem.

Pop quiz – the redo transport authentication problem in only one instance of the primary database

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?

What just happened?

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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.139.239.41