:: :: :: ::

::

公告

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 "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
                ) 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.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 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 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;

posted on 2024-07-25 14:55 追求完美9196 閱讀(1332) 評論(0) 收藏舉報

刷新頁面返回頂部