Pro Oracle Database 11g Administration (86 page)

Read Pro Oracle Database 11g Administration Online

Authors: Darl Kuhn

Tags: #Oracle DBA

BOOK: Pro Oracle Database 11g Administration
13Mb size Format: txt, pdf, ePub

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:


Your database is in archivelog mode.


You have a good baseline backup of your database.


You have any required redo that has been generated since the last backup.


All archive redo logs start from the point that the last online backup began.


If using, any incremental backups that RMAN can use for recovery must be available


Online redo logs that contain transactions that have not yet been archived must be 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 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

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

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

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

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

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

Key Thrd Seq S Low Time

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

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';

Other books

Surrender, Dorothy by Meg Wolitzer
The New World: A Novel by Chris Adrian, Eli Horowitz
Release by Kery, Beth
Heart Shaped Rock by Roppe, Laura
The Saint Bids Diamonds by Leslie Charteris
The Art of Happiness by The Dalai Lama
The Good People by Hannah Kent
A Wedding Wager by Jane Feather