Complete Recovery Oracle Database with RMAN tool

As discussed in This Post, the term complete recovery means that you can restore all transactions that were committed before a failure occurred. Complete recovery doesn’t mean that you are restoring and recovering all data files in your database. For instance, you are performing a complete recovery if you have a media failure with one data file, and you restore and recover the one data file. For complete recovery, the following conditions must be true:

• Your database is in archivelog mode.
• You have a good baseline backup of the data files that have experienced media failure.
• You have any required redo that has been generated since the last backup.
• All archive redo logs start from the point at which the last backup began.
• Any incremental backups that RMAN can use for recovery are available (if using).
• Online redo logs that contain transactions that have not yet been archived are available.

If you’ve experienced a media failure, and you have the required files to perform a complete recovery, then you can restore and recover your database.

Testing Restore and Recovery

You can determine which files RMAN will use for restore and recovery before you actually perform the restore and recovery. You can also instruct RMAN to verify the integrity of the backup files that will be used for the restore and recovery.

Previewing Backups Used for Recovery

Use the RESTORE...PREVIEW command to list the backups and archive redo log files that RMAN will use to restore and recover database data files. The RESTORE...PREVIEW command does not actually restore any files. Rather, it lists the backup files that will be used for a restore operation. This example previews in detail the backups required for restore and recovery for the entire database:

RMAN> restore database preview;

You can also preview require backup files at a summarized level of detail:

RMAN> restore database preview summary;

Here is a snippet of the output:

List of Backups

===============

Key     TY LV S Device Type Completion Time #Pieces #Copies Compress Tag

------- -- -- - ----------- --------------- ------- ------- -------- ---

12      B  0  A DISK        28-SEP-14       1       1       YES      TAG20140928T110657

11      B  0  A DISK        28-SEP-14       1       1       YES      TAG20140928T110657

Here are some more examples of how to preview backups required for restore and recovery:

RMAN> restore tablespace system preview;

RMAN> restore archivelog from time 'sysdate -1' preview;

RMAN> restore datafile 1, 2, 3 preview;

Validating Backup Files Before Restoring

There are several levels of verification that you can perform on backup files without actually restoring anything. If you just want RMAN to verify that the files exist and check the file headers, then use the RESTORE...VALIDATE HEADER command, as shown:

RMAN> restore database validate header;

This command only validates the existence of backup files and checks the file headers. You can further instruct RMAN to verify the integrity of blocks within backup files required to restore the database data files via the RESTORE...VALIDATE command (sans the HEADER clause). Again, RMAN will not restore any data files in this mode:

RMAN> restore database validate;

This command only checks for physical corruption within the backup files. You can also check for logical corruption (along with physical corruption), as follows:

RMAN> restore database validate check logical;

Here are some other examples of using RESTORE...VALIDATE:

RMAN> restore datafile 1,2,3 validate;

RMAN> restore archivelog all validate;

RMAN> restore controlfile validate;

RMAN> restore tablespace system validate;

Testing Media Recovery

The prior sections covered reporting and verifying the restore operations. You can also instruct RMAN to verify the recovery process via the RECOVER...TEST command. Before performing a test recovery, you need to ensure that the data files being recovered are offline. Oracle will throw an error for any online data files being recovered in test mode.

In this example the tablespace USERS is restored first, and then a trial recovery is performed:

RMAN> connect target /

RMAN> startup mount;

RMAN> restore tablespace users;

RMAN> recover tablespace users test;

If there are any missing archive redo logs that are required for recovery, the following error is thrown:

RMAN-06053: unable to perform media recovery because of missing log

RMAN-06025: no backup of archived log for thread 1 with sequence 6...

If the testing of the recovery succeeded, you’ll see messages such as the following, indicating that the application of redo was tested but not applied:

ORA-10574: Test recovery did not corrupt any data block

ORA-10573: Test recovery tested redo from change 4586939 to 4588462

ORA-10572: Test recovery canceled due to errors

ORA-10585: Test recovery can not apply redo that may modify control file

Here are some other examples of testing the recovery process:

