Data Guard

How to Resolve ORA-01017 in DGMGRL Switchover

ORA-01017 in DGMGRL Switchover

ORA-01017 means that the credentials you provides are not correct, either the username cannot be recognized by the database instance or the password does not match the user’s. In DGMGRL, ORA-01017 may cause switchover to be interrupted. But luckily, the interruptions are not serious.

There’re two error patterns for ORA-01017 in DGMGRL Switchover, one is common mistake, the other is pretty hard to troubleshoot.

  1. OS Authentication
  2. Case-Sensitive Name

ORA-01017 due to OS Authentication

We are able to connect to databases by OS authentication to check data guard status.

[oracle@primary-19c ~]$ dgmgrl
...
DGMGRL> connect /
Connected to "PRIMDB"
Connected as SYSDG.

For example, we can show broker configuration.

DGMGRL> show configuration verbose;

Configuration - drconf

  Protection Mode: MaxPerformance
  Members:
  primdb - Primary database
    standb - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'
    ExternalDestination1            = ''
    ExternalDestination2            = ''
    PrimaryLostWriteAction          = 'CONTINUE'
    ConfigurationWideServiceName    = 'COMPDB_CFG'

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS

Also, we can show the status of primary or standby databases.

DGMGRL> show database primdb;

Database - primdb

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

Database Status:
SUCCESS

DGMGRL> show database standb;

Database - standb

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 1.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    STANDB

Database Status:
SUCCESS

But we can’t perform a switchover.

DGMGRL> switchover to standb;
Performing switchover NOW, please wait...
Operation requires a connection to database "standb"
Connecting ...
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

        connect to instance "STANDB" of database "standb"

Since we provided empty credentials, the broker used the empty username and password to connect to the standby database for a switchover. That’s why we got ORA-01017.

It just like we try to connect to a database with empty credentials like this:

[oracle@primary-19c ~]$ sqlplus /@standb
...
ERROR:
ORA-01017: invalid username/password; logon denied

It’s the same pattern of ORA-01017.

Solution to ORA-01017

Please explicitly provide user credentials (username/password pair) to connect to database in DGMGRL. Usually, we use SYS to operate switchovers in DGMGRL.

DGMGRL> connect sys@primdb;
Password:
Connected to "PRIMDB"
Connected as SYSDBA.

Then we perform a switchover.

DGMGRL> switchover to standb;
Performing switchover NOW, please wait...
Operation requires a connection to database "standb"
Connecting ...
Connected to "STANDB"
Connected as SYSDBA.
New primary database "standb" is opening...
Operation requires start up of instance "PRIMDB" on database "primdb"
Starting instance "PRIMDB"...
Connected to an idle instance.
ORACLE instance started.
Connected to "PRIMDB"
Database mounted.
Database opened.
Connected to "PRIMDB"
Switchover succeeded, new primary is "standb"

Good, no more ORA-01017.

ORA-01017 due to Case-Sensitive Name

This error case could be rare and complicated, but we can still learn something from it. So be patient with it.

Data Guard Configuration

Let’s check some data guard configurations before switching over to the standby database.

Check Static Service for DGMGRL in listener.ora

For switching over smoothly, we usually add a very special static service to listener for data guard broker.

Primary Server
...
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (SID_NAME=primdb)
      (GLOBAL_DBNAME=primdb_DGMGRL)
      (ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1)
    )
  )

Standby Server
[oracle@standby-19c ~]$ cat $ORACLE_HOME/network/admin/listener.ora
...
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (SID_NAME=standb)
      (GLOBAL_DBNAME=standb_DGMGRL)
      (ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1)
    )
  )

Check Listener Status

Primary Server
[oracle@primary-19c ~]$ lsnrctl status
...
Services Summary...
Service "COMPDB_CFG" has 1 instance(s).
  Instance "PRIMDB", status READY, has 1 handler(s) for this service...
Service "PRIMDB" has 1 instance(s).
  Instance "PRIMDB", status READY, has 1 handler(s) for this service...
