This looks like a great approach, however we cannot take it as we will not have access to the redo logs. The database is proprietary, we will only have select privs, over a database link.

I'm guessing our only approach is to create packages with statements to pull, massage and insert the data.

Something like:

INSERT INTO local_table <
SELECT * <-- massage attributes as required here
FROM remote_table@remote_location
WHERE NOT EXISTS <-- criteria here to get new data
/

Any thoughts or advice on this approach? Any potential gotchas or things we should watch out for?

Again, we are just starting the development. Apologies for lack of a concrete example.

The other approach we are considering is doing a select from remote in a cursor and then the local copy is compared to the ‘original source’ new rows are added, existing rows are updated if they’ve changed.

I have a feeling your answer will be to test out the approaches and see what works / performs best.......That said, any insight you may have would be greatly appreciated.

Tom,

You are "really great". You've solved this guy's problem keeping it "as simple as possible". We are really "proud" for you.

I was faced with exactly the same problem as this, and have written a PL/SQL package to solve the problem which dynamically matches the columns in the specified "source" and "target" tables (either or both of which can be over a database link). It's been running quite happily for the last 3 years copying data each night between Tokyo, New York and London...
Ask Tom to forward you my email address if you want the code.
(btw, Tom, in the real world we often have to live with less than ideal situations and still find workable solutions. This is not a dig, just a statement of fact).

I don't understand this statement by Scott:

> This looks like a great approach, however we cannot take it
> as we will not have access to the redo logs.

AFAIK you don't need any kind of access - that's completely transparent, it's the "reader process" that reads the logs on your behalf:
</code> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96571/capture.htm#48703 <code>

Thank you for pointing this out. Although it is something I doubt they would accept, it is worth it for us to raise it as a option. I would imagine this company has received requests like ours and is open to ideas. I'll read more on it to see if it's worth while.

Thanks for the input, and the answers. If possible I'm interested in learning more about sending data around the world via procs and links. If Tom is willing to send the email address from the post above, I'm willing to take it.

When we are done, I will post an example of our solution for folks to review.

Tom -
no-one in this thread (except you) has mentioned anything about "tens of gigabytes" of data being involved - you're making assumptions about the situation which are not based on any facts.
My process which copies data between our various sites doesn't involve 10s of gig - if it did I wouldn't use this approach. However, in my case although the tables themselves are large I know that there will be only a small number of differences to merge from a small subset of the data and the approach works perfectly well under these circumstances.
If the case here is similar then there's no reason they shouldn't try a similar approach to solve it. But of course my solution is not appropriate for large volumes. But at least it's a positive suggestion ....

Just to give some idea of the volumes of data involved in my particular setup, I "merge" around 60 tables a night between the sites. The tables are mostly small (<5,000 rows). The largest tables contain in excess of 5mill rows, but I can identify a candidate subset of around 50,0000 of these which may need to be merged and would typically expect to find about a tenth of those actually needing to be merged. There are only two or three "large" tables like these.
The longest single table merge takes about 5 minutes. The set of 60 takes just over an hour each night. All these times and volumes are acceptable to my users.

I'm a great fan of this site and it's usually the first place I turn to when researching a problem. So I'm just hoping this contribution will help someone out here.

Tom -
first a correction to my last post : I meant 50,000 not "50,0000".

But once again another false assumption on your part. Actually my data does use timestamps on the largest tables and I'm only interested in data from the last 2 weeks.
Whatever you say in response to this (I'm sure there'll be something) I will counter with two points :

1 - I know my data and my system and this solution has worked satisfactorily for 3 years in an environment where failures (human and system) are not tolerated
2 - the users are happy and have been all this time.

Please forward my email address to the guy with the problem so he can try out my solution and see if it helps him. I'll explain all the caveats to him and he can decide for himself. If it doesn't work he's lost nothing and you can continue your rant ...

Thanks for forwarding on my email address - I'll get in touch with the guy in the next few days.

The article you reference looks interesting - I'll read it properly tomorrow, but I think in my case it doesn't apply. At least I've had no problems which would suggest it does.

