DataGuard switchover with wallet: ORA-01017

As mentioned in my article Nothing to hide, Oracle offers us to register the database connection credentials in a file named wallet, and of course this applies also to our interaction with Data Guard Broker.

However, if this is not implemented correctly, we may encounter errors such as ORA-01017, that will cause serious headaches.

Initial scenario

We have the orcl database, with a primary copy called orcl1 and a physical standby called orcl2, according to the following detail:

db_unique_nametnsroleinstance_namevip
orcl1orcl1_dgprimaryorcl1_1172.30.10.41
orcl1_2172.30.10.42
orcl2orcl2_dgstandbyorcl2_1172.30.10.85
orcl2_1172.30.10.86

We have a wallet, with an entry for orcl1 called orcl1_dg, and another for orcl2 called orcl2_dg.

The connectivity is tested with SQL*Plus and everything works correctly.

[oracle@node1 ~]$ mkstore -wrl /wallet -listCredential

List credential (index: connect_string username)
2: orcl2_dg sys
1: orcl1_dg sys

[oracle@node1 ~]$ sqlplus /@orcl1_dg as sysdba
SQL> exit;

[oracle@node1 ~]$ sqlplus /@orcl2_dg as sysdba
SQL> exit;

We also have Data Guard Broker properly configured and operating.

[oracle@node1 ~]$ dgmgrl /@orcl1_dg
Welcome to DGMGRL, type "help" for information.
Connected.

DGMGRL> show configuration

Configuration - dg_orcl

  Protection Mode: MaxPerformance
  Databases:
    orcl1 - Primary database
    orcl2 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database orcl1

Database - orcl1

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    orcl1_1
    orcl1_2

Database Status:
SUCCESS


DGMGRL> show database orcl2

Database - orcl2

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 0 seconds ago)
  Apply Lag:       0 seconds (computed 0 seconds ago)
  Apply Rate:      0 Byte/s
  Real Time Query: OFF
  Instance(s):
    orcl2_2

Database Status:
SUCCESS

The problem

We try to execute a switchover from orcl1 to orcl2.

Since everything is set up correctly, what could go wrong?

DGMGRL> switchover to orcl;
Performing switchover NOW, please wait...
Operation requires a connection to instance "orcl2_2" on database "orcl2"
Connecting to instance "orcl2_2"...
Connected.
New primary database "orcl2" is opening...
Operation requires startup of instance "orcl1_2" on database "orcl1"
Starting instance "orcl1_2"...
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

Please complete the following steps to finish switchover:
        start up and mount instance "orcl1_2" of database "orcl1"

Bad news! We see that orcl2 has assumed the primary role and orcl1 the physical standby role, but it has not been started, we will have to do it manually and check that everything has been left operating without additional problems.

[oracle@node1 ~]$ srvctl start database -d orcl1 -o mount

[oracle@node1 ~]$ dgmgrl /@orcl2_dg
Welcome to DGMGRL, type "help" for information.
Connected.

DGMGRL> show configuration

Configuration - dg_orcl

  Protection Mode: MaxPerformance
  Databases:
    orcl2 - Primary database
    orcl1 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

The solution

Luckily, in My Oracle Support there is a document that will help us to overcome our problem.

2856930.1Switchover using wallet fails with ORA-1017

During broker switchover operations database need to be restarted, so StaticConnectIdentifier will be used to connect with database. Therefore, we need to add both DGConnectIdentifier and StaticConnectIdentifier in the external password store (or) wallet for all databases to avoid authentication failures during DG switchover operations.

Bingo! We have to get the connection string registered under the StaticConnectIdentifier property of each instance of each database, and register them in the wallet.

Let’s see how they are obtained:

DGMGRL> show instance orcl1_1 StaticConnectIdentifier;

  StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.30.10.41)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl1_DGMGRL)(INSTANCE_NAME=orcl1_1)(SERVER=DEDICATED)))'

If we compare with the table presented at the beginning, we notice that the connection string includes to the IP of the vip of the node in which the consulted instance is operating.

With this new information we proceed to register new entries in the wallet.

[oracle@node1 ~]$ mkstore -wrl /wallet -createCredential '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.30.10.41)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl1_DGMGRL)(INSTANCE_NAME=orcl1_1)(SERVER=DEDICATED)))' sys Oracle_4U

[oracle@node1 ~]$ mkstore -wrl /wallet -createCredential '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.30.10.42)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl1_DGMGRL)(INSTANCE_NAME=orcl1_2)(SERVER=DEDICATED)))' sys Oracle_4U

[oracle@node1 ~]$ mkstore -wrl /wallet -createCredential '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.30.10.85)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl2_DGMGRL)(INSTANCE_NAME=orcl2_2)(SERVER=DEDICATED)))' sys Oracle_4U

[oracle@node1 ~]$ mkstore -wrl /wallet -createCredential '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.30.10.86)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl2_DGMGRL)(INSTANCE_NAME=orcl2_2)(SERVER=DEDICATED)))' sys Oracle_4U

We check that everything is registered.

[oracle@node1 ~]$ mkstore -wrl /wallet -listCredential

List credential (index: connect_string username)
6: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.30.10.41)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl2_DGMGRL)(INSTANCE_NAME=orcl2_2)(SERVER=DEDICATED))) sys
5: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.30.10.42)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl2_DGMGRL)(INSTANCE_NAME=orcl2_2)(SERVER=DEDICATED))) sys
4: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.30.10.85)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl1_DGMGRL)(INSTANCE_NAME=orcl1_2)(SERVER=DEDICATED))) sys
3: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.30.10.86)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl1_DGMGRL)(INSTANCE_NAME=orcl1_1)(SERVER=DEDICATED))) sys
2: orcl2_dg sys
1: orcl1_dg sys

Let’s cross our fingers and make a second attempt. This time the switchover will be from orcl2 to orcl1.

DGMGRL> switchover to orcl1;
Performing switchover NOW, please wait...
Operation requires a connection to instance "orcl1_1" on database "orcl1"
Connecting to instance "orcl1_1"...
Connected.
New primary database "orcl1" is opening...
Operation requires startup of instance "orcl2_2" on database "orcl2"
Starting instance "orcl2_2"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "orcl1"

Confirmed: the switchover was executed cleanly!

The orcl2 database could be started without problems, thanks to the fact that the appropriate connection string is already registered in the wallet.

What we Consultants keep silent

It wasn’t that complicated, was it? Well, I would like to tell you that although it seems that obtaining the solution was something simple, there is another Note in My Oracle Support that presents an alternative solution, much simpler, and that in fact I had been using in previous projects for more than a decade.

In this one we had it operating for several weeks, but we noticed that it caused that, randomly, hundreds of sessions were created in the ASM instances of both Clusters, reaching the limit of the processes parameter, with all that this implies.

Although we opened a Service Request with severity 1, after more than a month we did not get a solution, except a “stop using the wallet”, so we had to choose to implement this alternative. Although it implied making changes to what was already implemented, it was inevitable and it is undoubtedly a more stable solution, so it has become the method to use for future projects.

Did you find this article interesting, did you have any doubts, do you want to suggest a topic to cover, leave me your comments or contact me me right now!

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Related Posts

Learn how to identify the row involved in the occurrence of the wait even "enq: TX - row lock contention"
Learn how to resolve the CRS-2304 GPnP profile signature verification failed error when starting an 11.2 database on a 19c cluster.
Learn how to fix corrupted permissions on an Oracle Home, either Oracle Grid or Oracle Database Server.

Need Help?

Fill in these details and I will be in touch as soon as possible.