expdp ORA-31626: job does not exist的解决及分析过程是怎样的

本篇文章给大家分享的是有关expdp ORA-31626: job does not exist的解决及分析过程是怎样的，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。
问题描述：在执行数据库备份时报ORA-31626: job does not exist错误，详细错误信息如下所示：
[root@localhost backup]# /home/oracle/backup.sh
Starting bakup…
Bakup file path /backup
Export: Release 11.2.0.4.0 – Production on Mon Oct 23 11:20:37 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
Delete the file bakup before 15 days…
Delete the file bakup successfully.
Bakup completed.
在网上搜索类似错误信息，执行以下sql脚本。
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql执行完之后，错误依旧，所以原因不是升级导致的数据字典信息不一致造成的，从字面理解该错误信息，是job不存在，通过执行备份脚本跟踪发现，执行备份脚本时，会创建相关的job及会话信息，监控告警日志信息如下：
DM00 started with pid=121, OS id=22111, job SYSTEM.SYS_EXPORT_FULL_06所以expdp本身执行过程是没有问题的，结合告警日志中提示：Restarting dead background process QMNC，可能问题出现在qmnc或者jnnn进程上，检查系统数据库后台进程，如下所示：
[oracle@localhost ~]$ ps -ef | grep ora_
oracle 12877 1 0 Oct11 ? 00:01:30 ora_smco_sltwzdb
oracle 27283 1 0 Mar12 ? 00:43:33 ora_pmon_sltwzdb
oracle 27285 1 0 Mar12 ? 00:39:31 ora_psp0_sltwzdb
oracle 27287 1 0 Mar12 ? 00:45:05 ora_vktm_sltwzdb
oracle 27291 1 0 Mar12 ? 00:07:21 ora_gen0_sltwzdb
oracle 27293 1 0 Mar12 ? 00:13:07 ora_diag_sltwzdb
oracle 27295 1 0 Mar12 ? 00:38:05 ora_dbrm_sltwzdb
oracle 27297 1 0 Mar12 ? 16:52:06 ora_dia0_sltwzdb
oracle 27299 1 0 Mar12 ? 00:07:57 ora_mman_sltwzdb
oracle 27301 1 0 Mar12 ? 00:31:36 ora_dbw0_sltwzdb
oracle 27303 1 0 Mar12 ? 01:25:22 ora_lgwr_sltwzdb
oracle 27305 1 0 Mar12 ? 02:08:36 ora_ckpt_sltwzdb
oracle 27307 1 0 Mar12 ? 01:03:43 ora_smon_sltwzdb
oracle 27310 1 0 Mar12 ? 00:02:41 ora_reco_sltwzdb
oracle 27316 1 0 Mar12 ? 00:03:04 ora_d000_sltwzdb
oracle 27318 1 0 Mar12 ? 00:02:56 ora_s000_sltwzdb
oracle 29939 29616 0 11:13 pts/3 00:00:00 grep ora_
未看到有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 Qnnnprocesses as needed for performing these tasks.
Note that if theAQ_TM_PROCESSESinitialization 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.
Qnnnacts 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进程的值：
SQL> show parameter aq
NAME TYPE VALUE
———————————— ———– ——————————
aq_tm_processes integer 1
AQ_TM_PROCESSES进程的值是1，也是说QMNC是异常结束的，不是由于配置而导致的进程未能启动，尝试通过更改AQ_TM_PROCESSES参数的值来唤醒QMNC进程：
SQL> alter system set aq_tm_processes=0;
System altered.
SQL>
SQL> alter system set aq_tm_processes=1;
System altered.
SQL> show parameter process
NAME TYPE VALUE
———————————— ———– ——————————
aq_tm_processes integer 1
通过检查数据库后台进程，QMNC进程依然不存在，所以该问题只能通过重启数据库的方式，将QMNC进程启动起来。
重启完成后，检查数据库进程，如下所示：
oracle 10768 1 0 Oct25 ? 00:00:08 ora_pmon_sltwzdb
oracle 10770 1 0 Oct25 ? 00:00:06 ora_psp0_sltwzdb
oracle 10773 1 0 Oct25 ? 00:00:08 ora_vktm_sltwzdb
oracle 10777 1 0 Oct25 ? 00:00:01 ora_gen0_sltwzdb
oracle 10779 1 0 Oct25 ? 00:00:02 ora_diag_sltwzdb
oracle 10781 1 0 Oct25 ? 00:00:07 ora_dbrm_sltwzdb
oracle 10783 1 0 Oct25 ? 00:02:56 ora_dia0_sltwzdb
oracle 10785 1 0 Oct25 ? 00:00:03 ora_mman_sltwzdb
oracle 10787 1 0 Oct25 ? 00:00:07 ora_dbw0_sltwzdb
oracle 10789 1 0 Oct25 ? 00:00:08 ora_lgwr_sltwzdb
oracle 10791 1 0 Oct25 ? 00:00:24 ora_ckpt_sltwzdb
oracle 10793 1 0 Oct25 ? 00:00:05 ora_smon_sltwzdb
oracle 10795 1 0 Oct25 ? 00:00:00 ora_reco_sltwzdb
oracle 10797 1 0 Oct25 ? 00:03:01 ora_mmon_sltwzdb
oracle 10799 1 0 Oct25 ? 00:02:58 ora_mmnl_sltwzdb
oracle 10801 1 0 Oct25 ? 00:00:00 ora_d000_sltwzdb
oracle 10803 1 0 Oct25 ? 00:00:00 ora_s000_sltwzdb
oracle 10813 1 0 Oct25 ? 00:00:00 ora_qmnc_sltwzdb
oracle 10827 1 0 Oct25 ? 00:00:07 ora_cjq0_sltwzdb
oracle 10855 1 0 Oct25 ? 00:00:00 ora_q000_sltwzdb
oracle 10918 1 0 Oct25 ? 00:00:00 ora_q002_sltwzdb
oracle 10965 1 0 Oct25 ? 00:00:01 ora_smco_sltwzdb
oracle 17790 1 0 02:00 ? 00:00:00 ora_q001_sltwzdb
oracle 26085 1 0 10:38 ? 00:00:00 ora_w000_sltwzdb
可以看到QMNC、Qnnn进程已经正常启动，我们再执行expdp操作：
;;;
Export: Release 11.2.0.4.0 – Production on Wed Oct 23 18:57:37 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “SYSTEM”.”SYS_EXPORT_FULL_07″: system/******** directory=expdp_dir dumpfile=test.dmp logfile=test.log full=y
Estimate in progress using BLOCKS method…
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.014 GB
Processing object type DATABASE_EXPORT/TABLESPACE免费云主机域名
Processing object type DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
……
Expdp执行已不再报ORA-31626: job does not exist，该问题也会导致数据库job无法正常执行。以上就是expdp ORA-31626: job does not exist的解决及分析过程是怎样的，小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注百云行业资讯频道。

相关推荐: PLSQL连接远程库时报错ORA-28040 处理方法是什么

这篇免费云主机域名文章主要讲解了“PLSQL连接远程库时报错ORA-28040 处理方法是什么”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“PLSQL连接远程库时报错ORA-28040 处理方法是什么”吧！使用…