1 2 3 4 5 Previous Next 63 Replies Latest reply: Jul 26, 2012 2:11 PM by mseberg Go to original post RSS
      • 15. Re: able to switchoover to stdy, but after that from stdy, cnt switchback
        849425
        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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    have good time, Thanks for your help.