datapump cannot create job

impdp过程中报错信息如下：
ORA-31635: unable to establish job resource synchronization
ORA-06512: at “SYS.DBMS_SUS_ERROR”, line 79
ORA-06512: at “SYS.KUPV$FT_INT”, line 950
ORA-06512: at “SYS.KUPV$FT”, line 1646
ORA-06512: at “SYS.KUPV$FT”, line 1103

ORA-31626: job does not exist
ORA-31637: cannot create job SYS_IMPORT_FULL_02 for user SCMS_BAT
ORA-06512: at “SYS.KUPV$FT”, line 1142
ORA-06512: at “SYS.KUPV$FT”, line 1744
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
ORA-06512: at “SYS.KUPV$FT_INT”, line 1099
ORA-31635: unable to establish job resource synchronization
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 79
ORA-06512: at “SYS.KUPV$FT_INT”, line 950
ORA-06512: at “SYS.KUPV$FT”, line 1646
ORA-06512: at “SYS.KUPV$FT”, line 1103

ORA-31626: job does not exist
ORA-31637: cannot create job SYS_IMPORT_FULL_02 for user SCMS_APY
ORA-06512: at “SYS.KUPV$FT”, line 1142
ORA-06512: at “SYS.KUPV$FT”, line 1744
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
ORA-06512: at “SYS.KUPV$FT_INT”, line 1099
ORA-31635: unable to establish job resource synchronization
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 79
ORA-06512: at “SYS.KUPV$FT_INT”, line 950
ORA-06512: at “SYS.KUPV$FT”, line 1646
ORA-06512: at “SYS.KUPV$FT”, line 1103

###################场景1：未看到qmnc和jnnn进程
ps -ef|egrep ‘qmnc|jnnn’

未看到qmnc和jnnn进程，我们先来看一下官方文档对于这2个后台进程的描述：

QMNC is responsible for facilitating various background activities required by AQ and Oracle Streams: time management of messages, management of nonpersistent queues, cleanup of resources, and so on. QMNC dynamically spawns Qnnn processes as needed for performing these tasks.

Note that if the AQ_TM_PROCESSES initialization parameter is set to 0, this process will not start. The database writes the following message to the alert log: WARNING: AQ_TM_PROCESSES is set to 0. System might be adversely affected.

Qnnn acts as a slave process for QMNC and carry out tasks assigned by QMNC. The number of these processes is dynamically managed by QMNC based on load.

Jnnn:Job slave processes are created or awakened by the job coordinator when it is time for a job to be executed.

Job slaves gather all the metadata required to run the job from the data dictionary. The slave processes start a database session as the owner of the job, execute triggers, and then execute the job. After the job is complete, the slave processes commit and then execute appropriate triggers and close the session. The slave can repeat this operation in case additional jobs need to be run.

可以看到，QMNC负责协助AQ和Oracle Streams所需的各种背景活动：消息的时间管理，非持久性队列的管理，资源清理等。而Jnnn进程是执行作业的子进程，是由作业调度器唤醒的。我们在执行expdp时，作业调度已经完成，但是由于QMNC及Qnnn进程异常，调度作业无法进入消息队列。反过来就是说作业进程在消息队列中查找相关作业时，是查不到的，所以会出现执行expdp时作业不存在的错误信息。根据官网对QMNC的描述，我们先检查AQ_TM_PROCESSES进程的值：

show parameter job
show parameter aq

before 19c
AQ_TM_PROCESSES enables time monitoring of queue messages. The times can be used in messages that specify delay and expiration properties. Values from 1 to 10 specify the number of queue monitor processes created to monitor the messages. If AQ_TM_PROCESSES is not specified or is set to 0, then the queue monitor is not created.

19c

AQ_TM_PROCESSES controls time monitoring on queue messages and controls processing of messages with delay and expiration properties specified.

aq_tm_processes=1, AQ_TM_PROCESSES进程的值是1，也就是说QMNC是异常结束的，不是由于配置而导致的进程未能启动，尝试通过更改AQ_TM_PROCESSES参数的值来唤醒QMNC进程：

alter system set aq_tm_processes = 5 scope = both sid =’*’; #Doc ID 871873.1 & Doc ID 816839.1

ps -ef|egrep ‘qmnc|jnnn’

###################场景2：SGA不足
Doc ID 1924218.1

Increasing the SGA stopped the constant changing of the serial# and allowed for the session to be killed via Note 453338.1.
Once the session was killed, the next run of the Datapump Job did not encounter the errors.

select owner_name,job_name,state from dba_datapump_jobs;
select owner_name,job_name,state from dba_datapump_jobs;

ps -ef| grep <SID> | grep dm
ps -ef| grep <SID> | grep dw

查杀相关job，重启job

###################场景3：对象无效或不存在
Doc ID 563701.1

### conn / as sysdba
### spool support.out
### select owner, object_type, status from dba_objects where object_name = ‘DBMS_AQADM’;
### alter package sys.DBMS_AQADM compile;
### alter package sys.DBMS_AQADM compile body;
###
### — If any ALTER fails, then check the errors:
### select * from dba_errors where name = ‘DBMS_AQADM’ and type = ‘PACKAGE BODY’;
###
### — If it indicates:
### missing : SYS.AQ$_SCHEDULES ;
### missing/invalid package specification : DBMS_PRVTAQIP
###
### — These are both created by catqueue.sql which is run from catproc.sql:
### — Execute catproc.sql:
###
### $ORACLE_HOME/rdbms/admin/catproc.sql
### $ORACLE_HOME//rdbms/admin/utlrp.sql
###
### spool off

alter session set nls_date_format =’yyyy-mm-dd HH24:MI:SS’;
set lines 200 pages 50000
col object_name format a30
col owner format a20
select owner, object_name, object_type,status,last_ddl_time from dba_objects where status = ‘INVALID’;

$ORACLE_HOME//rdbms/admin/utlrp.sql

###################

How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ? (Doc ID 336014.1)