MIgration Testing

26 views

Skip to first unread message

Akshay T

unread,

Aug 28, 2013, 11:15:32 AM8/28/13

to oracle...@googlegroups.com

Suppose we've old_table in MS/SQL Server which is getting migrated to new_table in Oracle.To ensure the consistency & integrity of data we shall carry out following data checks :-

1) Check count of every column both at old & new migrated table to ensure all records have been migrated.

2) Check sum of lengths of all columns which are being migrated from source to target.

But, let us assume that the column_1 in old table has value "Akshay" migrated to column_2 in new table as "Skshay".Accordingly the results for both the steps would confirm that the data has been migrated properly.

If there is no DB link provided between MS SQL Server & Oracle(therefore Minus query also won't work) then how to ensure accurate data migration ?

Thanks & Regards,

Akshay S. Tanksale

ddf

unread,

Sep 3, 2013, 11:47:48 AM9/3/13

to oracle...@googlegroups.com

Let's presume the release of Oracle is 10.2 or later (since you failed to report which release you are using) -- you would dump the table data from SQL Server into a text file and use that as the source for an external table in Oracle. Then, in Oracle you would do a MINUS query. Here is an example using MERGE:

SQL> -- create source table
SQL>
SQL> CREATE TABLE admin_employees
2                     (test_id           NUMBER(4),
3                      test_name         VARCHAR2(25)
4                     );

Table created.

SQL>
SQL> insert into admin_employees
2 values (20, '"Test 3"');

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> -- create the external table
SQL> CREATE TABLE admin_ext_employees
2                     (test_id           NUMBER(4),
3                      test_name         VARCHAR2(25)
4                     )
5       ORGANIZATION EXTERNAL
6       (
7         TYPE ORACLE_LOADER
8         DEFAULT DIRECTORY admin_dat_dir
9         ACCESS PARAMETERS
10         (
11           records delimited by newline
12           badfile admin_bad_dir:'loader_tst%a_%p.bad'
13           logfile admin_log_dir:'loader_tst%a_%p.log'
14           fields terminated by ','
15           missing field values are null
16           ( test_id, test_name
17           )
18         )
19         LOCATION ('loader_tst1.dat', 'loader_tst2.dat')
20 -- LOCATION ('loader_tst1.dat')
21       )
22       PARALLEL
23       REJECT LIMIT UNLIMITED;

Table created.

SQL>
SQL> select * from admin_ext_employees;

   TEST_ID TEST_NAME
---------- -------------------------
         1 "Test 1"
         2 "Test 1"
         3 "Test 1"
         4 "Test 1"
         5 "Test 1"
         6 "Test 1"
         7 "Test 1"
         8 "Test 1"
         9 "Test 1"
        10 "Test 1"
        11 "Test 1"
        12 "Test 1"
        13 "Test 1"

   TEST_ID TEST_NAME
---------- -------------------------
        14 "Test 1"
        15 "Test 1"
        16 "Test 1"
        17 "Test 1"
        18 "Test 1"
        19 "Test 1"
        21 "Test 2"
        22 "Test 2"
        23 "Test 2"
        24 "Test 2"
        25 "Test 2"
        26 "Test 2"
        27 "Test 2"

   TEST_ID TEST_NAME
---------- -------------------------
        28 "Test 2"
        29 "Test 2"
        30 "Test 2"
        31 "Test 2"
        32 "Test 2"
        33 "Test 2"
        34 "Test 2"
        35 "Test 2"
        36 "Test 2"
        37 "Test 2"
        38 "Test 2"
        39 "Test 2"

38 rows selected.

SQL>
SQL> select * from admin_employees;

TEST_ID TEST_NAME
---------- -------------------------
20 "Test 3"

SQL>
SQL> set autotrace on linesize 150
SQL>
SQL> merge into admin_employees a
2          using admin_ext_employees e on (e.test_id = a.test_id)
3 when matched then
4          update
5          set test_name = a.test_name
6 when not matched then
7          insert
8          (test_id, test_name)
9          values (e.test_id, e.test_name);

38 rows merged.

Execution Plan
----------------------------------------------------------
Plan hash value: 1420256803

-----------------------------------------------------------------------------------------------------
| Id | Operation                     | Name                | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT               |                     | 8168 |   542K|    33   (4)| 00:00:01 |
|   1 | MERGE                        | ADMIN_EMPLOYEES     |       |       |            |          |
|   2 |   VIEW                        |                     |       |       |            |          |
|* 3 |    HASH JOIN RIGHT OUTER      |                     | 8168 |   526K|    33   (4)| 00:00:01 |
|   4 |     TABLE ACCESS FULL         | ADMIN_EMPLOYEES     |    39 | 1521 |     3   (0)| 00:00:01 |
|   5 |     EXTERNAL TABLE ACCESS FULL| ADMIN_EXT_EMPLOYEES | 8168 |   215K|    29   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("E"."TEST_ID"="A"."TEST_ID"(+))

Note
-----
- dynamic sampling used for this statement (level=2)
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

Statistics
----------------------------------------------------------
         87 recursive calls
          5 db block gets
        761 consistent gets
          0 physical reads
          0 redo size
        833 bytes sent via SQL*Net to client
        977 bytes received via SQL*Net from client
          3 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
         38 rows processed

SQL>
SQL> select * from admin_employees
2 order by 1;

   TEST_ID TEST_NAME
---------- -------------------------
        14 "Test 1"
        15 "Test 1"
        16 "Test 1"
        17 "Test 1"
        18 "Test 1"
        19 "Test 1"
        20 "Test 3"
        21 "Test 2"
        22 "Test 2"
        23 "Test 2"
        24 "Test 2"
        25 "Test 2"
        26 "Test 2"

   TEST_ID TEST_NAME
---------- -------------------------
        27 "Test 2"
        28 "Test 2"
        29 "Test 2"
        30 "Test 2"
        31 "Test 2"
        32 "Test 2"
        33 "Test 2"
        34 "Test 2"
        35 "Test 2"
        36 "Test 2"
        37 "Test 2"
        38 "Test 2"
        39 "Test 2"

39 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2437798676

--------------------------------------------------------------------------------------
| Id | Operation          | Name            | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |    39 | 1053 |     4 (25)| 00:00:01 |
|   1 | SORT ORDER BY     |                 |    39 | 1053 |     4 (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| ADMIN_EMPLOYEES |    39 | 1053 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=2)
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

Statistics
----------------------------------------------------------
          4 recursive calls
          0 db block gets
         15 consistent gets
          0 physical reads
          0 redo size
       1348 bytes sent via SQL*Net to client
        542 bytes received via SQL*Net from client
          4 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
         39 rows processed

SQL>

You can use MINUS as well since the external table is considered a table by Oracle.