Introduction

Password file is the heart of the Data Guard configuration, if in case either the password file is missing or required permissions on password file are not applicable or naming format is different in those cases the primary cannot communicate with standby database and vice versa. In this article we are going to see managing password file based on the various issues have experienced and seen from community forums.

Copying or replacing the password file.

While creating standby database by default we will place the password file in $ORACLE_HOME/dbs location, if in case the password file is corrupted or missing then we can copy the password file from primary. But there are numerous changes in managing password file. For example if primary database created using DBCA then the password file automatically created and now in order to place the password file then we have to create new password file until 10gR2 and now the story changed and starting form 11g we MUST copy the password file from primary database, but many users in same assumption that copy of password file from primary should work.. In fact it is misconception.

So less learned? Then we are not creating new password file to standby server but we are copying password file from primary. The mission is dead easy because it is purely OS level commands and straight forward. If database is created using manual method then initially we have to create password file and further we have to copy the password file to all the available databases.

[oracle@ora-r2n1 dbs]$ orapwd file=orapw$ORACLE_SID password=0a1b2c3 force=y entries=20

[oracle@ora-r2n1 dbs]$ ls -ltr orapwhapr11

-rw-r----- 1 oracle oinstall 12800 Oct  8 01:05 orapwhapr11

[oracle@ora-r2n1 dbs]$ scp orapwhapr11 oracle@ora-r2n2:/u01/app/oracle/product/12.1.0.1/db_1/dbs/

orapwhapr11                                                                                                            100%   13KB  12.5KB/s   00:00

[oracle@ora-r2n1 dbs]$ ssh ora-r2n2

[oracle@ora-r2n2 ~]$ cd /u01/app/oracle/product/12.1.0.1/db_1/dbs/

[oracle@ora-r2n2 dbs]$ ls -ltr orapwhapr11

-rw-r----- 1 oracle oinstall 12800 Oct  8 01:07 orapwhapr11

[oracle@ora-r2n2 dbs]$

 

Comparing password file

In many and many situations based on my experience I have seen that password file is exit in $ORACLE_HOME/dbs but unable to connect with the error “username or password invalid”, this may be applicable for even non-Data Guard but we are discussing purely on Data Guard where standby is able to connect but primary unable to connect standby database. In this situation below things we can crosscheck.

  1. Check password file name is valid
  2. Check password file permissions
  3. Check password file ownership user and group
  4. Compare check sum.

For a,b,c and d points it is easy to check using “ls” command

[oracle@ora-r2n2 dbs]$ ls -ltr orapwhapr11

-rw-r----- 1 oracle oinstall 12800 Oct  8 01:07 orapwhapr11

[oracle@ora-r2n2 dbs]$ exit

logout

Connection to ora-r2n2 closed.

[oracle@ora-r2n1 dbs]$ ls -ltr orapwhapr11

-rw-r----- 1 oracle oinstall 12800 Oct  8 01:05 orapwhapr11

[oracle@ora-r2n1 dbs]$

  1. Check view v$pwfile_users

If the password file is accessed by Oracle then we can crosscheck by using the view, If there are no rows appeared then we can assume that password file having issues.

SQL> select * from v$pwfile_users;

 

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID

------------------------------ ----- ----- ----- ----- ----- ----- ----------

SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0

SYSDG                          FALSE FALSE FALSE FALSE TRUE  FALSE          0

SYSBACKUP                      FALSE FALSE FALSE TRUE  FALSE FALSE          0

SYSKM                          FALSE FALSE FALSE FALSE FALSE TRUE           0

NASSYAM                        TRUE  FALSE FALSE FALSE TRUE  FALSE          0

 

SQL>

Now the checksum comparison can be performed using the OS additional command called “md5sum” which uses internal architecture of 128bit algorithm, this helps a lot to know if in case there is any check sum mismatch during copy across the network.

[oracle@ora-r2n1 dbs]$ ls -ltr orapwhapr11

-rw-r----- 1 oracle oinstall 12800 Oct  8 01:05 orapwhapr11

