Skip to Content
avatar image
Former Member

Huge archivelog generated in DB when loading data in BI.

We have observed that a huge log is being generated when ever theres a loading activity carried on BI.The log occupies huge Undo space.

Loaded 50 million recs in BI which generated 208 GB and undo space 117 GB at DB level.

So the concern is how to prevent such huge log being generated ? or are there any settings at BI that prevents generating huge Archive log at DB when ever theres huge load activity being performed ?

Pls suggest / inputs.

Regards

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    avatar image
    Former Member
    Sep 19, 2007 at 04:48 AM

    Hi Ellora,

    From my experience, DBA / BASIS has very limited control on the way BW works. Most of the activites are driven at the application level using automated procedures.

    You will need to work with the BI developer and perform the loads in smaller chunks which I believe is possible, instead of giving a large selection in single run.

    Automate brarchive runs at os level which are triggered once the space is 60% full in the archive directory.

    Regards,

    Nisch

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 19, 2007 at 03:29 AM

    Hi,

    I dont have exp on BI environment.

    But usually to avoid huge undo while big data loading happens we use commit in between means frequently

    Or there is an option NOLOGGING which can be used to avoid logging.

    You need to check with I how to use this option mostly as HINT.

    Vinod

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi Ellora

      Do you have a process chain set up like drop indexes, load data, create indexes? As far as i know indexes will be created with nologging.

      Please do also make sure your undo tablespace is not auto extending.

      Regards Michael

  • avatar image
    Former Member
    Sep 19, 2007 at 03:30 PM

    Hi Ellora,

    First you cannot control the Archive logs generated during the dataloads in the live environment. Only thing you can do is increase the frequency of your Archive log backup, to make the Archive log file system free and avoid Archive Stuck.

    Alternatively if it is a onetime activity you can plan for a down time and take the database to NOARCHIVE mode, do the dataload, and revert to ARCHIVE MODE. Ensure no Business activity happening during this period.

    To avoid UNDO space overflow, you can commit in small chunks, so that it will get refresh.

    Hope this answers your question.

    Regards,

    Sanujit

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 19, 2007 at 03:47 PM

    Note 584548 should be a good entry point for analyzing this kind of problem. But in case of BI loads it is quite normal to have a lot of archivelogs. Every record that is loaded results in several redo information (the record itself, its before image, and in case of existing indexes also the changes to the indexes and their before images).

    Add comment
    10|10000 characters needed characters exceeded