3 Replies Latest reply: Jul 30, 2013 4:52 AM by 1014446 RSS

    sys pwd change on primary

    891445

      When we change sys password on primary we need to change the pwd file on standby.

      What if we initially have different pwd  for sys and separate pwd for pwdfile. 

       

      Does this also affect log shipping when we don't change pwd in pwd file; but change only on database ?

        • 1. Re: sys pwd change on primary
          mseberg

          Hello;

           

          "What if we initially have different pwd  for sys and separate pwd for pwdfile. "

           

          I have never tested. But given the large number of issues around this subject I believe your logs won't ship.

           

          When I change the SYS password on the Primary I scp a copy to the Standby server. Stop apply and shutdown the Standby. Then I rename the password and restart the Standby using it and start apply.

           

          This always works.

           

          Best Regards

           

          mseberg

          • 2. Re: sys pwd change on primary
            CKPT

            May be you come with this idea because we can have multiple databases can have same password file with the parameter REMOTE_LOGIN_PASSWORDFILE. When archives are transporting from primary to standby by the password of primary it will authenticate to standby with the same password without any login check. If you have different passwords in primary and standby there is no mechanism to ask you to enter the password whenever it ships arch/redo to standby. With the intention of that oracle comes up with same password from primary and standby instances.

             

            Here some more details in Password file maintenance in a Data Guard environment « The Dutch Prutser's Blog

            • 3. Re: sys pwd change on primary
              1014446

              If you want to change sys password in primary, need to transfer password file from primary to standby server after taking backup of existing password file as the primary database sends its redo to its standby database and this redo transport is authenticated by the password of the sys user, or another user if configured, of the primary database. That is, the primary database logs into a standby database by using the password stored in the password file for the user who ships the redo, which is most likely sys. If the (encrypted) passwords of the primary database and the standby database don’t match redo transport will (eventually) be in trouble. You will face archive lag between primary and standby.

              You need to follow below steps if you want to change password in primary and don't want any trouble in standby database :


              (1) Take backup of existing password file present under ORACLE_HOME/dbs/orapw'SID'

              (2) change sys password through alter user command :

                             alter user username identified by password;

                             ( You can check that it will update password file with new timestamp

              OR

                   Create new password file with orapwd command.

              (3) Replace new password file to standby server in the same location i.e. ORACLE_HOME/dbs/ .  You can use scp command to transfer file between server in case you have linux/Aix server.

              (4) Then you can monitor you log shipping from primary to standby using view v$managed_standby or you can check the archive lag from below mentioned query :


              SET PAGESIZE 140

              COL DB_NAME FORMAT A10

              COL HOSTNAME FORMAT A14

              COL LOG_ARCHIVED FORMAT 999999

              COL LOG_APPLIED FORMAT 999999

              COL LOG_GAP FORMAT 9999

              COL APPLIED_TIME FORMAT A14

              SELECT

                 DB_NAME, HOSTNAME, LOG_ARCHIVED, LOG_APPLIED, APPLIED_TIME, LOG_ARCHIVED-LOG_APPLIED LOG_GAP

              FROM

              ( SELECT

                 NAME DB_NAME

              FROM

                 V$DATABASE

              ),

              (

              SELECT

                 UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,'.'),0,LENGTH(HOST_NAME), (INSTR(HOST_NAME,'.')-1))))) HOSTNAME

              FROM

                 V$INSTANCE

              ),

              (

              SELECT

                 MAX(SEQUENCE#) LOG_ARCHIVED

              FROM

                 V$ARCHIVED_LOG

              WHERE

                 ARCHIVED='YES'

              ),

              (

              SELECT

                 MAX(SEQUENCE#) LOG_APPLIED

              FROM

                 V$ARCHIVED_LOG

              WHERE

                 APPLIED='YES'

              ),

              (

              SELECT

                 TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME

              FROM

                 V$ARCHIVED_LOG

              WHERE

                 APPLIED='YES'

              );