How to Change password in Data Guard environment

Overview

The process of User password change in the Data Guard environment is not the same as the normal process. Generally, the effect of password change is applied in both the data dictionary and password file. So, when we change the password on the Primary Server, the data dictionary is changed on all servers but the physical password file is not updated automatically. For this reason, we need to copy the password file from the Primary Server to all the Secondary Servers i.e. Standby Servers.
If we change the password only on the Primary Server and not transferred or applied to the Standby Server, then the Primary Server stops transferring archive logs to the Standby and you will see the error in the alert log file.

Let’s start to learn how to change the password in the data guard environment. I recommended applying this process for Oracle Database 12c Release 1 and Release 2 and for Oracle Database 19c and 21c please check this link.

Lab Environment

	Primary Server	Standby Server
OS Release	Red Hat Enterprise Linux release 8.3 (Ootpa)	Red Hat Enterprise Linux release 8.3 (Ootpa)
Kernel	4.18.0-240.8.1.el8_3.x86_64	4.18.0-240.8.1.el8_3.x86_64
Release	Oracle Database 12.1.0.2.0	Oracle Database 12.1.0.2.0
IP Address	192.168.201.241	10.100.20.242
Host Name	dg1	dg2
User Name	Admin	Admin

Table 01: Lab Environment – Host

Data Guard Environment

	Primary Database	Standby Database
Oracle Release	Oracle Database 12.1.0.2.0	Oracle Database 12.1.0.2.0
SQL*Plus	Release 12.1.0.2.0	Release 12.1.0.2.0
DATABASE_ROLE	PRIMARY	PHYSICAL STANDBY
db_unique_name	orclproddgone	orclproddgtwo
db_name	ORCLPROD	ORCLPROD
IS_CDB	YES	YES
OPEN_MODE	READ WRITE	READ ONLY WITH APPLY
LOG_MODE	ARCHIVELOG	ARCHIVELOG
FLASHBACK_ON	YES	YES
Oracle Data Guard broker utility	Not Use	Not Use
remote_login_passwordfile	EXCLUSIVE	EXCLUSIVE

Table 02: Lab Environment – Database

Step 1: Check the Database Status:

At first, we check the Database status in both the Primary and Standby instance.

1.1. Connect to the Primary Database:

[oracle@dg1 ~]$ sqlplus / as sysdba

And set line size and column format by running the following code.

SQL> set linesize 9000
SQL> column db_unique_name format a14
SQL> column name format a8
SQL> column open_mode format a20
SQL> column LOG_MODE format a10
SQL> column FLASHBACK_ON format a12
SQL> column database_role format a16
SQL> column PROTECTION_MODE format a19
SQL> column PROTECTION_LEVEL format a19

Now, Run the below SQL statement to get the information of the Primary Database.

SQL> select db_unique_name,name,open_mode,LOG_MODE,FLASHBACK_ON,database_role,PROTECTION_MODE,PROTECTION_LEVEL from v$database;

We see our Primary Database is up and running in READ WRITE mode with the PRIMARY role.

1.2. Connect to the Standby Database:

[oracle@dg2 ~]$ sqlplus / as sysdba

Now, Run the same SQL statement that we run in the Primary Database to get the information of the Standby Database.

SQL> select db_unique_name,name,open_mode,LOG_MODE,FLASHBACK_ON,database_role,PROTECTION_MODE,PROTECTION_LEVEL from v$database;

Great! our Standby Database is running in READ ONLY WITH APPLY with the PHYSICAL STANDBY role.

Step 2: Check the Data Guard Status:

2.1. Connect to the Standby Database:

[oracle@dg2 ~]$ sqlplus / as sysdba

Run the below SQL statement on the Standby database.

SQL> set linesize 9000
SQL> column name format a25
SQL> column value format a20
SQL> column time_computed format a25
--for line size and column formatting

SQL> SELECT name, value, time_computed FROM v$dataguard_stats;

Happy to see that, our Data Guard environment is well sync with the Primary Database. At this point in time, we change the password in the Primary Database and check the effect.

