1 2 3 4 5 Previous Next 63 Replies Latest reply: Jul 26, 2012 2:07 PM by 849425 Go to original post RSS
      • 30. Re: able to switchoover to stdy, but after that from stdy, cnt switchback
        849425
        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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              Can you diagnose my parameters?
                              • 42. Re: able to switchoover to stdy, but after that from stdy, cnt switchback
                                mseberg
                                "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
                                  Can you diagnose my parameters?

                                  Yes. Post them
                                  • 44. Re: able to switchoover to stdy, but after that from stdy, cnt switchback
                                    849425
                                    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>