Automating Oracle Snapshots

I've previously written about how to Creating a new Oracle Database using an Array Snapshot, but this process involved a lot of manual steps - obviously the best way to handle this is to actually script the steps to automate the entire process.

The following script is an example of doing exactly that - it covers the entire process of refreshing an Oracle database, including both the Oracle level (including Grid) and array level tasks if using an XtremIO array.

NOTE THAT THIS SCRIPT IS INTENDED AS AN EXAMPLE ONLY. It will likely need some changes to work in your specific environment, but it should at least get you started down the path to automating the entire process. Please be sure to test it in a test environment before even considering using it in a production (or even close to production) environment!

The High-level Steps carried out by the script are :

• Copy database is shutdown
• Copy ASM diskgroup is unmounted
• XtremIO snapshot is refreshed
• ASM diskgroup is renamed then mounted
• Datafile locations in database are changed to point to new diskgroup
• Database is started in mount mode
• Database is renamed using "nid"
• Database is opened with resetlogs (required after rename)

All of the configuration for what to refresh is contained towards the top of the script, and most of it should be fairly obvious.

It is possible to remount the snapshot onto the same host as the original, but if you do this then you MUST specify the OS-level devices of the COPY in the "NEWDGDEVICES" configuration so that it can tell the difference between the original and the copy. Be very cautious using standard /dev/sdX style device names for doing this, as they can change on a reboot - it's best to use udev to configure device names based on (for example) the NAA or WWN of the LUN.

You can download the script from here. You'll also need the renamefile.sql script, available here.