Step 3: Check the User Status:

3.1. Connect to the Primary Database:

[oracle@dg1 ~]$ sqlplus / as sysdba

I want to check the present status of my Database user ( e.g. DBSNMP, SYSTEM, SYS ) in Primary Database from the dba_users data dictionary.

SQL> set linesize 9000
SQL> column USERNAME format a14
SQL> column PASSWORD format a8
SQL> column ACCOUNT_STATUS format a20
SQL> column LOCK_DATE format a10
SQL> column EXPIRY_DATE format a10
--for line size and column formatting

SQL> select USERNAME,PASSWORD,ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE from dba_users where USERNAME in ('DBSNMP','SYSTEM','SYS');

3.2. Connect to the Standby Database:

[oracle@dg2 ~]$ sqlplus / as sysdba

Now, Run the same SQL statement that we run in the Primary Database to get the user information from Standby Database.

SQL> select USERNAME,PASSWORD,ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE from dba_users where USERNAME in ('DBSNMP','SYSTEM','SYS');

Generally, the User password property of Standby is inherited from the Primary Database, otherwise, the Data Guard environment is not functioning well. In my case, everything is ok, because on both sides' the password property is the same.

Step 4: Change SYS Password in Primary:

4.1. Connect to the Primary Database:

[oracle@dg1 ~]$ sqlplus / as sysdba

Before the password change, makes sure you alter the value of log_archive_dest_state_n from ENABLE to DEFER. And then change the password. In my case, I want to change the password for the SYS user.

SQL> ALTER SYSTEM CHECKPOINT;SQL> ALTER SYSTEM SET log_archive_dest_state_2='DEFER';SQL> alter user SYS identified by Super1Secret2Password3 account unlock;

We successfully change the password in the Primary Database.

4.2. Transfer Password file from Primary Server to Secondary Servers:

4.2.1. Login to the Standby Database:

At first, back up the existing password file. I just move it with another name. And then copy the password file from the Primary Server to this server. This will also need to do for all of your Secondary Servers.

[oracle@dg2 dbs]$ mv -v /oracle/product/12.1.0/db_1/dbs/orapworclprod /oracle/product/12.1.0/db_1/dbs/orapworclprod.bak
[oracle@dg2 dbs]$ scp oracle@192.168.201.241:/oracle/product/12.1.0/db_1/dbs/orapworclprod /oracle/product/12.1.0/db_1/dbs/orapworclprod

Remember that, password file name and permission will be intact as the previous one.

4.3. Change the Archive State:

4.3.1. Connect to the Primary Database:

[oracle@dg1 ~]$ sqlplus / as sysdba

Alter the value of log_archive_dest_state_n from DEFER to ENABLE.

SQL> ALTER SYSTEM CHECKPOINT;
SQL> ALTER SYSTEM SET log_archive_dest_state_2='ENABLE';

Step 5: Check the User Status After Password Change:

5.1. Connect to the Primary Database:

[oracle@dg1 ~]$ sqlplus / as sysdba

Check the status of the user ( e.g., DBSNMP, SYSTEM, SYS ) in the Primary Database. You may notice, I only changed the password for the SYS user. So, let’s check what is the status of SYS and others.

SQL> select USERNAME,PASSWORD,ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE from dba_users where USERNAME in ('DBSNMP','SYSTEM','SYS');

5.2. Connect to the Standby Database:

[oracle@dg2 ~]$ sqlplus / as sysdba

Now, Run the same SQL statement that we run in the Primary Database to get the user information from Standby Database.

SQL> select USERNAME,PASSWORD,ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE from dba_users where USERNAME in ('DBSNMP','SYSTEM','SYS');

Yeh! the SYS password is accordingly. The message is clear that the SYS password has been changed and synchronized in both the primary server and secondary server.

Conclusion

This tutorial helps you to change the user password in the Data Guard environment step by step. If you have any queries, please comment to us.

References

In this tutorial, I follow the official docs as reference Oracle Data Guard Administration Guide.