Oracle Databases: Backup Best-Practices

Follow

Scope

This article discusses best-practices for backing up Oracle databases.

Overview

The Oracle SQL database platform is available on both Windows and Linux environments. The Datto solution takes an image-based, block-level backup of Oracle databases, which you use as an image for restoration.

Oracle databases can exist in two modes: ARCHIVELOG and NOARCHIVELOG. If the database is in ARCHIVELOG mode, the Datto backup solution can create a shadow copy when the database is open or closed; if the database is in NOARCHIVELOG mode, the copy can only be created when the database is closed.

There are also two methods to use when backing up the database. The first is using the Datto solution, and the second is using RMAN (Recovery Manager) from Oracle. This article will explain the benefits of both.

Windows

In Windows, the Oracle VSS writer is installed automatically as part of the database. Datto backups leverage the Oracle VSS Writer, which is application-aware, to create a shadow copy. The shadow copy contains the database files, control files, and server parameter file.

Datto recommends backing up the database in ARCHIVELOG mode, so that shadow copies of your data can be captured whether the database is open or closed. If the database is in NOARCHIVELOG mode, then the database must be in a consistent state when you create the VSS snapshot. This often means that the database needs to be shut down in order for a consistent state to be caught.

If the database is not in ARCHIVELOG mode, then you may see VSS errors similar to the following in the Windows Event Viewer:

VSS-00044: Database in NOARCHIVELOG mode must be in a consistent state for backup.
Cause : The database was open read/write or mounted after a SHUTDOWN IMMEDIATE or SHUTDOWN ABORT. 
Action : Either mount the database in a consistent state or open it read-only to back up the database files.

Non Vss-Aware Applications and Applications Without Direct DB/API Access

For applications that do not have direct DB/API access for an interactive backup, the following workarounds exist:

  • Stop and then start the database to get a consistent backup, and then restart the database after the backup has completed. With the Datto Windows Agent, you can watch for the disk to disconnect from the protected machine since its backups are performed through Mercury/iSCSI protocols. You can also save the database directly to a NAS share for recovery in the long run.

  • When using the ShadowSnap Backup Agent, you can switch to the STC Backup Engine, but will likely need to clear the archive logs.

  • You can use RMAN to perform the initial and incremental backups, and then copy those to a NAS share.

Linux

The Datto Linux Agent runs quiescing scripts from /etc/datto/dla/pps/. Since Oracle has different syntaxes compared to other RDBMS, such as MySQL, you will need to create a custom script to perform necessary pre- and post-processing. You can use the script to truncate logs as necessary, and perform other tasks.

Optimizations

Ensure that the database response time is fast enough that the Oracle VSS Writer can freeze the database in a timely manner. If it is too slow, it could cause VSS failures and timeouts, causing backups to not complete. Important performance considerations include CPU time, I/O time and non-idle wait time.

Optimize space usage by eliminating or minimizing fragmentation and whitespace. This will reduce the size of the database, and increase read/write times.

Periodically restart the Oracle VSS Writer, as it is known to have memory leaks. As a fallback, you can leverage RMAN to perform the database backups on Windows systems.


Was this article helpful?

0 out of 0 found this helpful

You must sign in before voting on this article.

Want to talk about it? Head on over to our Community Forum!