Oracle RMAN 11g Backup and Recovery (171 page)

Chapter 23: RMAN in the Workplace: Case Studies
543

Case #6: Recovering Online from the Loss of a Datafile

or Tablespace

The Scenario

Yang was working on his database the other day when a power surge caused a media failure.

The Problem

Unfortunately for Yang, he lost one file system. This file system contained the following:

■ All the datafiles for a tablespace called WORKING_DATA

■ One datafile for a tablespace called HISTORICAL_DATA

Several other tablespaces in this database are not related to the tablespace he is recovering, so Yang needs to do this recovery with the database up and running.

The Solution

Yang will restore the WORKING_DATA tablespace and the lone datafile missing from the HISTORICAL_DATA tablespace via RMAN. He first will take offline the tablespace and datafile, so that others may continue to work.

The Solution Revealed

Based on the preceding considerations, Yang devises and implements

the following recovery plan:

1.
Take offline the WORKING_DATA tablespace:

sql "alter tablespace working data offline";

2.
Take offline the HISTORICAL_DATA datafile needed to recover (Yang has already queried the V$DATAFILE view to determine that it is datafile 13):

sql "alter database datafile 13 offline";

3.
Restore and recover the tablespace and datafile using RMAN, and then bring them online:

restore tablespace working data;

restore datafile 13;

recover tablespace working data;

recover datafile 13;

sql "alter tablespace working data online";

sql "alter database datafile 13 online";

NOTE

If either tablespace contains active rollback segments, this recovery
case may not work. In the event of the loss of active rollback segment
tablespaces, you may well be required to do an offline recovery of that
tablespace or datafile.

544
Part IV: RMAN in the Oracle Ecosystem

Case #7: Recovering from Loss of an Unarchived

Online Redo Log

The Scenario

Today is not Bill’s day. A large thunderstorm is raging outside, and Bill has forgotten that his car’s soft top is down. To make Bill’s day worse, a strike of lightning hits the datacenter and fries several disk drives that Bill’s database calls home.

The Problem

Once the hardware is repaired, Bill is horrified to find that he has lost all of his online redo logs, in addition to some of his datafiles. Fortunately, his control file is intact.

The Solution

Bill needs to restore his database by using incomplete recovery. Since the online redo logs are not available, Bill has to accept that there will be some data loss as a result of the recovery.

The Solution Revealed
Based on the preceding considerations, Bill devises and implements the following recovery plan:

1.
Determine the last archive log for which there is a copy. Because Bill has to do incomplete recovery, he must query RMAN to determine the last archive log for which a backup exists.

startup mount;

List backup of archivelog from time 'sysdate-7';

The output will look something like the following output—note the log sequence number (log sequence number 3, in bold at the bottom of the report). Since this is the oldest backed up archived redo log, this is as far as Bill can recover to.

List of Backup Sets

BS Key Size Device Type Elapsed Time Completion Time

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

216 48K DISK 00:00:03 16-AUG-09

BP Key: 247 Status: AVAILABLE Tag: TAG20020816T095848

Piece Name: D:\BACKUP\RECOVER\75E08R2P 1 1

List of Archived Logs in backup set 216

Thrd Seq Low SCN Low Time Next SCN Next Time

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

1 2 1271924 16-AUG-09 1272223 16-AUG-09

BS Key Size Device Type Elapsed Time Completion Time

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

218 2K DISK 00:00:02 16-AUG-09

BP Key: 249 Status: AVAILABLE Tag: TAG20020816T100344

Piece Name: D:\BACKUP\RECOVER\77E08RC1 1 1

Chapter 23: RMAN in the Workplace: Case Studies
545

List of Archived Logs in backup set 218

Thrd
Seq

Low SCN Low Time Next SCN Next Time

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

1

3

1272223 16-AUG-09 1272321 16-AUG-09

2.
With the last log sequence number in hand, perform the restore and recovery and open the database. Bill first restores the database using the
until sequence
parameter. This ensures that all database datafiles will be restored to a point in time no later than log sequence 3.

Also note the use of the
force
parameter, which ensures that all datafiles are restored. Recall that one of the requirements for point-in-time recovery is that all database datafiles must be restored to the same consistent point in time. Thus, it’s important to restore all datafiles to at least a point in time prior to the point in time Bill wants to recover to.

restore database until sequence 4 thread 1 force;

3.
Recover the database until sequence 4 (since the
until sequence
recovers up to but not including the listed sequence number, Bill added one number to the last sequence number, and thus gets 4):

recover database until sequence 4 thread 1;

4.
Open the database:

sql "alter database open resetlogs";

NOTE

If Bill’s database had been shut down normally (via
shutdown normal
,
immediate
, or
transactional
) before the online redo logs were lost,
he may well have been able to open the database without needing to

recover it.

Case #8: Recovering Through resetlogs

The Scenario

Bill spent all night doing his recovery and then called in Tim to monitor the restore and finish the database recovery. Once the recovery was done, Tim was supposed to back up the database. One problem is that the business requirement demanded that the database be open and available during the backup.

Tim came in and finished the recovery. Following that, he opened the database using the
resetlogs
command (as previously described in Case #7). Following the business requirements, Tim began the backup, but allowed users access to the database.

The Problem

Unfortunately, on this troubled day, a power surge hit Tim’s system, and one of the disk drives of Tim’s database was damaged. After another hardware repair, Tim finds that several datafiles were lost. To make matters worse, he has no complete backup of these datafiles since he issued the
resetlogs
command. Fortunately for Tim, this database was upgraded to Oracle Database 11
g
about three months ago. So, Tim is in luck. Since Oracle Database 10
g,
Oracle has made recovery through the
resetlogs
command much easier.

546
Part IV: RMAN in the Oracle Ecosystem

When the database was upgraded to Oracle Database 11
g,
the LOG_ARCHIVE_FORMAT

parameter was changed. The DBA added the new %R parameter so that LOG_ARCHIVE_

FORMAT now looks like this:

SQL> show parameter log archive format

NAME TYPE VALUE

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

log archive format string ARC%S %R.%T

Now the LOG_ARCHIVE_FORMAT parameter includes the %R placeholder, which means the resetlogs ID is contained in the archived redo logs’ filenames. This will save our intrepid DBAs much time during this recovery!

The Solution

Tim is going to use RMAN to recover the database through
resetlogs
. For the most part, this is a recovery that is easy to complete since Oracle Database 10
g.

The Solution Revealed
Based on the preceding considerations, Tim devises and implements the following recovery plan:

1.
Mount the database:

/u01>Rman target /

RMAN>startup mount;

2.
Tim knows that datafile 4 is missing. It is part of the USERS tablespace. He therefore restores datafile 4:

restore datafile 4;

NOTE

Tim didn’t even have to reset the incarnation of the database! This is
totally new since Oracle Database 10
g
and makes cross incarnation
recovery so much easier.

3.
Having restored the datafile, Tim now recovers it, and then opens the database: recover datafile 4;

alter database open resetlogs;

NOTE

Did it occur to you that Tim could have opened the database by just