| Author |
Topic |
|
Glowball
Starting Member
7 Posts |
 Posted - 2005-11-07 : 14:50:45
|
| I'm a Web Developer (not a DBA by any means) and I've found myself in between an Oracle DBA and SQL Server DBA who won't work together. We're moving an active application from one server to another and the database will be moved from Oracle8i Enterprise Edition (8.1.7.4.0 64bit Production) to the latest SQL Server. The two machines will not have access to each other, so I need to transfer the data in file form to create the new database. After testing, I'll need to update that new database with the latest Oracle data (same table structure) so they're in sync again when the new application moves to production.The SQL Server DBA doesn't know Oracle, and the Oracle DBA doesn't know anything about SQL Server. What should I get from the Oracle DBA to give to the SQL Server DBA to make this process go smoothly? I'm not sure what to ask for.TIA |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-07 : 15:02:56
|
Hi Glowball, Welcome to SQL Team!You could use delimited files (one per Oracle table) and BCP or DTS [both of these offer "bulk copy" methods for getting data in, or out, of SQL Server] them into SQL Server. They need a delimiter that does not occur in the data stream - might be a problem if you have tables which contain blobs of TEXT.You could use XML too - that wouldn't suffer from the Delimiter Character problem. However, XML import into SQL Server is a LOT slower than a "delimited text file"But I reckon by far and the best way is to link the two servers together  .In all seriousness I would put some effort into linking the servers if at all possible, it makes the transfer of data a piece-of-cake compared to the other methods.DTS will happily take an Import Source of "Oracle" provided some Oracle client is installed and the Oracle box is "visible". if you can set up a Linked Server in SQL Server you can do:INSERT INTO MySQLServerTableSELECT *FROM OPENQUERY(MyOracleServer, 'SELECT * FROM MyOracleTable')which is pretty straightforward!One other thing to consider; import into SQL Server needs to "observe" the Referential Integrity rules - so to IMPORT you will need to do "parent tables" then "child tables", and to pre-delete everything before your next import you need to delete them the other way round: "delete child tables" then "delete parent tables"Kristen |
 |
