This discussion is archived
3 Replies Latest reply: Jul 30, 2013 2:52 AM by f1c78aa5-dae3-4e17-996f-0a49779089fe RSS

sys pwd change on primary

891445 Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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
    f1c78aa5-dae3-4e17-996f-0a49779089fe Newbie
    Currently Being Moderated

    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'

    );

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points