Oracle to Azure Sql Database Migration using the Azure Data factory

Question

Hi, I am migrating oracle to Azure sql database. While migrating one of the oracle table with the data type at LOB segment , I am getting the below error. Error Details Failure happened on 'Source' side. ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [HY000] [Microsoft][ODBC Oracle Wire Protocol driver][Oracle]ORA-22288: file or LOB operation failed ERROR [HY000] [Microsoft][ODBC Oracle Wire Protocol driver][Oracle]ORA-01555: snapshot too old: rollback segment number with name "" too small ORA-22924: snapshot too old ,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=System.Data.Odbc.OdbcException,Message=ERROR [HY000] [Microsoft][ODBC Oracle Wire Protocol driver][Oracle]ORA-22288: file or LOB operation failed ERROR [HY000] [Microsoft][ODBC Oracle Wire Protocol driver][Oracle]ORA-01555: snapshot too old: rollback segment number with name "" too small ORA-22924: snapshot too old ,Source=msora28.dll,' Please help in the resolution of the above error and fix i need to perform in the oracle database.

Answer 1

Hi Rahul, Thanks for reaching out to Microsoft Q&A.

The error seems to be from the source(oracle) side, not from ADF. Lets try to break down the error and the possible workarounds to fix them.

ORA-22288: File or LOB Operation FILEOPEN Failed: Error occurs when attempting to open a file or LOB (Large Object) in Oracle, but the filename, directory name, or volume label syntax is incorrect. May be due to incorrect directory path or filename, Insufficient privileges or even bugs related to Windows and DBMS_LOB manipulation. To fix this... verify that the file or LOB exists; Check the directory path and ensure it’s correct; Confirm that necessary privileges are set for the operation.

ORA-01555: Snapshot Too Old: Occurs during read consistency when a query tries to access a version of data that has been overwritten by other transactions; May be due to insufficient undo retention (controlled by the UNDO_RETENTION parameter) and small rollback segments. To fix... Increase the UNDO_RETENTION parameter, using larger rollback segments or by ensuring cursors are closed after FETCH and UPDATE statements.

ORA-22924: Snapshot Too Old: Similar to the previous error, this error indicates that the transaction’s snapshot is too old. Also remember to verify your configuration, and permissions, and undo retention settings

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.

Answer 2

In my opinion, please increase the size of the Undo retention (on the Oracle side) because it seems there have been too many changes/transformations, the migrations seem to be taken too much time, and Oracle use the Undo retention for any possible rollback. Be aware that if you increase the Undo retention size you need also to increase the Undo table space size. I know there is a formula for that, but I don't have that formula (to calculate the table space size) with me.