Unable to connect to read only database, Getting error ORA-00604 ORA-16000 [ID 461653.1]
Information in this document applies to any platform.
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
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.
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.
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.
No comments:
Post a Comment