11g迁移到19c

denglj

源端数据库：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
导入过程中出现如下异常：

登录/注册后可看大图