You can use a backup of a Oracle database 12C in archivelog mode to restore and recover up to the last committed transaction prior to a failure. Therefore, unlike a backup of a noarchivelog mode database, this type of backup is not necessarily intended to be used to reset the database back to a point in time in the past from which no recovery can be applied. The purpose of a backup of an archivelog mode database is usually to restore the database and roll forward and apply transactions to fully recover the database.
This has significant implications for the backups. Recall that for a noarchivelog mode database, DBAs sometimes include the online redo logs as part of the backup. For a backup of an archivelog mode database, you should never include the online redo logs in the backup. The online redo logs contain the most currently generated redo transaction information for the Oracle database 12C. Any transactions in the current online redo logs that haven’t been archived are required for a complete recovery. In the event of a failure, you don’t want to overwrite the online redo logs with backups of online redo logs taken from a point in time in the past; this would result in the inability to perform a complete recovery.
The high-level steps for a cold backup of a database in archivelog mode are identical to those for a noarchivelog mode database:
- Determine where to copy the backup files and how much space is required.
- Identify the locations and names of the database files to copy.
- Shut down the database with the IMMEDIATE, TRANSACTIONAL, or NORMAL clause.
- Copy the files (identified in step 2) to the backup location (determined in step 1).
- Restart your database.
The main difference between the cold archivelog mode backup and noarchivelog mode backup is that in step 2, you run this query to identify the files to be backed up:
select name from v$datafile union select name from v$controlfile;
Also, you don’t need to back up the data files associated with the TEMP tablespace. As of Oracle 10g, Oracle automatically attempts to create missing data files associated with the TEMP tablespace (for locally managed temp tablespaces) when the database is started.
Restoring and recovering with a cold backup of a database in archivelog mode is nearly identical to the restore and recovery from a hot backup.
UNDERSTANDING THE MECHANICS DOES MATTER
Knowing how a hot backup works also helps in untangling and surviving difficult RMAN scenarios. RMAN is a sophisticated and highly automated tool. With just a few commands, you can back up, restore, and recover your database. However, if there is a failure with any RMAN command or step, an understanding of Oracle’s underlying internal restore-and-recovery architecture pays huge dividends. A detailed knowledge of how to restore and recover from a hot backup helps you logically think your way through any RMAN scenario.
When you ride a bike, understanding how the derailleurs and gears and shifting work helps a great deal. You can usually tell when a rider knows only to push one button to go slower and another button to go faster. Riders who understand in more detail how the chain moves between gears will always be smoother at shifting gears. My editor, Jonathan Gennick, recounted the following anecdote while reading an early draft of this article:
“I loaned my bike to a guy the other week and went on a ride with him. You should have heard the horrible noises he conjured out of my derailleurs and drivetrain. I thought he was going to damage the bike. After a few minutes, he rode up to me and told me that my front derailleur wasn’t working right.
The derailleur was fine. He was just one of those guys who knows only how to push the button, without any understanding of what goes on underneath that action.”
Similarly, effort you put into understanding how backup and recovery is implemented pays off in the long run. You actually have less to remember—because your understanding of the underlying operation enables you to think through problems and solve them in ways that checklists don’t.