RMAN> recover database test;

RMAN> recover tablespace users, tools test;

RMAN> recover datafile 1,2,3 test;

Restoring and Recovering the Entire Database

The RESTORE DATABASE command will restore every data file in your database. The exception to this is when RMAN detects that data files have already been restored; in that case, it will not restore them again. If you want to override that behavior, use the FORCE command.

When you issue the RECOVER DATABASE command, RMAN will automatically apply redo to any data files that need recovery. The recovery process includes applying changes found in the following files:

• Incremental backup pieces (applicable only if using incremental backups)
• Archived redo log files (generated since the last backup or incremental backup applied)
• Online redo log files (current and unarchived)

You can open your database after the restore-and-recovery process is complete. Complete database recovery works only if you have good backups of your database and access to all redo generated after the backup was taken. You need all the redo required to recover the database data files. If you don’t have all the required redo, then you’ll most likely have to perform an incomplete recovery (see the section “Incomplete Recovery,” later in this chapter).

You can perform a complete database-level recovery with either the current control file or a backup control file.

Using the Current Control File

You must first put your database in mount mode to perform a database-wide restore and recovery. This is because Oracle won’t allow you to operate your database in open mode while data files associated with the SYSTEM tablespace are being restored and recovered. In this situation, start up the database in mount mode, issue the RESTORE and RECOVER commands, and then open the database, like so:

$ rman target /

RMAN> startup mount;

RMAN> restore database;

RMAN> recover database;

RMAN> alter database open;

If everything goes as expected, the last message you should see is this:

Statement processed

Using the Backup Control File

This technique uses an autobackup of the control file retrieved from the FRA (see the section “Restoring a Control File,” later in this chapter, for more examples of how to restore your control file). In this scenario the control file is first retrieved from a backup before restoring and recovering the database:

$ rman target /

RMAN> startup nomount;

RMAN> restore controlfile from autobackup;

RMAN> alter database mount;

RMAN> restore database;

RMAN> recover database;

RMAN> alter database open resetlogs;

If successful, the last message you should see is this:

Statement processed

Restoring and Recovering Tablespaces

Sometimes you’ll have a media failure that’s localized to a particular tablespace or set of tablespaces. In this situation, it’s appropriate to restore and recover at the tablespace level of granularity. The RMAN RESTORE TABLESPACE and RECOVER TABLESPACE commands will restore and recover all data files associated with the specified tablespace(s).

Restoring Tablespaces While the Database Is Open

If your database is open, then you must take offline the tablespace you want to restore and recover. You can do this for any tablespace except SYSTEM and UNDO. This example restores and recovers the USERS tablespace while the database is open:

$ rman target /

RMAN> sql 'alter tablespace users offline immediate';

RMAN> restore tablespace users;

RMAN> recover tablespace users;

RMAN> sql 'alter tablespace users online';

After the tablespace is brought online, you should see a message such as this:

sql statement: alter tablespace users online

Starting with Oracle 12c, you can run SQL statements directly, without the RMAN sql command and associated quotation marks; for example,

$ rman target /

RMAN> alter tablespace users offline immediate;

RMAN> restore tablespace users;

RMAN> recover tablespace users;

RMAN> alter tablespace users online;

Restoring Tablespaces While the Database Is in Mount Mode

Usually when performing a restore and recovery, DBAs will shut down the database and restart it in mount mode in preparation for performing the recovery. Placing a database in mount mode ensures that no users are connecting to the database and that no transactions are transpiring.

Also, if you’re restoring and recovering the SYSTEM tablespace, then you must start the database in mount mode. Oracle doesn’t allow for restoring and recovering the SYSTEM tablespace data files while the database is open. This next example restores the SYSTEM tablespace while the database is in mount mode:

$ rman target /

RMAN> shutdown immediate;

RMAN> startup mount;

RMAN> restore tablespace system;

RMAN> recover tablespace system;

RMAN> alter database open;

If successful, the last message you should see is this:

Statement processed

Restoring Read-Only Tablespaces

