?
Solved

Migrating from Red Brick to Oracle 11g - Need help with process flow

Posted on 2010-01-08
6
Medium Priority
?
1,033 Views
Last Modified: 2013-12-19
Hi All,

I am on this new project and on a very tight time line, where we are migrating from Red Brick warehouse to Oracle11g database. It will be very helpful if any of you can share your experience with me. My job would basically be able to load data from flat files in to the Oracle database.

Any suggestions about the process flow and potential pitfalls will be greatly appreciated!

Thanks so much.

Regards,
GP7
0
Comment
Question by:GP7
  • 3
  • 3
6 Comments
 
LVL 23

Expert Comment

by:Steve Wales
ID: 26214372
One relatively easy way to load flat files into Oracle is by using SQL*Loader.  You tell it the format of your input files in a little control file and it loads the data.

See the following couple of links (and post any more detailed questions you may have):

http://www.orafaq.com/wiki/SQL*Loader
http://download.oracle.com/docs/cd/B28359_01/server.111/b28319/ldr_concepts.htm#SUTIL003

0
 

Author Comment

by:GP7
ID: 26273330
Thank you for the suggestion sjwales. But the requirement is, we need to load files on a daily basis as there will be updates. SQL loader does it correctly on the first load but, it loads data as inserts when there are updates. once the data is loaded into the system, we cannot differentiate it if it was an insert or an update. SQL loader can be used for the initial load, but cannot be used the next time as it treats updates as inserts.
0
 
LVL 23

Expert Comment

by:Steve Wales
ID: 26273631
Ah, you didn't specify that in your original question :)

What is the source of your data ?  Is it from another Oracle database ?  You could look at Materialized Views for that.  Is it in SQL Server or some other non Oracle database ?  Depending upon the target, you could use Oracle Heterogeneous Services to make the connection and then use stored procedures to grab changed data (depending on volume).  There are ways around it.

Please give details on the source database, target database and data volume you're expecting and we can continue from there.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

Author Comment

by:GP7
ID: 26275780
I appreciate your help. So here is what we are doing. We have an IBM Red Brick Warehouse database on SQL server. Currently its being made available for 8*5. The problem with Red Brick Warehouse is that when data is being loaded (from the load servers)... inserts, updates etc cannot be done. So, the load is being done during off hours. Data will be loaded on a daily basis in to this Red Brick database. SO, in order to make the database more available and to use parallel processing (i.e loading as well as inserting etc) and to utilize the added benefits  of Oracle databases, Corporate has made a decision to migrate to Oracle 11g. The plan is to run both the Red Brick and Oracle database parallelly and eventually take down the Red Brick database.
The new Oracle database will be on RHEL server. The load files are being transformed in to flat files and are being made available on a shared disk (shared by redbrick database and the new Oracle database). Now, after creating the new oracle database and setting up all the required schemas, table spaces and tables etc, I'll need to load the data from flat files in to the Oracle database.

Now, should I use external tables, or write stored procedures or is there any other option? Please advice on the adv/disadv of using the above methods. Since we are on a very tight time line, I do not have much time for testing various options.

Thanks for all your help!

Regards,
GP7



0
 
LVL 23

Accepted Solution

by:
Steve Wales earned 1000 total points
ID: 26360532
One thing that may or may not work (I have a feeling it won't but it may be worth a shot) is to set up Oracle Heterogeneous Services so that your Oracle Database can query SQL Server.  This isn't particularly hard to set up.

It may even be possible to create a materialized view to over the HS Link to refresh the data - depending on the volume of the data - this would likely be very slow.

Other options (did something similar on a small database once) - triggers on the source to record a change table to record inserts and deletes and then replayed them into the target - not particularly elegant but the volume was small enough to do.

Hopefully others may have some better ideas for you if these don't help.
0
 

Author Closing Comment

by:GP7
ID: 31674780
Thank you for the input...
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Suggested Courses
Course of the Month4 days, 13 hours left to enroll

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question