Materialized Views and Archive Logs

[tvrdileep]

Hai Everybody.
I have a big problem. I mean our database had redologs of 180MB each and usually only 5 or 6 archivelogs are produced daily. Yesterday, suddenly more than 30GB of archivelogs are produced. One of my colleague said it may be due to Materialized Views. Actually yesterday they did lot of testing activity but they said that the data size that they ised for testins is small. I would like to know if really Materialized Views are increasing archive logs, how to check and reduce that. Please help me..

Dileep.

[davey23uk]

How about actually finding out what caused it?

Log miner will tell you that

[tvrdileep]

Yeah. I will do that. Hope I will get some information from that.

Dileep.

[dbaxchangedba]

If it a matter of you just finding out what objects had transactions performed on them and for which redo records were generated, a quick way would be for you to pick up an archive log file from around the time when excessive redo generation occured and use "alter system dump logfile " command to dump the contents. Make sure your max_dump_file_size value is set high since the tracefile dump can be large.
Once the logfile dump has been completed you can get the object details by doing this:
On unix (I've done this on sun solaris9):
cat |grep objn|cut -d' ' -f9-10|sort|uniq

The above will give a unique listing of object ids that were involved in transactions that generated redo logs and translates to the object_id column within the dba_objects view or obj# column within the obj$ table.

Hope this helps!

http://www.dbaxchange.com
(One stop site for practical solutions to oracle issues, oracle findings, Book recommendations, International job search site recommendations and much more....)