Oracle RMAN 11g Backup and Recovery (173 page)

filename

/…/flash recovery area/V102/archivelog/2006 03 19/o1 mf 1 210 21v9j3ts .arc thread 1 sequence 210

archive log

filename

//flash recovery area/V102/archivelog/2006 03 19/o1 mf 1 211 21v9j4jz .arc thread 1 sequence 211

media recovery complete, elapsed time: 00:00:03

Finished recover at 16 AUG 09

3.
Bring datafile 5 online:

sql>alter database datafile 5 online;

552
Part IV: RMAN in the Oracle Ecosystem

Case #12: Recovering from Running the Production Datafile

Out of the Flash Recovery Area

The Scenario

Tim used his FRA setup to significantly minimize the outage due to a corrupted datafile 5. However, now he is running a production datafile out of the FRA, the bad disk has been replaced, and it is time to get the datafile properly restored to its normal location.

The Problem

Using the datafile switch methodology to decrease MTTR is, of course, a very good thing. But it means that the production database is running live with a datafile that is in the flash recovery area. This can hold for a short period, but ultimately the file has to be switched back to the correct, standard location.

The Solution

Tim needs to make a new backup of datafile 5, restore it to the original file location, and then take a temporary outage while he switches back to this datafile and recovers it.

For Tim to restore the production datafile to the production environment, he has to plan a temporary outage. One of the trade-offs of a quick recovery time is this preplanned temporary outage. But, Tim can plan for it to occur deep in the night, when few users will be affected, and can absolutely minimize the amount of time the outage requires.

The Solution Revealed
Tim will use the following action plan:

1.
Take a new image copy backup of datafile 5. (Alternatively, Tim could use a previous backup of datafile 5 from tape, but Tim felt that taking a new backup of the file and restoring it from disk would actually be faster than trying to get the tape loaded and restored.)

RMAN> backup as copy datafile 5 format

'/u01/app/oracle/oradata/v102/payroll01.dbf';

Starting backup at 16 AUG 09

using channel ORA DISK 1

using channel ORA DISK 2

channel ORA DISK 1: starting datafile copy

input datafile fno 00005

name /u01/app/oracle/flash recovery area/V102/datafile/o1 mf payroll 21v9dh p .dbf

output filename /u01/app/oracle/oradata/v102/payroll01.dbf

tag TAG20060319T124728 recid 27 stamp 585492453

channel ORA DISK 1: datafile copy complete, elapsed time: 00:00:07

Finished backup at 16 AUG 09

2.
Restore datafile 5 to the original file location. In the code displayed previously, the file was backed up directly to the original location, so the restore is not required.

3.
Switch datafile 5 to the copy in the original location:

RMAN> switch datafile 5 to copy;

datafile 5 switched to datafile copy

"/u01/app/oracle/oradata/v102/payroll01.dbf"

Chapter 23: RMAN in the Workplace: Case Studies
553

4.
Recover datafile 5 and bring the file online:

RMAN> recover datafile 5;

Starting recover at 16 AUG 09

using channel ORA DISK 1

starting media recovery

archive log thread 1 sequence 221 is already on disk as file

/u01/app/oracle/flash recovery area/V102/archivelog/2006 03 19/o1 mf 1 221

1v9x6vv .arc

archive log thread 1 sequence 222 is already on disk as file

/u01/app/oracle/flash recovery area/V102/archivelog/2006 03 19/o1 mf 1 222

1v9xbvk .arc

archive log thread 1 sequence 223 is already on disk as file

/u01/app/oracle/flash recovery area/V102/archivelog/2006 03 19/o1 mf 1 223

1v9xjl5 .arc

archive log thread 1 sequence 224 is already on disk as file

/u01/app/oracle/flash recovery area/V102/archivelog/2006 03 19/o1 mf 1 224

1v9xk6m .arc

archive log

filename /u01/app/oracle/flash recovery area/V102/archivelog/2006 03 19/o1

f 1 221 21v9x6vv .arc thread 1 sequence 221

media recovery complete, elapsed time: 00:00:02

Finished recover at 16 AUG 09

…

SQL> alter database datafile 5 online;

Database altered.

SQL> select name from v$datafile where file# 5;

/u01/app/oracle/oradata/v102/payroll01.dbf

Case #13: Using Flashback Database and Media Recovery

to Pinpoint the Exact Moment to Open the Database with

resetlogs

The Scenario

Farouk did not notice the problem for all of Monday and part of Tuesday morning, because it was not brought to his attention. Finally, Tuesday morning, one of the managers for the woodscrew department called him to say that the Woodscrew database was missing records for some of the most popular woodscrew models. Farouk checked the database and, sure enough, found that someone had deleted rows from a primary table.

The Problem

Finding out who did it, and why, would have to wait. First, it was time to act. The manager said he had noticed the problem around lunchtime on Monday. Farouk checked his Flashback Query option, but found that the transaction was already older than his undo segments. He checked his Flashback Database option and found that he could still flashback nearly 48 hours.

554
Part IV: RMAN in the Oracle Ecosystem

The Solution

Farouk will use Flashback Database to do a point-in-time recovery of his entire database. There is no other option at this time. However, Farouk does not know the exact moment of failure, so he needs to be able to move back and forth in time to pinpoint the very last transaction before the failure. He will use Flashback Database and archive log recovery to scroll back and forth until he finds the correct moment to open the database. He will open the database in READ ONLY mode to check the table.

The Solution Revealed
Farouk devises the following plan:

1.
Flashback the database to the approximated first point of the failure: RMAN> run {

2> flashback database to time "to date('2006 03 19 13:30:00',

'YYYY MM DDHH24:MI:SS')";

3> }

Starting flashback at 19 MAR 06

allocated channel: ORA DISK 1

starting media recovery

archive log thread 1 sequence 225 is already on disk as file

/u01/app/oracle/flash recovery area/V102/archivelog/2006 03 19/o1 mf 1 225

1vcwlnx .arc

archive log thread 1 sequence 226 is already on disk as file

/u01/app/oracle/flash recovery area/V102/archivelog/2006 03 19/o1 mf 1 226

1vcwnlv .arc

archive log thread 1 sequence 227 is already on disk as file

/u01/app/oracle/flash recovery area/V102/archivelog/2006 03 19/o1 mf 1 227

1vcwt8h .arc

archive log thread 1 sequence 228 is already on disk as file

/u01/app/oracle/flash recovery area/V102/archivelog/2006 03 19/o1 mf 1 228

1vczqgf .arc

…

media recovery complete, elapsed time: 00:00:24

Finished flashback at 16 AUG 09

2.
Select from the affected table to see if the values are in place: sql> alter database open read only;

sql> select count(*) from ws app.woodscrew where thread cnt 30;

3.
Farouk finds the values are not there, so he flashes back again: sql> shutdown immediate;

sq> startup mount;

rman> run {

flashback database to time "to date('2009-09-16 13:20:00',

'YYYY-MM-DD HH24:MI:SS')";}

4.
He checks the values again:

sql> alter database open read only;

sql> select count(*) from ws app.woodscrew where thread cnt 30;

Chapter 23: RMAN in the Workplace: Case Studies
555

5.
The values are there, but are missing a few rows that can be gained. So Farouk can shut down the database again and recover a bit further.

sql> shutdown immediate;

sql> startup mount;

RMAN> recover database until sequence 227 thread 1;

sql> alter database open read only;

sql> select count(*) from ws app.woodscrew where thread cnt 30;

6.
The values are the best they can be, under the circumstances. Farouk opens the database in read/write mode.

sql> shutdown immediate;

sql> startup mount;

sql> alter database open resetlogs;

Summary

We hope you found these case studies helpful. We have done our best to provide you with a number of different circumstances that might come your way and solutions you can practice on a test system so that you will be ready to implement them in real life, should the occasion arise.

This is also the last chapter in this book on RMAN. We had a ball putting it together, and we hope that you find it useful. Thanks for buying it, and please let us know if you found it to be helpful.

We welcome any suggestions for the next revision!

This page intentionally left blank

PART

V

Appendixes

This page intentionally left blank

APPENDIX

A

RMAN Syntax

Reference Guide

560
Part V: Appendixes

his appendix is a quick reference to the RMAN command set. For each command, you will find a quick description of the command, an associated syntax diagram, a chapter reference, and some examples on the use of that command. This reference
T
also contains a list of the RMAN reserved words. We hope that you find this reference very useful. We expect that it will become very dog-eared from heavy use and will be the main reason you do not loan this book out to anyone!

RMAN Reserved Words

The following table provides a list of reserved words, which you should avoid using when performing RMAN operations:

,

#

(

)

/

{

}

\

<<<

>>>

;

&

_

‘

=

^

@

.

:

abort

accessible

active

adviseid

Advise

AES128

AES192

AES256

Affinity

ater

algorithm

allocate

allow

All

alter

and

append

applied

archivelog

area as

at

atall

autobackup

autoallocate

auxiliary

auxname

available

backed

backuppiece

backupset

backups

backup

before

between

blockrecover

blocks

block

By

cancel

catalog

change

channel

checksyntax

check

clear

clonename

clone

clone_cf

closed