Question about using Oracle Migration Workbench

[lydiattc]

Hi,

I'm very new to OMWB. Just installed it a couple days ago and tried to migrate a MSSQL database to Oracle.

After the first time I ran it, it seemed to be successful, but I couldn't see any tables in the destination database using OEM. When I tried to run it the second time (or any time after that), it gave me hundreds of errors because it found the destination database already exists. I don't know how to delete it and restart again, and I wonder where I can view the migrated database.

Thanks,
Lydia

 

[Turkbear]

Hi,
Try using SqlPlus to see the tables:
Select table_name from all_tables;

Be sure to be logged in as the same user that created the tables..

Let us know what results or errors , if any, you get with that attempt..

 

[lydiattc]

I tried to use SQL*Plus. If I use the command you listed, it gave me 922 rows, most of which are system tables. I modified the query and gave it a WHERE clause so that it should give me some tables that are in the source db, but no rows returned.

I used another query to list all the table names from only user_tables. It gave me four tables, which had been created for testing purpose before the migration took place.

Anything else I can try?

Thanks.

 

[Turkbear]

Hi, in your where clause use
where owner not in ('SYS','SYSTEM')

( Unless you used one of those to migrate the tables...)

Look for lowercase characters if any table_names are listed..

If you haven't browsed the docs on OTN yet, give them a try, may be some troubleshooting tips there

http://otn.oracle.com/tech/migration/workbench/content.html

You may need to register, do so, its free and this is very useful site for lots of Oracle stuff.

 

[lydiattc]

Thank you, Turkbear. I tried your query. It returned 451 rows. None of them is the migrated table.

I tried OTN MWB documentations. No luck. In all the documentation, there's no description on how to retrieve the migrated tables. Very odd.

I have also post a message on the forum under OTN's MWB discussion group. Nobody replied yet.

Thanks.

 

[sybaseguru]

Lydia,

The migration process involves two distinct stages. First capturing the source model and then generating an oracle database of your sql server database. I think what has happened is that you have generated the source model without actually doing the database migration (second stage)

I have done this with sybase to oracle migration which uses the same migration workbench for both sybase and MSSQL. This is the sequence of events:

The oracle migration workbench OMW asks you for an orcale username. This is user "repuser" in your oracle database. It immediately creates an OMW repository there and then a wizzard migrates your sybase/sql server sys tables to oracle. So if you log in as user "repuser" and do a "select table_name from user_tables you will get something like:
repuser@MYDB.WORLD> select table_name from user_tables;

