Oracle BLOB migration(Size-600GB) to the Azure Azure SQL Database

Question

Hi All,

I have migrated the a table from the oracle database ,datatype-BLOB and LOB segment size-600 GB.

Table Details-Records Count-312796

Chunk Size-8192

Retention-900

SEGMENT CREATED-YES

LOGGING-YES

IN_ROW -YES

LOB SEGMENT SIZE-600 GB

After the migration of the above table in Azure sql database using the Azure

data factory.

I am getting the below results.

Table Size (Azure Sql Database)-45 GB (approx).

Azure table Count-312796

Distinct Varbinary (Oracle-Blob) column count-312796

In Source-oracle the database size is 600 GB , after migration in Azure Sql database the database size is 45 GB.

There is difference of 550 GB.

How can i be assured/validate the data migration for the above table is complete ?

Please guide.?

Answer 1

Migrating a large Oracle BLOB (Binary Large Object) with a size of 600GB to an Azure SQL Database can be a complex task. Here are some general steps to consider for the migration process:

Assess the Azure SQL Database: Evaluate the Azure SQL Database's capabilities and limitations to ensure it can handle the size and performance requirements of the BLOB data. Consider factors such as maximum database size, maximum file size, and performance considerations.

Data Extraction: Develop a strategy to extract the BLOB data from the Oracle database. You can use Oracle tools like Oracle Data Pump or custom scripts to export the BLOB data as files.

Data Transfer: Transfer the BLOB data files to Azure storage. You can use Azure Blob Storage to store the files temporarily during the migration process. Azure Data Factory or Azure Storage Explorer can help with the data transfer.

Data Import to Azure SQL Database: Depending on the Azure SQL Database's capabilities, you may need to split the BLOB data into smaller chunks or convert it into a different format compatible with Azure SQL Database, such as varbinary(max). Develop a data import strategy, which can include using Azure Data Factory or custom scripts to import the data into the Azure SQL Database.

Validate and Verify: Once the data import is complete, perform thorough validation and verification to ensure the data integrity and accuracy of the migrated BLOB data. Compare the data in the Azure SQL Database with the source Oracle database to ensure consistency.

Testing and Optimization: Conduct thorough testing on the migrated BLOB data in the Azure SQL Database to ensure it meets the performance requirements. Optimize the database and query performance if needed.

Cleanup and Decommissioning: Once you have successfully migrated and validated the BLOB data, clean up any temporary storage or resources used during the migration process. Decommission the source Oracle database if it's no longer required.

It's important to note that the above steps provide a general outline for the migration process. The specifics may vary depending on your environment, data volume, network bandwidth, and other factors. It is recommended to consult Azure SQL Database documentation, seek guidance from Azure support, or engage with a professional services team to ensure a successful migration of a large BLOB dataset.

Answer 2

Total Blob Size is -600 GB.

Usecase-Oracle-Its table A with 20 columns and one of the columns as the BLOB datatype.

Oracle Table Record Count-312796

There are 312796 different blob data instance (blob record count) which makes the total blob memory as 600 GB , this memory is stored in LOB segment of the oracle.

I am migrating the table A with 20 columns and one BLOB datatype column from oracle table to azure sql database using ADF. Will it migrate the complete blob data (600GB) from oracle to azure using the copy activity.

Screenshot attached ?

End Goal-Oracle Table A- With 20 columns and one BLOB datatype column.

is migrated to the Azure sql database table with 20 columns and one varbinary column datatype( storing all blob data of oracle into azure sql database table).

Can blob transfer be done between the Oracle and Azure using the ADF copy activity (mapping section) only for the above mentioned scenario.??

Can it be done through the ADF copy activity only while connection from oracle and azure sql database.