Oracle Restore Tablespace undo writes full disk space to resolve this problem in specific steps



The cause of the problem is mainly the following two points:
1. There is a large transaction volume that allows Oracle undo to scale automatically, resulting in excessive disk space usage;
2. There are larger transactions that do not shrink or do not submit the guided system;
Note: This problem is a relatively normal phenomenon in Oracle system management, the daily maintenance pay more attention to the monitoring of disk space.

Undo Table Space Introduction
The undo tablespace is used to hold undo data, and when DML operations (Insert,update and delete) are performed, Oracle writes the old data for these operations to the undo segment, which is used when managing the undo data before Oracle9i (Rollback Segment). Starting with Oracle9i, managing undo data can not only use rollback segments, but also use the Undo table space. Because planning and managing rollback segments is complex, all Oracle database 10g has been completely discarded with rollback segments. and use the Undo Tablespace to manage the undo data.

1. View system disk Status
AIX system:/> df-g (Linux system: DF-H)
Filesystem GB blocks free%used iused%iused mounted on
/DEV/UNDOLV 30.00 0.00 100% 9 1%/u01/app/u01/app/oracle/undo

2. View the Oracle database table space share
Select A.tablespace_name,
Round ((a.maxbytes/1024/1024), 2) "Sum MB",
Round ((a.bytes/1024/1024), 2) "DataFile MB",
Round (((a.bytes-b.bytes)/1024/1024), 2) "Used MB",
Round (((a.maxbytes-a.bytes+b.bytes)/1024/1024), 2) "Free MB",
Round (((a.bytes-b.bytes)/a.maxbytes) * 2) "percent_used"
From (select Tablespace_name, sum (bytes) bytes,sum (maxbytes) maxbytes
From Dba_data_files where maxbytes!=0
Group by Tablespace_name) A,
(select Tablespace_name, sum (bytes) bytes, max (bytes) Largest
From Dba_free_space
Group BY Tablespace_name) b
where a.tablespace_name = B.tablespace_name
Order by ((a.bytes-b.bytes)/a.maxbytes) desc

Tablespace_name sumdatafile (MB) datafile used free precent_used
1 UNDOTBS1 32767.98 30000 29968 2799.98 91.46

Or, check the database tablespace footprint using the following script:
Select Tablespace_name,sum (bytes)/1024/1024/1024 GB
From Dba_data_files GROUP by Tablespace_name
UNION ALL
Select Tablespace_name,sum (bytes)/1024/1024/1024 GB
From Dba_temp_files GROUP by tablespace_name Order by GB;

3. Find out the path and size of undo table space
Sql> Select file_name,bytes/1024/1024 from Dba_data_files
where tablespace_name like ' UNDOTBS1 '

/U01/APP/ORACLE/UNDO/UNDOTBS01.DBF 30000

4. Check the Undo segment status
Sql> Select Usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks
From V$rollstat order by Rssize;

USN xacts rssize/1024/1024/1024 hwmsize/1024/1024/1024 shrinks
1 0 0 0.000358582 0.000358582 0
2 14 0 0.796791077 0.796791077 735
3 13 0 0.800453186 0.800453186 894
4 12 0 0.805213928 0.805213928 728
5 15 0 1.186126709 1.186126709 922
6 1 0 1.723365784 1.963180542 946
7 3 0 1.732704163 1.977462769 1051
8 5 0 1.978370667 2.228370667 654
9 2 0 2.032501221 2.034454346 707
10 4 0 2.065216064 2.318145752 875
11 11 0 2.100006104 2.100006104 1269
12 8 0 2.630340576 2.700653076 897
13 6 0 2.740814209 2.740814209 1030
14 9 0 2.745697021 2.772064209 1037
15 7 0 2.833526611 2.833526611 1033
16 10 0 3.088363647 3.310592651 989

This restores the 16 rollback objects in the tablespace.

5. Create a new temporary undo table space
You can temporarily create a restore table space on another disk space
Sql>
Create undo Tablespace UNDOTBS2
DataFile '/u01/app/oracle/pub/undotbs02.dbf '
Size 10M autoextend on;

Tablespace created.

6. Switch the undo table space to the new undo table space

Sql> alter system set UNDO_TABLESPACE=UNDOTBS2 Scope=both;
System altered.

7. Verify the Restore table space of the current database
Sql> Show Parameter Undo

NAME TYPE VALUE
------------------------------------ ----------- --------------
Undo_management string AUTO
Undo_retention Integer 900
Undo_tablespace string UNDOTBS2

8. Wait for the original undo table space all undo SEGMENT OFFLINE

