Begining Rollback-to-Undo conversion research

[davey23uk]

you have to create a new undo tablespace so do that first, then configure the init.ora parameters, startup using the undo tablespace then drop tablespace rbs including contents and datafiles and you are done

[roadwarriorDBA]

you have to create a new undo tablespace so do that first, then configure the init.ora parameters, startup using the undo tablespace then drop tablespace rbs including contents and datafiles and you are done

So you just leave the system rollback segments alone ?

Segment Name            Tablespace                        Size
-----------------            ------------                       ------
SYSTEM                       SYSTEM                            819200
R000                           SYSTEM                            1064960 

[davey23uk]

you will always have the system rollback segment (the one called system) even with undo its there.

The second one isnt a system rollback segment, it is one you created in the system tablespace - you should just drop that one

[roadwarriorDBA]

Thanks Davey.

I did not create any of them. I am new at a remoteDBA shop, I have 30 clients and little control over them. For instance last weekend I had to wrestle with 16GB refresh of test/dev and test was horribly fragmented because they are not using LM uniform extent size.

In my shops I only used LM : small, medium, large, JUMBO.

This job is looking more and more like a physical DBA gig.

Going to start a thread on it.

[roadwarriorDBA]

Well it seems to work for me but the client gets an error.


Tue Jun 28 14:11:27 2005 - program 'OE310', User 'OSID:dev' Driver Version '8.0.
DELETEDBREC() error on file 'CUSTORDER' in 'PROD8'
DeleteSqlRec(LAWPROD.CUSTORDER)
OCIStmtExecute() returned OCI_ERROR code 376 parse offset 0 (
ORA-00376: file 14 cannot be read at this time
ORA-01110: data file 14: '/oracle/data1/prod804/rbs_01.dbf'
)


I put that rbs_01.dbf offline.

But it works for me

SQL> insert into reader.test values ( 5 );



1 row created.



SQL> rollback;



Rollback complete.





SQL> insert into reader.test values ( 5 );



1 row created.



SQL> commit;



Commit complete.



SQL> delete from reader.test;



1 row deleted.



SQL> commit;



Commit complete.




#rollback_segments = (big1,big2,big3,big4,big5)
#rollback_segments = (rbs01, rbs02, rbs03, rbs04, rbs05, rbs06, rbs07, rbs08, rbs09, rbs10,rbs11,rbs12,rbs13,rbs
14,rbs15,rbs16,rbs17,rbs18,rbs19,rbs20)

UNDO_MANAGEMENT = AUTO
UNDO_TABLESPACE = undotbs_01
UNDO_RETENTION = 100
UNDO_SUPPRESS_ERRORS = TRUE

[davey23uk]

if that datafile is part of a tablespace which held your rollback segments then just drop it (and then the tablespace)

[roadwarriorDBA]

if that datafile is part of a tablespace which held your rollback segments then just drop it (and then the tablespace)

If it has to revert back to RBS then I can just online it.

Are you saying that it lying around offline can cause a problem? I would think the config parameters would cause it to strictly use the UNDO.

?

[davey23uk]

well my point to that would be, dont need it - get rid of it then you cant have any problems

[roadwarriorDBA]

well my point to that would be, dont need it - get rid of it then you cant have any problems

Problem gone. Odd. Even with the right startup parameters an old RBS tablespace lying around causes problems.

[marist89]

Problem gone. Odd. Even with the right startup parameters an old RBS tablespace lying around causes problems.

was somebody trying to explicitly use that RBS by chance?