Rebuild the rollback tablespace and the rollback tablespace

Rebuild the rollback tablespace and the rollback tablespace

Author: iamlaosong

The data in the rollback segment is not submitted due to unexpected reasons (power loss or manual killing), and a large amount of data retained in the rollback segment cannot be removed. The method I have come up with is to recreate the tablespace. When a large volume of data is read and written in Oracle, if the task is not submitted in time, the rollback tablespace increases rapidly and the rollback tablespace increases continuously, instead of automatically releasing the disk space it occupies (of course, after a few hours, the system will automatically release its own usage, but will not release the disk space it occupies, you can also recreate the tablespace.

1. Rebuild the tablespace rollback method,

Idea: first create a new rollback tablespace, then redirect to the new rollback tablespace, and then delete the original rollback tablespace. If necessary, you can recreate the original tablespace and delete the new tablespace.

-- Create a backup undo tablespace

Create undo tablespace undotbs2 datafile '/opt/app/oracle/oradata/orcl/undotbs02.dbf' size 100 m

-- Switch the undo tablespace so that the system uses the new rollback tablespace.

Alter system set undo_tablespace = undotbs2 scope = spfile

-- Close the service

Shutdown immediate

-- Restart the service

Startup

-- Delete the original hybrid tablespace

Drop tablespace undotbs1 including contents and datafiles;

-- Create the original undo tablespace

Create undo tablespace undotbs1 datafile '/opt/app/oracle/oradata/orcl/undotbs01.dbf' size 1000 m;

-- Switch undo tablespace

Alter system set undo_tablespace = undotbs1 scope = spfile;

-- Disable restart and drop the backup undo tablespace

Shutdown immediate

Startup

Drop tablespace undotbs2 including contents and datafiles;

 

2. the rollback segment cannot be deleted.

 

Symptoms:

The following error is reported when you drop tablespace undotbs1 including contents.

ORA-01548: Activity rollback segment '_ SYSSMU1 $' found to terminate tablespace Deletion

 

Processing Process:

1 create undo tablespace undotbs2 datafile '/opt/app/oracle/oradata/orcl/undotbs02.dbf 'size 100 m;

Alter system set undo_tablespace = undotbs2;

Drop tablespace undotbs1 including contents; (the following error will be reported during this operation ):

ORA-01548: Activity rollback segment '_ SYSSMU1 $' found to terminate tablespace Deletion

 

2. Modify the file/opt/app/oracle/admin/orcl/pfile/init. ora.913201117448 as follows:

Undo_management = manual

Undo_retention = 10800

Undo_tablespace = undotbs2

_ SYSSMU1 $, _ SYSSMU2 $, _ SYSSMU3 $, _ SYSSMU3 $, _ SYSSMU4 $, _ SYSSMU5 $, _ SYSSMU6 $, _ SYSSMU7 $, _ SYSSMU8 $, _ SYSSMU9 $, _ SYSSMU10 $)

3. Start the service

Startup pfile =/opt/app/oracle/admin/orcl/pfile/init. ora.913201117448

4. delete a tablespace

Drop tablespace undotbs1 including contents;

Create undo tablespace undotBS1 datafile '/opt/app/oracle/oradata/orcl/undotbs01.dbf' size 1000 m;

5. Modify init. ora.913201117448 as follows:

Undo_management = auto

Undo_retention = 10800

Undo_tablespace = undotBS1

# _ Incluupted_rollback_segments = (_ SYSSMU1 $, _ SYSSMU2 $, _ SYSSMU3 $, _ SYSSMU3 $, _ SYSSMU4 $, _ SYSSMU5 $, _ SYSSMU6 $, _ SYSSMU7 $, _ SYSSMU8 $, _ SYSSMU9 $, _ SYSSMU10 $)

6 shut down the service shutdown immediate and restart the service with the following command

Startup pfile =/opt/app/oracle/admin/orcl/pfile/init. ora.913201117448

7. Copy the spfile file and back up the original spfile file.

Create spfile = '/opt/app/oracle/product/10.1.0/db_1/dbs/spfileorcl. ora 'from pfile = '/opt/app/oracle/admin/orcl/pfile/init. ora.913201117448'

8. Shut down the server shutdown immediate, restart the server startup, and delete the new tablespace.

Drop tablespace undotbs2 including contents and datafiles;

Select segment_name, status, tablespace_name from dba_rollback_segs;

SEGMENT_NAME STATUS TABLESPACE_NAME
----------------------------------------------------------------------------
SYSTEM ONLINE SYSTEM
_ SYSSMU1 $ ONLINE UNDOTBS1
_ SYSSMU2 $ ONLINE UNDOTBS1
_ SYSSMU3 $ ONLINE UNDOTBS1
_ SYSSMU4 $ ONLINE UNDOTBS1
_ SYSSMU5 $ ONLINE UNDOTBS1
_ SYSSMU6 $ ONLINE UNDOTBS1
_ SYSSMU7 $ ONLINE UNDOTBS1
_ SYSSMU8 $ ONLINE UNDOTBS1
_ SYSSMU9 $ ONLINE UNDOTBS1
_ SYSSMU10 $ ONLINE UNDOTBS1

11 rows selected.