您好,登錄后才能下訂單哦!
本篇文章給大家分享的是有關(guān)expdp ORA-31626: job does not exist的解決及分析過(guò)程是怎樣的,小編覺(jué)得挺實(shí)用的,因此分享給大家學(xué)習(xí),希望大家閱讀完這篇文章后可以有所收獲,話不多說(shuō),跟著小編一起來(lái)看看吧。
問(wèn)題描述:在執(zhí)行數(shù)據(jù)庫(kù)備份時(shí)報(bào)ORA-31626: job does not exist錯(cuò)誤,詳細(xì)錯(cuò)誤信息如下所示:
[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.
在網(wǎng)上搜索類(lèi)似錯(cuò)誤信息,執(zhí)行以下sql腳本。
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
執(zhí)行完之后,錯(cuò)誤依舊,所以原因不是升級(jí)導(dǎo)致的數(shù)據(jù)字典信息不一致造成的,從字面理解該錯(cuò)誤信息,是job不存在,通過(guò)執(zhí)行備份腳本跟蹤發(fā)現(xiàn),執(zhí)行備份腳本時(shí),會(huì)創(chuàng)建相關(guān)的job及會(huì)話信息,監(jiān)控告警日志信息如下:
DM00 started with pid=121, OS id=22111, job SYSTEM.SYS_EXPORT_FULL_06
所以expdp本身執(zhí)行過(guò)程是沒(méi)有問(wèn)題的,結(jié)合告警日志中提示:Restarting dead background process QMNC,可能問(wèn)題出現(xiàn)在qmnc或者jnnn進(jìn)程上,檢查系統(tǒng)數(shù)據(jù)庫(kù)后臺(tái)進(jìn)程,如下所示:
[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進(jìn)程,我們先來(lái)看一下官方文檔對(duì)于這2個(gè)后臺(tái)進(jìn)程的描述:
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負(fù)責(zé)協(xié)助AQ和Oracle Streams所需的各種背景活動(dòng):消息的時(shí)間管理,非持久性隊(duì)列的管理,資源清理等。而Jnnn進(jìn)程是執(zhí)行作業(yè)的子進(jìn)程,是由作業(yè)調(diào)度器喚醒的。我們?cè)趫?zhí)行expdp時(shí),作業(yè)調(diào)度已經(jīng)完成,但是由于QMNC及Qnnn進(jìn)程異常,調(diào)度作業(yè)無(wú)法進(jìn)入消息隊(duì)列。反過(guò)來(lái)就是說(shuō)作業(yè)進(jìn)程在消息隊(duì)列中查找相關(guān)作業(yè)時(shí),是查不到的,所以會(huì)出現(xiàn)執(zhí)行expdp時(shí)作業(yè)不存在的錯(cuò)誤信息。根據(jù)官網(wǎng)對(duì)QMNC的描述,我們先檢查AQ_TM_PROCESSES進(jìn)程的值:
SQL> show parameter aq
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
AQ_TM_PROCESSES進(jìn)程的值是1,也是說(shuō)QMNC是異常結(jié)束的,不是由于配置而導(dǎo)致的進(jìn)程未能啟動(dòng),嘗試通過(guò)更改AQ_TM_PROCESSES參數(shù)的值來(lái)喚醒QMNC進(jìn)程:
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
通過(guò)檢查數(shù)據(jù)庫(kù)后臺(tái)進(jìn)程,QMNC進(jìn)程依然不存在,所以該問(wèn)題只能通過(guò)重啟數(shù)據(jù)庫(kù)的方式,將QMNC進(jìn)程啟動(dòng)起來(lái)。
重啟完成后,檢查數(shù)據(jù)庫(kù)進(jìn)程,如下所示:
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進(jìn)程已經(jīng)正常啟動(dòng),我們?cè)賵?zhí)行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執(zhí)行已不再報(bào)ORA-31626: job does not exist,該問(wèn)題也會(huì)導(dǎo)致數(shù)據(jù)庫(kù)job無(wú)法正常執(zhí)行。
以上就是expdp ORA-31626: job does not exist的解決及分析過(guò)程是怎樣的,小編相信有部分知識(shí)點(diǎn)可能是我們?nèi)粘9ぷ鲿?huì)見(jiàn)到或用到的。希望你能通過(guò)這篇文章學(xué)到更多知識(shí)。更多詳情敬請(qǐng)關(guān)注億速云行業(yè)資訊頻道。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。