RMAN will restore read-only tablespaces along with the rest of the database when you issue a RESTORE DATDABASE command. For example, the following command will restore all data files (including those in read-only mode):

RMAN> restore database;

Prior to Oracle 11g, you were required to issue RESTORE DATABASE CHECK READONLY to instruct RMAN to restore read-only tablespaces along with tablespaces in read-write mode. This is no longer a requirement in Oracle 11g and higher.

Restoring Temporary Tablespaces

Starting with Oracle 10g, you don’t have to restore or re-create missing locally managed temporary tablespace temp files. When you open your database for use, Oracle automatically detects and re-creates locally managed temporary tablespace temp files.

When Oracle automatically re-creates a temporary tablespace, it will log a message to your target database alert.log such as this:

Re-creating tempfile <your temporary tablespace filename>

If, for any reason, your temporary tablespace becomes unavailable, you can also re-create it yourself. Because there are never any permanent objects in temporary tablespaces, you can simply re-create them as needed. Here is an example of how to create a locally managed temporary tablespace:

CREATE TEMPORARY TABLESPACE temp TEMPFILE

'/u01/dbfile/O12C/temp01.dbf' SIZE 1000M

EXTENT MANAGEMENT

LOCAL UNIFORM SIZE 512K;

If your temporary tablespace exists, but the temporary data files are missing, you can just add them, as shown:

alter tablespace temp

add tempfile '/u01/dbfile/O12C/temp02.dbf' SIZE 5000M REUSE;

Restoring and Recovering Data Files

A data file–level restore and recovery works well when a media failure is confined to a small set of data files. With data file–level recoveries, you can instruct RMAN to restore and recover either with data file name or data file number. For data files not associated with the SYSTEM or UNDO tablespaces, you have the option of restoring and recovering while the database remains open. While the database is open, however, you must first take offline any data files being restored and recovered.

Restoring and Recovering Data Files While the Database Is Open

Use the RESTORE DATAFILE and RECOVER DATAFILE commands to restore and recover at the data file level. When your database is open, you’re required to take offline any data files that you’re attempting to restore and recover. This example restores and recovers data files while the database is open:

RMAN> sql 'alter database datafile 4, 5 offline';

RMAN> restore datafile 4, 5;

RMAN> recover datafile 4, 5;

RMAN> sql 'alter database datafile 4, 5 online';

You can also specify the name of the data file that you want to restore and recover; for example,

RMAN> sql "alter database datafile ''/u01/dbfile/O12C/users01.dbf'' offline";

RMAN> restore datafile '/u01/dbfile/O12C/users01.dbf';

RMAN> recover datafile '/u01/dbfile/O12C/users01.dbf';

RMAN> sql "alter database datafile ''/u01/dbfile/O12C/users01.dbf'' online";

As mentioned earlier, starting with Oracle 12c, you can run SQL commands directly, without the RMAN sql command and associated quotation marks; for example,

RMAN> alter database datafile 4 offline;

RMAN> restore datafile 4;

RMAN> recover datafile 4;

RMAN> alter database datafile 4 online;

Here are the corresponding 12c examples, with the data file names:

RMAN> alter database datafile '/u01/dbfile/O12C/users01.dbf' offline;

RMAN> restore datafile '/u01/dbfile/O12C/users01.dbf';

RMAN> recover datafile '/u01/dbfile/O12C/users01.dbf';

RMAN> alter database datafile '/u01/dbfile/O12C/users01.dbf' online;

Restoring and Recovering Data Files While the Database Is Not Open

In this scenario the database is first shut down and then started in mount mode. You can restore and recover any data file in your database while the database is not open. This example shows the restoring of data file 1, which is associated with the SYSTEM tablespace:

$ rman target /

RMAN> shutdown abort;

RMAN> startup mount;

RMAN> restore datafile 1;

RMAN> recover datafile 1;

RMAN> alter database open;

You can also specify the file name when performing a data file recovery:

$ rman target /

RMAN> shutdown abort;

RMAN> startup mount;

RMAN> restore datafile '/u01/dbfile/O12C/system01.dbf';

