My Oracle Support Banner

Session Failover Hangs if ORA-28002 is Signaled in The Session (Doc ID 2508288.1)

Last updated on JANUARY 06, 2020

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 to 19.1.0.0.0 [Release 12.1 to 18]
Information in this document applies to any platform.

Symptoms

o This is a RAC Database with a Service defined using Transparent Application Failover (TAF):

srvctl add service -d <DBNAME> -s <SERVICE NAME> -r <SID1> -a <SID2> -q TRUE -e SESSION -m BASIC -w 5 -z 200

o Also the environment have a Database Profile defined to expire the users password (PASSWORD_LIFE_TIME)

o If a database connection almost reaches the PASSWORD_LIFE_TIME and a service failovers the session reconnecting will produce the error ORA-28002 and the session will seems to hang.

For example:

$sqlplus <USER>/<PASSWORD>@<SERVICE NAME>

SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 14 08:33:38 2017

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

ERROR:

ORA-28002: the password will expire within 2 days

 

Last Successful login time: Thu Aug 10 2017 08:19:33 +00:00

Connected to:

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

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics, Real Application Testing and Unified Auditing options

SQL> select host_name, instance_name from v$instance;

HOST_NAME        INSTANCE_NAME

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

<nodename2>         <SID2>

SQL> !srvctl relocate service -d <DBNAME> -s <SERVICE> -i <SID2> -t <SID1> -f

SQL> select host_name from v$instance;

--- hangs here ----

Changes

 No changes were done recently 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.