使用数据泵expdp做数据迁移步骤

1、230主机导出数据 ---13分钟
expdp smartq/Smartq_Admin1@orcl directory=TCD_EXPDIR dumpfile=smartq_`date +%Y%m%d`.dmp logfile=smartq_`date +%Y%m%d`

2、230主机，拷贝到202 --12分钟
scp smartq_20210825.* oracle@172.16.29.202:/ora_bak/
密码：xxxxxxx

3、202主机上导入 --10分钟
impdp smartq/smartq@172.16.29.203:1531/t_smartq directory=MIGRATE dumpfile=smartq_20210825.dmp logfile=smartq_imp_20210825.log

4、在19C资源池中比对
1) 19C资源池中增加dblink
create public database link smartqlink connect to xxx identified by xxxxxxxx
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.17.xxx)(PORT = xxxx))
)
(CONNECT_DATA =
(SERVICE_NAME = xxxx)
)
)';

2) 比较 19c-11g ---11分钟
创建两个表t1和t2，查看这两个表中无数据即可。

使用sq生成以下语句：

select 'select '''||table_name||''',count(*) from '||table_name||

||' minus select '''||table_name||''',count(*) from '||table_name||'  @smartqlink union all'
from user_tables;

create table t1(tablename,rownu) as
select 'DW_LOAD_HISTORY_REPORT',count(*) from DW_LOAD_HISTORY_REPORT minus select 'DW_LOAD_HISTORY_REPORT',count(*) from DW_LOAD_HISTORY_REPORT@smartqlink union all
select 'DW_LOAD_PAF_DATA_BAK',count(*) from DW_LOAD_PAF_DATA_BAK minus select 'DW_LOAD_PAF_DATA_BAK',count(*) from DW_LOAD_PAF_DATA_BAK@smartqlink union all

--------

------

------

 

3）比较 11g-19c

使用sql生成以下语句：

select 'select '''||table_name||''',count(*) from '||table_name||'@smartqlink'
||' minus select '''||table_name||''',count(*) from '||table_name||' union all'
from user_tables;

create table t2(tablename,rownu) as
select 'DW_LOAD_HISTORY_REPORT',count(*) from DW_LOAD_HISTORY_REPORT@smartqlink minus select 'DW_LOAD_HISTORY_REPORT',count(*) from DW_LOAD_HISTORY_REPORT union all
select 'DW_LOAD_PAF_DATA_BAK',count(*) from DW_LOAD_PAF_DATA_BAK@smartqlink minus select 'DW_LOAD_PAF_DATA_BAK',count(*) from DW_LOAD_PAF_DATA_BAK union all
select 'DW_LOAD_TOOL_DATA',count(*) from DW_LOAD_TOOL_DATA@smartqlink minus select 'DW_LOAD_TOOL_DATA',count(*) from DW_LOAD_TOOL_DATA union all

------

------

------

5、检查其他对象 --很快
with c19 as
(select object_type object_type,count(*) object_num
from user_objects
where 1=1
and object_type in
(
'JOB','PACKAGE BODY','TYPE BODY','TRIGGER','PACKAGE','PROCEDURE','FUNCTION','TYPE','SEQUENCE','LOB','VIEW'
)
group by object_type
),
g11 as
(select object_type object_type,count(*) object_num
from user_objects@smartqlink
where 1=1
and object_type in
(
'JOB','PACKAGE BODY','TYPE BODY','TRIGGER','PACKAGE','PROCEDURE','FUNCTION','TYPE','SEQUENCE','LOB','VIEW'
)
group by object_type
)
select c19.object_type,c19.object_num,g11.object_num,c19.object_num-g11.object_num
from c19,g11
where c19.object_type=g11.object_type
;

6、收集统计信息 --13分钟
sys用户登录
exec dbms_stats.gather_schema_stats(ownname=>'SMARTQ',method_opt=>'FOR ALL COLUMNS',force=>true,degree=>8);

7、删除临时数据
drop table t1;
drop table t2;
drop database link smartqlink;