-
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
-
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
-
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
-
Do frequent commit to avoid RBS from swelling.
And add more redo log groups to avoid the other error.
Sanjay
-
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
-
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?
-
Hello;
As pando wrote, YOU MUST CHANGE the sizing and number of redo-log-files;
Orca
-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|