14 Replies Latest reply: Nov 17, 2012 11:19 AM by timscn RSS

    How to Perform Failover When GAP on Standby

    timscn
      hi ,
      i want to activate my standby in read/write
      but i can't..i don't have archivelogs and i can't restore standby from incremental backup



      ALTER DATABASE ACTIVATE STANDBY DATABASE
      ALTER DATABASE ACTIVATE [PHYSICAL] STANDBY DATABASE (work)
      Begin: Standby Redo Logfile archival
      End: Standby Redo Logfile archival
      Signalling error 1152 for datafile 1!
      Beginning standby crash recovery.
      Serial Media Recovery started
      Managed Standby Recovery starting Real Time Apply
      Media Recovery Waiting for thread 1 sequence 1174115
      Fetching gap sequence in thread 1, gap sequence 1174115-1174214
      Sat Nov 17 17:07:09 2012
      Fetching gap sequence in thread 1, gap sequence 1174115-1174214
      Sat Nov 17 17:07:19 2012
      Fetching gap sequence in thread 1, gap sequence 1174115-1174214
      Sat Nov 17 17:07:29 2012
      Fetching gap sequence in thread 1, gap sequence 1174115-1174214
      Sat Nov 17 17:07:39 2012
      Fetching gap sequence in thread 1, gap sequence 1174115-1174214
      Sat Nov 17 17:07:50 2012
      Fetching gap sequence in thread 1, gap sequence 1174115-1174214
      Sat Nov 17 17:08:00 2012
      Fetching gap sequence in thread 1, gap sequence 1174115-1174214
      Standby crash recovery need archive log for thread 1 sequence 1174115 to continue.
      Please verify that primary database is transporting redo logs to the standby database.
      Wait timeout: thread 1 sequence 1174115
      Standby crash recovery aborted due to error 16016.
      Errors in file /oracle/diag/rdbms/b2resnew/work/trace/work_ora_36176058.trc:
      ORA-16016: archived log for thread 1 sequence# 1174115 unavailable
      Recovery interrupted!
      Completed standby crash recovery.
      Signalling error 1152 for datafile 1!
      ORA-1152 signalled during: ALTER DATABASE ACTIVATE STANDBY DATABASE...
      Sat Nov 17 17:08:12 2012
      ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH
      Attempt to do a Terminal Recovery (work)
      Media Recovery Start: Managed Standby Recovery (work)
      started logmerger process
      Sat Nov 17 17:08:12 2012
      Managed Standby Recovery not using Real Time Apply
      Parallel Media Recovery started with 32 slaves
      Media Recovery Waiting for thread 1 sequence 1174115
      Fetching gap sequence in thread 1, gap sequence 1174115-1174214
      Fetching gap sequence in thread 1, gap sequence 1174115-1174214
      RECOVER...FINISH not allowed due to gap
      GAP - thread 1 sequence 1174115-1174214
      DBID 3179924020 branch 606674959
      Recovery interrupted!
      Media Recovery failed with error 16171
      Errors in file /oracle/diag/rdbms/b2resnew/work/trace/work_pr00_37421192.trc:
      ORA-00283: recovery session canceled due to errors
      ORA-16171: RECOVER...FINISH not allowed due to gap for thr 1, seq 1174115-1174214
      Slave exiting with ORA-283 exception
      Errors in file /oracle/diag/rdbms/b2resnew/work/trace/work_pr00_37421192.trc:
      ORA-00283: recovery session canceled due to errors
      ORA-16171: RECOVER...FINISH not allowed due to gap for thr 1, seq 1174115-1174214
      ORA-10877 signalled during: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH ...


      SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
      ALTER DATABASE ACTIVATE STANDBY DATABASE
      *
      ERROR at line 1:
      ORA-01152: file 1 was not restored from a sufficiently old backup
      ORA-01110: data file 1: '/oradata/ctrl/system01.dbf'


      SQL> RECOVER MANAGED STANDBY DATABASE FINISH
      ORA-10877: error signaled in parallel recovery slave


      SQL>

      how open my physical standby in rw?
      thanks
        • 1. Re: How to Perform Failover When GAP on Standby
          mseberg
          Hello;

          These things don't go together. But in theory you can just do ( ALTER DATABASE ACTIVATE STANDBY DATABASE; )

          Step 1

          Fix your gap.

          Step 2

          Test failover

          http://www.visi.com/~mseberg/Data_Guard_Failover_Test_using_SQL.pdf

          OR

          How to Perform Failover When GAP on Standby [ID 846087.1]     


          If you have problems with both systems you need to use RMAN to recover.


          Best Regards

          mseberg
          • 2. Re: How to Perform Failover When GAP on Standby
            teits
            timscn wrote:

            how open my physical standby in rw?
            thanks
            Hi,

            Use FAILOVER METHOD. Failover is use when the primary database is not available.
            after successfully failover operation, the standby will become primary DB, open in read/write mode.

            see example
            http://www.oracle-base.com/articles/11g/data-guard-setup-11gr2.php#failover

            for more details
            http://docs.oracle.com/cd/B28359_01/server.111/b28294/role_management.htm#i1026398

            HTH
            Tobi
            • 3. Re: How to Perform Failover When GAP on Standby
              timscn
              thanks for answer for me...i cannot restore from backup rman..not have free physical space on lun's

              can you help me to open my standby on rw in another way?, not rman(

              thanks
              • 4. Re: How to Perform Failover When GAP on Standby
                timscn
                can i open my standby to rw if i start my db with controlfile type=primary
                • 5. Re: How to Perform Failover When GAP on Standby
                  mseberg
                  Hello again;

                  On the standby :

                  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

                  ALTER DATABASE ACTIVATE STANDBY DATABASE;

                  Best Regards

                  mseberg
                  • 6. Re: How to Perform Failover When GAP on Standby
                    timscn
                    hi thanks for reply;)


                    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
                    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
                    *
                    ERROR at line 1:
                    ORA-16136: Managed Standby Recovery not active


                    SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
                    ALTER DATABASE ACTIVATE STANDBY DATABASE
                    *
                    ERROR at line 1:
                    ORA-01152: file 1 was not restored from a sufficiently old backup
                    ORA-01110: data file 1: '/oradata/ctrl/system01.dbf'


                    SQL>

                    alert:
                    Sat Nov 17 17:53:48 2012
                    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
                    ORA-16136 signalled during: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL...
                    ALTER DATABASE ACTIVATE STANDBY DATABASE
                    ALTER DATABASE ACTIVATE [PHYSICAL] STANDBY DATABASE (work)
                    Begin: Standby Redo Logfile archival
                    End: Standby Redo Logfile archival
                    Signalling error 1152 for datafile 1!
                    Beginning standby crash recovery.
                    Serial Media Recovery started
                    Managed Standby Recovery starting Real Time Apply
                    Warning: Datafile 87 (/oradata/data/b2_data75.dbf) is offline during full database recovery and will not be recovered
                    Media Recovery Waiting for thread 1 sequence 1174115
                    Fetching gap sequence in thread 1, gap sequence 1174115-1174214
                    Sat Nov 17 17:54:02 2012
                    Fetching gap sequence in thread 1, gap sequence 1174115-1174214
                    Sat Nov 17 17:54:12 2012
                    Fetching gap sequence in thread 1, gap sequence 1174115-1174214
                    Sat Nov 17 17:54:22 2012
                    Fetching gap sequence in thread 1, gap sequence 1174115-1174214
                    Sat Nov 17 17:54:33 2012
                    Fetching gap sequence in thread 1, gap sequence 1174115-1174214
                    Sat Nov 17 17:54:43 2012
                    Fetching gap sequence in thread 1, gap sequence 1174115-1174214
                    Sat Nov 17 17:54:53 2012
                    Fetching gap sequence in thread 1, gap sequence 1174115-1174214
                    Standby crash recovery need archive log for thread 1 sequence 1174115 to continue.
                    Please verify that primary database is transporting redo logs to the standby database.
                    Wait timeout: thread 1 sequence 1174115
                    Standby crash recovery aborted due to error 16016.
                    Errors in file /oracle/diag/rdbms/b2resnew/work/trace/work_ora_37617862.trc:
                    ORA-16016: archived log for thread 1 sequence# 1174115 unavailable
                    Recovery interrupted!
                    Completed standby crash recovery.
                    Signalling error 1152 for datafile 1!
                    ORA-1152 signalled during: ALTER DATABASE ACTIVATE STANDBY DATABASE...
                    • 7. Re: How to Perform Failover When GAP on Standby
                      timscn
                      i have this in v$datafile_header
                      CHECKPOINT_CHANGE# STATUS
                      ------------------ -------
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391048138102 ONLINE
                      391281413544 OFFLINE
                      391687728700 OFFLINE

                      88 rows selected.
                      • 8. Re: How to Perform Failover When GAP on Standby
                        CKPT
                        >
                        End: Standby Redo Logfile archival
                        Signalling error 1152 for datafile 1!
                        Beginning standby crash recovery.
                        Serial Media Recovery started
                        Managed Standby Recovery starting Real Time Apply
                        >

                        Do you have Broker enabled?
                        If yes Please disable it, and then retry.

                        Beside that do you have any archive log from 1174115, At least 1 archive log sequence?

                        Step 1:
                        SQL>recover managed standby database cancel;
                        - Provide the sequence whichever requesting, Either copying from the primary system or from backup.
                        - If you able to apply atleast one archive log, then you can perform cancel the recovery and then give "CANCEL"

                        Step 2:
                        SQL> alter database activate standby database;

                        Now post the status.
                        • 9. Re: How to Perform Failover When GAP on Standby
                          timscn
                          CKPT hi.
                          Do you have Broker enabled?
                          yes.now i disabled.

                          +1174115 I do not have+

                          Sat Nov 17 18:17:13 2012
                          Completed: Data Guard Broker shutdown
                          Sat Nov 17 18:17:14 2012
                          ALTER SYSTEM SET dg_broker_start=FALSE SCOPE=BOTH;
                          Sat Nov 17 18:17:27 2012
                          ALTER DATABASE RECOVER managed standby database cancel
                          ORA-16136 signalled during: ALTER DATABASE RECOVER managed standby database cancel ...
                          Sat Nov 17 18:17:45 2012
                          alter database activate standby database
                          ALTER DATABASE ACTIVATE [PHYSICAL] STANDBY DATABASE (work)
                          Begin: Standby Redo Logfile archival
                          End: Standby Redo Logfile archival
                          Signalling error 1152 for datafile 1!
                          Beginning standby crash recovery.
                          Serial Media Recovery started
                          Managed Standby Recovery starting Real Time Apply
                          Warning: Datafile 87 (/oradata/data/b2_data75.dbf) is offline during full database recovery and will not be recovered
                          Media Recovery Waiting for thread 1 sequence 1174115
                          Fetching gap sequence in thread 1, gap sequence 1174115-1174214
                          Sat Nov 17 18:17:55 2012
                          Fetching gap sequence in thread 1, gap sequence 1174115-1174214
                          Sat Nov 17 18:18:06 2012
                          Fetching gap sequence in thread 1, gap sequence 1174115-1174214
                          Sat Nov 17 18:18:16 2012
                          Fetching gap sequence in thread 1, gap sequence 1174115-1174214
                          Sat Nov 17 18:18:26 2012
                          Fetching gap sequence in thread 1, gap sequence 1174115-1174214
                          Sat Nov 17 18:18:36 2012
                          Fetching gap sequence in thread 1, gap sequence 1174115-1174214
                          Sat Nov 17 18:18:47 2012
                          Fetching gap sequence in thread 1, gap sequence 1174115-1174214
                          Standby crash recovery need archive log for thread 1 sequence 1174115 to continue.
                          Please verify that primary database is transporting redo logs to the standby database.
                          Wait timeout: thread 1 sequence 1174115
                          Standby crash recovery aborted due to error 16016.
                          Errors in file /oracle/diag/rdbms/b2resnew/work/trace/work_ora_37617862.trc:
                          ORA-16016: archived log for thread 1 sequence# 1174115 unavailable
                          Recovery interrupted!
                          Completed standby crash recovery.
                          Signalling error 1152 for datafile 1!
                          ORA-1152 signalled during: alter database activate standby database...



                          SQL> select TS#,STATUS,ENABLED,file# from v$datafile order by status;

                          TS# STATUS ENABLED FILE#
                          ---------- ------- ---------- ----------
                          6 ONLINE READ WRITE 66
                          6 ONLINE READ WRITE 67
                          6 ONLINE READ WRITE 68
                          6 ONLINE READ WRITE 69
                          6 ONLINE READ WRITE 70
                          6 ONLINE READ WRITE 71
                          6 ONLINE READ WRITE 72
                          6 ONLINE READ WRITE 73
                          6 ONLINE READ WRITE 74
                          6 ONLINE READ WRITE 75
                          6 ONLINE READ WRITE 76
                          6 ONLINE READ WRITE 77
                          6 ONLINE READ WRITE 78
                          6 ONLINE READ WRITE 79
                          6 ONLINE READ WRITE 80
                          6 ONLINE READ WRITE 81
                          6 ONLINE READ WRITE 82
                          6 ONLINE READ WRITE 83
                          6 ONLINE READ WRITE 84
                          6 ONLINE READ WRITE 85
                          6 ONLINE READ WRITE 86
                          1 ONLINE READ WRITE 2
                          3 ONLINE READ WRITE 3
                          4 ONLINE READ WRITE 4
                          5 ONLINE READ WRITE 5
                          6 ONLINE READ WRITE 6
                          7 ONLINE READ WRITE 7
                          8 ONLINE READ WRITE 8
                          9 ONLINE READ WRITE 9
                          6 ONLINE READ WRITE 10
                          6 ONLINE READ WRITE 11
                          1 ONLINE READ WRITE 12
                          6 ONLINE READ WRITE 13
                          6 ONLINE READ WRITE 14
                          6 ONLINE READ WRITE 15
                          1 ONLINE READ WRITE 16
                          6 ONLINE READ WRITE 17
                          6 ONLINE READ WRITE 18
                          6 ONLINE READ WRITE 19
                          6 ONLINE READ WRITE 20
                          6 ONLINE READ WRITE 21
                          6 ONLINE READ WRITE 22
                          6 ONLINE READ WRITE 23
                          6 ONLINE READ WRITE 24
                          6 ONLINE READ WRITE 25
                          7 ONLINE READ WRITE 26
                          6 ONLINE READ WRITE 27
                          10 ONLINE READ WRITE 28
                          6 ONLINE READ WRITE 29
                          6 ONLINE READ WRITE 30
                          6 ONLINE READ WRITE 31
                          6 ONLINE READ WRITE 32
                          6 ONLINE READ WRITE 33
                          6 ONLINE READ WRITE 34
                          6 ONLINE READ WRITE 35
                          6 ONLINE READ WRITE 36
                          6 ONLINE READ WRITE 37
                          6 ONLINE READ WRITE 38
                          6 ONLINE READ WRITE 39
                          6 ONLINE READ WRITE 40
                          6 ONLINE READ WRITE 41
                          6 ONLINE READ WRITE 42
                          6 ONLINE READ WRITE 43
                          6 ONLINE READ WRITE 44
                          6 ONLINE READ WRITE 45
                          6 ONLINE READ WRITE 46
                          6 ONLINE READ WRITE 47
                          6 ONLINE READ WRITE 48
                          6 ONLINE READ WRITE 49
                          6 ONLINE READ WRITE 50
                          6 ONLINE READ WRITE 51
                          6 ONLINE READ WRITE 52
                          6 ONLINE READ WRITE 53
                          6 ONLINE READ WRITE 54
                          6 ONLINE READ WRITE 55
                          6 ONLINE READ WRITE 56
                          6 ONLINE READ WRITE 57
                          6 ONLINE READ WRITE 58
                          6 ONLINE READ WRITE 59
                          6 ONLINE READ WRITE 60
                          6 ONLINE READ WRITE 61
                          6 ONLINE READ WRITE 62
                          6 ONLINE READ WRITE 63
                          6 ONLINE READ WRITE 64
                          6 ONLINE READ WRITE 65
                          6 RECOVER READ WRITE 87
                          6 RECOVER READ WRITE 88
                          0 SYSTEM READ WRITE 1

                          88 rows selected.

                          SQL>


                          :(((
                          how i can remove "RECOVER" in mount state ?
                          • 10. Re: How to Perform Failover When GAP on Standby
                            mseberg
                            Use SQLPlus AS SYSDBA to issue the RECOVER command :

                            RECOVER DATAFILE x;

                            Best Regards

                            mseberg
                            • 11. Re: How to Perform Failover When GAP on Standby
                              timscn
                              thanks
                              but i not have archivelog:((( on primary too((
                              from backup cannot restore(((
                              • 12. Re: How to Perform Failover When GAP on Standby
                                CKPT
                                have you tried fake recovery as below?
                                SQL> recover standby database;
                                ORA-00279: change 4810910 generated at 11/17/2012 22:14:13 needed for thread 1
                                ORA-00289: suggestion :
                                /u02/app/oracle/flash_recovery_area/standby/archivelog/2012_11_17/o1_mf_1_847_%u_.arc
                                ORA-00280: change 4810910 for thread 1 is in sequence #847
                                
                                Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
                                "cancel"
                                Media recovery cancelled.
                                SQL> 
                                If this haven't worked, Then I suggest you go with Incremental roll forward to perform recovery until some time, then do failover. But as of now there is inconsistency and it is not allowing you to open database by failover. check below link

                                http://www.oracle-ckpt.com/rman-incremental-backups-to-roll-forward-a-physical-standby-database-2/
                                • 13. Re: How to Perform Failover When GAP on Standby
                                  timscn
                                  thanks all..i open db in read write
                                  i change controlfilt to primary controlfile type and open db with resetlogs
                                  cREATE CONTROLFILE REUSE DATABASE "WORK" RESETLOGS FORCE LOGGING ARCHIVELOG

                                  +++select open_mode,database_role from v$database;+

                                  +++OPEN_MODE DATABASE_ROLE+

                                  READ WRITE           PRIMARY

                                  SQL> exit

                                  good luck all..