This discussion is archived
14 Replies Latest reply: Nov 17, 2012 9:19 AM by timscn RSS

How to Perform Failover When GAP on Standby

timscn Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    thanks
    but i not have archivelog:((( on primary too((
    from backup cannot restore(((
  • 12. Re: How to Perform Failover When GAP on Standby
    CKPT Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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..
  • 14. Re: How to Perform Failover When GAP on Standby
    timscn Newbie
    Currently Being Moderated
    thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points