Oracle Database Backups - A Beginners Guide

A key part of running a production Oracle Database is making sure you can get it back online quickly when something goes wrong.

The focus of this article is on the broad steps that must be taken to backup your database, and subsequently restore it when something goes wrong.

There are other data recovery situations where other tools should be used in favor of backups, but they are out of the scope of this article.

Oracle Database Backup And Restore Process

Backups are not a goal per se, the ability to recover and restore data is the final goal (of which database backups form an important part).

The Oracle tool commonly used to perform backup, restore and recovery operations is called RMAN ( Recovery MANager). We’ll learn more about RMAN later in the article.

Three Stage Recovery Process For Oracle Backups

To be able to recover a database, you need to perform the following actions:

1. Backup data BEFORE the incident or failure occurs.
2. Restore database files AFTER the incident or failure occurs.
3. Recover the database files (if the backup was taken while database was in use, see below for more details).

Types of Backups in Oracle Database

Database backups can be classified in different ways. We are going to explain some of them in the following sections.

There are really two types of Oracle database backups:

1. Offline Backups (sometimes called ‘cold’ or ‘consistent’ backups)
2. Online Backups (sometimes called ‘hot’ or ‘inconsistent’ backups)

Offline vs Online Backups

“Offline” (or “Cold” or “Consistent”) backups are those performed when the database has been closed and is not accepting traffic.

“Online” (also called “Hot” or “Inconsistent”) backups are those that are performed while the database is open and accessible to other users.

Oracle Database Offline Backups

With offline backups, since the database is not open to users, no changes can occur while the backup is taken. This is very good, as we can ensure that our backup didn’t miss anything.

Offline backups are the most easy to understand because you’re really just copying files. This means that offline backups can be performed any number of tools.

• Regular OS utilities or scripts that makes a copy of the Oracle database files manually (datafiles, online redo log files).
• Any utility that can create a snapshot of the whole filesystem.
• Cloud vendor specific filesystem snapshotting tools.
• The Oracle RMAN Backup command.

Restoring a cold backup is equally simple, you just need to put the files back in the right place (or restore the entire filesystem), then start the database server back up again. Since there was no user activity during the backup process, no recovery step is required since all data is consistent.

Oracle Database Online Backups

Online backups are more tricky, so should only be performed with the RMAN tool. They also require the database to be configured in ARCHIVELOG mode.

While an online backup has the great benefit that it does not require the database to be closed while backup is generated, the recovery process is a little more complicated.

Since data can be changing while the backup process is running, after data restoration we are required to run a recovery step. The recovery step reapplies changes that occurred while the backup was being taken, using information stored in the archived log files. This is required to make the restored database consistent.

Backup Strategies

Within both offline and online backups there are different strategies that many organizations employ to perform their backups. I cover a few of these below.

RMAN Backup Set vs Image Copy Backups

A Backup Set is a logical RMAN backup strategy that ignores unused file blocks, reducing the size of the final backup and the time required to create it. Only blocks that contain data are backed up. So for example if you created a database with ten 100 Gb files assigned to it, but only 5 Gb of each file have been used, RMAN will produce a single backup piece with a final size of 50 Gb (10 x 5gb). Backup sets can be also compressed and / encrypted, features not available on Image Copies.

Image copies are also created with RMAN, but they are an actual exact copy of each database file, including all unused space. While an image copy may seem the same as an offline backup performed with any OS tool, creating them with RMAN stores file locations in the RMAN catalog, which automates a part of the restoration process, making it much simpler.

Differential and Cumulative Oracle Backups

What should a backup contain? Up until now we’ve assumed that a backup contains the whole set of data in a database, but that’s not always true.

Full backups imply that all data stored in the database is backed up. All cold backups (taken with RMAN or using OS tools) and RMAN image copies are full backups.

Differential and Cumulative backups store only data that has changed since a previous backup. Differential and Cumulative backups can be created only with RMAN using the Backup Set format. To create them, RMAN requires a previous backup to exist called the Incremental Level 0 backup (also created with RMAN).

Differential and cumulative backups, while similar, are different.

• Differential backups contain only changes since the previous Differential backup
• Cumulative backups contain all changes since the last Incremental Level 0 backup.

Pros and Cons of Differential and Cumulative Backups

Let’s say we create a Incremental Level 0 backup each Sunday.

The benefit of Differential backups is that the size of the backup and execution time required to create it remain small, regardless of the time since the last Level 0 backup. This is because each Differential backup stores just the changes since the last.

So if we run a Differential backup every day, they will remain a constant size (1 day of data).

The negative is that if a failure occurs on Friday, we need to restore the initial Level 0 backup taken on Sunday plus each and every Differential backup created between then and Friday.

On the other hand, with a cumulative backup strategy, each backup taken after initial Level 0 will include all changes since that Level 0 backup (increasing in size and time each day). Conversely, if a failure occurs on Friday, we just need to restore last Sunday’s Level 0 plus Friday’s Cumulative backup (since it contains all cumulative changes since last Level 0 backup).

Cloud Block Storage Snapshots

The wide usage of Oracle in cloud environments has encouraged cloud providers to design strategies for creating efficient and consistent database backups.

Amazon takes advantage of an Oracle 12c enhancement called “Storage Snapshot Optimization” to perform snapshot copies of database files without the need to put the database or tablespace in BACKUP mode (although the process does generate extra redo activity that can impact performance).

Azure does not use this feature, but has automated the process of putting the database in BACKUP mode, taking a snapshot, and then returning it to operation. This allows administrators to take snapshots directly from Azure without manual interaction with the database.

What do you need to back up?

An Oracle database is made of several types of files, some of them contain your data (they are called Datafiles and they are logically grouped into Tablespaces). There are more files than these, for example files with undo and redo information to guarantee that a rollback or recovery can be performed, and others which contain information about database file structures and locations.

To be able to recover from any possible media failure, all of them need a backup strategy. Oracle RMAN allows you to backup:

• An entire Database.
• An entire Tablespace.
• A specific Datafile.
• Control Files (contain information about structure and file location).
• Archive Logs (contain information to reapply or rollback changes made on each transaction).
• SPFile (contain parameter values used to startup the database).

Other Ways of Keeping Oracle Data Safe

Although they are not backups, the following features may help keep an Oracle database in a consistent state when particular issues that do not require restore and recovery arise.

Export

Although exports are not actually backups, Oracle allows you to create an export of all or part of your data using a utility called expdp (Export Data Pump). An export may be useful for copying data from one database to another, or to recover from a user generated issues (like a user incorrectly deleting or updating their data).

Flashback

Flashbacks are a set of technologies that allows users to:

• Query data as it was on a particular moment in the past.
• Query database metadata to identify structural changes applied to the database over time.
• Recover tables deleted from the database without needing to restore the entire database or tablespace.
• Rollback a transaction or set of transactions while database is online.

The Oracle Backup Strategy To Use

Unfortunately, there is no one-size-fits-all strategy for me to recommend to everyone. There are multiple pros and cons that need to be taken into account when designing a backup and recovery strategy.

That said, many organizations require their databases to be online 24x7. In that case, using hot backups and a mix of full and incremental or differential backup sets is a pretty standard way to maintain uptime, reduce the performance impact of taking backups, and minimize the time and storage requirements that more frequent full backups would require.

When Your Database Is Online, Use Beekeeper Studio

👋 Hey there. Thanks for reading all the way to the bottom.

When we’re not writing about Oracle backups, we’re building Beekeeper Studio, a modern, intuitive database management app with excellent Oracle support.

---