When running the Datamart Extraction Job using an Oracle Database 12c, the job fails with ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

Document ID : KB000009036
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

We recently began using the Oracle Database 12c with PPM. When we run the Datamart Extraction Job, it fails with this series of error message

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-20000: Error in NBI_EXTRACT_SP - Calling NBI_EXTR_PRTF_SP:
ORA-20000: Error in NBI_EXTR_PRTF_SP - Analyzing NBI_PRT_FACTS: ORA-00039: error during periodic action 
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
ORA-06512: at "NIKU.NBI_EXTRACT_SP", line 375  

The job didn't fail with that type of error message prior to upgrading our database implementation. How can we resolve it?

Environment:
PPM 14.3 and above on Oracle 12c database.
Cause:

In Oracle Database 12c, a parameter is introduced called PGA_AGGREGATE_LIMIT that sets a limit on the amount of PGA memory space that can be used by an instance -- in this case, the run of the Datamart Extraction Job.

When that limit is exceeded, Oracle ends the session occupied by the job which results in the error message, and subsequently, the job fails.

Resolution:
  1. The PGA_AGGREGATE_LIMIT can be set to 0, which removes the limit on PGA usage per session (pre-12c behavior) and so the parameter has no impact on the run of the job

    alter system set pga_aggregate_limit=0 scope=both;

  2. The size of the PGA_AGGREGATE_LIMIT can be increased to a higher value. This should be done only if enough physical memory is allocated to the server

This is a dynamic parameter so the database service does not need to be restarted.

Additional Information: