一、背景说明本次需要在 Oracle 19c 环境中将导出的应急系统数据通过 Data Pump 导入到测试库YJXTTEST中实现一次覆盖恢复。目标库信息数据库版本Oracle Database 19c Enterprise Edition 19.25 目标 PDBYJXTTEST 导入方式impdp 导入目录/data/yjxtexpdp 目录对象EXPDP源库导出语句如下nohup expdp expdp/用户expdp的密码yjxt \ dumpfileyjxt_%U.dmp \ logfileyjxt20260506.log \ directoryEXPDP \ fully \ clusterN \ parallel4 \ filesize20G \ logtimeall \ /u02/expdp/yjxt_nohup.out 21 导出文件如下/data/yjxtexpdp/yjxt_01.dmp /data/yjxtexpdp/yjxt_02.dmp /data/yjxtexpdp/yjxt_03.dmp /data/yjxtexpdp/yjxt_04.dmp /data/yjxtexpdp/yjxt_05.dmp /data/yjxtexpdp/yjxt_06.dmp /data/yjxtexpdp/yjxt_07.dmp /data/yjxtexpdp/yjxt_08.dmp由于目标库YJXTTEST原来已有数据本次采用的方式是保留 PDB 保留表空间 清理业务 schema 重新 impdp 导入没有直接删除整个 PDB这样可以减少重建表空间、目录对象、服务名等额外风险。二、导入前准备1. 修改 dump 文件权限chown oracle:oinstall /data/yjxtexpdp/yjxt_*.dmp chmod 750 /data/yjxtexpdp/yjxt_*.dmp确认文件权限ll /data/yjxtexpdp/yjxt_*.dmp2. 确认目标 PDBsqlplus / as sysdba show pdbs;确认目标 PDBYJXTTEST处于READ WRITE状态。3. 确认目录对象alter session set containerYJXTTEST; set lines 200 col directory_name for a20 col directory_path for a60 select directory_name, directory_path from dba_directories where directory_nameEXPDP;结果确认EXPDP /data/yjxtexpdp说明 Data Pump 可以通过directoryEXPDP访问导入文件。4. 确认 EXPDP 用户目录权限select grantee, privilege, table_name from dba_tab_privs where table_nameEXPDP and granteeEXPDP;确认存在READ WRITE三、清理目标库业务 Schema由于是覆盖恢复不能直接 impdp 导入否则容易出现旧数据和新数据混杂。先查看当前非系统用户set lines 200 pages 200 col username for a30 col account_status for a20 col default_tablespace for a25 col created for a20 select username, account_status, default_tablespace, to_char(created,yyyy-mm-dd hh24:mi:ss) created from dba_users where oracle_maintained N order by created;本次识别出的业务用户主要有IPLAT4J YJXT SCYF LFYP ZTFW GEN GGMK PXNL TXYX YJXT_ADMIN保留导入用户EXPDP1. 处理业务连接导入前发现IPLAT4J存在会话连接select inst_id, sid, serial#, username, status, machine, program from gv$session where con_id to_number(sys_context(USERENV,CON_ID)) and username in ( IPLAT4J,YJXT,SCYF,LFYP,ZTFW,GEN,GGMK,PXNL,TXYX ) order by username;为避免应用重连先锁定业务用户alter user IPLAT4J account lock; alter user YJXT account lock; alter user SCYF account lock; alter user LFYP account lock; alter user ZTFW account lock; alter user GEN account lock; alter user GGMK account lock; alter user PXNL account lock; alter user TXYX account lock;然后杀掉已有会话alter system kill session 32,30148,1 immediate; alter system kill session 45,12199,1 immediate; alter system kill session 766,29320,1 immediate; alter system kill session 2295,37094,1 immediate; alter system kill session 791,6362,1 immediate; alter system kill session 792,24348,1 immediate; alter system kill session 1538,45753,1 immediate; alter system kill session 780,41876,1 immediate;2. 删除业务用户drop user IPLAT4J cascade; drop user YJXT cascade; drop user SCYF cascade; drop user LFYP cascade; drop user ZTFW cascade; drop user GEN cascade; drop user GGMK cascade; drop user PXNL cascade; drop user TXYX cascade; drop user YJXT_ADMIN cascade;确认业务用户已清理select username from dba_users where username in ( IPLAT4J,YJXT,SCYF,LFYP,ZTFW,GEN,GGMK,PXNL,TXYX ) order by username;结果no rows selected确认当前非系统用户只剩EXPDPselect username, account_status, default_tablespace from dba_users where oracle_maintained N order by username;结果EXPDP OPEN SYSTEM四、执行 impdp 导入退出 SQL*Plus 后使用 oracle 用户执行导入nohup impdp expdp/expdp123//目标库IP:1521/YJXTTEST \ directoryEXPDP \ dumpfileyjxt_%U.dmp \ logfileyjxttest_imp_20260506.log \ fully \ clustern \ parallel4 \ excludestatistics,tablespace \ logtimeall \ /data/yjxtexpdp/yjxttest_imp_20260506.out 21 查看进程ps -ef | grep impdp | grep -v grep查看日志tail -f /data/yjxtexpdp/yjxttest_imp_20260506.out导入开始后日志正常出现imported TXYX.T_WJGL_FILE_MANAGEMENT_UNIT 8.520 MB 28525 rows imported IPLAT4J.TEWPI02 29.62 MB 129996 rows imported PXNL.T_PXXX_STUDY_USER_RECORD 121.0 MB 714601 rows说明表数据已经开始正常导入。五、中途遇到的问题及处理问题1EXPDP 用户和目录对象已存在日志中出现ORA-31684: Object type USER:EXPDP already exists ORA-31684: Object type DIRECTORY:EXPDP already exists ORA-31684: Object type DIRECTORY:DPUMP_DIR already exists ORA-31684: Object type DIRECTORY:DATA_BACK_DIR already exists ORA-31684: Object type DIRECTORY:IMPORT_DIR already exists原因目标库中保留了 EXPDP 用户和部分目录对象导入时尝试创建这些对象因此提示已存在。处理方式该类错误不影响业务表数据导入可以忽略。问题2DS_ZTFW 表空间不足导入进入挂起等待导入过程中日志出现ORA-39171: Job is experiencing a resumable wait. Resumable error: ORA-01652: unable to extend temp segment by 8192 in tablespace DS_ZTFW Resumable stmt status: SUSPENDED这里虽然提示temp segment但表空间是DS_ZTFW所以需要扩容的是业务表空间DS_ZTFW不是TEMP表空间。查看DS_ZTFW数据文件select file_id, tablespace_name, file_name, round(bytes/1024/1024/1024,2) size_gb, autoextensible, round(maxbytes/1024/1024/1024,2) max_gb from dba_data_files where tablespace_name DS_ZTFW order by file_id;发现已有两个数据文件均已达到 32G 上限。于是新增数据文件。第一次执行alter tablespace DS_ZTFW add datafile /data/oradata/yjxttest/YJCS/xxx/datafile/ds_ztfw_03.dbf size 20G autoextend on next 512M maxsize 32G;报错ORA-03206: maximum file size of (4194304) blocks in AUTOEXTEND clause is out of range原因是 smallfile 表空间下8K block 的单个数据文件最大约为 32G 减 1 个块不能直接写maxsize 32G。改为alter tablespace DS_ZTFW add datafile /data/oradata/yjxttest/YJCS/xxx/datafile/ds_ztfw_03.dbf size 20G autoextend on next 512M maxsize 32767M; alter tablespace DS_ZTFW add datafile /data/oradata/yjxttest/YJCS/xxx/datafile/ds_ztfw_04.dbf size 20G autoextend on next 512M maxsize 32767M; alter tablespace DS_ZTFW add datafile /data/oradata/yjxttest/YJCS/xxx/datafile/ds_ztfw_05.dbf size 20G autoextend on next 512M maxsize 32767M;扩容后查看DS_ZTFW 原有 2 个文件32G 32G 新增 3 个文件20G 20G 20G 当前总大小约 124G同时给ZTFW用户放开表空间配额alter user ZTFW quota unlimited on DS_ZTFW;检查 resumable 状态select u.username, r.session_id, r.status, r.error_number, r.name, substr(r.sql_text,1,120) sql_text from dba_resumable r left join dba_users u on r.user_id u.user_id order by r.session_id;结果显示STATUS NORMAL ERROR_NUMBER 0说明挂起解除Data Pump 自动继续导入。问题四两个外键创建失败导入最后阶段出现ORA-39083: Object type REF_CONSTRAINT:IPLAT4J.ACT_FK_VAR_EXE failed to create with error: ORA-02298: cannot validate (IPLAT4J.ACT_FK_VAR_EXE) - parent keys not found ORA-39083: Object type REF_CONSTRAINT:IPLAT4J.ACT_FK_VAR_BYTEARRAY failed to create with error: ORA-02298: cannot validate (IPLAT4J.ACT_FK_VAR_BYTEARRAY) - parent keys not found对应失败 SQLALTER TABLE IPLAT4J.ACT_RU_VARIABLE ADD CONSTRAINT ACT_FK_VAR_BYTEARRAY FOREIGN KEY (BYTEARRAY_ID_) REFERENCES IPLAT4J.ACT_GE_BYTEARRAY (ID_) ENABLE;原因ACT_RU_VARIABLE 表中存在引用不到父表记录的数据因此外键校验失败。检查约束是否存在select owner, constraint_name, table_name, constraint_type, status, validated from dba_constraints where owner IPLAT4J and constraint_name in (ACT_FK_VAR_EXE,ACT_FK_VAR_BYTEARRAY);结果no rows selected说明这两个外键确实没有创建成功。本次是测试库恢复业务主要关注数据可用性因此这两个外键暂不处理作为遗留项记录。后续如果要求结构完全一致再结合业务确认是否清理孤儿数据或使用ENABLE NOVALIDATE方式补建。六、导入完成结果最终 Data Pump 日志显示Job EXPDP.SYS_IMPORT_FULL_01 completed with 13 error(s)虽然有 13 个错误但主要包括1. EXPDP 用户已存在 2. DIRECTORY 目录对象已存在 3. DS_ZTFW 表空间不足已在线扩容解决 4. IPLAT4J 两个外键因父记录缺失未创建核心业务数据已完成导入。七、导入后检查1. 检查无效对象set lines 200 pages 200 col owner for a20 col object_type for a25 col object_name for a45 select owner, object_type, object_name, status from dba_objects where oracle_maintained N and status VALID order by owner, object_type, object_name;结果no rows selected2. 编译无效对象?/rdbms/admin/utlrp.sql结果OBJECTS WITH ERRORS 0 ERRORS DURING RECOMPILATION 0说明导入后对象状态正常。八、恢复总结本次恢复流程如下1. 准备 dump 文件并设置权限 2. 确认 PDB、目录对象和 EXPDP 权限 3. 清理目标 PDB 中原有业务 schema 4. 使用 impdp fully 方式导入 5. 导入过程中遇到 DS_ZTFW 表空间不足 6. 在线新增数据文件后Data Pump 自动继续 7. 导入最终 completed with 13 errors 8. 检查无效对象为 0编译错误为 0 9. 两个 IPLAT4J 外键因历史孤儿数据未创建测试库场景暂不处理最终结论YJXTTEST 覆盖恢复完成业务 schema 已重新导入数据库对象编译状态正常。导入过程中出现的表空间不足问题已通过在线扩容解决剩余两个外键未创建问题暂作为测试库遗留项记录后续结合业务验证决定是否处理。这次恢复的关键经验是覆盖恢复不要直接盲目 impdp 先清业务 schema再导入 遇到 ORA-39171/ORA-01652 不要中断 Data Pump 在线扩容对应业务表空间即可自动续跑。一句话收尾Data Pump 没那么脆别一报错就重跑看清楚错误类型能在线救的就在线救。