Service "PRIMDBXDB" has 1 instance(s).
  Instance "PRIMDB", status READY, has 1 handler(s) for this service...
Service "primdb_DGMGRL" has 1 instance(s).
  Instance "primdb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Standby Server
[oracle@standby-19c ~]$ lsnrctl status
...
Services Summary...
Service "COMPDB_CFG" has 1 instance(s).
  Instance "STANDB", status READY, has 1 handler(s) for this service...
Service "STANDB" has 1 instance(s).
  Instance "STANDB", status READY, has 1 handler(s) for this service...
Service "STANDBXDB" has 1 instance(s).
  Instance "STANDB", status READY, has 1 handler(s) for this service...
Service "standb_DGMGRL" has 1 instance(s).
  Instance "standb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

As we can see, the special static services for DGMGRL are working on both listeners.

Switchover by DGMGRL

First of all, we have to connect to the primary database in DGMGRL.

Connect to Data Guard Broker

[oracle@primary-19c ~]$ dgmgrl
...
DGMGRL> connect sys@primdb
Password:
Connected to "PRIMDB"
Connected as SYSDBA.

First Attempt of Switchover

DGMGRL> switchover to standb;
Performing switchover NOW, please wait...
Operation requires a connection to database "standb"
Connecting ...
Connected to "STANDB"
Connected as SYSDBA.
New primary database "standb" is opening...
Operation requires start up of instance "PRIMDB" on database "primdb"
Starting instance "PRIMDB"...
ORA-01017: invalid username/password; logon denied


Please complete the following steps to finish switchover:
        start up instance "PRIMDB" of database "primdb"

We found ORA-01017 during switching over. It seemed that the broker tried to connect to the new standby database (former primary) PRIMDB, but it failed with ORA-01017.

No matter what cause it could be, we should startup the new standby database for data synchronization.

Startup Standby Database

We startup the instance manually by running dbstart in order to recover the data guard synchronization.

[oracle@primary-19c ~]$ dbstart
Since ORACLE_HOME is not set, cannot auto-start Oracle Net Listener.
Usage: /u01/app/oracle/product/19.0.0/dbhome_1/bin/dbstart ORACLE_HOME
Processing Database instance "PRIMDB": log file /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/log/startup.log

By the way, we usually use dbstart to automatically startup the instance and the listener on system boot.

Then we checked the status.

[oracle@primary-19c ~]$ sqlplus / as sysdba
...
SQL> select name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
COMPDB    READ ONLY WITH APPLY PHYSICAL STANDBY

Please note that, READ ONLY WITH APPLY is a feature of active data guard, which is called real-time query.

DGMGRL Problem Tracing

Here comes the most important part. For tracing the connection problem that caused ORA-01017 in DGMGRL, we enabled the debug mode.

Connect to Data Guard Broker

We added -debug option for DGMGRL utility to enable debug mode.

[oracle@primary-19c ~]$ dgmgrl -debug
Created directory /u01/app/oracle/product/19.0.0/dbhome_1/dataguard
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Aug 13 21:22:52 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@primdb
Password:
[W000 2019-08-13T21:23:03.360+08:00] Connecting to database using primdb.
[W000 2019-08-13T21:23:03.361+08:00] Attempt logon as SYSDBA
[W000 2019-08-13T21:23:03.421+08:00] Successfully logged on as SYSDBA
[W000 2019-08-13T21:23:03.421+08:00] Executing query [select sys_context('USERENV','CON_ID') from dual].
[W000 2019-08-13T21:23:03.423+08:00] Query result is '0'
[W000 2019-08-13T21:23:03.423+08:00] Executing query [select value from v$parameter where name = 'db_unique_name'].
[W000 2019-08-13T21:23:03.430+08:00] Query result is 'PRIMDB'
Connected to "PRIMDB"
[W000 2019-08-13T21:23:03.431+08:00] Checking broker version [BEGIN :version := dbms_drs.dg_broker_info('VERSION'); END;].
[W000 2019-08-13T21:23:03.440+08:00] Oracle database version is '19.3.0.0.0'
Connected as SYSDBA.

