Overview

This document describes how to replace the Undo Tablespace in Tibero.

Test Environment

• DB: Tibero 7 (DB 7.2.4)
• OS: Rocky Linux 9.6

※ This test document is based on T7; for T6, separate procedures are required.

Method

Undo Tablespace

1.1 Definition 

When performing DML, the original data is called Undo Data, and the segment that stores it is called the Undo Segment.

The tablespace that stores and manages Undo Segments is called the Undo Tablespace.

1.2 Purpose 

• TX Rollback: Used to roll back transactions that have been performed.
• Read Consistency: Creates CR blocks so that data for transactions not yet completed can be accessed in its pre-change state.

2. Undo Tablespace Replacement Test

1. Create a temporary Undo Tablespace
SQL> create undo tablespace new_undo datafile 'new_undo001.dtf' size 1G;
Tablespace 'NEW_UNDO' created.


2. Change and replace the Undo parameter
SQL> alter system set UNDO_TABLESPACE = NEW_UNDO
System altered.
※ For TAC, change on each node / To retain the change after restart, modify the parameter in the environment file (tip file)


3. Check Undo Tablespace change
SQL> select name,value from sys._vt_parameter where name='UNDO_TABLESPACE';
NAME                           VALUE                                             
------------------------------ --------------------------------------------------
UNDO_TABLESPACE                UNDO0


4. Delete Undo Tablespace
 4-1. Check undo tablespace ts id
 SQL> select ts#, name from v$tablespace;
        TS# NAME                          
 ---------- ------------------------------
          0  SYSTEM
          1  UNDO0
          2  TEMP
          3  USR
          4  SYSSUB
          5  NEW_UNDO

 4-2. Check undo segment status
  SQL> select a.name, a.ts#, b.status from v$tablespace a, vt_usgmt_cache b 
  where a.ts# = b.ts_id and a.name in ('UNDO0','NEW_UNDO');
  NAME                                  TS#     STATUS
  ------------------------------ ---------- ----------
  UNDO0                                   1          1
  UNDO0                                   1          1
  UNDO0                                   1          1
  UNDO0                                   1          1
  UNDO0                                   1          1
  UNDO0                                   1          1
  UNDO0                                   1          1
  UNDO0                                   1          1
  UNDO0                                   1          1
  UNDO0                                   1          1
  UNDO0                                   1          1
  NEW_UNDO                                5          0
  NEW_UNDO                                5          0
  NEW_UNDO                                5          0
  NEW_UNDO                                5          0
  NEW_UNDO                                5          0
  NEW_UNDO                                5          0
  NEW_UNDO                                5          0
  NEW_UNDO                                5          0
  NEW_UNDO                                5          0
  NEW_UNDO                                5          0
  NEW_UNDO                                5          0

 4.3. drop Undo Tablespace
  ※ Perform when the above status is confirmed as offline(1)
  SQL> drop tablespace undo0 including contents and datafiles;
  Tablespace 'UNDO1' dropped
  ※ Check undo_retention time; after the retention period, offline deletion is possible

3. Precautions When Changing Undo Tablespace

3.1. For TAC, you must proceed for the Undo of each node.

• If node 1's Undo is Undo1 and node 2's Undo is Undo2, node 1 should proceed with replacement for Undo1, and node 2 should proceed with replacement for Undo2.
• At this time, the number of temporary Undos to be created should match the number of nodes.

3.2 The Undo Tablespace to be deleted must not be using any segments.

The following SQL can be used to check usage.

SELECT vs.sid,
 vs.serial#,
 dr.segment_id,
 DECODE(vst.command_type, 1, 'SELECT'
 , 2, 'INSERT'
 , 3, 'UPDATE'
 , 4, 'DELETE'
 , 5, 'CALL', 0) "SQL Type",
 --vst.sql_id,
 vst.sql_id || '/' || vst.child_number "SQL_ID",
 dr.tablespace_name,
 vt.used_blk,
 vr.curext,
 vr.cursize,
 vr.xacts
FROM dba_rollback_segs dr,
 v$rollstat vr,
 v$transaction vt,
 v$session vs,
 (select distinct command_type, sql_id, child_number from v$sqltext) vst
WHERE dr.segment_id=vr.usn
 and vr.usn=vt.usn
 and vt.sess_id=vs.sid
 and nvl(vs.sql_id, vs.prev_sql_id)=vst.sql_id
 and nvl2(vs.sql_id, vs.sql_child_number, vs.prev_child_number)=vst.child_number;

3.3 After the Undo Retention period has passed, drop the target Undo Tablespace.

How to check the Undo Retention value (unit: seconds)

SQL> show param undo_retention

NAME                                                 TYPE     VALUE
---------------------------------------------------- -------- -------------
UNDO_RETENTION                                       INT32    900

How to check Undo usgmt status

 # status 0: Online, 1:Offline, 2: Offline pending, 3: Dropped
 select us_no, status from sys._dd_usgmt where ts_id=1 union all
 select us_no,status from vt_usgmt_cache where ts_id=1; 
 
     US_NO     STATUS
---------- ----------
         1          1
         2          1
         3          1
         4          1
         5          1
         6          1
         7          1
         8          1
         9          1
        10          1
        11          1
         1          1
         2          1
         3          1
         4          1
         5          1
         6          1
         7          1
         8          1
         9          1
        10          1
        11          1 

3.4 Possible Errors If the Method Is Not Followed

3.4.1 If Undo Segment Is In Use

Unable to drop active undo tablespace. error occurs.

3.4.2 If Undo Retention Time Has Not Passed

TBR-7218: Unable to drop active undo tablespace. error occurs.