<p>Using Oracle 10g with our testing server what is the most efficient/easy way to backup and restore a database to a static point, assuming that you always want to go back to the given point once a backup has been created.</p>

<p>A sample use case would be the following</p>

<ol>
<li>install and configure all software</li>
<li>Modify data to the base testing point</li>
<li>take a backup somehow (this is part of the question, how to do this)</li>
<li>do testing</li>
<li>return to step 3 state (restore back to backup point, this is the other half of the question)</li>
</ol>

<p><strong>Optimally this would be completed through sqlplus or rman or some other scriptable method.</strong></p>

[{'answer_id': 67694, 'author': 'Rich Adams', 'author_id': 10018, 'author_profile': 'https://Stackoverflow.com/users/10018', 'pm_score': 3, 'selected': False, 'text': '<p>You could use a feature in Oracle called <a href="http://www.oracle.com/technology/deploy/availability/htdocs/Flashback_Overview.htm" rel="noreferrer">Flashback</a> which allows you to create a restore point, which you can easily jump back to after you\'ve done testing.</p>\n\n<p>Quoted from the site,</p>\n\n<blockquote>\n  <p>Flashback Database is like a \'rewind\n  button\' for your database. It provides\n  database point in time recovery\n  without requiring a backup of the\n  database to first be restored. When\n  you eliminate the time it takes to\n  restore a database backup from tape,\n  database point in time recovery is\n  fast.</p>\n</blockquote>\n'}, {'answer_id': 67709, 'author': 'Michael Ridley', 'author_id': 4838, 'author_profile': 'https://Stackoverflow.com/users/4838', 'pm_score': 1, 'selected': False, 'text': "<p>If you are using a filesystem that supports copy-on-write snapshots, you could set up the database to the state that you want.  Then shut down everything and take a filesystem snapshot.  Then go about your testing and when you're ready to start over you could roll back the snapshot.  This might be simpler than other options, assuming you have a filesystem which supports snapshots.</p>\n"}, {'answer_id': 70060, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 4, 'selected': True, 'text': '<p>You do not need to take a backup at your base time. Just enable flashback database, create a guaranteed restore point, run your tests and flashback to the previously created restore point.</p>\n\n<p>The steps for this would be:</p>\n\n<ol>\n<li>Startup the instance in mount mode.<p>\nstartup force mount;</li>\n<li>Create the restore point.<p>\ncreate restore point before_test guarantee flashback database;</li>\n<li>Open the database.<p>\nalter database open;</li>\n<li>Run your tests.<p></li>\n<li>Shutdown and mount the instance.<p>\nshutdown immediate;\nstartup mount;</li>\n<li>Flashback to the restore point.<p>\nflashback database to restore point before_test;</li>\n<li>Open the database.<p>\nalter database open;</li>\n</ol>\n'}, {'answer_id': 71241, 'author': 'robertpostill', 'author_id': 11219, 'author_profile': 'https://Stackoverflow.com/users/11219', 'pm_score': 2, 'selected': False, 'text': '<p>From my experience import/export is probably the way to go.  Export creates a logical snapshot of your DB so you won\'t find it useful for big DBs or exacting performance requirements.  However it works great for making snapshots and whatnot to use on a number of machines.</p>\n\n<p>I used it on a rails project to get a prod snapshot that we could swap between developers for integration testing and we did the job within rake scripts.  We wrote a small sqlplus script that destroyed the DB then imported the dump file over the top.</p>\n\n<p>Some articles you may want to check:\n<a href="http://www.orafaq.com/wiki/Import_Export_FAQ" rel="nofollow noreferrer">OraFAQ Cheatsheet</a>\n<a href="http://wiki.oracle.com/page/Oracle+export+and+import+?t=anon" rel="nofollow noreferrer">Oracle Wiki</a></p>\n\n<p>Oracle apparently don\'t like imp/exp any more in favour of <a href="http://www.oracle-base.com/articles/10g/OracleDataPump10g.php" rel="nofollow noreferrer">data pump</a>, when we used data pump we needed things we couldn\'t have (i.e. SYSDBA privileges we couldn\'t get in a shared environment).  So take a look but don\'t be disheartened if data pump is not your bag, the old imp/exp are still there :) </p>\n\n<p>I can\'t recommend RMAN for this kind of thing becuase RMAN takes a lot of setup and will need config in the DB (it also has its own catalog DB for backups which is a pain in the proverbial for a bare metal restore).</p>\n'}, {'answer_id': 71446, 'author': 'Matthew Watson', 'author_id': 3839, 'author_profile': 'https://Stackoverflow.com/users/3839', 'pm_score': 0, 'selected': False, 'text': '<p>@Michael Ridley solution is perfectly scriptable, and will work with any version of oracle.</p>\n\n<p>This is exactly what I do, I have a script which runs weekly to</p>\n\n<ol>\n<li>Rollback the file system</li>\n<li>Apply production archive logs</li>\n<li>Take new "Pre-Data-Masking" FS snapshot</li>\n<li>Reset logs</li>\n<li>Apply "preproduction" data masking.</li>\n<li>Take new "Post-Data-Masking" snapshot (allows rollback to post masked data)</li>\n<li>Open database</li>\n</ol>\n\n<p>This allows us to keep our development databases close to our production database.</p>\n\n<p>To do this I use ZFS.</p>\n\n<p>This method can also be used for your applications, or even you entire "environment" (eg, you could "rollback" your entire environment with a single (scripted) command.</p>\n\n<p>If you are running 10g though, the first thing you\'d probably want to look into is Flashback, as its built into the database.</p>\n'}]
['https://Stackoverflow.com/questions/67666', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1474/']