Chapter 6. Extended RMAN

The acronym RMAN stands for Oracle's Recovery Manager, with an emphasis on the word Recovery. Backups are worthless if you can't use them to restore lost data! RMAN is Oracle's recommended standard for database backups for any sized organization. There are other storage-based technologies that are also available for database backup—each one is vendor-specific. If your environment is still using the old-fashioned hot or cold backups (also known as user-managed), it is time to come out of the dinosaur age. The RMAN utility is easy to automate by scripting with the command-line version, which the provided example code for this chapter utilizes.

RMAN is used for many different tasks as part of the DBA to-do list presented back in Chapter 1 of this book. That importance deserves its own section in the book. This chapter will touch only briefly on some of the basics, with the understanding that you have gained some knowledge from previous chapters in this book, have read...

Recovery goals determine backup configuration

A successful RMAN implementation will include the formal definition of both backup and recovery goals. You could refer back to Chapter 5 for RTO and RPO as a starting point. Mean Time to Recover (MTTR) is another objective that will be touched on in this chapter.

MTTR (also known as fast-start checkpointing) is not enabled in 11g by default. It allows the database to recover (apply committed transactions, rollback uncommitted), which automatically occurs while bringing up a database after a crash or during an actual restore and recovery session. To enable this feature, the following database parameter is set to a non-zero number.

fast_start_mttr_target               integer     0

At the same time, reset the following database parameters back to zero:

LOG_CHECKPOINT_INTERVAL
LOG_CHECKPOINT_TIMEOUT
FAST_START_IO_TARGET