|
|
Glowball
Starting Member
7 Posts |
Posted - 2005-11-07 : 15:13:36
|
| Thanks for your quick reply, Kristen! Unfortunately there is no way the servers will ever be able to work with each other -- it's mostly political, but you know how that goes.I've only moved data between MySQL databases in the past, and it's always been a simple SQL file. I asked for an Oracle export and got an export.dmp.Z file that I can't read, so I have no idea what's in it and if SQL Server can read that. I also received a plain text log file that started with this:Export done in US7ASCII character set and US7ASCII NCHAR character set About to export the entire database .... exporting tablespace definitions. exporting profiles. exporting user definitions. exporting roles. exporting resource costs. exporting rollback segment definitions. exporting database links. exporting sequence numbers. exporting directory aliases. exporting context namespaces. exporting foreign function library names. exporting object type definitions. exporting system procedural objects and actions. exporting pre-schema procedural objects and actions. exporting cluster definitionsThen it listed the export of each of the system tables and data tables. I guess that's what's in the dump file, but can SQL Server read it?Is there a way to turn off the foreign key constraints so we can import the whole thing all at once without doing parent tables then child tables?If this is too painful I could always write a script to spit out the data from Oracle using your INSERT as a guide. I could always write scripts for all of this, but I figured an actual Oracle export might be easier and better.Thanks for your patience! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-07 : 15:30:41
|
| "it's mostly political, but you know how that goes"Indeed ... but that's nuts. It will cost at least 10x as much to do it without a direct link as with one. The link to Oracle only needs to be READ ONLY; politics or not, I'm sure a sufficiently senior manager could make the cost-benefit equation on the back of their cigarette packet!"Is there a way to turn off the foreign key constraints"Its easy to create a script to "DROP CONSTRAINS" and "CREATE CONSTRAINTS" in SQL Server, and that would certainly be a sensible option."I figured an actual Oracle export might be easier and better"The issue is only really the delimiter character. For tables that don't contain "TEXT" datatypes the TAB character is normally "safe". So I think it depends whether you have TEXT (or BINARY) columns, or not ... ??Edit: Added "Binary"Kristen |
|
|
|
Glowball
Starting Member
7 Posts |
Posted - 2005-11-07 : 15:55:25
|
| Yes, there are plenty of TEXT columns (not sure about binary). As for linking the servers, coming up with 10x of the money would be easier than getting a read-only link. It's crazy but that situation will not change. I'm already having an extremely difficult time getting the Oracle exports. This is part of the reason we're moving to a new environment controlled by different people.So it sounds like this binary dump file from Oracle isn't going to be helpful? That's a bummer.If I create one file for each table of data in a standard CSV then SQL Server would be fine with that, right? I'm not too worried about speed because we'll be doing this twice for an application not yet in use.How well would SQL Server handle a .sql file with CREATE statements to make all of the tables? The issue is that the current Oracle database is a disaster (I didn't make it and haven't worked on it, and there isn't time to fix it yet). Most tables do not have primary keys and there are very few relationships between tables. I can't even create a data model for it because my software won't allow it in the state it's in, but Oracle seems to be okay with it.Thoughts? |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-11-07 : 16:16:43
|
| Well, I know both.For Oracle 8i to export the data, they are going to have to write stored procedures and packages. 1 Per Table, which you will certainly get a lot of push back on.Sounds like they gave you a dump, which is useless to you.You will probably need all of the DDL. If you had ERWin, it would be a snap...as well as converting it into SQL Server syntax.PM me if you need assistance. Where in the world are you located?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
Glowball
Starting Member
7 Posts |
Posted - 2005-11-07 : 17:04:54
|
| I'm located in Colorado, USA. It's starting to look like I'll be writing some scripts to write some text files. It'll be the same amount of effort as it would be for them to write the stored procedures so I might as well do it.I'll stop messing with that dump file, then, thanks for the heads up on that.I'm not sure if the Oracle guy has ERWin or anything like it. If he does, what would I ask for specifically from him? Thanks! |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-11-07 : 17:17:22
|
| well, both of those DBAs should be fired then. There is a professional way to do things, and these guys just aren't professionals. I would fire both of them in a second over something like this. It sounds to me like this is a management issue. They should probably be fired too I guess.Anyway, here is a good overview of your options for moving the data: http://www.microsoft.com/technet/itsolutions/cits/interopmigration/unix/oracleunixtosql/or2sql08.mspxI personally would use DTS for a migration like this. It is pretty easy to configure, and it is totally self contained in the package that you create. The downside for DTS is that is somewhat hard to debug when something goes wrong.Good luck.-ec |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-07 : 18:31:37
|
| "They should probably be fired too I guess" Sounds like the company that had their top 6 executives in a plane that flew into a hillside. Six months later the company turned in its best results ever!Kristen |
|
|
|
Glowball
Starting Member
7 Posts |
Posted - 2005-11-07 : 18:51:45
|
| We're moving things from in house to outsourced hosting and we're concerned that once the in house people find out that we won't have access to our data anymore. Even if they did know we were moving the application they wouldn't allow us to have any more access to the server than we do now, which is very little. We've had issues before and we've been at 96% uptime for the last year for a critical application. It has been painful but hopefully we're seeing the light at the end of the tunnel.Thanks for the link, eyechart. Naturally we're going from Windows to Unix but the article should apply. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-11-07 : 19:01:49
|
quote:
Originally posted by GlowballThanks for the link, eyechart. Naturally we're going from Windows to Unix but the article should apply.
I thought you said you were moving for Oracle to SQL Server. Is that correct? Now I'm confused.-ec |
|
|
|
Glowball
Starting Member
7 Posts |
Posted - 2005-11-07 : 20:08:10
|
| Yep, Oracle on Windows to SQL Server on Unix. The article has the operating systems the other way around (the one you linked to). Yeah I guess I was being pretty vague, sorry about that. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-11-07 : 20:47:06
|
That's gonna be a neat trick getting SQL Server to run on Unix. Good luck with that.  I imagine you're talking about Sybase SQL Server? SQL Team is a Microsoft SQL Server site, we tend to think that you're doing everything on Windows. |
|
|
|
Glowball
Starting Member
7 Posts |
Posted - 2005-11-07 : 22:00:25
|
| Oh yeah now that you mention it we must be moving to another Windows server. Sorry, it's still in the process of getting set up and I haven't worked with SQL Server much. I'm sure we'll be on Windows. |
|
|
|
|
Moving Oracle Database to SQL Server