Solved

Within a perl script set transaction to use certain rollback segment

Posted on 2011-02-14
21
890 Views
Last Modified: 2013-12-19
I have oracle 10.2.0.4 on SUN Solaris 10 - t2000 server
I have a perl script that does some archiving and I am trying to get it to use a large rollback segment because it keep failing with

ORA-01555: snapshot too old: rollback segment

I am not a perl programmer so I don't know if I have this correct.
I run this script on Friday nights and it continues to fail

Can  you tell me what I am doing wrong?

my $db = DBI->connect('dbi:Oracle:prod', 'admin', 'admin', {RaiseError=>0, PrintError=>1, AutoCommit=>0, LongReadLen=>8092, LongTruncOk=>1});
$db->do("set transaction use rollback segment RBS_BIG");
0
Comment
Question by:bkreynolds48
  • 10
  • 9
  • 2
21 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34890278
I don't think forcing your transaction to use a large RBS will fix your problem.

Unless your transaction is actually stepping on itself, this is likely caused because data your large transaction needs has been removed from the rollback segments from other processes.

If you run the process from sql*plus with the RBS_BIG set and normal workload on the database, does the process run successfully without an ORA-01555?
0
 
LVL 1

Author Comment

by:bkreynolds48
ID: 34890345
Cannot run this from sqlplus.
This process was down for quite a while - our programmer left and no one was monitoring this.
Now I have it and don't know how to run this standalone.
This script is supposed to archive data older than three months in PRODUCTION so I can't play around with it.

Is there no way in perl to make this work or can you think of another way around this?
I had thought to maybe take all the other rollback segments offline before the script runs but do not think that is an option because the application is still running.  
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34890390
>>Is there no way in perl to make this work or can you think of another way around this?

I was wanting to see what is causing the error.  If it is generated because your archive process takes to long and the other OLTP transactions are rewriting the online files faster than your process, then setting your process to a larger rollback segment will probably not help.

If you are getting it because your archive process is stepping on it's own toes, then it might work.

My guess is it is because your archive process is taking to long and a single large rollback segment for it will not fix the problem.

If you cannot tweak/tune the archive process then you will probably need to build larger rollback segments.
0
 
LVL 1

Author Comment

by:bkreynolds48
ID: 34890504
FetDBD::Oracle::st fetchrow_array failed: ORA-01555: snapshot too old: rollback segment number 12 with name "RBS_15" too small (DBD ERROR: OCIStmtFetch) [
for statement ``


I think it is just takiing too long because there is so much data to get rid of
0
 
LVL 1

Author Comment

by:bkreynolds48
ID: 34890521
DBD::Oracle::st fetchrow_array failed: ORA-01555: snapshot too old: rollback segment number 20 with name "RBS_20" too small (DBD ERROR: OCIStmtFetch) [for s
tatement ``
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34890536
>>segment number 20 with name "RBS_20" too small

I stand corrected.  It might NOT be because of the other processes.

Did you get the "too small" error when you tried your code with 'set transaction' above?

I'm not a Perl person so I can only be limited help if it is a Perl issue.

0
 
LVL 1

Author Comment

by:bkreynolds48
ID: 34890550
Yes I set the transaction as stated above to rbs_big but it does not use that rollback segment - it grabs the smaller ones
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34890628
My 'guess' (and it really a guess), is that somewhere else in the process a commit or rollback is happening which is ending the current transaction.
0
 
LVL 1

Author Comment

by:bkreynolds48
ID: 34890658
I see some commits in the script - I don't know if I can just copy the set transaction line down and put it under each commit or not - do you think that will work?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34890701
You 'should' be able to to that.  I can't see why you couldn't.

The question is do you 'need' to.  Are all the DML commands that large or will you only need to set a specific RBS for a couple of the really big ones?

Remember to take everything I say here with skepticism since I am posting from a non-Perl background.

Hopefully some Perl Experts will arrive later and confirm my theories or prove that I really should stay away from Perl questions.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 1

Author Comment

by:bkreynolds48
ID: 34890721
Yes I was hoping to get someone who know perl to look at it.
There are a few large tables that need to have thier data deleted - these fail becuase of the amount of data - all the rest - the smaller ones run fine in this script.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34890794
While you wait for a Perl expert, I confirmed this from the docs:

http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_10005.htm#SQLRF01705

Issuing either a COMMIT or ROLLBACK statement explicitly ends the current transaction.

The operations performed by a SET TRANSACTION statement affect only your current transaction, not other users or other transactions.
0
 
LVL 1

Author Comment

by:bkreynolds48
ID: 34890828
There is usually not a lot going on when this script run
Most of the time there is not a lot of rollback activity - I probably have too many rollback segments to start with

Online Rollback Segments:

Name    NrEx     Size         Init         Next PctI MinE     MaxE     Opt size Stat
------- ---- -------- ------------ ------------ ---- ---- -------- ------------ ----
RBS_01     2       16            8            8    0    2   32,765           16 OnL
RBS_02     2       16            8            8    0    2   32,765           16 OnL
RBS_03     2       16            8            8    0    2   32,765           16 OnL
RBS_04     2       16            8            8    0    2   32,765           16 OnL
RBS_05     2       16            8            8    0    2   32,765           16 OnL
RBS_06     2       16            8            8    0    2   32,765           16 OnL
RBS_07     2       16            8            8    0    2   32,765           16 OnL
RBS_08     2       16            8            8    0    2   32,765           16 OnL
RBS_09     2       16            8            8    0    2   32,765           16 OnL
RBS_10     2       16            8            8    0    2   32,765           16 OnL
RBS_11     2       16            8            8    0    2   32,765           16 OnL
RBS_12     2       16            8            8    0    2   32,765           16 OnL
RBS_13     2       16            8            8    0    2   32,765           16 OnL
RBS_14     2       16            8            8    0    2   32,765           16 OnL
RBS_15     2       16            8            8    0    2   32,765           16 OnL
RBS_16     2       16            8            8    0    2   32,765           16 OnL
RBS_17     2       16            8            8    0    2   32,765           16 OnL
RBS_18     4       32            8            8    0    2   32,765           16 OnL
RBS_19     2       16            8            8    0    2   32,765           16 OnL
RBS_20     2       16            8            8    0    2   32,765           16 OnL
RBS_21     2       16            8            8    0    2   32,765           16 OnL
RBS_22     3       24            8            8    0    2   32,765           16 OnL
RBS_BIG   58       58            2            1    0    1   32,765              OnL
SYSTEM     2        0            0            0    0    2      505              OnL
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34890863
>> I probably have too many rollback segments to start with

It depends.


I really should have mentioned this earlier:  Rollback segments are pretty old-school.  Mainly for the problem you are seeing.  Undo is the 'new' and improved way.

Can you migrate from Rollback to Undo?
0
 
LVL 1

Author Comment

by:bkreynolds48
ID: 34890929
I have read a little on that - but being old school I have been a bit leary of doing that - not know how this would work.  Yes I can do it - as long as I keep it running my boss let's me make the database choices.  Is there a simple explanation of what that will do for me and a simple way to do it?
I have a maintenance window every Sunday morning
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 400 total points
ID: 34891035
>>but being old school I have been a bit leary of doing that

I'm also leery of many 'new' features Oracle tries to sell me.  However UNDO has been around long enough and it really is the way to go.

Here's a 'nutshell' article (it's 9i but should be the same for 10g):
http://www.oracle-base.com/articles/9i/AutomaticUndoManagement.php

The only thing to be aware of in your setup is the undo_retention.  You might need a higher number to 'guarantee' the data for your archive is available.

Please don't jump in and thing "my archive runs X hours, I MUST set undo_retention to X hours.  It's not that linear.
0
 
LVL 34

Expert Comment

by:johnsone
ID: 34899522
Based on the descriptions here, it sounds like you are running into a fetch across commits problem.

Is this what your script is doing?

Loop through some select statement
inside loop, update the record that was selected
at some interval commit (or commit every record)

Based on the descriptions here, that is what sounds like is happening.

If this really is the case, then changing the rollback segment used by the transaction will not help.  You will still get the message.

You have 2 basic options.

After committing, reopen the select cursor, this will avoid the issue by not requiring the pre-commmit data.

Load all the primary keys, or rowids, of the records to be updated into a temporary table and then select from that.  This will also avoid the issue as you are not selecting from the table you are updating and therefore the pre-commit data is not required to satisfy your loop query.
0
 
LVL 1

Author Comment

by:bkreynolds48
ID: 34905997
johnsone,

I believe your description is what is going on but as I am not a programmer I don't know that I can initiate your suggestions in this perl script
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34907391
>>You will still get the message.

You'll get a 'rollback segment too small' for a fetch across commit?
0
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 100 total points
ID: 34907462
Absolutely.  The worst message in Oracle.  The important part of the message is "snapshot too old", the rest of it is really irrelevant.  You will definitely get ORA-01555 for fetch across commit.  Since the data was committed, the rollback segment that holds that is marked for reuse.  Once one of the segments wraps, it overwrites the committed part of the rollback segment.  In this case, it will wrap rather than extend, so it doesn't use the full segment.  Really sucks, but that is the way it has always been.

The only way that I know of that you could possibly get around this without changing the code is to go to automatic undo management, set undo retention high enough that it can hold all undo for the time it takes to run this process, and make sure the undo space is large enough to hold it all.
0
 
LVL 1

Author Closing Comment

by:bkreynolds48
ID: 34907563
Thanks - I will try to get that set up during our maintenance window
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
oracle global variables 4 51
Cannot open form error 6 47
JDeveloper 12c for 32 bit 4 34
VB.Net - CSV to Oracle table 4 27
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now