Skip to main content

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 ServerStandby Server
OS ReleaseRed Hat Enterprise Linux release 8.3 (Ootpa)Red Hat Enterprise Linux release 8.3 (Ootpa)
Kernel4.18.0-240.8.1.el8_3.x86_644.18.0-240.8.1.el8_3.x86_64
ReleaseOracle Database 12.1.0.2.0Oracle Database 12.1.0.2.0
IP Address192.168.201.24110.100.20.242
Host Namedg1dg2
User NameAdminAdmin
Table 01: Lab Environment – Host

Data Guard Environment

Primary DatabaseStandby Database
Oracle ReleaseOracle Database 12.1.0.2.0Oracle Database 12.1.0.2.0
SQL*PlusRelease 12.1.0.2.0Release 12.1.0.2.0
DATABASE_ROLEPRIMARYPHYSICAL STANDBY
db_unique_nameorclproddgoneorclproddgtwo
db_nameORCLPRODORCLPROD
IS_CDBYESYES
OPEN_MODEREAD WRITEREAD ONLY WITH APPLY
LOG_MODEARCHIVELOGARCHIVELOG
FLASHBACK_ONYESYES
Oracle Data Guard broker utilityNot UseNot Use
remote_login_passwordfileEXCLUSIVEEXCLUSIVE
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.

Comments

Popular posts from this blog

Upgrading Issue for RHEL 7 to 8 With Leapp

Overview The Leapp utility is a framework for updating and upgrading operating systems as well as applications. The operations of this utility consist of two phases 1. the preupgrade Phase – that chack the upgrade possibilities and 2. the actual upgrade phase – that map packages between previous and current versions of the software packages. Issue – 01: After running ‘ sudo leapp preupgrade ‘ sometimes you find the below issue in ‘ /var/log/leapp/leapp-report.txt ‘. Detail: Risk Factor: high (inhibitor) Title: Leapp detected loaded kernel drivers which have been removed in RHEL 8. Upgrade cannot proceed. Summary: Support for the following RHEL 7 device drivers has been removed in RHEL 8: – pata_acpi Key: f08a07da902958defa4f5c2699fae9ec2eb67c5b Remediation: 1. Disable detected kernel drivers in order to proceed with the upgrade process using the rmmod or modprobe -r . rmmod – Simple program to remove a module from the Linux Kernel modprobe – Add and remove modules from the Linux Ke

Upgrading Oracle Linux 6 to 7

Overview It is possible to upgrade an Oracle Linux 6 system to Oracle Linux 7.6 under the following conditions: The system meets the minimum installation requirements for Oracle Linux 7 as described in Chapter 1, System Requirements and Limits. The Oracle Linux 6 system has been completely updated from the ol6_x86_64_latest channel or ol6_latest repository. UEK R3 or UEK R4 has been installed on the system to be upgraded and is the default boot kernel. Upgrading from UEK R2 is not supported. Note that the system is upgraded to use the UEK R5 release provided with Oracle Linux 7.6. Upgrading is supported only for systems that are installed with the Minimal Install base environment. If additional packages are installed from an alternative repository or channel, upgrade might fail or the resulting upgrade might not function as expected. reference: https://docs.oracle.com/en/operating-systems/oracle-linux/7/relnotes7.6/ol7-install.html#ol7-upgrade-ol6 Verifying the system before Upgrade: #

Software-only Installation of oracle Database 21c on RHEL 8

Overview Oracle Database 21c is a multi-model database that provides full support for relational and non-relational data, such as JSON, XML, text, spatial and graph data. There are lots of new features available in this new release like partitioned hybrid tables, encryption capabilities in the built-in data dictionary, statistics-only queries, and many more. It also enables Oracle's Autonomous Database Cloud Services. This article describes the installation of Oracle Database 21c 64-bit on Red Hat 8 64-bit. Lab Environment Particulars                                     Database Info --------------                                            ------------------------------------------------------- OS Release                                     Red Hat Enterprise Linux release 8.4 (Ootpa) Kernel                                              4.18.0-425.10.1.el8_7.x86_64 IP Address                                     1 92.168.0.10 Host Name                                  oemsrv User Na