This content has been marked as final. Show 4 replies
What I would do is fix the password on the Primary.
Stop redo apply on the Standby and shutdown the database. ( ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL ) ( SHUTDOWN - will remind you its not open )
Copy/Move the password file from the Primary server to the Standby Server. ( Rename as needed )
Example ( rename after move to Standby ) :
Then Startup Mount ( this will use the new password file - very important )
mv /u01/app/oracle/product/11.2.0/dbs/orapwPRIMARY /u01/app/oracle/product/11.2.0/dbs/orapwSTANDBY
Then start redo apply again. The issue should be solved.
In Oracle 11 you can no longer just create a new password file for the physical standby database.
You musts always copy the primary database password file to all physical standby databases whenever a privileged user's password is changed.
On my system I created a NO_LIMIT profile which I assign to all privileged users ( SYS, SYSTEM etc ) I manually change this on my outrage windows as Audit demands.
For additional information see
9.3.7 Refresh the Password File - Data Guard Concepts and Administration 11g Release 2 (11.2) E10700-02
I need to clarify my issue. Our Oracle 10g administrative passwords are never expired. The only passwords subject to our password expiration belong to traditional end users who have user accounts in our primary database. Our typical end user only logs into and queries the standby database, avoiding logins to the primary database until their password needs to be reset (every couple of months). The end users become irritated at the recursive login error which is received before any warning is received. Once their password is reset on the primary, the recursive login error goes away following application of the archived redo logs to the standby database. The end users would like the grace period to be applied equally to both primary and standby databases. Why can the end user login to the primary, but not the standby during the password expiration grace period?
Thanks for the information. Sorry for the delay.
The cause of this is you cannot have "failed_logon_attempts" profiles set on an opened Read-only Standby Database.
You need to change the Profile on the Standby to not use "Failed_logon_attempts" by setting them to unlimited on the Primary or disable using profiles on the Standby.
Example : ( change as needed if you have additional profiles ) ( Example on Primary )
This gets passed through the archive redo logs to the Standby.
alter profile default limit failed_login_attempts unlimited;
There's also a bug, 7581964 and its possible you might be hitting that.
Another thing that would cause this is a logon trigger
alter trigger on_logon disable;
Edited by: mseberg on Apr 20, 2012 5:36 PM
End users may receive:
ORA-00604: Error occurred at recursive SQL line 1
ORA-16000: Database open for read-only access
This can be expected behavior for a physical standby database opened in read-only mode when PASSWORD_GRACE_TIME parameter is set. End users may enter a grace period upon the first attempt to login to the standby database after their password has expired in the primary database. This event should be recorded in the database, but the read-only status of the standby database will prevent this update. A suggested workaround is to disable the setting in the primary database, but I do not recommend it.