1 2 Previous Next 21 Replies Latest reply: Feb 24, 2013 7:13 AM by 877440 RSS

    DG standby db recovery mode idle, and lag for days.

    877440
      We have a RAC 2 node primary and 2 node RAC standby qa environment in redhat linux.

      two nights ago, some maintenance caused primary down, and db failed over to standby. and now I found out by dgmgrl the following error:

      DGMGRL> show configuration

      Configuration - oraqa_dg

      Protection Mode: MaxPerformance
      Databases:
      orasqa - Primary database
      Error: ORA-16810: multiple errors or warnings detected for the database

      oraqa - (*) Physical standby database
      Warning: ORA-16829: fast-start failover configuration is lagging

      Fast-Start Failover: ENABLED

      Configuration Status:
      ERROR

      DGMGRL> failover to oraqa
      Performing failover NOW, please wait...
      Error: ORA-16600: not connected to target standby database for failover

      Failed.
      Unable to failover
      DGMGRL> switchover to oraqa
      Performing switchover NOW, please wait...
      Error: ORA-16810: multiple errors or warnings detected for the database

      Failed.
      Unable to switchover, primary database is still "orasqa"

      I started recover managed standby database using current logfile disconnect;

      and see one one node MRP process running, but other node not.

      and recover mode is idle ,and no media recovery based on alert logs.



      LOGS TIME
      ---------------- ------------------------
      Last applied : 21-FEB-13:01:16:34
      Last received : 21-FEB-13:01:16:34

      NAME VALUE UNIT
      -------------------------------- ---------------------------------------------------------------- ---------------------------------------------
      apply finish time day(2) to second(3) interval
      apply lag day(2) to second(0) interval
      estimated startup time 32 second
      transport lag day(2) to second(0) interval

      Time Computed: 02/22/2013 15:23:10

      How do I get the media recovery started?

      Another issue I found out through crsctl stat res, the db on standby shows intermedia status.
      ora.oraqa.db
      1 ONLINE INTERMEDIATE oracle02 Mounted (Closed)
      2 ONLINE INTERMEDIATE oracle03 Mounted (Closed)

      Please advice what I can do from this point.

      Thanks in advance.
        • 1. Re: DG standby db recovery mode idle, and lag for days.
          mseberg
          Hello;

          What is your setting for :

          FastStartFailoverLagLimit

          Can you run this on the standby ?

          select name,value from v$dataguard_stats where name like 'transport%';

          Best Regards

          mseberg
          • 2. Re: DG standby db recovery mode idle, and lag for days.
            CKPT
            We have a RAC 2 node primary and 2 node RAC standby qa environment in redhat linux.
            two nights ago, some maintenance caused primary down, and db failed over to standby. and now I found out by dgmgrl the following error:
            Here failover initiated and now standby(oraqa) become new primary.

            Have you configured flashback on primary database?
            SQL> select flashback_on from v$database;

            If you configured Flashback, Then reinstate the failed primary database as a standby database of the new primary database.
            Startup failed primary to MOUNT status and

            DGMGRL> REINSTATE DATABASE 'orasqa';
            DGMGRL> failover to oraqa
            Performing failover NOW, please wait...
            Error: ORA-16600: not connected to target standby database for failover
            Failed.
            Unable to failover
            When you try to perform failover, you have to check the database_role and resetlogs_change# from v$database (and) you must check broker log file for errors what happened exactly.
            DGMGRL> switchover to oraqa
            Performing switchover NOW, please wait...
            Error: ORA-16810: multiple errors or warnings detected for the database
            Failed.
            Unable to switchover, primary database is still "orasqa"
            To perform failover, They both were sync?
            they both are same resetlogs_change#? because FSFO initiated and there is no standby database to perform switchover.
            I started recover managed standby database using current logfile disconnect;
            and see one one node MRP process running, but other node not.
            I suggest you to gather below information from primary and standby.
            SQL> select db_unique_name,database_role,resetlogs_change# from v$database;

            From primary:
            http://www.oracle-ckpt.com/script-to-monitor-primary-and-standby-databases/
            • 3. Re: DG standby db recovery mode idle, and lag for days.
              877440
              Lag Limit: 30 seconds
              • 4. Re: DG standby db recovery mode idle, and lag for days.
                877440
                yes, both database flashback on.

                SQL> select flashback_on from v$database;

                FLASHBACK_ON
                ------------------
                YES
                • 5. Re: DG standby db recovery mode idle, and lag for days.
                  mseberg
                  If you think this is your only issue try setting the parameter to 0 and try to re-enable the fast start failover. I think that's infinate.

                  Or you can try setting it higher, say 40.

                  Would read CKPT's post as he is the best in the business.

                  Best Regards

                  mseberg
                  • 6. Re: DG standby db recovery mode idle, and lag for days.
                    877440
                    on now-primary:

                    DB_UNIQUE_NAME DATABASE_ROLE RESETLOGS_CHANGE#
                    ------------------------------ ---------------- -----------------
                    orasqa PRIMARY 35257865520

                    On now-standby:
                    SQL> select db_unique_name,database_role,resetlogs_change# from v$database;

                    DB_UNIQUE_NAME DATABASE_ROLE RESETLOGS_CHANGE#
                    ------------------------------ ---------------- -----------------
                    oraqa PHYSICAL STANDBY 34631450685
                    • 7. Re: DG standby db recovery mode idle, and lag for days.
                      CKPT
                      982335 wrote:
                      on now-primary:

                      DB_UNIQUE_NAME DATABASE_ROLE RESETLOGS_CHANGE#
                      ------------------------------ ---------------- -----------------
                      orasqa PRIMARY 35257865520

                      On now-standby:
                      SQL> select db_unique_name,database_role,resetlogs_change# from v$database;

                      DB_UNIQUE_NAME DATABASE_ROLE RESETLOGS_CHANGE#
                      ------------------------------ ---------------- -----------------
                      oraqa PHYSICAL STANDBY 34631450685
                      If you see here, As expected the RESETLOGS_CHANGE# been changed in primary and standby databases, So there will be no recovery will be performed.
                      Post

                      DGMGRL> SHOW DATABASE 'orasqa' StatusReport;
                      DGMGRL> show database 'oraqa';
                      • 8. Re: DG standby db recovery mode idle, and lag for days.
                        877440
                        DGMGRL> show database 'orasqa' statusreport;
                        STATUS REPORT
                        INSTANCE_NAME SEVERITY ERROR_TEXT
                        * ERROR ORA-16783: cannot resolve gap for database oraqa
                        orasqa2 ERROR ORA-16737: the redo transport service for standby database "oraqa" has an error
                        * WARNING ORA-16829: fast-start failover configuration is lagging
                        orasqa1 ERROR ORA-16737: the redo transport service for standby database "oraqa" has an error

                        DGMGRL> show database oraqa

                        Database - oraqa

                        Role: PHYSICAL STANDBY
                        Intended State: APPLY-ON
                        Transport Lag: (unknown)
                        Apply Lag: (unknown)
                        Real Time Query: OFF
                        Instance(s):
                        oraqa1 (apply instance)
                        oraqa2

                        Database Warning(s):
                        ORA-16829: fast-start failover configuration is lagging

                        Database Status:
                        WARNING
                        • 9. Re: DG standby db recovery mode idle, and lag for days.
                          CKPT
                          982335 wrote:
                          DGMGRL> show database 'orasqa' statusreport;
                          STATUS REPORT
                          INSTANCE_NAME SEVERITY ERROR_TEXT
                          * ERROR ORA-16783: cannot resolve gap for database oraqa
                          orasqa2 ERROR ORA-16737: the redo transport service for standby database "oraqa" has an error
                          * WARNING ORA-16829: fast-start failover configuration is lagging
                          orasqa1 ERROR ORA-16737: the redo transport service for standby database "oraqa" has an error

                          DGMGRL> show database oraqa

                          Database - oraqa

                          Role: PHYSICAL STANDBY
                          Intended State: APPLY-ON
                          Transport Lag: (unknown)
                          Apply Lag: (unknown)
                          Real Time Query: OFF
                          Instance(s):
                          oraqa1 (apply instance)
                          oraqa2

                          Database Warning(s):
                          ORA-16829: fast-start failover configuration is lagging

                          Database Status:
                          WARNING
                          Here is one link to resolve such above issue with reinstate stuff http://www.databasejournal.com/features/oracle/article.php/10893_3849106_2/Fast-Start-Failover-in-Oracle-11g-Data-Guard.htm
                          • 10. Re: DG standby db recovery mode idle, and lag for days.
                            877440
                            reinstate not work:

                            DGMGRL> reinstate database oraqa
                            Reinstating database "oraqa", please wait...
                            Error: ORA-16653: failed to reinstate database

                            Failed.
                            Reinstatement of database "oraqa" failed
                            • 11. Re: DG standby db recovery mode idle, and lag for days.
                              877440
                              on now-primary drc*.log:

                              Redo transport problem detected: redo transport for database oraqa has the following error:
                              ORA-03135: connection lost contact
                              02/22/2013 16:16:51
                              Redo transport problem detected: redo transport for database oraqa has the following error:
                              ORA-03135: connection lost contact
                              02/22/2013 16:17:02
                              Redo transport problem detected: redo transport for database oraqa has the following error:
                              ORA-03135: connection lost contact
                              02/22/2013 16:17:51
                              Redo transport problem detected: redo transport for database oraqa has the following error:
                              ORA-03135: connection lost contact
                              02/22/2013 16:29:51
                              Redo transport problem detected: redo transport for database oraqa has the following error:
                              ORA-03135: connection lost contact
                              02/22/2013 16:30:51

                              on now-standby:
                              ON Registering service oraqa_DGB with listener(s)
                              Broker Configuration: "oraqa_dg"
                              Protection Mode: Maximum Performance
                              Fast-Start Failover (FSFO): Enabled, flags=0x44007, version=5080
                              Primary Database: remesqa (0x02010000)
                              Standby Database: oraqa, Enabled Physical Standby (FSFO target) (0x01010000)
                              02/22/2013 14:51:46
                              oraqa version check successfully completed
                              oraqa has current configuration metadata,
                              completing bootstrap
                              Creating process RSM0
                              02/22/2013 15:11:19
                              Apply Instance for Database oraqa set to oraqa1
                              02/22/2013 15:11:31
                              Command EDIT DATABASE oraqa SET PROPERTY ActualApplyInstance = oraqa1 completed
                              • 12. Re: DG standby db recovery mode idle, and lag for days.
                                CKPT
                                Redo transport problem detected: redo transport for database oraqa has the following error:
                                ORA-03135: connection lost contact
                                It seems to be network issues and i cannot say exactly.
                                I will suggest to post all the Broker log files after issued "reinstate" command ?
                                also do refer alert log file for any kind of information.
                                • 13. Re: DG standby db recovery mode idle, and lag for days.
                                  877440
                                  Can I just recreate the standby to the old primary then switchover to primay?
                                  • 14. Re: DG standby db recovery mode idle, and lag for days.
                                    CKPT
                                    982335 wrote:
                                    Can I just recreate the standby to the old primary then switchover to primay?
                                    I recommend to fix the issue.
                                    So if it is not critical database, then of course you can play. Yes.
                                    1 2 Previous Next