TABLE_NAME
------------------------------
MTG_COL_DEP_CHG
MTG_DATABASES
MTG_DEPENDENCY
MTG_DEPENDENCY_MESSAGES
MTG_DEPENDENCY_REL
MTG_DROP_PLUGIN
MTG_ENTITY_DEP_CHG
MTG_GENERAL_PREF
MTG_LAST_OPEN_PROJECT
MTG_LOG_COLUMN_DISPLAY_PREF
MTG_LOG_TABLE
MTG_LONG_TABLE
MTG_MESSAGE_TYPE_DISPLAY_PREF
MTG_PLUGIN_MENUITEMS
MTG_PREF_HANDLER_TABLE
MTG_PROJECTS
MTG_PROJECT_ATTRIBUTES
MTG_PROJECT_STATES
MTG_SESSION_TABLE
MTG_SOURCE_DATA_TYPE_MAP
MTG_SOURCE_LOAD
MTG_SYSIDXCOL
MTG_SYSPRIMKEY
MTG_TREES
MTG_TREE_NODES
MTG_VERSION
OM_ALL_INDEXES_INFO
OM_ALL_TABLES_INFO
OM_ALL_USER_INFO
OM_CHECK_INFO
OM_COMMAND_GENERATOR
OM_CONTROL_GENERATOR
OM_DATATYPE_INFO
OM_DDLTEXT
OM_DEF_INFO
OM_FORN_KEY_INFO
OM_FUNCTION_INFO
OM_GLOBAL_AREA_PKG
OM_IDX_COL_INFO
OM_INDEX_INFO
OM_JAVA_TEXT
OM_LIST_GENERATOR
OM_LITE_DATABASES
OM_OBJ_INFO
OM_ORA_RESERVED_WORDS
OM_ORA_SYSTEM_PRIVILEGE_MAP
OM_PARAMETER_LIST
OM_PRIM_KEY_INFO
OM_PRO_C_SRC_INFO
OM_ROLE_INFO
OM_ROLE_PRIV
OM_ROLE_USER_INFO
OM_SEQ_INFO
OM_SQLTEXT_INFO
OM_SRC_ORACLE_SYSPRIV_MAP
OM_STATEMENT_GENERATOR
OM_SYN_INFO
OM_TABLESPACES
OM_TABLESPACE_DETAILS
OM_TBL_COL_INFO
OM_TBL_INFO
OM_TEMPTABLES
OM_TRIGGER_INFO
OM_USER_INFO
OM_USER_PRIV
OM_USER_SYSTEM_PRIV
SYB12_PARSE_OPTIONS_PREF
SYBASE12_SYSALTERNATES
SYBASE12_SYSCOLUMNS
SYBASE12_SYSCOMMENTS
SYBASE12_SYSCONFIGURES
SYBASE12_SYSCONSTRAINTS
SYBASE12_SYSDATABASES
SYBASE12_SYSDEPENDS
SYBASE12_SYSDEVICES
SYBASE12_SYSINDEXES
SYBASE12_SYSKEYS
SYBASE12_SYSLANGUAGES
SYBASE12_SYSLOGINS
SYBASE12_SYSOBJECTS
SYBASE12_SYSPROCEDURES
SYBASE12_SYSPROTECTS
SYBASE12_SYSREFERENCES
SYBASE12_SYSSEGMENTS
SYBASE12_SYSSPTVALUES
SYBASE12_SYSTYPES
SYBASE12_SYSUSAGES
SYBASE12_SYSUSERS

88 rows selected.

Once the source database is successfully captured, the wizzard will then try to create sql server schema (for a given MSSQL database) in oracle database. However the objects will be created under oracle user "SA" (aauming that all worked OK)!. You should be able to see this in the OWB screen under "Oracle model" or just log on to oracle via SQL*Plus as user "SA" (you may need to reset the password for this user, log in as system and reset SA password) and then do
select substr(object_name,1,30),created from user_objects;

You should be able to see all your MSSQL objects for a given MSSQL database in Oracle.

Good luck and hope this helps

 

[lydiattc]

Thanks, sybaseguru.

I knew there were two stages and I thought I did both of them. However, there might have been something going wrong.

Here's what I have done.

1. Captured the source database successfully. It returned 83 rows from your first query.
2. Migrated to Oracle using the wizard. There were some errors, but I saw one tablespace available in the Oracle model tab.
3. I went to OEM to look for the migrated tables. I couldn't find anything.
4. I thought there must be something wrong, so I wanted to re-migrate.
5. Re-run the migrate wizard, then I received error saying the destination database already exists. I choose to ignor the error and ran the migration to Oracle again.
6. After re-ran migration, the icon next to the tablespace which had been created before re-ran the migration was covered by a red square with a red cross in it. This seemed to be an error sign to me.
7. I deleted the tablespaces and users (there were only one tablespace and one user) in the Oracle Model tab in MWB. And created oracle model again.
8. This time, I saved all the report and error messages. There were 486 errors and the report says all object failed. However, there's a new tablespace and a new user being created in the Oracle Model tab.
9. I ran your second query from user_objects. It returned no rows. And it seems like the SA user did no exist until I tried to set a password to it. I had to create this user SA.
10. Conclusion: I think you are right that the database didn't get migrated at all.

Here are a few messages I received. I think it has something to do with the owner of all the tables. However, I'm not sure what I should do. Please help!