We’re in debug mode of DGMGRL.

Second Attempt of Switchover

In fact, it’s a switch back operation within debug mode.

DGMGRL> switchover to primdb;
[W000 2019-08-13T21:23:39.512+08:00] <DO_MONITOR version="19.1"><VERIFY object_id="4096" level="minor"/></DO_MONITOR>


[W000 2019-08-13T21:23:39.555+08:00] <RESULT ><MESSAGE ><SUCCESS ></SUCCESS></MESSAGE><MESSAGE ><SUCCESS ></SUCCESS></MESSAGE></RESULT>


Performing switchover NOW, please wait...
[W000 2019-08-13T21:23:39.563+08:00] <DO_CONTROL version="19.1"><DO_MOVE type="Switchover" site_id="16777216"/></DO_CONTROL>


[W000 2019-08-13T21:24:09.465+08:00] <TABLE  name="DG BROKER CLIENT OPERATION LIST"><DESCRIPTION ><COLUMN  name="OPERATION" type="string" max_length="20"></COLUMN><COLUMN  name="INSTANCE_ID" type="integer" max_length="30"></COLUMN><COLUMN  name="CONNECT" type="string" max_length="4095"></COLUMN></DESCRIPTION><TR ><TD >OPENING</TD><TD >16842753</TD><TD >(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=primary-19c)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PRIMDB_DGMGRL)(INSTANCE_NAME=PRIMDB)(SERVER=DEDICATED)))</TD></TR><TR ><TD >STARTUP</TD><TD >33619969</TD><TD >(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=standby-19c)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STANDB_DGMGRL)(INSTANCE_NAME=STANDB)(SERVER=DEDICATED)))</TD></TR></TABLE>


New primary database "primdb" is opening...
Operation requires start up of instance "STANDB" on database "standb"
Starting instance "STANDB"...
[W000 2019-08-13T21:24:12.470+08:00] Connecting to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=standby-19c)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STANDB_DGMGRL)(INSTANCE_NAME=STANDB)(SERVER=DEDICATED))).
[W000 2019-08-13T21:24:12.470+08:00] Attempt logon as SYSDBA
ORA-01017: invalid username/password; logon denied


Please complete the following steps to finish switchover:
        start up instance "STANDB" of database "standb"

In the above log, the broker tried to connect to the standby database STANDB, but it failed with ORA-01017. The best thing is that it showed the connect descriptor that it used to connect to the database.

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=standby-19c)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STANDB_DGMGRL)(INSTANCE_NAME=STANDB)(SERVER=DEDICATED)))

Please note that, the service name used to connect is a static service. That is to say, we can connect the database with the static service, no matter it is idle, nomount or restricted as long as the listener is up.

Test the Connect Descriptor

We used sqlplus to test the connect descriptor. Let’s see how I use it.

[oracle@primary-19c ~]$ sqlplus sys@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=standby-19c)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STANDB_DGMGRL)(INSTANCE_NAME=STANDB)(SERVER=DEDICATED)))" as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 13 21:28:30 2019
Version 19.3.0.0.0

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

Enter password:
ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:

Oh, it’s true, I cannot connect to the database with the connect descriptor. The connection failed with ORA-01017.

It looked like that it can reach the right listener and find the right Oracle home (ORACLE_HOME), but it cannot find the right instance (SID_NAME) to enter.

What’s wrong? What the difference between the connect descriptor and the listener configuration? Can you tell?

Startup Standby Database

Although we are in troubleshooting, we still need to startup the instance as soon as possible in order to recover the data guard synchronization.

[oracle@standby-19c ~]$ dbstart
Since ORACLE_HOME is not set, cannot auto-start Oracle Net Listener.
Usage: /u01/app/oracle/product/19.0.0/dbhome_1/bin/dbstart ORACLE_HOME
Processing Database instance "STANDB": log file /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/log/startup.log
[oracle@standby-19c ~]$ sqlplus / as sysdba
...
SQL> select name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
COMPDB    READ ONLY WITH APPLY PHYSICAL STANDBY

