合 数据泵导入报错UDI-03113、UDI-03114和ORA-03113、ORA-03114、ORA-04036参数pga_aggregate_limit太小
Tags： Oracle数据泵impdpORA-03113ORA-03114UDI-03113UDI-03114
👉 本文共约537个字，系统预计阅读时间或需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进去后，可以看到部分错误：
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
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
