本文档风哥主要介绍Oracle数据泵迁移相关知识，包括数据泵迁移的概念、数据泵迁移组成、数据泵迁移使用、数据泵迁移配置、数据泵迁移监控、数据泵迁移故障处理等内容，由风哥教程参考Oracle官方文档Install and Upgrade内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。
Part01-基础概念与理论知识
1.1 数据泵迁移的概念
Oracle数据泵（Data Pump）是Oracle提供的高速数据迁移工具。数据泵可以快速导出和导入数据库对象，支持并行处理、网络传输、数据过滤等功能。数据泵迁移是常用的数据库迁移方式，特别适合跨平台、跨版本的数据库迁移。更多视频教程www.fgedu.net.cn
- 高速传输：支持并行处理，传输速度快
- 灵活过滤：支持数据过滤和对象过滤
- 网络传输：支持网络直接传输
- 断点续传：支持断点续传功能
- 跨平台迁移：支持跨平台迁移
1.2 数据泵迁移组成
Oracle数据泵迁移组成：
- expdp：数据泵导出工具
- impdp：数据泵导入工具
- 目录对象：存储导出文件的目录
- 参数文件：存储导出导入参数的文件
- 日志文件：记录导出导入过程的日志
1. expdp
– 数据泵导出工具
– 导出数据库对象
– 支持多种导出模式
– 支持并行处理
– 支持数据过滤
2. impdp
– 数据泵导入工具
– 导入数据库对象
– 支持多种导入模式
– 支持并行处理
– 支持对象转换
3. 目录对象
– 存储导出文件的目录
– 必须预先创建
– 必须授权访问
– 支持多个目录
– 支持网络目录
4. 参数文件
– 存储导出导入参数
– 避免命令行参数过长
– 支持注释
– 支持变量
– 便于复用
5. 日志文件
– 记录导出导入过程
– 包含错误信息
– 包含统计信息
– 便于问题排查
– 便于审计
1.3 数据泵迁移使用
Oracle数据泵迁移使用：
- 全库迁移：迁移整个数据库
- 模式迁移：迁移指定模式
- 表迁移：迁移指定表
- 表空间迁移：迁移指定表空间
- 跨平台迁移：跨平台数据库迁移
Part02-生产环境规划与建议
2.1 数据泵迁移规划
Oracle数据泵迁移规划要点：
– 迁移评估：评估迁移的必要性和可行性
– 迁移准备：准备迁移所需资源
– 迁移测试：在测试环境中测试
– 迁移执行：执行迁移过程
# 迁移评估规划
– 数据量评估：评估数据量大小
– 时间评估：评估迁移所需时间
– 资源评估：评估所需资源
– 风险评估：评估迁移风险
# 迁移准备规划
– 目录准备：准备目录对象
– 空间准备：准备存储空间
– 权限准备：准备用户权限
– 网络准备：准备网络环境
# 迁移测试规划
– 测试环境：在测试环境中测试
– 测试数据：使用测试数据
– 测试用例：制定测试用例
– 测试验证：验证迁移结果
# 迁移执行规划
– 迁移时间：选择合适的迁移时间
– 迁移方式：选择合适的迁移方式
– 迁移监控：监控迁移过程
– 迁移验证：验证迁移结果
2.2 数据泵迁移场景
Oracle数据泵迁移场景：
- 全库迁移：迁移整个数据库
- 模式迁移：迁移指定模式
- 表迁移：迁移指定表
- 表空间迁移：迁移指定表空间
- 跨平台迁移：跨平台数据库迁移
1. 全库迁移
– 迁移整个数据库
– 包含所有对象
– 迁移时间较长
– 需要充足空间
– 风险较高
2. 模式迁移
– 迁移指定模式
– 包含模式内所有对象
– 迁移时间适中
– 空间需求适中
– 风险适中
3. 表迁移
– 迁移指定表
– 包含表数据和依赖对象
– 迁移时间较短
– 空间需求较小
– 风险较低
4. 表空间迁移
– 迁移指定表空间
– 包含表空间内所有对象
– 迁移时间适中
– 空间需求适中
– 风险适中
5. 跨平台迁移
– 跨平台数据库迁移
– 如Linux到Windows
– 迁移时间较长
– 需要特别注意
– 风险较高
2.3 数据泵迁移最佳实践
Oracle数据泵迁移最佳实践：
- 充分测试：在测试环境中充分测试
- 备份数据：迁移前备份数据
- 文档记录：记录迁移过程
- 验证数据：验证迁移后数据完整性
- 经验总结：总结迁移经验
Part03-生产环境项目实施方案
3.1 数据泵迁移配置
3.1.1 创建目录对象
$ mkdir -p /backup/datapump
$ chown oracle:oinstall /backup/datapump
$ chmod 755 /backup/datapump
# 2. 创建目录对象
SQL> create directory datapump_dir as ‘/backup/datapump’;
Directory created.
# 3. 授权目录访问权限
SQL> grant read, write on directory datapump_dir to system;
Grant succeeded.
SQL> grant read, write on directory datapump_dir to public;
Grant succeeded.
# 4. 查看目录对象
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
—————————— —————————— ——————————
SYS DATAPUMP_DIR /backup/datapump
SYS DATA_PUMP_DIR /oracle/app/oracle/admin/FGEDUDB/dpdump/
# 5. 验证目录访问
SQL> declare
2 v_file utl_file.file_type;
3 begin
4 v_file := utl_file.fopen(‘DATAPUMP_DIR’, ‘test.txt’, ‘w’);
5 utl_file.put_line(v_file, ‘Test’);
6 utl_file.fclose(v_file);
7 end;
8 /
PL/SQL procedure successfully completed.
$ ls -l /backup/datapump/test.txt
-rw-r–r– 1 oracle oinstall 5 Mar 31 10:00 /backup/datapump/test.txt
3.1.2 执行数据泵导出
$ expdp system/password directory=datapump_dir dumpfile=full_export.dmp logfile=full_export.log full=y
Export: Release 19.0.0.0.0 – Production on Tue Mar 31 10:00:00 2026
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.3.0.0.0 – Production
Starting “SYSTEM”.”SYS_EXPORT_FULL_01″: system/******** directory=datapump_dir dumpfile=full_export.dmp logfile=full_export.log full=y
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . exported “SYS”.”KU$_USER_MAPPING_VIEW” 5.960 KB 12 rows
. . exported “SYSTEM”.”REPCAT$_AUDIT_ATTRIBUTE” 6.273 KB 2 rows
. . exported “SYSTEM”.”REPCAT$_AUDIT_COLUMN” 6.257 KB 2 rows
. . exported “SCOTT”.”EMP” 8.570 KB 14 rows
. . exported “SCOTT”.”DEPT” 6.031 KB 4 rows
Master table “SYSTEM”.”SYS_EXPORT_FULL_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
/backup/datapump/full_export.dmp
Job “SYSTEM”.”SYS_EXPORT_FULL_01″ successfully completed at Tue Mar 31 10:05:00 2026 elapsed 0 00:05:00
# 2. 模式导出
$ expdp system/password directory=datapump_dir dumpfile=scott_export.dmp logfile=scott_export.log schemas=scott
Export: Release 19.0.0.0.0 – Production on Tue Mar 31 10:05:00 2026
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.3.0.0.0 – Production
Starting “SYSTEM”.”SYS_EXPORT_SCHEMA_01″: system/******** directory=datapump_dir dumpfile=scott_export.dmp logfile=scott_export.log schemas=scott
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . exported “SCOTT”.”EMP” 8.570 KB 14 rows
. . exported “SCOTT”.”DEPT” 6.031 KB 4 rows
. . exported “SCOTT”.”SALGRADE” 5.960 KB 5 rows
. . exported “SCOTT”.”BONUS” 5.960 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Master table “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/backup/datapump/scott_export.dmp
Job “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully completed at Tue Mar 31 10:06:00 2026 elapsed 0 00:01:00
# 3. 表导出
$ expdp system/password directory=datapump_dir dumpfile=table_export.dmp logfile=table_export.log tables=scott.emp,scott.dept
Export: Release 19.0.0.0.0 – Production on Tue Mar 31 10:06:00 2026
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.3.0.0.0 – Production
Starting “SYSTEM”.”SYS_EXPORT_TABLE_01″: system/******** directory=datapump_dir dumpfile=table_export.dmp logfile=table_export.log tables=scott.emp,scott.dept
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . exported “SCOTT”.”EMP” 8.570 KB 14 rows
. . exported “SCOTT”.”DEPT” 6.031 KB 4 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Master table “SYSTEM”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/backup/datapump/table_export.dmp
Job “SYSTEM”.”SYS_EXPORT_TABLE_01″ successfully completed at Tue Mar 31 10:07:00 2026 elapsed 0 00:01:00
# 4. 并行导出
$ expdp system/password directory=datapump_dir dumpfile=parallel_export_%U.dmp logfile=parallel_export.log schemas=scott parallel=4
Export: Release 19.0.0.0.0 – Production on Tue Mar 31 10:07:00 2026
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.3.0.0.0 – Production
Starting “SYSTEM”.”SYS_EXPORT_SCHEMA_01″: system/******** directory=datapump_dir dumpfile=parallel_export_%U.dmp logfile=parallel_export.log schemas=scott parallel=4
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . exported “SCOTT”.”EMP” 8.570 KB 14 rows
. . exported “SCOTT”.”DEPT” 6.031 KB 4 rows
. . exported “SCOTT”.”SALGRADE” 5.960 KB 5 rows
. . exported “SCOTT”.”BONUS” 5.960 KB 0 rows
Master table “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/backup/datapump/parallel_export_01.dmp
/backup/datapump/parallel_export_02.dmp
/backup/datapump/parallel_export_03.dmp
/backup/datapump/parallel_export_04.dmp
Job “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully completed at Tue Mar 31 10:08:00 2026 elapsed 0 00:01:00
3.1.3 执行数据泵导入
$ impdp system/password directory=datapump_dir dumpfile=full_export.dmp logfile=full_import.log full=y
Import: Release 19.0.0.0.0 – Production on Tue Mar 31 10:10:00 2026
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.3.0.0.0 – Production
Master table “SYSTEM”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “SYSTEM”.”SYS_IMPORT_FULL_01″: system/******** directory=datapump_dir dumpfile=full_export.dmp logfile=full_import.log full=y
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported “SYS”.”KU$_USER_MAPPING_VIEW” 5.960 KB 12 rows
. . imported “SYSTEM”.”REPCAT$_AUDIT_ATTRIBUTE” 6.273 KB 2 rows
. . imported “SYSTEM”.”REPCAT$_AUDIT_COLUMN” 6.257 KB 2 rows
. . imported “SCOTT”.”EMP” 8.570 KB 14 rows
. . imported “SCOTT”.”DEPT” 6.031 KB 4 rows
Job “SYSTEM”.”SYS_IMPORT_FULL_01″ successfully completed at Tue Mar 31 10:15:00 2026 elapsed 0 00:05:00
# 2. 模式导入
$ impdp system/password directory=datapump_dir dumpfile=scott_export.dmp logfile=scott_import.log schemas=scott
Import: Release 19.0.0.0.0 – Production on Tue Mar 31 10:15:00 2026
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.3.0.0.0 – Production
Master table “SYSTEM”.”SYS_IMPORT_SCHEMA_01″ successfully loaded/unloaded
Starting “SYSTEM”.”SYS_IMPORT_SCHEMA_01″: system/******** directory=datapump_dir dumpfile=scott_export.dmp logfile=scott_import.log schemas=scott
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported “SCOTT”.”EMP” 8.570 KB 14 rows
. . imported “SCOTT”.”DEPT” 6.031 KB 4 rows
. . imported “SCOTT”.”SALGRADE” 5.960 KB 5 rows
. . imported “SCOTT”.”BONUS” 5.960 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job “SYSTEM”.”SYS_IMPORT_SCHEMA_01″ successfully completed at Tue Mar 31 10:16:00 2026 elapsed 0 00:01:00
# 3. 表导入
$ impdp system/password directory=datapump_dir dumpfile=table_export.dmp logfile=table_import.log tables=scott.emp,scott.dept
Import: Release 19.0.0.0.0 – Production on Tue Mar 31 10:16:00 2026
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.3.0.0.0 – Production
Master table “SYSTEM”.”SYS_IMPORT_TABLE_01″ successfully loaded/unloaded
Starting “SYSTEM”.”SYS_IMPORT_TABLE_01″: system/******** directory=datapump_dir dumpfile=table_export.dmp logfile=table_import.log tables=scott.emp,scott.dept
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “SCOTT”.”EMP” 8.570 KB 14 rows
. . imported “SCOTT”.”DEPT” 6.031 KB 4 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job “SYSTEM”.”SYS_IMPORT_TABLE_01″ successfully completed at Tue Mar 31 10:17:00 2026 elapsed 0 00:01:00
# 4. 模式重映射
$ impdp system/password directory=datapump_dir dumpfile=scott_export.dmp logfile=remap_import.log remap_schema=scott:hr
Import: Release 19.0.0.0.0 – Production on Tue Mar 31 10:17:00 2026
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.3.0.0.0 – Production
Master table “SYSTEM”.”SYS_IMPORT_SCHEMA_01″ successfully loaded/unloaded
Starting “SYSTEM”.”SYS_IMPORT_SCHEMA_01″: system/******** directory=datapump_dir dumpfile=scott_export.dmp logfile=remap_import.log remap_schema=scott:hr
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported “HR”.”EMP” 8.570 KB 14 rows
. . imported “HR”.”DEPT” 6.031 KB 4 rows
. . imported “HR”.”SALGRADE” 5.960 KB 5 rows
. . imported “HR”.”BONUS” 5.960 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job “SYSTEM”.”SYS_IMPORT_SCHEMA_01″ successfully completed at Tue Mar 31 10:18:00 2026 elapsed 0 00:01:00
3.2 数据泵迁移监控
3.2.1 配置数据泵迁移监控脚本
$ vi /home/oracle/scripts/monitor_datapump.sh
#!/bin/bash
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# 数据泵迁移监控脚本
export ORACLE_HOME=/oracle/app/oracle/product/19c/dbhome_1
export ORACLE_SID=FGEDUDB
export PATH=$ORACLE_HOME/bin:$PATH
LOG_DIR=”/home/oracle/datapump_monitor”
mkdir -p $LOG_DIR
DATE=$(date +%Y%m%d)
LOG_FILE=”$LOG_DIR/datapump_monitor_$DATE.log”
echo “$(date): Starting datapump monitor…” > $LOG_FILE
# 检查数据泵作业
sqlplus -s / as sysdba <
set linesize 200
set pagesize 100
— 检查数据泵作业
select owner_name, job_name, operation, job_mode, state, degree
from dba_datapump_jobs;
— 检查数据泵会话
select sid, serial#, username, program, status
from v\$session
where program like ‘%expdp%’ or program like ‘%impdp%’;
exit
EOF
echo “$(date): Datapump monitor completed.” >> $LOG_FILE
# 发送报告
mail -s “Datapump Monitor Report – $DATE” admin@fgedu.net.cn < $LOG_FILE
# 2. 设置脚本权限
$ chmod +x /home/oracle/scripts/monitor_datapump.sh
# 3. 测试脚本
$ /home/oracle/scripts/monitor_datapump.sh
# 4. 设置定期监控
$ crontab -e
# 每小时监控数据泵状态
0 * * * * /home/oracle/scripts/monitor_datapump.sh >> /home/oracle/datapump_monitor/cron.log 2>&1
3.3 数据泵迁移故障处理
3.3.1 数据泵迁移常见问题
# 错误信息
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
# 解决方案
# 1. 检查目录对象
SQL> select * from dba_directories where directory_name = ‘DATAPUMP_DIR’;
# 2. 创建目录对象
SQL> create directory datapump_dir as ‘/backup/datapump’;
# 3. 授权访问权限
SQL> grant read, write on directory datapump_dir to system;
# 问题2：空间不足
# 错误信息
ORA-39095: Dump file space has been exhausted
# 解决方案
# 1. 检查磁盘空间
$ df -h /backup/datapump
# 2. 清理空间
$ rm -rf /backup/datapump/*.dmp
# 3. 或使用多个文件
$ expdp system/password directory=datapump_dir dumpfile=export_%U.dmp filesize=1G schemas=scott
# 问题3：导入失败
# 错误信息
ORA-39083: Object type TABLE:”SCOTT”.”EMP” failed to create with error:
ORA-00955: name is already used by an existing object
# 解决方案
# 1. 删除已存在对象
SQL> drop table scott.emp purge;
# 2. 使用REPLACE选项
$ impdp system/password directory=datapump_dir dumpfile=scott_export.dmp table_exists_action=replace
# 3. 使用TRUNCATE选项
$ impdp system/password directory=datapump_dir dumpfile=scott_export.dmp table_exists_action=truncate
Part04-生产案例与实战讲解
4.1 数据泵迁移案例
在生产环境中使用数据泵迁移的完整案例：
4.1.1 场景描述
某企业需要将Oracle数据库从11g迁移到19c，使用数据泵进行迁移。
4.1.2 分析步骤
$ vi /home/oracle/scripts/auto_datapump_migration.sh
#!/bin/bash
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# 数据泵迁移自动化脚本
export ORACLE_HOME=/oracle/app/oracle/product/19c/dbhome_1
export ORACLE_SID=FGEDUDB
export PATH=$ORACLE_HOME/bin:$PATH
LOG_DIR=”/home/oracle/datapump”
mkdir -p $LOG_DIR
DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE=”$LOG_DIR/datapump_$DATE.log”
echo “========================================” > $LOG_FILE
echo “Data Pump Migration Report” >> $LOG_FILE
echo “Date: $(date)” >> $LOG_FILE
echo “========================================” >> $LOG_FILE
# 执行导出
echo “” >> $LOG_FILE
echo “Exporting data…” >> $LOG_FILE
expdp system/password directory=datapump_dir dumpfile=full_export.dmp logfile=full_export.log full=y parallel=4 >> $LOG_FILE
# 执行导入
echo “” >> $LOG_FILE
echo “Importing data…” >> $LOG_FILE
impdp system/password directory=datapump_dir dumpfile=full_export.dmp logfile=full_import.log full=y parallel=4 >> $LOG_FILE
# 验证迁移结果
echo “” >> $LOG_FILE
echo “Verifying migration…” >> $LOG_FILE
sqlplus -s / as sysdba <
set linesize 200
set pagesize 100
select count(*) from dba_objects;
select count(*) from dba_tables;
select count(*) from dba_indexes;
EOF
echo “” >> $LOG_FILE
echo “Data pump migration completed at $(date)” >> $LOG_FILE
# 发送报告
mail -s “Data Pump Migration Report – $DATE” admin@fgedu.net.cn < $LOG_FILE
# 2. 设置脚本权限
$ chmod +x /home/oracle/scripts/auto_datapump_migration.sh
# 3. 测试脚本
$ /home/oracle/scripts/auto_datapump_migration.sh
4.2 数据泵迁移故障处理
在数据泵迁移故障处理过程中的方法和技巧：
4.2.1 故障处理流程
# 1. 识别问题
# – 查看数据泵日志
# – 查看错误信息
# – 检查数据库状态
# 2. 收集信息
# – 收集错误信息
# – 收集诊断数据
# – 收集系统信息
# 3. 分析原因
# – 分析错误原因
# – 分析迁移过程
# – 分析配置问题
# 4. 制定方案
# – 制定修复方案
# – 制定回滚方案
# – 制定预防措施
# 5. 执行修复
# – 执行修复操作
# – 验证修复结果
# – 记录修复过程
# 示例：处理导入失败
# 1. 识别问题
$ cat /backup/datapump/full_import.log
# 2. 收集信息
SQL> select * from dba_objects where status = ‘INVALID’;
# 3. 分析原因
— 对象已存在
# 4. 制定方案
— 使用REPLACE选项
# 5. 执行修复
$ impdp system/password directory=datapump_dir dumpfile=full_export.dmp table_exists_action=replace
4.3 数据泵迁移优化
优化数据泵迁移配置的最佳实践：
4.3.1 优化数据泵迁移管理
SQL> create or replace procedure manage_datapump_results(
p_migration_date in date,
p_source_database in varchar2,
p_target_database in varchar2,
p_migration_status in varchar2
) as
begin
dbms_output.put_line(‘— Data Pump Migration Results Management —‘);
dbms_output.put_line(‘Migration Date: ‘ || to_char(p_migration_date, ‘YYYY-MM-DD HH24:MI:SS’));
dbms_output.put_line(‘Source Database: ‘ || p_source_database);
dbms_output.put_line(‘Target Database: ‘ || p_target_database);
dbms_output.put_line(‘Migration Status: ‘ || p_migration_status);
— 可以扩展为记录到表中
end manage_datapump_results;
/
Procedure created.
# 2. 执行管理存储过程
SQL> set serveroutput on
SQL> exec manage_datapump_results(sysdate, ’11g’, ’19c’, ‘SUCCESS’);
— Data Pump Migration Results Management —
Migration Date: 2026-03-31 10:00:00
Source Database: 11g
Target Database: 19c
Migration Status: SUCCESS
PL/SQL procedure successfully completed.
# 3. 创建数据泵迁移历史表
SQL> create table datapump_history (
id number primary key,
migration_date date,
source_database varchar2(50),
target_database varchar2(50),
migration_status varchar2(50),
migration_duration number,
notes varchar2(4000)
);
Table created.
SQL> create sequence datapump_history_seq;
Sequence created.
# 4. 记录迁移历史
SQL> insert into datapump_history values (
datapump_history_seq.nextval,
sysdate,
’11g’,
’19c’,
‘SUCCESS’,
120,
‘Data pump migration from 11g to 19c’
);
1 row created.
SQL> commit;
Commit complete.
# 5. 查看迁移历史趋势
SQL> column migration_date format a20
SQL> select migration_date, source_database, target_database, migration_status
from datapump_history
order by migration_date desc
fetch first 12 rows only;
MIGRATION_DATE SOURCE_DATABASE TARGET_DATABASE MIGRATION_STATUS
——————– ————— ————— —————-
2026-03-31 10:00:00 11g 19c SUCCESS
2026-03-30 10:00:00 12c 19c SUCCESS
2026-03-29 10:00:00 10g 11g SUCCESS
Part05-风哥经验总结与分享
5.1 数据泵迁移总结
Oracle数据泵是常用的数据库迁移工具，具有以下特点：
- 高速传输：支持并行处理，传输速度快
- 灵活过滤：支持数据过滤和对象过滤
- 网络传输：支持网络直接传输
- 断点续传：支持断点续传功能
- 跨平台迁移：支持跨平台迁移
5.2 数据泵迁移检查清单
Oracle数据泵迁移检查清单：
- 充分测试：在测试环境中充分测试
- 备份数据：迁移前备份数据
- 文档记录：记录迁移过程
- 验证数据：验证迁移后数据完整性
- 经验总结：总结迁移经验
- 定期检查：定期检查迁移状态
5.3 数据泵迁移工具推荐
Oracle数据泵迁移工具推荐：
- expdp：数据泵导出工具
- impdp：数据泵导入工具
- DBMS_DATAPUMP：PL/SQL数据泵包
- Shell脚本：自动化迁移过程
- 监控脚本：监控迁移过程
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
