Unable to connect to read only database, Getting error ORA-00604 ORA-16000 (Doc ID 461653.1)

Applies to:

Oracle Server - Enterprise Edition - Version 9.2.0.1 to 10.2.0.1 [Release 9.2 to 10.2]
Information in this document applies to any platform.

Symptoms

A read-only database is cloned from a read-write production database.
The read-only database is brought to sync with the production database everyday and is used only for reporting.

At times, users are unable to connect to the database, otherwise they can login to the read-only database without any problem.

The error they receive is:
ORA-00604: Error occurred at recursive SQL line 1
ORA-16000: Database open for read-only access

Changes

Profile has been set for the users connecting to the read-only database.

SQL> select RESOURCE_NAME,RESOURCE_TYPE,LIMIT from dba_profiles where profile='NEWBIZUSERS';


RESOURCE_NAME                     RESOURCE             LIMIT
----------------------------      --------     --------------------
....
FAILED_LOGIN_ATTEMPTS             PASSWORD        6
PASSWORD_LIFE_TIME                PASSWORD        60
PASSWORD_REUSE_TIME               PASSWORD        UNLIMITED
PASSWORD_REUSE_MAX                PASSWORD        5
PASSWORD_VERIFY_FUNCTION          PASSWORD        VERIFY_PASSWD_8CHAR
PASSWORD_LOCK_TIME                PASSWORD        UNLIMITED
PASSWORD_GRACE_TIME               PASSWORD        0

Cause

Errorstack for the error ORA-16000 shows that the following statement was being executed.

update user$ set name=:2,password=:3,datats#=:4,tempts#=:5,type#=:6,defrole=:7,
resource$=:8,ptime=DECODE(to_char(:9, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL)
, :9),exptime=DECODE(to_char(:10, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :
10),ltime=DECODE(to_char(:11, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :11),
astatus=:12, lcount=:13, defschclass=:14 where user#=:1

In a read only database, update can't happen when the users try to connect, so the users were unable to connect and got the error
ORA-00604: Error occurred at recursove SQL line 1
ORA-16000: Database open for read-only access.

The field LCOUNT keeps track of the number of unsuccessful logins. It is reset when a successful login is made.

If for a user there is LCOUNT >0 in Production (which is a read write), that count is pushed to the READ
database after sync. Then the user attempts to log on to the READ with a correct password. Oracle
recognizes this and attempts to reset the LCOUNT in user$ in database, for this user id, to zero.
Because in a read only database, update can't happen when the users try to connect, so the users were unable to connect and got the error
ORA-00604: Error occurred at recursove SQL line 1
ORA-16000: Database open for read-only access.

Solution

For the users to always successfully connect to the read only database, the LCOUNT should never be > 0.
This can possibly be worked around either by disabling the profiles for the users, or by removing the FAILED_LOGIN_ATTEMPTS or setting it to unlimited.

==========

In a standby environment this could also be:
Bug 7581964 - Incorrect login to primary causes ORA-16000 on login to standby database
Applying the fix for bug 7581964 may resolve the issue.