Debezium Connector for Oracle fails: ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

[masami310]

Hello,

I am getting the following error in our environment.
「ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT」

Is there any solution other than "Increase PGA memory"?

Thanks

[Log details]
2021-12-14 07:42:36,046 INFO   ||  WorkerSourceTask{id=bakskvs-stg-connector-0} Committing offsets   [org.apache.kafka.connect.runtime.WorkerSourceTask]
2021-12-14 07:42:36,046 INFO   ||  WorkerSourceTask{id=bakskvs-stg-connector-0} flushing 0 outstanding messages for offset commit   [org.apache.kafka.connect.runtime.WorkerSourceTask]
2021-12-14 07:42:51,352 ERROR  Oracle|server1|streaming  Mining session stopped due to the {} [io.debezium.connector.oracle.logminer.LogMinerHelper]
java.sql.SQLException: ORA-00039: error during periodic action
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:628)
        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:562)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1145)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:726)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:291)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:492)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:108)
        at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:887)
        at oracle.jdbc.driver.OracleStatement.prepareDefineBufferAndExecute(OracleStatement.java:1158)
        at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1093)
        at oracle.jdbc.driver.OracleStatement.executeSQLSelect(OracleStatement.java:1402)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1285)
        at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1687)
        at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:394)
        at io.debezium.jdbc.JdbcConnection.queryAndMap(JdbcConnection.java:648)
        at io.debezium.jdbc.JdbcConnection.queryAndMap(JdbcConnection.java:517)
        at io.debezium.connector.oracle.OracleConnection.getCurrentScn(OracleConnection.java:337)
        at io.debezium.connector.oracle.logminer.LogMinerHelper.getEndScn(LogMinerHelper.java:137)
        at io.debezium.connector.oracle.logminer.LogMinerStreamingChangeEventSource.execute(LogMinerStreamingChangeEventSource.java:163)
        at io.debezium.connector.oracle.logminer.LogMinerStreamingChangeEventSource.execute(LogMinerStreamingChangeEventSource.java:63)
        at io.debezium.pipeline.ChangeEventSourceCoordinator.streamEvents(ChangeEventSourceCoordinator.java:160)
        at io.debezium.pipeline.ChangeEventSourceCoordinator.lambda$start$0(ChangeEventSourceCoordinator.java:122)
        at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
        at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
        at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: Error : 39, Position : 2926, Sql = SELECT CURRENT_SCN FROM V$DATABASE, OriginalSql = SELECT CURRENT_SCN FROM V$DATABASE, Error Msg = ORA-00039: error during periodic action
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:632)
        ... 26 more
2021-12-14 07:42:51,352 ERROR  Oracle|server1|streaming  Producer failure   [io.debezium.pipeline.ErrorHandler]
java.sql.SQLException: ORA-00039: error during periodic action
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:628)
        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:562)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1145)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:726)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:291)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:492)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:108)
        at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:887)
        at oracle.jdbc.driver.OracleStatement.prepareDefineBufferAndExecute(OracleStatement.java:1158)
        at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1093)
        at oracle.jdbc.driver.OracleStatement.executeSQLSelect(OracleStatement.java:1402)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1285)
        at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1687)
        at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:394)
        at io.debezium.jdbc.JdbcConnection.queryAndMap(JdbcConnection.java:648)
        at io.debezium.jdbc.JdbcConnection.queryAndMap(JdbcConnection.java:517)
        at io.debezium.connector.oracle.OracleConnection.getCurrentScn(OracleConnection.java:337)
        at io.debezium.connector.oracle.logminer.LogMinerHelper.getEndScn(LogMinerHelper.java:137)
        at io.debezium.connector.oracle.logminer.LogMinerStreamingChangeEventSource.execute(LogMinerStreamingChangeEventSource.java:163)
        at io.debezium.connector.oracle.logminer.LogMinerStreamingChangeEventSource.execute(LogMinerStreamingChangeEventSource.java:63)
        at io.debezium.pipeline.ChangeEventSourceCoordinator.streamEvents(ChangeEventSourceCoordinator.java:160)
        at io.debezium.pipeline.ChangeEventSourceCoordinator.lambda$start$0(ChangeEventSourceCoordinator.java:122)
        at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
        at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
        at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: Error : 39, Position : 2926, Sql = SELECT CURRENT_SCN FROM V$DATABASE, OriginalSql = SELECT CURRENT_SCN FROM V$DATABASE, Error Msg = ORA-00039: error during periodic action
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:632)
        ... 26 more

