11g迁移到19c
发布时间：2022-03-24
源端数据库：oracle 11.2.0.4
目标端数据库：oracle 19.3.0.0
1、源库导出
1.1、系统建导出目录
$ mkdir –p /u01/dumpbak/
$ chmod –R 775 /u01/dumpbak/
1.2、数据库建导出目录
SQL> create directory DUMP as ‘/u01/dumpbak/’;
1.3、查看directory
SQL> select owner,directory_name,directory_path from dba_directories;
1.4、源库导出
[oracle@dbserver ~]$ expdp \'/ as sysdba\' directory=DUMP dumpfile=exp%U.dmp logfile=exp.log full=y FILESIZE=20G
1.5、导出结果
Dump file set for SYS.SYS_EXPORT_FULL_01 is:
/u01/dumpbak/exp01.dmp
/u01/dumpbak/exp02.dmp
/u01/dumpbak/exp03.dmp
/u01/dumpbak/exp04.dmp
/u01/dumpbak/exp05.dmp
/u01/dumpbak/exp06.dmp
2、测试库导入
2.1、系统建导入目录
$ mkdir –p /data/orcldmp
$ chmod -R 775 /data/orcldmp
2.2、数据库建导入目录
SQL> create directory DUMP as ‘/data/orcldmp’;
2.3、源库传送dmp文件到测试库导入目录
$ scp exp01.dmp root@192.168.133.120:/data/orcldmp
注：6个dmp文件都要传送到测试库，exp01.dmp仅作参考
2.4、开始导入
2.4.1、试错1
第一次导入：
$ impdp system/his@192.168.133.120:1521/orcl directory=imp_dir dumpfile=exp01.dmp,exp02.dmp,exp03.dmp,exp04.dmp,exp05.dmp,exp06.dmp full=y logfile=imp.log
主要报错如下：
ORA-31684: Object type USER:"ORTLN" already exists
ORA-39111: Dependent object type OBJECT_GRANT:"APAX_030200" skipped, base object type SEQUENCE:"APAX_030200"."WVV_SEQ" already exists
ORA-39151: Table "AREX_030400"."WVV_FLTW_JOB_BIND_VALUES" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
异常原因：
a、目标端表空间未处理
b、目标端即有用户未处理
2.4.2、试错2
删除用户：
生成drop user命令，将测试库所有非系统用户删除:
select 'drop user'||' '||username||' cascade;' from dba_users where user_id<>5 and user_id<>0 and user_id<>31 and user_id<>2147483638 and user_id<>3191 and user_id<>30 and user_id<>64 and user_id<>2147483617 and user_id<>3177 and user_id<>3176 and user_id<>46 and user_id<>74 and user_id<>3192 and user_id<>75 and user_id<>3170 and user_id<>2147483620 and user_id<>43 and user_id<>77 and user_id<>3194 and user_id<>56 and user_id<>78 and user_id<>3202 and user_id<>79 and user_id<>3163 and user_id<>55 and user_id<>3164 and user_id<>14 and user_id<>3200 and user_id<>2147483619 and user_id<>54 and user_id<>9 and user_id<>3187 and user_id<>21 and user_id<>3201 and user_id<>45 and user_id<>2147483618 and user_id<>32 and user_id<>53 and user_id<>69
注意：以上排除的是19c数据库的系统用户，user_id为19c新库所查，切记勿删系统用户.
备注：此处其实可以通过以下sql语句批量生成drop命令，oracle_maintained显示Y的为系统用户.
select 'drop user'||' '||username||' cascade;' from dba_users where user_id not in (select user_id from dba_users where oracle_maintained='Y');
结果示例：
drop user N0278 cascade;
drop user N0523 cascade;
drop user N0567 cascade;
drop user D0056 cascade;
drop user N0479 cascade;
drop user D0727 cascade;
drop user N0914 cascade;
drop user N0268 cascade;
drop user N0501 cascade;
……
然后将目标库所有非系统用户全部删除.
处理表空间：
增加目标端数据库表空间容量，大小参照源端表空间已使用大小.
说明：
目标端表空间大小需大于源端对应表空间已使用空间大小；
目标端没有的表空间需要重建.
第二次导入：
[oracle@his orcldmp]$ impdp system/his@192.168.133.120:1521/orcl directory=imp_dir dumpfile=exp01.dmp,exp02.dmp,exp03.dmp,exp04.dmp,exp05.dmp,exp06.dmp full=y logfile=imp1.log
导入过程中出现如下异常：
原因：此异常为字符集引起，中文在UTF-8中占3个字节，ZHS16GBK中占2个字节，要避免此异常发生，需保证导出导入端字符集相同.
停止导入：
a、查看视图dba_datapump_jobs
select * from dba_datapump_jobs;
b、停止impdp导入任务
impdp system/his@orcl attach=job_name
c、关闭job
stop_job=immediate
d、确认job状态
select * from dba_datapump_jobs;
2.4.3、重新建库导入
注意：
a、export DISPLAY时IP需为本地Windows IP
b、建库前用dbca将原库orcl删除
c、字符集需选择与源库相同
源库字符集为ZHS16GBK，所以建立新库到此处时也选择ZHS16GBK。
新库建好后，创建对应表空间及数据文件:
alter tablespace TEMP add tempfile '/data/oradata/orcl/temp01.dbf' size 31g;
alter tablespace TEMP add tempfile '/data/oradata/orcl/temp02.dbf' size 31g;
alter tablespace TEMP add tempfile '/data/oradata/orcl/temp03.dbf' size 5g autoextend on;
create temporary tablespace TOOLA tempfile '/data/oradata/orcl/TOOLA.dbf' size 31g;
alter tablespace TOOLA add tempfile '/data/oradata/orcl/TOOLA01.dbf' size 5g autoextend on;
create tablespace PATIENT1 datafile '/data/oradata/orcl/PATIENT1.dbf' size 16g autoextend on;
create tablespace EXPENSE datafile '/data/oradata/orcl/EXPENSE.dbf' size 31g;
alter tablespace EXPENSE add datafile '/data/oradata/orcl/EXPENSE01.dbf' size 31g;
alter tablespace USERS add datafile '/data/oradata/orcl/users.dbf' size 31g;
alter tablespace USERS add datafile '/data/oradata/orcl/users01.dbf' size 16g autoextend on;
......
2.6、第三次导入
[oracle@his orcldmp]$ impdp system/his@192.168.133.120:1521/orcl directory=imp_dir dumpfile=exp01.dmp,exp02.dmp,exp03.dmp,exp04.dmp,exp05.dmp,exp06.dmp full=y logfile=imp.log
最后提示：
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 852 error(s) at Mon Feb 21 06:09:29 2022 elapsed 0 04:27:37
备注：
a、导出导入过程中需随时关注报错信息，过程中特别要注意字符集问题.
b、数据量大时，可以采用并行方式缩短时间.
c、导出导入后要查看相关日志记录.
目标端数据库：oracle 19.3.0.0
1、源库导出
1.1、系统建导出目录
$ mkdir –p /u01/dumpbak/
$ chmod –R 775 /u01/dumpbak/
1.2、数据库建导出目录
SQL> create directory DUMP as ‘/u01/dumpbak/’;
1.3、查看directory
SQL> select owner,directory_name,directory_path from dba_directories;
1.4、源库导出
[oracle@dbserver ~]$ expdp \'/ as sysdba\' directory=DUMP dumpfile=exp%U.dmp logfile=exp.log full=y FILESIZE=20G
1.5、导出结果
Dump file set for SYS.SYS_EXPORT_FULL_01 is:
/u01/dumpbak/exp01.dmp
/u01/dumpbak/exp02.dmp
/u01/dumpbak/exp03.dmp
/u01/dumpbak/exp04.dmp
/u01/dumpbak/exp05.dmp
/u01/dumpbak/exp06.dmp
2、测试库导入
2.1、系统建导入目录
$ mkdir –p /data/orcldmp
$ chmod -R 775 /data/orcldmp
2.2、数据库建导入目录
SQL> create directory DUMP as ‘/data/orcldmp’;
2.3、源库传送dmp文件到测试库导入目录
$ scp exp01.dmp root@192.168.133.120:/data/orcldmp
注：6个dmp文件都要传送到测试库，exp01.dmp仅作参考
2.4、开始导入
2.4.1、试错1
第一次导入：
$ impdp system/his@192.168.133.120:1521/orcl directory=imp_dir dumpfile=exp01.dmp,exp02.dmp,exp03.dmp,exp04.dmp,exp05.dmp,exp06.dmp full=y logfile=imp.log
主要报错如下：
ORA-31684: Object type USER:"ORTLN" already exists
ORA-39111: Dependent object type OBJECT_GRANT:"APAX_030200" skipped, base object type SEQUENCE:"APAX_030200"."WVV_SEQ" already exists
ORA-39151: Table "AREX_030400"."WVV_FLTW_JOB_BIND_VALUES" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
异常原因：
a、目标端表空间未处理
b、目标端即有用户未处理
2.4.2、试错2
删除用户：
生成drop user命令，将测试库所有非系统用户删除:
select 'drop user'||' '||username||' cascade;' from dba_users where user_id<>5 and user_id<>0 and user_id<>31 and user_id<>2147483638 and user_id<>3191 and user_id<>30 and user_id<>64 and user_id<>2147483617 and user_id<>3177 and user_id<>3176 and user_id<>46 and user_id<>74 and user_id<>3192 and user_id<>75 and user_id<>3170 and user_id<>2147483620 and user_id<>43 and user_id<>77 and user_id<>3194 and user_id<>56 and user_id<>78 and user_id<>3202 and user_id<>79 and user_id<>3163 and user_id<>55 and user_id<>3164 and user_id<>14 and user_id<>3200 and user_id<>2147483619 and user_id<>54 and user_id<>9 and user_id<>3187 and user_id<>21 and user_id<>3201 and user_id<>45 and user_id<>2147483618 and user_id<>32 and user_id<>53 and user_id<>69
注意：以上排除的是19c数据库的系统用户，user_id为19c新库所查，切记勿删系统用户.
备注：此处其实可以通过以下sql语句批量生成drop命令，oracle_maintained显示Y的为系统用户.
select 'drop user'||' '||username||' cascade;' from dba_users where user_id not in (select user_id from dba_users where oracle_maintained='Y');
结果示例：
drop user N0278 cascade;
drop user N0523 cascade;
drop user N0567 cascade;
drop user D0056 cascade;
drop user N0479 cascade;
drop user D0727 cascade;
drop user N0914 cascade;
drop user N0268 cascade;
drop user N0501 cascade;
……
然后将目标库所有非系统用户全部删除.
处理表空间：
增加目标端数据库表空间容量，大小参照源端表空间已使用大小.
说明：
目标端表空间大小需大于源端对应表空间已使用空间大小；
目标端没有的表空间需要重建.
第二次导入：
[oracle@his orcldmp]$ impdp system/his@192.168.133.120:1521/orcl directory=imp_dir dumpfile=exp01.dmp,exp02.dmp,exp03.dmp,exp04.dmp,exp05.dmp,exp06.dmp full=y logfile=imp1.log
导入过程中出现如下异常：
原因：此异常为字符集引起，中文在UTF-8中占3个字节，ZHS16GBK中占2个字节，要避免此异常发生，需保证导出导入端字符集相同.
停止导入：
a、查看视图dba_datapump_jobs
select * from dba_datapump_jobs;
b、停止impdp导入任务
impdp system/his@orcl attach=job_name
c、关闭job
stop_job=immediate
d、确认job状态
select * from dba_datapump_jobs;
2.4.3、重新建库导入
注意：
a、export DISPLAY时IP需为本地Windows IP
b、建库前用dbca将原库orcl删除
c、字符集需选择与源库相同
源库字符集为ZHS16GBK，所以建立新库到此处时也选择ZHS16GBK。
新库建好后，创建对应表空间及数据文件:
alter tablespace TEMP add tempfile '/data/oradata/orcl/temp01.dbf' size 31g;
alter tablespace TEMP add tempfile '/data/oradata/orcl/temp02.dbf' size 31g;
alter tablespace TEMP add tempfile '/data/oradata/orcl/temp03.dbf' size 5g autoextend on;
create temporary tablespace TOOLA tempfile '/data/oradata/orcl/TOOLA.dbf' size 31g;
alter tablespace TOOLA add tempfile '/data/oradata/orcl/TOOLA01.dbf' size 5g autoextend on;
create tablespace PATIENT1 datafile '/data/oradata/orcl/PATIENT1.dbf' size 16g autoextend on;
create tablespace EXPENSE datafile '/data/oradata/orcl/EXPENSE.dbf' size 31g;
alter tablespace EXPENSE add datafile '/data/oradata/orcl/EXPENSE01.dbf' size 31g;
alter tablespace USERS add datafile '/data/oradata/orcl/users.dbf' size 31g;
alter tablespace USERS add datafile '/data/oradata/orcl/users01.dbf' size 16g autoextend on;
......
2.6、第三次导入
[oracle@his orcldmp]$ impdp system/his@192.168.133.120:1521/orcl directory=imp_dir dumpfile=exp01.dmp,exp02.dmp,exp03.dmp,exp04.dmp,exp05.dmp,exp06.dmp full=y logfile=imp.log
最后提示：
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 852 error(s) at Mon Feb 21 06:09:29 2022 elapsed 0 04:27:37
备注：
a、导出导入过程中需随时关注报错信息，过程中特别要注意字符集问题.
b、数据量大时，可以采用并行方式缩短时间.
c、导出导入后要查看相关日志记录.
