很开心有机会能参与这次DCP的学习，本次培训正好教了DTS数据迁移工具的使用，授课老师也趁机演示了一把从oracle到达梦的迁移。
正好我本地有一套老早以前的oracle环境一直没删，趁此机会，在自己的环境小试一下。

迁移前准备

1、确认目标oracle

2、确认源端oracle 信息

---统计页大小
SELECT name,value FROM v$parameter WHERE name = 'db_block_size';
---查询编码格式
SELECT * FROM "V$NLS_PARAMETERS" a WHERE a."PARAMETER" ='NLS_CHARACTERSET';
---统计目标用户对象
SELECT object_type,count(1) FROM all_objects WHERE OWNER = 'HR' GROUP BY OBJECT_type;

3、创建目标端 DM 实例

---初始化实例
dminit PATH=/dmdata DB_NAME=orcl INSTANCE_NAME=orcl SYSDBA_PWD=dameng123 SYSAUDITOR_PWD=dameng123 PAGE_SIZE=8 EXTENT_SIZE=32 LENGTH_IN_CHAR=0 LOG_SIZE=1024 CASE_SENSITIVE=Y CHARSET=1 PORT_NUM=7236 BLANK_PAD_MODE=1

---修改ini参数（测试环境内存为4G）
Vim /dmdata/orcl/dm.ini
MAX_OS_MEMORY 	= 80
BUFFER 				= 2000
COMPATIBLE_MODE	= 2

---修改日志大小
SQL> select * from v$rlogfile;
SQL> alter database resize logfile 'orcl01.log' to 2048;
SQL> alter database resize logfile 'orcl02.log' to 2048;

---创建目标用户和表空间
SQL> create user hr identified by 'dameng123' default tablespace hr;
SQL> create user hr identified by "dameng123" default tablespace hr;

dts连接测试

1、选择迁移类别

2、测试源端连接

3、测试目标端连接

4、选择迁移目标

5、迁移目标

迁移后注意

统计表和数据量

---创建统计表
create table table_count (owner varchar(100),table_name varchar(100),cnt int);

---统计各表数据量
declare
v_owner VARCHAR2(100);
v_tabname VARCHAR2(100);
stmt  VARCHAR2(200);
num_rows number;
begin
for rec in (select owner,table_name from dba_tables where owner='HR' order by 1, 2)---owner根据实际情况调整
            loop
            select rec.owner,rec.table_name into v_owner,v_tabname from dual; 
            stmt := 'select count(*) from "' || v_owner || '"."' || v_tabname || '"';
            EXECUTE IMMEDIATE stmt INTO num_rows;  
EXECUTE IMMEDIATE 'insert into table_count values('''||v_owner||''','''||v_tabname||''','''||to_number(num_rows)||''')';
           end loop;
end;
---查询验证数据量
select * from "SYSDBA"."TABLE_COUNT";

---查询并统计迁移对象
SELECT
 A.USERNAME  "用户名",
 (SELECT COUNT(1) FROM DBA_TABLES B WHERE B.OWNER = A.USERNAME)  "表数量",
 ( SELECT COUNT(1) FROM DBA_VIEWS G WHERE G.OWNER = A.USERNAME )  "视图数量",
 ( SELECT COUNT(1) FROM DBA_TRIGGERS H WHERE H.OWNER = A.USERNAME )  "触发器数量",
 ( SELECT COUNT(DISTINCT I.NAME) FROM DBA_SOURCE I WHERE I.OWNER = A.USERNAME AND I.TYPE = 'FUNCTION' )  "函数数量",
 ( SELECT COUNT(1) FROM DBA_SEQUENCES J WHERE J.SEQUENCE_OWNER = A.USERNAME )  "序列数量",
 ( SELECT COUNT(DISTINCT L.NAME) FROM DBA_SOURCE L WHERE L.OWNER = A.USERNAME AND L.TYPE = 'PROCEDURE' )  "存储过程数量",
 ( SELECT COUNT(1) FROM DBA_DB_LINKS M WHERE M.OWNER = A.USERNAME )  "DBLINK数量",
 ( SELECT COUNT(1) FROM DBA_INDEXES I WHERE UNIQUENESS = 'UNIQUE' AND OWNER =A.USERNAME OR INDEX_NAME NOT LIKE 'INDEX335%' AND OWNER =A.USERNAME)  "索引数量",
 ( SELECT COUNT(1) FROM DBA_OBJECTS WHERE OBJECT_TYPE='TYPE' AND OWNER =A.USERNAME OR OBJECT_TYPE='CLASS' AND OWNER =A.USERNAME )  "自定义类型",
 ( SELECT COUNT(1) FROM DBA_OBJECTS WHERE OBJECT_TYPE='PACKAGE' AND OWNER =A.USERNAME)  "PKG数量"
FROM
 DBA_USERS A WHERE A.USERNAME IN ('HR');

小结与心得

因为整个测试环境的数据量很小，也没什么复杂数据，所以整个学习和测试的过程还是很顺的，要注意的是，迁移作为一个大活，迁移前后的数据对比非常重要，数据一致性非常重要。
总之，非常感谢这次培训的机会，学到了不少新东西。