Shulou(Shulou.com)05/31 Report--

What this article shares with you is about the process of solving and analyzing expdp ORA-31626: job does not exist. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article. Without saying much, let's take a look at it.

Problem description: ORA-31626: job does not exist error in performing database backup times. The detailed error information is as follows:

[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.

Search the web for similar error messages and execute the following sql script.

@? / rdbms/admin/catalog.sql

@? / rdbms/admin/catproc.sql

After the execution, the error remains the same, so the reason is not caused by the inconsistent data dictionary information caused by the upgrade. Literally, the error message is that job does not exist. It is found by tracking the execution of the backup script that relevant job and session information will be created when the backup script is executed. The monitoring alarm log information is as follows:

DM00 started with pid=121, OS id=22111, job SYSTEM.SYS_EXPORT_FULL_06

Therefore, there is no problem with the execution process of expdp itself. Combined with the prompt in the alarm log: Restarting dead background process QMNC, the problem may occur on the qmnc or jnnn process. Check the background process of the system database, as shown below:

[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 10 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_

We don't see any qmnc and jnnn processes. Let's take a look at the official document description of these two background processes:

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.

As you can see, QMNC is responsible for assisting with the various background activities needed by AQ and Oracle Streams: time management of messages, management of non-persistent queues, resource cleanup, and so on. The Jnnn process is a child process that executes the job and is awakened by the Job Scheduler. When we execute expdp, the job scheduling has been completed, but the scheduling job cannot enter the message queue because of QMNC and Qnnn process exceptions. In turn, when the job process looks up the relevant job in the message queue, it cannot find it, so there will be an error message that the job does not exist when expdp is executed. According to the description of QMNC on the official website, let's first check the value of the AQ_TM_PROCESSES process:

SQL > show parameter aq

NAME TYPE VALUE

-

Aq_tm_processes integer 1

The value of the AQ_TM_PROCESSES process is 1, which means that the QMNC ends abnormally, not because the process failed to start due to configuration. Try to wake up the QMNC process by changing the value of the AQ_TM_PROCESSES parameter:

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

By checking the database background process, the QMNC process still does not exist, so the problem can only start the QMNC process by restarting the database.

After the restart is complete, check the database process, as follows:

Oracle 10768 10 Oct25? 00:00:08 ora_pmon_sltwzdb

Oracle 10770 10 Oct25? 00:00:06 ora_psp0_sltwzdb

Oracle 10773 10 Oct25? 00:00:08 ora_vktm_sltwzdb

Oracle 10777 10 Oct25? 00:00:01 ora_gen0_sltwzdb

Oracle 10779 10 Oct25? 00:00:02 ora_diag_sltwzdb

Oracle 10781 10 Oct25? 00:00:07 ora_dbrm_sltwzdb

Oracle 10783 10 Oct25? 00:02:56 ora_dia0_sltwzdb

Oracle 10785 10 Oct25? 00:00:03 ora_mman_sltwzdb

Oracle 10787 10 Oct25? 00:00:07 ora_dbw0_sltwzdb

Oracle 10789 10 Oct25? 00:00:08 ora_lgwr_sltwzdb

Oracle 10791 10 Oct25? 00:00:24 ora_ckpt_sltwzdb

Oracle 10793 10 Oct25? 00:00:05 ora_smon_sltwzdb

Oracle 10795 10 Oct25? 00:00:00 ora_reco_sltwzdb

Oracle 10797 10 Oct25? 00:03:01 ora_mmon_sltwzdb

Oracle 10799 10 Oct25? 00:02:58 ora_mmnl_sltwzdb

Oracle 10801 10 Oct25? 00:00:00 ora_d000_sltwzdb

Oracle 10803 10 Oct25? 00:00:00 ora_s000_sltwzdb

Oracle 10813 10 Oct25? 00:00:00 ora_qmnc_sltwzdb

Oracle 10827 10 Oct25? 00:00:07 ora_cjq0_sltwzdb

Oracle 10855 10 Oct25? 00:00:00 ora_q000_sltwzdb

Oracle 10918 10 Oct25? 00:00:00 ora_q002_sltwzdb

Oracle 10965 10 Oct25? 00:00:01 ora_smco_sltwzdb

Oracle 17790 1 0 02:00? 00:00:00 ora_q001_sltwzdb

Oracle 26085 10 10:38? 00:00:00 ora_w000_sltwzdb

You can see that the QMNC and Qnnn processes have been started normally, and then we perform the expdp operation:

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 execution is no longer reported to ORA-31626: job does not exist, and this problem can also cause the database job to fail to execute properly.

The above is the process of solving and analyzing expdp ORA-31626: job does not exist. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.