Huge archivelog generated in DB when loading data in BI.

[Former Member]

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

[Former Member · Accepted solution]

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

[Former Member]

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).

[Former Member]

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

[Former Member]

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

[Former Member]

Hi Vinod,

I belong to BI team and as far as my knowledge goes i do not see any option 'No Logging' while performing the load.

So lets see if any other experts would answer this Query.

Regards.

[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