The Dutch Prutser's Blog

By: Harald van Breederode

  • Disclaimer

    The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.
  • Subscribe

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 245 other followers

Password file maintenance in a Data Guard environment

Posted by Harald van Breederode on June 13, 2011

In a previous posting I wrote about password file maintenance in a clustered ASM and RAC environment.
This article raised another question: Is there anything specific about password file maintenance in a Data Guard environment?

Yes, updating a password file in a Data Guard environment isn’t as straight forward as one might think. In this posting I will shed some light on this subject and show you how to properly update a password file in a Data Guard environment.

Let’s get started by taking a look at my Data Guard setup before diving into password file maintenance procedures.

DGMGRL> show configuration;

Configuration - PeppiEnKokki

  Protection Mode: MaxPerformance
  Databases:
    peppi - Primary database
    kokki - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

This output shows that I have a primary database called peppi, a physical standby database called kokki and that the overall status of my Data Guard configuration is healthy.

Note: peppi runs on host prutser and kokki runs on host el5.

Revealing the problem

The first question is: Are updates to the password file on the primary database propagated to the standby database(s)? We can easily figure this out by triggering an update to the password file on peppi followed by querying the password file on kokki.

SQL> connect sys/oracle@peppi as sysdba
Connected.
SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE

Currently there is only one entry in the password file on peppi. By granting SYSDBA to SYSTEM we can trigger an update to the password file as shown below:

SQL> grant sysdba to system;

Grant succeeded.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
SYSTEM                         TRUE  FALSE FALSE

There are now two entries in the password file on the primary database. How many entries are there on the standby database?

SQL> connect sys/oracle@kokki as sysdba
Connected.
SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE

The output above makes clear that updates to the password file on the primary database are not propagated to the standby database.

Before continuing, we take away SYSDBA from SYSTEM because we don’t want SYSTEM to become too powerful, do we?

SQL> connect sys/oracle@peppi as sysdba
Connected.
SQL> revoke sysdba from system;

Revoke succeeded.

Does this affect Data Guard?

The next question is: Does this affect Data Guard? The primary database sends its redo to its standby database(s) 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(s) don’t match redo transport will (eventually) be in trouble.

To demonstrate this behavior we will change the SYS password on peppi and see if or how it affects kokki.

SQL> alter user sys identified by prutser;

User altered.
SQL> connect sys/prutser@peppi as sysdba
Connected.
SQL> connect sys/prutser@kokki as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.

The above output shows (again) that an update to the password file on the primary database is not automatically propagated to the standby database(s).

SQL> connect sys/oracle@kokki as sysdba
Connected.

As a matter of fact, we can still connect to kokki using the old SYS password as shown above.

The next question is: Is redo transport still possible now that the passwords are no longer the same? Let’s switch a few logs on the primary database and see what happens.

SQL> connect sys/prutser@peppi as sysdba
Connected.
SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

DGMGRL> show configuration;

Configuration - PeppiEnKokki

  Protection Mode: MaxPerformance
  Databases:
    peppi - Primary database
    kokki - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

According to the above output everything is still fine. Apparently the password change didn’t affect the redo transport. This is because the primary database was already logged into the standby database and as long as this connection remains open there is no need for the standby database to re-authenticate the incoming redo. However if we disable and re-enable redo transport it becomes clear that we indeed have a problem as shown below.

DGMGRL> edit database kokki set property LogShipping=off;
Property "logshipping" updated

DGMGRL> edit database kokki set property LogShipping=on;
Property "logshipping" updated

DGMGRL> show configuration;

Configuration - PeppiEnKokki

  Protection Mode: MaxPerformance
  Databases:
    peppi - Primary database
      Error: ORA-16778: redo transport error for one or more databases

    kokki - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

It is pretty clear that redo transport ceased indicated by the ORA-16778. It is important to realize that a problem caused by updating the password file on the primary database doesn’t necessarily show up immediately but may show up much later in time.

What is an ORA-16778 anyway?

$ oerr ora 16778
16778, 00000, "redo transport error for one or more databases"
// *Cause:  The redo transport service was unable to send redo data to one
//          or more standby databases.
// *Action: Check the Data Guard broker log and Oracle alert log for
//          more details. Query the LogXptStatus property to see the
//          errors.

Because we just changed the SYS password on the primary database we already know what caused the ORA-16778. So the question is: How do we fix this?

In search of a solution

Maybe we can simply re-create the password file on kokki with the new SYS password? Let’s give it a try:

el5$ rm $ORACLE_HOME/dbs/orapwv1120

el5$ orapwd file=$ORACLE_HOME/dbs/orapwv1120 password=prutser

