7 Replies Latest reply: Apr 18, 2014 4:23 AM by tvCa-Oracle RSS

    Archive log issues.

    1830597

      All of a sudden, we are experiencing 100x the number of archived logs switch

       

      Does anyone have ideas on how to identify what is causing so many archived logs?

       

      oracle: 11.2.0.1

      OS Windows

      2008 R2

      Thanks.

        • 1. Re: Archive log issues.
          sb92075

          1830597 wrote:

           

          All of a sudden, we are experiencing 100x the number of archived logs switch

           

          Does anyone have ideas on how to identify what is causing so many archived logs?

           

          oracle: 11.2.0.1

          OS Windows

          2008 R2

          Thanks.

           

          only DML results in REDO

           

          DBMS_LOGMNR can reveal who & what is changing

          • 2. Re: Archive log issues.
            ddf_dba_ifox

            Your log switch rate has increased by that same amount.  I suspect that the transactional activity has also increased dramatically for this database.  Another possibility, although remote, is that the redo logs were recreated at a much smaller size (again, I can't expect that this would have happened).  You could run this query and see how many log switches are occurring per day:

             

            set verify off echo off feedback off pages 999
            
            col hour form a15
            col day noprint
            
            break on day skip 1
            compute sum of log_switches on day
            
            alter session set nls_date_format='dd-MON-yy hh24:mi' ;
            
            Accept num_days Prompt 'How many days do you want to look at? '
            
            set feedback on
            
            SELECT TRUNC(first_time,'HH24') hour, COUNT(SEQUENCE#) log_switches, TRUNC(first_time,'DD') day
            FROM   sys.V_$LOG_HISTORY
            WHERE  TRUNC(first_time) >= TRUNC(SYSDATE)-&num_days+1
            GROUP BY TRUNC(first_time,'HH24'), TRUNC(first_time,'DD')
            order by 1
            /
            

             

            Or run this to see how many log switches per minute:

            set verify off echo off feedback off pages 999
            
            col hour form a15
            col day noprint
            
            break on day skip 1
            compute sum of log_switches on day
            
            alter session set nls_date_format='dd-MON-yy hh24:mi' ;
            
            Accept num_days Prompt 'How many days do you want to look at? '
            
            set feedback on
            
            SELECT TRUNC(first_time,'MI') minute, COUNT(SEQUENCE#) log_switches, TRUNC(first_time,'DD') day
            FROM   sys.gV_$LOG_HISTORY
            WHERE  TRUNC(first_time) >= TRUNC(SYSDATE)-&num_days+1
            GROUP BY TRUNC(first_time,'MI'), TRUNC(first_time,'DD')
            order by 1
            /
            

             

            You can then investigate why these log switches have increased considerably.

             

             

            David Fitzjarrell

            • 3. Re: Archive log issues.
              jgarry

              Other, more silly causes could include someone setting up an OS or Oracle job to give the switch logfile command far too often, or changing the archive_lag_target from, say, 3600 to 36.  How big are the archived logs?  Does anyone use dbconsole to change init parameters (it might have a notation on changes)?  What does the alert log say about it?

              • 4. Re: Archive log issues.
                gandolf999

                Are the archive logs smaller than they were before, if so jgarry might have a point.

                • 5. Re: Archive log issues.
                  846411

                  You will have to check the redo log file size it should not be very small.

                   

                  Are you performing any bulk data load?

                  • 6. Re: Archive log issues.
                    tvCa-Oracle

                    Can this issue be from somebody creating a backup/copy of a database table (ex. using : Create table as ...) ?

                    • 7. Re: Archive log issues.
                      tvCa-Oracle

                      The number of redo switches is irrelevant, you must check the volume of data that is archived through redos. The number may be information as a side note, the main information is the volume of data archived. If you do not have this query yet, I can provide. Let me know.