Automatic password file synchronization with Oracle Data Guard 12.2 – #orapwd

Based on my experience when working with customers or checking the questions in community.oracle.com and majority of the questions are on LAG because of mismatch password file. Mismatch of password file can happen if the sys user or any sys related users password changed in primary and then every time we have to copy fresh file again on standby. But most of the cases DBA’s realize once the standby is out of sync. I can in Oracle 12.2 this is one of the best feature in Data Guard and it fixes many issues i guess.

Challenges in copying the password file.

  1. No direct SCP from Oracle users.when we copy from Oracle user –> normal user –> scp –> normal user –> Oracle user.
  2. Changes in permissions
  3. Changes in groups.

This blog post i have tested in November 7th 2016 but haven’t got chance to post them in my blog. 🙂 We will see the demo how this feature is going to work and how reduces the overhead to DBA’s 🙂

  • Check the password file permissions of primary database and also lets see what is the output of md5sum of password file.
[oracle@ORA-C1 dbs]$ ls -ltr orapwobdb
-rw-r-----. 1 oracle oinstall 3072 Nov  7 00:45 orapwobdb
[oracle@ORA-C1 dbs]$ md5sum orapwobdb
3fbe96899db0e06ce8021107e337ecf7  orapwobdb
[oracle@ORA-C1 dbs]$
  • The same like above we will check the output in standby database , we are expected to see the same output.
[oracle@ORA-C2 dbs]$ ls -ltr orapwobdb
-rw-r-----. 1 oracle oinstall 3072 Nov 7 01:53 orapwobdb
[oracle@ORA-C2 dbs]$ md5sum orapwobdb
3fbe96899db0e06ce8021107e337ecf7 orapwobdb
[oracle@ORA-C2 dbs]$
SQL> col username for a10
SQL> col profile for a12
SQL> col external_time for a20
SQL> col last_login for a20
SQL> col external_name for a20

SQL> select username,sysdba,sysoper,sysasm,sysbackup,sysdg,syskm,account_status,authentication_type,common,con_id from v$pwfile_users;

USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM ACCOUNT_STATUS AUTHENTI COM CON_ID
---------- ----- ----- ----- ----- ----- ----- ------------------------------ -------- --- ----------
SYS TRUE TRUE FALSE FALSE FALSE FALSE OPEN PASSWORD YES 0
SYSDG FALSE FALSE FALSE FALSE TRUE FALSE OPEN PASSWORD NO 1
SYSBACKUP FALSE FALSE FALSE TRUE FALSE FALSE OPEN PASSWORD NO 1
SYSKM FALSE FALSE FALSE FALSE FALSE TRUE OPEN PASSWORD NO 1

SQL>

If we see the output of md5sum is same on both primary and standby database. Until unless the output is same then we no need to worry.

Next?

In traditional Oracle versions or releases when we change the password in primary or if we grant the sysdba to any user then again we have to copy to all the available standby databases. In this example we will create new user and we grant SYSDG to the same user.

SQL> create user c##nassyam identified by idgoug;

User created.
SQL> grant create session,sysdg to c##nassyam;

Grant succeeded.

SQL>

SQL> select username,sysdba,sysoper,sysasm,sysbackup,sysdg,syskm,account_status,authentication_type,common,con_id from v$pwfile_users;

USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM ACCOUNT_STATUS AUTHENTI COM CON_ID
---------- ----- ----- ----- ----- ----- ----- ------------------------------ -------- --- ----------
SYS TRUE TRUE FALSE FALSE FALSE FALSE OPEN PASSWORD YES 0
SYSDG FALSE FALSE FALSE FALSE TRUE FALSE OPEN PASSWORD NO 1
SYSBACKUP FALSE FALSE FALSE TRUE FALSE FALSE OPEN PASSWORD NO 1
SYSKM FALSE FALSE FALSE FALSE FALSE TRUE OPEN PASSWORD NO 1
C##NASSYAM FALSE FALSE FALSE FALSE TRUE FALSE OPEN PASSWORD NO 1

In above output we can see the new user is listed from the password file view. Hence it confirms that the changes are done on password file. Now if we see the md5sum output of primary password file and let’s see comparison with standby database.

[oracle@ORA-C1 dbs]$ md5sum orapwobdb
dc41f3a0622c66e8c27c9ad74cc4aa9c orapwobdb
[oracle@ORA-C1 dbs]$

[oracle@ORA-C2 dbs]$ ls -ltr orapwobdb
-rw-r-----. 1 oracle oinstall 3584 Nov 7 04:50 orapwobdb
[oracle@ORA-C2 dbs]$ md5sum orapwobdb
dc41f3a0622c66e8c27c9ad74cc4aa9c orapwobdb
[oracle@ORA-C2 dbs]$

If we see the above output even though changes in the password file in primary and it is replicated to standby database without any changes such as copying or without using redo_transport_user so on. Am sure this feature is big relief for especially DBA’s. Happy Reading. 🙂

 

Website Comments

Post a comment

*