4 Replies Latest reply: Feb 10, 2013 8:48 PM by Dropbear67 RSS

    Reduce Archived Log Generation

    Arun Natarajan-OC
      Hi Friends,

      Hi Friends,

      we are using 11g R2 Database on windows. The Database size is 100GB.

      The size of the database grows about 1(or)2 Gb per day but the Archived logs generation is around 80 GB every day.

      How can I reduce the size of archived Logs generation which is highly contrast against my database growth.

      Is there any best practices (or) it is based on the Application code?

      The Data Mining on Archived log shows Delete,Insert and Update.

      Please let me know any suggestions.

      Regards,
      DB
        • 1. Re: Reduce Archived Log Generation
          Osama_Mustafa
          "In fact the size of your archived logs is generally fixed by the size of your online redo logs. So if you reduce the size of your online redo logs you'll get smaller archived logs."

          Check this thread
          Reduce the size of Archive Logs
          • 2. Re: Reduce Archived Log Generation
            Bjoern Rost
            Hi,

            I am afraid there is not much you can do. It is not uncommon to generate that much archive log, and since most redo/archive is generated for update and delete operations, it does not have to do with your data growth. Here are a few things you could look at to minimize redo generation. But most (if not all of them) are very hard to implement from a dba standpoint since it is usually the application that is responsible for DML and redo generation

            - avoid rollbacks. they generate redo but don't change data in the end
            - for bulk inserts, consider direct path inserts with /*+ APPEND */ and NOLOGGING on the table - http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5280714813869
            - instead of deleting all data from a table, truncate may be a better option
            - CREATE TABLE AS ... can also be used with NOLOGGING
            - are you rebuilding your objects (tables, indexes, materialized views) a lot? If yes, try to minimize that since it will generate large amounts of redo

            bjoern
            • 3. Re: Reduce Archived Log Generation
              EdStevens
              Osama_mustafa wrote:
              "In fact the size of your archived logs is generally fixed by the size of your online redo logs. So if you reduce the size of your online redo logs you'll get smaller archived logs."

              Check this thread
              Reduce the size of Archive Logs
              But more of them. The total volume of redo is a function of the amount of DML being processed, NOT the size of the online redo logs. The only thing resizing his online redo logs will accomplish is change the frequency of log switches and and the size of the individual archivelogs. So now he gets smaller, but more, archivelogs. What is smaller, a 12-inch pizza cut into 6 pieces or a 12-inch pizza cut into 8 pieces?

              Yes, the OP asked about reducing the size of his archivelogs, but that question was either poorly stated, or from a position of ignorance on the part of the OP. Yes, you answered the exact question he asked, but I'm pretty certain that isn't his real problem.
              • 4. Re: Reduce Archived Log Generation
                Dropbear67
                Bjoern provided a very good answer, but I'll just add generically that the amount of redo being generated is very roughly proportional to the amount of CHANGE happening in the database.

                You can use the tricks Bjoern described to try and generate less logging of the changes being made, but at the end of the day, the amount of change and the size of the database are not always closely related.

                You can have a very big database where nothing much changes over time (small redo generation) or a smallish database where a great deal changes over time (large redo generation).