Password maintenance in DataGuard Environment

Password maintenance in DataGuard Environment

The motivation of this article is a friend's question. He asked me if the password file of the slave database can be directly re-built. I said it is best to copy it. If re-built, there may be some potential problems, of course, this so-called potential problem is also a matter of self-help. If there is a problem in the end, it seems that no effective information is found in my mind, however, it seems that it is an unconventional method to build a trusted uard without directly recreating the password file, but in the twinkling of an eye, once this happens, or when there are some potential problems in the password file, how can we effectively prevent them? This problem has increased to another height, so I made some preliminary analysis on this problem, then I can see some technical experts on the Internet who have made in-depth analysis on such seemingly detailed issues. I don't want to write any more, share his achievements with you.

This article will mention another question: do you pay special attention to the maintenance and management of password files in the dataguard environment?
The answer is yes. Updating the password file in the Data Guard environment is not as simple as you think. Based on this problem

First, let's take a look at my current deployment uard configuration, and then go into some details about the password file.
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 there is currently a master database peppi, a physical standby database kokki, and the overall state of the dataguard environment is success.
Note that peppi runs on host pruster and kokki runs on el5.

Raise Questions

The first question is: if the password file of the master database is changed, will it be transmitted to the slave database? We can perform simple verification in the master database peppi, that is, update the password file in the master database, and then view the password file in the backup database kokki.
SQL> connect sys/oracle @ peppi as sysdba
Connected.
SQL> select * from v $ pwfile_users;
USERNAME SYSDB SYSOP SYSAS
---------------------------------------------
SYS TRUE FALSE

Currently, only one record exists in the password file of peppi in the master database. If sysdba is assigned to the system user, updating the password file will be triggered.
SQL> grant sysdba to system;
Grant succeeded.

SQL> select * from v $ pwfile_users;
USERNAME SYSDB SYSOP SYSAS
---------------------------------------------
SYS TRUE FALSE
SYSTEM TRUE FALSE

In this way, there will be two records in the password file of the master database. How many records are there in the slave database?
SQL> connect sys/oracle @ kokki as sysdba
Connected.
SQL> select * from v $ pwfile_users;
USERNAME SYSDB SYSOP SYSAS
---------------------------------------------
SYS TRUE FALSE
The output clearly shows that the password file change of the master database is not transmitted to the slave database.

Then we revoke the sysdba permission of the system, because we still do not want the system permission to be too broad?
SQL> connect sys/oracle @ peppi as sysdba
Connected.
SQL> revoke sysdba from system;
Revoke succeeded.
Does this affect dataguard?

The following question is: does this affect the consumer uard? The redo transmission from the master database to the slave database requires the sys user password in the password file for authentication. If other sysdba users are configured in the master database, however, the problem is that the redo transmission of the master database uses password files like sys as the basis for authentication. Once the encrypted password of the master database is inconsistent with that of the slave database, the redo transmission will be troublesome.
? If you need to verify this problem, you can modify the sys password in peppi and then see how kokki is affected.
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 that the password file changes in the master database will not be automatically transmitted to the slave database.
But in fact, we can still use the original password file on the slave database kokki, as follows:
SQL> connect sys/oracle @ kokki as sysdba
Connected.

Then the second question is: Can redo be transmitted normally if the password file is inconsistent? To solve this problem, we need to switch the log to the master database to verify it.
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

Through the above output, we can see that the results are normal. Obviously, the change in the password file will not directly affect the redo transmission. This is because the master database and slave database have established a connection, for the redo part, the Standby database does not need to re-authenticate the primary database. However, once the redo transmission is reset, it is clear that there will be problems.
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

Through this we can clearly see that the redo transmission is interrupted and then prompted the ORA-16778 error, if the master database password file once changed, then it will not immediately expose the problem, however, it may erupt at a later time.
So what do ORA-16778 errors mean?
$ Oerr ora 1, 16778
16778,000 00, "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
// More details. Query the LogXptStatus property to see
// Errors.

We have modified the sys password in the master database. How can we fix this problem?
Answer:

Maybe we can simply solve it by recreating the password file on kokki. Let's try it.
El5 $ rm $ ORACLE_HOME/dbs/orapwv1120
El5 $ orapwd file = $ ORACLE_HOME/dbs/orapwv1120 password = prutser

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

It seems acceptable, but the question is, is it optimistic that the problem has been solved?
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

At this time, we found that the situation did not look so good. Do we need to reset the redo transmission? Let's simply verify it.
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 still does not work at this time. Rebuilding the password file in the standby database does not seem to be a solution, because the password file is encrypted. Although the password is equivalent, the encrypted file has different effects.
So what if I copy the password from the master database to the slave database?

We try to copy the password file to the standby database kokki and then see the effect:
$ Scp $ ORACLE_HOME/dbs/orapwv1120 el5: $ ORACLE_HOME/dbs/orapwv1120
Orapwv1120 100% 1536 1.5KB/s

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

At this time, we can use sys to access the instance in kokki. Is it OK for dataguard?
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

Once again, it turns out that the replica uard is not satisfactory, but it seems that everything is the same as the original one. If we wait for a moment and then enable the transfer of archived logs again, the redo transmission will be associated with it, of course, this process can be completed by disabling redo transmission.
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, the status of the slave uard is normal because the redo of the master database is transmitted to the slave database.
Summary:
If you need to ensure the sustainability of mongouard, if there is any change to the password file in the master database, we must copy the password file from the master database to the slave database. The last sentence is Happy Data Guarding ;-)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

Important configuration parameters of Oracle Data Guard

Configure Oracle 11g Data Guard based on the same host

Explore Oracle 11g elastic uard

Oracle Data Guard (RAC + DG) archive deletion policies and scripts

Role conversion for Oracle Data Guard

FAL gap in Oracle Data Guard logs

Oracle 11g Data Guard Error 16143 Heartbeat failed to connect to standby