Menganalisis dependensi objek untuk migrasi database sebagian dari Oracle ke PostgreSQL

Situs web, Amazon Web Services

Ringkasan

Pola ini menjelaskan pentingnya mengidentifikasi dan mengelola dependensi sistem secara sistematis saat memigrasikan sebagian database Oracle ke Amazon Relational Database Service (Amazon RDS) atau Amazon Aurora PostgreSQL. Dalam migrasi sebagian, hanya subset objek database dan data dari database asli yang dimigrasikan, sedangkan database sumber terus beroperasi dan melayani aplikasi yang bergantung pada komponen yang tidak bermigrasi

Anda harus mengidentifikasi dan menganalisis ruang lingkup migrasi saat menangani database skala besar yang memiliki aplikasi yang digabungkan erat dengan dependensi hulu dan hilir. Untuk memulai migrasi sebagian, identifikasi objek lingkup, termasuk tabel, pemicu, tampilan, prosedur tersimpan, fungsi, dan paket. Proses identifikasi ruang lingkup mengikuti pendekatan komprehensif:

Ketika Anda memahami bagaimana bagian-bagian yang berbeda dari sistem Anda berinteraksi, Anda dapat merencanakan urutan yang benar untuk memindahkan komponen database dengan lebih baik dan mengurangi risiko kegagalan migrasi. Tabel berikut mencantumkan berbagai jenis analisis ketergantungan.

Untuk menjaga konsistensi antara sistem sumber dan target, buat mekanisme sinkronisasi data selama masa transisi. Anda juga harus memodifikasi kode aplikasi dan fungsi untuk menangani distribusi data di kedua sumber Oracle dan target database PostgreSQL.

Prasyarat dan batasan

Prasyarat

Versi produk

Arsitektur

Tumpukan teknologi sumber

Tumpukan teknologi target

Arsitektur target

Diagram berikut menunjukkan proses migrasi dari database Oracle lokal ke Amazon RDS for Oracle, yang melibatkan hal-hal berikut:

Untuk informasi selengkapnya, lihat Mengintegrasikan AWS Database Migration Service dengan AWS Schema Conversion Tool dalam AWS dokumentasi.

Alat

Layanan AWS

Layanan lainnya

Praktik terbaik

Untuk praktik terbaik tentang penyediaan dan migrasi database Oracle, lihat Praktik terbaik untuk bermigrasi ke Amazon RDS for Oracle.

Epik

CREATE TABLE REFERENTIAL_ANALYSIS_BASELINE
(CHILD_OWNER VARCHAR2(50 BYTE),
CHILD_NAME VARCHAR2(100 BYTE),
PARENT_OWNER VARCHAR2(50 BYTE),
PARENT_NAME VARCHAR2(50 BYTE),
REFERENCE_PATH VARCHAR2(1000 BYTE));

CREATE TABLE TABLE_READ_OBJECT_DETAILS
(OWNER VARCHAR2(50 BYTE),
TAB_NAME VARCHAR2(50 BYTE),
READER_OWNER VARCHAR2(50 BYTE),
READER_NAME VARCHAR2(50 BYTE),
READER_TYPE VARCHAR2(50 BYTE));

CREATE TABLE TABLE_WRITE_OBJECT_DETAILS
(TABLE_NAME VARCHAR2(100 BYTE),
WRITEOBJ_OWNER VARCHAR2(100 BYTE),
WRITEOBJ_NAME VARCHAR2(100 BYTE),
WRITEOBJ_TYPE VARCHAR2(100 BYTE),
LINE VARCHAR2(100 BYTE),
TEXT VARCHAR2(4000 BYTE),
OWNER VARCHAR2(50 BYTE));

CREATE TABLE OBJECT_PRIVS_ANALYSIS
(OWNER VARCHAR2(50 BYTE),
OBJECT_NAME VARCHAR2(50 BYTE),
USER_NAME VARCHAR2(50 BYTE),
PRIVS VARCHAR2(50 BYTE));

Penyelesaian Masalah

Sumber daya terkait

Dokumentasi AWS

Dokumentasi lainnya

Informasi tambahan

Script untuk DEPENDENT_ANALYSIS_BASELINE

