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 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進程的值：

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的解決及分析過程是怎樣的，小編相信有部分知識點可能是我們日常工作會見到或用到的。希望你能通過這篇文章學到更多知識。更多詳情敬請關注億速云行業資訊頻道。