Thursday
Sep242015

Update to Oracle backups for Windows Script #1 - support for NOARCHIVELOG

Scroll to the bottom for original post "Simple Backups for Oracle on Windows" as it contains both scripts necessary for the solution.
I updated the first script so by including an optional flag you can backup databases that do not have archivelog mode enabled (and still backup those that do).
Thursday
Jan222015

Simple Backups for Oracle on Windows

If you're hosting an Oracle database on Windows I've come up with a quick-and-dirty method for getting it backed up.

I've written two .bat files that can be scheduled through Task Scheduler in the Windows OS.

One does a full backup and is meant to be run nightly. It leverages RMAN to take a full backup along with ARCHIVELOG and stash it locally.

The second is meant to supplement the first .bat and should be scheduled to run throughout the day (e.g. hourly). It will copy your backups out to a network location if they're not already there. It looks at your Flash Recovery Area (FRA) and copies the files without tidying up the FRA. In this way you have your latest archivelogs locally available to Oracle, but in the event of a complete local server failure your recovery point can be as recent as when you last successfully ran this script to stash your more recent archivelogs to a safe remote spot.

Script #1 highlights

  • Copies all .ora files
  • Backs up the controlfile
  • Checks backup for corruption and sends e-mail alerts upon failure
  • Checks that all components are present and sends e-mail alerts upon failure

Here is Script #1:

Here is Script #2:

Summary

Script #1 is clearly the workhorse of this solution. Comments in the script itself list the specifics for prerequisites (i.e. both scripts rely heavily on robocopy.exe, your database needs to be in archivelog mode) and other stuff like getting the most out of the six different error conditions checks.

Script #1 also sets parameters for your RMAN environment. Generally speaking you'll want to set the degree of parallelism to what works best in your environment. You may want to tune for encryption, compression, whatever.

Script #1 comments also outline the arguments you'll need to specify when running the script.

Finally you'll also want to search for the following string to tweak things like your favorite SMTP server and other settings hard-coded in the script:

:::::>

Script #2 is a necessity for moving the backups off the local server in the event of a server-wide failure. Keep in mind too that if run frequently throughout the day then the latest and greatest archivelogs are copied to a safe place. More recent files means you'll be able to restore to a more recent time in the unfortunate event of a failure.

As with any script you rely on to take good backups please ensure you're able to successfully restore from backups generated from whatever solution you choose. My advice would be to perform this restore in a parallel environment on a completely separate server to avoid clobbering the database.

Members of a properly configured "ora_dba" local Windows security group should have no permissions-related problems executing this script which can be done through the native Windows Task Scheduler or your favorite enterprise task scheduling tool.

Finally when editing these scripts if your formatting doesn't look quite right I had no problems when using the Notepad++ tool. It's a great editor and should still be available as a free download. Google that.

All feedback welcome. Hope someone finds this helpful and good luck with your backups.