CREATE TABLE DEPENDENT_ANALYSIS_BASELINE
(OWNER VARCHAR2(128 BYTE) NOT NULL ENABLE,
OBJECT_NAME VARCHAR2(128 BYTE) NOT NULL ENABLE,
OBJECT_TYPE VARCHAR2(20 BYTE),
DEPEDNCY_LEVEL NUMBER,
PROJECT_NEED VARCHAR2(20 BYTE),
CATAGORY VARCHAR2(4000 BYTE),
COMMENTS VARCHAR2(4000 BYTE),
CATAGORY1 CLOB,
COMMENTS1 CLOB,
CUSTOMER_COMMENTS VARCHAR2(1000 BYTE),
BACKFILL_TO_GUS VARCHAR2(1000 BYTE),
BACKFILL_NEAR_REAL_TIME_OR_BATCH VARCHAR2(1000 BYTE),
PK_EXISTS VARCHAR2(3 BYTE),
UI_EXISTS VARCHAR2(3 BYTE),
LOB_EXISTS VARCHAR2(3 BYTE),
MASTER_LINK VARCHAR2(100 BYTE),
CONSTRAINT PK_DEPENDENT_ANALYSIS_BASELINE PRIMARY KEY (OWNER,OBJECT_NAME,OBJECT_TYPE));

Prosedur untuk SP_WRITER_OBJECTS_ANALYSIS

CREATE OR REPLACE PROCEDURE SP_WRITER_OBJECTS_ANALYSIS IS
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE TABLE_WRITE_OBJECT_DETAILS';
  FOR I IN (SELECT OWNER, OBJECT_NAME FROM DEPENDENT_ANALYSIS_BASELINE WHERE OBJECT_TYPE = 'TABLE')
  LOOP
    INSERT INTO TABLE_WRITE_OBJECT_DETAILS(OWNER, TABLE_NAME, WRITEOBJ_OWNER, WRITEOBJ_NAME, WRITEOBJ_TYPE, LINE, TEXT)
    SELECT DISTINCT I.OWNER, I.OBJECT_NAME, OWNER WRITEOBJ_OWNER, NAME, TYPE, LINE, TRIM(TEXT)
    FROM DBA_SOURCE 
    WHERE UPPER(TEXT) LIKE '%' || I.OBJECT_NAME || '%'
      AND (UPPER(TEXT) LIKE '%INSERT%' || I.OBJECT_NAME || '%' 
        OR UPPER(TEXT) LIKE '%UPDATE%' || I.OBJECT_NAME || '%' 
        OR UPPER(TEXT) LIKE '%DELETE%' || I.OBJECT_NAME || '%' 
        OR UPPER(TEXT) LIKE '%UPSERT%' || I.OBJECT_NAME || '%' 
        OR UPPER(TEXT) LIKE '%MERGE%' || I.OBJECT_NAME || '%') 
      AND UPPER(TEXT) NOT LIKE '%PROCEDURE%' 
      AND UPPER(TEXT) NOT LIKE 'PROCEDURE%' 
      AND UPPER(TEXT) NOT LIKE '%FUNCTION%' 
      AND UPPER(TEXT) NOT LIKE 'FUNCTION%'
      AND UPPER(TEXT) NOT LIKE '%TRIGGER%' 
      AND UPPER(TEXT) NOT LIKE 'TRIGGER%' 
      AND UPPER(TRIM(TEXT)) NOT LIKE '%AFTER UPDATE%' 
      AND UPPER(TRIM(TEXT)) NOT LIKE 'BEFORE UPDATE%' 
      AND UPPER(TRIM(TEXT)) NOT LIKE 'BEFORE INSERT%' 
      AND UPPER(TRIM(TEXT)) NOT LIKE 'AFTER INSERT%' 
      AND UPPER(TRIM(TEXT)) NOT LIKE 'BEFORE DELETE%' 
      AND UPPER(TRIM(TEXT)) NOT LIKE 'AFTER DELETE%' 
      AND UPPER(TRIM(TEXT)) NOT LIKE '%GGLOGADM.GG_LOG_ERROR%' 
      AND (TRIM(TEXT) NOT LIKE '/*%' AND TRIM(TEXT) NOT LIKE '--%' ) 
      AND (OWNER, NAME, TYPE) IN (SELECT OWNER, NAME, TYPE FROM DBA_DEPENDENCIES WHERE REFERENCED_NAME = I.OBJECT_NAME);
  END LOOP;
