And what about changes in Target DB ? If TARGET DB is in read write mode sooner or later changes WILL OCCUR in TARGET, if these changes were not applied in SOURCE, they will be "lost" ? And what about conflicts, ie, anyone changed data and/or structures in TARGET, if this is not applied in SOURCE the data coming from SOURCE could be "incompatible"...
So, OR Target DB must be in READ-ONLY (to avoid changes not replicated to SOURCE), OR your replication methos MUST be able to deal with conflicts....

Regards,

J. Laurindo Chiappa

Hi,

Thanks for the response. Please can you advice some other method other than golden gate . ?

Before anything, let me say that if Source database is your Production and it contains sensitive data and this data must be sent to DEV database, you WILL NEED apply some kind of DATA MASKING : is a very very VERY bad idea to give real data to Developers.... If you do it, sooner or later you could receive a non-cool little visit of Mr not-so-nice guy the lawyer, to talk about data theft... A bad thing, really...

OK : the short answer for you is that many more options to data replication exists in Oracle RDBMS besides GoldenGate - for non-programming options, look for Distributed Transactions, Oracle Advanced Replication, Oracle Streams, DBMS_COMPARISON, CDC (Change Data Capture), Materialized Views, triggers doing DMLs via dblinks.... In addition to data replication, you have too FULL DATABASE sync options/features, such as Oracle Standby databases (Logical or Physical) - of course, with full db sync I´m thinking in the possibility of the DEV db being a "clone" of the Source database... And option such as restore of (incremental, of course) Source database backup in DEV server, or transportable tablespaces or options like these maybe could be feasible, too, thinking in full db sync...
These are more or less the available options....

BUT to give you a better answer, we really need to know from you :

- both database are in archive mode ?

- what Operaions Systems/environments are PROD and DEV ?? What the hardwares and softwares in point for both environments ?

- you Really want the full DEV database to be absolutelly equal to Source (PROD) ??? I ask due to the fact that many times only a handful of tables/indexes/views are actively modified , maybe replicating only these few ones could satisfy you... In this case you could replicate only the tables being used by the application and give to the DEVs the right to create their own tables, and thus all the modifications needed would be done in these tables, not in the app tables, so eliminating the chace for conflicts...

- how much delay is to be allowed between Source/PROD data and DEV data ? Is viable to have, say, yesterday´s data in DEV/target DB ??

- we are speaking about roughly what sizes, to avaliate full db sync options ? Is there a reliable network connection between the SOURCE and DEV databases ?

- DEV really need to be in read/write mode ??? If yes, as I said before you WILL NEED to resolve conflicts (things like one database deleting a record while the other update it, say, OR structural changes such as columns being remove/added and alike) - the majority of the replic options/features is capable of dealing with conflicts but this is a *** very **** complex thing, and it is something that you WILL need to setup according to your needs - no way to give to you a generic conflict-handler that works in any case/situation

Only with all this answers we could give a better guidance to you...

Regards,

J. Laurindo Chiappa

P.S. : I will not even cite the non-Oracle options because the majority of them are paid, and if you don´t have $$ for Goldengate maybe you don´t have $$$ for them, too...

Hi J. Laurindo Chiappa , Thank you so much for the suggestion.
Please find my response below and provide the convinient sync method.

- both database are in archive mode ?
##yes

- what Operaions Systems/environments are PROD and DEV ?? What the hardwares and softwares in point for both environments ?

##The two enviornments are in dev only. Source DB is used by DEVLOPER and Target DB will be used by QA team.

- you Really want the full DEV database to be absolutelly equal to Source (PROD) ??? I ask due to the fact that many times only a handful of tables/indexes/views are actively modified , maybe replicating only these few ones could satisfy you... In this case you could replicate only the tables being used by the application and give to the DEVs the right to create their own tables, and thus all the modifications needed would be done in these tables, not in the app tables, so eliminating the chace for conflicts...

##If we apply any changes in DEV db(source) then it has to reflect in the QA db(Target db).

- how much delay is to be allowed between Source/PROD data and DEV data ? Is viable to have, say, yesterday´s data in DEV/target DB ??
Delay of 6 hr is fine.


- we are speaking about roughly what sizes, to avaliate full db sync options ? Is there a reliable network connection between the SOURCE and DEV databases ?
#around 130 gb of data.YES, there is a network connection between source and Target.

- DEV really need to be in read/write mode ??? If yes, as I said before you WILL NEED to resolve conflicts (things like one database deleting a record while the other update it, say, OR structural changes such as columns being remove/added and alike) - the majority of the replic options/features is capable of dealing with conflicts but this is a *** very **** complex thing, and it is something that you WILL need to setup according to your needs - no way to give to you a generic conflict-handler that works in any case/situation
# If target is read only also fine.

Hi : first, if Source is not PROD, it does not contains sensitive data, so my obs about data security will not apply...

Ok : given your answer, no one and only option will arise as the BEST : in truth ALL the options given to you could be applied.... Please get the references in Oracle Documentation (and check the LIMITATIONS for each one), follow the links with small Examples that I will give to you and see what you will choose...

Some details :

==> due to the fact that TARGET can be READ-only , the Replication / data sync options will ALL be possible in a much much easier way : see http://www.akadia.com/services/ora_replication_guide.html , http://www.orafaq.com/wiki/Advanced_Replication_FAQ , http://oracledbascriptsfromajith.blogspot.com.br/2010/10/oracle-advanced-replication-setup.html and https://oracle-base.com/articles/8i/advanced-replication as Examples

==> due to the fact that the size of the databasse is something < 200 GBs, both are in archive mode and a fast and reliable network exists between the two servers, the FULL DB SYNC options are Very Doable ones : say, a STANDBY DATABASE, maybe with the DELAY option... If/when the TARGET db (that will act as HOMOLOGATION environment, as you say) must become READ-WRITE for some time, you could temporarily "turn off" the standby and later "re-connect", see http://satya-dba.blogspot.com.br/2012/06/snapshot-standby-databases-oracle.html as an example

==> if the TARGET db is not 24x7, the possibility for CLONE options (ie, restore Source backup in TARGET, or do a RMAN clone operation) is open too : see https://oracle-base.com/articles/11g/duplicate-database-using-rman-11gr2 as an example

==> you don´t answeered if data replication (not full db, only a few tables OR a given schema, say) is applicable : if so, see http://ulfet.blogspot.com.br/2012/07/oracle-streams-example-schema-base.html and http://dharamdba.blogspot.com.br/2012/03/oracle-streams-replication-at-schema.html , https://oracle-base.com/articles/misc/materialized-views and http://www.akadia.com/services/ora_readonly_snapshot.html as examples

I repeat, NO SINGLE OPTION would be specially recommended in your scene, so see all of them, try / test all and choose the best for you ... Personally I would have a pendant for the snapshot standby option (physycal), because with this option you will have very few restrictions (no datatype restrictions, say, in opposition to Replication and Streams) and imho it is a very Robust option, built-in in the Oracle RDBMS for eons, but it is a personal preferency, only...

Regards,

J. Laurindo Chiappa