This discussion is archived
1 2 3 4 5 Previous Next 63 Replies Latest reply: Jul 26, 2012 12:07 PM by 849425 Go to original post RSS
  • 30. Re: able to switchoover to stdy, but after that from stdy, cnt switchback
    849425 Newbie
    Currently Being Moderated
    ok, I removed dg configuraiton and recreated one.

    Now on standby , some query results:
    SQL> select process,status,sequence# from v$managed_standby
    2 ;

    PROCESS STATUS SEQUENCE#
    --------- ------------ ----------
    ARCH CLOSING 1256
    ARCH CLOSING 2349
    ARCH CONNECTED 0
    ARCH CLOSING 2350
    MRP0 APPLYING_LOG 2352
    RFS IDLE 0
    RFS IDLE 0
    RFS IDLE 0

    however v$archived_log shows gap:

    +FRA/prds/archivelog/2012_07_22/thread_1_seq_2341.4310.789322633
    2341 YES 22-JUL-12

    +FRA/prds/archivelog/2012_07_22/thread_1_seq_2342.4313.789322633
    2342 YES 22-JUL-12


    STANDBY
    --------------------------------------------------------------------------------
    SEQUENCE# APPLIED COMPLETIO
    ---------- --------- ---------
    +FRA/prds/archivelog/2012_07_22/thread_1_seq_2343.4316.789322633
    2343 YES 22-JUL-12

    +FRA/prds/archivelog/2012_07_22/thread_1_seq_2344.4317.789322633
    2344 YES 22-JUL-12


    71 rows selected.


    alert log shows it is apply the most current archivelogs.
  • 31. Re: able to switchoover to stdy, but after that from stdy, cnt switchback
    CKPT Guru
    Currently Being Moderated
    Let Mseberg have dinner with his wife ;-) let me jump in for some time :)
    SQL Execution error=604, sql=[alter system set log_archive_dest_1='']. See error stack below.
    So broker is trying to reset with null value with log archive destination. what is the value you have mentioned in your PFILE/SPFILE?

    Another issue, v$archived_log is not updating but archives are applying without any issues, Have you try to bounce database once? You can take another chance to create new standby controlfile from primary & Start MRP... Lets see how it goes.
  • 32. Re: able to switchoover to stdy, but after that from stdy, cnt switchback
    849425 Newbie
    Currently Being Moderated
    standby alert log shows this at the end:

    Media Recovery Waiting for thread 1 sequence 2351 (in transit)
    Recovery of Online Redo Log: Thread 1 Group 7 Seq 2351 Reading mem 0
    Mem# 0: +DAT/prds/onlinelog/group_7.960.788688215
    Mem# 1: +FRA/prds/onlinelog/group_7.4164.789166309
    Sun Jul 22 17:14:23 2012
    Media Recovery Log +FRA/prds/archivelog/2012_07_22/thread_1_seq_2351.4328.789326063
    Media Recovery Waiting for thread 1 sequence 2352 (in transit)
    Recovery of Online Redo Log: Thread 1 Group 7 Seq 2352 Reading mem 0
    Mem# 0: +DAT/prds/onlinelog/group_7.960.788688215
    Mem# 1: +FRA/prds/onlinelog/group_7.4164.789166309


    is the (in transit) means it is not applied yet?
  • 33. Re: able to switchoover to stdy, but after that from stdy, cnt switchback
    CKPT Guru
    Currently Being Moderated
    846422 wrote:
    standby alert log shows this at the end:

    Media Recovery Waiting for thread 1 sequence 2351 (in transit)
    Recovery of Online Redo Log: Thread 1 Group 7 Seq 2351 Reading mem 0
    Mem# 0: +DAT/prds/onlinelog/group_7.960.788688215
    Mem# 1: +FRA/prds/onlinelog/group_7.4164.789166309
    Sun Jul 22 17:14:23 2012
    Media Recovery Log +FRA/prds/archivelog/2012_07_22/thread_1_seq_2351.4328.789326063
    Media Recovery Waiting for thread 1 sequence 2352 (in transit)
    Recovery of Online Redo Log: Thread 1 Group 7 Seq 2352 Reading mem 0
    Mem# 0: +DAT/prds/onlinelog/group_7.960.788688215
    Mem# 1: +FRA/prds/onlinelog/group_7.4164.789166309


    is the (in transit) means it is not applied yet?
    In transit means the redo is transporting to standby now (or) you can say its writing into SRL's.. I can say your standby is very much good except that output of views, Have a try with scenarios as mentioned in earlier post.
  • 34. Re: able to switchoover to stdy, but after that from stdy, cnt switchback
    849425 Newbie
    Currently Being Moderated
    I have stop the db and restart several times.

    I will stop one more time.

    Everytime restart, on standby side, the v$archived_log always lag 4 or 6 lags.

    I will update soon.
  • 35. Re: able to switchoover to stdy, but after that from stdy, cnt switchback
    CKPT Guru
    Currently Being Moderated
    846422 wrote:
    I have stop the db and restart several times.

    I will stop one more time.

    Everytime restart, on standby side, the v$archived_log always lag 4 or 6 lags.

    I will update soon.
    Waiting for you update ;-) if that not worked have a test to recreate new standby controlfile in primary & restore in standby database.
    i think Mseberg will respond you. Its time to sleep ;-)
  • 36. Re: able to switchoover to stdy, but after that from stdy, cnt switchback
    849425 Newbie
    Currently Being Moderated
    Restarted standby, here are results:

    SQL>
    select process,status,sequence# from v$managed_standbySQL> ;

    PROCESS STATUS SEQUENCE#
    --------- ------------ ----------
    ARCH CONNECTED 0
    ARCH CONNECTED 0
    ARCH CONNECTED 0
    ARCH CONNECTED 0
    RFS IDLE 0
    RFS IDLE 0
    MRP0 APPLYING_LOG 2353

    7 rows selected.

    SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

    THREAD# MAX(SEQUENCE#)
    ---------- --------------
    1 2352
    2 1258

    SQL> select ds.dest_id id
    2 , ad.status
    3 , ds.database_mode db_mode
    4 , ad.archiver type
    5 , ds.recovery_mode
    6 , ds.protection_mode
    7 , ds.standby_logfile_count "SRLs"
    8 , ds.standby_logfile_active active
    9 , ds.archived_seq#
    from v$archive_dest_status ds
    10 11 , v$archive_dest ad
    12 where ds.dest_id = ad.dest_id
    13 and ad.status != 'INACTIVE'
    14 order by
    ds.dest_id
    15 16 /

    ID STATUS DB_MODE TYPE RECOVERY_MODE
    ---------- --------- --------------- ---------- -----------------------
    PROTECTION_MODE SRLs ACTIVE ARCHIVED_SEQ#
    -------------------- ---------- ---------- -------------
    1 VALID OPEN_READ-ONLY ARCH MANAGED REAL TIME APPLY
    MAXIMUM PERFORMANCE 0 0 0

    2 VALID UNKNOWN LGWR IDLE
    MAXIMUM PERFORMANCE 0 0 0

    32 VALID UNKNOWN ARCH IDLE
    MAXIMUM PERFORMANCE 0 0 2352


    SQL> select open_mode, switchover_status from v$database;

    OPEN_MODE SWITCHOVER_STATUS
    -------------------- --------------------
    READ ONLY WITH APPLY NOT ALLOWED

    SQL>
  • 37. Re: able to switchoover to stdy, but after that from stdy, cnt switchback
    849425 Newbie
    Currently Being Moderated
    any help? I built qa environment with exactly same parameters basically. qa environment never had this kind of issues.

    why this is keep happening?
  • 38. Re: able to switchoover to stdy, but after that from stdy, cnt switchback
    mseberg Guru
    Currently Being Moderated
    Hello;

    Back. Just trying to read everything you and my friend CKPT posted while I was away. Really glad he jumped in.

    Have never seen this message before :

    "READ ONLY WITH APPLY NOT ALLOWED"

    Not sure what the next action should be.


    Best Regards

    mseberg

    Edited by: mseberg on Jul 22, 2012 3:00 PM
  • 39. Re: able to switchoover to stdy, but after that from stdy, cnt switchback
    849425 Newbie
    Currently Being Moderated
    the open_mode is read only with apply, the switchover_status is not allowed.

    I wonder why it is not allowed.
  • 40. Re: able to switchoover to stdy, but after that from stdy, cnt switchback
    849425 Newbie
    Currently Being Moderated
    I performed switchver to standby, worked as usual.

    switchover to primary failed again with those errors:

    Error 16146 for archive log file 1 to 'prds'
    Errors in file /u01/app/oracle/diag/rdbms/prd/PRD1/trace/EPRD1_nsa2_11750.trc:
    ORA-16146: control file enqueue unavailable
  • 41. Re: able to switchoover to stdy, but after that from stdy, cnt switchback
    849425 Newbie
    Currently Being Moderated
    Can you diagnose my parameters?
  • 42. Re: able to switchoover to stdy, but after that from stdy, cnt switchback
    mseberg Guru
    Currently Being Moderated
    "not allowed" almost always means " A Gap"

    But none of the normal queries pin point which sequence number.

    You posted something yesterday, I will review a second time.

    Based on that can you run these on both databases ?
    SELECT  NAME, SEQUENCE#, APPLIED, COMPLETION_TIME FROM V$ARCHIVED_LOG WHERE SEQUENCE# = 2314;
    SELECT  NAME, SEQUENCE#, APPLIED, COMPLETION_TIME FROM V$ARCHIVED_LOG WHERE SEQUENCE# = 2315;
    SELECT  NAME, SEQUENCE#, APPLIED, COMPLETION_TIME FROM V$ARCHIVED_LOG WHERE SEQUENCE# = 2325;
    We only care about them if APPLIED is NO

    Best Regards

    mseberg
  • 43. Re: able to switchoover to stdy, but after that from stdy, cnt switchback
    mseberg Guru
    Currently Being Moderated
    Can you diagnose my parameters?

    Yes. Post them
  • 44. Re: able to switchoover to stdy, but after that from stdy, cnt switchback
    849425 Newbie
    Currently Being Moderated
    Here is the results of your query:

    NAME SEQUENCE# APPLIED COMPLETIO
    -------------------------------------------------------------------------------- --------- --------- ---------
    +FRA/prds/archivelog/2012_07_22/thread_1_seq_2325.4277.789312301             2325 YES       22-JUL-12

    SQL> SELECT NAME, SEQUENCE#, APPLIED, COMPLETION_TIME FROM V$ARCHIVED_LOG WHERE SEQUENCE# = 2314;

    NAME SEQUENCE# APPLIED COMPLETIO
    -------------------------------------------------------------------------------- --------- --------- ---------
    prd 2314 YES 22-JUL-12
    +FRA/prds/archivelog/2012_07_22/thread_1_seq_2314.4256.789278835             2314 YES       22-JUL-12

    SQL> SELECT NAME, SEQUENCE#, APPLIED, COMPLETION_TIME FROM V$ARCHIVED_LOG WHERE SEQUENCE# = 2315;
    SELECT NAME, SEQUENCE#, APPLIED, COMPLETION_TIME FROM V$ARCHIVED_LOG WHERE SEQUENCE# = 2325;
    NAME SEQUENCE# APPLIED COMPLETIO
    -------------------------------------------------------------------------------- --------- --------- ---------
    +FRA/prds/archivelog/2012_07_22/thread_1_seq_2315.4258.789278839             2315 YES       22-JUL-12
    prd 2315 YES 22-JUL-12

    SQL>

Legend

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