9 Performing Backup and Recovery
This chapter introduces you to Oracle Database backup and recovery with Oracle Recovery Manager (RMAN). After reading this chapter, you should be familiar with the basic concepts of Oracle Database backup and recovery operations, know how to implement a disk-based backup strategy, and perform simple repairs to database files.
This chapter contains the following sections:
See Also:
-
Oracle Database Backup and Recovery User's Guide for information about using RMAN to perform advanced backup and recovery operations
-
Oracle Database Backup and Recovery Reference for the syntax and semantics of RMAN commands
9.1 Overview of Database Backup and Recovery
The focus in Oracle Database backup and recovery is on the physical backup of database files, which permits you to reconstruct your database.
Oracle Recovery Manager (RMAN), a command-line tool, is the method preferred by Oracle for efficiently backing up and recovering your Oracle database. The files protected by the backup and recovery facilities built into RMAN include data files, control files, server parameter files, and archived redo log files. With these files you can reconstruct your database. RMAN is designed to work intimately with the server, providing block-level corruption detection during backup and restore. RMAN optimizes performance and space consumption during backup with file multiplexing and backup set compression, and integrates with leading tape and storage media products. The backup mechanisms work at the physical level to protect against file damage, such as the accidental deletion of a data file or the failure of a disk drive. RMAN can also be used to perform point-in-time recovery to recover from logical failures when other techniques such as flashback cannot be used.
Logical backups, such as exporting database objects such as tables or tablespaces, are a useful supplement to physical backups, but cannot protect your whole database. An effective backup strategy must be based on physical backups.
The Oracle Database flashback features provide a range of physical and logical data recovery tools as efficient, easy-to-use alternatives to physical and logical backups. The flashback features enable you to reverse the effects of unwanted database changes without restoring data files from backup.
This section provides links to the following flashback features that are documented in Oracle Database Backup and Recovery User’s Guide:
-
Oracle Flashback Table, which enables you to revert a table to its contents at a time in the recent past
-
Oracle Flashback Drop, which enables you to retrieve deleted (dropped) database tables
-
Oracle Flashback Database, which enables you to revert the entire database to a past point in time
The first two features operate at the logical level, whereas the last feature operates at the physical level. None of the preceding features requires advance preparation such as creating logical exports to allow for retrieval of your lost data, but Oracle Flashback Database requires the advance preparation of enabling the feature. You can use all of the features while your database is available. Oracle Database Backup and Recovery User’s Guide discusses the flashback features of Oracle Database at greater length.
9.1.1 Overview of Backing Up and Recovering CDBs and PDBs
-
Full backups
-
Incremental backups
-
Complete recovery
-
Point-in-time recovery (PITR)
-
Flashback Database
-
Reporting operations (such as listing backups and cross-checking backups)
About Connecting to CDBs and PDBs
You can connect to the root in one of the following ways:
-
Connect using operating system authentication
You are connected to the root as the
SYS
user with theSYSDBA
privilege. -
Connect locally as a common user
-
Connect as a common user through Oracle Net Services
To connect as TARGET
to a PDB, use one of the following methods:
-
Connect with a net service name that resolves to a database service for that PDB
-
Connect locally as a common user or local user with the
SYSDBA
orSYSBACKUP
privilege
Note:
Certain operations are not available when you connect directly to a PDB. See Oracle Database Backup and Recovery User’s Guide for a list of these operations.
See Also:
The following sections in the Oracle Database Backup and Recovery User’s Guide provide detailed information about backing up and recovering PDBs:
9.1.1.1 Backup and Complete Recovery of CDBs
TARGET
to the root. The connection must be established as a common user with the SYSDBA
or SYSBACKUP
privilege.
After you connect to the root, the same commands that are used to perform operations on non-CDBs are used to perform backup and complete recovery on the entire CDB.
See Also:
The following sections in the Oracle Database Backup and Recovery User’s Guide provide detailed information about performing backup and complete recovery of CDBs:
9.1.1.2 Backup and Complete Recovery of PDBs
To perform backup and complete recovery operations on a single PDB, you can connect as TARGET
to the PDB or to the root.
-
If you connect to the PDB, use the same commands that you would use to backup or recover non-CDBs. For example, to back up a PDB, use the
BACKUP DATABASE
command. -
If you connect to the root, use the
PLUGGABLE DATABASE
clause in your RMAN commands.The following command backs up the PDB
hrpdb
when connected to the root:BACKUP PLUGGABLE DATABASE hrpdb;
To perform backup and complete recovery operations on multiple PDBs using a single command, you must connect to the root. Use the PLUGGABLE DATABASE
clause followed by the list of PDBs on which you want to perform the operation. The following example backs up the PDBs hrpdb
, salespdb
, and invpdb
when connected to the root:
BACKUP PLUGGABLE DATABASE hrpdb, salespdb, invpdb;
See Also:
The following sections in the Oracle Database Backup and Recovery User’s Guide provide detailed information about backing up and recovering PDBs:
9.1.1.3 Point-in-Time Recovery of CDBs and PDBs
You can perform point-in-time recovery of the whole multitenant container database (CDB) or a particular pluggable database (PDB).
To perform point-in-time recovery of a CDB, you must connect to the root as a common user with the SYSDBA
or SYSBACKUP
privilege. The CDB must be mounted. Use the same commands that are used for non-CDBs to perform the point-in-time recovery.
When performing point-in-time recovery of PDBs, the PDBs being recovered must be closed but the CDB can be open. To perform point-in-time recovery of a single PDB, you can connect as TARGET
either to the root or the PDB. When connected to the PDB, use the same commands that are used for non-CDBs. When connected to the root, include the PLUGGABLE DATABASE
clause to specify the PDB that must be recovered. To perform point-in-time recovery of multiple PDBs, connect to the root and include the PLUGGABLE DATABASE
clause to specify the PDBs that must be recovered.
RMAN uses an auxiliary destination to store temporary files created during point-in-time recovery. If a fast recovery area has been configured, it is used as the auxiliary destination. You can explicitly specify an auxiliary destination using the AUXILIARY DESTINATION
clause in the RECOVER
command.
See Also:
The following sections in the Oracle Database Backup and Recovery User’s Guide for more information about point-in-time recovery.
9.1.1.4 Flashback of CDBs and PDBs
You can perform a Flashback Database operation for a whole multitenant container database (CDB) or for a particular pluggable (PDB).
To perform Flashback Database for a CDB, connect to the root as a common user with the SYSDBA
or SYSBACKUP
privilege and place the CDB in mount mode. The target point in time for the flashback operation can be specified using a CDB restore point, time expression, or target SCN. A CDB restore point is accessible to every PDB within the CDB. However, it does not reflect the PDB sub-incarnation of any of its PDBs.
To perform Flashback Database for a PDB, the PDB must be closed but the CDB can be open. The target point in time for the flashback operation can be specified using a CDB restore point, PDB restore point, clean PDB restore point, time expression, or target SCN. A PDB restore point is accessible only to the PDB in which it is defined and can be used for operations only on that PDB. Clean PDB restore points are only applicable to CDBs that use shared undo.
RMAN uses an auxiliary destination to store temporary files created during point-in-time recovery. By default, the fast recovery area is used as the auxiliary destination. You can explicitly specify an auxiliary destination using the AUXILIARY DESTINATION
clause in the RECOVER
command.
See Also:
The following sections in the Oracle Database Backup and Recovery User’s Guide for more information about flashback of CDBs and PDBs:
9.2 Database Backup and Recovery Concepts
ARCHIVELOG
mode). Restoring a database means copying the physical files that comprise the database from a backup medium, typically disk or tape, to their original or to new locations. Database recovery is the process of updating database files restored from a backup with the changes made to the database after the backup by applying incremental backups and redo logs to the restored files.
This section contains the following topics:
See Also:
-
Oracle Database Backup and Recovery User’s Guide for an overview of Oracle Recovery Manager (RMAN) architecture
-
Oracle Database Backup and Recovery User’s Guide for more conceptual details about RMAN backups
9.2.1 ARCHIVELOG and NOARCHIVELOG Mode
ARCHIVELOG
mode or NOARCHIVELOG
mode. The mode you choose depends on your availability and reliability requirements. It also impacts the type of backup and recovery operations that you can perform.
In NOARCHIVELOG
mode, the filled redo log groups that become inactive can be reused. This mode protects the database against instance failure, but not against media failure. In ARCHIVELOG
mode, filled groups of redo logs are archived. This mode protects the database from both instance and media failure, but may require additional hardware resources.
See Also:
For a detailed discussion about ARCHIVELOG
and NOARCHIVELOG
mode, see Oracle Database Administrator’s Guide
9.2.2 RMAN Repository
The primary location for the RMAN repository for a database is its control file. The importance of this metadata for RMAN is one more reason why protecting your control file is a vital part of your backup strategy. In some installations, a second copy of the RMAN repository is stored in a schema called the recovery catalog. The recovery catalog is located in a separate database and can store metadata for multiple target databases.
It is recommended that you use a recovery catalog. Because a recovery catalog stores metadata history for longer than the control file, you can perform a recovery that goes further back in time than the history in the control file. Also, if the target control file and all backups are lost, then the RMAN metadata in the recovery catalog can be used.
See Also:
For more information about creating and managing a recovery catalog, see Oracle Database Backup and Recovery User’s Guide
9.2.3 Image Copies and Backup Sets
Database backups created by Oracle Recovery Manager (RMAN) are stored as image copies or backup sets.
Image copies are exact byte-for-byte copies of files. You can create an image copy by copying a file at the operating system level. Unlike copying files at the operating system level, however, image copies created through RMAN are recorded in the RMAN repository so that RMAN can use these copies during database restore operations and recovery. RMAN can restore files only if they are recorded in the RMAN repository. RMAN can create image copies only on disk.
Backup sets are logical entities produced by the RMAN BACKUP
command. This command can produce one or more backup sets on disk or tape devices. Although image copies cannot use all RMAN features, their advantages are that you can apply incremental backups to them (synthetic full backups) and you can use them directly in place without first copying them, for very fast restores.
Each backup set contains one or more physical files called backup pieces. A backup piece stores the backup of one or more database files in a compact RMAN-specific format. One advantage of backup sets is that RMAN uses unused block compression to save space in backing up data files. Only those blocks in the data files that have been used to store data are included in the backup set. Backup sets can also be compressed, encrypted, sent to tape, and use advanced unused-space compression that is not available with datafile copies.
9.2.4 Full Backups and Incremental Backups
A full backup of a data file includes all used blocks of the data file. A full backup can be either an image copy or backup set.
An incremental backup copies only those blocks in a data file that change between backups. A level 0 incremental backup, which copies all blocks in the data file, is used as a starting point for an incremental backup strategy.
A level 1 incremental backup copies only images of blocks that have changed since the previous level 0 or level 1 incremental backup. Level 1 backups can be cumulative, in which case all blocks changed since the most recent level 0 backup are included, or differential, in which case only blocks changed since the most recent level 0 or level 1 incremental backup are included.
Incremental backups at level 0 can be either backup sets or image copies, but incremental backups at level 1 can only be backup sets.
A typical incremental strategy makes level 1 backups at regular intervals such as once each day.
During recovery, Oracle Recovery Manager (RMAN) will automatically apply both incremental backups and redo logs as required, to recover the database to the exact point in time desired.
9.2.5 Consistent and Inconsistent Backups
If the database is in ARCHIVELOG
mode, then you can make inconsistent backups that are recoverable using archived redo log files. Open database backups are inconsistent because the online redo log files contain changes not yet applied to the data files. The online redo log files must be archived and then backed up with the data files to ensure recoverability.
Despite the name, an inconsistent backup is as robust a form of backup as a consistent backup. The advantage of making inconsistent backups is that you can back up your database while the database is open for updates.
9.2.6 Media Recovery
Media recovery requires a control file, data files (typically restored from backup), and online and archived redo log files containing changes since the time the data files were backed up. Media recovery is most often used to recover from media failure, such as the loss of a file or disk, or a user error, such as the deletion of the contents of a table.
Media recovery can be a complete recovery or a point-in-time recovery. Complete recovery can apply to individual datafiles, tablespaces, or the entire database. Point-in-time recovery applies to the whole database (and also sometimes to individual tablespaces, with automation help from Oracle Recover Manager (RMAN)).
In a complete recovery, you restore backup data files and apply all changes from the archived and online redo log files to the data files. The database is returned to its state at the time of failure and can be opened with no loss of data.
In a point-in-time recovery, you return a database to its contents at a user-selected time in the past. You restore a backup of data files created before the target time and a complete set of archived redo log files from backup creation through the target time. Recovery applies changes between the backup time and the target time to the data files. All changes after the target time are discarded.
RMAN enables you to perform both a complete and a point-in-time recovery of your database. However, this documentation focuses on complete recovery.
See Also:
-
Oracle Database Backup and Recovery User’s Guide for more detailed information about point-in-time recovery
9.2.7 Fast Recovery Area
Oracle Database automatically manages this storage, deleting files that are no longer needed. Periodically copying backups to tape frees space in the fast recovery area for other files.
When you issue the RMAN BACKUP
command without specifying a backup destination, RMAN automatically backs up to the fast recovery area if it is configured.
Oracle recommends that you configure a fast recovery area to simplify backup management. Except as noted, this documentation assumes the use of a recovery area.
9.3 Configuring Your Database for Basic Backup and Recovery
This section explains how to set up your database to take advantage of Oracle suggested backup strategies.
To take maximum advantage of Oracle Database features that automatically manage backup and recovery files and operations, configure your database as follows:
-
Use a fast recovery area, which automates storage management for most backup-related files, and specify it as an archived redo log file destination.
-
Run the database in
ARCHIVELOG
mode so you can perform online backups and have data recovery options such as complete and point-in-time media recovery.
You must also set several policies governing which files are backed up, what format is used to store backups on disk, and when files become eligible for deletion.
In a multitenant environment, you must connect to the root and configure backup and recovery settings for the whole multitenant container database (CDB). These settings are applicable to the root and to all pluggable databases (PDBs) in the CDB.
This section contains the following topics:
9.3.1 Planning Space Usage and Location for the Fast Recovery Area
Oracle recommends placing the fast recovery area on a separate storage device from the working set of database files. Otherwise, the storage device becomes a single point of failure for your database.
The amount of storage space to allocate for the fast recovery area depends on the size and activity levels of your database and on your recovery objectives. Your objectives dictate what kinds of backups you use, when you make them, and how long to keep them.
This section contains the following topics:
9.3.1.1 About the Backup Retention Policy and the Fast Recovery Area
Space management in the fast recovery area is governed by a backup retention policy. A retention policy determines when files are obsolete, meaning that they are no longer needed to meet your data recovery objectives.
Retention policies can be based on redundancy of backups or on a recovery window (period of time).
When using a policy based on redundancy, you specify how many full or level 0 backups of each data file and control file that Oracle Recovery Manager (RMAN) keeps. If the number of full or level 0 backups for a specific data file or control file exceeds the redundancy setting, then RMAN considers the extra backups as obsolete.
When using a recovery policy based on a period of time (or window), you specify a time interval in days. Files are obsolete only when they are no longer needed for complete recovery or point-in-time recovery to a system change number (SCN) within the window. Therefore, a recovery retention policy based on a window is recommended.
The default retention policy is a redundancy of 1. Even after files in the fast recovery area are obsolete, they are typically not deleted until space is needed for new files. If space permits, files recently moved to tape remain on disk to avoid restoring them from tape for a recovery. The automatic deletion of obsolete files and files moved to tape from the fast recovery area makes it a convenient archiving destination. Other destinations require manual deletion of logs.
See Also:
-
Oracle Database Backup and Recovery User’s Guide for examples of configuring a redundancy-based policy.
9.3.1.2 About the Fast Recovery Area Size
If your backup strategy includes incremental backups, then add enough space to the fast recovery area for these files. If you can move some backups to tape, then you can reduce the size of the fast recovery area. Note that retrieving files from tape causes longer database restore operations and recovery times.
See Also:
Oracle Database Backup and Recovery User’s Guide for information about handling a full fast recovery area
9.3.2 Configuring Users to Perform Backup and Recovery
This section describes the credentials required to perform backup and recovery and how to grant the SYSBACKUP
privilege to database users.
9.3.2.1 Credentials Required to Perform Backup and Recovery
SYSDBA
or SYSBACKUP
administrative privilege. The SYSBACKUP
privilege encompasses all the privileges required to back up and recover the database. Those privileges are a subset of the privileges included in the SYSDBA
administrative privilege.
The following types of users have the SYSBACKUP
privilege.
-
The
SYSBACKUP
user.When you install the database, the
SYSBACKUP
user, with theSYSBACKUP
privilege, is created automatically. -
Database users to whom you grant the
SYSBACKUP
privilege. -
Database host users who are members of the OSBACKUPDBA operating system group—for operating system authentication.
The OSBACKUPDBA group is assigned to a specific operating system group during database installation. For example, on UNIX and Linux systems, the
backupdba
group is typically designated as the OSBACKUPDBA group. Host users in this group can connect to the target database using operating system authentication; they do not need to be defined as a database user.
For the Oracle suggested backup strategy described in this documentation, you use operation system authentication. Consult your operating system documentation for instructions for creating host users and adding them to the OSBACKUPDBA group.
Note:
In previous releases, you needed the SYSDBA
administrative privilege to perform backup and recovery tasks. Starting with Oracle Database 12c, it is recommended that you use the SYSBACKUP
administrative privilege for backup and recovery operations.
Oracle recommends that you do not use the SYSBACKUP
user. Instead, create a user and grant the SYSBACKUP
privilege to that user.
See Also:
-
"Starting SQL*Plus and Connecting to the Database" for more information about operating system authentication
9.3.3 Connecting to the Target Database Using RMAN
SYSDBA
or SYSBACKUP
administrative privilege.
To connect to the target database:
Note:
You can start RMAN on one database host and connect to a target database on another host. This is known as connecting to a remote database, and it requires that:
-
You supply a net service name in the connect string.
-
Your user name be entered in the password file of the remote database to connect as
SYSBACKUP
.
Connecting to a remote database is outside the scope of this documentation. For more information, see Oracle Database Backup and Recovery User’s Guide
See Also:
-
Oracle Database Backup and Recovery User’s Guide for information about connecting to multitenant container databases (CDBs) and pluggable databases (PDBs)
-
Oracle Database Backup and Recovery User’s Guide for more information about connecting to the target database
-
"Client Connections" for information about net service names.
9.3.4 Configuring Recovery Settings
This section explains how to configure settings used for instance recovery, media recovery, and fast recovery. It includes the following topics:
9.3.4.1 Configuring the Fast Recovery Area
If you did not specify a location for the fast recovery area during installation, the installation process automatically configures a fast recovery area in the Oracle base directory. Oracle recommends, however, that the fast recovery area be located on a separate storage device from the database files.
You can modify the following initialization parameters to relocate the fast recovery area and to adjust its size:
-
DB_RECOVERY_FILE_DEST
Specifies the location of the fast recovery area. This can be a file system directory or an Oracle Automatic Storage Management (Oracle ASM) disk group, but not a raw disk.
-
DB_RECOVERY_FILE_DEST_SIZE
Specifies the size of the fast recovery area, in bytes.
The DB_RECOVERY_FILE_DEST_SIZE
parameter must be set before the DB_RECOVERY_FILE_DEST
parameter.
You can set these parameters without having to shut down and restart the database. In an Oracle Real Application Clusters (Oracle RAC) database, all instances must have the same values for these initialization parameters. The location must be on a cluster file system, Oracle ASM, or a shared directory.
To configure the fast recovery area:
Assume that you want to want to place the fast recovery area in the directory /u02/oracle/fra
and you want its size to have an upper limit of 10 GB.
See Also:
9.3.4.2 Enabling Archiving of Redo Log Files
To back up the database while it is open, or to be able to perform complete or point-in-time media recovery, you must enable the archiving of redo log files. To do so, you place the database in ARCHIVELOG
mode. You can determine if archiving of redo logs is enabled for the target database using the following query:
SELECT LOG_MODE FROM V$DATABASE;
If you do not specify a destination to which the database should write archived log files, the database writes them to the fast recovery area. You can specify a different destination, or you can specify that multiple copies of each archived log file be written, each to a different destination. Redundant copies help ensure that archived log files are always available in the event of a failure at one of the destinations.
The following procedure assumes that you want to place archived log files in the directory /u02/oracle/logfiles
, and redundant copies of archived log file in the directory /u03/oracle/logfiles
. The redundant copies are optional.
WARNING:
You must ensure that there is sufficient disk space at all times for archived log file destinations. If the database encounters a disk full error as it attempts to archive a log file, a fatal error occurs and the database stops responding. You can check the alert log for a disk full message.
To enable archiving of redo log files:
Note:
You cannot use backups from before the switch to ARCHIVELOG
mode to restore and recover the database to a point in time after the switch. Thus, if you do not immediately make a backup after switching, then you are running your database without a valid backup. See "Performing and Scheduling Backups Using RMAN" to learn how to make database backups.
See Also:
-
Oracle Database Administrator’s Guide for more information about setting initialization parameters for archived log file destinations
-
"Monitoring General Database State and Workload" for information about the alert log
9.3.4.3 Enabling Flashback Database
To revert the entire database to a prior point in time, you can either revert the entire database to a prior point in time by restoring a backup and doing point-in-time recovery, or you can enable Flashback Database. When you enable Flashback Database, the database generates flashback logs in the fast recovery area. These logs are used to flash back the database to a specified time. During usual operation, the database occasionally logs images of data blocks to the flashback logs. The database automatically creates, deletes, and resizes flashback logs.
Use the following command to check if Flashback Database is enabled for your target database:
SELECT FLASHBACK_ON FROM V$DATABASE;
To enable Flashback Database:
You can also execute the commands in this section by connecting to the target database using SQL*Plus instead of RMAN.
See Also:
-
Oracle Database Backup and Recovery User’s Guide for more details about configuring a fast recovery area
9.3.5 Configuring Backup Settings
This section contains the following topics:
9.3.5.1 Configuring Backup Device Settings
For disk-based backups, you can configure the default format for backups, the location on disk where backups are stored, whether backup tasks run in parallel, and whether backups are compressed.
For tape backups, you can configure settings such as the number of tape drives and whether backups are compressed. On most platforms, you must integrate a media manager with the Oracle database to use sequential media for storage.
You can use Oracle Secure Backup, which supports both database and file system backups to tape, as your media manager. Oracle Secure Backup provides the same services for Oracle Recovery Manager (RMAN) as other third-party SBT interfaces. This section assumes that you make only disk backups.
Use the RMAN CONFIGURE
command to configure backup device settings such as the default backup type, disk location to which database files are backed up, and parallelism. Use the RMAN SHOW ALL
command to view the currently configured settings.
To configure backup device settings:
See Also:
-
Oracle Database Backup and Recovery User’s Guide for more information about image copies and backup sets
-
Oracle Database Backup and Recovery User’s Guide for information about setting tape as the default device
9.3.5.2 Configuring Backup Policy Settings
You can set the backup policies that govern control file and server parameter file backups, tablespaces to exclude from an entire database backup, and the backup retention policy.
To configure the backup policy settings:
See Also:
Oracle Database Backup and Recovery User’s Guide for information about other methods of configuring the retention policy
9.3.5.3 Configuring Automatic Backups for the Control File and Server Parameter File
If the database runs in ARCHIVELOG
mode, then an autobackup is also taken whenever the database structure metadata in the control file changes.
Note:
For multitenant container databases (CDBs), the control file and server parameter file autobackups are performed by default.
To configure automatic backups for the control file and server parameter file:
RMAN uses a default format to assign names for these backups.
See Also:
Oracle Database Backup and Recovery User’s Guide for more information about configuring the format for backups
9.3.5.4 Enabling Block Change Tracking
You can enable block change tracking when the database is either open or mounted. This section assumes that you intend to create the block change tracking file as an Oracle Managed File in the database area, which is where the database maintains active database files such as data files, control files, and online redo log files.
To determine if block change tracking is enabled, check the STATUS
and FILENAME
columns in the V$BLOCK_CHANGE_TRACKING
view, using the following statement from the SQL or RMAN prompt:
SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING;
To enable block change tracking:
See Also:
-
Oracle Database Reference for more information about the
V$BLOCK_CHANGE_TRACKING
view. -
Oracle Database Reference for more information about the
DB_CREATE_FILE_DEST
initialization parameter
9.4 Backing Up Your Database
This section contains the following topics:
See Also:
9.4.1 Additional Backup Concepts
This section includes topics about incrementally updated backups and backup tags.
9.4.1.1 Incrementally Updated Backups: Rolling Forward Image Copies of Data Files
Incorporating incrementally updated backups into your backup strategy shortens expected recovery times. Media recovery to the present time or to a point in time in the recent past can begin at the time of the last level 1 backup applied, rather than at the time of the last full database backup.
See Also:
9.4.1.2 Backup Tags
FULL_SAT
to identify this backup.
You can use tags to refer to specific backups in RMAN commands. For example, you could issue a command to move the latest FULL_SAT
backup to tape. If you do not specify a tag, then RMAN creates a unique tag automatically.
Because you can use tags to refer to different groups of backups, you can create different routines in your backup strategy that do not interfere with each other. When you schedule a backup job and give the job a name, the job name is the tag.
9.4.2 Performing and Scheduling Backups Using RMAN
Oracle Recovery Manager (RMAN) enables you to perform the different types of backups that are required by your backup strategy. This section discusses creating a whole database backup.
You can also individually back up data files, control files, and archived redo log files. You can use some advanced RMAN features such as encrypting backups. For more information about these topics, see Oracle Database Backup and Recovery User’s Guide.
See Also:
Oracle Database Backup and Recovery User’s Guide for information about backing up multitenant conainer databases (CDBs) and pluggable databases (PDBs)
This section contains the following topics:
9.4.2.1 Performing a Whole Database Backup
Whole backups of a database include the complete contents of all data files of the database, plus the control file, archived redo log files, and server parameter file. With these files, you can perform a complete recovery.
While whole database backups can be an important element in your overall backup strategy, they are also a required step in some situations, such as when you enable or disable ARCHIVELOG
mode (see "Configuring Recovery Settings"). This section explains how to make whole database backups, both offline and online, to disk. Typically, you want to perform online backups to maximize database availability.
To perform a whole database backup when the database is open:
-
Connect RMAN to the target database as described in "Connecting to the Target Database Using RMAN."
-
Ensure that your database is in
ARCHIVELOG
mode as described in "Enabling Archiving of Redo Log Files."You can make online backups only if your database is running in
ARCHIVELOG
mode. -
Back up the database, along with archived redo logs by using the following command:
BACKUP DATABASE PLUS ARCHIVELOG;
This backup is created on the default device that you configured for storing backups. If you did not configure a default device, then the backup is created in the fast recovery area. RMAN uses a default format while naming the backup sets that comprise the backup.
To perform a whole database backup when the database is closed:
See Also:
-
Oracle Database Backup and Recovery User’s Guide for information about performing backups of multitenant container databases (CDBs) and pluggable databases (PDBs)
9.4.2.2 Using the Oracle Suggested Backup Strategy
The Oracle suggested backup strategy is a scheduled disk backup strategy that protects your data and provides efficient recoverability to any point in the user-specified recovery window (time period). It leverages the incrementally updated backup features to provide faster backups than whole database backups, and faster recoverability than is possible by applying multiple incremental backups to the last full backup.
After reading the introductory "About the Oracle Suggested Backup Strategy" topic, complete the following tasks described in this section to schedule a daily backup that implements the Oracle suggested backup strategy:
9.4.2.2.1 About the Oracle Suggested Backup Strategy
The Oracle suggested backup strategy is based on incrementally updated backups. This strategy starts with an image copy of each data file and then rolls forward the image copies each day by applying an incremental level 1 backup.
For each data file, the strategy calls for backups as follows:
-
At the beginning of day 1 of the strategy (the time the first scheduled job actually runs), Oracle Recovery Manager (RMAN) creates an incremental level 0 image copy. It contains the data file contents at the beginning of day 1.
If a recovery is required, then the archived redo log files from day 1 can be used to recover to any point during day 1.
-
At the beginning of day 2, RMAN creates a differential incremental level 1 backup that contains the blocks changed during day 1.
If a recovery is required, then RMAN can apply this incremental level 1 to roll forward the level 0 backup to the beginning of day 2. RMAN can use archived redo log files to recover to any point during day 2.
-
At the beginning of each day n for day 3 and onward, RMAN applies the level 1 backup from the beginning of day n-1 to the level 0 backup. This action brings the data file copy to its state at the beginning of day n-1. Then, RMAN creates a new level 1 backup that contains the blocks changed during day n-1.
If a recovery is required, then RMAN can apply this incremental level 1 backup to the data file rolled forward on day n-1 to the beginning of day n. RMAN can use archived redo log files to recover the database to any point during day n.
In this Oracle suggested backup strategy, the data file image copies and the level 1 incremental backups share the same tag. You can safely implement other backup strategies without interfering with the Oracle suggested backup strategy.
Oracle suggested backup strategies also use tape backups in addition to disk backups, but these are beyond the scope of this section.
9.4.2.2.2 Task 1 - Preparing to Use the Oracle Suggested Backup Strategy
To use the Oracle suggested backup strategy, ensure that:
-
The database is in
ARCHIVELOG
mode. -
The fast recovery area size is configured, or a default device for storing backups is configured.
-
You have added a database host user to the OSBACKUPDBA operating system group, for operating system authentication.
9.4.2.2.3 Task 2 - Creating the Backup Script—UNIX and Linux
This backup script implements the Oracle suggested backup strategy, enabling quick recovery to any time in the preceding 24 hours. This script can be used to back up a non-CDB or a whole multitenant container database (CDB).
To create the backup script for UNIX and Linux:
-
Start a text editor and create and save a file with the following contents. Save the file in a directory that is accessible to the Oracle Database software and on which the Oracle software owner has the read permission.
Note:
In the following script, substitute the correct values for your installation for the
ORACLE_HOME
andORACLE_SID
environment variables.#!/bin/sh export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 export ORACLE_SID=orcl PATH=$ORACLE_HOME/bin:$PATH rman <<EOF connect target / RUN { ALLOCATE CHANNEL disk_iub DEVICE TYPE DISK; RECOVER COPY OF DATABASE WITH TAG daily_iub; BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG daily_iub DATABASE; } exit EOF
9.4.2.2.4 Task 3 - Testing the Backup Script
It is recommended that you run the script manually, to check for errors, before scheduling it. Your manual run of the script will start day one of the strategy, creating an incremental level 0 image copy of all datafiles.
To test the backup script:
The script starts Oracle Recovery Manager (RMAN), which starts the backup. The output from RMAN includes warning messages similar to the following:
... no copy of datafile 1 found to recover no copy of datafile 2 found to recover ... no parent backup or copy of datafile 1 found no parent backup or copy of datafile 2 found ...
These messages are normal for the first run of the script.
Note:
For the second run of the script, the output includes only these warning messages:
no copy of datafile 1 found to recover no copy of datafile 2 found to recover ...
Again, these messages are normal. For the third and subsequent script runs, no further warning messages are output.
9.4.2.2.5 Task 4 - Scheduling the Daily Backup—UNIX and Linux
The following procedure uses the cron
utility to schedule daily database backups at 2:00 a.m.
To schedule the Oracle-suggested disk backup strategy:
See Also:
Your operating system documentation for a description of the crontab command and crontab files
9.4.2.3 About the Oracle Suggested Backup Strategy and Retention
RECOVER COPY OF DATABASE WITH TAG 'ORA_OEM_LEVEL_0' UNTIL TIME "SYSDATE-4";
The configured retention is not honored for either the retention or obsolete settings. So when using the Oracle suggested backup strategy, Oracle recommends that the default setting remains unchanged to avoid confusion:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
9.4.2.4 Scheduling Miscellaneous Backup Tasks
cron
utility.
See Also:
Oracle Database Backup and Recovery User’s Guide for information about performing customized backups
9.4.3 Displaying Backups Stored in the RMAN Repository
LIST
command to view information about backups stored in the Oracle Recovery Manager (RMAN) repository. The information includes backups of data files, individual tablespaces, archived redo log files, and control files. You can also use this command to display information about expired and obsolete backups.
See Also:
Oracle Database Backup and Recovery User’s Guide for more information about the LIST
command
The syntax used to display backups of multitenant container databases (CDBs) and pluggable databases (PDBs), which has minor variations from that used for non-CDBs, is described in Oracle Database Backup and Recovery User’s Guide.
To display all backups:
-
Connect RMAN to the target database as described in "Connecting to the Target Database Using RMAN."
-
Use the
LIST
command to display a summary of all the backups, both backup sets and image copies.LIST BACKUP SUMMARY; List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ------- -- -- - ----------- --------------- ------- ------- ---------- --- 12 B F A DISK 28-MAR-12 1 1 NO TAG20120328T051810 13 B F A DISK 28-MAR-12 1 1 NO TAG20120328T051811 14 B F A DISK 28-MAR-12 1 1 NO TAG20120328T051921 15 B F A DISK 28-MAR-12 1 1 NO TAG20120328T051936 16 B F A DISK 28-MAR-12 1 1 NO TAG20120328T052241
To display selected backups:
9.4.4 Validating Backups and Testing Your Backup Strategy
You can validate your backups in the following ways:
-
Select specific backup sets or image copies in Oracle Recovery Manager (RMAN) and validate them. This technique indicates if a backup exists and can be restored. For this form of validation, use the steps described in "Validating Selected Backups."
-
Specify database files and let RMAN select backups to use when restoring those files, as it would for an actual restore operation. This technique ensures that your available backups are sufficient to restore the database. For this form of validation, use the steps described in "Validating Backups for Restore Operations."
Note:
Validating backups stored on tape can be time-consuming because the entire backup is read from tape.
You can perform both forms of validation using RMAN. You should incorporate both forms of validation into your backup strategy to ensure that your recoverability goals are met by your available backups.
See Also:
Oracle Database Backup and Recovery User’s Guide for information about validating backups of multitenant container databases (CDBs) and pluggable databases (PDBs)
9.4.4.1 Validating Selected Backups
To validate selected backups:
When you suspect that one or more backup pieces in a backup set are missing or have been damaged, use the VALIDATE BACKUPSET
command to validate the backup set.
See Also:
Oracle Database Backup and Recovery User’s Guide for more information about validating backups
9.4.4.2 Validating Backups for Restore Operations
Validating the restoration of files tests whether the file can be restored given the available backups, but it does not test whether all backups of the specified object are valid.
To verify whether specified database files can be restored:
See Also:
-
Oracle Database Concepts for datafile recovery concepts
-
Oracle Database Backup and Recovery User’s Guide to learn how to use the
RESTORE
...
VALIDATE
command
9.5 Displaying Backup Reports
V$RMAN_BACKUP_JOB_DETAILS
contains information about backup jobs run by RMAN. This view contains information such as the time taken for the backup, when a job started and finished, and what type of backup was performed, and the status of the backup job.
To display backup reports:
Use the following query to display the backup job history.
SELECT SESSION_KEY,INPUT_TYPE,STATUS,START_TIME,END_TIME,ELAPSED_SECONDS/3600 hrs FROM V$RMAN_BACKUP_JOB_DETAILS; SESSION_KEY INPUT_TYPE STATUS START_TIM END_TIME HRS ----------- ------------- ----------------------- --------- --------- ---------- 8 DB FULL FAILED 27-MAR-12 27-MAR-12 1.64666666 50 DB FULL COMPLETED 28-MAR-12 28-MAR-12 .243055555 69 DB FULL COMPLETED 30-MAR-12 05-APR-12 147.176388
SESSION_KEY
is the unique key for the RMAN session in which the backup job occurred.
9.6 Managing Backups
In a multitenant environment, you can manage backups for the whole multitenant container database (CDB) or for one or more pluggable databases (PDBs). The steps in this section are applicable to CDBs and PDBs, with minor modifications. To manage backups for the whole CDB, connect to the root and use the steps described in this section. To manage backups of a single PDB, connect to that PDB and use the steps described in this section. To manage backups of multiple PDBs using one command, connect to the root and use the PLUGGABLE DATABASE
clause followed by a list of PDBs.
This section contains the following topics:
9.6.1 About Backup Management
An essential part of a backup and recovery strategy is managing backups after you create them. Backup management includes deleting obsolete backups and performing periodic checks to ensure that backups are available and usable.
A backup recorded in the Oracle Recovery Manager (RMAN) repository has one of the following status values:
-
Available, meaning that the backup is still present on disk or tape, as recorded in the repository
-
Expired, meaning that the backup no longer exists on disk or tape, but is still listed in the repository
-
Unavailable, meaning that the backup is temporarily not available for data recovery operations (because, for example, it is stored on a tape that is stored offsite or on a disk that is currently not mounted)
Backups can also be obsolete. An obsolete backup is, based on the currently configured retention policy, no longer needed to satisfy data recovery goals.
Maintenance tasks that you can perform in RMAN include the following:
-
Viewing details about your backups
-
Cross-checking your repository, which means checking whether backups listed in the repository exist and are accessible, and marking as expired any backups not accessible at the time of the cross-check
-
Deleting the record of expired backups from your RMAN repository
-
Deleting obsolete backups from the repository and from the backup media
-
Validating backups to ensure that a given backup is available and not corrupted
Note:
If a backup no longer exists, then immediately delete the backup record from the RMAN repository. Without an accurate record of available backups, you may discover that you no longer have complete backups of your database when you must perform a recovery.
Some tasks, such as periodic cross-checks of your backups, should be among the regularly scheduled components of your backup strategy.
If you use a fast recovery area for backup storage, then many maintenance activities are reduced or eliminated. The automatic storage space management mechanisms delete backups and other files as needed, thereby satisfying storage space demands for ongoing database operations without compromising the retention policy. However, you must monitor space usage in the fast recovery area to ensure that it is large enough to contain backups and other recovery-related files.
9.6.2 Cross-Checking Backups
Backups to disk are listed as available if they are still on disk in the location listed in the RMAN repository, and if they have no corruption in the file header. Backups on tape are listed as available if they are still on tape. The file headers on tape are not checked for corruption. Backups that are missing or corrupt are listed as expired.
To cross-check individual files:
-
Connect RMAN to the target database as described in "Connecting to the Target Database Using RMAN."
-
Display a summary of the existing backups to determine which backup you want to cross-check.
LIST BACKUP SUMMARY;
-
Identify the backup that you want to cross-check from the output of the previous
LIST
command. -
Cross-check the identified file using the
CROSSCHECK
command.-
To cross-check the backup set 1345:
CROSSCHECK BACKUPSET 1345; using channel ORA_DISK_1 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/ade/b/191802369/oracle/work/orcva/RDBMS/backupset/2012_04_05/o1_mf_annnn_TAG20120405T075520_7qvdlrsl_.bkp RECID=1345 STAMP=779788520 Crosschecked 1 objects
-
To cross-check the data files 1 and 5:
CROSSCHECK DATAFILECOPY 1,5;
-
To cross-check all files:
Note:
Cross-checking all backups in the RMAN repository may take a long time, especially for tape backups. A cross-check of all files, unlike cross-checking individual files, is handled as a scheduled job.
See Also:
Oracle Database Backup and Recovery Reference for the syntax used to cross-check backups of multitenant container databases (CDBs) and pluggable databases (PDBs)
9.6.3 Deleting Expired Backups
EXPIRED
. Expired backups are those found to be inaccessible during a cross-check. No attempt is made to delete the files containing the backup from disk or tape; this action updates only the RMAN repository.
See Also:
Oracle Database Backup and Recovery Reference for the syntax used to delete expired backups of multitenant container databases (CDBs) and pluggable databases (PDBs)
To delete expired backups:
9.6.4 Marking Backups as Available or Unavailable
Note:
Backups stored in the fast recovery area cannot be marked as unavailable.
RMAN keeps the record of unavailable backups in the RMAN repository and does not delete backups listed as unavailable when you delete expired backups. If the unavailable backups become accessible again, then you can mark them as available.
See Also:
Oracle Database Backup and Recovery Reference for the syntax used for multitenant container databases (CDBs) and pluggable databases (PDBs)
To mark backups as available or unavailable:
9.6.5 Deleting Obsolete Backups
See Also:
Oracle Database Backup and Recovery Reference for the syntax used to delete obsolete backups of CDBs and PDBs
To delete obsolete backups:
9.6.6 Monitoring Fast Recovery Area Space Usage
V$RECOVERY_FILE_DEST
and V$RECOVERY_AREA_USAGE
.
Use the V$RECOVERY_FILE_DEST
view to obtain the following information about the fast recovery area: total number of files, current location, disk quota, space in use, and space reclaimable by deleting files. The space details are in bytes. Querying V$RECOVERY_FILE_DEST
produces the following output.
SELECT * FROM V$RECOVERY_FILE_DEST; NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES -------------- ----------- ---------- ----------------- --------------- /mydisk/rcva 5368709120 109240320 256000 28
The V$RECOVERY_AREA_USAGE
view contains the percentage of disk quota used by different type of files, and the percentage of space that can be reclaimed by deleting files that are obsolete, redundant, or backed up to tape. Querying the V$RECOVER_AREA_USAGE
view produces the following output.
SELECT * FROM V$RECOVERY_AREA_USAGE; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES ------------ ------------------ ------------------------- --------------- CONTROLFILE 0 0 0 ONLINELOG 2 0 22 ARCHIVELOG 4.05 2.01 31 BACKUPPIECE 3.94 3.86 8 IMAGECOPY 15.64 10.43 66 FLASHBACKLOG .08 0 1
9.7 Performing Oracle Advised Recovery
Note:
Data Recovery Advisor can only be used to diagnose and repair failures in multitenant container databases (CDBs). It is not supported for pluggable databases (PDBs).
RMAN provides a command-line interface to the Data Recovery Advisor. You can use following RMAN commands to diagnose and repair data failures for the Oracle Database, including for Oracle RAC databases:
-
LIST FAILURE
Use this command to view problem statements for failures and the effect of these failures on database operations. Each failure is identified by a failure number.
-
ADVISE FAILURE
Use this command to view repair options, including both automated and manual repair options.
-
REPAIR FAILURE
Use this command to automatically repair failures listed by the most recent
ADVISE FAILURE
command.
See Also:
Oracle Database Backup and Recovery User’s Guide for information about altering the status or priority of a failure by using the CHANGE FAILURE
command
This section contains the following topics:
9.7.1 About Data Recovery Advisor
In the context of Data Recovery Advisor, a health check is a diagnostic procedure run by the Health Monitor to assess the state of the database or its components. Health checks are invoked reactively when an error occurs. You can also invoke checks manually.
A failure is a persistent data corruption detected by a health check. Failures are usually detected reactively. A database operation involving corrupted data results in an error, which automatically invokes a health check in the database. The check searches the database for failures related to the error. If failures are diagnosed, then they are recorded in the Automatic Diagnostic Repository (ADR).
You can use Data Recovery Advisor to generate repair advice and repair failures only after failures have been detected by the database and stored in ADR. Data Recovery Advisor can report on and repair failures such as inaccessible files, physical and logical block corruptions, and I/O failures. Every failure has a failure priority: CRITICAL, HIGH, or LOW. Every failure also has a failure status of OPEN or CLOSED.
You can also use Data Recovery Advisor to view repair options. A repair is an action that fixes one or more failures. Examples of repairs include block media recovery, data file media recovery, and Oracle Flashback Database. Typically, Data Recovery Advisor presents both automated and manual repair options. If appropriate, you can choose an automated repair option in order to perform a repair. In this case, Data Recovery Advisor verifies the repair success, and closes the relevant repaired failures.
9.7.2 Using Data Recovery Advisor
This section describes a scenario in which you use Data Recovery Advisor to repair a corrupted block.
To use the Oracle advised recovery strategy to automatically repair failures:
9.8 Performing User-Directed Recovery
-
Repair unwanted changes to database objects with the logical flashback features
-
Rewind the entire database with Oracle Flashback Database
-
Completely restore and recover the database
-
Perform point-in-time recovery of the database or selected tablespaces
-
Perform block media recovery of data files that have corrupted blocks
You can determine which parts of the database must be restored and recovered, including detecting situations such as corrupted database files before they affect database operations.
This section contains a few typical recovery examples so that you can become familiar with the performing whole database or object-level recovery using Oracle Recovery Manager (RMAN). Use the RESTORE
and RECOVER
commands to perform whole database or object-level recovery.
This section contains the following topics:
9.8.1 Rewinding a Table Using Oracle Flashback Table
For this example, you use Flashback Table on the employees
table in the hr
schema. Assume that an erroneous update shortly after October 23, 2005 at 15:30:00 has changed the lastname
column for all employees to an empty string, and you must return the original lastname
values to the table.
This section contains the following topics:
9.8.1.1 Enabling Row Movement on a Table
To enable row movement on a table:
9.8.2 Recovering a Dropped Table Using Oracle Flashback Drop
As with Flashback Table, you can use Flashback Drop while the database is open. Also, you can perform the flashback without undoing changes in objects not affected by the Flashback Drop operation. Flashback Table is more convenient than forms of media recovery that require taking the database offline and restoring files from backup.
Note:
For a table to be recoverable using Flashback Drop, it must reside in a locally managed tablespace. Also, you cannot recover tables in the SYSTEM
tablespaces with Flashback Drop, regardless of the tablespace type.
This section contains the following topics:
9.8.2.1 Dropping a Table
reg_hist
and then drop it. The database places the table in the recycle bin so that it can be retrieved with the Flashback Drop feature.
To create and then drop a table:
9.8.2.2 Retrieving a Dropped Table
reg_hist
from the recycle bin. This section assumes that you created and then dropped the reg_hist
table, as described in "Dropping a Table."To perform the Flashback Drop operation:
9.8.3 Rewinding a Database Using Oracle Flashback Database
Flashback Database uses flashback logs to access previous versions of data blocks and also uses some data in the archived redo log files. To have the option of using Flashback Database to repair your database, you must have configured the database to generate flashback logs as explained in "Configuring Recovery Settings."
Note:
You can use the Oracle Recovery Manager (RMAN) FLASHBACK DATABASE
command to rewind the entire multitenant container database (CDB) only, not individual pluggable databases (PDBs).
To perform a Flashback Database operation:
9.8.4 Restoring and Recovering the Database
To restore and recover the entire database:
See Also:
-
Oracle Database Backup and Recovery User’s Guide for information about recovering multitenant container databases (CDBs) and pluggable databases (PDBs)
-
Oracle Database Backup and Recovery User’s Guide for instructions for recovering a database when the control file and server parameter file are available only on backup
-
Oracle Database Backup and Recovery User’s Guide for information about restoring data files to a location that is different from the default location
9.9 Performing Backup and Recovery: Oracle By Example Series
Oracle By Example (OBE) has a series on the Oracle Database 2 Day DBA guide. This OBE steps you through the tasks in this section, and includes annotated screenshots.
To view the Performing Backup and Recovery OBE, enter the following URL in your web browser:
https://apexapps.oracle.com/pls/apex/f?p=44785:24:::NO:24:P24_CONTENT_ID:16832