Oracleundo tablespace Switch

Source: Internet
Author: User
Since its birth, Oracle's AUM (AutoUndoManagement) often appears to only expand, not shrink (shrink) (usually we can set enough UNDO tablespace size, however

Since its birth, Oracle's AUM (Auto Undo Management) has often experienced scaling-only and shrink (we can usually set enough UNDO tablespace size, however

Since its birth, Oracle's AUM (Auto Undo Management) has often experienced scaling-only, not shrinking (shrink) (usually we can set enough UNDO tablespace size, and then cancel the auto-scaling attribute). In the actual environment, when the undo tablespace is broken up, alarm messages are continuously sent.

1. Confirm the file

SQL> select file_name, bytes/1024/1024 from dba_data_files 2 where tablespace_name like 'undotbs1 '; FILE_NAME--------------------------------------------------------------------------------BYTES/1024/1024 --------------- + ORADG/danaly/datafile/undotbs1.265.60017387527810


2. Check the UNDO Segment status

SQL> select usn, xacts, rssize/1024/1024/1024, hwmsize/1024/1024/1024, shrinks 2 from v $ rollstat order by rssize; usnxacts rssize/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS ------------ ------------------------- -------------------- ---------- donerows selected.


3. Create a New UNDO tablespace

SQL> create undo tablespace undotbs2; Tablespace created.


4. Switch the UNDO tablespace to the new UNDO tablespace.

SQL> alter system set undo_tablespace = undotbs2 scope = both; System altered.


Used hereSpfileYou must note that you have previously recorded such a case:Oracle diagnosis case-Spfile case 1
5. Wait for all undo segment offline of the original UNDO tablespace

SQL> select usn, xacts, status, rssize/1024/1024/1024, hwmsize/1024/1024/1024, shrinks 2 from v $ rollstat order by rssize; usnxacts statusrssize/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS ---------- ----------------- please wait ---------- 140 then there will be too many requests when there are too many STATUSRSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS ---------- --------------- --------------------- ---------------------- ---------- 60 PENDING OFFLINE2.96719362.9671936012 rows selected.


Let's look at it again:

11:32:11 SQL>/USNXACTS STATUSRSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS ---------- --------------- ---------------------- 151 million rows have been written too many rows have been inserted into rows selected. elapsed: 00:00:00. 00


6. Delete the original UNDO tablespace.

11:34:00 SQL> drop tablespace undotbs1 including contents; Tablespace dropped. Elapsed: 00:00:03. 13

Perform the following operations on your own;

-- Procedure
1. Create an undo tablespace
Create undo tablespace undotbs2 datafile '/dev/vgdata01/rlv_ora_log11 ';
Alter tablespace undotbs2 add datafile '/dev/vgdata01/rlv_ora_log12 ';
Alter tablespace undotbs2 add datafile '/dev/vgdata01/rlv_ora_log13 ';
2. Switch the undo tablespace
Alter system set undo_tablespace = undotbs2 scope = both;
3. Delete the original tablespace
Drop tablespace undotbs1 including contents;
4. Original tablespace and data files:
/Dev/vgdata01/rlv_ora_rbs01 UNDOTBS1

,

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.