How to Check Backups in Oracle 12c

By Chris Ruel, Michael Wessler

Backups are an important, yet sometimes overlooked, part of database management. Checking your Oracle 12c backups should be a regular part of your daily routine. Checking backups includes these things:

  • Ensuring the database backups completed successfully and without errors.

    From an Oracle DBA’s standpoint, you need to make sure the entire backup process is logged and no errors were detected. A common mistake is for the DBA to setup a database backup, but not monitor the output logs to confirm it was successful.

  • Following up occasionally with appropriate personal about OS backups.

  • Checking regularly to ensure the system admin is moving the database backup from disk to tape.

Too many environments put system backups on the back burner because they were scheduled jobs; no alerting was in place. If you subscribe to this methodology, you could be signing up for a heap of trouble. Be sure to verify that your backups are running without errors.

It would be extremely embarrassing and potentially career-limiting to discover you’ve “lost” a database because, as the DBA, you ignored e-mailed error messages for months.

Keep these backup tips in mind:

  • Oracle Recovery Manager has a LOG option that you can pass in with your backup script. This option forces RMAN to log the details for every step of the backup as it runs. This shell script example logs the output of your RMAN backup on Linux/UNIX:

    # Environment Settings
    export ORACLE_BASE=/u01/app/oracle
    export ORACLE_HOME=$ORACLE_BASE/product/12.1.0
    export ORACLE_SID=dev12c
    export BAK_DATE=`date '+%d%b%Y_%H_%M'`
    export PATH=$ORACLE_HOME/bin:$PATH
    # Run Backup
    rman target / cmdfile=full_hot_backup.rmn
    # Check Error Code
    Export ECODE=$?
    if [ $ECODE -gt 0 ]; then
          mailx –s "RMAN BACKUP FAILED!"

    The simple script, which you might schedule in cron, runs a backup script of your choice (called full_hot_backup.rmn) and logs the output to a file with the database name and date attached.

    After the backup completes, the script checks whether RMAN exited cleanly. It does this by checking a mechanism called an error code. Well-written programs have this mechanism. If the environment variable $? has a value of non-zero, something failed.

    It is recommended to have an if-then section that sends an e-mail if that backup failed. Of course, if there’s a failure, you have to find out why and fix it. Either way, implementing a notification similar to the example helps you tighten your backup and recovery planning.

  • Make sure the backup is usable. This task is important for all backups but is especially important if you store any backups to tape. Make sure those backups can be restored from tape and then actively recovered to a database. For obvious reasons, you don’t necessarily have to do this with your production database.

    You can do the restore to a different database. A common DBA task is to refresh test database images with production backup copies; this process is a good way to test your production backups. Either way, as reliable as you would like to think tapes can be, you need to test them.

    What if one of the tape’s heads is going bad and writing corrupt blocks? Silent corruption of backups is a rare but serious problem encountered by DBAs. Testing your backups helps you to practice your recovery strategies and validate your overall backup and recovery posture.

  • At the very least, date and save this backup log in a directory on the system. If you want to go one step further, have it e-mailed to you every day when the backup completes. If you need to use a backup from a specific date, saving the log files for those backups helps you confirm the backup is valid.

  • Look at the backup log for errors. Imagine how you’d feel if you experienced a failure and had to tell your boss that you can’t recover because the backup’s been failing for six months.