RAC, Data Guard, and Password Files

By | April 15, 2021

When moving from one system to another, one of my favorite migration methods is using data guard.  It has its restrictions – the destination should be the same platform (exceptions noted in MOS note #413484.1), same version unless you’re performing an upgrade, and that a block-for-block copy of the database is acceptable.  Utilizing data guard allows for most of the heavy lifting to be performed before the actual cutover, and generally provides an easy backout procedure if there are issues.  

I was in the process of creating a standby database on a customer’s shiny new Exadata X8M to get them off of their aging Exadata X5-2, when we hit an issue after enabling the data guard configuration.  The database in question began on the X5-2 running 11.2.0.4, then was upgraded to 12.1.0.2, followed by 12.2.0.1, and finally 19c.  The upgrades themselves had been rather smooth, thanks to excellent application vendor documentation for recommended patches and parameter settings, as well as the various upgrade nodes found through MOS note #888828.1.

We cloned the database using our normal procedure – copy the password file from the primary to the standby, start the instance in nomount mode, and clone using RMAN.  After the database had been cloned, we added the database to the broker configuration, and saw an error.

I have recreated the environment using with new hostnames and database names, to protect client details – in the example here, database ac4dg is on the system to be retired, and ac6dg represents the new platform.  When we enabled the data guard broker, we immediately saw issues:

DGMGRL> show configuration;

Configuration - acolvin_dg

  Protection Mode: MaxPerformance
  Members:
  ac4dg - Primary database
    Error: ORA-16778: redo transport error for one or more members

    ac6dg - Physical standby database
      Warning: ORA-16854: apply lag could not be determined

Fast-Start Failover:  Disabled

Configuration Status:
ERROR   (status updated 27 seconds ago)

SQL> select thread#, sequence#, archived, applied from v$archived_log order by 1,2;

   THREAD#  SEQUENCE# ARC APPLIED
---------- ---------- --- ---------
	 1	    7 YES NO
	 1	    8 YES NO
	 1	    9 YES NO
	 1	   10 YES NO
	 1	   11 YES NO
	 1	   12 YES NO
	 1	   13 YES NO
	 1	   14 YES NO

8 rows selected.

Checking the alert log on the primary database, we saw the following logged on instance 2:

2021-04-15T10:11:33.500586-05:00
TT00 (PID:106020): Error 1033 received logging on to the standby
2021-04-15T10:16:38.640301-05:00
TT00 (PID:106020): Error 1033 received logging on to the standby
2021-04-15T10:21:43.804389-05:00
TT00 (PID:106020): Error 1033 received logging on to the standby
2021-04-15T10:26:48.956976-05:00
TT00 (PID:106020): Error 1033 received logging on to the standby
2021-04-15T10:31:54.141234-05:00
TT00 (PID:106020): Error 1033 received logging on to the standby

We also saw issues being logged in the standby alert log on instance 2:

2021-04-14T15:12:08.200276-05:00
PR00 (PID:23854): Error 1017 received logging on to the standby
PR00 (PID:23854): -------------------------------------------------------------------------
PR00 (PID:23854): Check that the source and target databases are using a password file
PR00 (PID:23854): and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
PR00 (PID:23854): and that the SYS password is same in the password files,
PR00 (PID:23854): returning error ORA-16191
PR00 (PID:23854): -------------------------------------------------------------------------
PR00 (PID:23854): FAL: Error 16191 connecting to ac4dg for fetching gap sequence
2021-04-14T15:12:08.211820-05:00
Errors in file /u01/app/oracle/diag/rdbms/ac6dg/ac6dg2/trace/ac6dg2_pr00_23854.trc:
ORA-16191: Primary log shipping client not logged on standby

The logs gave some pretty specific messages, so I began to investigate. My standby creation process was the same as I’d used in the past – copy the password file from the primary to the standby, then after the standby database is cloned, move it to ASM. This was when I remembered that the primary database did not use a shared password file, but a file for each instance located in $ORACLE_HOME/dbs. This database had been originally created before RAC databases supported a shared password file, and the password file had never been updated to a single shared file on ASM.

Sure enough, I compared the password files, and they had a different md5sum between the two nodes. When you’re running RAC, changing the SYS password from sqlplus will only update the password file on the local node. I verified this in my lab by comparing the md5sum of the password files before and after changing the SYS password:

[oracle@enkx4db03c01 ~]$ dcli -l oracle -g dbs_group 'md5sum /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/orapw*'
enkx4db03c01: 74af939a4301ff6a0be9c2a31a1777c9  /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/orapwac4dg1
enkx4db04c01: 74af939a4301ff6a0be9c2a31a1777c9  /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/orapwac4dg2

[oracle@enkx4db03c01 ~]$ dcli -l oracle -g dbs_group 'ls -al /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/orapw*'
enkx4db03c01: -rw-r----- 1 oracle oinstall 2048 Apr 14 14:00 /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/orapwac4dg1
enkx4db04c01: -rw-r----- 1 oracle oinstall 2048 Apr 14 14:00 /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/orapwac4dg2

-- change password --

[oracle@enkx4db03c01 ~]$ dcli -l oracle -g dbs_group 'md5sum /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/orapw*'
enkx4db03c01: 50ba6684c119fbc25960167813c63bae  /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/orapwac4dg1
enkx4db04c01: 74af939a4301ff6a0be9c2a31a1777c9  /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/orapwac4dg2

[oracle@enkx4db03c01 ~]$ dcli -l oracle -g dbs_group 'ls -al /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/orapw*'
enkx4db03c01: -rw-r----- 1 oracle oinstall 2048 Apr 14 14:11 /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/orapwac4dg1
enkx4db04c01: -rw-r----- 1 oracle oinstall 2048 Apr 14 14:00 /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/orapwac4dg2

The first order of business was to get the password file moved to a shared location. We could easily do that with asmcmd’s pwcopy command:

ASMCMD> pwcopy --dbuniquename ac4dg /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/orapwac4dg1 +DATAC1/ac4dg/orapwac4dg
copying /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/orapwac4dg1 -> +DATAC1/ac4dg/orapwac4dg

ASMCMD> ls -l +DATAC1/ac4dg/orapwac4dg
Type      Redund  Striped  Time             Sys  Name
PASSWORD  HIGH    COARSE   APR 15 10:00:00  N    orapwac4dg => +DATAC1/ac4dg/PASSWORD/pwdac4dg.351.1069929821

[oracle@enkx4db03c01 ~]$ srvctl config database -db ac4dg | egrep 'unique|Password'
Database unique name: ac4dg
Password file: +DATAC1/ac4dg/orapwac4dg

That part was simple, but would anything else be required? I initially thought we may need to perform a rolling restart of the instances after moving the password file to a shared location, but it was actually much easier – we just needed to bounce the data guard broker on the primary:

SQL> alter system set dg_broker_start=false sid='*' scope=both;

System altered.

SQL> alter system set dg_broker_start=true  sid='*' scope=both;

System altered.

Once we bounced the broker on the primary database, log shipping picked up, and logs were applied.

DGMGRL> show configuration;

Configuration - acolvin_dg

  Protection Mode: MaxPerformance
  Members:
  ac4dg - Primary database
    ac6dg - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 0 seconds ago)

SQL> select thread#, sequence#, archived, applied from v$archived_log order by 1,2;

   THREAD#  SEQUENCE# ARC APPLIED
---------- ---------- --- ---------
	 1	    7 YES YES
	 1	    8 YES YES
	 1	    9 YES YES
	 1	   10 YES YES
	 1	   11 YES YES
	 1	   12 YES YES
	 1	   13 YES YES
	 1	   14 YES YES
	 2	    7 YES YES
	 2	    8 YES YES
	 2	    9 YES YES

11 rows selected.

Overall, the lesson that I was able to take away from this was that shared password files are more than just a cool feature in RAC – it should be a necessity. Also, it’s very easy to continue to rely on older functionality and methods after numerous upgrades, but sometimes you’re better off by taking advantage of the new features after an upgrade.

 

Leave a Reply

Your email address will not be published. Required fields are marked *