Wednesday, July 16, 2008

Converting from Rollback to Undo

From : http://www.shutdownabort.com

This procedure will drop the existing rollback segments and tablespace, and replace them with an auto-managed undo tablespace.
# 1. Identify the rollback segments and tablespaces to be removed:

select segment_name
, tablespace_name
, status
from dba_rollback_segs
where tablespace_name != 'SYSTEM'
/

#

# 2. Produce offline and drop statements for each rollback segment:

select 'alter rollback segment ' || segment_name || ' offline;'
from dba_rollback_segs
where tablespace_name = '&TS'
and tablespace_name != 'SYSTEM'
/

select 'drop rollback segment ' || segment_name || ';'
from dba_rollback_segs
where tablespace_name = '&TS'
and tablespace_name != 'SYSTEM'
/

Review the output and, if you are happy with it, cut and paste it back into sqlplus.
# 3. Make a note of the old tablespace's size and location, then drop it.

drop tablespace including contents and datafiles
/

# 4. Create a new 'undo' tablespace in place of the old one.

create undo tablespace undo
datafile '' size
extent management local
/

# 5. Update the initialisation parameters
If you are using a spfile (and you should be!) run the following commands:

alter system reset rollback_segments scope=spfile sid='*'
/

alter system set undo_management=auto scope=spfile
/

alter system set undo_tablespace=undo scope=spfile
/

If you are still using a pfile (init.ora) then do the following:

Remove the following settings:

rollback_segments=...

Add/alter these two lines:

undo_management=auto
undo_tablespace=undo

# 6. Restart the instance
Note the location of the alert log. If there is a problem during start-up, you will probably receive nothing more than a message saying 'instance aborted'. The alert log will contain a slightly more detailed explanation.

Restart the instance:

shutdown immediate
startup

Rerun the query from step 1. There will be a whole load of rollback segments with names like '_SYSSMU1$'. This is an indictaion that the instance is now using auto undo.

No comments: