Rebuild the rollback tablespace

Source: Internet
Author: User
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 reading and writing a large volume of data in Oracle, if the task is not submitted in time, the rollback tablespace increases rapidly, and the rollback tablespace increases continuously without automatically releasing it.

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 reading and writing a large volume of data in Oracle, if the task is not submitted in time, the rollback tablespace increases rapidly, and the rollback tablespace increases continuously without automatically releasing it.

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.