Oracle GoldenGate and threaded_execution

Oracle database version: 12.1.0.2.0
Oracle GoldenGate version: Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO

threaded_execution

What is this parameter all about?

The multi-threaded Oracle model enables Oracle processes on UNIX and Linux to run as operating system threads in separate address spaces. It applies to both BACKGROUND PROCESSES and SERVER PROCESSES. By default, some background processes on UNIX and Linux always use threaded execution; the remaining Oracle processes run as operating system processes. This feature can be helpful to reduce CPU and memory usage because it reduces the number of Oracle processes. It is disabled by default.

In one of our environment, we have enabled this parameter considering this a new 12c feature helping to utilize resources in an efficient way. So setting is as below

SQL> sho parameter threaded

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
threaded_execution                   boolean     TRUE
SQL>

The detailed notes on this parameter or multi-threaded Oracle model can be found in the support note Multi-Threaded Oracle Model : 12c New (Doc ID 1958348.1)

With this setting turned on, I was trying to register an integrated extract on the multi tenant database (which is what we can do as we can't use classic extract if the database is multi tenant) which is throwing the below error.

GGSCI (exdevqadbadm01.wm.cginet as C##GGADMIN@pl360cdb2/CDB$ROOT) 2> register extract eo2dtfsa database container (ptetfsa) 

2017-08-02 12:26:51 ERROR OGG-08221 Cannot register or unregister EXTRACT EO2DTFSA because of the following SQL error: OCI Error 1,031. 

If you look at the error, this states that the error is due to insufficient privileges. The oracle user we use has has all the privileges granted as per Oracle documentation and the user has DBA role in addition as well.

There are many notes regarding the impacts of the threaded_execution setting to TRUE. Below are few examples and you will get a lot of notes related to the parameter

12c: threaded_execution=true Prevents OS Login As Sysdba
How to connect Non-sysdba Account via RMAN, When threaded_execution=TRUE

We have raised a support request with Oracle and they are working on the same.

Things tried as per Oracle's suggestion

Try to register extract with threaded_execution=FALSE and check what happens when flip the setting back.

With the setting changed to FALSE, I was able to register the process and add extract and the parameter flip to TRUE doesn't affect the extract process until I tried to stop the process after testing. 

The process couldn't find it's parent and became a zombie that I couldn't stop the process via GGSCI and the process in due course utilized all the cpu available on the server making the database to hang. I had to kill the process from OS using  "kill -9".

Now after sending all the required information, Oracle has filed a bug against the case 

Bug 26694412 - REGISTER EXTRACT DATABASE FAILS WHEN THREADED_EXECUTION IS TRUE

We are waiting for Oracle's development team to work on this bug. I'll update this post when I'm successfully able to register the extract in the environment. 

Currently the database is running with threaded_execution=FALSE

Happy registering!!! :)

Update - 21 Sep 2017
Oracle has updated the ticket as below before closing

The following enhancement has been filed: 

Bug 26834790 - IE Support for db in THREADED_EXECUTION mode. 

So for now, to have GoldenGate to work with integrated extract one needs to set threaded_execution=FALSE.

Update - 06 Nov 2017
Enhancement  has been done. The fix for the bug is introduced in Oracle GoldenGate version 12.3.0.1.0
Hence if you are running OGG 12.3.0.1.0, then you can have threaded_execution=TRUE