Choosing the best Oracle backup strategy for your environment

Can you explain what the different options are for backing up Oracle?

There are a few different Oracle backup strategies. The old-timer method is user-managed backups, which are shell scripts that put the database in and out of backup mode or shut it down, and then you back up the file. Then there is RMAN, which is the preferred method that Oracle pushes you toward, and is also the only way to integrate it with your third-party backup application. There's also something called an import/export, which is not technically a backup, but it is one of the ways people back up Oracle. And then there are also myriad ways outside of the Oracle spectrum such as continuous data protection (CDP). It's important to understand that those types of technologies are also going after the Oracle backup space, just like they're going after the SQL Server and Exchange backup space. But we're going to focus on the Oracle-sanctioned ways of doing that type of backup.

Can you explain what user-managed backups are?

One Oracle backup strategy is called user-managed backups. With user-managed backups, you basically have a shell script that is written by the user -- it is not provided by Oracle at all -- that utilizes built-in commands inside the Oracle database. This includes everything from shutting down the instance to just placing the database in backup mode. Oracle has actually added features to this over time. For example, several years ago the only way you could do this was you had to query Oracle for a list of all the tablespaces, and then you had to put each individual instance into backup mode. Now Oracle has provided a single command where you can put the entire instance in backup mode. That takes out a lot of the guesswork. Without that single command, you had to be pretty good a scripting to properly query Oracle, get the list of tablespaces, parse that list and pass that list that onto another loop that then puts all of those tablespaces into backup mode. It's not rocket science, but it has eluded a number of people and it's easy to make a mistake; so it's nice that Oracle has added that feature. So with a user-managed backup, you basically either put it into offline mode or you put it in hot backup mode and then back up the files. When you're done, you have to start the database up and take it out of backup mode.

Using user-managed backups implies that you're going to do something else with the files. Either you'll back them up with a commercial backup product, or you might just be copying them over to another piece of storage to then grab those files with your commercial backup product. This is becoming a pretty common practice, and it's not necessarily one that I like. But more and more database administrators (DBAs) are managing the backups of their databases. They will write these scripts and essentially dump the database by copying the files over after they've put them into backup mode or shut them down. Then they'll put them into a directory and the backup person will back up these files.

I'm not a fan of the user-managed backup approach because of the challenges it presents. First of all, it's all about you with user-managed backups, and it's easy to make a mistake and mess up a script. If you do mess up a script, then you're not going to get any help from Oracle. Another problem, especially if you're using a two-step method that I described earlier, is that you have to buy that extra disk which can be expensive. You also have to coordinate all of these activities. Your backup software is just backing up files, and it doesn't necessarily know that those files are any good. It doesn't know if the DBA's shell script worked or didn't work. That disconnect between the backup process and the database process is the biggest challenge with user-managed backups.

On the restore side, you're biggest challenge is that you're also managing the restore. It is up to you to start the database at various modes and manually restore files from tape or disk. User-managed backups are a very manual process, whereas with RMAN, that whole process can be completely automated. Another big thing to consider with user-managed backups is that there's no concept of incremental backup. So every backup you do from Oracle is going to be full with bandwidth, CPU and storage challenges.

However, despite its challenges, many people like user-managed backups for two reasons. First, they've been doing it that way for a long time and they know all the risks and do it properly. Second, they see it as a cheaper option. Because if you go the RMAN route, you're going to be required to buy an agent for your backup software.

Speaking of RMAN, how does RMAN work and why is it the preferred method of backing up Oracle?

RMAN is built into Oracle and it is essentially an API. You can actually log into RMAN, and give it a command that you can script. And that command talks to an API which talks to a database. And you can simply say "back up database." With RMAN, the most basic setup will then copy the database for you to a location that you've specified. Compare that to user-managed backups where you have to put the backup in a database and then figure out where the files are and copy all the appropriate files. With RMAN, even without a commercial backup app, you can log into RMAN for free and say, "back up database." It will then copy the files to a directory that you've specified.

The overall benefit here is that RMAN is simpler than user-managed backups. It's also risk-free. But the real joy of RMAN comes from being able to combine it with your commercial backup application. A library connects RMAN with Oracle, so instead of going to disk, your backup application replaces that library with their library. In other words, with RMAN you now have this highway straight from your backup app straight into Oracle that issues that command, and then there's this stream of data that comes out of Oracle that gets passed back to your backup application, which they can then put on whatever device you have specified, whether that's disk, tape or a deduplicated target. Again, compare that to the user-managed backups, which don't have that connection. User-managed backups have a disconnect between the backup app and what's actually happening.

With RMAN there is a connection all the way down to the database and all the way back to the database. If anything happens anywhere along that route and we don't get the backup, the backup app knows and it can report that. But that doesn't happen in the user-managed space. Let's say, for example, the dump didn't work and so the backup app keeps grabbing a file and it doesn't realize the file has been changed because of that disconnect. But with RMAN, it's just a straight connection right into Oracle. So that's the big difference. It's supported by Oracle and it's supported by your third-party backup application.

There are a lot of advantages to RMAN because of that connection. First, you can do true incremental backups. Your backup application says, "I'm here to back you up and I want to do an incremental." RMAN keeps a bitmap and it knows the blocks that have been changed since the last time a backup was performed, so it just says, "Here are the blocks," and sends them off. You can also do full backups with RMAN, so you have this complete connection all the way down to your database. And of course, you don't need an intermediate staging device.

RMAN is the preferred Oracle backup strategy because it's simpler and easier for Oracle to support.

But the best thing about RMAN is when it comes time to restore a database that's broken. You simply start RMAN and tell it to restore the database and recover the database. When RMAN restores the database, it puts the files back where they belong. When it recovers the database, it applies "redo," in Oracle speak. It has to apply redo because if you restore a backup from last night, there are some changes that happened since that time, so it needs to go into the redo logs to get the redo to replay the backups -- that's why it's referred to as recovery after the restore. During a restore and recovery, you can login to RMAN, and RMAN knows that it's been connected to your backup app. You can then run the RMAN recovery restore database command. RMAN talks to the API, that talks to the library, that talks to the backup app, and RMAN figures out the files that are broken and puts those files back in place. Then you tell RMAN to recover the database. Again, it talks to the API, it talks to the library, and the library talks to the backup app and says, "here's the redo that you need to do that." So it's this complete connection back and forth, making it a simple recovery as opposed to the more complicated recovery from user-managed backups.

Finally, there's actually an option from Oracle themselves. If you find yourself interested in RMAN but you don't have a backup app connected to RMAN, Oracle offers a backup software product that they acquired several years ago that you can buy from them and manage tape libraries, etc. That way your entire backup solution can come straight from Oracle.

What's the use case for import/export backups?

The thing with import/export is that it's not really a backup; it's more of an archive because it's a single point in time copy of the contents and structure of the database. Import/export can only be used to recover either the entire database or a part of the database to a single point in time when you took that export. The biggest problem with using it for a backup is it needs to be done in a particular mode. Backups can be done when the database is running, but export cannot because if you're making any updates to the database while you're exporting it, you're going to have referential integrity problems. It's like pulling the rug out from underneath your backups. You need to stop updates to the database while you're exporting. So that's the first really big problem for most people.

The second problem is that you can only use it to recover to that exact point in time; if you made any changes after that you cannot use redo like you can with backups to roll forward to a point in time -- it has to be at the time of the export. But people do it because they think it's easier. I don't think it's easier. The one advantage that the export has is that it tends to be more version independent than RMAN or user-managed backups. If you've got an RMAN or user-managed backup of Oracle, it's pretty much tied to the version of Oracle that was running at the time of the backup, but export can be used by a previous or more recent version of Oracle. So the main reason people use export is as an archive to provide a historical view of the database that isn't necessarily tied to a version.

The other thing people use exports for is tablespace recovery. It has been historically difficult in Oracle to recover a single table. If a table goes bad you have to recover an entire tablespace. But there's been some movement and added functionality with that. So if that's the reason why you're sticking with exports, perhaps you should talk to Oracle again and get an update on how they solve those problems today with RMAN.

What are some Oracle backup best practices to keep in mind?

First, back it up. Second, consider RMAN. If you read some of my writings from 10 years ago, you'll see that I was pushing user-managed backups over RMAN. I originally thought RMAN made backups more complicated, but it's now so much easier than user-managed backups. So the first is to really consider moving towards RMAN if you're not using it today. One of the main arguments against RMAN, the license issue, has gone away with most commercial backup products. So within the last year, Symantec Backup Exec and NetBackup, CommVault Simpana, and others have all gone to a capacity-based license that doesn't have extra charges for databases. So if you've moved to that capacity-based license you can start using RMAN without paying extra.

The next thing is to not ignore your redo. Really think about the redo logs and how to back them up. My best answer for those is you can back up your database directly to tape, but your redo logs really need to be going to disk. The last 24 to 36 hours of redo logs should be accessible directly on disk because if you do any significant amount of redo on tape, it's going to be slow, but if you do it on disk it'll be 10 times faster.