[oracle@ora-r2n1 dbs]$ md5sum orapwhapr11

89081fcc812a58c7c66b98aef1d04b6c  orapwhapr11

[oracle@ora-r2n1 dbs]$ ssh ora-r2n2

Last login: Sat Oct  8 01:07:28 2016 from ora-r2n1.oracle-ckpt.com

[oracle@ora-r2n2 ~]$ cd /u01/app/oracle/product/12.1.0.1/db_1

[oracle@ora-r2n2 db_1]$ cd dbs/

[oracle@ora-r2n2 dbs]$ md5sum orapwhapr11

89081fcc812a58c7c66b98aef1d04b6c  orapwhapr11

[oracle@ora-r2n2 dbs]$

 

Above we can compare the md5sum output and thus we can come to conclusion if the password file is correct or not using the above 4 points.

Please note that in Unix/Linux the password file naming as “orapw$ORACLE_SID”, where in case of windows system it is “PWD%ORACLE_SID%.ora”, this is the major difference in password file when it comes windows.

Changing sys password when Data Guard Broker Involved

Consider we have Data Guard configuration of 12.1.0.2 with Far Sync is involved and the situation is the “SYS” password is working nor somewhere stored or it is requirement from customer, hence we have to change the sys password file. In this case we have to change the SYS user password and then we have to ensure working on standby databases as well. Below is the configuration details that the redo transport will be sending first from primary database to Far Sync instance and then Far Sync will transport redo to Standby database.

 

  1. Check confiuration

DGMGRL> show configuration

 

Configuration - hadg

 

  Protection Mode: MaxPerformance

  Members:

  canada - Primary database

 

  Members Not Receiving Redo:

  india  - Physical standby database (alternate of CANFAR)

  CANFAR - Far sync instance

 

Fast-Start Failover: DISABLED

 

Configuration Status:

SUCCESS   (status updated 49 seconds ago)

 

DGMGRL>

  1. Check the current connectivity status.

If we see below we are able to connect all the sites using the current password, this is how we can validate first whether the current password is working on all the sites of Primary, Far Sync and standby databases.

[oracle@ORA-C1 ~]$ sqlplus sys/oracle@canada as sysdba

 

SQL*Plus: Release 12.1.0.2.0 Production on Sat Oct 8 09:19:29 2016

 

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

 

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@ORA-C1 ~]$ sqlplus sys/oracle@canfar as sysdba

 

SQL*Plus: Release 12.1.0.2.0 Production on Sat Oct 8 09:19:38 2016

 

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

 

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@ORA-C1 ~]$ sqlplus sys/oracle@india as sysdba

 

SQL*Plus: Release 12.1.0.2.0 Production on Sat Oct 8 09:19:45 2016

 

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

 

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@ORA-C1 ~]$

  1. Disable redo transport from primary to remote destinations.

In this our setup primary will be sending reo to far sync instance and also sends to standby database when there is no redo routes to far sync instance. Hence we are stopping redo

[oracle@ORA-C1 ~]$ dgmgrl /

DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

 

Copyright (c) 2000, 2013, Oracle. All rights reserved.

 

Welcome to DGMGRL, type "help" for information.

Connected as SYSDG.

DGMGRL> show database canada 'LogShipping'

  LogShipping = 'ON'

DGMGRL> edit database canada set property LogShipping='OFF';

Property "logshipping" updated

DGMGRL> show database canada 'LogShipping'

  LogShipping = 'OFF'

DGMGRL>

DGMGRL> show far_sync 'CANFAR' LogShipping

  LogShipping = 'ON'

DGMGRL> edit far_sync 'CANFAR' set property LogShipping='OFF';

Property "logshipping" updated

DGMGRL> show far_sync 'CANFAR' LogShipping

  LogShipping = 'OFF'

DGMGRL>

  1. Change the password

Now change the password based on the requirement as below from primary database.

SQL> alter user sys identified by welcome123;

 

User altered.

 

SQL>

  1. Test connectivity – perform the connectivity test to all the databases and we should expect invalid username and password from Far Sync and the standby database.