As we both agree, this is not an approach which should be used for large volumes of data, but it might be of some use.
We'll see.

Tom,

you said that Streams could solve Scott's problem.
Is this a theoretical statement or somewhat practically confirmed?

I've been told this technology is very very unstable. People who tried to use it, bumped into multiple 00600 and 07445.

Have you personally seen succesful replication-oriented PRODUCTION deployment of Streams?

wow, maybe I can throw some lights here :)

This requirement is quite normal, and the solutions are so much: streams, Transparent Gateway, MV, merge, PLSQL or ProC DIY, dblink, export to csv (check Tom's classic advice ;) ), ETL tools(e.g. informatica), partition operation, even dmp->ftp->imp and so on.

I don't think there's a solution that's perfect for any situation. A rule here is impact the source as few as possible.

Maybe Tom could give us some favorite practices. Data sharing is always the topic. :)

Tom is good-tempered, I found bugs in my words. And Oracle has introduced two features: oracle customer data hub & asynchronous change data capture. Maybe Oracle have already made a plan to solve the whole question. :)

Very nice thread,I am looking for Tom's personally favorite solution as there are many many ones in real world.

Tom,

I still don't understand, is it worth investing our time and efforts in Oracle 10g Streams for the data synchronization?

Do you say us -
"theoretically Streams looks like what you want and may be you should play with it, be cautious however".

- OR you say

"I recommend you to investigate in Streams for such a replication because it's very cool and flexible technology and I absolutely KNOW company "AAA" succesfully synchronize their data with Streams over 24 countries for the last 2 years"?

Please be more concrete.

I have biuld a Procedure which is Mergeing data between two
database (100 tables)by using Datalink.
But one of Table is creating problem. the code I wrote-
BEGIN
MERGE INTO PRAN.CHALLAN_TRIP@TR_LINK P_CHTP
USING (SELECT ALL
PRAN.CHALLAN_TRIP.TRIP_NO,
PRAN.CHALLAN_TRIP.TRIP_TRN_ID,
PRAN.CHALLAN_TRIP.TRIP_DESC,
PRAN.CHALLAN_TRIP.TRIP_DATE,
PRAN.CHALLAN_TRIP.VEHICLE_ID,
PRAN.CHALLAN_TRIP.DRIVER_ID,
PRAN.CHALLAN_TRIP.D_MAN_ID,
PRAN.CHALLAN_TRIP.WH_ID,
PRAN.CHALLAN_TRIP.TRIP_FAIR,
PRAN.CHALLAN_TRIP.CHTP_ADVA,
PRAN.CHALLAN_TRIP.CHTP_PAID,
PRAN.CHALLAN_TRIP.CHTP_VHCL,
PRAN.CHALLAN_TRIP.UDT,
PRAN.CHALLAN_TRIP.VER,
PRAN.CHALLAN_TRIP.OID
FROM PRAN.CHALLAN_TRIP
WHERE PRAN.CHALLAN_TRIP.UDT >(SYSDATE-15)) P11_CHTP
ON (P_CHTP.TRIP_NO= P11_CHTP.TRIP_NO)
WHEN MATCHED THEN
UPDATE SET
P_CHTP.TRIP_TRN_ID = P11_CHTP.TRIP_TRN_ID,
P_CHTP.TRIP_DESC = P11_CHTP.TRIP_DESC,
P_CHTP.TRIP_DATE = P11_CHTP.TRIP_DATE,
P_CHTP.VEHICLE_ID = P11_CHTP.VEHICLE_ID,
P_CHTP.DRIVER_ID = P11_CHTP.DRIVER_ID,
P_CHTP.D_MAN_ID = P11_CHTP.D_MAN_ID,
P_CHTP.WH_ID = P11_CHTP.WH_ID,
P_CHTP.TRIP_FAIR = P11_CHTP.TRIP_FAIR,
P_CHTP.CHTP_ADVA = P11_CHTP.CHTP_ADVA,
P_CHTP.CHTP_PAID = P11_CHTP.CHTP_PAID,
P_CHTP.CHTP_VHCL = P11_CHTP.CHTP_VHCL,
P_CHTP.UDT = P11_CHTP.UDT,
P_CHTP.VER = P11_CHTP.VER,
P_CHTP.OID = P11_CHTP.OID
WHEN NOT MATCHED THEN
INSERT
(
P_CHTP.TRIP_NO,
P_CHTP.TRIP_TRN_ID,
P_CHTP.TRIP_DESC,
P_CHTP.TRIP_DATE,
P_CHTP.VEHICLE_ID,
P_CHTP.DRIVER_ID,
P_CHTP.D_MAN_ID,
P_CHTP.WH_ID,
P_CHTP.TRIP_FAIR,
P_CHTP.CHTP_ADVA,
P_CHTP.CHTP_PAID,
P_CHTP.CHTP_VHCL,
P_CHTP.UDT,
P_CHTP.VER,
P_CHTP.OID
)
VALUES
(
P11_CHTP.TRIP_NO,
P11_CHTP.TRIP_TRN_ID,
P11_CHTP.TRIP_DESC,
P11_CHTP.TRIP_DATE,
P11_CHTP.VEHICLE_ID,
P11_CHTP.DRIVER_ID,
P11_CHTP.D_MAN_ID,
P11_CHTP.WH_ID,
P11_CHTP.TRIP_FAIR,
P11_CHTP.CHTP_ADVA,
P11_CHTP.CHTP_PAID,
P11_CHTP.CHTP_VHCL,
P11_CHTP.UDT,
P11_CHTP.VER,
P11_CHTP.OID
)
;
END;

But at the end it is giving error is -

> CHALLAN_TRIP
BEGIN
*
ERROR at line 1:
ORA-00904: "P11_CHTP"."TRIP_NO": invalid identifier
ORA-06512: at line 2

--------
the code is working nicely in between two user in same Schema.

Please help to find where is the problem.

I was just wondering if Scott (who started this thread) ever received and tried the PL/SQL package I sent him. I'd be interested to know if he found it useful or not.

Tom,

I have a situation here, we have 30 tables, these tables needs to be in sync with remote database. these tables are really small, less than 5000 rows, and tables should be in sync on daily basis at the end of the day and need not be in fast refresh mode. we dont want import/export and we cant afford Oracle streams .

Could you please suggest the best approach for this...

Thanks

We did not use Chris's method to synchronize the tables, one of our developers had already written something by the time we got his code. Thanks to Chris for sharing with us.

I'm going to outline how this unfolded, it might prove beneficial for someone. Our requirement is to update 7 local tables, with data from remote tables. Rowcounts as outlined below. (Rowcounts turned out to be less than we originally thought)

COMPANY 937
CONNECTIONS 35586
EQUIPMENT 46089
ANALYSIS 35570
METER 94048
SITE 17042
INTEREST 59338

The first method we tried was to select the rows from the remote site into a cursor, loop through the cursor, update records where they already existed, or insert records if they did not. This gave us the results we needed, however it was very, very, very expensive in terms of CPU and network utilization.

Results from a trace of the process, using the original method, outlined below.

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 869 0.04 0.05 0 0 1 0
Execute 389981 99.29 98.11 13 9538 1445473 195094
Fetch 390607 23.82 86.72 6 390540 0 4234143542
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 781457 123.17 184.90 19 400078 1445474 4234338636

Misses in library cache during parse: 12
Misses in library cache during execute: 6

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to dblink 194582 0.00 0.21
SQL*Net message from dblink 194582 0.05 70.83
SQL*Net more data from dblink 1 0.00 0.00
db file sequential read 19 0.01 0.05
log file sync 9 0.00 0.02
log file switch completion 4 0.11 0.19
free buffer waits 2 0.22 0.41


After reviewing the code, the tkprof output, and the OS performance profile (network and CPU utilization), I knew two things:

1. We needed to find a more efficient way to do this.
2. If there was a better way, I would find it on this site, or in one of Tom's books.

I found the answer in Effective Oracle by Design.

We now use the MERGE INTO syntax to achieve the same results with drastically reduced overhead (NETWORK and CPU utilization), and improved response time.

MERGE INTO local_table
USING remote_table
ON (remote_table.id = local_table.id)
WHEN MATCHED THEN
UPDATE
SET local_table.field1=remote_table.field1
,.......etc
WHEN NOT MATCHED THEN
INSERT (
field1
,....etc
)
VALUES (
remote_table.field1
,....etc
);

Results from a trace of the process, using the MERGE syntax, outlined below. Note, we are achieving the same results as the original method.

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 875 0.06 0.05 0 0 1 0
Execute 875 7.92 26.92 3387 13799 985350 195106
Fetch 1504 0.04 0.01 0 2702 0 1186
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3254 8.03 26.99 3387 16501 985351 196292

Misses in library cache during parse: 6

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to dblink 37 0.00 0.00
SQL*Net message from dblink 37 0.00 0.02
SQL*Net more data from dblink 6443 0.05 0.36
SQL*Net more data to dblink 2 0.00 0.00
direct path write 481 0.00 0.05
direct path read 481 1.81 8.91
db file sequential read 20 0.41 0.68
log buffer space 4 0.99 2.16
log file sync 9 0.00 0.02
free buffer waits 10 1.00 6.34
log file switch completion 2 0.17 0.27


Hope that helps.

We have a Supply Chain Planning Instance(Apps), the Program runs on Destination and pulls the Data from Source (by design), We have a join on 3 tables and all 3 tables are from remote database, the Join was happening on the target instance as a result of which there was high SQL Net Traffic, we added a Driving Site Hint but the plan didnt change, the other altenative would be to create a view on the source instance for the join and do a select on that from the destination. But the question why the driving site hint is also making the optimiser not choose that plan,

Rows Row Source Operation
------- ---------------------------------------------------
702225 NESTED LOOPS
702225 NESTED LOOPS OUTER
702225 REMOTE
692991 REMOTE
702225 REMOTE

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to dblink 2806906 0.00 3.93
SQL*Net more data to dblink 2 0.00 0.00
SQL*Net message from dblink 2806906 0.81 4996.93
SQL*Net more data from dblink 29698 99.44 2752.76
db file sequential read 1 0.00 0.00
enqueue 4 0.00 0.00
row cache lock 4 0.00 0.01
log file sync 28 0.03 0.38
latch free 3 0.00 0.00
log file switch completion 2 0.01 0.02