[Chris Cranford]

Hi

You shouldn't need to increase the PGA memory, but this can be indicative of other problems.  What version of Debezium are you using and what is your connector configuration? 

Chris

--
You received this message because you are subscribed to the Google Groups "debezium" group.
To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/5877d586-bda9-4367-9a4e-261d79ba6eb2n%40googlegroups.com.

[masami310]

Hi, Chris

Thank you for your prompt response.

The version is 1.6.3.Final.
configuration  are as follows.

{

  "name": "xx-connector",
  "config": {
    "connector.class" : "io.debezium.connector.oracle.OracleConnector",
    "tasks.max" : "1",
    "database.server.name" : "server1",
    "database.oracle.version" : "12+",
    "database.user" : "c##DBZUxx",
    "database.password" : "xxx",
    "database.url": "jdbc:oracle:thin:@(DESCRIPTION_LIST=(LOAD_BALANCE=off)(FAILOVER = on)(DESCRIPTION=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=XX)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XX))))",
    "database.dbname" : "xx",
    "database.pdb.name" : "xx",
    "database.connection.adapter" : "logminer",
    "table.include.list": "XX.TABLE001,XX.TABLE003",
    "database.history.kafka.bootstrap.servers" : "b-2.xx:9092,b-1.xx:9092",
    "database.history.kafka.topic": "data-changes.xxx",
    "snapshot.mode": "schema_only",
    "database.history.skip.unparseable.ddl": "true"
  }
}

2021年12月14日火曜日 14:41:20 UTC+9 Chris Cranford:

[masami310]

I checked the memory usage from the time I started debezium until the error occurred.

The results are attached for reference.（dba_hist_active_sess_history.xlsx）

After starting debezium, the usage is gradually increasing.

Thank you for your continued support.

2021年12月14日火曜日 16:02:08 UTC+9 masami310:

[Chris Cranford]

HI

Can you please check with your DBA and see how often the redo logs are switching to archive logs, i.e. switches per hour.  I'm curious if perhaps the database isn't rotating logs too quickly or its rotating them too often.  If you could also ask your DBA what are the number of log groups and their respective configured sizes.

In the case where the logs are switching too infrequent, the PGA_AGGREGATE_LIMIT can be reached because the connector reuses the same session until a log switch occurs.  Normally this is not a problem but I've seen some reports where this has happened in dev/test environments because the database changes so infrequent.  We added SGA/PGA tracking to JMX metrics in 1.7.0.Alpha1 so I would suggest you upgrade to the latest 1.7 and track the SGA/PGA values against log switches to see if this is indeed the case.

The opposite effect is when the connector is down for an extended period of time or the database is tuned poorly and there is an excessive number of archive logs that must be read.  Right now all the logs are loaded to the first mining session and this could lead to a PGA_AGGREGATE_LIMIT.  Under normal operations this shouldn't be the case but its definitely something we should consider guarding against for stability reasons.

Can you confirm which of the two use cases you're facing?

Chris

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/60a21064-b2b8-4098-b27c-d4890032cf46n%40googlegroups.com.

[masami310]

Hi

The redo log file is rotated by three.
One file size is 1GB.

Upgrade to version 1.7 and give it a try.

2021年12月15日水曜日 1:28:41 UTC+9 Chris Cranford:

[masami310]

Hi, Chris

I have an additional question.

1 file size: 1GB * 3 = 3GB
In fact, the memory used is over 6G.

Is there a formula to calculate the PGA memory needed to run Debezium?

2021年12月15日水曜日 10:00:56 UTC+9 masami310:

[masami310]

Hi, Chris

I checked the timing of log switching.

