Unable to allocate log, archival required ???
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Unable to allocate log, archival required ???

  1. #1
    Join Date
    Sep 2001
    Posts
    261

    Question

    Help!

    I was trying to run a script that updates several tables (w/ SQL Plus), after awhile i got a an error (from Command Prompt shell) - Unable to allocate log, archival required, Please acknowledge. I also got another error - unable to extent rollback segment (SQL Plus).

    I created a much larger rollback segment for my transaction using SET TRANSACTION USE ROLLBACK SEGMENT x. however, I still get the same error.

    Can anyone explain why I got an "- Unable to allocate log, archival required, Please acknowledge." error from what I am doing?

    I please to create a bigger rbs to resolve the second error message. will this work? Another alternative is to breakdown my script into smaller ones and commit before running the next. Any suggestions?

    BTW, Im running Oracle 8.1.6 on NT4 SP6a

    Thanks, I hope to get a reply for you.

    [Edited by D Maverick on 06-04-2002 at 02:27 AM]
    The Maverick
    Oracle Certified DBA - 8i

  2. #2
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Your ARCHIVE DESTINATION is full, or your redo logs are switching to fast for the ARCHIVE process to keep up.

    Cheers,
    OCP 8i, 9i DBA
    Brisbane Australia

  3. #3
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Hello;

    First there seems to be no place for your rbs to grow;
    Probably ther is place but it is fragmented; -> ALTER TABLSPACE COALESCE
    or set the RBS in an own Tablespace.

    Second it seems that the logwriter timed out in allocating the new log; If your db is not corrupt then
    create BIG ( 64M, 256M,... ) Redo-files and for example 10 or more logfile-groups; So your transaction has place to write the log wihout contention;

    Orca

  4. #4
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Do frequent commit to avoid RBS from swelling.
    And add more redo log groups to avoid the other error.

    Sanjay

  5. #5
    Join Date
    Sep 2001
    Posts
    261
    I have check the archive destination - it has about 6GB of free space. Im just curios why i got an "Unable to allocate log, archival required, Please acknowledge" error.

    In the meantime, i plan to:
    1. modify my script to perform COMMIT more frequently
    2. increase the size of the RBS tablespace,
    3. create a larger RBS
    4. assign the transaction to that RBS

    Any suggestions? solution?
    The Maverick
    Oracle Certified DBA - 8i

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    your log switch is too fast, i.e redo log too small, too few redo log groups. archiver is too slow compared with log switch

    the rbs error simply means you either hit maxextents error or tablespace is full, rbs tablespace is not used by a single rbs is it?

  7. #7
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Hello;
    As pando wrote, YOU MUST CHANGE the sizing and number of redo-log-files;
    Orca

  8. #8
    Join Date
    Sep 2001
    Posts
    261
    Pando, Orca777, grjohnson

    Even if the error occurs simulaneously with "unable to extent rollback segment"? Actually i believe your explanation is very valid, im just curious abt how both errors are related.
    hope u guys can explain further.

    i ran d script 3x and it generates d sames errors =) thanks
    The Maverick
    Oracle Certified DBA - 8i

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    post

    Code:
    select 
    a.segment_name segname, 
    a.tablespace_name tabsp, 
    a.max_extents maxext, 
    b.extents ext, 
    b.hwmsize hwm, 
    c.sum(bytes/1024/1024) free
    from dba_rollback_segs a, v$rollstat b, dba_free_space c
    where b.usn=a.segment_id
    and a.tablespace_name=c.tablespace_name
    and c.tablespace_name='YOU_RBS_TABLESPACE'
    group by a.segment_name, a.tablespace_name, a.max_extents, b.extents, b.hwmsize;
    if you are the only user in the server then itīs perfectly understandable you run into same error 3 times

  10. #10
    Join Date
    Sep 2001
    Posts
    261
    Thanks pando for the script, it was quite useful. But the thing that puzzles me is how the two errors are related to each other:

    (from Command Prompt shell) - Unable to allocate log, archival required, Please acknowledge.

    (form SQL Plus) - unable to extent rollback segment

    since both errors occured simutaneously of each other for 3 same times. Thanks

    [Edited by D Maverick on 06-04-2002 at 05:11 AM]
    The Maverick
    Oracle Certified DBA - 8i

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