insert into MSC_ST_SUPPLIES ( SR_MTL_SUPPLY_ID, INVENTORY_ITEM_ID,
ORGANIZATION_ID, SUBINVENTORY_CODE, FROM_ORGANIZATION_ID,
SOURCE_ORGANIZATION_ID, SOURCE_SR_INSTANCE_ID,
DISPOSITION_ID, SUPPLIER_ID, SUPPLIER_SITE_ID, ORDER_TYPE,
NEW_SCHEDULE_DATE, NEW_ORDER_QUANTITY, QTY_SCRAPPED,
EXPECTED_SCRAP_QTY, DELIVERY_PRICE, PURCH_LINE_NUM,
PO_LINE_ID, FIRM_PLANNED_TYPE, NEW_DOCK_DATE,
ORDER_NUMBER, REVISION, PROJECT_ID, TASK_ID,
PLANNING_GROUP, UNIT_NUMBER, VMI_FLAG,
PO_LINE_LOCATION_ID, PO_DISTRIBUTION_ID, DELETED_FLAG,
REFRESH_ID, NEW_ORDER_PLACEMENT_DATE, ORIGINAL_NEED_BY_DATE,
ORIGINAL_QUANTITY, PROMISED_DATE, NEED_BY_DATE,
ACCEPTANCE_REQUIRED_FLAG, POSTPROCESSING_LEAD_TIME,
SR_INSTANCE_ID, SCHEDULE_GROUP_NAME) select x.TRANSACTION_ID,
x.ITEM_ID, x.TO_ORGANIZATION_ID, DECODE(
:v_mps_consume_profile_value, 1, x.MRP_TO_SUBINVENTORY,
x.TO_SUBINVENTORY), x.FROM_ORGANIZATION_ID,
x.FROM_ORGANIZATION_ID, DECODE(x.FROM_ORGANIZATION_ID,NULL,NULL,
:v_instance_id), x.PO_HEADER_ID, x.VENDOR_ID,
x.VENDOR_SITE_ID, 1, DECODE( :v_mps_consume_profile_value,
1, x.MRP_EXPECTED_DELIVERY_DATE,
x.EXPECTED_DELIVERY_DATE)- :v_dgmt, DECODE(
:v_mps_consume_profile_value, 1, x.MRP_PRIMARY_QUANTITY,
x.TO_ORG_PRIMARY_QUANTITY), DECODE(
:v_mps_consume_profile_value, 1, x.MRP_PRIMARY_QUANTITY,
x.TO_ORG_PRIMARY_QUANTITY)*
DECODE(SIGN(x.SHRINKAGE_RATE), -1, 0,(NVL(x.SHRINKAGE_RATE, 0))),
DECODE( :v_mps_consume_profile_value, 1,
x.MRP_PRIMARY_QUANTITY, x.TO_ORG_PRIMARY_QUANTITY)*
DECODE(SIGN(x.SHRINKAGE_RATE), -1, 0,(NVL(x.SHRINKAGE_RATE, 0))),
x.UNIT_PRICE, x.LINE_NUM, x.PO_LINE_ID,
DECODE( decode( decode( sign(nvl(x.ph_firm_date,sysdate+1)-sysdate),
1, x.ph_firm_status_lookup_code, 'Y'), 'N',
decode(sign(nvl(x.pll_firm_date,sysdate+1)-sysdate), 1,
x.pll_firm_status_lookup_code, 'Y'), 'Y'), 'Y',1, 2),
x.EXPECTED_DOCK_DATE- :v_dgmt, x.PO_NUMBER,
x.ITEM_REVISION, x.PROJECT_ID, x.TASK_ID,
mpp.PLANNING_GROUP, x.END_ITEM_UNIT_NUMBER,x.VMI_FLAG,
x.PO_LINE_LOCATION_ID,x.PO_DISTRIBUTION_ID, 2,
:v_refresh_id, x.NEW_ORDER_PLACEMENT_DATE,
x.ORIGINAL_NEED_BY_DATE, x.ORIGINAL_QUANTITY, x.PROMISED_DATE,
x.NEED_BY_DATE, x.ACCEPTANCE_REQUIRED_FLAG,
x.POSTPROCESSING_LEAD_TIME, :v_instance_id,
decode(pll.attribute5,'Y','OVERLAP', pll.attribute4 ) from
PJM_PROJECT_PARAMETERS@evncpap1 mpp, MRP_AP_PO_PO_SUPPLY_V@evncpap1 x,
PO_LINE_LOCATIONS_ALL@evncpap1 pll where x.TO_ORGANIZATION_ID IN
(7732,7747,7734,3202,7741,7733,7746,3182,3222,7749,7736,7735,7748,7755,7750,
7753,3302,7754,7738,7757,7740,7744,7731,3042,3162,7767,7743,7745,7751,7727,
7752,7739,7756,7730,7742,10467,10468,10469,10470,15967,15547,21442,242,246,
261,262,243,981,10587,10547,244,268,267,266,270,269,7737) AND
mpp.project_id (+)= x.project_id AND x.po_line_location_id=
pll.line_location_id and mpp.organization_id (+)= DECODE(
:v_mps_consume_profile_value, 1,
x.MRP_TO_Organization_ID,
x.Organization_ID) and DECODE( :v_mps_consume_profile_value,
1, x.MRP_DESTINATION_TYPE_CODE, x.DESTINATION_TYPE_CODE)=
'INVENTORY' AND ( x.RN2>-1 OR x.RN3>-1)

NEVER diff a database by queries unless the data is small
(MAYBE?).
If you do the above use MERGE statements at least.

If you have Enterprise Ed. use CDC, streams, etc use those instead.

And last but not least a timestamp alone will NEVER give you consistent data as to what changed from a source.

Did I get that right?

Tom, this is the best thread! I am in a similar situation as many people participating in this discussion and I was wondering if you have any suggestions. We are about to build and application and we have 2 databases. Well, one db we have total control over and one is "theirs" (but "they" are willing to work with us :) ). Database #1 - where the app will be taking its data from (our users will be making modifications to data here). However, there are going to be times (rarely but still) that this db will be down. So we are going to replicate 14 tables that this app needs on database #2. This db is going to be on our site (rather then off site like db 1) and will always be up. These 14 tables have data from 5,000 to 2 million records. So it varies. Our users want to have a functionality where they basically push a button and 14 tables that are located in our database (db 2) get refreshed with data from db 1. Ok, so many options here.. We can write a pl/sql proc that would merge the data as some suggested here. But this approach kind of worries me. What if the users decide to refresh every hour? One of our tables has 2 mil rows in it¿I read some about Change Data Capture, but I am not sure if you can do it on demand. Or maybe streams? What do you suggest? Any input would be greatly appreciated. Thank you!

nika

p.s. both databases run Oracle 9i

Tom, thank you for your response. We considered using materialized views as a solution (copying 14 tables from one db to another on demand). However, in the past we ran into an unexplainable issue with mv. For instance, we have a mv that is based on a complicated select with 15 where conditions. The mv is set up to refresh on demand as follows:
----------------------------------------------------------

CREATE MATERIALIZED VIEW BANINST1.WEBSERVICEEMPLDATA_MV
NOLOGGING
BUILD IMMEDIATE
REFRESH FORCE
START WITH TO_DATE('05-Jan-2007 22:10:34','dd-mon-yyyy hh24:mi:ss')
NEXT (SYSDATE + 1/24)
WITH PRIMARY KEY
AS
SELECT /* ALL_ROWS */
spriden_pidm, spriden_id,spriden_last_name,spriden_first_name, spriden_mi,
SPRIDEN_SEARCH_LAST_NAME, SPRIDEN_SEARCH_FIRST_NAME, SPRIDEN_SEARCH_MI, spbpers_ssn,
spbpers_name_prefix, spbpers_name_suffix,spbpers_birth_date,spbpers_ethn_code, spbpers_sex,
TO_CHAR (TRUNC ((SYSDATE - spbpers_birth_date) / 365,0)) age,
pebempl_empl_status, pebempl_orgn_code_home, pebempl_ecls_code, pebempl_bcat_code,
pebempl_current_hire_date, pebempl_adj_service_date,
nbrjobs_pidm, nbrjobs_posn, nbrjobs_suff, nbrjobs_ecls_code, nbrjobs_desc,
nbrjobs_orgn_code_ts, nbrjobs_ann_salary, nbbposn_pcls_code,
NBRJOBS_EFFECTIVE_DATE, NBRBJOB_BEGIN_DATE, NBRBJOB_END_DATE,
nbrbjob_posn, nbrbjob_contract_type
FROM saturn.spriden, saturn.spbpers, posnctl.nbrjobs X, posnctl.nbrbjob, payroll.pebempl, posnctl.nbbposn
WHERE pebempl_pidm = nbrbjob_pidm
AND pebempl_pidm = spriden_pidm
AND spriden_pidm = spbpers_pidm
AND spriden_change_ind is null
AND nbrbjob_posn = nbrjobs_posn
AND nbrbjob_suff = nbrjobs_suff
AND nbrbjob_pidm = nbrjobs_pidm
AND nbbposn_posn = nbrjobs_posn
AND nbrjobs_effective_date = (SELECT max(nbrjobs_effective_date)
FROM posnctl.nbrjobs
WHERE nbrjobs_pidm = X.nbrjobs_pidm
AND nbrjobs_posn = X.nbrjobs_posn
AND nbrjobs_suff = X.nbrjobs_suff
AND nbrjobs_effective_date <= SYSDATE);

