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
  • 15. Re: able to switchoover to stdy, but after that from stdy, cnt switchback
    849425 Newbie
    Currently Being Moderated
    I recreated configuraiton using standby as primary. and created configuration.
  • 16. Re: able to switchoover to stdy, but after that from stdy, cnt switchback
    849425 Newbie
    Currently Being Moderated
    Here is the query results:

    From Primary:

    +FRA/prd/ar       2300 YES        21-JUL-12
    chivelog/2012_07_21/
    thread_1_seq_2300.44
    22.789256427

    STANDBY SEQUENCE# APPLIED COMPLETIO
    -------------------- ---------- ---------- ---------

    +FRA/prd/ar       2301 YES        21-JUL-12
    chivelog/2012_07_21/
    thread_1_seq_2301.44
    27.789256427

    +FRA/prd/ar       2310 YES        22-JUL-12
    chivelog/2012_07_22/
    thread_1_seq_2310.44
    44.789278779


    STANDBY SEQUENCE# APPLIED COMPLETIO
    -------------------- ---------- ---------- ---------
    +FRA/prd/ar       2311 YES        22-JUL-12
    chivelog/2012_07_22/
    thread_1_seq_2311.44
    45.789278779

    prds 2316 YES 22-JUL-12
    prds 2317 YES 22-JUL-12
    prds 2318 YES 22-JUL-12
    prds 2319 YES 22-JUL-12
    prds 2320 YES 22-JUL-12
    prds 2321 YES 22-JUL-12

    STANDBY SEQUENCE# APPLIED COMPLETIO
    -------------------- ---------- ---------- ---------
    prds 2322 YES 22-JUL-12
    prds 2323 YES 22-JUL-12
    prds 2324 YES 22-JUL-12
    prds 2325 NO 22-JUL-12

    48 rows selected.

    SQL> SELECT
    MAX(SEQUENCE#)
    FROM
    V$ARCHIVED_LOG
    WHERE
    NEXT_TIME > SYSDATE -1; 2 3 4 5 6

    MAX(SEQUENCE#)
    --------------
    2325



    from Standby:
    STANDBY SEQUENCE# APPLIED COMPLETIO
    -------------------- ---------- --------- ---------
    PRD 2299 YES 21-JUL-12
    PRD 2300 YES 21-JUL-12
    PRD 2301 YES 21-JUL-12
    PRD 2302 YES 21-JUL-12
    PRD 2303 YES 21-JUL-12
    prd 2304 YES 22-JUL-12
    prd 2305 YES 22-JUL-12
    prd 2306 YES 22-JUL-12
    prd 2307 YES 22-JUL-12
    prd 2308 YES 22-JUL-12
    prd 2309 YES 22-JUL-12

    STANDBY SEQUENCE# APPLIED COMPLETIO
    -------------------- ---------- --------- ---------
    prd 2310 YES 22-JUL-12
    prd 2311 YES 22-JUL-12
    prd 2312 YES 22-JUL-12
    prd 2313 YES 22-JUL-12
    prd 2314 NO 22-JUL-12
    prd 2315 NO 22-JUL-12
    +FRA/prds/a       2316 YES       22-JUL-12
    rchivelog/2012_07_22
    /thread_1_seq_2316.4
    264.789310781


    STANDBY SEQUENCE# APPLIED COMPLETIO
    -------------------- ---------- --------- ---------
    +FRA/prds/a       2317 YES       22-JUL-12
    rchivelog/2012_07_22
    /thread_1_seq_2317.4
    265.789310781

    +FRA/prds/a       2318 YES       22-JUL-12
    rchivelog/2012_07_22
    /thread_1_seq_2318.4
    266.789310781

    +FRA/prds/a       2319 YES       22-JUL-12

    STANDBY SEQUENCE# APPLIED COMPLETIO
    -------------------- ---------- --------- ---------
    rchivelog/2012_07_22
    /thread_1_seq_2319.4
    269.789310781


    44 rows selected.

    SQL> SELECT
    MAX(SEQUENCE#)
    FROM
    V$ARCHIVED_LOG
    WHERE
    NEXT_TIME > SYSDATE -1; 2 3 4 5 6

    MAX(SEQUENCE#)
    --------------
    2325

    SQL> select * from v$archive_gap;

    no rows selected



    I found out two weird thing, first is no gap from v$archive_gap.
    2nd, why sometimes it shows a whole path of archive log, sometimes just the name of db?

    THanks, Please help.
  • 17. Re: able to switchoover to stdy, but after that from stdy, cnt switchback
    849425 Newbie
    Currently Being Moderated
    Standby alert log shows the most current archivelogs are applied. and I did see in asmcmd, the archivelog shipped to standby side.

    Sun Jul 22 13:00:29 2012
    Media Recovery Log +FRA/prds/archivelog/2012_07_22/thread_2_seq_1230.4271.789310787
    Media Recovery Log +FRA/prds/archivelog/2012_07_22/thread_1_seq_2321.4270.789310785
    Media Recovery Log +FRA/prds/archivelog/2012_07_22/thread_1_seq_2322.4272.789310787
    Media Recovery Log +FRA/prds/archivelog/2012_07_22/thread_1_seq_2323.4273.789310795
    Media Recovery Log +FRA/prds/archivelog/2012_07_22/thread_2_seq_1231.4274.789310799
    Media Recovery Log +FRA/prds/archivelog/2012_07_22/thread_1_seq_2324.4275.789310819
    Media Recovery Log +FRA/prds/archivelog/2012_07_22/thread_2_seq_1232.4276.789310819
    Media Recovery Waiting for thread 1 sequence 2325 (in transit)
    Recovery of Online Redo Log: Thread 1 Group 7 Seq 2325 Reading mem 0
    Mem# 0: +DAT/prds/onlinelog/group_7.960.788688215
    Mem# 1: +FRA/prds/onlinelog/group_7.4164.789166309
    Media Recovery Waiting for thread 2 sequence 1233 (in transit)
    Recovery of Online Redo Log: Thread 2 Group 11 Seq 1233 Reading mem 0
    Mem# 0: +DAT/prds/onlinelog/group_11.964.788688217
    Mem# 1: +FRA/prds/onlinelog/group_11.4168.789166355
    Sun Jul 22 13:25:01 2012
    Archived Log entry 252 added for thread 1 sequence 2325 ID 0xf9fbd28 dest 1:
    Sun Jul 22 13:25:01 2012
    RFS[2]: Selected log 7 for thread 1 sequence 2326 dbid 258815187 branch 785963734
    Sun Jul 22 13:25:02 2012
    Media Recovery Log +FRA/prds/archivelog/2012_07_22/thread_1_seq_2325.4277.789312301
    Media Recovery Waiting for thread 1 sequence 2326 (in transit)
    Recovery of Online Redo Log: Thread 1 Group 7 Seq 2326 Reading mem 0
    Mem# 0: +DAT/prds/onlinelog/group_7.960.788688215
    Mem# 1: +FRA/prds/onlinelog/group_7.4164.789166309


    However v$archived_log shows last archived log is 2219.
  • 18. Re: able to switchoover to stdy, but after that from stdy, cnt switchback
    mseberg Guru
    Currently Being Moderated
    Will help as much as possible with the limits of the demands my family puts on me each weekend.


    v$archive_gap - I don't trust it.

    Would check for gap using :

    http://www.visi.com/~mseberg/monitor_data_guard_transport.html


    From Before

    log_archive_config
    log_archive_dest_1
    log_archive_dest_2
    log_archive_dest_state_1
    log_archive_dest_state_2 ( Should be DEFER on database in Standby mode )

    They look OK except for the DEFER note



    Would consider removing Broker

    How to Safely Remove a Data Guard Broker Configuration [ID 261336.1]


    Give the mass changes made to DG setup its hard to know where we are and this could clear that up.

    So I would remove broker then run "Monitor Data Guard Transport" and post the results

    Best Regards

    mseberg
  • 19. Re: able to switchoover to stdy, but after that from stdy, cnt switchback
    849425 Newbie
    Currently Being Moderated
    hi, here is my results from your query:

    DB_NAME HOSTNAME LOG_ARCHIVED LOG_APPLIED APPLIED_TIME LOG_GAP
    ---------- -------------- ------------ ----------- -------------- -------
    PRD CHI17 2325 2319 22-JUL/12:59 6


    However I saw on standby alert log, all those logs from 2319-2325 are applied:
    Media Recovery Log +FRA/prds/archivelog/2012_07_22/thread_1_seq_2319.4269.789310781
    Media Recovery Log +FRA/prds/archivelog/2012_07_22/thread_1_seq_2320.4261.789310779
    Media Recovery Log +FRA/prds/archivelog/2012_07_22/thread_2_seq_1228.4260.789310779
    Media Recovery Log +FRA/prds/archivelog/2012_07_22/thread_2_seq_1229.4259.789310779
    Sun Jul 22 13:00:29 2012
    Media Recovery Log +FRA/prds/archivelog/2012_07_22/thread_2_seq_1230.4271.789310787
    Media Recovery Log +FRA/prds/archivelog/2012_07_22/thread_1_seq_2321.4270.789310785
    Media Recovery Log +FRA/prds/archivelog/2012_07_22/thread_1_seq_2322.4272.789310787
    Media Recovery Log +FRA/prds/archivelog/2012_07_22/thread_1_seq_2323.4273.789310795
    Media Recovery Log +FRA/prds/archivelog/2012_07_22/thread_2_seq_1231.4274.789310799
    Media Recovery Log +FRA/prds/archivelog/2012_07_22/thread_1_seq_2324.4275.789310819
    Media Recovery Log +FRA/prds/archivelog/2012_07_22/thread_2_seq_1232.4276.789310819
    Media Recovery Waiting for thread 1 sequence 2325 (in transit)
    Recovery of Online Redo Log: Thread 1 Group 7 Seq 2325 Reading mem 0
    Mem# 0: +DAT/prds/onlinelog/group_7.960.788688215
    Mem# 1: +FRA/prds/onlinelog/group_7.4164.789166309
    Media Recovery Waiting for thread 2 sequence 1233 (in transit)
    Recovery of Online Redo Log: Thread 2 Group 11 Seq 1233 Reading mem 0
    Mem# 0: +DAT/prds/onlinelog/group_11.964.788688217
    Mem# 1: +FRA/prds/onlinelog/group_11.4168.789166355
    Sun Jul 22 13:25:01 2012
    Archived Log entry 252 added for thread 1 sequence 2325 ID 0xf9fbd28 dest 1:
    Sun Jul 22 13:25:01 2012
    RFS[2]: Selected log 7 for thread 1 sequence 2326 dbid 258815187 branch 785963734
    Sun Jul 22 13:25:02 2012
    Media Recovery Log +FRA/prds/archivelog/2012_07_22/thread_1_seq_2325.4277.789312301
    Media Recovery Waiting for thread 1 sequence 2326 (in transit)
    Recovery of Online Redo Log: Thread 1 Group 7 Seq 2326 Reading mem 0
  • 20. Re: able to switchoover to stdy, but after that from stdy, cnt switchback
    mseberg Guru
    Currently Being Moderated
    Wow!

    Very odd. So you are using DEST_ID 1 and 2 right?

    Both of these things cannot be true. Seems like the Primary is talking to to the Standby, but the Standby is not talking back to the Primary.

    Would like to connect using SQLPLUS from both sides so

    SQL> connect sys/<password>@prds as sysdba

    and then


    SQL> connect sys/<password>@prd as sysdba


    Do these both work?


    Best Regards

    mseberg
  • 21. Re: able to switchoover to stdy, but after that from stdy, cnt switchback
    849425 Newbie
    Currently Being Moderated
    from primary:

    sqlplus sy s@prds as sysdba

    SQL*Plus: Release 11.2.0.3.0 Production on Sun Jul 22 14:39:05 2012

    Copyright (c) 1982, 2011, Oracle. All rights reserved.

    Enter password:

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
    Data Mining and Real Application Testing options

    SQL> select instance_name from v$instance;

    INSTANCE_NAME
    ----------------
    PRDS2

    From standby:
    sqlplus sys@prd as sysdba

    SQL*Plus: Release 11.2.0.3.0 Production on Sun Jul 22 14:38:26 2012

    Copyright (c) 1982, 2011, Oracle. All rights reserved.

    Enter password:

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
    Data Mining and Real Application Testing options

    SQL> select instance_name from v$instance;

    INSTANCE_NAME
    ----------------
    PRD1


    Can login on both sides
  • 22. Re: able to switchoover to stdy, but after that from stdy, cnt switchback
    mseberg Guru
    Currently Being Moderated
    Excellent!

    From the Primary

    select severity,message,timestamp,error_code from v$dataguard_status where dest_id=2;

    And post the results please. We need more clues

    Use below link and post results from primary and standby databases using coded format.

    By that I maen enclose it between
     
    ( only chnage CODE to lower case )


    http://www.oracle-ckpt.com/dataguard_troubleshoot_snapper/

    Best Regards

    mseberg

    Edited by: mseberg on Jul 22, 2012 10:15 AM
  • 23. Re: able to switchoover to stdy, but after that from stdy, cnt switchback
    849425 Newbie
    Currently Being Moderated
    SQL> select severity,message,timestamp,error_code from v$dataguard_status where dest_id=2;

    no rows selected


    I did a switchover to standby just now, and in dr log file,
    there are such errors:
    07/22/2012 14:45:03
    SQL Execution error=604, sql=[alter system set log_archive_dest_1='']. See error stack below.
    ORA-00604: error occurred at recursive SQL level 1
    ORA-02097: parameter cannot be modified because specified value is invalid
    ORA-16028: new LOG_ARCHIVE_DEST_1 causes less destinations than LOG_ARCHIVE_MIN_SUCCEED_DEST requires
    07/22/2012 14:45:19
    Notifying Oracle Clusterware to buildup standby database after SWITCHOVER
    Data Guard notifying Oracle Clusterware to start services and other instances change
  • 24. Re: able to switchoover to stdy, but after that from stdy, cnt switchback
    mseberg Guru
    Currently Being Moderated
    So we still have Broker right ?

    This note should apply :

    Broker overrides local LOG_ARCHIVE_DEST_n destination on Bystander Standby [ID 1364467.1]     

    Best Regards

    mseberg
  • 25. Re: able to switchoover to stdy, but after that from stdy, cnt switchback
    849425 Newbie
    Currently Being Moderated
    I have removed the configuration, recreated twice.

    Why this keep happening?

    There must be some wrong parameters somewhere.
  • 26. Re: able to switchoover to stdy, but after that from stdy, cnt switchback
    mseberg Guru
    Currently Being Moderated
    Agreed.

    I would remove until this is resolved to keep it simple.

    If you have it removed create new Pfile from spfile and post them. I will be happy to review.

    ( My wife will have plans for me soon ! )

    Best Regards

    mseberg

    Edited by: mseberg on Jul 22, 2012 10:51 AM
  • 27. Re: able to switchoover to stdy, but after that from stdy, cnt switchback
    849425 Newbie
    Currently Being Moderated
    ok, I am going to remove it and let you know.
  • 28. Re: able to switchoover to stdy, but after that from stdy, cnt switchback
    mseberg Guru
    Currently Being Moderated
    Understood.

    Will check back ASAP. ( Lunch with my Wife )

    Best Regards

    mseberg
  • 29. Re: able to switchoover to stdy, but after that from stdy, cnt switchback
    849425 Newbie
    Currently Being Moderated
    have good time, Thanks for your help.

Legend

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