You should know from the previous chapters that faster checkpoints (synchronizing all the datafile headers, flushes data written to...

Backup types and the default configuration

This section lists a few definitions that will need to be referred to for understanding the recommended backup strategy:

• Backup sets: Default type of RMAN backup that contains what you want backed up, which can be datafiles, controlfiles, archived redo logs, or spfiles. Each backup set consists of pieces, which can be subsets (a backup of a large datafile cut up into small chunks) of the item you want backed up. You cannot cut up a large datafile across different backup sets or mix different types of files into the same backup set, but you can multiplex several database files into a single backup piece.

• Image copy: Type of backup that is a bit-for-bit copy of the original—the same as creating a copy at the OS level (with Unix cp or dd commands), but is known to the RMAN utility. Making it known to RMAN means that the location and header file information has been recorded in either the control file or, optionally, the RMAN catalog. Archived redo...

Oracle's recommended backup strategy

A rolling updated full backup strategy has its place in a production environment. Here is a short list of the best reasons and features:

• It reduces the number of repeated full backups

• It can reduce the restore time if switch to copy is used

• It reduces network bandwidth to transport backups offsite and incremental(s) take up less space

• Improved Tape bandwidth due to less reads

• Option for restoring objects changed with NOLOGGING option because changed data blocks are captured

• It synchronizes the physical standby with incremental merge changes

• There is no need to modify CONTROL_FILE_RECORD_KEEP_TIME

• It's useful for merging monthly datawarehouse backups with transportable tablespaces

Issues with incremental merge backups

Just as there are positives to Oracle's recommended method of backups and recovery, there are also some downsides that need to be covered:

• The amount of disk storage needed is higher for the image copy backup versus a single full RMAN backup. That...

Corruption detection

There are several types of database corruptions that can cause extensive data loss if due diligence is not taken to prevent it from happening in the first place:

• Datafile block corruption—physical or logical

• Table/index inconsistency

• Extents inconsistencies

• Data dictionary inconsistencies

Physical corruption

Physical corruption is most often caused by defective memory boards, power disruption, I/O controller problems, or broken sectors on a hard disk drive. A defective physical component prevents the complete write to the data block, which also includes the accompanying update to the header block. You may have block corruption, but the database will appear to operate normally because reads usually don't have an issue but writes will report the corruption error as something similar to the following:

ORA-01578:
ORACLE data block corrupted (file # string, block # string)

Don't always expect issues such as corruption to show up in an obvious way. While doing testing for this chapter...

Data Recovery Adviser

Oracle has the ability to create the script to repair the database. This is known as the Data Recovery Adviser (DRA). If you need to introduce your own corruption for testing purposes, you could research on the Internet for examples. The following example is how the DRA was used to restore a corrupted datafile.

I removed all backups before starting this scenario. This means that the recovery came from the redo and any archived logs. Cool! See the restoration from the redo section for more about this.

1. RMAN backup command detects a problem and it doesn't mention the word "corrupt".

   

   Notice that the alert log has the same date time stamp as the RMAN session, which detected the problem.

   

2. Using RMAN, I start investigating to see what RMAN knows about the problem.

   

3. Using RMAN, I ask the DRA what I should do about this problem—this is known as the advise failure command.

   

The contents of the repair script are included below:

RMAN>
 # restore and recover datafile
   sql 'alter database...

What does RMAN backup, restore, and recover?

Did you notice the addition of the word "restore" in the title of this section? Hopefully you did, as there is an important difference between restore and recover when it comes to Oracle. Restore is putting back the copy of the object (datafile, controlfile, spfile, archivelog) to a specific location. Recover is applying all of the transactions located in the online or offline (archived) redo and online undo segments to bring the database to a consistent state.

Consistency is where the checkpoint change numbers, for all datafiles agree with the checkpoint SCN of the database. That is your basic goal when doing database recovery. It is important to remember because anything that interrupts this process may result in lost transactions. Consistency is also important for the controlfile (it has its own checkpoint SCN), as Oracle takes a snapshot (read-consistent) of the controlfile if you back up the database when it is either mounted or open. The...

What doesn't RMAN backup, restore, and recover?

The following files are still considered critical, so here are a few comments on what their role is in the overall backup and recovery strategy for your organization.

• Online redo: Redo is archived for recovery

• Online undo: Usually not needed for recovery

• Temporary tablespaces: No need to backup; only used during queries

• Binaries: Multiple $ORACLE_HOMES and standbys provide some redundancy

• Password files: Can be recreated; requires database cycle if password is changed

• Parameter files (pfiles): Can be recreated or a temporary one put into place

• Diagnostic destination: Restore from OS backups

• SQL*Net files: Can be recreated; may need to recycle database

• Block change tracking file: File recreated the next time RMAN is run

• User-initiated RMAN backup logs: E-mail to DBA; retain on disk for historical comparisons

Online redo: Key to consistency

One of the biggest misunderstandings for a new DBA is assuming that it is safe or reasonable to backup...

What do I do before starting a restore and recovery?

Hopefully, you have taken the time to practice several different types of recovery scenarios before having to do this for real in a production situation. This can be extremely nerve wracking. So take some time to settle down before starting, as you will need to think clearly. Don't leave the scene of the crime until things are normal, even if it turns out badly. Here are some generalized steps on how to proceed when the database goes down:

• Determine what is actually wrong before continuing

  Error messages don't always point to exactly what is wrong. They can be vague or general unless some sort of tracing is enabled. First, start with the database alert log and check for the existence of core dumps. Oracle has designed an RMAN-directed Failure Adviser that can be used in conjunction with manual procedures, but the adviser won't replace any of the steps in this list.

  Run the recovery_status.sql script provided for this chapter. That might help...

RMAN cloning and standbys—physical, snapshot, or logical

In 11g, all databases are now identified uniquely by DBIDs. In earlier versions, every database copy made by a method other than the duplicate command had the same DBID. In previous versions of Oracle, you would use the NID utility to change the DBID and ORACLE_SID for certain cloning procedures to a test database. You couldn't do this for standby databases because changing the DBID or ORACLE_SID would invalidate the configuration and the Data Guard process would not work. This is no longer needed—RMAN can now duplicate a production database for any reason. There is just a small difference in the commands run for the different types.

It is very easy to create a copy of a production instance with RMAN using the duplicate command. Most often this is to refresh a database for testing environments. Duplicating a database for a standby is slightly different, but the basic concepts are the same. What is great about doing this process on a...

Summary

You should have figured out by now that RMAN is a highly advanced tool in your arsenal of weapons against things that may attack the database. It even helps protect the database against the DBA, the most powerful being that it is sworn to protect but is also the greatest threat.

One of the most important things you should take away from this chapter is to maintain a constant vigilant eye on the database alert log. Errors may occur within SQL*Plus or other clients that actually mask a much larger issue such as corruption.

In order to provide the best possible service to your customers while keeping outages to a minimum, RMAN is one of the best tools for database backups and recovery. Remember the following list because these are important to backup and recovery as part of the MAA in addition to using RMAN:

• Running in archivelog mode—gives the ability to recover more transactions.

• Multiple copies of the controlfile—redundancy on different mount points.

• Multiple copies of online redo logs...