Let’s say you have a database that everything can be recreated just by re-running a process and no nightly backups are required- the database would be quicker to recreate from the backup running on it’s source database than restoring from a nightly backup or taking the performance hit of doing so. Let’s say you have three tablespaces that are on I/O fusion cards that aren’t protected by hardware mirroring and losing these three could be more of a challenge than the business would like to have.
What options could you come up with to protect those tablespaces?
- Oracle Streams?
- CTAS of the objects within those tablespaces to the mirrored disks?
Now you realize that the data that resides on these tablespaces are NEVER written to post their initial creation- they are read only objects!
This was a solution that we came up due to this unique scenario-
- As part of the steps to create this data, added to the scripts, post the final writes to all objects, and before they are made available to the users, made the three tablespaces residing on I/O fusion cards read only.
- Perform an O/S copy to our backup directory as part of this same script- completely automated.
- If a change is required for any reason, make the tablespaces read/write
- Once complete, put the tablespaces back to read only and perform the backup steps again.
Most DBA’s are a bit obsessive compulsive and I performed numerous tests, verifying that I could alter the tablespaces to read only and then perform all the standard processing that would occur in a normal day.
I then had a lot of fun copying the files over, deleting a few from the original location and attempting the processing again, watching the database shrill in complaint as it could not locate one or another datafile.
Testing involved ensuring that I performed a cycle of the database to a restricted mode to alter the tablespaces to read only, as any access to the objects could create a situation where the alter statement could hang, waiting till it had sole access to all datafiles involved. Tasks such as cycling through logs, creating other objects and re-starting the process, having the database act as if nothing was amiss to begin with is a bit bizarre. It almost seemed too simple at times, but this gives us the recoverability we need for our I/O fusion card data, but doesn’t impact the best performance and best choice for a unique database environment.