Skip to main content

Search

Welcome to Tibero GTS!

Check Rollback Completion Time When DB is Forced to Shut Down

Document Type | Technical Information

Category | Backup/Recovery

Applicable Product Version | 5SP1FS02

Document Number | TBATI027

 

Overview

This guide explains the process of checking the time taken for rollback when a transaction has not been committed and rollback is in progress after shutting down WAS or similar before shutting down the DB.
 
 

Method

If a normal DB down command (normal, post_tx, immediate, switchover) is issued while rollback is in progress, it will wait until the rollback is complete.
 

Checking Completion Time

Case 1. Tibero5sp1 FS02 and later versions

You can find out the completion time for rollback transactions through the EXP_RB_TIME column in V$TRANSACTION. (Unit: seconds)
  • This item does not provide meaningful values for short transactions, but for long rollbacks lasting several minutes to several hours, you can roughly check the required time. However, the expected time may vary depending on the load within the DB.
SQL>  select sess_id, start_time, used_blk, EXP_RB_TIME from  v$transaction;

SESS_ID START_TIME                         USED_BLK EXP_RB_TIME
---------- -------------------------------- ---------- -----------
        33 2016-04-22 19:35:32                   142690          970

1 row selected.

 

Case 2. Versions prior to Tibero5sp1 FS02

If the version is older and the EXP_RB_TIME column does not exist in V$TRANSACTION, you can estimate the approximate completion time by calculating the speed at which the USED_BLK value decreases and the remaining amount.
  • USED_BLK value in V$TRANSACTION: Number of undo blocks used by the transaction
SQL> !date
Fri Apr 22 17:27:22 KST 2016
SQL> select sess_id, start_time, used_blk from  v$transaction;

   SESS_ID START_TIME                         USED_BLK
---------- -------------------------------- ----------
        30 2016-04-22 17:07:18                    7537

1 row selected.

SQL> !date          

Fri Apr 22 17:29:14 KST 2016
SQL> select sess_id, start_time, used_blk from  v$transaction;

   SESS_ID START_TIME                         USED_BLK
---------- -------------------------------- ----------
        30 2016-04-22 17:07:18                    2448

1 row selected.

 

 

 

Related to