现象
Oracle 19c，windows 2016 server环境，数据泵通过network_link直接迁移数据，标准版不能使用并行，200g数据量，跑了8个小时，在快结束的时候报错了：
. . 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进去后，可以看到部分错误：
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查看告警日志：
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调整大一点：
ALTER SYSTEM SET pga_aggregate_limit=8G SCOPE=BOTH;
若OS的内存本身就不足，那么就只能通过其它方式来迁移数据了，例如之前我是全库导出导入的，若是内存不足，那么可以使用schemas进行分批导出导入。
可以使用如下命令生成分批导出的命令：
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
