Oracle Datapump - ORA-39014 - ORA-39029 - ORA-31671 - Error

Environment:

- Oracle 19c

- Oracle EL 8.x

Symptom:

- During datapump process error occurs:

ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 17 with process name "DW03" prematurely terminated
ORA-31671: Worker process DW03 had an unhandled exception.
Job "BACKUPUSER"."SYS_EXPORT_FULL_82" stopped due to fatal error

Check alert.log, 

Errors in file /u01/app/oracle/diag/rdbms/ejd/ejd2/trace/ejd2_dw06_2930219.trc  (incident=412638) (PDBNAME=EJDPDB):
ORA-00600: internal error code, arguments: [qosdExpStatRead: expcnt mismatch], [], [], [], [], [], [], [], [], [], [], []
EJDPDB(3):Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/ejd/ejd2/trace/ejd2_dw06_2930219.trc  (incident=412713) (PDBNAME=EJDPDB):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [qosdExpStatRead: expcnt mismatch], [], [], [], [], [], [], [], [], [], [], []
EJDPDB(3):Incident details in: /u01/app/oracle/diag/rdbms/ejd/ejd2/incident/incdir_412713/ejd2_dw06_2930219_i412713.trc
2021-08-11T01:06:32.396154+03:00
EJDPDB(3):*****************************************************************
EJDPDB(3):An internal routine has requested a dump of selected redo.
EJDPDB(3):This usually happens following a specific internal error, when
EJDPDB(3):analysis of the redo logs will help Oracle Support with the
EJDPDB(3):diagnosis.
EJDPDB(3):It is recommended that you retain all the redo logs generated (by
EJDPDB(3):all the instances) during the past 12 hours, in case additional
EJDPDB(3):redo dumps are required to help with the diagnosis.
EJDPDB(3):*****************************************************************
2021-08-11T01:06:35.413583+03:00
EJDPDB(3):Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/ejd/ejd2/trace/ejd2_dw06_2930219.trc  (incident=413737) (PDBNAME=EJDPDB):
ORA-00600: internal error code, arguments: [qosdExpStatRead: expcnt mismatch], [], [], [], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_STATS", line 2141
ORA-06512: at "SYS.DBMS_STATS", line 57587
ORA-06512: at "SYS.DBMS_STATS", line 57820
ORA-06512: at "SYS.KUPW$WORKER", line 30029
ORA-06512: at "SYS.KUPW$WORKER", line 3503
ORA-06512: at "SYS.KUPW$WORKER", line 13746
ORA-06512: at "SYS.KUPW$WORKER", line 2439
ORA-06512: at line 2

Solution:

For common solutions in internet you will see suggestions regarding turning off cost based optimizer.

Not suggested> alter system set "_optimizer_cost_based_transformation"=off scope=both sid='*';

But i prefer not to turn off a settings, instead i prefer solve main problem.

I found solution: https://www.programmersought.com/article/5563668317/

Here;

# sqlplus / as sysdba
SQL> alter session set container=containername;
SQL> With b as ( 
select count(*) cnt,objn,snapshot_id from sys.exp_stat$ es group by objn,snapshot_id) 
select * from sys.exp_obj$ a, b where a.objn=b.objn and a.snapshot_id=b.snapshot_id 
and a.EXP_CNT!=b.CNT;

      OBJN SNAPSHOT_ID    EXP_CNT        CNT       OBJN SNAPSHOT_ID
---------- ----------- ---------- ---------- ---------- -----------
    200146           1          2      65538     200146           1

SQL> update sys.exp_obj$ a set exp_cnt=(select count(*) from sys.exp_stat$ b where
a.objn=b.objn and a.snapshot_id=b.snapshot_id ) where a.objn=200146;   2

2 rows updated.

SQL> With b as (
select count(*) cnt,objn,snapshot_id from sys.exp_stat$ es group by objn,snapshot_id)
select * from sys.exp_obj$ a, b where a.objn=b.objn and a.snapshot_id=b.snapshot_id
and a.EXP_CNT!=b.CNT;

no rows selected

Then datapump is successfully completed.

How to increate imq max thread size?

Symptoms:

After connecting JMS service in glassfish getting error:

JMSException: [C4000]: Packet acknowledge failed

Validate Problem:

Check log file

$glassfishhome/domains/<domainname>/imq/instances/imqbroker/log/log.txt

[06/Jul/2021:10:40:07 EET] WARNING [B3004]: No threads are available to process a new connection on service jms. 1000 threads out of a maximum of 1000 threads are already in use by other connections. A minimum of 2 threads must be available to process the connection. Please either limit the # of connections or increase the imq.<service>.max_threads property. Closing the new connection.

[06/Jul/2021:10:40:08 EET] [B1066]:   Closing: ???@172.16.35.34:0->jms:0 because "[B3004]: No threads are available to process a new connection on service jms. 1000 threads out of a maximum of 1000 threads are already in use by other connections. A minimum of 2 threads must be available to process the connection. Please either limit the # of connections or increase the imq.<service>.max_threads property. Closing the new connection. ". Count: service=0 broker=508

Solution:

Step 1: edit config.properties file in $glassfishhome/domains/<domainname>/imq/instances/imqbroker/props

Step 2: increment max thread count: (default value is 1000, so, use value bigger than 1000)

imq.jms.max_threads=2048

Step 3: restart domain

JAX-WS Reverse Proxy Wrong URL in WSDL (Apache & Nginx)

Environment:

• Payara Application Server (4.x, 5.x or Glassfish)
• Apache 2.4 Http Server as Reverse Proxy
• Reverse Proxy is HTTPS
• Application server is HTTP

• Update as 23.04.2021 - Nginx Reverse Proxy scenerio has been added

Symptoms:

• Wsdl Location Is: https://myapp.mydomain.com/MyApp/MyWebService?WSDL
• JAX-WS generated WSDL shows invalid URL:

<xsd:import namespace="http://myapp.ws.mydomain.com/"schemaLocation="http://myapp.mydomain.com:80/MyApp/MyWebService?xsd=1"/>

<soap:address location="http://myapp.mydomain.com:80/MyApp/MyWebService"/>

• Generated URL is not https which causes bad request error

Solution:

• Set virtual host server name with your domain name in payara (glassfish) admin console.
• Activate mod_substitute for apache.
• In apache virtual conf add Substitution for new url:

<Location "/mergenTechKampusWSIstemci">
    AddOutputFilterByType INFLATE;SUBSTITUTE;DEFLATE text/xml
    Substitute "s|http://myapp.mydomain.com:80/|https://myapp.mydomain.com/|ni"
</Location>

• Restart apache:

• For nginx
• Use substitions : https://www.nginx.com/resources/wiki/modules/substitutions/
• Edit site config

location /mergenTechKampusWSIstemci {

    subs_filter_types text/xml

    subs_filter    http://myapp.mydomain.com:80/ https://myapp.mydomain.com/

}

<xsd:import namespace="http://myapp.ws.mydomain.com/"schemaLocation="https://myapp.mydomain.com/MyApp/MyWebService?xsd=1"/>

<soap:address location="https://myapp.mydomain.com/MyApp/MyWebService"/>