Home » Server Options » Replication » Data Integrator (database)
Data Integrator [message #648544] Thu, 25 February 2016 02:19 Go to next message
deceneu
Messages: 4
Registered: February 2016
Junior Member
Hi,
on project, that our company should start, raised a discussion regarding solution to use, for the following problem:
- we have a big database (1T/month - window) that scale very good on real-time data collecting project,
- was requested to build some reports, based on this database.

To meet this requirement was decided to replicate data from primary database into another one, because of performance reasons.
For this requirement I'm asking what is the best way to do it?

Our way was to use:
1. Data Gard to replicate primary database, but this solution is not applicably, because secondary database should be fully functional in read/write mode.
2. Data Pump partial, what is not applicable because, offline time for database is not accepted.
3. CDC - not applicable because of many problems that we have in the past and also is deprecated.
4. last one, to develop custom scripts for data transfers, that is not accepted from maintenance costs.

Please Help with best solution !
Re: Data Integrator [message #648545 is a reply to message #648544] Thu, 25 February 2016 03:01 Go to previous messageGo to next message
Michel Cadot
Messages: 65194
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Oracle version (4 decimals)?

Re: Data Integrator [message #648557 is a reply to message #648545] Thu, 25 February 2016 07:10 Go to previous messageGo to next message
deceneu
Messages: 4
Registered: February 2016
Junior Member
oracle 11.2.0.1.0
Re: Data Integrator [message #648559 is a reply to message #648557] Thu, 25 February 2016 07:43 Go to previous messageGo to next message
Michel Cadot
Messages: 65194
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you buy Active Data Guard option you can use the first solution.
But if your standby database is only needed for reporting I don't see why you need it is open in read write mode and not read only.
If you don't need a 24/24 standby, you can use snapshot standby (which is free) during reporting period/batch.
You can also use a logical standby if you only need to update tables that are not in the primary database.

Note: you should upgrade to 11.2.0.4.

Re: Data Integrator [message #648568 is a reply to message #648559] Thu, 25 February 2016 09:48 Go to previous messageGo to next message
deceneu
Messages: 4
Registered: February 2016
Junior Member
Thank you Michel,
your remarks a precious for me, I'll look forward into logical stand by for Data Guard.

Was requested a fully functional 24/24 data-base because, for reporting is using Oracle APEX system.
Also reporting is only the first request, but they want to have options open for futures requests. Like, data are really big in a month windows, and will grow up quickly during years, and for sure will be necessary some data aggregations.
Re: Data Integrator [message #648570 is a reply to message #648568] Thu, 25 February 2016 09:59 Go to previous messageGo to next message
Michel Cadot
Messages: 65194
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It seems logical standby is what you need.
It is also possible to use cascade replication:
(1) OLTP -> (2) Data warehouse -> (3) Data mart (aggregation)
Between (2) and (3) you can use materialized views.

The architecture depends on your current (detailed) and future needs... and on the money you can have.
As I said, is the standby a 24/24, 7/7? Does it need to have the current data or a delay is possible? Often the user needs are only to have in the morning the reports of the end of the day before.

[Updated on: Thu, 25 February 2016 10:00]

Report message to a moderator

Re: Data Integrator [message #648573 is a reply to message #648570] Thu, 25 February 2016 11:49 Go to previous messageGo to next message
John Watson
Messages: 7148
Registered: January 2010
Location: Global Village
Senior Member
Michel, you get
Quote:
Thank you Michel,
your remarks a precious for me,

but on his thread here,
https://community.oracle.com/thread/3901986
OP has not even bothered to reply to anyone Mad

@OP If you want to run an APEX app (something you might have mentioned on your topic on OTN) then you need some form of replication. As I said there, Materialized View is the obvious answer.
Re: Data Integrator [message #648597 is a reply to message #648570] Fri, 26 February 2016 02:33 Go to previous message
deceneu
Messages: 4
Registered: February 2016
Junior Member
Michel Cadot wrote on Thu, 25 February 2016 09:59

It seems logical standby is what you need.
It is also possible to use cascade replication:
(1) OLTP -> (2) Data warehouse -> (3) Data mart (aggregation)
Between (2) and (3) you can use materialized views.

The architecture depends on your current (detailed) and future needs... and on the money you can have.
As I said, is the standby a 24/24, 7/7? Does it need to have the current data or a delay is possible? Often the user needs are only to have in the morning the reports of the end of the day before.



Michel you so right,
that I want to scream that way that all, my company and the customer will hear this.
All normal people will do like this, but not our customer.
He wants to see real-time info on active system, some kind of real-time Data warehouse.

I'll try to explain here in more details.
The main reason for this project to start is to be able provide some information of the processing data from active system.
If it is not able to respond with a delay of maximum 10 min, there will be no reason for the project to exists.
In other words, primary database (Oracle 11gR2) will provide data, secondary database should replicate data incrementally during the all day. We can negotiate 1/2 hour maximum gap.

The main database process medium 3 000 of transactions per second. Replicated data will be around 1G per hour, in the local fiber-link network. Network overhead is not our regarding. The stand-by time for primary is 24/24 7/7 and for secondary 20/24 7/7.

It was a hard 5 years work, of tuning at all levels, hardware and software. We can not think to introduce some overhead for queering or aggregations on the main database, not even triggering some bath processing queries for materialized views.

We are using RAC for primary database and DataGuard for backup of RAC.
The idea of DataGuard seems magical at the beginning, but has to many constraints.
RAC node - was also considered, but not applied because of reasons below.
Regarding costs, it should be fixed and known from the beginning.

Normally secondary database will suffer a lot of changes on the table structures, and also on tables rows data, and we want to keep options open for the futures.

Please give more infos about use of cascade replication:
(1-primary db) OLTP -> (2-secondary db) Data warehouse -> (3-secondary db) Data mart (aggregation)
Between (2) and (3) you can use materialized views.

Does I understood well ?


Previous Topic: No records were replicated
Next Topic: Cannot create 2 different users on database to handle extract and replicat operations.
Goto Forum:
  


Current Time: Sun Nov 19 23:36:28 CST 2017

Total time taken to generate the page: 0.02465 seconds