数据泵导入报错ORA-31626 job does not exist
Tags： OraclePROCESSESSESSIONS参数故障处理数据泵
👉 本文共约302个字，系统预计阅读时间或需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> |
最后重新导入正常：
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 | [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:21:19 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 Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_FULL_01": "/******** AS SYSDBA" directory=D1 dumpfile=LHRA_20221027.dmp FULL=Y CLUSTER=N table_exists_action=REPLACE Processing object type SCHEMA_EXPORT/USER ORA-31684: Object type USER:"LHRA" already exists Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "LHRA"."LHRA_REQUEST_MONITOR" 36.38 MB 237735 rows . . imported "LHRA"."CORE_ABC_HIST" 19.01 MB 39224 rows 。。。。。。 . . imported "LHRA"."ZHL1" 0 KB 0 rows . . imported "LHRA"."ZINFO_MESG" 0 KB 0 rows Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY Processing object type SCHEMA_EXPORT/TABLE/TRIGGER Job "SYS"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Thu Oct 27 12:24:14 2022 elapsed 0 00:01:36 |
其它原因
1、参数AQ_TM_PROCESSES应该配置为1
2、需要重新编译数据字典：
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
1 2 | @?/rdbms/admin/catalog.sql @?/rdbms/admin/catproc.sql |
3、SGA或内存太小
总结
请查看高级日志和附加输出日志进行判断该问题的原因。