SQL> connect sys/prutser@kokki as sysdba
Connected.

That seems to work! The question is of course: Does Data Guard agree with my optimism?

SQL> connect sys/prutser@peppi as sysdba
Connected.
SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

DGMGRL> show configuration;

Configuration - PeppiEnKokki

  Protection Mode: MaxPerformance
  Databases:
    peppi - Primary database
      Error: ORA-16778: redo transport error for one or more databases

    kokki - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

Hmmm, this doesn’t look too good, does it? Maybe we need to disable and re-enable redo shipment?

DGMGRL> edit database kokki set property LogShipping=off;
Property "logshipping" updated

DGMGRL> edit database kokki set property LogShipping=on;
Property "logshipping" updated

DGMGRL> show configuration;

Configuration - PeppiEnKokki

  Protection Mode: MaxPerformance
  Databases:
    peppi - Primary database
      Error: ORA-16778: redo transport error for one or more databases

    kokki - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

No, this didn’t help either. Re-creating the password file on the standby database is not the solution. The reason for this is the way the passwords are encrypted in the password file. Even if the passwords are the same, the result of the encryption is not.

How about copying the password file from the primary database to the standby database?

The solution

We will copy the password file from peppi to kokki and see if it works:

$ scp $ORACLE_HOME/dbs/orapwv1120 el5:$ORACLE_HOME/dbs/orapwv1120
orapwv1120                                    100% 1536     1.5KB/s   00:00    

SQL> connect sys/prutser@kokki as sysdba
Connected.

Again we can connect to kokki with the updated SYS password, but is Data Guard happy now?

SQL> connect sys/prutser@peppi as sysdba
Connected.
SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

DGMGRL> show configuration;

Configuration - PeppiEnKokki

  Protection Mode: MaxPerformance
  Databases:
    peppi - Primary database
      Error: ORA-16778: redo transport error for one or more databases

    kokki - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

Hmmm, again Data Guard doesn’t look like a happy camper. But things are not what they seem. If we wait long enough Data Guard will re-open its archive destinations and redo will automatically flow again. We can trigger this by disabling and re-enabling redo shipment:

DGMGRL> edit database kokki set property LogShipping=off;
Property "logshipping" updated

DGMGRL> edit database kokki set property LogShipping=on;
Property "logshipping" updated

DGMGRL> show configuration;

Configuration - PeppiEnKokki

  Protection Mode: MaxPerformance
  Databases:
    peppi - Primary database
    kokki - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Finally Data Guard is back in business again because the primary database is able to login on its standby database(s) and thereby successfully ship redo to them.

In summary

In order to keep Data Guard going, we must copy the password file from the primary database to the standby database(s) after an update is made to the password file on the primary database. Happy Data Guarding ;-)
-Harald

About these ads

19 Responses to “Password file maintenance in a Data Guard environment”

  1. Noons said

    Good catch, Harald!

  2. myhandle said

    What are the implications of this for a Maximum Protection configuration ?

    • Harald van Breederode said

      Hi MyHandle,

      I don’t think there is anything specific to a Maximum Protection configuration as long as you immediatly copy the updated password file from the primary database to all standby databases.
      -Harald

      • myhandle said

        Harald,
        my concern was that if you stop LogShipping to a standby, then under Maximum Protection, you’d close the primary, but I suppose you would have more than one standby, so as long as you don’t stop shipping to each standby concurrently, you will keep the primary happy.

  3. Harald van Breederode said

    Hi MyHandle,

    If you immediatly copy the updated password file from the primary database to all its standby databases, there is no need to stop redo shipment. This is because the primary is already logged in on the standby databases and it will happily continue shipping redo. If for whatever reason the primary needs to re-login to one ora all of its standby databases this will succeed because the standby databases already have the updated password file.
    However, if you don’t copy the updated password file you could be in deep trouble. And yes, if you stop redo shipment to all the standby databases in a maximum protected configuration your primary will fail. SO, just copy the password file after updating it and you are in good shape even in a maximun protection configuration.
    -Harald

  4. Hi Harald,

    I really like your instance names….

    I also had some trouble in configuring DataGuard in a MAA environment with RAC primary and Physical Standby because of 11g case-senstive password management.

    I made the experience that in 11gR2 the password files of both primary RAC instances need to be identical (copied!) in order to have data guard working. Manual remote connects from primary to standby and vice versa were working fine, but with RFS ist was only working for one RAC Primary Instance. If we copied the orapw file from the nonworking RAC primary to the standby, then they would swap: the connect problem was shifted to the other RAC primary. We then recreated the password file with ignorecase=Y in RAC1, copied it to RAC2 and Standby and from that point onwards, it was working fine.

    Best regards,
    Martin

    • Harald van Breederode said

      Hi Martin,

      The names I use come from a well-known Dutch children’s TV series.

      Good point on the RAC password files. It is basicly the same problem. I don’t think you have to disable case sensitivity as long as you keep them synchronized across all databases in your MAA configuration.
      -Harald

  5. Saskia van Mourik said

    Hi Harald,

    Since 11g, copying the passwordfile from the primary database to the standby database(s) is Oracle’s recommended and documented way to get an identical passwordfiles on all sites. In previous versions, 10g, the documented way to generate identical passwordfiles on all sites was doing it by orapwd.

    Exactly this changed approach (with a reason) caused us some troubles when we updated our scripts to create standby databases including dataguard-configuration in an 11gR2-environment and did not read the documentation carefully enough in advance:
    1) Create the standby using active database duplication
    Result: All went fine, including configuring/enabling dataguard configuration, because as part of the duplicate Oracle copies the passwordfile to the standby environment.

    2) Create the standby using an already existing backup, and generate the passwordfile on the standby via orapwd (the 10g-way)
    Result: Adding the standby database to the dataguard configuration failed with “Error: ORA-01031: insufficient privileges”. Messages in the drc**.log pointed us to non-equal passwords for SYS, but they were according testing it with sqlplus. Actually, the passwordfiles were not identical due to the ‘salt’ added to the password when it was hashed as part of the encryption mechanism if case-sensitivity is turned on. And yes, this was the reason you should copy the passwordfile to the standby environment instead of generating it via orapwd.

    For us scripting the copying of the passwordfile would mean introducing ssh-keys only for this purpose (not using RAC currently), so we decided to go for one of the two alternatives as described in Note 462219.1 (DATA GUARD LOG SHIPPING FAILS WITH ERROR ORA-16191 IN 11G ):
    “Create password files on both servers using the same password and pass ignorecase=Y to orapwd”, to bypass the hashing and generate identical passwordfiles. This works for us, although it is not the most secure solution.

    Kind regards,
    Saskia

  6. Harald van Breederode said

    Hi Saskia,

    Thanx for your comment about the workaround you use and the reason behind it. You should not forget to re-create the password file(s) on your standby database(s) after changing the SYS password on the primary.

    It is indeed documented that you should copy the password file on 11g while creating a standby database, but it is not documented (as far as I am aware) how to handle password file changes afterwarths. Hence my posting ;-)
    -Harald

  7. Dirk Brouwer said

    Hi Harald,

    If you have a shared filesystem, a possible solution might be to put the passwordfile over there and create a softlink in the $ORACLE_HOME/dbs directory. In that way the passwordfile is maintained at one location.

    Regards,

    Dirk

    • Harald van Breederode said

      Hi Dirk,

      Yes, this is a possible solution but the chances of having a shared filesystem in a Data Guard environment are pretty low in my opinion. Especially if one is using Data Guard as a disaster recovery solution, but it might be quite possible if one uses Data Guard in an off-loading scenario. Or maybe there is an ACFS filesystem available with replication enabled. So yes, it is a possible solution, but it would be really nice if Data Guard would propagate password changes from the primary to its standby’s.
      -Harald

  8. Frank said

    Harald:

    I like the way you describe the solutions to this password copying. Thanks a lot and hope to see more your article.

  9. ravi said

    Good explanation about password file in datagaurd configuration

  10. Hi Herald,

    I have two databases in dataguard setup, Oracle 10.2.0.4
    I stumbled over your blog when looking for user management in a Data Guard environment.
    At the primary side i added a user, and gave it sysdba rights. I was wondering if that user was also created at the secondary site.
    Then i read your blog and looked at my passwrd files, in order to copy it from prim to secondary. But they are both with the same timestamp. Do i still need to copy them, i did not change any passwrds?

  11. Thanks a lot for posting “Password file maintenance in a Data Guard environment The Dutch Prutser’s Blog”. I personallymight definitely end up being coming back for much more browsing and commenting here soon enough. Thanks, Carmine

  12. […] Password file maintenance in a Data Guard environmenthttp://prutser.wordpress.com/2011/06/13/password-file-maintenance-in-a-data-guard-environment/ […]

  13. ash said

    Hi
    did you try switchover using broker in 12c cluster config. Letting you know, password file are on ASM. Since new role sysdg in 12c for data guard, trying to figure out how does the broker read the password file when password file is in ASM.
    THANK

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 245 other followers

%d bloggers like this: