Sparx DBMS to DBMS Project Transfer, ORA-03113 & sqlnet.expire_time

[BruceTOGAF2]

A Sparx DBMS to DBMS Project Transfer (Oracle to SQL Server) was previously successful in our DEVELOPMENT environment, but a recent attempt to migrate PRODUCTION Oracle to PRODUCTION SQL Server has failed.

The project transfer failed between migrating table t_diagramlinks and table t_diagramobjects. Details below.

I suspect that SQL Server was busy building indexes for table t_diagramlinks in SQL Server, then Oracle got tired of waiting for next SQL query on table t_diagramobjects, so Oracle timed out with “ORA-03113: end of file on communication channel”. One of the most frequently reported causes of an ORA-03113 results from the presence of a firewall. This can end the Oracle connection after a certain period of time.

Here are my questions. 
1. Would you recommend utilizing a sqlnet.expire_time parameter? See explanation below. 
2. After target SQL Server table t_diagramlinks has the same record count as the source Oracle table t_diagramlinks, why does it take a long time before any records start to appear in target SQL Server table t_diagramobjects?
3. After target SQL Server table t_diagramlinks has the same record count as the source Oracle table t_diagramlinks, what is happening during that period before any records appear in target SQL Server table t_diagramobjects?
4. After target SQL Server table t_diagramlinks has the same record count as the source Oracle table t_diagramlinks, does the project Transfer process stop talking to the source DB (Oracle) for a period of time before retrieving t_diagramobjects records from the source DB (Oracle)?

Details of Project Transfer

I launched a Sparx Project Transfer (Oracle to SQL Server) on Thursday 22nd August. I monitored progress by counting the records in the target DB and comparing with records in the source DB. The following record counts illustrate the size of the migration task, by listing just some of the major tables in Oracle. On Monday 23rd August at 18:00 (5 days & 4 hours since the launch) the following progress was noted.

·   t_xref (181,891 > 181,891) fully migrated
·   t_authors (270 > 270) fully migrated
·   t_stereotypes (776 > 776) fully migrated
·   t_object (119, 111 > 119,111) fully migrated
·   t_package (6,821 > 6,821) fully migrated
·   t_objectproperties (1,180,808 > 1,180,808) fully migrated
·   t_operation (10,774 > 10,774) fully migrated
·   t_attribute (20,959 > 20,959) fully migrated
·   t_objectscenarios (3,350 > 3,350) fully migrated
·   t_connector (127,944 > 127,944) fully migrated
·   t_diagramlinks (78,764 > 78,764) fully migrated

At this progress rate, it was estimated that migration will finish in the evening of Monday 26th August.
After t_diagramlinks was fully migrated, there was a long pause. Nothing seemed to be happening. Maybe new indexes were being built in t_diagramlinks in SQL Server. Then, before migrating any records to t_diagramobjects, the process crashed.

The following errors were displayed
·   OraOLEDB [-2147467259]
·   ORA-03113: end of file on communication channel     

As instructed by a Sparx EA message, I pressed OK to continue table upgrade, but the session ended with message 'Migration Complete'.  No more data was migrated.

In Oracle SQL Developer I received message 'DB connection has been reset' at the same time as the Sparx migration failed due to 'ORA-03113: end of file on communication channel'.

No records were migrated to the following remaining tables.
·   t_diagramobjects (102,981 > )
·   t_snapshot (43,130 > )

I suspect SQL Server was busy building indexes for table t_diagramlinks in SQL Server, then Oracle got tired of waiting for next SQL query on table t_diagramobjects.
One of the most frequently reported causes of an ORA-03113 results from the presence of a firewall. This can end the Oracle connection after a certain period of time. For this, utilize a sqlnet.expire_time parameter. What this will do is enable the server to detect a dead connection after a specified period of time, and if not connected the server process will exit. This will effectively render the firewall to consider that the connection is still live. 

Our DB Team suggested that Firewall timeouts will terminate inactive sessions after a fixed period. DB server connections are first received on port 1570 , then rerouted to 1571 or 1561 depending on the config. In the firewall, all these ports have max timeout threshold.

sqlnet.expire_time was not set. I have requested a sqlnet.expire_time parameter change to sqlnet.expire_time=1, but the DB team have said this would impact many other systems, so they will not make the change without extensive testing of all systems.

[Geert Bellekens]

I would try Oracle -> .eap(x) -> SQL Server.

I've seen this work faster then SQL Server -> SQL Server direct.

Geert

[Paolo F Cantoni]

I would try Oracle -> .eap(x) -> SQL Server.

I've seen this work faster than SQL Server -> SQL Server direct.

Geert

+1

We have exported ~1GB .eapx from SQL Server in under 10mins.  Reverse direction can take hours, so we don't normally do it.

