14 Replies Latest reply: Apr 17, 2013 9:08 PM by Hemant K Chitale RSS

    DG Setup Perfect, Running Ok, Yet One Unique Issue, Need Ur Intervention!

    DBA2013
      Hi Experts,

      Version : 11.2.03
      Box : AIX 5l
      Active Passive RAC Node:
      Standby : StandAlone DR
      ==================
      I am stuck in some silly situation, where I setup my DG and everything went fine.
      My DG is in complete sync with Primary.
      The archives are generated and moved to Standby smothly.
      I can see no error whatsoever in my Alert log.
      All things are ok except One.

      I CAN SEE ONLY 0 ARCHIVE COUNT WHEN I CHECK : ARCHIVE LOG LIST.....I Tried hard...to see how come it shows zero, if at all archives are coming, and getting stored in DISK GROUP ( checked them physically there) and then getting applied successfully...Yet result Zero.

      Can some One give some Out of Box Idea...because Inbox Idea's of few experts have yield no result.


      Thanks,
        • 1. Re: DG Setup Perfect, Running Ok, Yet One Unique Issue, Need Ur Intervention!
          FreddieEssex
          Can you post the output of the following from sqlplus:
          SELECT sequence#, name, standby_dest
          FROM v$archived_log
          WHERE sequence# = (SELECT MAX(sequence#) FROM v$archived_log);
          
          archive log list
          • 2. Re: DG Setup Perfect, Running Ok, Yet One Unique Issue, Need Ur Intervention!
            Hemant K Chitale
            I CAN SEE ONLY 0 ARCHIVE COUNT WHEN I CHECK : ARCHIVE LOG LIST
            Can you clarify ? What command / SQL query did you issue ? At the Standby site or Primary site ? What was the output displayed ?


            Hemant K Chitale
            • 3. Re: DG Setup Perfect, Running Ok, Yet One Unique Issue, Need Ur Intervention!
              DBA2013
              SQL> SELECT sequence#, name, standby_dest
              FROM v$archived_log
              WHERE sequence# = (SELECT MAX(sequence#) FROM v$archived_log); 2 3

              SEQUENCE#
              ----------
              NAME
              --------------------------------------------------------------------------------
              STA
              ---
              175
              +FRA/col_dg/archivelog/2013_04_17/thread_2_seq_175.310.812977257
              NO


              SQL> archive log list
              Database log mode Archive Mode
              Automatic archival Enabled
              Archive destination USE_DB_RECOVERY_FILE_DEST
              Oldest online log sequence 0
              Next log sequence to archive 0
              Current log sequence 0
              • 4. Re: DG Setup Perfect, Running Ok, Yet One Unique Issue, Need Ur Intervention!
                DBA2013
                Hemant Sir,

                Both the Primary /standby are in complete sync..No issue whatsoever is seen.

                It is only archive count at DG side, wch shows zero..From primary end it shows exact number(current archive sequence)..

                See what i get from standby end..

                SQL> SELECT sequence#, name, standby_dest
                FROM v$archived_log
                WHERE sequence# = (SELECT MAX(sequence#) FROM v$archived_log); 2 3

                SEQUENCE#
                ----------
                NAME
                --------------------------------------------------------------------------------
                STA
                ---
                175
                +FRA/col_dg/archivelog/2013_04_17/thread_2_seq_175.310.812977257
                NO


                SQL> archive log list
                Database log mode Archive Mode
                Automatic archival Enabled
                Archive destination USE_DB_RECOVERY_FILE_DEST
                Oldest online log sequence 0
                Next log sequence to archive 0
                Current log sequence 0
                • 5. Re: DG Setup Perfect, Running Ok, Yet One Unique Issue, Need Ur Intervention!
                  DBA2013
                  Thread Last Sequence Received Last Sequence Applied Difference
                  ---------- ---------------------- --------------------- ----------
                  2 175 175 0

                  so no difrence at all...
                  ==========================================
                  EXAMPLE:
                  =======
                  Here is one more database on the same server which I created long back..It shows everything perfect
                  though have same configuration

                  NAME TYPE VALUE
                  ------------------------------------ ----------- ------------------------------
                  db_unique_name string ACS01_DG

                  SEQUENCE# NAME STANDBY_DEST
                  ---------- ---------------------------------------------------------------------- --------------------
                  539 +FRA/acs01_dg/archivelog/2013_04_17/thread_1_seq_539.311.812980839     NO

                  SQL> archive log list
                  Database log mode Archive Mode
                  Automatic archival Enabled
                  Archive destination USE_DB_RECOVERY_FILE_DEST
                  Oldest online log sequence 539
                  Next log sequence to archive 0
                  Current log sequence 540

                  Edited by: FS2013 on Apr 17, 2013 3:36 AM
                  • 6. Re: DG Setup Perfect, Running Ok, Yet One Unique Issue, Need Ur Intervention!
                    Mahir M. Quluzade
                    Hi, FS2013

                    You are says : Running Ok.

                    How to you sure Data Guard configuration is perfect and runing ok?
                    How to you check this?

                    Can you paste here :
                    select max(Sequence#) from v$archived_log; -- on primary 
                    
                    select max(Sequence#) from v$archived_log; -- on standby
                    select max(Sequence#) from v$archived_log where applied='YES'; -- on standby
                    
                    On primary 
                    
                    show parameter log_archive_config
                    show parameter log_archive_dest_2 (or witch is you  are using for service) 
                    show parameter log_archive_dest_state_2
                    
                    select *  from v$dataguard_status; -- on both side
                    Regards
                    Mahir M. Quluzade
                    • 7. Re: DG Setup Perfect, Running Ok, Yet One Unique Issue, Need Ur Intervention!
                      Mahir M. Quluzade
                      Then nothing is wrong (this sentence aremy friend MSeberg)

                      Please check : Next log sequence to archive is different
                      • 8. Re: DG Setup Perfect, Running Ok, Yet One Unique Issue, Need Ur Intervention!
                        DBA2013
                        Hi Sir,

                        For checking whether they are in sync or not, archives have reached and applied. I have already updated in my previous update sir..All archives have reached and Both primary and standby is in sync.

                        From Primary END:

                        SQL> show parameter log_archive_config

                        NAME TYPE VALUE
                        ------------------------------------ ----------- ------------------------------
                        log_archive_config string DG_CONFIG=(COL,COL_DG)
                        SQL> show parameter log_archive_dest_2

                        NAME TYPE VALUE
                        ------------------------------------ ----------- ------------------------------
                        log_archive_dest_2 string SERVICE=col_dg NOAFFIRM ASYNC
                        VALID_FOR=(ONLINE_LOGFILES,PRI
                        MARY_ROLE) DB_UNIQUE_NAME=col_
                        dg


                        SQL> show parameter log_archive_dest_state_2

                        NAME TYPE VALUE
                        ------------------------------------ ----------- ------------------------------
                        log_archive_dest_state_2 string ENABLE
                        • 9. Re: DG Setup Perfect, Running Ok, Yet One Unique Issue, Need Ur Intervention!
                          DBA2013
                          Sir you got me wrong...I gave this example of my another standby which I had created long back...

                          In this scenario...it comes only 0 in OLd/Next/Current Sequence.

                          Please have a look sir.
                          • 10. Re: DG Setup Perfect, Running Ok, Yet One Unique Issue, Need Ur Intervention!
                            Mahir M. Quluzade
                            Thanks your pastes.
                            Did you read my last reply.
                            Can you paste here result of on both side.
                            SQL> archive log list;
                            Mahir
                            • 11. Re: DG Setup Perfect, Running Ok, Yet One Unique Issue, Need Ur Intervention!
                              DBA2013
                              Sorry missed few info to update..Here we go..

                              From Primary:
                              ========

                              SQL> archive log list
                              Database log mode Archive Mode
                              Automatic archival Enabled
                              Archive destination USE_DB_RECOVERY_FILE_DEST
                              Oldest online log sequence 175
                              Next log sequence to archive 176
                              Current log sequence 176


                              Standby:
                              =====

                              SQL> archive log list
                              Database log mode Archive Mode
                              Automatic archival Enabled
                              Archive destination USE_DB_RECOVERY_FILE_DEST
                              Oldest online log sequence 0
                              Next log sequence to archive 0
                              Current log sequence 0

                              ===============

                              Select * from v$dataguard_standby...

                              From Primary:
                              =======
                              FACILITY SEVERITY DEST_ID MESSAGE_NUM ERROR_CODE CAL TIMESTAMP MESSAGE
                              ---------- ---------- ---------- ----------- ---------- --- ---------- ------------------------------------------------------------
                              ort Servic
                              es

                              Log Transp Control 0 11608 0 YES 17.04.2013 LNS: Completed archiving log 3 thread 2 sequence 175
                              ort Servic
                              es

                              Log Transp Control 0 11609 0 YES 17.04.2013 ARC2: Beginning to archive thread 2 sequence 175 (3164674-31
                              ort Servic 83007)
                              es


                              FACILITY SEVERITY DEST_ID MESSAGE_NUM ERROR_CODE CAL TIMESTAMP MESSAGE
                              ---------- ---------- ---------- ----------- ---------- --- ---------- ------------------------------------------------------------
                              Log Transp Control 0 11610 0 YES 17.04.2013 ARC2: Completed archiving thread 2 sequence 175 (3164674-318
                              ort Servic 3007)
                              es

                              Log Transp Warning 1 11611 0 NO 17.04.2013 LNS: Standby redo logfile selected for thread 2 sequence 176
                              ort Servic for destination LOG_ARCHIVE_DEST_2
                              es

                              Log Transp Control 0 11612 0 YES 17.04.2013 LNS: Beginning to archive log 4 thread 2 sequence 176
                              ort Servic
                              es
                              ------------------------
                              From Standby:
                              ========
                              FACILITY SEVERITY DEST_ID MESSAGE_NUM ERROR_CODE CAL TIMESTAMP MESSAGE
                              -------------------- ------------- ---------- ----------- ---------- --- ---------- ------------------------------------------------------------

                              Remote File Server Informational ########## 152 0 NO 17.04.2013 Primary database is in MAXIMUM PERFORMANCE mode
                              Remote File Server Informational ########## 153 0 NO 17.04.2013 Re-archiving standby log 6 thread 2 sequence 175
                              Remote File Server Informational ########## 154 0 NO 17.04.2013 RFS[19]: Assigned to RFS process 19988528
                              Log Transport Servic Control ########## 155 0 YES 17.04.2013 ARC2: Completed archiving thread 2 sequence 175 (0-0)
                              es

                              Log Apply Services Informational ########## 156 0 NO 17.04.2013 Media Recovery Log +FRA/col_dg/archivelog/2013_04_17/thread_
                              2_seq_175.310.812977257

                              Log Apply Services Warning ########## 157 0 NO 17.04.2013 Media Recovery Waiting for thread 2 sequence 176 (in transit

                              FACILITY SEVERITY DEST_ID MESSAGE_NUM ERROR_CODE CAL TIMESTAMP MESSAGE
                              -------------------- ------------- ---------- ----------- ---------- --- ---------- ------------------------------------------------------------
                              )

                              Log Transport Servic Control ########## 158 0 YES 17.04.2013 ARC5: Beginning to archive thread 2 sequence 176 (3183007-32
                              es 00865)

                              Remote File Server Informational ########## 159 0 NO 17.04.2013 Primary database is in MAXIMUM PERFORMANCE mode
                              Remote File Server Informational ########## 160 0 NO 17.04.2013 Re-archiving standby log 5 thread 2 sequence 176
                              Log Transport Servic Control ########## 161 0 YES 17.04.2013 ARC5: Completed archiving thread 2 sequence 176 (0-0)

                              Edited by: FS2013 on Apr 17, 2013 4:11 AM
                              • 12. Re: DG Setup Perfect, Running Ok, Yet One Unique Issue, Need Ur Intervention!
                                Mahir M. Quluzade
                                You primary is RAC database.

                                Did you sure thead 1 is running ?
                                • 13. Re: DG Setup Perfect, Running Ok, Yet One Unique Issue, Need Ur Intervention!
                                  DBA2013
                                  It is not Active-Active RAC, It is Active-Passive RAC with Standalone DG
                                  ==========================================
                                  Is it necessary that only If thread1 is used to move archives then only we see archive log list.
                                  • 14. Re: DG Setup Perfect, Running Ok, Yet One Unique Issue, Need Ur Intervention!
                                    Hemant K Chitale
                                    I believe that ARCHIVE LOG LIST at the Standby would show zero. I don't have a Standby to verify this.


                                    Hemant K Chitale