END;

Script untuk SP_READER_OBJECTS_ANALYSIS

CREATE OR REPLACE PROCEDURE SP_READER_OBJECTS_ANALYSIS IS
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE TABLE_READ_OBJECT_DETAILS';
  FOR I IN (SELECT OWNER, OBJECT_NAME FROM DEPENDENT_ANALYSIS_BASELINE WHERE OBJECT_TYPE = 'TABLE')
  LOOP
    INSERT INTO TABLE_READ_OBJECT_DETAILS
    SELECT DISTINCT i.owner, i.object_name, owner, name, type 
    FROM dba_dependencies 
    WHERE referenced_name = I.OBJECT_NAME
    AND referenced_type = 'TABLE' 
    AND type NOT IN ('SYNONYM', 'MATERIALIZED VIEW', 'VIEW') 
    AND (owner, name, type) NOT IN (
      SELECT DISTINCT owner, trigger_name, 'TRIGGER' 
      FROM dba_triggers 
      WHERE table_name = I.OBJECT_NAME 
      AND table_owner = i.owner
      UNION ALL
      SELECT DISTINCT owner, name, type 
      FROM dba_source
      WHERE upper(text) LIKE '%' || I.OBJECT_NAME || '%' 
      AND (upper(text) LIKE '%INSERT %' || I.OBJECT_NAME || '%' 
        OR upper(text) LIKE '%UPDATE% ' || I.OBJECT_NAME || '%' 
        OR upper(text) LIKE '%DELETE %' || I.OBJECT_NAME || '%' 
        OR upper(text) LIKE '%UPSERT %' || I.OBJECT_NAME || '%' 
        OR upper(text) LIKE '%MERGE %' || I.OBJECT_NAME || '%') 
      AND upper(text) NOT LIKE '%PROCEDURE %' 
      AND upper(text) NOT LIKE 'PROCEDURE %'
      AND upper(text) NOT LIKE '%FUNCTION %' 
      AND upper(text) NOT LIKE 'FUNCTION %'
      AND upper(text) NOT LIKE '%TRIGGER %'
      AND upper(text) NOT LIKE 'TRIGGER %'
      AND upper(trim(text)) NOT LIKE 'BEFORE INSERT %'
      AND upper(trim(text)) NOT LIKE 'BEFORE UPDATE %' 
      AND upper(trim(text)) NOT LIKE 'BEFORE DELETE %' 
      AND upper(trim(text)) NOT LIKE 'AFTER INSERT %' 
      AND upper(trim(text)) NOT LIKE 'AFTER UPDATE %' 
      AND upper(trim(text)) NOT LIKE 'AFTER DELETE %' 
      AND (trim(text) NOT LIKE '/*%' AND trim(text) NOT LIKE '--%'));
  END LOOP;
END;

Script untuk SP_OBJECT_REFERENTIAL_ANALYSIS

CREATE OR REPLACE PROCEDURE SP_OBJECT_REFERENTIAL_ANALYSIS IS
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE REFERENTIAL_ANALYSIS_BASELINE';
  INSERT INTO REFERENTIAL_ANALYSIS_BASELINE
  WITH rel AS (
    SELECT DISTINCT c.owner, c.table_name, c.r_owner r_owner,
      (SELECT table_name FROM dba_constraints 
       WHERE constraint_name = c.r_constraint_name 
       AND owner = c.r_owner) r_table_name 
    FROM dba_constraints c 
    WHERE constraint_type = 'R' 
    AND c.owner NOT IN (SELECT username FROM dba_users WHERE oracle_maintained = 'Y')
    AND c.r_owner NOT IN (SELECT username FROM dba_users WHERE oracle_maintained = 'Y')),
  tab_list AS (
    SELECT OWNER, object_name 
    FROM DEPENDENT_ANALYSIS_BASELINE 
    WHERE UPPER(OBJECT_TYPE) = 'TABLE')
  SELECT DISTINCT owner child_owner, table_name child, r_owner parent_owner,
    r_table_name parent, SYS_CONNECT_BY_PATH(r_table_name, ' -> ') || ' -> ' || table_name PATH
  FROM rel 
  START WITH (r_owner, r_table_name) IN (SELECT * FROM tab_list)
  CONNECT BY NOCYCLE (r_owner, r_table_name) = ((PRIOR owner, PRIOR table_name))
  UNION
  SELECT DISTINCT owner child_owner, table_name child, r_owner parent_owner,
    r_table_name parent, SYS_CONNECT_BY_PATH(table_name, ' -> ') || ' -> ' || r_table_name PATH
  FROM rel 
  START WITH (owner, table_name) IN (SELECT * FROM tab_list)
  CONNECT BY NOCYCLE (owner, table_name) = ((PRIOR r_owner, PRIOR r_table_name));
