Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2012
    Posts
    26

    Unanswered: Archivelog Every 30 Seconds

    Our database was generating archivelog(50MB) every 30seconds! I think this is not normal because what I did is open our database, I was the only one who is connected, I'm not running anything, but our database is still generating archivelogs!

    Our redo logs: 6groups 3members.

    This are the things I saw on our alert logs:
    - advanced to log sequence
    - cannot allocate new log, sequence
    - checkpoint not complete
    - private strand flush not complete

    What I did is change the log mode of our database to noarchivelog then open the database, then returned it to archivelog mode then that fixed the problem. But the thing is after 6hours its abnormal behavior goes back again.

    Edit*
    Our database is Oracle 10gR2 and our OS is SUSE Linux 64bit

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I would resize the REDO log file to be 2GB each
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    No one else connected? Are there jobs running?
    Code:
    select * from dba_jobs

  4. #4
    Join Date
    Feb 2012
    Posts
    26
    Noone else is connected.
    I checked the time of the archivelogs, it has 20min-1hr interval but after 3:49 PM yesterday the interval became 20sec-1min.

    There are two values for dba_jobs:
    JOB, LOG_USER, LAST_DATE, INTERVAL, WHAT
    1, sysman, <sysdate>, sysdate + 1 / (24 * 60), EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS();
    41, sys, 3/1/2012 3:49 PM, SYSDATE + 7, DBMS_STATS.gather_schema_stats('CPI',DBMS_STATS.AU TO_SAMPLE_SIZE);

    Is it the cause of the problem or just a coincidence?

    BTW I did change our log mode again to noarchivelog then return it to normal, 2AM-3:45AM the time interval of the archivelogs was 20sec-1minute again but the next one was 5:52AM.

    Is there a way to trace what inside the archivelogs or redo logs so I can trace the root cause of the problem? Or is it because of my DB parameters?

    NAME VALUE DISPLAY_VALUE
    remote_archive_enable true true
    log_archive_config
    log_archive_start FALSE FALSE
    log_archive_dest
    log_archive_duplex_dest
    log_archive_dest_1 location=/d01/arc location=/d01/arc
    log_archive_dest_n
    log_archive_dest_10
    log_archive_dest_state_1 enable enable
    log_archive_dest_state_n enable enable
    log_archive_dest_state_10 enable enable
    log_archive_max_processes 2 2
    log_archive_min_succeed_dest 1 1
    standby_archive_dest ?/dbs/arch ?/dbs/arch
    log_archive_trace 0 0
    log_archive_local_first TRUE TRUE
    log_archive_format %t_%s_%r.dbf %t_%s_%r.dbf
    archive_lag_target 0 0

    log_checkpoint_interval 0 0
    log_checkpoint_timeout 1800 1800
    log_checkpoints_to_alert FALSE FALSE

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    DBMS_LOGMNR can be used to learn who & what is doing the DML
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Your EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS() job is running every minute. And 50 meg is too small for archivelogs
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Feb 2012
    Posts
    26
    @beilstwh: A senior DBA told me the same thing, he also told me to apply 10.2.0.5 patch set. I'll share if this is the solution to our problem.

    @anacedent: Thanks. That really helps.

    @MCrowley: Thanks!

    @anacedent: Thank you!

  8. #8
    Join Date
    Feb 2012
    Posts
    26
    Problem solved.

    There was a module in our system that causes continues generation of archive log, it has an infinite loop in it. Thanks everyone!

  9. #9
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    @Rektanocrit, good to see you located the problem.
    Could you maybe give us a one or two liner as to how you tracked down the module causing the problem? I.e. viewed session wait stats, IO stats, AWR report, someone woke up and realised they forgot to exit when sql%notfound?
    Thanks...

  10. #10
    Join Date
    Feb 2012
    Posts
    26
    @dayneo: This is what I did. I open the archive logs (<filename>.arc) using notepad++, it was non-readable but there was a string in it "ASOLOMON", I found out that it was a one of the user.

    The next thing I did is open Toad for Oracle. Database menu > Monitor > SGA Trace/Optimization.. Then I sort all the SQL statement by Elapsed Time and found out that ASOLOMON was the top user. (I saved the Update statement ASOLOMON execute)

    I asked her what she was doing, she said she was using <module causing the cont. generation of arc logs> and then I check the source code of that module and found a bad update statement in it.

    We just fixed that module and it fixed the problem. That's it. I hope this helps you somehow.

  11. #11
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    @Rektanocrit
    Thanks for the feedback. Sometimes we get similar kinds of problems with modules not doing what they are supposed to. It's always a challenge to find out what is causing the problem and why.

Posting Permissions

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