How to deal with UNDO space usage alert

-

How to deal with UNDO space usage alert

Oracle Database creates and manages information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo.

Undo records are used to:
  • Roll back transactions when a ROLLBACK statement is issued
  • Recover the database
  • Provide read consistency
  • Analyze data as of an earlier point in time by using Oracle Flashback Query
  • Recover from logical corruptions using Oracle Flashback features
When a ROLLBACK statement is issued, undo records are used to undo changes that were made to the database by the uncommitted transaction. During database recovery, undo records are used to undo any uncommitted changes applied from the redo log to the datafiles. Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it.

Whenever you get an UNDO space usage alert, do not go for immediately increasing the UNDO tablespace size. Check how many expired extents and free extents are left in the undo tablespace. These extents can be reused and hence even though if the undo tablespace shows near 100% utilized, you don’t need to add space to the tablespace as there are segments to get reused. If the sum of (expired extent + free space) is low or you are getting ORA-1555 Snapshot too old error (in the alert log) then you need to think about it and go for increasing space in undo tablespace.

SQL Query for checking UNDO tablespace usage [Free Space]:

set pages 999
col tablespace_name format a40
col "size MB" format 999,999.99
col "free MB" format 999,999.99
col "% Used" format 999.99
select      tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
,     decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
,     decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
               100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% Used"
from  (select tablespace_name, sum(bytes)/1024/1024 used_mb
      from dba_data_files group by tablespace_name union all
      select      tablespace_name || '  **TEMP**'
      ,     sum(bytes)/1024/1024 used_mb
      from dba_temp_files group by tablespace_name) tsu
,     (select tablespace_name, sum(bytes)/1024/1024 free_mb
      from dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
and tsu.tablespace_name = 'APPS_UNDOTS1'
order by 4
/


[Specify the name of Undo Tablespace that you are using.]


SQL Query for checking UNDO extent(s) status [Expired Extents]:

set linesize 200 pagesize 200
select tablespace_name, status segment_status,
count(extent_id) "Extent Count", sum(blocks) "Total Blocks",
sum(blocks)*8/(1024) "Total Space in MB"
from dba_undo_extents
group by tablespace_name, status
order by tablespace_name
/

TABLESPACE_NAME                SEGMENT_S Extent Count Total Blocks Total Space in MB
------------------------------ --------- ------------ ------------ -----------------
APPS_UNDOTS1                   ACTIVE              20         9496           74.1875
APPS_UNDOTS1                   EXPIRED            761       336312         2627.4375
APPS_UNDOTS1                   UNEXPIRED         2513       686448          5362.875

Note:
All the above said are valid only when the undo is in auto extend off.
The best practice is to have it in autoextend off.

Comments

Post a Comment

Back To Top

Popular posts from this blog

error 18 at 0 depth lookup: self signed certificate

-
I was trying to test SSL connection between MySQL client and server. For that I created SSL certificate and keys by following the MySQL documentation at: Creating SSL Certificates and Keys Using openssl After finishing up all the commands when I verify the certificates by using: openssl verify -CAfile ca.pem server-cert.pem client-cert.pem I got the following output: error 18 at 0 depth lookup : self signed certificate error server-cert.pem: verification failed client-cert.pem: OK  Reason : The Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate. Otherwise, the certificate and key files will not work for servers compiled using OpenSSL. Solution : When OpenSSL prompts you for the Common Name for each certificate, use different names. Common Name (e.g. server FQDN or YOUR name) []: I used values as follows: CA Cert:  ssl-ca-cert Server Cert: ssl-ca-server-cert Client Cer...
Read more

How to check fragmentation in MySQL tables

-
MySQL tables, including MyISAM and InnoDB, two of the most common types, experience fragmentation as data is inserted, updated and deleted randomly. Fragmentation can leave large holes in your table, blocks which must be read when scanning the table. Optimizing your table can therefore make full table scans and range scans more efficient. We will mention how to check fragmentation in MySQL tables using SQL queries. USE < put_your_dbname_here > SELECT TABLE_NAME, CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 ), 2), 'M') TOTAL_SIZE, CONCAT(ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2), 'M') DATA_FREE FROM information_schema.TABLES where table_schema = database() and ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2) > 0.00 ORDER BY DATA_FREE DESC; SELECT TABLE_NAME, ROUND(((Data_length - (TABLE_ROWS * Avg_row_length))/Data_length) * 100, 2) FRAG_Percent FROM information_schema.TABLES where table_schema = database() and TABLE_ROWS > 0 ...
Read more

How to Drop or Remove or Decommission a Database in Oracle

-
How to Drop or Remove or Decommission a Database in Oracle Database Decommission steps for Oracle 10G and 11g databases Note: ~~~~~~~~ Every organization has its own documented way of removing or decommissioning the active/running database that is supporting an Enterprise’s Application. Here I am presenting some general steps which may coincide with your organization’s standard operation procedure (SOP). 1)   Please get the required approval from Business/Customer to proceed for database decommissioning. 2)   Send the Notification to Business/Customer that we are going to decommission the database. (if required) 3)   Raise a Request for Storage and Backup team to take the required backup (provide all the details in the raised request) and to keep the Backup for a specified period of time, say for next 2 years, (That is called the backup retention period). Retention Period may be varied based on your business needs and SLA. The backup can...
Read more