END;

Script untuk SP_OBJECT_PRIVS_ANALYSIS

CREATE OR REPLACE PROCEDURE SP_OBJECT_PRIVS_ANALYSIS IS
  V_SQL VARCHAR2(4000);
  V_CNT NUMBER;
BEGIN
  V_SQL := 'TRUNCATE TABLE OBJECT_PRIVS_ANALYSIS';
  EXECUTE IMMEDIATE V_SQL;
  FOR I IN (SELECT OWNER, OBJECT_NAME FROM DEPENDENT_ANALYSIS_BASELINE WHERE OBJECT_TYPE = 'TABLE')
  LOOP
    INSERT INTO OBJECT_PRIVS_ANALYSIS(OWNER, OBJECT_NAME, USER_NAME, PRIVS)
    WITH obj_to_role AS (
      SELECT DISTINCT GRANTEE role_name, 
        DECODE(privilege, 'SELECT', 'READ', 'REFERENCE', 'READ', 'INSERT', 'WRITE', 
               'UPDATE', 'WRITE', 'DELETE', 'WRITE', privilege) privs
      FROM DBA_TAB_PRIVS t, DBA_ROLES r 
      WHERE OWNER = I.OWNER 
      AND TYPE = 'TABLE' 
      AND TABLE_NAME = I.OBJECT_NAME 
      AND t.GRANTEE = r.ROLE 
      AND r.ROLE IN (SELECT ROLE FROM DBA_ROLES WHERE ORACLE_MAINTAINED = 'N')
    )
    SELECT I.OWNER, I.OBJECT_NAME, grantee, privs 
    FROM (
      -- Recursively Role to User mapping with privilege
      SELECT DISTINCT grantee, privs 
      FROM (SELECT rp.granted_role, rp.grantee, privs,
        (SELECT DECODE(COUNT(*), 0, 'ROLE', 'USER') 
         FROM (SELECT 'User' FROM DBA_users WHERE username = rp.GRANTEE)) grantee_type 
        FROM DBA_role_privs rp, obj_to_role r 
        WHERE rp.granted_role = r.role_name 
        AND grantee IN ((SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED = 'N') 
                       UNION (SELECT ROLE FROM DBA_ROLES WHERE ORACLE_MAINTAINED = 'N'))
        AND granted_role IN (SELECT ROLE FROM DBA_ROLES WHERE ORACLE_MAINTAINED = 'N') 
        START WITH granted_role IN (SELECT DISTINCT role_name FROM obj_to_role) 
        CONNECT BY granted_role = PRIOR grantee) 
      WHERE grantee_type = 'USER'
    )
    UNION
    (
      -- Direct Object grants to User
      SELECT I.OWNER, I.OBJECT_NAME, GRANTEE, 
        DECODE(privilege, 'SELECT', 'READ', 'REFERENCE', 'READ', 'INSERT', 'WRITE',
               'UPDATE', 'WRITE', 'DELETE', 'WRITE', privilege) privs 
      FROM DBA_TAB_PRIVS, DBA_USERS 
      WHERE GRANTEE = USERNAME 
      AND OWNER = I.OWNER 
      AND TYPE = 'TABLE' 
      AND TABLE_NAME = I.OBJECT_NAME
    ) 
    ORDER BY 2 DESC;
  END LOOP;
END;

Prosedur untuk SP_OBJECT_DEPENDENCY_ANALYSIS

