Shulou(Shulou.com)06/02 Report--

1. Check to see if the dbf file of the database undo tablespace is larger than 25g, and rebuild it if it is larger than us.

-rw-r- 1 oracle oinstall 26G 2011-05-10 10:10 / opt/oracle/oradata/CMCCWAP/undotbs02.dbf

two。 View the default UNDO tablespace of the database (the red part is shown as UNDOTBS2, indicating that the tablespace currently being used is: UNDOTBS2)

SQL > show parameter undo

NAME TYPE VALUE

-

Undo_management string AUTO

Undo_retention integer 900

Undo_tablespace string UNDOTBS2

3. Create a new UNDO space UNDOTB1

SQL > create undo tablespace UNDOTBS1 datafile'/ opt/oracle/oradata/CMCCWAP/undotbs01.dbf' size 200m autoextend on

4. Switch the default tablespace of the database to the newly created tablespace

SQL > alter system set undo_tablespace=UNDOTBS1 scope=both

5. Check whether the default tablespace of the database has been switched to the newly created tablespace. The red part below uses UNDOTBS1, indicating that it has been migrated to the new tablespace.

SQL > show parameter undo

NAME TYPE VALUE

-

Undo_management string AUTO

Undo_retention integer 900

Undo_tablespace string UNDOTBS1

6. Make sure that all undo segment in UNDOTBS2 have been offline. The red part below indicates that they are not yet offline.

SQL > select SEGMENT_NAME, STATUS, TABLESPACE_NAME from dba_rollback_segs

SEGMENT_NAME STATUS TABLESPACE_NAME

-

SYSTEM ONLINE SYSTEM

_ SYSSMU1 $OFFLINE UNDOTBS2

_ SYSSMU2 $OFFLINE UNDOTBS2

_ SYSSMU3 $OFFLINE UNDOTBS2

_ SYSSMU4 $OFFLINE UNDOTBS2

_ SYSSMU5 $OFFLINE UNDOTBS2

_ SYSSMU6 $OFFLINE UNDOTBS2

_ SYSSMU7 $ONLINE UNDOTBS2

_ SYSSMU8 $OFFLINE UNDOTBS2

_ SYSSMU9 $OFFLINE UNDOTBS2

_ SYSSMU10 $OFFLINE UNDOTBS2

SEGMENT_NAME STATUS TABLESPACE_NAME

-

_ SYSSMU11 $OFFLINE UNDOTBS2

_ SYSSMU12 $ONLINE UNDOTBS1

_ SYSSMU13 $ONLINE UNDOTBS1

_ SYSSMU14 $ONLINE UNDOTBS1

_ SYSSMU15 $ONLINE UNDOTBS1

_ SYSSMU16 $ONLINE UNDOTBS1

17 rows selected.

When oracle does not have undo tablespace, it uses the system tablespace as the undo tablespace.

7. When all the above UNDOTBS2 are offline, delete the original UNDO tablespace (if you do not delete it, an error will be reported during backup)

SQL > drop tablespace UNDOTBS2 including contents and datafiles

Drop tablespace undotbs1 including contents and datafiles

*

ERROR at line 1:

ORA-01116: error in opening database file 2

ORA-01110: data file 2:'/ opt/oracle/oradata/CMCCWAP/undotbs02.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

Offline data file 2

SQL > alter database datafile'/ opt/oracle/oradata/CMCCWAP/undotbs02.dbf' offline

Database altered.

Delete

SQL > drop tablespace undotbs1 including contents and datafiles

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.