Looking at the past month, There was a gap of about 9 to 14 hours.

Therefore, I think that it applies to "when the log switching frequency is too low".

And I saw the ticket below.

https://issues.redhat.com/browse/DBZ-4204

Is this a valid solution for cases like this one?
Are there any parameters that can be set in Version 1.7.2?

2021年12月15日水曜日 10:19:23 UTC+9 masami310:

[Chris Cranford]

Hi

I'm sure Oracle could provide you one :)

In layperson terms, the memory needed is going to depend entirely on a number of factors such as the number of logs that need to be read, the size of each of the logs, the size of your data dictionary if you aren't using the online catalog strategy, and the duration of the mining session.

The way the user session and mining work is that we do not reset the mining state if a log switch doesn't happen.  This is for performance reasons because doing so would force a reload and re-parse of the data dictionary for users who use the default mining strategy.  This can be very performance impacting and very heavy on database IO so we don't do that.  The idea is that the mining steps build on top of the previous mining step to only load/parse the differences.  Only if a log switch happens do we discard the mining state.

This is why duration of the mining session is critical, which is what I was eluding to below. 

If the same mining session gets re-used constantly then the PGA memory pool will grow indefinitely.  We do report the UGA and PGA usage via JMX metrics so you can monitor this and actively stop and restart the connector if it goes beyond a threshold. 

But again there is the inverse of this where the logs are switch too frequent and the number and size of the logs being mined in one session exhaust the PGA pool.  This is always indicative of a poorly tuned environment or the connector being down for a very extended period of time. 

This is where the DBA's knowledge is going to help to understand which of the two scenarios are you in.

Chris

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/4361c960-4b45-4d22-b960-eac6e0f12308n%40googlegroups.com.

[Chris Cranford]

Hi

I mentioned this in my prior mail, but for now the only solution would be to monitor the JMX metrics and if the PGA usage goes beyond a threshold you would need to automate a stop and restart of the connector.  For now there is no automated way for the connector to do this on its own. 

If you could log a new issue for this that would be great. 

We would also welcome a PR to making this automated via configuration too :P.  I'd propose in such a case to add a new configuration option, perhaps called log.mining.pga.threshold, that is the number of bytes consumed by PGA before a session reset happens.  A session reset in this case is to be treated as a log switch, i.e. the mining session gets stopped and restarted.  You can see this in the Oracle LogMiner streaming source when a log switch is detected.  I would also suggest that this threshold be set to a default of "0" which means that we don't check the threshold and the behavior would be as it is now.

Chris

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/1279c08f-9bbb-4ce4-8e26-13dfb61bbc4bn%40googlegroups.com.

[masami310]

Hi, Chris

Thank you for explaining in detail.
I was able to roughly understand what you explained.

I would like to read and study the source code of debezium.

2022年1月2日日曜日 21:15:15 UTC+9 Chris Cranford:

[Ignatenko, Andrey]

alter system set archive_lag_target=1200 scope=both;




enforses automatic switch every 20 minutes, hence resets PGA 

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/65f31663-1251-43ce-8e1e-f6ea9bc95b54n%40googlegroups.com.

--

Andrey Ignatenko
Staff Engineer
tel: +15102675105 /// mobile:  ///  email: andrey.i...@navis.com

Navis LLC /// 55 Harrison Street, Suite 600 Oakland CA 94607-3776 United States
www.navis.com




CONFIDENTIAL – Information in this email, including any attachments, is confidential, may be legally privileged and may contain proprietary information. If you are not the intended recipient, please immediately notify the sender by reply email and then delete this email message and any attachments. You should not copy, use or disclose to any other person this message or any attachments. Thank you.

[Chris Cranford]

That's a nifty find Andrey; thanks!

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/CAObaRxp43Kn34X1WOcGSv_PQ%2BDBXFTgouEfZdU%2BeG_UHrP88dA%40mail.gmail.com.

[masami310]

thank you.
There is a parameter that can force the log to be switched.

2022年1月6日木曜日 23:19:35 UTC+9 andrey.i...@navis.com: