Oracle to SQL Server migration issue in copying 'sysdb' database from one to another environment

Question

Hi Experts,

Need your advise on one issue which I am facing in migrated data. I have succesfully migrated database from Oracle to SQL Server. Now I have just copy the migrated database from one environment (DEV) to another environment (QA), along with this I copied the "sysdb" database (which was created during SSMA installation in DEV) to QA as well.

But while running the application in QA we getting error on stored procedure "master..xp_ora2ms_exec2 ". Is this object created in master database while installaion of SSMA tool?. Should I create this object in master database?

Can anyone suggest what going wrong? can't we just copy database from one SQL Server evnironment to another ? I don't think we should install the SSMA tool in DEV/QA/PROD and repeate migration everytime.

Error Details:

####<May 24, 2012 2:35:10 AM EDT> <Warning> <org.hibernate.util.JDBCExceptionReporter> <NDCVWEBD01> <AdminServer> <[ACTIVE] ExecuteThread: '4' for queue: 'weblogic.kernel.Default (self-tuning)'> <c7579> <BEA1-110AD6ACA3A53057A4BD> <> <1337841310126> <BEA-000000> <SQL Error: 2812, SQLState: HY000>

####<May 24, 2012 2:35:10 AM EDT> <Error> <org.hibernate.util.JDBCExceptionReporter> <NDCVWEBD01> <AdminServer> <[ACTIVE] ExecuteThread: '4' for queue: 'weblogic.kernel.Default (self-tuning)'> <c7579> <BEA1-110AD6ACA3A53057A4BD> <> <1337841310126> <BEA-000000> <[OWLS][SQLServer JDBC Driver][SQLServer]Could not find stored procedure 'master..xp_ora2ms_exec2'.>

####<May 24, 2012 2:35:10 AM EDT> <Warning> <com.XYZ.cont.data.util.HibernateBase> <NDCVWEBD01> <AdminServer> <[ACTIVE] ExecuteThread: '4' for queue: 'weblogic.kernel.Default (self-tuning)'> <c7579> <BEA1-110AD6ACA3A53057A4BD> <> <1337841310173> <BEA-000000> <could not insert: [com.XYZ.cont.data.dto.LoginAuditDto]: com.XYZ.cont.data.dto.LoginAuditDto@2a0e0cd[

Thanks for the help.

Answer 1

Can anyone suggest what going wrong? can't we just copy database from one SQL Server evnironment to another ? I don't think we should install the SSMA tool in DEV/QA/PROD and repeate migration everytime.

Hi rajan,

You could detach the databases from DEV and attach them on QA.  Or use backup and restore option. Please make sure if xp_ora2ms_exec2  is in DEV DB. For More information please refer to Move System Databases.

---

Best Regards,
Peja

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 2

We finaly come up with an approach of  installing the "SSMA for Oracle Extension pack" in QA and PROD ,which actualy create all these objects (sysdb and extended stored procedures in master database)which are required for Emulating Oracle sequences in SQL Server. Then by just copying migrated data from DEV to QA and PROD things worked.

But I would like to see in future versions of SSMA that , once data is migrated to SQL Server there should be no need of copying these objects. Microsoft should come up with some solution so that there is no dependecy of these objects and migrated data can be moved independently from one server to other.

regards,

Rajan Singh