Pro Oracle Database 11g Administration (86 page)
Starting Up
As with SQL*Plus, you can use a combination of STARTUP and ALTER DATABASE commands to step the database through startup phases using RMAN, like so:
RMAN> startup nomount;
RMAN> alter database mount;
RMAN> alter database open;
Here's another example:
RMAN> startup mount;
RMAN> alter database open;
515
CHAPTER 19 ■ RMAN RESTORE AND RECOVERY
If you want to start the database with restricted access, use the DBA option: RMAN> startup dba;
Complete Recovery
Complete recovery means that you can restore all transactions that were committed before the failure occurred. Complete recovery does not mean that you are restoring and recovering all datafiles in your database. For example, you can perform a complete recovery if you have a media failure with one datafile, and you restore and recover the one datafile. For complete recovery, the following conditions must be true:
•
•
•
•
•
•
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 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 datafiles. The RESTORE...PREVIEW does not actually restore any files; rather, it lists out 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:
516
CHAPTER 19 ■ RMAN RESTORE AND RECOVERY
List of Backups
===============
------- -- -- - ----------- --------------- ------- ------- --- ---
571 B F A DISK 22-SEP-10 1 1 YES TAG20100922T141215
570 B F A DISK 22-SEP-10 1 1 YES TAG20100922T141215
List of Archived Log Copies for database with db_unique_name O11R2
=====================================================================
------- ---- ------- - ---------
878 1 1 A 22-SEP-10
Media recovery start SCN is 19993679
Recovery must be done beyond SCN 19993680 to clear datafile fuzziness 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, the use the RESTORE...VALIDATE HEADER command like so:
RMAN> restore database validate header;
This command only validates the existence of files and the file headers. You can further instruct RMAN to verify the integrity of blocks within backup files required to restore the database datafiles via the RESTORE...VALIDATE command (sans the HEADER clause). Again, RMAN will not restore any datafiles 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 datafiles being recovered are offline. Oracle will throw an error for any online datafiles being recovered in test mode.
In this example, the tablespace USERS is restored first, and then a trial recovery is performed: 517
CHAPTER 19 ■ RMAN RESTORE AND RECOVERY
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 10 and starting SCN...
If the testing of the recovery succeeded, you will messages like the following, indicating 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 19993679 to 19993861
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 Entire Database
The RESTORE DATABASE command will restore every datafile in your database. The exception to this is when RMAN detects that datafiles 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 datafiles that need recovery. The recovery process includes applying changes found in the following:
• Incremental backup pieces (applicable only if using incremental backups)
• Archived redo log files (generated since the last backup or last incremental backup that is 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 have access to all redo generated after the backup was taken. You need all the redo required to recover the database datafiles. If you don’t have all the required redo, then you’ll most likely have to perform an incomplete recovery (covered later in this chapter).
■
Note
Your database has to be at least mounted to restore datafiles using RMAN. This is because RMAN reads information from the control file during the restore and recovery process.
You can perform a complete database-level recovery with either the current control file or a backup control file.
518
CHAPTER 19 ■ RMAN RESTORE AND RECOVERY
Using Current Control File
You must first put your database in mount mode to perform a database-wide restore and recovery. This is because the SYSTEM tablespace datafile(s) must be offline when being restored and recovered. Oracle won’t allow you to operate your database in open mode with datafiles associated with the SYSTEM
tablespace offline. In this situation, start up the database in mount mode, issue the RESTORE and RECOVER
commands, and then open the database like so:
RMAN> connect target /
RMAN> startup mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open;
If everything went as expected, the last message you should see is this: database opened
Using Backup Control File
This solution uses a backup of the control file retrieved from the fast recovery area. For more examples of how to restore your control file, see the “Restoring Control Files” section of this chapter. In this scenario, the control file is first retrieved from a backup before restoring and recovering the database: RMAN> connect 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 everything went as expected, the last message you should see is this: database opened
■
Note
You are required to open your database with the OPEN RESETLOGS command anytime you use a backup control file during a recovery operation.
Restoring Tablespaces
Sometimes you'll have media failures that are localized to a particular tablespace or set of tablespaces.
In these circumstances, 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 datafiles associated with the specified tablespace(s).
519
CHAPTER 19 ■ RMAN RESTORE AND RECOVERY
Restoring Tablespace While 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 tablespaces except for SYSTEM and UNDO. This example restores and recovers the USERS
tablespace while database is open:
RMAN> connect 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 similar to this: sql statement: alter tablespace users online
Restoring Tablespace While Database in Mount Mode
Usually when performing a restore and recovery, DBAs will shut down the database re-start the database in mount mode in preparation to perform the recovery. While a database is mount mode this ensures that no users are connecting to the database and also ensures that no transactions are transpiring. This next example restores the SYSTEM tablespace while the database is in mount mode: RMAN> connect target /
RMAN> shutdown immediate;
RMAN> startup mount;
RMAN> restore tablespace system;
RMAN> recover tablespace system;
RMAN> alter database open;
If everything was successful, the last message you should see is this: database opened
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 datafiles (including those in read-only mode):
RMAN> restore database;
Prior to Oracle Database 11
g
, 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 Database 11
g
or higher.
■
Note
If you are using a backup that was created after the read-only tablespace was placed into read-only mode, then no recovery is necessary for the read-only datafiles. In this situation, there is no redo that has been generated for the read-only tablespace since it was backed up.
520
CHAPTER 19 ■ RMAN RESTORE AND RECOVERY
Restoring Temporary Tablespaces
Starting with Oracle Database 10
g
, you don’t have to restore or re-create missing locally managed temporary tablespace tempfiles. When you open your database for use, Oracle automatically detects and re-creates locally managed temporary tablespace tempfiles.
When Oracle automatically re-creates a temporary tablespace, it will log a message to your target database alert.log similar to the following:
Re-creating tempfile
If for any reason your temporary tablespace becomes unavailable, you can also re-create it yourself.
Since 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
'/ora03/oradata/BRDSTN/temp01.dbf' SIZE 5000M REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K;
If your temporary tablespace exists but the temporary datafiles are missing, you can simply add the temporary datafile(s) as shown here:
alter tablespace temp
add tempfile '/ora03/oradata/BRDSTN/temp01.dbf' SIZE 5000M REUSE;
Restoring Datafiles
A datafile-level restore and recovery works well when a media failure is isolated to a small set of datafiles.
With datafile-level recoveries, you can instruct RMAN to restore and recover either with datafile number or the datafile name. For datafiles 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, you have to first take offline any datafiles being restored and recovered.
Restoring Datafile While Database Is Open
Use the RESTORE DATAFILE and RECOVER DATAFILE commands to restore and recover at the datafile level.
When your database is open, you’re required to take offline any datafiles you’re attempting to restore and recover. This example restores and recovers datafiles 32 and 33 while the database is open: RMAN> sql 'alter database datafile 32, 33 offline';