000028149 - KB-1301 How to resolve ORA-30036 UNDO Tablespace error

Document created by RSA Customer Support Employee on Jun 14, 2016Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 2Show Document
  • View in full screen mode

Article Content

Article Number000028149
Applies ToAffected Versions: All Versions
Resolution

This summary information regarding the Oracle Undo Tablespace is available from many online Oracle documentation sites and is repeated here as reference. For detailed specifics, please refer to the official Oracle Documentation.


 


The information presented here assumes the use of an ACM appliance with the default ASM partition. The basic idea behind undo tablespace usage is the same regardless if a local filesystem or ASM partion is used, however, the directory path/device designation will be different. In both cases, the amount of available 'free' space should be ascertained and understood before adding new tablespace extents.
Summary:
Oracle uses the Undo tablespace to store information used to roll back changes to the Oracle Database. Every Oracle Database must have a method of maintaining 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.


 


The amount of space required (and ultimately the filesize of the undo tablespace file(s), depends on how much transaction activity is taking place at any given time for a particular database. The existing Aveksa ACM database is configured to use up to a maximum filesize of 32 GB.


 


Error:


Typically, this is more than enough space, as the undo tablespace is re-used during normal processing. However, an event may occur which requires additional undo space, and if this space is not currently available, an ORA-30036 error may occur. Although this error implies that only 8 additional bytes are required, those 8 bytes were only the initial request. There is no way to determine at this point how much addictional space will be necessary to complete the current transactions. A standard ROT ('Rule of Thumb") guildeline is to increase the current maxsize (in our case, 32GB) by 10-15%, which is approximately 5GB.


 


How to workaround:


Based on our 10%-15% ROT, the recommended command to add an additional 2nd UNDO tablespace data file, is shown below. This command adds a 2nd file to the UNDO tablespace, starting with an initial size of 5GB, incremented as needed by 5GB, up to a max 32GB size. This will make a grand total of 64GB for your UNDO tablespace. Note that these values can be changed for each system, as appropriate. Additional datafiles can also be added from the Oracle OEM UI, but this sql command (executed as sysdba) is more direct.


 


ALTER TABLESPACE "UNDOTBS1" ADD DATAFILE '+DG01' SIZE 5120M AUTOEXTEND ON NEXT 5120M MAXSIZE 32767M;


 


Long Term Consequences:


Once an additional datafile is added to the Undo Tablespace, the ORA-30036 error should not been seen. The actual instance use of the new 'space' can be seen by accessing the Tablespace options from the Oracle OEM UI display. The new 'size' as well as datafile usage can be seen from this UI.


 


Note that once the Undo Tablespace is increased in size, there is really no way to physically 'shrink' it. There is a way to 'functionally' shrink this tablespace, and that is by creating a new, smaller tablespace. Since only 1 tablespace can be active at any one time, the original 'large' file would be disabled and the new 'smaller' file would be enabled, thus effectively making it 'smaller'. Unless current diskspace allocation becomes dysfunctionally small, there is typically no need to change or reduce the file size of the Undo Tablespace datafile(s), unless there were special creation considerations to begin with. If this is a special case, please contact Aveksa Technical Support for guidance.


 


 


 


Attachments

    Outcomes