4 Replies Latest reply: May 22, 2012 1:17 PM by 876213 RSS

    Recursive Login Error (ORA-604) during Password Grace Period

    876213
      When a password on the primary database is in its' expiration grace period, a subsequent login to the physical standby database generates a recursive read-only error (ORA-00604). The physical standby database is opened read-only and passwords cannot be changed. The login to the primary database does not encounter this login error, only a warning, during the same grace period. In contrast, the physical standby database login is unable to proceed during the grace period. Why is standby prohibiting logins during the primary grace period? What is the recommended workaround?
        • 1. Re: Recursive Login Error (ORA-604) during Password Grace Period
          mseberg
          Bob;

          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 ) :
          mv /u01/app/oracle/product/11.2.0/dbs/orapwPRIMARY /u01/app/oracle/product/11.2.0/dbs/orapwSTANDBY
          Then Startup Mount ( this will use the new password file - very important )

          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.

          Avoiding

          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

          Best Regards

          mseberg
          • 2. Re: Recursive Login Error (ORA-604) during Password Grace Period
            876213
            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?
            • 3. Re: Recursive Login Error (ORA-604) during Password Grace Period
              mseberg
              OK;

              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 )
              alter profile default limit failed_login_attempts unlimited; 
              This gets passed through the archive redo logs to the Standby.

              There's also a bug, 7581964 and its possible you might be hitting that.

              Best Regards

              mseberg

              h2. Later

              Another thing that would cause this is a logon trigger

              Solution

              alter trigger on_logon disable;

              Edited by: mseberg on Apr 20, 2012 5:36 PM
              • 4. Re: Recursive Login Error (ORA-604) during Password Grace Period
                876213
                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.