Begining Rollback-to-Undo conversion research

Printable View

06-20-2005, 04:13 PM
roadwarriorDBA

Begining Rollback-to-Undo conversion research

One of our clients needs an upgrade to take advantage of 9i's UNDO automation.

I am going to embark on the best approach to this - if anyone knows a great link please post it here. Otherwise I will post what I end up going with.
06-20-2005, 04:54 PM
gandolf989

FYI, You should watch out for any set transaction statements in your code.
06-20-2005, 05:24 PM
dbtoo

google didn't find anything for you? :confused:

http://www.google.com/search?q=oracl...&start=30&sa=N

Here's a 10g whitepaper...

http://download.oracle.com/oowsf2004/1241_wp.pdf
06-21-2005, 01:50 AM
OracleDoc

Quote:

Originally Posted by gandolf989

FYI, You should watch out for any set transaction statements in your code.

ooooooooo!!! good catch Gandolf!
06-21-2005, 02:30 AM
TimHall

Hi.

This might help:

http://www.oracle-base.com/articles/...Management.php

Cheers

Tim...
06-21-2005, 02:58 PM
roadwarriorDBA

Tim, did you author that article ?

"Hope this helps. Regards Tim..."

thanks

I am wondering about how sized rollback segments map over to the size of undo segments.

..off to tahiti..
06-21-2005, 05:30 PM
roadwarriorDBA

Finding that most content on the web talks about 10G, this is a 9i client so I need a way to size it based on existing rollback. I could just make it as big as all rollback space.

DBMS_UNDO_ADV.RBU_MIGRATION ( 10 G )

Migrating from Rollback Segments to UNDO Tablespace

To move an existing Oracle9i or Oracle Database 10g system from manual undo management (using rollback segments) to automatic undo management requires a shut down and restart—you must shut-down the database, set the undo_management parameter to AUTO, and then restart the database—assuming you've created the UNDO tablespace first, however. To get some guidance about how large to make that UNDO tablespace, you can use a function from the Undo Advisor PL/SQL package created just for that purpose, as in this example:

SQL> set serveroutput on;
SQL> declare utbsize_in_MB number;
2 begin
3 utbsize_in_MB := DBMS_UNDO_ADV.RBU_MIGRATION;
4 dbms_output.put_line(TO_CHAR(utbsize_in_MB));
5 end;
6 /

187

PL/SQL procedure successfully completed.

In this case, the return value of 187 is the suggested starting size in megabytes for an UNDO tablespace based on current rollback segment utilization in an existing system.
06-22-2005, 03:57 AM
simply_dba

Quote:

Originally Posted by gandolf989

FYI, You should watch out for any set transaction statements in your code.

Shouldn't pose a problem if undo_supress_errors=TRUE
:confused:
06-22-2005, 06:09 AM
TimHall

Quote:

Originally Posted by roadwarriorDBA

Tim, did you author that article ?

"Hope this helps. Regards Tim..."

thanks

I am wondering about how sized rollback segments map over to the size of undo segments.

..off to tahiti..

Yes I did ;-)

I would start by making the UNDO tablespace about that same size as your current total redo size. Of course, if you want to retain the data for longer you are going to need a bit more space.

Once you are up and running you can use the undo advisor to check how your undo is performing, and what sort of retention time you can expect under typical load.

Suppress errors? Yes, unless you want to identify code that tries to use a specific RBS.

Cheers

Tim...
06-22-2005, 01:17 PM
roadwarriorDBA

PHP Code:

SYSTEM SYSTEM 819200 R000 SYSTEM 1064960 RBS01 RBS 11714560 RBS02 RBS 11714560 RBS03 RBS 11714560 RBS04 RBS 11714560 RBS05 RBS 11714560 RBS06 RBS 11714560 RBS07 RBS 11714560 RBS08 RBS 10649600 RBS09 RBS 11714560 RBS10 RBS 11714560 RBS11 RBS 11714560 RBS12 RBS 11714560 RBS13 RBS 11714560 RBS14 RBS 11714560 RBS15 RBS 11714560 RBS16 RBS 11714560 RBS17 RBS 10649600 RBS18 RBS 11714560 RBS19 RBS 11714560 RBS20 RBS 11714560 RBSBIG RBS 15728640

So I offline the rb segments the drop the RBS tablespace including contents.

What should be done with the 2 system RBS's ?

So far I have not seen in any Oracle doc a recommended process steps for converting from RB to UNDO.
06-22-2005, 01:22 PM
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
06-22-2005, 03:22 PM
roadwarriorDBA

Quote:

Originally Posted by 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

So you just leave the system rollback segments alone ?

PHP Code:

Segment Name Tablespace Size ----------------- ------------ ------ SYSTEM SYSTEM 819200 R000 SYSTEM 1064960
06-22-2005, 03:56 PM
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
06-22-2005, 05:01 PM
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.
06-28-2005, 06:02 PM
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
06-28-2005, 06:47 PM
davey23uk

if that datafile is part of a tablespace which held your rollback segments then just drop it (and then the tablespace)
06-28-2005, 09:45 PM
roadwarriorDBA

Quote:

Originally Posted by davey23uk

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.

?
06-29-2005, 04:14 AM
davey23uk

well my point to that would be, dont need it - get rid of it then you cant have any problems
06-30-2005, 09:38 AM
roadwarriorDBA

Quote:

Originally Posted by davey23uk

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.
06-30-2005, 10:12 AM
marist89

Quote:

Originally Posted by roadwarriorDBA

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?
06-30-2005, 11:32 AM
roadwarriorDBA

Dunno.

PHP Code:

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' )