RMAN> recover datafile '/u01/dbfile/O12C/system01.dbf';

RMAN> alter database open;

Restoring Data Files to Nondefault Locations

Sometimes a failure will occur that renders the disks associated with a mount point inoperable. In these situations, you will need to restore and recover the data files to a location different from the one where they originally resided. Another typical need for restoring data files to nondefault locations is that you’re restoring to a different database server, on which the mount points are completely different from those of the server on which the backup originated.

Use the SET NEWNAME and SWITCH commands to restore data files to nondefault locations. Both of these commands must be run from within an RMAN run{} block. You can think of using SET NEWNAME and SWITCH as a way to rename data files (similar to the SQL*Plus ALTER DATABASE RENAME FILE statement).

This example changes the location of data files when doing a restore and recover. First, place the database in mount mode:

$ rman target /

RMAN> startup mount;

Then, run the following block of RMAN code:

run{

set newname for datafile 4 to '/u02/dbfile/O12C/users01.dbf';

set newname for datafile 5 to '/u02/dbfile/O12C/users02.dbf';

restore datafile 4, 5;

switch datafile all; # Updates repository with new datafile location.

recover datafile 4, 5;

alter database open;

}

This is a partial listing of the output:

datafile 4 switched to datafile copy

input datafile copy RECID=79 STAMP=804533148 file name=/u02/dbfile/O12C/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=80 STAMP=804533148 file name=/u02/dbfile/O12C/users02.dbf

If the database is open, you can place the data files offline and then set their new names for restore and recovery, as follows:

run{

sql 'alter database datafile 4, 5 offline';

set newname for datafile 4 to '/u02/dbfile/O12C/users01.dbf';

set newname for datafile 5 to '/u02/dbfile/O12C/users02.dbf';

restore datafile 4, 5;

switch datafile all; # Updates repository with new datafile location.

recover datafile 4, 5;

sql 'alter database datafile 4, 5 online';

}

Starting with Oracle 12c, you no longer need to specify the RMAN sql command when running SQL statements, such as ALTER DATABASE; for example,

run{

alter database datafile 4, 5 offline;

set newname for datafile 4 to '/u02/dbfile/O12C/users01.dbf';

set newname for datafile 5 to '/u02/dbfile/O12C/users02.dbf';

restore datafile 4, 5;

switch datafile all; # Updates repository with new datafile location.

recover datafile 4, 5;

alter database datafile 4, 5 online;

}

Performing Block-Level Recovery

Block-level corruption is rare and is usually caused by some sort of I/O error. However, if you do have an isolated corrupt block within a large data file, it’s nice to have the option of performing a block-level recovery. Block-level recovery is useful when a small number of blocks are corrupt within a data file. Block recovery is not appropriate if the entire data file needs media recovery.

RMAN will automatically detect corrupt blocks whenever a BACKUP, VALIDATE, or BACKUP VALIDATE command is run. Details on corrupt blocks can be viewed in the V$DATABASE_BLOCK_CORRUPTION view. In the following example the regular backup job has reported a corrupt block in the output:

ORA-19566: exceeded limit of 0 corrupt blocks for file...

Querying the V$DATABASE_BLOCK_CORRUPTION view indicates which file contains corruption:

SQL> select * from v$database_block_corruption;



     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO     CON_ID

---------- ---------- ---------- ------------------ --------- ----------

         4         20          1                  0 ALL ZERO           0

Your database can be either mounted or open when performing block-level recovery. You do not have to take offline the data file being recovered. You can instruct RMAN to recover all blocks reported in V$DATABASE_BLOCK_CORRUPTION, as shown:

RMAN> recover corruption list;

If successful, the following message is displayed:

media recovery complete...

Another way to recover the block is to specify the data file and block number, like so:

RMAN> recover datafile 4 block 20;

It’s preferable to use the RECOVER CORRUPTION LIST syntax because it will clear out any blocks recovered from the V$DATABASE_BLOCK_CORRUPTION view.

