Home » Other » Client Tools » SQL Server to Oracle Migration (SQL server 2008 and Oracle 12C, Oracle SQL Developer 4.1.3)
SQL Server to Oracle Migration [message #648016] Mon, 15 February 2016 00:58 Go to next message
pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India
Senior Member
Hi,
I involved in a migration project which will migratre sql server to oracle 12c.
I use oracle sql developer tool 4.1.3 with jTDS 1.3.1 driver installed for third party migration.
I have followed the steps provided in the sql developer tool for migration and the migration was successful.
But I'm not able to see the migrated table in my oracle user instead I'm able to see the system tables of SQL server which got
migrtated with MD_.

The following is the loginformation I got it from MIGRLOG table after migration.
Kindly let me know what is missing here and how to proceed further.

614751 15-FEB-16 11.23.11.954000000 AM Convert completed in 9 seconds
614701 15-FEB-16 11.22.31.040000000 AM Catalog DEMO_Mig, Schema dbo coalesced to single schema DEMO_Mig
Re: SQL Server to Oracle Migration [message #648031 is a reply to message #648016] Mon, 15 February 2016 04:13 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Post output from:

sho user

select owner, count(*) from dba_tables where upper(owner) = 'DEMO_MIG';
Re: SQL Server to Oracle Migration [message #648035 is a reply to message #648031] Mon, 15 February 2016 05:16 Go to previous messageGo to next message
pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India
Senior Member
Hi,

Please find the output below. The migrated user shows only the system tabels that are migrated and not the application table.
And I'm not able to run this query on dba_tables. As I don't have the privilege to select the dba_tables. So I selected it from all_tables. For migration from SQL server to Oracle is it necessary that I should have the oradba with ADM privilege?
Is that preventing to create tables in the migration user?

Kindly provide some solution.

USER is "DEMO_MIG"
OWNER
--------------------------------------------------------------------------------
COUNT(*)
----------
DEMO_MIG
44
Re: SQL Server to Oracle Migration [message #648042 is a reply to message #648035] Mon, 15 February 2016 07:01 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
That means you're logged on as DEMO_MIG, so you have to be able to see the tables.

Or do you mean the tables are there but have no data?
Re: SQL Server to Oracle Migration [message #648062 is a reply to message #648042] Mon, 15 February 2016 23:02 Go to previous messageGo to next message
pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India
Senior Member
Hi,
The table count shows the system tables that were migrated from SQL Server to Oracle.
The actual application tables were not available in the Oracle user.
When I see in the migration repository, I can see the application tables, views, procedures on the prescribed user.
But when I log in to the prescribed oracle user, I can see only the system tables and not the application tables.

Kindly show some light on this.

Thanks.

Regards,
Anand.T
I don't see any tables [message #648063 is a reply to message #648016] Mon, 15 February 2016 23:12 Go to previous messageGo to next message
pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India
Senior Member
Hi,
I am doing MSSQL server to Oracle migration in SQL Developer 4.1.3 and MS SQL Server 2008 with Oracle 12c.
Having a connection with ORADBA privileges and created a migration user and granted all the privileges.
After migration, I received migration have been completed successfully.
In the migration repository too, I can see the tables under the user.

I followed the following steps.

1. Repository - Repository name is given
2. Project - project name provided
3. Source Database - source database as SQL Server DB chosen
4. Capture -- Selected the particular db under SQL Server DB to be migrated
5. Convert --
6. Translate - Selected all the ALL_CONSTRAINTS, ALL_FUNCTIONS,ALL_PROCEDURES,ALL_TRIGGERS, ALL_VIEWS
7. Target Database --- Target database as Oracle Database chosen
8. Move Data.

In the migration log file also I got the following message.

Catalog DEMO_mig, Schema dbo coalesced to single schema DEMO_mig
Convert completed in 9 seconds.

But when I checked in the (DEMO_mig)oracle user I don't see any tables listed under the user except the SQL Server system tables which starts with MD_.

Did I missed anything in the migration?

Kindly provide me a solution.

Thanks.
Regards,
Anand.T


[EDITED by LF: this message was moved here from - in my opinion - unrelated topic: Error to Associate Migration Repository; as if the author missed the topic he opened and posted a message in another one]

[Updated on: Tue, 16 February 2016 14:06] by Moderator

Report message to a moderator

Re: SQL Server to Oracle Migration [message #648080 is a reply to message #648062] Tue, 16 February 2016 02:32 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
It sounds like your import only partially completed.

What do your logs say?

Have you tried reimporting?
Re: SQL Server to Oracle Migration [message #648084 is a reply to message #648080] Tue, 16 February 2016 04:09 Go to previous messageGo to next message
pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India
Senior Member
My MIGRLOG table shows the below result. I logged into DEMO_Mig user and checked for the tables.
But I'm not getting the actual application tables.


614751 15-FEB-16 11.23.11.954000000 AM Convert completed in 9 seconds
614701 15-FEB-16 11.22.31.040000000 AM Catalog DEMO_Mig, Schema dbo coalesced to single schema DEMO_Mig


Is it could be any privilege issue that prevents me to view the tables,views got migrated from MS SQL Server to Oracle.

Kindly let me know.

Regards,
Anand.T
Re: SQL Server to Oracle Migration [message #648085 is a reply to message #648084] Tue, 16 February 2016 04:18 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
You said the tables aren't there though.

The oracle schema owner will be able to see its own tables.

If you have some but not all, it's sounding like a migration problem rather than a database problem.
Re: SQL Server to Oracle Migration [message #648093 is a reply to message #648085] Tue, 16 February 2016 06:57 Go to previous messageGo to next message
pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India
Senior Member
Hi,
Attached is the steps I took during the migration. I have captured all the steps until checking the oracle user for tables that were migrated from MS SQL Server. Kindly go through it and provide a solution.
Also let me know where I'm missing.

Thanks.

REgards,
Anand.T
Re: SQL Server to Oracle Migration [message #648094 is a reply to message #648093] Tue, 16 February 2016 07:28 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
I'm honestly not sure. your OP sounded like they were there but you didnt have permissions to see them; I don't know why only some of the stuff you expect isnt there.
Re: SQL Server to Oracle Migration [message #648097 is a reply to message #648062] Tue, 16 February 2016 08:39 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
pstanand wrote on Mon, 15 February 2016 23:02
Hi,
The table count shows the system tables that were migrated from SQL Server to Oracle.
The actual application tables were not available in the Oracle user.


In your understanding, what is the difference between a "system" table and an "application" table?

Re: SQL Server to Oracle Migration [message #648134 is a reply to message #648097] Wed, 17 February 2016 03:29 Go to previous message
pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India
Senior Member
Hi,
System tables are the tables that gets created during migration on background process. Application tables are the actual tables that contains data.

Thanks

Regards,
Anand.T
Previous Topic: Error to Associate Migration Repository
Next Topic: ORA-44412: XE edition memory parameter invalid or not specified
Goto Forum:
  


Current Time: Thu Mar 28 18:28:57 CDT 2024