RE: Real time data trasfer between 2 DB
- From: Upendra N <nupendra@xxxxxxxxxxx>
- To: <arvind.kumar@xxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 27 Aug 2010 09:33:09 -0400
Are you suggesting 10K GPS units will be sending inserts every 5 seconds to the
database?
60 seconds = 12 * 10K = 120K records? Since they are individual units I assume
the transactions will be committed for every record -120K transactions/sec.
You'll have lots of opportunities to tune your production database.. :) You may
want to look into using SSDs for redologs.
Is the data purged from these tables periodically? If so, that will define your
replication strategy, because any deletes you'd run on these tables will also
have to be replicated across in addition to the normal replication traffic. You
could also consider creating partitioned table with daily partitions so you can
drop them after certain time.
With this many records/transactions any trigger based replication will be a
nightmare to manage. You may want to look into Active Dataguard from 10g to 11g
(as Andrew suggested), Streams or logical standby database.
Near real-time replication requirements with this volume of data will be too
difficult to handle if there was a network glitch, you'll have difficult time
recovering from a 2-3 hour network outage. You really need to think if it is
even worth setting up the second database as supposed to dedicate a RAC node in
production for reporting/read only operations.
-Upendra
Subject: RE: Real time data trasfer between 2 DB
Date: Fri, 27 Aug 2010 09:59:19 +0200
From: Arvind.Kumar@xxxxxxxxxxxxx
To: nupendra@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Hi Upendra,
Thanks for the suggestions.
There
will be three tables for initial load with 2.7GB in total.
Destination
table only be used for reporting so not updates.
There
would be 10000GPS units which will send their coordinates and other
information
At every 5 second so (10000) records would need to be transferred
destination database with every refresh.
We
are using varchar2 and number in those tables.
Thanks
Arvind
Kumar
From: Upendra N
[mailto:nupendra@xxxxxxxxxxx]
Sent: Friday, August 27, 2010 7:51
AM
To: Arvind
Kumar (IT-SIS-UIF); oracle-l@xxxxxxxxxxxxx
Subject: RE: Real time data
trasfer between 2 DB
Hi Arvind,
Depends on the flexibility on "near real time" requirements, you
could use the following:
1. Materialized views - You could setup MVs with fast refresh to every minute
or 30 seconds refresh interval. Depending on your read-only or read write
requirements
on the tables, this may or may not work. This is easy to setup and easy to
manage.
2. Active Data guard - If you want only a read only database at the other site,
this will work.
3. Advanced replication - You could setup replication interval to 30-60
seconds. Provides flexibility to use tables that can be updated at both the
databases.
4. Oracle Streams - You could setup schema level or selective table level
replication, replication interval could be 30-60 seconds.
5. Depending on the flexibility you could also consider Oracle RAC - Metro
cluster where RAC nodes spread across multiple data centers.
Near real time could be 5 milliseconds or 5 minutes... you need to define this.
Some of the questions you need to clarify:
How large are the initial size of the tables?
Do you want update capabilities on the destination database?
How much data will you generate between the refresh interval?
Do you have any special data types that needs to be replicated?
-Upendra
Subject: Real time data trasfer
between 2 DB
Date: Fri, 27 Aug 2010 07:21:59 +0200
From: Arvind.Kumar@xxxxxxxxxxxxx
To: oracle-l@xxxxxxxxxxxxx
Greeting all,
What would be the best method to transfer data between two
databases (10g R2) in real time (near real time). It’s a transport system
where GPS devices installed in City Buses inserts records (location, timings)
into oracle DB then this data has to be transferred to other oracle DB for
reporting with a max latency on 5 second.
Any input or experience on similar scenario will be highly
appreciated.
Thanks
Arvind Kumar
Other related posts:
