Recommended Approach to Programmatically Backing Up/Restoring Oracle DB

Question

Supposing I have an ever-growing Oracle DB on one server, and I want to duplicate this schema and the data on another server - what would be the best approach to achieving this as part of a bespoke .net app?

What I've Tried/Researched:

• RMAN - seems to be the most appropriate technology available for writing batch files to do a backup but I don't feel as though I'd have as much control over it as if it were developed from scratch in C#.
• PL/SQL - I could use stored procs to export data to csv and combine it with C#?
• DataReaders and official Oracle API's - Seems to me like the most favourable approach as it is what I'm most familiar with - but am I reinventing the wheel given the fact that the above two technologies have the power to create these backups?
• 'OOTB' oracle executables - https://stackoverflow.com/questions/12524284/taking-oracle-database-dump-via-c-sharp-net http://rockingtechnology.blogspot.co.uk/2011/06/oracle-backup-and-restore-code-in-cnet.html

• Alistair: What is the reason for duplicating the database? Is it just to have a backup? If so are you prepared to lose data in case the primary server dies? Are you wanting high availability?
• DeeMac: Good question, thanks very much for your feedback. It is for backup yes, it's all in aid of DR. Why would the consideration towards losing data need to be made?
• Alistair: I asked as obviously scheduled backups have a period of time in which changes can be made without being backed up (e.g. 24 hours for nightly backups). This can be both good and bad depending on what you are attempting to achieve. If this is just regarding DR, have you considered filesystem backups?
• DeeMac: Again, very constructive feedback, thank you. I have not looked down the avenue of filesystem backups as of yet, I think I'd prefer not to if it implies dealing with system files (assuming it does) as opposed to the database directly. The user will want diagnostics on the DB such as size etc. anyway so chances are it'll prove beneficial to interface with the DB itself for other reasons anyway. It's not important that 24 hours worth is missing. rockingtechnology.blogspot.co.uk/2011/06/… < what's your thoughts on that approach?
• Alistair: The only issue I would have with that approach is how long will the backups take. As those web applications wait. I would have a very good look at docs.oracle.com/cd/B19306_01/server.102/b14220/backrec.htm and I would also recommend Rman as Ivan did. Preferably I would do backup as a scheduled task instead of an adhoc click button approach

Answer 1

If you want to clone prod. db into dev. env. regulary I would recommend you to use RMAN for whole database. Or exp/imp (expdp/impdp) for single schema. RMAN is not easy to use for developers who have no Oracle experience, but finally you will find this approach fastest and safest.

If you're about to clone your prod db into some reporting server maybe you should look at "Active Data Guard".

In Oracle you will find the "right" technology for every purpose. The final decision will depend on your real need(goal).