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_name | tns | role | instance_name | vip |
|---|---|---|---|---|
| orcl1 | orcl1_dg | primary | orcl1_1 | 172.30.10.41 |
| orcl1_2 | 172.30.10.42 | |||
| orcl2 | orcl2_dg | standby | orcl2_1 | 172.30.10.85 |
| orcl2_1 | 172.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:
SUCCESSThe 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:
SUCCESSThe solution
Luckily, in My Oracle Support there is a document that will help us to overcome our problem.
| 2856930.1 | Switchover 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_4UWe 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 sysLet’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.