This discussion is archived
6 Replies Latest reply: Jun 8, 2011 11:40 AM by 843389 RSS

How not to generate archive logs for database jobs

TamasSzecsy Newbie
Currently Being Moderated
Hi,

I have a 10g R2 EE RDBMS with archive log 'ON'. In the database I have a job that runs every two hours and executes a procedure. This procedure makes some heavy processing (lots of select statements) and during this process it refreshes the content of 30 or so reporting tables.

My problem is that during this process 50 GB (!) of archive log is generated, that is double the size of the database's actual valuable data content size.

On the other hand I would happily switch redo log generation off for this process. These reporting tables are not valuable at all in the sense that should the database instance crash, I could recreate these tables and by executing the named process I could reproduce this information any time.

Is it possible to force Oracle not to generate redo log for this job?

Regards, Tamas
  • 1. Re: How not to generate archive logs for database jobs
    gokhanatil Oracle ACE
    Currently Being Moderated
    Hello Tamas,

    You can't just turn off logging for a process but you can reduce log generation by using "nologging" mode on some operations.

    Please read:
    MOS [ID 188691.1] How to Avoid Generation of Redolog Entries
    MOS [ID 290161.1] The Gains and Pains of Nologging Operations
    http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#sthref2199

    Regards

    Gokhan Atil
  • 2. Re: How not to generate archive logs for database jobs
    TamasSzecsy Newbie
    Currently Being Moderated
    Hello Gokhan,

    It does not help me, but at least I know it is 'normal'. Thank you.

    Regards, Tamas
  • 3. Re: How not to generate archive logs for database jobs
    John Spencer Oracle ACE
    Currently Being Moderated
    Based on your description, it sounds like you may bedoing lots of row by row processing, possibly with commits scattered around during the processing of each of the reporting tables. Something like:
    FOR r in cursor LOOP
       UPDATE table 
       SET column = cursor.column
       WHERE other_column = cursor.other_column;
       rows_update := rows_update + 1;
       IF rows_update > some_value THEN
          COMMIT;
          rows_updated := 0;
       END IF;
    END LOOP;
    If that is your processing, then you need to look at changing the row by row processing to set based processing and only commit once at the end of each logical transaction.

    John
  • 4. Re: How not to generate archive logs for database jobs
    730428 Guru
    Currently Being Moderated
    First of all let me say that if the redo logs generated by an application is greather then the whole db probably there's some tuning to do in the application.
    The redo logs don't store the data you change but the sql statements you issue...
    Are you sure you cannot change the application to run less sql statements (for example replacing some INSERT...VALUES with an equivalent INSERT...SELECT)?

    Anyway, if you can change your inserts to direct-path inserts (using the /*+ APPEND */ hint) you can turn off logging on these reporting tables
    ALTER TABLE MY_REPORTING_TABLE NOLOGGING;
    All direct-path inserts won't be logged...

    Max
  • 5. Re: How not to generate archive logs for database jobs
    TamasSzecsy Newbie
    Currently Being Moderated
    John, Massimo,

    actually the code definitely needs tunings. It has been written by someone, who already left the company and the documentation is pretty questionable. It is hard to touch it without risking getting some unexpected results. All the same, based on what you wrote, I think it is unavoidable.

    Thank you for the tips and help.

    Regards, Tamas
  • 6. Re: How not to generate archive logs for database jobs
    843389 Newbie
    Currently Being Moderated
    becareful with no logging in certain cases such as active clones it can cause block corruption.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points