1 DTS(DM数据迁移工具)迁移应注意检查的相关事项
1.1 迁移数据可能会导致oracle内存溢出，需要oracle数据库管理人员在
1.2先查询出备份表，确认查出来的是备份表，然后drop这些备份表，生产环境可能备份表也需要迁
select t.OWNER, table_name,T.NUM_ROWS
from dba_tabLES t
where ((t.table_name LIKE '%\_BAK\_%' ESCAPE '\')
OR (t.table_name LIKE 'BAK_%')
OR (t.table_name LIKE '%BAK')
OR (t.table_name LIKE '%\_BAK%' ESCAPE '\')
--OR (t.table_name LIKE '%\_TMP\_%' ESCAPE '\')
--OR (t.table_name LIKE 'TMP%')
--OR (t.table_name LIKE '%\_TEMP\_%' ESCAPE '\')
--OR (t.table_name LIKE 'TEMP%')
OR t.TABLE_NAME LIKE 'BIN$%'
OR t.TABLE_NAME LIKE 'BK%'
OR TRANSLATE(SUBSTR(t.table_name, -4), '$1234567890', '$') IS NULL
--OR comments is null
)
ORDER BY T.OWNER, table_name,T.NUM_ROWS DESC
1.3 统计数据量(结果表bk_20240516_dba_tables),后续核对数据使用(迁移完成后,达梦库也要使用该sql统计数据量)
--达梦库建表bk_20240516_dm_dba_tables
--oracle建表 bk_20240516_dba_tables
create table bk_20240516_dba_tables (owner VARCHAR2(100),table_name varchar2(200), num_rows number);
create global temporary table bk_20240516_tmp_dba_tables (owner VARCHAR2(100),table_name varchar2(200))
on commit preserve rows;
DECLARE
L_NUMBER NUMBER;
BEGIN
execute immediate 'TRUNCATE TABLE bk_20240516_tmp_dba_tables';
INSERT INTO bk_20240516_tmp_dba_tables
(owner, table_name)
select T.OWNER, T.TABLE_NAME
from dba_tables t
left join bk_20240516_dba_tables t2
on t.OWNER = t2.owner
and t.TABLE_NAME = t2.table_name
where t.table_name not like 'BIN%'
and t2.table_name is null --避免中途报错后跑之前跑过的数据
;
for f in (select T.OWNER, T.TABLE_NAME
from bk_20240516_tmp_dba_tables t
order by owner, TABLE_NAME) LOOP
EXECUTE IMMEDIATE 'select /*+ parallel(4)*/ count(1) from ' || F.OWNER || '.' ||
F.TABLE_NAME
INTO L_NUMBER;
DELETE FROM bk_20240516_dba_tables T --达梦改为bk_20240516_dm_dba_tables
WHERE T.OWNER = F.OWNER
AND T.TABLE_NAME = F.TABLE_NAME;
INSERT INTO bk_20240516_dba_tables
(owner, table_name, num_rows)
VALUES
(F.owner, F.table_name, L_NUMBER);
END LOOP;
commit;
end;
1.4 因为oracle和达梦数据库的用户名不一样,而且DTS有可能会少建一些对象。利用plsql导出oralce同义词，视图,触发器，存储过程，函数，包的创建语句，修改用户名后在达梦数据库执行。Dts不需要迁移同义词，视图,触发器，存储过程，函数，包。
1.5 检查达梦数据库用户是否有dba_tables查询权限
1.6 迁移表参数修改,一定要勾选应用当前项到其他同类对象
1.7 如果选择了删除表，就不要选择’删除后拷贝记录’。这样会报错。要选择’拷贝记录’
1.8 如果内存充足，可以考虑启动多个dts，同时迁移多个用户数据
1.9 进行数据验证
2 数据验证
2.1 如果涉及迁移多个数据库用户，而且数据库用户之间需要授权表的权限。则在达梦数据库每个数据库用户执行授予其他用户操作表的授权语句
DECLARE
l_local_user varchar2(50);
BEGIN
select t.USERNAME into l_local_user from user_users t;
for f in (select USERNAME
from dba_users t
where t.username not in (l_local_user)) LOOP
FOR R IN (SELECT table_name FROM user_tables) LOOP
EXECUTE IMMEDIATE 'grant select,delete,insert,update on ' ||
R.table_name || ' to ' || F.USERNAME;
END LOOP;
END LOOP;
END;
2.2 在达梦数据库进行表数据量统计（参考1.3）
2.3在oracle数据库创建临时表(bk_20240516_dm_dba_tables),并将达梦的bk_20240516_dm_dba_tables数据导入oracle的bk_20240516_dm_dba_tables
--oracle执行
create table bk_20240516_dm_dba_tables (owner VARCHAR2(100),table_name varchar2(200), num_rows number);
2.4 oracle创建bk_20240516_dba_tables_remark，用来存储表数据量不一致得原因。
CREATE TABLE bk_20240516_dba_tables_REMARK(OWNER VARCHAR2(50),TABLE_NAME VARCHAR2(100),REMARK VARCHAR2(4000))
2.5 在oracle中查询oracle和达梦表数据不一致的表
select T1.OWNER ora_OWNER,
T2.OWNER dm_OWNER,
T1.TABLE_NAME ora_TABLE_NAME,
T2.TABLE_NAME dm_TABLE_NAME,
T1.NUM_ROWS ora_NUM_ROWS,
T2.NUM_ROWS dm_NUM_ROWS,
CASE
WHEN T3.TABLE_NAME IS NULL THEN
case
when t1.num_rows / decode(t2.num_rows, 0, 1, null, 1, t2.num_rows) >= 1.3 then
'数据量匹配不上(严重)'
WHEN t2.num_rows / decode(t1.num_rows, 0, 1, null, 1, t1.num_rows) >= 1.1 then
'导入数据可能重复'
else
'数据量匹配不上'
end
WHEN T2.TABLE_NAME IS NULL THEN
'表缺失'
end problem,
t5.remark
from bk_20240516_dba_tables t1
left join bk_20240516_dm_dba_tables t2
on t2.owner = T1.OWNER
AND T2.TABLE_NAME = T1.TABLE_NAME
left join bk_20240516_dm_dba_tables t3
on t3.owner = T1.OWNER
AND T3.TABLE_NAME = T1.TABLE_NAME
AND nvl(T3.NUM_ROWS, 0) = nvl(T1.NUM_ROWS, 0)
left join bk_20240516_dba_tables_REMARK t5
on t1.owner = t5.owner
and t1.table_name = t5.table_name
ORDER BY T1.OWNER, T1.TABLE_NAME;
2.6 如果有表不一致，需要重新导表数据。DTS可以指定特定的表导入数据
2.6.1 先重置表选择，然后点击导入迁移对象
2.6.2 导入文件是TXT格式,具体文本内容格式如下
2.6.3 查找对应的表，设置转换设置
2.6.4 将重新迁移的表放入BK_20240516_TMP_TABLE中。根据BK_20240516_TMP_TABLE重新统计达梦库中的数据量。BK_20240516_TMP_TABLE放入数据时，要自己在excel生成ID后再放入
--达梦库执行
CREATE TABLE "PSP_PSR_PLAN"."BK_20240516_TMP_TABLE"
(
"ID" VARCHAR2(8188) DEFAULT SYS_GUID() NOT NULL,
"OWNER" VARCHAR2(50),
"TABLE_NAME" VARCHAR2(100),
NOT CLUSTER PRIMARY KEY("ID")) ;
truncate table BK_20240516_TMP_TABLE;
select * from BK_20240516_TMP_TABLE for update;
2.6.5 重新统计这重新迁移的表在达梦数据量
DECLARE
L_NUMBER NUMBER;
BEGIN
execute immediate 'TRUNCATE TABLE bk_20240516_tmp_dba_tables';
INSERT INTO bk_20240516_tmp_dba_tables
(owner, table_name)
select T.OWNER, T.TABLE_NAME
from BK_20240516_TMP_TABLE t
;
for f in (select T.OWNER, T.TABLE_NAME
from bk_20240516_tmp_dba_tables t
order by owner, TABLE_NAME) LOOP
EXECUTE IMMEDIATE 'select /*+ parallel(4)*/ count(1) from ' || F.OWNER || '.' ||
F.TABLE_NAME
INTO L_NUMBER;
DELETE FROM bk_20240516_dm_dba_tables T
WHERE T.OWNER = F.OWNER
AND T.TABLE_NAME = F.TABLE_NAME;
INSERT INTO bk_20240516_dm_dba_tables
(owner, table_name, num_rows)
VALUES
(F.owner, F.table_name, L_NUMBER);
END LOOP;
commit;
end;
2.6.6 将达梦bk_20240516_dm_dba_tables的记录重新放入oracle
2.6.7 如果有必要,将数据不一致的原因放入oracle的BK_20240516_DBA_TABLES_REMARK
2.6.8 在oracle执行查询数据差异的语句(参考2.5)
3 其他数据库对象验证(以存储过程为例)
3.1 利用PLSQL导出oracle的存储过程。因为oracle和达梦的用户名不一致以及达梦不兼容oralce的函数和关键字，因此修改导出存储过程中涉及到的用户名以及函数，关键字。然后在达梦数据库执行。
3.2 如果遇到报错，先解决报错。解决完报错后，重新编译存储过程以及授权给其他用户执行。
3.2.1 在oracle中重新编译对象
BEGIN
for f in (select t.OWNER, t.OBJECT_NAME, t.OBJECT_TYPE
from dba_objects t
where t.object_type in ('VIEW',
'TRIGGER',
'PACKAGE',
'PROCEDURE',
'FUNCTION',
'TYPE')
-- and t.OBJECT_NAME='DELETE_PLAN_PROJECT_DATA'
) loop
--编译
begin
/* dbms_output.put_line('alter ' || f.object_type || ' ' || f.owner || '.' ||
f.object_name || ' compile');*/
execute immediate 'alter ' || f.object_type || ' ' || f.owner || '.' ||
f.object_name || ' compile';
exception
when others then
null;
end;
end loop;
end;
3.2.2 如果涉及迁移多个数据库用户，而且数据库用户之间需要授权数据库对象的调用权限。在达梦数据库每个用户重新编译对象和授权
DECLARE
l_local_user varchar2(50);
BEGIN
select t.username into l_local_user from user_users t;
for f in (select t.OBJECT_NAME, t.OBJECT_TYPE
from user_objects t
where t.object_type in ('VIEW',
'TRIGGER',
'PACKAGE',
'PROCEDURE',
'FUNCTION',
'TYPE')) loop
--编译
begin
execute immediate 'alter ' || f.object_type || ' ' || f.object_name ||
' compile';
exception
when others then
null;
end;
for f2 in (select USERNAME
from dba_users t
where t.username like 'PSP_PSR%'
AND t.username not in (l_local_user, 'PSP_PSR_RUN')) LOOP
begin
IF F.OBJECT_TYPE != 'VIEW' THEN
--授权
execute immediate 'grant execute on ' || f.object_name || ' to ' ||
f2.username;
ELSE
--视图授权
execute immediate 'grant select on ' || f.object_name || ' to ' ||
f2.username;
end if;
exception
when others then
null;
end;
end loop;
end loop;
end;
3.3 在达梦数据库查询数据库对象的状态，然后放入ORACLE的BK_20240516_DM_DBA_OBJECTS。可以将编译不通过的原因写在oracle的bk_20240516_dba_OBJECTs_REMARK表中。然后在oracle数据库查询oracle状态正常但是达梦状态异常的对象
--Oracle建表
CREATE TABLE bk_20240516_dba_OBJECTs_REMARK(OWNER VARCHAR2(50),OBJECT_NAME VARCHAR2(100),REMARK VARCHAR2(4000));
--达梦建表
create table BK_20240516_DM_DBA_OBJECTS
(
owner VARCHAR2(200),
object_name VARCHAR2(200),
Object_type varchar2(50),
status VARCHAR2(200)
);
--达梦执行，然后将BK_20240516_DM_DBA_OBJECTS数据要迁入oracle
truncate table BK_20240516_DM_DBA_OBJECTS ;
insert into BK_20240516_DM_DBA_OBJECTS
SELECT T.OWNER, T.OBJECT_NAME, T.OBJECT_TYPE, T.status
--count(1)
FROM DBA_OBJECTS T
WHERE T.OBJECT_TYPE IN ('CLASS',
'TRIGGER',
'TYPE',
'PACKAGE BODY',
'FUNCTION',
'VIEW',
'PROCEDURE',
'SEQUENCE',
'SYNONYM',
'CONSTRAINT',
'INDEX');
commit;
--oracle查询有问题的对象
select T1.OWNER,
T1.OBJECT_NAME,
T1.OBJECT_TYPE,
T1.status,
t2.status dm_status,
case
when t2.object_name is null then
'缺失对象'
when t2.status = 'INVALID' THEN
'对象无效'
End FLAG,
T3.REMARK
from (select *
from DBA_OBJECTS TT
WHERE TT.OWNER like 'PSR%'
AND TT.OWNER != 'PSR_RUN'
AND TT.OBJECT_TYPE IN ('CLASS',
'TRIGGER',
'TYPE',
'PACKAGE BODY',
'FUNCTION',
'VIEW',
'PROCEDURE',
'SEQUENCE',
'SYNONYM',
'CONSTRAINT',
'INDEX')
and tt.status = 'VALID'
AND TT.OBJECT_NAME NOT LIKE 'SYS_IL%$$') T1
LEFT JOIN BK_20240516_DM_DBA_OBJECTS T2
ON T1.OWNER = T2.OWNER
AND T1.OBJECT_NAME = T2.OBJECT_NAME
LEFT JOIN bk_20240516_dba_OBJECTs_REMARK T3
ON T1.OWNER = T3.OWNER
AND T1.OBJECT_NAME = T3.OBJECT_NAME
WHERE t2.object_name is null
OR t2.status = 'INVALID'
order by t1.owner, t1.object_type, t1.object_name;
3.4 有些存储过程在达梦编译时会提示报错,但是实际上没有错误。编译时报错，但是实际可以调用成功。检查存储过程是否报错用如下sql查询:
--STATUS为VALID时,存储过程没有错误
select T.OWNER, T.OBJECT_NAME,T.STATUS
from DBA_OBJECTS T
WHERE T.OBJECT_NAME='TEST';
3.5 处理完索引以外的报错，在oracle生成达梦数据库缺失的索引的创建语句和报错索引的重建语句放入表BK_20240516_INDEX_ERROR。注意：达梦数据中dba_objects存储约束的类型是CONSTRAINT,ORACLE中dba_objects存储约束的类型是INDEX;
--ORACLE执行,创建BK_20240516_INDEX_ERROR表
CREATE TABLE BK_20240516_INDEX_ERROR
(ID VARCHAR2(50) DEFAULT SYS_GUID(),
OWNER VARCHAR2(100),
OBJECT_NAME VARCHAR2(200),
SQL_TEXT VARCHAR2(4000),
ERROR_INFO VARCHAR2(4000)
);
--ORACLE执行,将语句放到BK_20240516_INDEX_ERROR表中
DECLARE
L_SQLTEXT VARCHAR2(4000);
BEGIN
FOR F IN (select CASE
WHEN T3.CONSTRAINT_NAME IS NOT NULL THEN
'CONSTRAINT'
ELSE
T1.OBJECT_TYPE
END OBJECT_TYPE,
T1.OWNER,
T1.OBJECT_NAME,
case
when t2.object_name is null then
'缺失对象'
when t2.status = 'INVALID' THEN
'对象无效'
End FLAG,
T5.COLUMN_NAMES,
T4.UNIQUENESS
from (select *
from DBA_OBJECTS TT
WHERE TT.OBJECT_TYPE IN ('INDEX')
and tt.status = 'VALID'
AND TT.OBJECT_NAME NOT LIKE 'SYS_IL%$$'
AND TT.OBJECT_NAME NOT LIKE 'SYS_C%'
--and tt.OBJECT_NAME = 'PK_G_GADGETS_TC1_DS_433'
) T1
LEFT JOIN BK_20240516_DM_DBA_OBJECTS T2
ON T1.OWNER = T2.OWNER
AND T1.OBJECT_NAME = T2.OBJECT_NAME
left join dba_constraints T3
ON T3.CONSTRAINT_NAME = T1.OBJECT_NAME
AND T3.OWNER = T1.OWNER
LEFT JOIN DBA_INDEXES T4
ON T4.OWNER = T1.OWNER
AND T4.INDEX_NAME = T1.OBJECT_NAME
LEFT JOIN (select T.OWNER,
T.NAME TABLE_NAME,
listagg(T.COLUMN_NAME, ',') COLUMN_NAMES
from DBA_PART_KEY_COLUMNS T
GROUP BY T.OWNER, NAME) T5
ON T5.TABLE_NAME = T4.TABLE_NAME
AND T5.OWNER = T4.OWNER
WHERE t2.object_name is null
OR t2.status = 'INVALID'
order by t1.owner, t1.object_name) LOOP
--生成创建索引语句
IF F.FLAG = '缺失对象' THEN
SELECT to_char(dbms_metadata.get_ddl(F.OBJECT_TYPE,
F.OBJECT_NAME,
F.OWNER))
INTO L_SQLTEXT
FROM DUAL;
IF F.OBJECT_TYPE = 'CONSTRAINT' THEN
select SUBSTR(L_SQLTEXT, 1, INSTR(L_SQLTEXT, 'USING INDEX') - 1)
INTO L_SQLTEXT
from dual;
ELSIF F.OBJECT_TYPE = 'INDEX' AND INSTR(L_SQLTEXT, 'PCTFREE ') > 0 THEN
select SUBSTR(L_SQLTEXT, 1, INSTR(L_SQLTEXT, 'PCTFREE ') - 1)
INTO L_SQLTEXT
from dual;
END IF;
--达梦建不包含分区列的唯一性全局索引要加GLOBAL
IF F.OBJECT_TYPE = 'INDEX' AND INSTR(L_SQLTEXT, F.COLUMN_NAMES) = 0 AND
F.UNIQUENESS = 'UNIQUE' THEN
L_SQLTEXT := replace(trim(L_SQLTEXT),CHR(10),'') || ' global';
END IF;
--生成重建索引语句
elsif F.FLAG = '对象无效' then
L_SQLTEXT := 'alter index ' || f.owner || '.' || f.object_name ||
' rebuild ';
end if;
---如果oracle数据库用户名和达梦数据库用户不一致。将oracle数据库用户名替换为达梦的数据库用户名
L_SQLTEXT := replace(L_SQLTEXT, 'TEST_USER1', 'DM_TEST_USER1');
L_SQLTEXT := replace(L_SQLTEXT, 'TEST_USER2', 'DM_TEST_USER2');
DELETE FROM BK_20240516_INDEX_ERROR T
WHERE T.OWNER = F.OWNER
AND T.OBJECT_NAME = F.OBJECT_NAME;
INSERT INTO BK_20240516_INDEX_ERROR
(OWNER, OBJECT_NAME, SQL_TEXT)
VALUES
( 'DM_'||F.OWNER, F.OBJECT_NAME, trim(L_SQLTEXT));
END LOOP;
COMMIT;
END;
3.6 将ORACLE的BK_20240516_INDEX_ERROR数据迁移到达梦数据库达梦数据库用户。如果迁移涉及多个数据用户，也要授予其他用户的表的查询权限。然后在达梦数据库各个用户遍历表中的SQL语句并执行。
--在达梦数据库执行
declare
l_err_sql varchar2(4000);
begin
for f in (select t.OWNER, OBJECT_NAME, trim(SQL_TEXT) sql_text
from BK_20240516_INDEX_ERROR t
ORDER BY T.OBJECT_NAME) loop
begin
execute immediate f.sql_text;
update PSP_PSR_PLAN.BK_20240516_INDEX_ERROR t
set t.error_info = null
WHERE T.OWNER = F.OWNER
AND T.OBJECT_NAME = F.OBJECT_NAME;
exception
when others then
l_err_sql := sqlerrm;
update PSP_PSR_PLAN.BK_20240516_INDEX_ERROR t
set t.error_info = l_err_sql
WHERE T.OWNER = F.OWNER
AND T.OBJECT_NAME = F.OBJECT_NAME;
END;
end loop;
commit;
end;
3.7 在达梦数据库查询上一步报错信息，看看有没有报错
SELECT * FROM BK_20240516_INDEX_ERROR T WHERE T.ERROR_INFO IS NOT NULL
ORDER BY T.OWNER,T.OBJECT_NAME;