"
Type: Debug
Time: 23-07-2003 11:31:57
Phase: Creating
Message: EXCEPTION :OracleModelImpl._insertObjInfo() java.sql.SQLException: ORA-01400: cannot insert NULL into ("REPUSER"."OM_OBJ_INFO"."OBJ_OWNER&quot


Type: Error
Time: 23-07-2003 11:31:57
Phase: Creating
Message: failed to map table : necoms.null.null;oracle.mtg.migration.MigrationSQLException: ORA-01400: cannot insert NULL into ("REPUSER"."OM_OBJ_INFO"."OBJ_OWNER&quot
:ORA-01400: cannot insert NULL into ("REPUSER"."OM_OBJ_INFO"."OBJ_OWNER&quot

Type: Debug
Time: 23-07-2003 11:31:57
Phase: Creating
Message: EXCEPTION :OracleModelImpl._truncateTblColInfo() ORA-00903: invalid table name


Type: Error
Time: 23-07-2003 11:31:57
Phase: Creating
Message: null : Table. Owner name is NULL. Use the SQL Server 2000 sp_changedbowner/sp_changeobjectowner procedure to assign a valid login as owner of the table

Type: Error
Time: 23-07-2003 11:31:57
Phase: Creating
Message: EXCEPTION : SQLServer2KSourceModelMap.mapTables(): null.[null].[null]; oracle.mtg.migration.MigrationSQLException: ORA-00903: invalid table name
:ORA-00903: invalid table name
"

Thanks a lot!

Lydia

 

[sybaseguru]

Lydia,

SA would have been created by MWB not by you. Go to OEM aand how many logins are there?

 

[lydiattc]

FIXED!

It was the ownership problem. The owner to all the objects that were transporting from MSSQL to Oracle was 'dbo', a defaulted sysadmin user in MSSQL. (I didn't realize this until I read the error messages the second time. The database in MSSQL was created by sombody else.) For some reason, MWB doesn't recoganize 'dbo' as a valid user name as an owner of an object. I created a test database and created a table with a different user name, and tried to ran MWB again. And it WORKED!

Thanks for all your advice.

Best wishes,
Lydia

 

[sybaseguru]

Lydia,

Please that all worked. Let us know how you get on with migration. I would expect at first iteration 40-60% of sps and functions will fail to be compiled in Oracle.

 

[lydiattc]

FYI,

I have finished my migration from MSSQL2K to Oracle 9201. All tables are migrated successfully.

Almost all stored procedures are migrated successfully excepter for a few which had incorrect usernames on tables. For some reason, during the migration, in some stored procedures the table names have been added a prefix of computername\administrators. (eg., table MODULES became computername\administrators.MODULES) After I deleted these prefixes, everything seemed to be compiled successfully.

Only half of the views are migrated successfully. I'm still working on them and trying to find out why they are not migrated. It's not a big number, so I may just go ahead and recreate them in Oracle.

Best wishes,
Lydia

 

[sybaseguru]

Lydia,

Those tables have a prefix most probably because you repeated migration few times and those tables existed already in the Oracle schema before. How about your stored procedures? They may have migrated but would have been created with a very dubious code. I would have expected a large number of them (around 60%) would have failed compilation (invalid). Just log in as dbo in the Oracle schema and do the following:

select substr(object_name,1,30), object_type from user_objects
WHERE object_type in ('PROCEDURE','FUNCTION')
AND STATUS = 'INVALID'
order by 2
/

This will give you the list of all thoses objects which failed compilation. See how it goes.

By the way is your Oracle on UNIX or windows?

 

[lydiattc]

To my surprise, there's only one row returned, and that's the procudure I created to change the object owner in SQL server.

I'm running Oracle 9.2.0.1 on Windows2000 server.

I know they have all compiled successfully after I deleted all the table prefixes inside procedures, but I haven't tried to run them. I'll let you know if I have further problems.

In fact, I've got a couple other problems after the migration. I'll start new threads for those.

Oh, by the way, the problem of table prefix occured in stored procedures not in table creation. For example, if in a stored procedure, there's a statement like INSERT INTO MODULES (col1, col2) VALUES (var1, var2). After the migration, it turned into INSERT INTO computername\administrators.MODULES (col1, col2) VALUES (var1, var2). Any idea why this happened?

Thanks.

 

[sybaseguru]

lydia,

For the "module" send an example of the transact SQL (source code) and PL/SQL code (converted code) please