Migrate the Data

You can migrate the data in one of two ways: online or offline.

Online Data Move: You can create a connection from within Oracle SQL Developer to your Microsoft SQL Server source database and migrate the data.
Offline Data Move: You can export the data from Microsoft SQL Server. SQL Developer will create a series of BCP and sqlldr files that you can run from a batch file.

Online data moves are suitable for small data sets, whereas offline data moves are useful for moving large volumes of data. Furthermore, in cases of large volumes of data, it is recommended to use Oracle Cloud Infrastructure File Storage to help overall performance of data movement during migration process.

Migrate the Data Offline

During the capture process, the offline data move script would have been generated and the data would have been extracted into the project output directory named datamove. The execution of these scripts is what migrates the data from sql server windows host machine to Oracle Autonomous Database.

The datamove directory should look like this:

Wkdir
	projectName
		datamove
			YYYY-MM-DD_HH-MI-SS
				AdventureWorks

Inside the AdventureWorks directory, you should see six subfolders that correspond to all users under SQL Server AdventureWorks database.

You should see the following directories in the AdventureWorks folder:

ADVENTUREWORKS
SALES_ADVENTUREWORKS
PERSON_ADVENTUREWORKS
PURCHASING_ADVENTUREWORKS
HUMANRESOURCES_ADVENTUREWORKS
PRODUCTION_ADVENTUREWORKS

You should see the following files in the AdventureWorks folder:

MicrosoftSQLServer_data.bat
MicrosoftSQLServer_data.sh
oracle_external.bat
oracle_external.sh
oracle_loader.bat
oracle_loader.sh

The script MicrosoftSqlServer_data.bat is the master Windows system batch script that calls other batch scripts created within each of the schema directories. Here is sample execution:

Launch the MicrosoftSqlServer_data.bat script on the source Windows system prompt. At the prompt, change to the directory and enter the Windows server hostname, Windows system administrator account (usually sa), and the SA password.
```
prompt> cd "C:\_rkd\_OCI\DB-Migration\Non-Oracle\MS-SQL\wkdir\Capture\sqladvworks4\datamove\YYYY-MM-DD_HH-MI-SS\AdventureWorks\“
prompt> MicrosoftSQLServer_data.bat sql-server-hostname sa sa-password
```
During the above execution, the respective logs are generated for each database table by schema and stored in each of the schema folders under subdirectory log.

(Optional) Review these logs either during the migration of data or after the completion, and validate the data movement.

It is highly recommended to review the logs and validate the data migration. The following is a sample log, with the summary of load in bold.

SQL*Loader: Release 19.0.0.0.0 - Production on Thu Mar 4 07:39:44 2021
Version 19.10.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

Control File:   control\AdventureWorks.DatabaseLog.ctl
Data File:      data/AdventureWorks.DatabaseLog.dat
  File processing option string: "str '<EORD>'"
  Bad File:     control\AdventureWorks.DatabaseLog.bad
  Discard File:  none specified
 
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     250 rows, maximum of 1048576 bytes
Continuation:    none specified
Path used:      Conventional

Table ADVENTUREWORKS.DATABASELOG, loaded from every logical record.
Insert option in effect for this table: INSERT
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DATABASELOGID                       FIRST     *           CHARACTER            
    Terminator string : '<EOFD>'
POSTTIME                             NEXT     *           CHARACTER            
    Terminator string : '<EOFD>'
    SQL string for column : "TO_TIMESTAMP(:PostTime, 'yyyy-mm-dd HH24:mi:ss.ff3')"
DATABASEUSER                         NEXT     *           CHARACTER            
    Terminator string : '<EOFD>'
    SQL string for column : "DECODE(:DatabaseUser, CHR(00), ' ', :DatabaseUser)"
EVENT                                NEXT     *           CHARACTER            
    Terminator string : '<EOFD>'
    SQL string for column : "DECODE(:Event, CHR(00), ' ', :Event)"
SCHEMA                               NEXT     *           CHARACTER            
    Terminator string : '<EOFD>'
    SQL string for column : "DECODE(:Schema, CHR(00), ' ', :Schema)"
OBJECT                               NEXT     *           CHARACTER            
    Terminator string : '<EOFD>'
    SQL string for column : "DECODE(:Object, CHR(00), ' ', :Object)"
TSQL                                 NEXT *****           CHARACTER            
    Maximum field length is 2000000
    Terminator string : '<EOFD>'
XMLEVENT                             NEXT *****           CHARACTER            
    Maximum field length is 2000000
    Terminator string : '<EOFD>'
value used for ROWS parameter changed from 250 to 1

Table ADVENTUREWORKS.DATABASELOG:
  1596 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                4001572 bytes(1 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:          1596
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Thu Mar 04 07:39:44 2021
Run ended on Thu Mar 04 07:39:56 2021

Elapsed time was:     00:00:12.44
CPU time was:         00:00:01.79

Test the Migration

Depending on the complexity of the database being migrated, a large part of the migration project might be testing.

Oracle does not provide any tools that can automatically validate a migrated database for you. This phase of the migration should be planned and taken into account before the migration begins. The following should be part of your project plan:

Verify the database structure.
Verify the database security.
Verify the data.
Verify the logical correctness of views.
Test the applications.