CREATE OR REPLACE PROCEDURE SP_OBJECT_DEPENDENCY_ANALYSIS (v_level NUMBER) IS
  TYPE typ IS RECORD (
    schema VARCHAR2(100),
    obj_type VARCHAR2(100),
    obj_name VARCHAR2(100),
    path VARCHAR2(5000)
  );
  TYPE array IS TABLE OF typ;
  l_data array;
  c SYS_REFCURSOR;
  l_errors NUMBER;
  l_errno NUMBER;
  l_msg VARCHAR2(4000);
  l_idx NUMBER;
  l_level NUMBER;
BEGIN
  l_level := v_level + 1;
  OPEN c FOR 
    WITH obj_list AS (
      SELECT owner schema_name, object_type, object_name 
      FROM DEPENDENT_ANALYSIS_BASELINE 
      WHERE depedncy_level = v_level
    ),
    fw_dep_objects AS (
      SELECT level lvl, owner, name, type, referenced_owner, referenced_name,
        referenced_type, SYS_CONNECT_BY_PATH(name, ' -> ') || ' -> ' || referenced_name PATH 
      FROM dba_dependencies
      START WITH (owner, CASE WHEN type = 'PACKAGE BODY' THEN 'PACKAGE' ELSE type END, name) 
        IN (SELECT schema_name, object_type, object_name FROM obj_list)
      CONNECT BY NOCYCLE (owner, type, name) = 
        ((PRIOR referenced_owner, PRIOR referenced_type, PRIOR referenced_name))
    ),
    bw_dep_objects AS (
      SELECT level lvl, owner, name, type, referenced_owner, referenced_name,
        referenced_type, SYS_CONNECT_BY_PATH(name, ' <- ') || ' <- ' || referenced_name PATH 
      FROM dba_dependencies
      START WITH (referenced_owner, CASE WHEN referenced_type = 'PACKAGE BODY' THEN 'PACKAGE' 
        ELSE referenced_type END, referenced_name) IN (SELECT schema_name, object_type, object_name FROM obj_list)
      CONNECT BY NOCYCLE (referenced_owner, referenced_type, referenced_name) = 
        ((PRIOR owner, PRIOR type, PRIOR name))
    )
    SELECT * FROM (
      (SELECT DISTINCT referenced_owner schema, referenced_type obj_type, 
        referenced_name obj_name, path FROM fw_dep_objects)
      UNION
      (SELECT DISTINCT owner schema, type obj_type, name obj_name, path 
       FROM bw_dep_objects)
    )
    WHERE schema IN (SELECT username FROM all_users WHERE oracle_maintained = 'N')
    ORDER BY obj_type;

  LOOP
    FETCH c BULK COLLECT INTO l_data LIMIT 100;
    BEGIN
      FORALL i IN 1..l_data.count SAVE EXCEPTIONS
        INSERT INTO DEPENDENT_ANALYSIS_BASELINE (
          owner, object_name, object_type, catagory, depedncy_level, project_need, comments
        ) 
        VALUES (
          l_data(i).schema, 
          l_data(i).obj_name,
          CASE WHEN l_data(i).obj_type = 'PACKAGE BODY' THEN 'PACKAGE' ELSE l_data(i).obj_type END,
          'level ' || l_level || ' dependency',
          l_level,
          '',
          'from dependency proc' || l_data(i).path
        );
    EXCEPTION
      WHEN OTHERS THEN
        l_errors := sql%bulk_exceptions.count;
        FOR i IN 1..l_errors LOOP
          l_errno := sql%bulk_exceptions(i).error_code;
          l_msg := SQLERRM(-l_errno);
          l_idx := sql%bulk_exceptions(i).error_index;
          UPDATE DEPENDENT_ANALYSIS_BASELINE 
          SET catagory1 = catagory1 || ', found in level' || l_level || ' dependent of ' || l_data(l_idx).path,
              comments1 = comments1 || ', from dependency proc exception ' || l_data(i).path
          WHERE owner = l_data(l_idx).schema 
          AND object_name = l_data(l_idx).obj_name 
          AND object_type = l_data(l_idx).obj_type;
        END LOOP;
    END;
    EXIT WHEN c%NOTFOUND;
  END LOOP;
  CLOSE c;
END;