000028149 - How to resolve an ORA-30036 UNDO Tablespace error in RSA Identity Governance & Lifecycle

Document created by RSA Customer Support Employee on Jun 14, 2016Last modified by RSA Customer Support on Sep 5, 2019
Version 6Show Document
  • View in full screen mode

Article Content

Article Number000028149
Applies ToRSA Product Set: Identity Governance & Lifecycle
RSA Product/Service Type: Appliance
RSA Version/Condition: All
IssueWhat can be done to resolve or workaround the below undo tablespace error found in the aveksaServer.log file?
 

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


Please refer to RSA Knowledge Base Article 000030327 - Artifacts to gather in RSA Identity Governance & Lifecycle to find the location of the log files for your specific deployment.
Resolution

This summary information presented in here 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 RSA Identity Governance & Lifecycle hard appliance with the default ASM partition but the concepts apply to a local file system as well. 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 default RSA Identity Governance & Lifecycle database is configured to use up to a maximum filesize of 32 GB.



Error:



Typically, 32 GB 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:
 




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



 Although this error implies that only eight additional bytes are required, those eight bytes are only the initial request. There is no way to determine at this point how much additional space will be necessary to complete the current transactions. A standard Rule of Thumb (ROT) guideline is to increase the current maxsize (in our case 32GB) by 10% to 15%, which is approximately 5GB.



How to Workaround the Error:



Based on our 10% to 15% Rule of Thumb, the recommended command to add an additional second UNDO tablespace data file, is shown below. This command adds a second file to the UNDO tablespace, starting with an initial size of 5GB, incremented as needed by 5GB, up to a maximum size of 32GB. 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 Oracle Enterprise Manager (OEM), but the below SQL command (executed as SYSDBA) is more direct.
 




$ SQLPLUS / AS SYSDBA
SQL> 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 Oracle Enterprise Manager. The new size as well as datafile usage can be seen from the Oracle Enterprise Manager user interface.



Note that once the Undo Tablespace is increased in size, there is really no way to physically shrink it. However, there is a way to functionally shrink this tablespace, and that is by creating a new, smaller tablespace. Since only one tablespace can be active at any one time, the original larger 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 RSA Identity Governance & Lifecycle Support for guidance.



 



 



 


Attachments

    Outcomes