Select T.segment_name,t.tablespace_name,t.segment_id,t.status from Dba_rollback_segs t;
Segment_name tablespace_name segment_id STATUS
1 System System 0 ONLINE
2 _syssmu1$ UNDOTBS1 1 OFFLINE
3 _syssmu2$ UNDOTBS1 2 OFFLINE
_syssmu47$ UNDOTBS1 OFFLINE
_syssmu48$ UNDOTBS1 OFFLINE
_syssmu49$ UNDOTBS1 OFFLINE
Wuyi _syssmu50$ UNDOTBS1 OFFLINE
_syssmu51$ UNDOTBS1 Wuyi OFFLINE
_syssmu52$ UNDOTBS1 OFFLINE
_syssmu53$ UNDOTBS1-OFFLINE
_syssmu54$ UNDOTBS1 OFFLINE
_syssmu55$ UNDOTBS1 OFFLINE
_syssmu56$ UNDOTBS1 OFFLINE
_syssmu57$ UNDOTBS1-OFFLINE
UNDOTBS1 _syssmu58$ OFFLINE
_syssmu59$ UNDOTBS1 OFFLINE
_syssmu60$ UNDOTBS1 OFFLINE
_syssmu61$ UNDOTBS1 OFFLINE
_syssmu62$ UNDOTBS2 ONLINE
_syssmu63$ UNDOTBS2 ONLINE
_syssmu64$ UNDOTBS2 ONLINE
_syssmu65$ UNDOTBS2 ONLINE
_syssmu66$ UNDOTBS2-ONLINE
_syssmu67$ UNDOTBS2 ONLINE
_syssmu68$ UNDOTBS2-ONLINE

The corresponding rollback segments for the UNDOTBS1 Restore table space above are offline

9. Delete the original undo table space

Sql> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

10. You can view the system disk space again:
AIX system:/> df-g (Linux system: DF-H)

If you need to standardize the tablespace and path of the database, restore the tablespace name UNDOTBS1 and the path cannot be changed.
You can install just the steps to switch back.
1. Create a new undo table space
You can temporarily create a restore table space on another disk space
Sql>
Create undo Tablespace UNDOTBS1
DataFile '/u01/app/oracle/undo/undotbs01.dbf '
Size 10M autoextend on maxsize 15G;

Start with 10M, set auto scale to 15GB Max

Tablespace created.

2. Switch the undo table space to the new undo table space

Sql> alter system set UNDO_TABLESPACE=UNDOTBS1 Scope=both;
System altered.

3. Verify the Restore table space of the current database
Sql> Show Parameter Undo

NAME TYPE VALUE
------------------------------------ ----------- --------------
Undo_management string AUTO
Undo_retention Integer 900
Undo_tablespace string UNDOTBS1

4. Wait for the original undo table space all undo SEGMENT OFFLINE

Select T.segment_name,t.tablespace_name,t.segment_id,t.status from Dba_rollback_segs t;
Segment_name tablespace_name segment_id STATUS

The corresponding rollback segments for the UNDOTBS2 Restore table space above are offline

5. Delete UNDO2 table space

Sql> drop tablespace UNDOTBS2 including contents and datafiles;

Tablespace dropped.

6. You can view the system disk space again:
AIX system:/> df-g (Linux system: DF-H)

Undo_retention: Specifies the time (in seconds) that undo will be saved after a commit, and the default is 900 seconds in oracle10g.

GUARANTEE: To ensure that the time set by the Undo_retention parameter is valid, this is the new function of 10g.

sql> ALTER tablespace undotbs1 RETENTION GUARANTEE;

sql> ALTER tablespace undotbs1 RETENTION noguarantee;

Without guarantee, Oracle does not guarantee that the undo information can be stored for 900 seconds, and if the undo table space is insufficient, Oracle ignores the Undo_retention setting and overwrites the previous undo directly, There may be ORA-01555 errors at this time. If the Undo table space is sufficient, undo will be saved for a long time until the undo tablespace reaches MaxSize, overwriting the undo information, and Oracle will overwrite the oldest undo information.

Oracle recommends that we set the datafile setting in the Undo table space MaxSize, do not let it extend automatically, if Oracle has the ability to automatically expand, then the old undo will not be overwritten, then the undo table space will become larger and larger, Until you run out of disk space.

With the guarantee guarantee, Oracle will ensure that the undo information is saved to the value set by the undo_retention before it is overwritten, and if this time a lot of things are done and the undo table space is consumed, then that thing will fail. will report ORA-30036 errors, so use guarantee must be cautious, if you want to use guarantee, then try to set the undo table space a little larger.

oracle10g start, if you set Undo_retention to 0, then Oracle enables auto-tuning to meet the needs of the longest running query. Of course, if there is not enough space, Oracle satisfies the maximum allowable long-time queries without requiring the user to adjust manually.

Oracle Restore Tablespace undo writes full disk space to resolve this problem in specific steps