Block-level media recovery allows you to keep your database available and also reduces the mean time to recovery, as only the corrupt blocks are offline during the recovery. Your database must be in archivelog mode for performing block-level recoveries. Starting with Oracle 11g, RMAN can restore the block from the flashback logs (if available). If the flashback logs are not available, then RMAN will attempt to restore the block from a full backup, a level-0 backup, or an image copy backup generated by the BACKUP AS COPY command. After the block has been restored, any required archived redo logs must be available to recover the block. RMAN can’t perform block media recovery using incremental level-1 (or higher) backups.

Restoring a Container Database and Its Associated Pluggable Databases

Starting with Oracle 12c, you can create pluggable databases within one container database. When dealing with container and associated pluggable databases, there are three basic scenarios:

• All data files have experienced media failure (container root data files as well as all associated pluggable database data files).
• Just the data files associated with the container root database have experienced media failure.
• Only data files associated with a pluggable database have experienced media failure.

The prior scenarios are covered in the following sections.

Restoring and Recovering All Data Files

To restore and recover all data files associated with a container database (this includes the root container, the seed container, and all associated pluggable databases), use RMAN to connect to the container database as a user with sysdba or sysbackup privileges. Because the data files associated with the root system tablespace are being restored, the database must be started in mount mode (and not open):

$ rman target /

RMAN> startup mount;

RMAN> restore database;

RMAN> recover database;

RMAN> alter database open;

Keep in mind that when you open a container database, this does not, by default, open the associated pluggable databases. You can do that from the root container, as follows:

RMAN> alter pluggable database all open;

Restoring and Recovering Root Container Data Files

If just data files associated with the root container have been damaged, then you can restore and recover at the root level. In this example the root container’s system data file is being restored, so the database must not be open. The following commands instruct RMAN to restore only the data files associated with the root container database, via the keyword root:

$ rman target /

RMAN> startup mount;

RMAN> restore database root;

RMAN> recover database root;

RMAN> alter database open;

In the prior code the restore database root command instructs RMAN to restore only data files associated with the root container database. After the container database is opened, you must open any associated pluggable databases. You can do so from the root container, as shown:

RMAN> alter pluggable database all open;

You can check the status of your pluggable databases via this query:

SQL> select name, open_mode from v$pdbs;

Restoring and Recovering a Pluggable Database

You have two options for restoring and recovering a pluggable database:

• Connect as the container root user, and specify the pluggable database to be restored and recovered.
• Connect directly to the pluggable database as a privileged pluggable-level user, and issue RESTORE and RECOVER commands.

This first example connects to the root container and restores and recovers the data files associated with the salespdb pluggable database. For this to work, the pluggable database must not be open (because the pluggable database’s system data files are also being restored and recovered):

$ rman target /

RMAN> alter pluggable database salespdb close;

RMAN> restore pluggable database salespdb;

RMAN> recover pluggable database salespdb;

RMAN> alter pluggable database salespdb open;

You can also connect directly to a pluggable database and perform restore and recovery operations. When connected directly to the pluggable database, the user only has access to the data files associated with the pluggable database:

$ rman target sys/foo@salespdb

RMAN> shutdown immediate;

RMAN> restore database;

RMAN> recover database;

RMAN> alter database open;

The prior code only affects data files associated with the pluggable database to which you are connected. The pluggable database needs to be closed for this to work. However, the root container database can be open or mounted. Also, you must use a backup that was taken while connected to the pluggable database as a privileged user. The privileged pluggable database user can’t access backups of data files initiated by the root container database privileged user.

Вас заинтересует / Intresting for you:

Oracle Database 12C Backup: un... 2786 views Андрей Волков Sat, 29 Feb 2020, 10:15:04

RMAN: Specifying the Backup Us... 2483 views Андрей Волков Sat, 29 Feb 2020, 10:14:03

Stopping the Oracle Database 1... 2183 views Андрей Волков Sat, 29 Feb 2020, 10:19:28

How to connect to Oracle Datab... 3024 views Андрей Волков Sat, 29 Feb 2020, 10:19:58