transaction - ORACLEAGENT BLOG

UNDO TABLESPACE

Undo – Written on Blue Keyboard Key. Male Hand Presses Button on Black PC Keyboard. Closeup View. Blurred Background. 3D Render.

Undo tablespace check undo tablespace:

SQL> show parameter undo_tablespace;

NAME                                    TYPE              VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                        string            UNDOTBS1

Check to undo retention:

SQL> show parameter undo_retention;

NAME                                    TYPE             VALUE
------------------------------------ ----------- ------------------------------
undo_retention                         integer            900

Check to undo management:

SQL> show parameter undo_management;

NAME                                     TYPE                   VALUE
------------------------------------ ----------- ------------------------------
undo_management                          string                  AUTO

create new undo tablespace

SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/ORACLEAGENT/undo2.dbf' size 100m;

Tablespace created.

change undo tablespace

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

SQL> show parameter undo_tablespace;

NAME                                   TYPE               VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                        string            UNDOTBS1

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 1610609200 bytes 
Fixed Size 8897072 bytes
Variable Size 385875968 bytes 
Database Buffers 1207959552 bytes 
Redo Buffers 7876608 bytes
 Database mounted.
Database opened.

SQL> show parameter undo_tablespace;

NAME                                   TYPE             VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                        string           undotbs2

change undo_retention

SQL> show parameter undo_retention;

NAME                                    TYPE              VALUE
------------------------------------ ----------- ------------------------------
undo_retention                         integer             900

SQL> alter system set undo_retention=920;
System altered.

SQL> show parameter undo_retention; 

NAME                                    TYPE           VALUE
------------------------------------ ----------- ------------------------------
undo_retention                        integer           920 

check db block size

SQL> show parameter db_block_size;

NAME                                     TYPE              VALUE
------------------------------------ ----------- ------------------------------
db_block_size                            integer           8192

change undo management { auto | manual }

SQL> show parameter undo_management;
 NAME                                  TYPE               VALUE
------------------------------------ ----------- ------------------------------
undo_management                       string             MANUAL

SQL> alter system set undo_management='auto' scope=spfile; 
System altered.

SQL> shut immediate; 
Database closed.
Database dismounted. 
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 1610609200 bytes 
Fixed Size 8897072 bytes
Variable Size 385875968 bytes 
Database Buffers 1207959552 bytes 
Redo Buffers 7876608 bytes 
Database mounted.
Database opened.

SQL> show parameter undo_management;

NAME                                   TYPE            VALUE
------------------------------------ ----------- ------------------------------
undo_management                        string         AUTO

Undo stages:

SQL> select tablespace_name tablespace, status, sum(bytes)/1024/1024 sum_in_mb, 
count(*) counts from dba_undo_extents group by tablespace_name, status order by 1,2;

TABLESPACE                      STATUS    SUM_IN_MB   COUNTS
------------------------------ --------- ---------- ----------
UNDOTBS1                        EXPIRED      1399.25     200
UNDOTBS1                        UNEXPIRED    168         21
UNDOTBS2                        EXPIRED      .375        6
UNDOTBS2                        UNEXPIRED     5.4375     42

Active, expired and unexpired transaction space usage in Undo Tablespace:-

ACTIVE: Status shows us the active transaction going in the database, utilizing the undo tablespace and cannot be truncated.

EXPIRED: Status shows us the transaction which is completed and complete the undo_retention time and now the first candidate for truncated from undo tablespace.

UNEXPIRED: Status shows us the transaction which is completed but not completed the undo retention time. It can be truncated if required.

Free blocks : At the time of creating new undo tablespace shows empty blocks

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Feb, 2021

Oracle database Health check scripts

PERFOMANCE TUNING SCRIPTS

MONITOR SESSION
SQL PROFILE & BASELINE
ALL IN ONE SCRIPT
HISTORY OF EXECUTED QUERIES
SQL HISTORY
WAIT EVENTS, AWR ,ASH , TIME MODEL SCRIPTS
MONITOR SQL’S

MAIL SCRIPTS

Get DDL ‘s For All objects in oracle database

TEMP TABLESPACE scripts

UNDO TABLESPACE scripts

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

UNDO scripts

v$parameter for undo:

select inst_id,name,value from gv$parameter where name like '%undo%';

UNDO tablespaces Free As per expired segments:

