数据泵导入报错UDI-03113、UDI-03114和ORA-03113、ORA-03114、ORA-04036参数pga_aggregate_limit太小
Tags： impdpORA-03113ORA-03114OracleUDI-03113UDI-03114数据泵
👉 本文共约547个字，系统预计阅读时间或需3分钟。
现象
Oracle 19c，windows 2016 server环境，数据泵通过network_link直接迁移数据，标准版不能使用并行，200g数据量，跑了8个小时，在快结束的时候报错了：
1 2 3 4 5 6 7 8 9 10 11 | . . imported "P10"."F98ABCDE70628E" 10 rows UDI-03113: operation generated ORACLE error 3113 ORA-03113: end-of-file on communication channel Process ID: 4396 Session ID: 255 Serial number: 49187 UDI-03114: operation generated ORACLE error 3114 ORA-03114: not connected to ORACLE |
分析
attach进去后，可以看到部分错误：
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
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 | C:\Users\lhr>impdp system/"lhr"@127.0.0.1/jdp attach=SYS_IMPORT_FULL_01 Import: Release 19.0.0.0.0 - Production on Sat Dec 10 08:12:37 2022 Version 19.15.0.0.0 Copyright (c) 1982, 2022, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production ORA-31626: job does not exist ORA-31638: cannot attach to job SYS_IMPORT_FULL_01 for user SYSTEM ORA-06512: at "SYS.KUPV$FT", line 402 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: at "SYS.KUPV$FT_INT", line 498 ORA-39077: unable to subscribe agent KUPC$A_1_081239903000000 to queue "KUPC$C_1_20221210000552_0" ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: at "SYS.KUPC$QUE_INT", line 294 ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT ORA-06512: at "SYS.DBMS_AQADM_SYS", line 9306 ORA-06512: at "SYS.DBMS_PRVTAQIS", line 1873 ORA-06512: at "SYS.DBMS_PRVTAQIS", line 77 ORA-06512: at "SYS.DBMS_PRVTAQIS", line 169 ORA-06512: at "SYS.DBMS_PRVTAQIS", line 1724 ORA-06512: at "SYS.DBMS_PRVTAQIS", line 1516 ORA-06512: at "SYS.DBMS_AQADM_SYS", line 9900 ORA-06512: at "SYS.DBMS_AQADM_SYS", line 9269 ORA-06512: at "SYS.DBMS_AQADM", line 881 ORA-06512: at "SYS.KUPC$QUE_INT", line 267 ORA-06512: at "SYS.KUPC$QUE_INT", line 1360 ORA-06512: at line 1 ORA-06512: at "SYS.KUPC$QUEUE_INT", line 65 ORA-06512: at "SYS.KUPV$FT_INT", line 465 ORA-06512: at "SYS.KUPV$FT", line 335 |
查看告警日志：
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 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 | 2022-12-10T07:15:55.627275+08:00 Errors in file D:\APP\diag\rdbms\orcl19c\orcl19c\trace\orcl19c_j000_4628.trc (incident=21009) (PDBNAME=JDE92P): ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT JDE92P(3):Incident details in: D:\APP\diag\rdbms\orcl19c\orcl19c\incident\incdir_21009\orcl19c_j000_4628_i21009.trc 2022-12-10T07:17:39.505785+08:00 JDE92P(3):Resize operation completed for file# 21, fname E:\ORADATA\ORCL19C\JDELHRXX\PY920T01.DBF, old size 21836224K, new size 21880384K JDE92P(3):Resize operation completed for file# 22, fname E:\ORADATA\ORCL19C\JDELHRXX\PY920T02.DBF, old size 13987840K, new size 14049280K 2022-12-10T07:17:43.912072+08:00 Errors in file D:\APP\diag\rdbms\orcl19c\orcl19c\trace\orcl19c_j000_4628.trc (incident=21010) (PDBNAME=JDE92P): ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT JDE92P(3):Incident details in: D:\APP\diag\rdbms\orcl19c\orcl19c\incident\incdir_21010\orcl19c_j000_4628_i21010.trc 2022-12-10T07:17:57.943494+08:00 Errors in file D:\APP\diag\rdbms\orcl19c\orcl19c\trace\orcl19c_j000_4628.trc (incident=21011) (PDBNAME=JDE92P): ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT JDE92P(3):Incident details in: D:\APP\diag\rdbms\orcl19c\orcl19c\incident\incdir_21011\orcl19c_j000_4628_i21011.trc 2022-12-10T07:18:02.849825+08:00 Errors in file D:\APP\diag\rdbms\orcl19c\orcl19c\trace\orcl19c_j000_4628.trc (incident=21012) (PDBNAME=JDE92P): ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT JDE92P(3):Incident details in: D:\APP\diag\rdbms\orcl19c\orcl19c\incident\incdir_21012\orcl19c_j000_4628_i21012.trc 2022-12-10T07:18:19.943776+08:00 Errors in file D:\APP\diag\rdbms\orcl19c\orcl19c\trace\orcl19c_j000_4628.trc (incident=21013) (PDBNAME=JDE92P): ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT JDE92P(3):Incident details in: D:\APP\diag\rdbms\orcl19c\orcl19c\incident\incdir_21013\orcl19c_j000_4628_i21013.trc 2022-12-10T07:19:27.913952+08:00 Errors in file D:\APP\diag\rdbms\orcl19c\orcl19c\trace\orcl19c_j000_4628.trc (incident=21014) (PDBNAME=JDE92P): ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT 2022-12-10T07:19:34.445280+08:00 Errors in file D:\APP\diag\rdbms\orcl19c\orcl19c\trace\orcl19c_j000_4628.trc (incident=21015) (PDBNAME=JDE92P): ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT 2022-12-10T07:19:41.429746+08:00 Errors in file D:\APP\diag\rdbms\orcl19c\orcl19c\trace\orcl19c_j000_4628.trc (incident=21016) (PDBNAME=JDE92P): ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT 2022-12-10T07:19:49.507955+08:00 Unable to allocate memory for new incident error in file D:\APP\diag\rdbms\orcl19c\orcl19c\trace\orcl19c_j000_4628.trc: ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT 2022-12-10T07:19:49.507955+08:00 JDE92P(3):Errors in file D:\APP\diag\rdbms\orcl19c\orcl19c\trace\orcl19c_j000_4628.trc: ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT 2022-12-10T07:19:58.352304+08:00 Unable to allocate memory for new incident error in file D:\APP\diag\rdbms\orcl19c\orcl19c\trace\orcl19c_j000_4628.trc: ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT 2022-12-10T07:19:58.352304+08:00 JDE92P(3):Errors in file D:\APP\diag\rdbms\orcl19c\orcl19c\trace\orcl19c_j000_4628.trc: ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT 2022-12-10T07:20:57.950854+08:00 Unable to allocate memory for new incident error in file D:\APP\diag\rdbms\orcl19c\orcl19c\trace\orcl19c_j000_4628.trc: ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT 2022-12-10T07:20:57.950854+08:00 JDE92P(3):Errors in file D:\APP\diag\rdbms\orcl19c\orcl19c\trace\orcl19c_j000_4628.trc: ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT 2022-12-10T07:21:04.794711+08:00 Unable to allocate memory for new incident error in file D:\APP\diag\rdbms\orcl19c\orcl19c\trace\orcl19c_j000_4628.trc: ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT 2022-12-10T07:21:04.794711+08:00 JDE92P(3):Errors in file D:\APP\diag\rdbms\orcl19c\orcl19c\trace\orcl19c_j000_4628.trc: ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT 2022-12-10T07:21:11.154215+08:00 Unable to allocate memory for new incident error in file D:\APP\diag\rdbms\orcl19c\orcl19c\trace\orcl19c_j000_4628.trc: ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT 2022-12-10T07:21:11.154215+08:00 JDE92P(3):Errors in file D:\APP\diag\rdbms\orcl19c\orcl19c\trace\orcl19c_j000_4628.trc: ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT 2022-12-10T07:21:18.216833+08:00 Unable to allocate memory for new incident error in file D:\APP\diag\rdbms\orcl19c\orcl19c\trace\orcl19c_j000_4628.trc: ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT 2022-12-10T07:21:18.216833+08:00 JDE92P(3):Errors in file D:\APP\diag\rdbms\orcl19c\orcl19c\trace\orcl19c_j000_4628.trc: ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT 2022-12-10T07:21:25.654343+08:00 Unable to allocate memory for new incident error in file D:\APP\diag\rdbms\orcl19c\orcl19c\trace\orcl19c_j000_4628.trc: ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT 2022-12-10T07:21:25.654343+08:00 JDE92P(3):Errors in file D:\APP\diag\rdbms\orcl19c\orcl19c\trace\orcl19c_j000_4628.trc: ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT 2022-12-10T07:21:33.123197+08:00 Unable to allocate memory for new incident error in file D:\APP\diag\rdbms\orcl19c\orcl19c\trace\orcl19c_j000_4628.trc: ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT 2022-12-10T07:21:33.123197+08:00 JDE92P(3):Errors in file D:\APP\diag\rdbms\orcl19c\orcl19c\trace\orcl19c_j000_4628.trc: ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT 2022-12-10T07:21:36.888952+08:00 PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT of 4916 MB (3):KILL SESSION for sid=(257, 41362): (3): Reason = pga_aggregate_limit (3): Mode = KILL HARD SAFE -/-/- (3): Requestor = DBRM (orapid = 11, ospid = 3312, inst = 1) (3): Owner = Process: J000 (orapid = 66, ospid = 4628) (3): Result = ORA-31 2022-12-10T07:21:38.342004+08:00 opidrv aborting process J000 ospid (4628) as a result of ORA-28 2022-12-10T07:21:38.342004+08:00 Errors in file D:\APP\diag\rdbms\orcl19c\orcl19c\trace\orcl19c_j000_4628.trc: ORA-00028: your session has been killed ORA-00028: your session has been killed ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 168 ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 14424 ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 11319 ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 11428 ORA-06512: at "SYS.DBMS_STATS", line 34983 ORA-06512: at "SYS.DBMS_STATS", line 36699 ORA-06512: at "SYS.DBMS_STATS", line 38784 ORA-06512: at "SYS.DBMS_STATS", line 39786 ORA-06512: at "SYS.DBMS_STATS", line 41177 ORA-06512: at "SYS.DBMS_STATS", line 51726 ORA-06512: at "SYS.DBMS_STATS", line 51830 2022-12-10T07:44:00.819865+08:00 Thread 1 cannot allocate new log, sequence 118 Private strand flush not complete Current log# 3 seq# 117 mem# 0: E:\ORADATA\ORCL19C\ONLINELOG\O1_MF_3_KS6K41TM_.LOG Current log# 3 seq# 117 mem# 1: D:\APP\FAST_RECOVERY_AREA\ORCL19C\ONLINELOG\O1_MF_3_KS6K43YZ_.LOG 2022-12-10T07:44:03.897967+08:00 Thread 1 advanced to log sequence 118 (LGWR switch), current SCN: 1895123282 Current log# 4 seq# 118 mem# 0: E:\ORADATA\ORCL19C\ONLINELOG\REDO04_01.LOG Current log# 4 seq# 118 mem# 1: E:\ORADATA\ORCL19C\ONLINELOG\REDO04_02.LOG 2022-12-10T08:07:01.697632+08:00 Errors in file D:\APP\diag\rdbms\orcl19c\orcl19c\trace\orcl19c_ora_4396.trc (incident=20817) (PDBNAME=JDE92P): ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT Errors in file D:\APP\diag\rdbms\orcl19c\orcl19c\trace\orcl19c_ora_4396.trc (incident=20818) (PDBNAME=JDE92P): ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT Errors in file D:\APP\diag\rdbms\orcl19c\orcl19c\trace\orcl19c_ora_4396.trc (incident=20819) (PDBNAME=JDE92P): ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT 2022-12-10T08:07:08.541489+08:00 Errors in file D:\APP\diag\rdbms\orcl19c\orcl19c\trace\orcl19c_ora_4396.trc (incident=20820) (PDBNAME=JDE92P): ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT 2022-12-10T08:07:16.619793+08:00 Errors in file D:\APP\diag\rdbms\orcl19c\orcl19c\trace\orcl19c_ora_4396.trc (incident=20821) (PDBNAME=JDE92P): ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT Errors in file D:\APP\diag\rdbms\orcl19c\orcl19c\trace\orcl19c_ora_4396.trc (incident=20822) (PDBNAME=JDE92P): ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT 2022-12-10T08:07:22.541714+08:00 PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT of 4916 MB (3):KILL SESSION for sid=(255, 49187): (3): Reason = pga_aggregate_limit (3): Mode = KILL HARD SAFE -/-/- (3): Requestor = DBRM (orapid = 11, ospid = 3312, inst = 1) (3): Owner = Process: USER (orapid = 42, ospid = 4396) (3): Result = ORA-0 2022-12-10T08:11:43.855058+08:00 Errors in file D:\APP\diag\rdbms\orcl19c\orcl19c\trace\orcl19c_ora_7572.trc (incident=20823) (PDBNAME=JDE92P): ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT 2022-12-10T08:11:50.027012+08:00 Errors in file D:\APP\diag\rdbms\orcl19c\orcl19c\trace\orcl19c_ora_7572.trc (incident=20824) (PDBNAME=JDE92P): ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT 2022-12-10T08:11:57.058352+08:00 Errors in file D:\APP\diag\rdbms\orcl19c\orcl19c\trace\orcl19c_ora_7572.trc (incident=25609) (PDBNAME=JDE92P): ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT 2022-12-10T08:12:40.794636+08:00 Errors in file D:\APP\diag\rdbms\orcl19c\orcl19c\trace\orcl19c_ora_7548.trc (incident=25610) (PDBNAME=JDE92P): ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT 2022-12-10T08:12:47.513421+08:00 Errors in file D:\APP\diag\rdbms\orcl19c\orcl19c\trace\orcl19c_ora_7548.trc (incident=25611) (PDBNAME=JDE92P): ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT Errors in file D:\APP\diag\rdbms\orcl19c\orcl19c\trace\orcl19c_ora_7548.trc (incident=25612) (PDBNAME=JDE92P): ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT Errors in file D:\APP\diag\rdbms\orcl19c\orcl19c\trace\orcl19c_ora_7548.trc (incident=25613) (PDBNAME=JDE92P): ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT |
都有报错：ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
解决
若OS有足够内存，那么可以把参数pga_aggregate_limit调整大一点：
1 2 3 | ALTER SYSTEM SET pga_aggregate_limit=8G SCOPE=BOTH; |
若OS的内存本身就不足，那么就只能通过其它方式来迁移数据了，例如之前我是全库导出导入的，若是内存不足，那么可以使用schemas进行分批导出导入。
可以使用如下命令生成分批导出的命令：
1 2 3 4 | SELECT 'impdp system/lhr@127.0.0.1/jde DIRECTORY=DATA_PUMP_DIR LOGFILE=impdp_'||D.USERNAME ||'_20221210.log network_link=dbl_12c EXCLUDE=STATISTICS table_exists_action=replace schemas='||D.USERNAME FROM DBA_USERS D WHERE D.USERNAME NOT IN ('SYS','SYSTEM','PUBLIC','MDSYS','TSMSYS','DMSYS','DBSNMP','SCOTT','LHR','LHR2','DB_MONITOR','OUTLN','MGMT_VIEW','FLOWS_FILES','ORDSYS','EXFSYS','WMSYS','APPQOSSYS','APEX_030200','APEX_050000','OWBSYS_AUDIT','ORDDATA','CTXSYS','ANONYMOUS','SYSMAN','XDB','ORDPLUGINS','OWBSYS','SI_INFORMTN_SCHEMA','OLAPSYS','ORACLE_OCM','XS$NULL','BI','PM','MDDATA','IX','SH','DIP','OE','APEX_PUBLIC_USER','HR','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','APEX_040200','DVSYS','LBACSYS','GSMADMIN_INTERNAL','AUDSYS','OJVMSYS','SYS$UMF','GGSYS','DBSFWUSER','DVF','GSMCATUSER','SYSBACKUP','REMOTE_SCHEDULER_AGENT','GSMUSER','SYSRAC','SYSKM','SYSDG','PDBADMIN','WKSYS','GSMROOTUSER') order by username; |
后续
这里有个问题，就是我数据泵已经跑了8个小时了，而且99%的数据都已经入库了，那难道我还得重新从头开始导入吗？答案不是的，我们有办法，就是结合“table_exists_action=SKIP content=metadata_only sqlfile=index.sql”这几个参数来解决，具体请参考：https://www.xmmup.com/impdpshujubengdaorushiyongtable_exists_actionskipcunzaidewentiyijiruhejiezhedaoru.html
