Reset and Reopen - Oracle

Now that we've executed the first test of our new batch process, we need to "reset" the data back to how it appeared before running the test. We'll shut down and remount the database, then use the ''flashback database" command to "reset" the database to our specified restore point:

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 159383552 bytes

Fixed Size 1259696 bytes

Variable Size 62916432 bytes

Database Buffers 92274688 bytes

Redo Buffers 2932736 bytes

Database mounted.

SQL> flashback database to restore point before_test;

Flashback complete.

We've now "reset" the database back to the restore point. We need to re-open the database, but because we're essentially doing a point-in-time recovery, we need to use the "resetlogs" command to recreate the redo logs and create a new instantiation of the database:

SQL> alter database open resetlogs;

Database altered.

We can verify that we once again see 200 rows in the "testuser.test" table, that our "testuser.logging" table is empty, and that we're ready to test another iteration of our batch processing algorithm:

SQL> select count(*) from testuser.test;

COUNT(*)

----------

200

SQL> select count(*) from testuser.logging;

COUNT(*)

----------

0

Once we've completed our several iterations of testing, we're ready to remove the guaranteed restore point and free up whatever space the database used to manage the restore point. We accomplish this with the following:

SQL> drop restore point before_test;

Restore point dropped.

Conclusion

Restore points provide a means for a DBA to label a particular point-in-time in the timeline of the database with a user-defined name, without digging into the details of SCNs or timestamps. The guaranteed restore point takes this a step further, providing the ability to guarantee a flashback database is possible until the DBA decides it is no longer needed, along with the ability to perform a flashback database without enabling flashback logging (with a reduced level of functionality). In conclusion, restore points make an invaluable addition to the DBA's arsenal of tools, useful any time a point-in-time recovery is needed.

NOTE: All code tested and known to be working on Oracle 10.2.0.2, running on 32-bit SLES9 (linux).