• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Database Synchronization

 
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi All,

In our application, we have total four databases - dev/test/stage/production.
our requirement is, if prod database is updated by users, then automically our other test databases get updated.

So is there any tool/way which can synchronize one database if other is modified?

Please help.

Thanks,
Manisha
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Databases usually provide tools out the box to do this (or at lease the big databases do). Which database are you using?
 
author
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
If you want to replace one database with another, or replace a strict setup of tables, there are tools for that. But I suspect if you have 4 databases you might need something more fine grain such as "replacing records in table Y after date X", which case I recommend writing your own synchronization tool.

By the way, what you're describing worries me. I'm familiar with 4-database teirs (dev/test/stag/prod) but usually we want the data kept completely separate. In fact, in many systems I've worked on the majority of developers do not have access to production-level data. In some cases (like medical systems for example) in violates laws for developers to be able to access it. I'd reconsider any solution that pushes production data immediately down to test systems and argue it should never be done. The reasoning being "If you took the time to setup 4 separate database tiers, then you probably dont want to muddle them". Else, mine as well just have 1-2 databases and not bother with the setup.
 
manisha Gupta Garg
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Paul and Scott..for the responses !

well we use Oracle database. The purpose of synchronizing all databases is - after 4 years of our application, some data available in test databases are not in use and new updates are happening in prod database. And we are the admins of our application and yes we have full access for production. Main reason for availing this facility to us is just to reduce our efforts in synchronising the databases. Beacuse it is us, who update the prod database along with other normal users too.

Please suggest me any solution to synchronize other test databases according to prod data and NOT VICE VERSA.
One solution is creating datalink and copying prod data in stage database. But that is very time consuming process and do copying one by one table not all at one time

Thanks,
Manisha
 
Ranch Hand
Posts: 182
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Manisha,

It seems to me that it is an issue that concerns dbas' attention. They mostly have common issues like this and may have a better solution than a developer can come up.

Regards,

Fatih.
 
Ranch Hand
Posts: 466
1
IntelliJ IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You can use Oracle Links and created jobs that calls some procedure which fetches data from other database and insert/update the database.

However I would recommend that you write a utility that exports production database objects and imports them into other databases. This activity can be automated via job to scheduled daily / weekly.
 
Paul Sturrock
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


Please suggest me any solution to synchronize other test databases according to prod data and NOT VICE VERSA.


That sounds particularily messy. What do you do if a tester working against the test database enters data that will prevent the import from production working? Sounds like a bit of a mine field - I reckon this might dramatically increase the effort synchronizing databses rather than reduce it.

Would it not be easier to restore these databses from production nightly?
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
A datapump export/import of the full database is very fast. I would do that when needed and then load any specific test data you need present afterwards.
 
manisha Gupta Garg
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
hi Jeanne,


could you please let me know more about 'datapump' ?

Thanks,
Manisha
 
Paul Sturrock
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Data pump is an Oracle tool to export/import data from/to another database. See the Oracle documentation for a full explanation.
 
manisha Gupta Garg
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
ok thanks Paul
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic