Hi, I am new to ORACLE. We currently have a lot of databases running on a Microsoft SQL server(MSSQL). I am working on converting them to ORACLE, which is running on Solaris. I know how to load a database from MSSQL to ORACLE, but I am not quiet understanding some ORACLE design issues.<br><br>To convert these MSSQL databases to ORACLE, should I create one schemas for each database on different tablespace? Am I on the right track? What is the best approach for the conversion? Adding more instance does not matter here, right? <br><br>Any advice would be very appreciated. Thanks! <br><br>Mary<br><br><br>
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Should I create a schema for each database (convert MSSQL to ORACLE) 1
- Thread starter mlu
- Start date
-
1
- #2
Hi Mary,<br><br>The answer, sorry, is: "It Depends."<br><br>Use Separate Instances If:<br>==========================<br>You need to be able to take down individual databases once they're on Oracle (and by databases I mean the groups of tables that used to be an MSSQL db)<br><br>The databases are large and/or busy.<br><br>Use Schemas Within One Instance If:<br>===================================<br>You don't mind that when one database is unavailable everything is.<br><br>The databases are not too large or too busy to need individual optimization approaches.<br><br>You should also bear in mind:<br>=============================<br><br>That processes working in one instance can read/update tables in another instance easily.<br><br>There's no programming advantage to having all your tables in the same instance -- a remote table can look just like a local table to a programmer.<br><br>If your applications get real busy it's much easier to move just the one database to another server -- so that you can spread the load about.<br><br>My bias is (obviously) to create separate instances for each existing MSSQL database. There's less work involved in having everything in the same instance -- but not *that* much less. <p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href= Cargill's Corporate Web Site</a><br>
- Thread starter
- #3
Hi Mike,<br><br>Thank you very much for your valuable information. It is very helpful to me. I've been stuggling over these two ways. Now I can see the advantage of each method. <br><br>I prefer creating separate instance for each MSSQL databases too. But I still have some questions on multiple instances. On MSSQL, there are over 15 databases. Some are big and some are small, and they all can be fit into our new server. So is creating over 10 instances on one server practical? will so many instances on one server effect performance badly or just slightly? Should they be spread over multiple servers?<br><br>Any advice or suggestion on these? Thanks very much!<br><br>Mary<br><br>
listener22
Programmer
Each instance requires its own SGA and background processes.<br> Do the MS SQL databases support more than one application?<br>One option would be to have one Oracle instance per application.<br>There can be multiple schemas within one application. <p>Jim Carlson<br><a href=mailto:nx56@inetarena.com>nx56@inetarena.com</a><br><a href= > </a><br>oracle, vb, some javascript
- Thread starter
- #5
Hi, Mary.<br><br>I would expect that the biggest problem in creating so many instances is that you may run short of memory. You are right that each instance needs its own SGA which must be large enough to efficiently run the application. You're in danger of duplicating memory structures which could be shared.<br><br>Of course this is not an all or nothing decision. Instead of creating an instance for every single database perhaps you could create only a few. Combine all the small applications into a single instance and put all the large applications into separate instances.
Karl's quite right, the balance is between:<br><br>one instance per application: <br> simplicity in managing the different applications<br> uses more memory<br><br>one instance containing lots of application: <br> can become very unwieldy<br> uses less memory<br><br>"Combine all the small applications into a single instance and put all the large applications into separate instances." -- Yes -- I wish *I'd* said that.<br> <p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href= Cargill's Corporate Web Site</a><br>
- Status
Similar threads
