General
cancel
Showing results for 
Search instead for 
Did you mean: 

RBS segment not flushed after an import

SOLVED
Go to solution
Enrico Venturi
Super Advisor

RBS segment not flushed after an import

Hello colleagues,
we're using ORACLE 8.1.7.
We've found that the RBS isn't flushed after that an import run.
This means that if during the first import we use 40% of the RBS, at the end of the second import we've used 80% and the third import fails since not enough RBS is available.
The RBS isn't flushed when the database is halted.
I'd like to know if there's an automatic way to flush the RBS when the database is stopped, or alternatively which is the SQL command to flush the RBS.

Thanks, regards
Enrico
5 REPLIES
Steven E. Protter
Exalted Contributor

Re: RBS segment not flushed after an import

Shalom Enrico,

I would suggest increasing the frequency of rotating the RBS in the init.ora for the database.

You risk data loss if this configuration is not correct.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Hein van den Heuvel
Honored Contributor

Re: RBS segment not flushed after an import

Hmm,

RBS segments are not 'flushed' and they don't die. They just fade away, If they get re-used too soon you'll get the infamous :
"Ora-1555:Snapshot too old"

Oracle 8.1.7. So that is still the manual rollbackl segment manipulation right? You might want to google for some handy scripts to display usage.

>> We've found that the RBS isn't flushed after that an import run.

Any other long running queries?

>> The RBS isn't flushed when the database is halted.

Exactly what do you mean by that? How can you tell? Which SQL statement you you use to conclude that?

RBS data has no value when the database is halted. After stop + start you should be able to drop and recreate as you feel like.
(and re-create teh segments and put them online)

>> and the third import fails since not enough RBS is available.

Care to share the exact error message?
Anyhing extra in alert.log

>> which is the SQL command to flush the RBS

Something like;

alter rollback segment R01 offline;
drop rollback segment R01;
alter rollback segment R02 offline;
drop rollback segment R02;
alter rollback segment R03 offline;
drop rollback segment R03;
:
L
drop rollback segment RBS_DUMMY;
drop tablespace RBS1;
drop tablespace RBS2;
create rollback segment rbs_dummy tablespace SYSTEM;
alter rollback segment rbs_dummy online;
create tablespace RBS1
extent management local uniform size 1m
datafile '/oracle/dbf/roramst_rbs01.dbf' size 4000M reuse;
create tablespace RBS2
extent management local uniform size 1m
datafile '/oracle/dbf/roramst_rbs02.dbf' size 4000M reuse;
create rollback segment R01 tablespace RBS1 storage (initial 1m next 1m minextents 2 maxextents unlimited);
alter rollback segment R01 online;
create rollback segment R02 tablespace RBS2 storage (initial 1m next 1m minextents 2 maxextents unlimited);
alter rollback segment R02 online;
:
:

( back in 8.17. days I used to use perl scripts to generate sequences like this based on the active RBS situation )

Hth,
Hein van den Heuvel
HvdH Performance Consulting
spex
Honored Contributor

Re: RBS segment not flushed after an import

Hi Enrico,

ALTER ROLLBACK SEGMENT rbsname SHRINK TO n M

If the rollback segment has an OPTIMAL value defined, 'ALTER ROLLBACK SEGMENT rbsname SHRINK' will return it to this OPTIMAL size.

PCS
TwoProc
Honored Contributor

Re: RBS segment not flushed after an import

I think Spex nailed it for you - along with Spex's comment, I'd add that you should review the values of "optimal" on those rollback segments.

Still, your problem is a bit unusual in behavior - I believe the last 8.1.7 might have been 8.1.7.4 - are you up to that level?
We are the people our parents warned us about --Jimmy Buffett
Patti Johnson
Respected Contributor
Solution

Re: RBS segment not flushed after an import

Not sure I'm reading the question correctly, but when you run the import are you using COMMIT=Y - the default is N.

RBS are available for reuse once the data has been committed - if you shutdown the db then it will need the rbs to rollback the uncommitted transaction when the db is restarted.

Patti