SELECT d.tablespace_name, round(((NVL(f.bytes,0) + (a.maxbytes - a.bytes))/1048576+ u.exp_space),2) as max_free_mb, round(((a.bytes - (NVL(f.bytes,0)+ (10241024u.exp_space)))100/a.maxbytes),2) used_pct FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes, sum(greatest(maxbytes,bytes)) maxbytes from sys.dba_data_files group by tablespace_name) a, (select tablespace_name, sum(bytes) bytes from sys.dba_free_space group by tablespace_name) f , (select tablespace_name , sum(blocks)8/(1024) exp_space from dba_undo_extents where status NOT IN ('ACTIVE','UNEXPIRED') group by tablespace_name) u WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND d.tablespace_name=u.tablespace_name AND d.contents = 'UNDO' AND u.tablespace_name = (select UPPER(value) from v$parameter where name = 'undo_tablespace');

does undo have expired segments?

select status, count(1) from dba_undo_extents group by status;

User Generated UNDO

col sql_text format a40 set lines 130 select sq.sql_text sql_text, t.USED_UREC Records, t.USED_UBLK Blocks, (t.USED_UBLK*8192/1024) KBytes from v$transaction t, v$session s, v$sql sq where t.addr = s.taddr and s.sql_id = sq.sql_id and s.username = '<user>' /

UNDO tablespace usage by USER

SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial, NVL(s.username, 'None') orauser, s.program, r.name undoseg, t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo" FROM sys.v_$rollname r, sys.v_$session s, sys.v_$transaction t, sys.v_$parameter x WHERE s.taddr = t.addr AND r.usn = t.xidusn(+) AND x.name = 'db_block_size'; (or) select username, t.used_ublk ,t.used_urec from gv$transaction t,gv$session s where t.addr=s.taddr and s.sid='&sid';

Recreating the undo tablespace or resolving to undo issue

select a.name,b.status , d.username , d.sid , d.serial# from v$rollname a,v$rollstat b, v$transaction c , v$session d where a.name IN ( select segment_name from dba_segments where tablespace_name = 'UNDOTBS1') and a.usn = b.usn and a.usn = c.xidusn and c.ses_addr = d.saddr;

Ref: https://community.oracle.com/tech/developers/discussion/1098943/create-new-undo-tablespace-and-remove-the-old-one

ORA-30019: Illegal rollback Segment operation in Automatic Undo mode

ALTER SYSTEM SET UNDO_SUPPRESS_ERRORS=TRUE scope=both;

If TRUE means it will not show any undo errors, So some times job may complete successfully.

Find active, expired, unexpired undo

select status, round(sum_bytes / (1024*1024), 0) as MB, round((sum_bytes / undo_size) * 100, 0) as PERC from ( select status, sum(bytes) sum_bytes from dba_undo_extents group by status ), ( select sum(a.bytes) undo_size from dba_tablespaces c join v$tablespace b on b.name = c.tablespace_name join v$datafile a on a.ts# = b.ts# where c.contents = 'UNDO' and c.status = 'ONLINE' ); select count(status) from dba_undo_extents where status = 'EXPIRED'; select count(status) from dba_undo_extents where status = 'UNEXPIRED'; select count(status) from dba_undo_extents where status = 'ACTIVE';

Find active, expired, and unexpired undo by Size in MB

select status,TABLESPACE_NAME,sum(bytes) / 1024 / 1024 || 'MB' from DBA_UNDO_EXTENTS group by status,TABLESPACE_NAME; select status, sum(bytes) / 1024 / 1024 || 'MB' from DBA_UNDO_EXTENTS group by status;

Undo advisor:

SELECT d.undo_size/(10241024) "ACTUAL UNDO SIZE [MByte]", SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]", ROUND((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]" FROM ( SELECT SUM(a.bytes) undo_size FROM v$datafile a, v$tablespace b, dba_tablespaces c WHERE c.contents = 'UNDO' AND c.status = 'ONLINE' AND b.name = c.tablespace_name AND a.ts# = b.ts# ) d, v$parameter e, v$parameter f, ( SELECT MAX(undoblks/((end_time-begin_time)3600*24)) undo_block_per_sec FROM v$undostat ) g WHERE e.name = 'undo_retention' AND f.name = 'db_block_size'; (or) SELECT dbms_undo_adv.required_undo_size(1800, SYSDATE-30, SYSDATE) FROM dual;

Thank you for giving your valuable time to read the above information. If you want to be updated with all our articles send us the Invitation or Follow us: Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/ LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/ Facebook Page: https://www.facebook.com/Oracleagent-344577549964301 Ramkumar’s Twitter : https://twitter.com/ramkuma02877110 Ramkumar’s Telegram: https://t.me/oracleageant Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Tag Archives: transaction