Solution to ORA-01017

A tiny difference I found is that the names used in the connect descriptor by the broker are all upper-cased, but the names for static service in listener.ora are lower-cased. Could it be the cause to ORA-01017?

Modify listener.ora

It seemed a little stupid, but I still changed all names in upper-case on both listener configurations to comply with the broker’s behaviors.

Primary Server
[oracle@primary-19c ~]$ vi $ORACLE_HOME/network/admin/listener.ora
...
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (SID_NAME=PRIMDB)
      (GLOBAL_DBNAME=PRIMDB_DGMGRL)
      (ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1)
    )
  )

Then we restarted the listener.

[oracle@primary-19c ~]$ lsnrctl stop; lsnrctl start
...
[oracle@primary-19c ~]$ lsnrctl status
...
Services Summary...
Service "COMPDB_CFG" has 1 instance(s).
  Instance "PRIMDB", status READY, has 1 handler(s) for this service...
Service "PRIMDB" has 1 instance(s).
  Instance "PRIMDB", status READY, has 1 handler(s) for this service...
Service "PRIMDBXDB" has 1 instance(s).
  Instance "PRIMDB", status READY, has 1 handler(s) for this service...
Service "PRIMDB_DGMGRL" has 1 instance(s).
  Instance "PRIMDB", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Standby Server
[oracle@standby-19c ~]$ vi $ORACLE_HOME/network/admin/listener.ora
...
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (SID_NAME=STANDB)
      (GLOBAL_DBNAME=STANDB_DGMGRL)
      (ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1)
    )
  )

Then we restarted the listener.

[oracle@standby-19c ~]$ lsnrctl stop; lsnrctl start
...
[oracle@standby-19c ~]$ lsnrctl status
...
Services Summary...
Service "COMPDB_CFG" has 1 instance(s).
  Instance "STANDB", status READY, has 1 handler(s) for this service...
Service "STANDB" has 1 instance(s).
  Instance "STANDB", status READY, has 1 handler(s) for this service...
Service "STANDBXDB" has 1 instance(s).
  Instance "STANDB", status READY, has 1 handler(s) for this service...
Service "STANDB_DGMGRL" has 1 instance(s).
  Instance "STANDB", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Third Attempt of Switchover

[oracle@primary-19c ~]$ dgmgrl
...
DGMGRL> connect sys@primdb
Password:
Connected to "PRIMDB"
Connected as SYSDBA.
DGMGRL> switchover to standb;
Performing switchover NOW, please wait...
Operation requires a connection to database "standb"
Connecting ...
Connected to "STANDB"
Connected as SYSDBA.
New primary database "standb" is opening...
Operation requires start up of instance "PRIMDB" on database "primdb"
Starting instance "PRIMDB"...
Connected to an idle instance.
ORACLE instance started.
Connected to "PRIMDB"
Database mounted.
Database opened.
Connected to "PRIMDB"
Switchover succeeded, new primary is "standb"

Good, we switch over to the standby database smoothly. Try to switch back to the primary database.

DGMGRL> switchover to primdb;
Performing switchover NOW, please wait...
Operation requires a connection to database "primdb"
Connecting ...
Connected to "PRIMDB"
Connected as SYSDBA.
New primary database "primdb" is opening...
Operation requires start up of instance "STANDB" on database "standb"
Starting instance "STANDB"...
Connected to an idle instance.
ORACLE instance started.
Connected to "STANDB"
Database mounted.
Database opened.
Connected to "STANDB"
Switchover succeeded, new primary is "primdb"

To my surprise, ORA-01017 in this case turned out to be an instance name mismatch problem caused by case-sensitive SID_NAME in listener.ora.

Please note that, the key action to troubleshoot the problem is to enable debug mode of DGMGRL to check any clues related to the database connection.

Leave a Reply

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