合 数据泵导入报错ORA-31626 job does not exist
Tags： Oracle故障处理数据泵参数PROCESSESSESSIONS
👉 本文共约314个字，系统预计阅读时间或需2分钟。
现象
数据泵导入报错ORA-31626: job does not exist
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 | [oracle@xtrac1 ~]$ impdp \'/ AS SYSDBA\' directory=D1 dumpfile=LHRA_20221027.dmp FULL=Y CLUSTER=N table_exists_action=REPLACE Import: Release 11.2.0.4.0 - Production on Thu Oct 27 12:18:18 2022 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, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options ORA-31626: job does not exist -- 这里卡了老一会。。。。 [oracle@xtrac1 ~]$ impdp \'/ AS SYSDBA\' directory=D1 dumpfile=LHRA_20221027.dmp FULL=Y CLUSTER=N table_exists_action=REPLACE Import: Release 11.2.0.4.0 - Production on Thu Oct 27 12:16:25 2022 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, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options ORA-31626: job does not exist ORA-31637: cannot create job SYS_IMPORT_FULL_01 for user SYS ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: at "SYS.KUPV$FT", line 1587 ORA-39062: error creating master process DM00 ORA-31613: Master process DM00 failed during startup. [oracle@xtrac1 ~]$ impdp \'/ AS SYSDBA\' directory=D1 dumpfile=LHRA_20221027.dmp FULL=Y CLUSTER=N table_exists_action=REPLACE Import: Release 11.2.0.4.0 - Production on Thu Oct 27 12:15:32 2022 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. UDI-00020: operation generated ORACLE error 20 ORA-00020: maximum number of processes (150) exceeded UDI-00003: all allowable logon attempts failed [oracle@xtrac1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Oct 27 12:13:36 2022 Copyright (c) 1982, 2013, Oracle. All rights reserved. ERROR: ORA-00020: maximum number of processes (150) exceeded Enter user-name: [oracle@xtrac1 ~]$ |
问题分析
从输出信息很明显的可以看到，问题是由于进程数满了导致的。
解决
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | [oracle@xtrac1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Oct 27 12:13:42 2022 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> show parameter session NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ java_max_sessionspace_size integer 0 java_soft_sessionspace_limit integer 0 license_max_sessions integer 0 license_sessions_warning integer 0 session_cached_cursors integer 50 session_max_open_files integer 10 sessions integer 256 shared_server_sessions integer SQL> show parameter proce NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ aq_tm_processes integer 1 cell_offload_processing boolean TRUE db_writer_processes integer 2 gcs_server_processes integer 2 global_txn_processes integer 1 job_queue_processes integer 1000 log_archive_max_processes integer 4 processes integer 150 processor_group_name string SQL> alter system set processes=10000 scope=spfile sid='*'; System altered. SQL> exit |
由于是rac环境，所以分节点进行重启实例后，查看：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 | [oracle@xtrac1 ~]$ sas SQL*Plus: Release 11.2.0.4.0 Production on Thu Oct 27 12:39:57 2022 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 3.1267E+10 bytes Fixed Size 2268592 bytes Variable Size 8120173136 bytes Database Buffers 2.3085E+10 bytes Redo Buffers 58839040 bytes Database mounted. Database opened. SQL> show parameter proce NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ aq_tm_processes integer 1 cell_offload_processing boolean TRUE db_writer_processes integer 2 gcs_server_processes integer 2 global_txn_processes integer 1 job_queue_processes integer 1000 log_archive_max_processes integer 4 processes integer 10000 processor_group_name string SQL> show parameter session NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ java_max_sessionspace_size integer 0 java_soft_sessionspace_limit integer 0 license_max_sessions integer 0 license_sessions_warning integer 0 session_cached_cursors integer 50 session_max_open_files integer 10 sessions integer 15024 shared_server_sessions integer SQL> |
最后重新导入正常：
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
