12c向19c迁移：使用数据泵（impdp）+dblink做全量迁移

目录

1、数据泵

2、impdp+dblink

3、公司迁移项目注意事项

4、监控数据泵状态

5、遇到的问题和解决

---

1、数据泵

单独使用数据泵（expdp、impdp）做表的全量迁移，通过指定表名，即可将表的定义、主键、索引、外键、CHECK约束和全量数据导出到一个文件，在另一个数据库导入文件，这是一种逻辑层面的导出导入，可以跨Oracle版本，适合12c到19c的迁移。

导出：

expdp paastest/Paas1015 dumpfile=mk_iclul_mnt_sn_hs.dmp tables=MK_ICLUL_MNT_SN_HS directory=workdir

directory选项是在sqlplus中创建的目录对象，例如：

create directory workdir as '/mnt/disk01/u01/app/oracle/workdir/';

如果不指定directory选项，默认导出文件在$ORACLE_BASE/admin/$ORACLE_SID/dpdump/mk_iclul_mnt_sn_hs.dmp

tables选项可以指定多个表，例如：

tables=“MK_ICLUL_MNT_SN_HS,JOB_TRIGGER_LOG”

导入：

impdp paastest/Paas1015 dumpfile=job_trigger_log.dmp directory=workdir

注意，最好使用table模式，指定具体表名列表，而不是整个用户的schema，这样更容易控制，出错也更容易解决，尽管这样有时命令中会有上百个表，下面impdp+dblink也是一样。

2、impdp+dblink

在目标数据库执行impdp命令时，指定选项network_link=（事先配置好dblink），则可将表从源数据库直接导入目标数据库。这样跳过了导出到文件，再从文件导入的步骤。

而且，数据泵+dblink可以支持CLOB、BLOB类型的导入。（公司的数据库中许多表都定义了CLOB和BLOB类型的列，经测试支持导出导入且源端和目标端CLOB和BLOB类型的数据内容一致。）

在目标数据库，创建到源数据库的TNS连接项：

vim network/admin/tnsnames.ora

PAAS_TEST =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.32.150.13)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = nlpass01)

    )

  )

sqlplus paastest/Paas1015@paas_test

在目的数据库中，创建到源数据库的database link：

create public database link paas_test_link connect to paastest identified by Paas1015 using 'PAAS_TEST';

在目的数据库中，执行impdp+dblink导入：

impdp paastest/Paas1015 network_link=paas_test_link tables="t1,t2,t3" cluster=no exclude=statistics,grant parallel=2 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y

注意（对单独使用数据泵和数据泵+dblink都适用）：

a) 源数据库与目标数据库字符集需要相同，字符集是创建数据库时指定的，创建以后不能改，例如公司生产系统的字符集为ZHS16GBK：

b) 目标数据库中，应创建与源数据库相同的：用户名、权限、表空间。源数据库中，需要迁移的所有表所在的表空间，索引所在表空间，表空间的大小，数据文件的个数和类型（smallfile或bigfile）。这些需要手动在目标数据库创建，原则上一一对应，表空间可以有所扩展。 

查看表空间和数据文件：

select USERNAME,DEFAULT_TABLESPACE from dba_users where USERNAME like 'PAASTEST';

select TABLESPACE_NAME from user_tablespaces;

select file_name, tablespace_name, BYTES/(1000*1000*1000) GB from dba_data_files;

select * from dba_sys_privs where grantee='PAASTEST'; 

创建表空间

create tablespace tab_paastest datafile '/u01/app/oracle/oradata/NLPASS01/paastest01.dbf' size 31G;

alter tablespace tab_paastest add datafile '/u01/app/oracle/oradata/NLPASS01/paastest02.dbf' size 31G;

create tablespace paas2 datafile '/u01/app/oracle/oradata/NLPASS01/paas2_01.dbf' size 100m autoextend on next 100m;

创建用户

create user paastest identified by Paas1015 default tablespace tab_paastest;

alter user paastest quota unlimited on tab_paastest;

alter user paastest quota unlimited on paas2;

grant connect,resource to paastest;

grant dba to paastest;

3、公司迁移项目注意事项

不迁移统计数据：

数据泵默认会迁移表的统计数据，但是在测试中发现，迁移统计数据会出错（可能是权限原因），由于统计数据不需要完全与源数据库相同，迁移时可以不迁移统计数据，而是在迁移后，在目标数据库重新收集统计数据，使用impdp选项exclude=statistics排除导出统计数据。

在目标数据库导入完成后，重新生成统计数据：

-- 收集paastest用户所有表的统计信息

exec dbms_stats.gather_schema_stats('paastest');

-- 收集一个表的统计信息

dbms_stats.gather_table_stats(‘table_name’);

查看表的统计信息：

select TABLE_NAME,NUM_ROWS,BLOCKS,SAMPLE_SIZE,AVG_ROW_LEN from user_tab_statistics where table_name='QRTZ_TRIGGERS_PK';

不迁移grant：

迁移时，会在目标数据库中，尝试将表的操作权限赋给某些用户，如果目标数据库没有这些用户，会迁移失败。确实如果需要用到这些用户，就在目标数据库创建，如果不需要，可以使用impdp选项exclude=grant排除。

4、监控数据泵状态

对于运行时间很长的expdp和impdp可以监控其运行过程（对单独使用数据泵和数据泵+dblink都适用）：

col JOB_NAME for a50

col STATE for a30

select JOB_NAME, STATE from dba_datapump_jobs;

 impdp paastest/Paas1015 attach=SYS_IMPORT_FULL_01

>status

stop_job=immediate

kill_job

5、遇到的问题和解决

impdp时会出现ORA-01555: snapshot too old: 参考

expdp error ORA-01555: snapshot too old — oracle-tech

增大源端Oracle的undo_retention，增大到90000：

ALTER SYSTEM SET UNDO_RETENTION=90000 scope=both;

（参数UNDO_RETENTION不用重启Oracle就可以生效）

这可能是因为源数据库导出表时，同时有数据更新，此时被导出的表的数据会放到undo表空间里，如果导出时间过长，undo空间的数据得不到释放，undo空间不够用，就会有这个错误。如果没有数据更新，估计这种情况不会发生。解决方法是：每次执行impdp时，tables参数中的表不要太多，不超过50个，如果表很大，就指定一个表。

下面是执行impdp的shell脚本，接受一个文件作为输入，一个表名一行：

table_list=''
list_size=0
for line in $(cat $1)
do
    if test -n "$table_list";then
        table_list+=','
        table_list+=$line
    else
        table_list+=$line
    fi

    list_size=$(expr $list_size + 1)
    if test $list_size -eq 50;then
        set -x
        impdp paastest/Paas1015 network_link=paas_test_link tables="$table_list" cluster=no exclude=statistics,grant parallel=2 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y
        set +x
        list_size=0
        table_list=''
    fi
done

if test -n "$table_list";then
set -x
impdp paastest/Paas1015 network_link=paas_test_link tables="$table_list" cluster=no exclude=statistics,grant parallel=2 TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y
set +x

fi

被外键引用的主表和引用它的子表，要一起在tablse参数中，否则会导出失败。实际上，先导入所有被外键引用的主表，再导入引用它们的子表，这样也可以。