Showing posts with label UNDO management. Show all posts
Showing posts with label UNDO management. Show all posts

Tuesday, January 29, 2019

The difference between REDO and UNDO


UNDO REDO
Used during normal database operations Used during instance and media recovery only
Contains changed column values Contains "change vectors", a description of a change made to a single block
Stored inside the database, in a separate tablespace Stored outside the database, in redo log files
Can be utilized for many purposes Has one purpose only

The redo log files has one and only one purpose in life: instance recovery.
The undo tablespace(s) has one primary purpose in life: to support read-consistency. However, unlike redo, UNDO data can also be utilized for many other purposes, like Oracles flashback technologies.

Oracle 12.2 Documentation about the redo logs can be found here

Thursday, October 19, 2017

How to monitor rollback activity after killing long-running session

Found this brilliant script here: http://www.oracle-wiki.net/startsqlshowrollinfo



prompt This query will monitor rollbacks of transactions after killing a job or session. 
prompt If the used undo records/used undo blocks count is going down, then the transaction is rolling back. 
prompt If they are going up, then the transaction is still running.
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
 
COLUMN username FORMAT A20
COLUMN sid FORMAT 9999
COLUMN serial# FORMAT 99999
 
SELECT s.username,
       s.sid,
       s.serial#,
       t.used_ublk,
       t.used_urec,
       rs.segment_name,
       r.rssize,
       r.status
FROM   v$transaction t,
       v$session s,
       v$rollstat r,
       dba_rollback_segs rs
WHERE  s.saddr = t.ses_addr
AND    t.xidusn = r.usn
AND   rs.segment_id = t.xidusn
ORDER BY t.used_ublk DESC
/
In my environment, the output looked as follows:
This query will monitor rollbacks of transactions after killing a job or session.
If the used undo records/used undo blocks count is going down, then the transaction is rolling back.
If they are going up, then the transaction is still running.

USERNAME               SID SERIAL#  USED_UBLK  USED_UREC SEGMENT_NAME                       RSSIZE STATUS         
-------------------- ----- ------- ---------- ---------- ------------------------------ ---------- ---------------
MYUSERNAME_XX_XXX_  1194   10616     670875   14088690 _SYSSMU22_1415974968$          1189502976 ONLINE

The USED_UBLK was dropping, so the ongoing rollback could be confirmed to the users.

Tuesday, August 9, 2016

How to check for active usage of an UNDO tablespace

SELECT a.name,n.status
FROM   v$rollname a inner join v$rollstat n
USING(usn)
WHERE a.name IN (
                  SELECT segment_name
                  FROM dba_segments
                  WHERE tablespace_name = 'UNDOTBS1'
)
;

NAME       STATUS
---------- ---------------
_SYSSMU8$  PENDING OFFLINE


Status can be either ONLINE, OFFLINE, PENDING OFFLINE or FULL.

* ONLINE means that the undo segments in the UNDO tablespace is being actively used
* OFFLINE means that the undo segments are not accessible for usage
* FULL means that the tablespace in which the undo segments reside has reached its limit, and you will most likely have seen the error

ORA-30036: unable to extend segment by  in undo tablespace 'UNDOTBS1'

at this point

* PENDING OFFLINE means there are still uncommitted transactions depending on the information in the undo segments in this particular UNDO tablespace.