Getting "ORA-01000: maximum open cursors exceeded" running Confluence in Oracle database
プラットフォームについて: Data Center - この記事は、Data Center プラットフォームのアトラシアン製品に適用されます。
このナレッジベース記事は製品の Data Center バージョン用に作成されています。Data Center 固有ではない機能の Data Center ナレッジベースは、製品のサーバー バージョンでも動作する可能性はありますが、テストは行われていません。サーバー*製品のサポートは 2024 年 2 月 15 日に終了しました。サーバー製品を利用している場合は、アトラシアンのサーバー製品のサポート終了のお知らせページにて移行オプションをご確認ください。
*Fisheye および Crucible は除く
要約
Confluence may fail in different situations when executing SQL statements in the Confluence repository (stored in Oracle database), which can lead to functional errors or a performance impact.
環境
Confluence 8.X
診断
In the atlassian-confluence.log
file you will notice multiple errors caused by a similar stack trace:
Caused by: java.sql.SQLException: ORA-01000: maximum open cursors exceeded
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
...
...
You can use the following SQL statement to identify the actual number of Open Cursors configured and the number of cursors taken by Confluence process:
show parameter open_cursors
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid and b.name = 'opened cursors current' and USERNAME='<INSERT-DATABASE-USERNAME-HERE>
原因
For every SQL statement execution, certain area in the memory is allocated. This private SQL memory are is called cursor. These cursors take up space in the shared pool, which is essential memory component of Oracle Database, specifically in the library cache.
To prevent a session from filling up the library cache, the OPEN_CURSORS
database parameter limits this. When reached the max allowed value set for OPEN_CURSORS
, the ORA-01000: maximum open cursors exceeded is triggered in the Oracle side.
ソリューション
As part of our Database Setup for Oracle in Confluence, we do not specify any minimum value for the OPEN_CURSORS
parameter, as Confluence does not work directly with them. Usually the management of cursors is delegated in the JDBC Driver.
Oracle's recommendation is that JDBC driver version should always be either same as or higher than the Oracle database version in order to leverage the latest capabilities of the driver.
Hence, our recommendation is that you upgrade your Oracle JDBC Driver to the latest one available in the Oracle JDBC Downloads page. This might help to fix any already identified JDBC Driver bug that could cause a open cursors leak.
回避策
As an alternative, you may want to increase the maximum open cursor in the database by executing the following SQL command on the Oracle side:
ALTER SYSTEM SET open_cursors = 1000 SCOPE=BOTH;
This example sets the maximum open cursors to 1000. Change this value as required. Please, check with our Oracle DBA before implementing any change in this regard.