Materialized Views and Archive Logs
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Materialized Views and Archive Logs

  1. #1
    Join Date
    Oct 2001
    Location
    Doha, Qatar
    Posts
    103

    Question Materialized Views and Archive Logs

    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.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    How about actually finding out what caused it?

    Log miner will tell you that

  3. #3
    Join Date
    Oct 2001
    Location
    Doha, Qatar
    Posts
    103
    Yeah. I will do that. Hope I will get some information from that.

    Dileep.

  4. #4
    Join Date
    Nov 2005
    Posts
    32

    Analying the archive log contents:

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width