Paolo

[BruceTOGAF2]

t_objectproperties has 1,180,808 records. This causes DB to exceed .eap size limit, so migrating Oracle > .eap is not an option.

The latest update to this story is the SQL Server CPU has been upgraded (from 4G to 8G) and SQL Server version has been upgraded from 2012 to 2017.  Although this does not address the issue with Oracle/Firewall timeouts, I am testing this new environment by migrating from our DEV Oracle to our new PROD SQL Server.

[Paolo F Cantoni]

t_objectproperties has 1,180,808 records. This causes DB to exceed .eap size limit, so migrating Oracle > .eap is not an option.

The latest update to this story is the SQL Server CPU has been upgraded (from 4G to 8G) and SQL Server version has been upgraded from 2012 to 2017.  Although this does not address the issue with Oracle/Firewall timeouts, I am testing this new environment by migrating from our DEV Oracle to our new PROD SQL Server.

Bruce,

Are you still using Access97 .EAP or Jet4  Access2000?  The repository I mentioned earlier has over 100K t_object and over 500K t_objectproperties rows.  Try switching to .eapx (Jet4) or using the older Jet4 .eap (which you can get from Sparx).

HTH,
Paolo

[BruceTOGAF2]

"over 100K t_object rows" is similar to my t_object rows, but "over 500K t_objectproperties rows" is only half what I have in t_objectproperties.

The latest update to this story is our SQL Server DBA installed the latest version of Microsoft SQL Server Migration Assistant (SSMA). Although SSMA is not supported by Sparx Systems and it did not work for me, our SQL Server DBA sounds confident that (instead of me using Sparx Project Transfer function) he can use SSMA to migrate our PRODUCTION Oracle DB to the new PRODUCTION SQL Server Sparx Repository. He is currently testing with our DEVELOPMENT Oracle DB.

[Geert Bellekens]

I think your DBA might be right. After all, project transfer is nothing more then copying the database table by table, row by row.
Should be rather easy to script that.

Geert

[BruceTOGAF2]

Yes Geert, The DBA was correct about Microsoft SSMA.  After considerable trials and tribulations we have successfully migrated from Oracle to SQL Server 2017 using Microsoft SSMA. Instead of 5 days, the process took less than 3 hours. Performance (response time) is looking much better now that we are using SQL Server instead of Oracle for our Sparx Repository. We plan to go live soon.

[Geert Bellekens]

I think Sparx should take Oracle from the list of compatible databases to host EA repositories.

Too many users start out with Oracle only to discover after months or years that the performance on an Oracle database is terrible.

Geert

[BruceTOGAF2]

We now have a problem with the migrated SQL Server Repository.

When I use Sparx EA to try to create a new package, I get the following error message.

Microsoft Cursor Engine [-2147217887] Multiple-step operation generated errors. Check each status value.

I will start a new post with the above error message.

[philchudley]

This is what is says in the Microsoft web site
Symptoms

If you use ADO to insert a new record through a client-side recordset into a SQL Server table that has a non-nullable datetime field with a default value, you receive the following error message if you do not supply a value for the datetime field:
Run-time error '-2147217887 (80040e21)': Multiple-step operation generated errors. Check each status value.
This error occurs whether you use the OLE DB Provider for SQL Server or the OLE DB Provider for ODBC Drivers. The error message may differ when you use Microsoft Data Access Components (MDAC) version 2.5 Service Pack 1 (SP1) or earlier. This error does not occur with a server-side cursor.
Cause

This error occurs in the Client Cursor Engine when it attempts to convert the value of type DBTYPE_DBTIMESTAMP to DBTYPE_VARIANT.
Resolution

There are several ways to work around this problem:
Use a server-side cursor for the recordset.
Remove the default value that is specified for the field in the database.
Always specify a value for the field when you add a new record.
Status

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.

The workarounds probably won't help.

It might be worth having a look at the tables in SQL Server Management Studio and seeing if any date fields are non-nullable, and then making them nullable

That may work

We have the same problem with updating diagrams, but after ignoring the message a few times, it goes away.

Phil

[BruceTOGAF2]

The reason for migrating from Oracle to SQL Server is to improve Sparx performance in a WAN Architecture. I have successfully migrated to SQL Server by locating Sparx Project Transfer on same machine as SQL Server DB server. I was given this recommendation by Sparx Systems Support in April, but my company told me that was not permitted. Having tried other avenues and failed, I was given permission to temporarily locate Sparx EA on same VM as DB server. Instead of Sparx Project Transfer taking 5 days, it took 5 hours.  Our Sparx Production Repository is now on SQL Server. Performance is much better.

[Geert Bellekens]

Good to hear that you finally got it transferred.

Geert