Best-Practices: Backing up Oracle Databases

Topic

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

Environment

  • Oracle SQL Databases
  • Datto Windows Agent
  • Datto Linux Agent

Description

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 MercuryFTP/iSCSI protocols. You can also save the database directly to a NAS share for recovery in the long run.
  • 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.

Multi-tenant database concerns

There is currently an Oracle VSS writer bug that affects VSS backups taken by third party solutions like Datto when the Oracle db is configured as a multitenant solution.

When the backup job is initiated, the Datto device asks Windows to take a snapshot of the protected system. The OracleVSS Writer on the protected system performs necessary functions to prepare and snapshot the Oracle databases.

The issue occurs when the writer attempts to place any tablespaces into backup-mode. Any Pluggable DataBases (PDBs) will fail to enter this mode and cause the job to fail. Normally, once VSS gives the all clear, the datto agent will begin copying data from the snapshot, but this never happens due to the above error.

This error can cause the databases to end up in an inconsistent state. We've observed freezing and there is a potential for data loss.

Some of the symptoms manifested are:

  • The server freezes during a backup.
  • The datto agent log will present VSS errors for the oracle writer similar to this: Thu 05/11/20 1:45:58 pm - Writer "Oracle VSS Writer -XXXXX" Failed: True Status: 9 (VSS_WS_FAILED_AT_FREEZE) Writer Failure code: 0x800423f4 (<Unknown error code>) Writer ID: XXXXXX Instance ID: XXXXXX

Until this issue is fully patched by Oracle, we suggest the following work-around:

  1. Set the Datto Windows Agent backup Option to best in Configure Agent Settings > Backup Engine
  2. Exclude the Oracle VSS Writer in the list of VSS Writers available
  3. Exclude the Drive that houses the Oracle Database entirely, in Configure Agent Settings > Volume Level Backup Control This will prevent the Datto Windows Agent from interacting with the database, and will prevent the VSS writer from being used.
  4. Create a NAS Share on the Datto
  5. Map this new NAS Share drive to the Oracle server as a network drive
  6. Use Oracle Recovery Manager, RMAN (external link) to take backups of the Oracle Database, and then have the backups sent to the NAS Share.
  7. Configure the NAS Share on the Datto to take snapshots of itself, so that you have copies of the backups going to our Offsite Servers.

While this method is not ideal, it does provide a workable method until such time as Oracle addresses the issue.