Q

Minimizing archive log generation

We are using Oracle 9.2.0.1.0 under Windows 2003 Enterprise Server OS. After putting the database into archive log mode, it is generating a lot of archive logs. The database size is 10 GB, and every day it is generating 20-30 GB of archive logs. What is the reason that it generates that much of archive logs. How can I minimize the archive log generation?

It is not impossible for a database to generate large amounts of redo logs even though the database may not be

that large. The amount of redo generation is determined by the volume of data change in the database. To illustrate this, assume that I issue the following command:

UPDATE scott.emp SET lname=UPPER(lname);
Now I issue this command 1,000 times (for some really odd reason). The amount of data in the database has not changed yet the amount of redo can be large.

So the big question to be answered is to determine what large changes are happening in your instance. Luckily, you can mine your archived redo logs to see if one or two tables are undergoing more changes than other tables. Chapter 9 of the Administrator's Guide is titled Using LogMiner to Analyze Redo Logs. This document contains the instructions you'll need to mine your archived redo logs to get an idea of the changes occurring in your database.

This was first published in June 2005

Dig deeper on Oracle database backup and recovery

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close