----------------------------------------------

The reason there is an /* ALL_ROWS */ hint is because accroding to documentation for materialized view to work you got to have CBO and we have RBO. Without this hint we are still having the problem.. Which is that as you can see this query is complicated. It takes 1.5. minutes to create the view (1.5. munutes to run the query). It refreshes fine. But, while it refreshes, the data from the view is not available. The thing is that an online application uses this view - hence, the data has to be real time. The view refreshes every hour and the select in the view takes about 1.5 min to run, so for about 1.5 minutes there are 0 records in the view. I understand that we are forcing Oracle to completely recreate the view every hour. I also understand that Oracle is truncating here, rather then deleting and inserting. But is there any way that the old data will remain available for users until the refresh completes? MV sounds like a great solution, but as I brought it up, our team went - yeah, right, we are going to run into that 'mysteriously data is not available problem' again...Any suggestions would be greatly appreciated. Thank you!

Nika

Sir,

Is it possible to synchronize two databases at the same time? I think "Stand by" database is only the viable solution but my colleagues suggest using Oracle Stream. I am quite confused, as I know Oracle Stream is an enriched edition of Oracle Replication.
Please help me get the right solution of bringing two databases into same position at any point of time.

Regards,

Sir,

Client's Requirement is JUST IN TIME replication. I know only Two Phase Commit (2PC) but it raises problem. So I suggested them using Oracle STREAM, so two databases located in different locations can be synchronized in near real time. There is no requirement of standby database or data guard because two databases run simultaneously in different locations.

Is there any way to synchronize two databases at the same time or nearly same time?

Tom---I was searching for my answers on Oracle Streams 9iR2 for our environment. why streams and why not streams?

I think I am still not clear on this...So asking you atlast..
We have to synchronize two oracle databases at different locations i.e maintaining two read write consistent copies of same data.This is because the network between these two locations is very bad and so to satisfy the user community at one location we thought of using 2-way Streams replication.So do you think this will suit or solve our issues?

I am open for new ideas...Because I did start working on setting up streams.Though it was successful I relaized it is very difficult to maintain and troubleshoot in case of errors.

Awaiting your thoughts..

Tom,
I've been looking through your site and the net for the last few days, doing some research. I read that the Oracle Sales force automation was a disaster, but we seem to have a requirement for it. We have a fully web enabled Forms application that will be used for online, however our client has a requirement for offline sales force automation (placing orders, checking stale inventory). Our plan is to install Oracle database and sales force application on the laptops. When internet is available, to use the web, when not, use their local version. I'm just having trouble figuring out the best way to sync the local database with the main system (upload any orders, and refresh inventory). Would "streams" be your recommended approach? Are there any other or newer technologies that would help with this?

Thanks,
Scott

Hi Tom,
I have scenario where i need to sync the tableA in DB1 to tableB in DB2, Please find the scenario below
1. I have a provisioning system with High Available configuration - Say site 1
2. Site 1 has high available DB setup using Oracle Data Guard (Primary and Secondary DB)
3. There is DR site for my provisioning system with same HA configuration - Say site 2.

Now i want to sync a table called RoutingData from site 1 (Primary DB - db0 of site 1) to DR site DB (Primary DB - db0 of site 2) table RoutingData (Both Site DA has same schema and tables)
Note: Both the sites have exact same configuration.

What i want to achieve
1. When Site 1 is acting as Active/Primary site and Site 2 acting as secondary site then i want to sync the table RoutingData from Site1 DB to Site 2 DB table RoutingData.
2. When Site 2 acting as active/primary site and site1 acting as Secondary site then i want to sync the table RoutingData from Site 2 DB to Site 1 DB table RoutingData.

I need a suggestion/solution to achieve the above scenarios.
Thanks
Prem