7 Replies Latest reply: Jan 15, 2014 2:14 AM by MHAIDAR RSS

    Archived log files not registered in the Database

    MHAIDAR

      I have Widows Server 2008 R2

      I have Oracle 11g R2

      I configured primary and standby database in 2 physical servers , please find below the verification:

       

      I am using DG Broker

       

      Renetly I did failover from primary to standby database

      Then I did REINSTATE DATABASE to returen the old primary to standby mode

      Then I did Switchover again

       

      I have problem that archive logs not registered and not imeplemented.

       

      SQL> select max(sequence#) from v$archived_log; 

      MAX(SEQUENCE#)
      --------------
               16234

       

      I did alter system switch logfile then I ssue the following statment to check and I found same number in primary and stanbyd has not been changed


      SQL> select max(sequence#) from v$archived_log;

      MAX(SEQUENCE#)
      --------------
               16234



      Any body can help please?

       

      Regards


        • 1. Re: Archived log files not registered in the Database
          Anar Godjaev

          HI

           

          Can you please send me result in Primary database;

           

          SELECT status, error

          FROM V$ARCHIVE_DEST_STATUS

          WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE';

           

          and past result in secondary location:

           

          select name, value from v$dataguard_Stats;

           

          Thank you

           


          • 2. Re: Archived log files not registered in the Database
            MHAIDAR

            Hi

             

            Please find the result From Primary:

            SQL> SELECT status, error
              2  FROM V$ARCHIVE_DEST_STATUS
              3  WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE';

            STATUS    ERROR
            --------- -----------------------------------------------------------------
            VALID
            VALID

             

            From Secondary

             

            SQL> select name, value from v$dataguard_Stats;

            NAME                             VALUE
            -------------------------------- -------------------------------------------
            transport lag                    +00 00:00:00
            apply lag                        +00 00:00:00
            apply finish time                +00 00:00:00.000
            estimated startup time           22

            • 3. Re: Archived log files not registered in the Database
              Anar Godjaev

              HI,

               

              It is very good. There is no problem. Estimated time before log apply services will finish applying the redo data available on the standby database. "apply finish time                +00 00:00:00.000"

               

              Thank you

              • 4. Re: Archived log files not registered in the Database
                MHAIDAR

                But log files not implemented

                 

                I am doing alter system switch logfile to generate new archive file and still the sequence as it is???

                • 5. Re: Archived log files not registered in the Database
                  MHAIDAR

                  I can see archived log files generated in the disk but I can't see them in the database


                  • 6. Re: Archived log files not registered in the Database
                    Anar Godjaev

                    I can't understand . What is " you can see archived log files generated in the disk but I can't see them in the database"

                     

                    Please connect to Primary database:

                     

                    SQL> alter system switch logfile;

                    System altered.

                    SQL> /
                    /

                    System altered.

                    SQL>
                    System altered.

                     

                    and Run this query in Primary:

                     

                    SELECT DB_NAME,

                    HOSTNAME,

                    LOG_ARCHIVED,

                    LOG_APPLIED_02,

                    LOG_APPLIED_03,

                    APPLIED_TIME,

                    LOG_ARCHIVED - LOG_APPLIED_02 LOG_GAP_02,

                    LOG_ARCHIVED - LOG_APPLIED_03 LOG_GAP_03

                    FROM (SELECT NAME DB_NAME FROM V$DATABASE),

                    (SELECT UPPER(SUBSTR(HOST_NAME, 1, (DECODE(INSTR(HOST_NAME, '.'),0, LENGTH(HOST_NAME),(INSTR(HOST_NAME, '.') - 1))))) HOSTNAME FROM V$INSTANCE),

                    (SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM V$ARCHIVED_LOG WHERE DEST_ID = 1 AND ARCHIVED = 'YES'),

                    (SELECT MAX(SEQUENCE#) LOG_APPLIED_02 FROM V$ARCHIVED_LOG WHERE DEST_ID = 2 AND APPLIED = 'YES'),

                    (SELECT MAX(SEQUENCE#) LOG_APPLIED_03 FROM V$ARCHIVED_LOG WHERE DEST_ID = 3 AND APPLIED = 'YES'),

                    (SELECT TO_CHAR(MAX(COMPLETION_TIME), 'DD-MON/HH24:MI') APPLIED_TIME FROM V$ARCHIVED_LOG WHERE DEST_ID = 2 AND APPLIED = 'YES');

                     

                    Here you can see what is happening archivelog's

                     

                    Thank you

                    • 7. Re: Archived log files not registered in the Database
                      MHAIDAR

                      Thanks for reply

                       

                      What I mean after I do alter system switch log file, I can see the archived log files is generated in the physical Disk but when

                      select MAX(SEQUENCE#) FROM V$ARCHIVED_LOG;

                      the sequence number not changed it should increase by 1 when ever I do switch logfile.

                       

                      however I did as you asked please find the result below:

                       

                      SQL> alter system switch logfile;

                      System altered.

                      SQL> /

                      System altered.

                      SQL> /

                      System altered.

                      SQL> /

                      System altered.

                      SQL> SELECT DB_NAME,HOSTNAME,LOG_ARCHIVED,LOG_APPLIED_02,LOG_APPLIED_03,APPLIED_TIME,LOG_ARCHIVED - LOG_APPLIED_02 LOG_GAP_02,
                        2  LOG_ARCHIVED - LOG_APPLIED_03 LOG_GAP_03
                        3  FROM (SELECT NAME DB_NAME FROM V$DATABASE),
                        4  (SELECT UPPER(SUBSTR(HOST_NAME, 1, (DECODE(INSTR(HOST_NAME, '.'),0, LENGTH(HOST_NAME),(INSTR(HOST_NAME, '.') - 1))))) HOSTNAME FROM V$INSTANCE),
                        5  (SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM V$ARCHIVED_LOG WHERE DEST_ID = 1 AND ARCHIVED = 'YES'),
                        6  (SELECT MAX(SEQUENCE#) LOG_APPLIED_02 FROM V$ARCHIVED_LOG WHERE DEST_ID = 2 AND APPLIED = 'YES'),
                        7  (SELECT MAX(SEQUENCE#) LOG_APPLIED_03 FROM V$ARCHIVED_LOG WHERE DEST_ID = 3 AND APPLIED = 'YES'),
                        8  (SELECT TO_CHAR(MAX(COMPLETION_TIME), 'DD-MON/HH24:MI') APPLIED_TIME FROM V$ARCHIVED_LOG WHERE DEST_ID = 2 AND APPLIED = 'YES');

                       

                       

                       

                      DB_NAME HOSTNAME           LOG_ARCHIVED   LOG_APPLIED_02    LOG_APPLIED_03     APPLIED_TIME     LOG_GAP_02      LOG_GAP_03

                      ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

                      EPPROD  CORSKMBBOR01     16252                  16253                        (null)                      15-JAN/12:04                  -1                   (       null)