000038055 - Enabling the Oracle 12c parameter TEMP_UNDO_ENABLED in RSA Identity Governance & Lifecycle to manage GTT UNDO activity within TEMP tablespace

Document created by RSA Customer Support Employee on Oct 14, 2019
Version 1Show Document
  • Comment • 0
  • View in full screen mode

Article Content

Article Number000038055
Applies ToRSA Product Set: Identity Governance & Lifecycle
RSA Version/Condition: 7.0.x, 7.1.x

 
IssueStarting in Oracle 12c, there is a new database parameter called TEMP_UNDO_ENABLED. RSA Identity Governance & Lifecycle Engineering recommends this parameter be enabled for use with the RSA Identity Governance & Lifecycle application.

The reason for this recommendation is to improve performance and reduce disk space usage. This parameter is used to move the UNDO/REDO activity generated by temporary table activity from the regular UNDO tablespace to TEMP tablespace. This reduces the the time and space needed for flashback recovery, backups, redo logs, and archived redo logging (if enabled) as less data is generated to the UNDO tablespace and to the redo logs.

Because RSA Identity Governance & Lifecycle makes heavy use of temporary tables (particularly GTT or global temporary tables), the application can benefit from enabling this parameter.  

Per the Oracle documentation:
 
"If database applications make use of temporary objects (using global temporary tables or temporary table transformations), it is advisable to set this parameter's value to true."
 


For additional information on this setting, please refer to the Oracle documentation mentioned above.
Resolution

The TEMP_UNDO_ENABLED parameter may be enabled using the alter system command as follows:



  1. Backup the database. Always backup the database before making changes to the database.
  2. Login as SYS user
  3. Check the size of the TEMP tablespace. Ensure that it is at least 96 GB as per the Database Setup and Management Guide.  

NOTES :


  1. If you use a custom TEMP tablespace for AVUSER, note the UNDO transactions logged by the activity on the temporary tables will still be logged to the default database temporary tablespace. Ensure that the default database temporary tablespace is at least 96 GB.
  2. At this time there is no recommendation to reduce the minimum required size of the UNDO tablespace.

  1. Set the parameter:


alter system set temp_undo_enabled=true scope=spfile;


The next time the database is restarted the parameter will be in effect.


  1.  Restart the database as the oracle user for the parameter to take effect.


acm stopdb
acm startdb


  1. Check the parameter setting. Login as SYS user.


show parameter temp_undo_enabled


  1. The output should look similar to:



NAME                       TYPE            VALUE
----------------------------------------------------------------
temp_undo_enabled          boolean          true


 

Attachments

    Outcomes