[oracle@ORA-C1 ~]$ sqlplus sys/welcome123@canada as sysdba

 

SQL*Plus: Release 12.1.0.2.0 Production on Sat Oct 8 09:34:37 2016

 

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

 

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@ORA-C1 ~]$ sqlplus sys/welcome123@canfar as sysdba

 

SQL*Plus: Release 12.1.0.2.0 Production on Sat Oct 8 09:34:46 2016

 

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

 

ERROR:

ORA-01017: invalid username/password; logon denied

 

 

Enter user-name: ^C

[oracle@ORA-C1 ~]$ sqlplus sys/welcome123@india as sysdba

 

SQL*Plus: Release 12.1.0.2.0 Production on Sat Oct 8 09:34:55 2016

 

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

 

ERROR:

ORA-01017: invalid username/password; logon denied

 

 

Enter user-name: ^C

 

  1. Copy the password file

We have seen above that we are unable to connect to either standby or Far sync instance after changes to the sys password. Now we have to copy the password file to all other destinations.

[oracle@ORA-C1 ~]$ cd $ORACLE_HOME/dbs

[oracle@ORA-C1 dbs]$ scp orapwORC1 192.168.0.90:/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/

oracle@192.168.0.90's password:

orapwORC1                                                                                                              100% 7680     7.5KB/s   00:00

[oracle@ORA-C1 dbs]$ scp orapwORC1 192.168.0.120:/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/

oracle@192.168.0.120's password:

orapwORC1                                                                                                              100% 7680     7.5KB/s   00:00

[oracle@ORA-C1 dbs]$

 

  1. Check connectivity  - After copying the password file to the remote destinations now we can able to connect to all the destinations.

[oracle@ORA-C1 dbs]$ sqlplus sys/welcome123@canada as sysdba

 

SQL*Plus: Release 12.1.0.2.0 Production on Sat Oct 8 09:39:12 2016

 

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

 

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@ORA-C1 dbs]$ sqlplus sys/welcome123@canfar as sysdba

 

SQL*Plus: Release 12.1.0.2.0 Production on Sat Oct 8 09:39:19 2016

 

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

 

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@ORA-C1 dbs]$ sqlplus sys/welcome123@india as sysdba

 

SQL*Plus: Release 12.1.0.2.0 Production on Sat Oct 8 09:39:27 2016

 

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

 

SQL>

  1. Enable redo transport – Like wise we have disabled log shipping now perform same to enable log shipping from both destinations Canada and canfar.

DGMGRL> edit database canada set property LogShipping='ON';

Property "logshipping" updated

DGMGRL> edit database canfar set property LogShipping='ON';

Object "canfar" was not found

DGMGRL> edit far_sync 'CANFAR' set property LogShipping='ON';

Property "logshipping" updated

DGMGRL> show database canada logshipping

  LogShipping = 'ON'

DGMGRL> show far_sync 'CANFAR' LogShipping

  LogShipping = 'ON'

DGMGRL>

  1. Check configuration

DGMGRL> show configuration

 

Configuration - hadg

 

  Protection Mode: MaxPerformance

  Members:

  canada - Primary database

 

  Members Not Receiving Redo:

  india  - Physical standby database (alternate of CANFAR)

  CANFAR - Far sync instance

 

Fast-Start Failover: DISABLED

 

Configuration Status:

SUCCESS   (status updated 43 seconds ago)

 

DGMGRL>

 

Summary

We’ve seen starting from the introduction and how crucial role plays in Data Guard configuration with the various reasons why the remote authentication fails from primary to standby or vice versa and also explained various reasons with demonstration and importantly we used the md5sum command to check the output in order to determine the password file status. In another demonstration we can see how to change the password file in Data Guard environment with Far Sync environment and how safely we can accomplish the task. Next linked article will be soon available on “how to manage password file in RAC Data guard environment and various other new features”

References

https://docs.oracle.com/cd/E11882_01/server.112/e41134/log_transport.htm#SBYDB00523