Oracle RMAN 11g Backup and Recovery (172 page)

taking the datafile offline? He then could have restored the datafile
and brought it online after the recovery. Here is an example of the

commands he would have used to do an online recovery:

rman>startup mount

rman>sql 'alter database datafile 4 offline'

rman>alter database open;

rman>restore datafile 4;

rman>recover datafile 4;

rman>sql 'alter database datafile 4 online'

Chapter 23: RMAN in the Workplace: Case Studies
547

Case #9: Completing a Failed Duplication Manually

The Scenario

Tim decided to use RMAN duplication to create a clone of his production database on a different server. He ran the
duplicate
command, and the datafiles were successfully restored to the new server. The database is very large, and this file restore process took six hours to complete.

The Problem

Tim forgot to move the archive logs over to the auxiliary server for media recovery, so the duplication failed. This means that the cloned database is not fully recovered and does not have a new DBID.

The Solution

Tim isn’t worried, though, and he certainly isn’t going to take another six hours to perform the file restore. Tim can manually perform the media recovery and then use the DBNEWID utility on the clone database to create a new DBID and finish the duplication process without RMAN’s assistance.

The Solution Revealed
Based on the solution he decided upon, Tim will implement the following action plan to complete his failed duplication:

1.
Move the archive logs from the production to the auxiliary site: cd /space/oracle user/OraHome1/oradata/sun92

tar –cvf arch.tar arch

gzip arch.tar

2.
Use FTP to move the arch.tar file to the auxiliary system, and then enter the following: cd /space/oracle user/OraHome1/oradata/sun92

gunzip arch.tar.gz

tar –xvf arch.tar

3.
Perform manual recovery on the database. Tim needs to note the sequence number of the last archive log available on his target database and then to set the recovery to stop at that sequence number. The %s variable in the LOG_ARCHIVE_FORMAT parameter signifies the sequence number and will be in the archive log name. Tim will perform manual recovery from SQL*Plus, connecting locally to his auxiliary database (at the auxiliary site).

ORACLE SID aux1

export ORACLE SID

Sqlplus /nolog

SQL> connect / as sysdba

SQL> recover database using backup controlfile until sequence 11 thread 1;
4.
Use DBNEWID to create a new DBID for the clone database. Tim’s auxiliary database has been mounted, but it has not yet been opened. This is the right state in which to use DBNEWID. If you are unsure of the database state, you can go ahead and remount it without doing any harm.

SQL> Shutdown immediate;

SQL> startup mount;

SQL> exit

$ nid target /

$ sqlplus /nolog

548
Part IV: RMAN in the Oracle Ecosystem

SQL> connect / as sysdba

SQL> Shutdown immediate;

SQL> startup mount;

SQL> alter database open resetlogs;

Case #10: Using RMAN Duplication to Create a Historical

Subset of the Target Database

The Scenario

Svetlana is a DBA at an online toy-train reseller. Her production database is under heavy load, with constant updates, inserts, and deletes. Over time, Svetlana has noticed that performance is starting to trail off for data-mining operations against certain inventory-tracking tables.

The Problem

She suspects foul play from the Cost-Based Optimizer, thinking that the Explain Plan might be changing in an adverse way. To test things, she is looking for a way to get a historical snapshot of a subset of production tables. She’s considered duplication in the past, but doesn’t have enough room on any server to clone the entire production database.

The Solution

Svetlana can use the Oracle Database 11
g
feature of being able to specify tablespaces to skip during duplication. In this way, she can include only tablespaces that are part of the subset that she needs to test. In addition to skipping tablespaces, she can specify an
until
clause in the
duplication
command itself to set the historical point in time that she would like to test against.

The Solution Revealed
Svetlana will be duplicating to the same server that runs her target database, so she needs to make sure that she has her file renaming strategy worked out. Then, she needs to get an auxiliary database started in NOMOUNT mode. After that, she runs her duplication code.

$ rman log /space/backup/pitrdup.out

connect target /

connect auxiliary sys/password@aux1

duplicate target database to aux1

pfile /space/oracle user/OraHome1/admin/aux1/pfile/init.ora

skip tablespace 'CWMLITE' , 'USERS' , 'ODM' , 'TOOLS'

until sequence 11 thread 1

logfile

'/space/oracle user/OraHome1/oradata/aux1/redo01.dbf' size 5m,

'/space/oracle user/OraHome1/oradata/aux1/redo02.dbf' size 5m,

'/space/oracle user/OraHome1/oradata/aux1/redo03.dbf' size 5m;

The outcome would look something like the following RMAN log. This output has been truncated to save space; we’ve left the highlights that show what RMAN is doing to get rid of unneeded tablespaces. Remember that when you set an
until sequence
clause in RMAN, the sequence you specify is not included as part of the recover set. So, in Svetlana’s code, she has archive logs through sequence 10, but not sequence 11.

Chapter 23: RMAN in the Workplace: Case Studies
549

Recovery Manager: Release 11.2.0.0.2 - Beta on Sun Aug 16 15:41:40 2009

Starting Duplicate Db at 16-AUG-09

using target database control file instead of recovery catalog

allocated channel: ORA AUX DISK 1

channel ORA AUX DISK 1: SID 280 device type DISK

Checking that duplicated tablespaces are self-contained

Datafile 4 skipped by request

Datafile 5 skipped by request

contents of Memory Script:

{

set until scn 945053;

set newname for datafile 1 to

"/home/oracle/app/oracle/oradata/aux1/system01.dbf";

set newname for datafile 2 to

"/home/oracle/app/oracle/oradata/aux1/sysaux01.dbf";

set newname for datafile 3 to

"/home/oracle/app/oracle/oradata/aux1/undotbs01.dbf";

set newname for datafile 6 to

"/home/oracle/app/oracle/oradata/aux1/fbs01.dbf";

restore

clone database

skip forever tablespace "USERS",

"RECO CAT" ;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 16-AUG-09

executing script: Memory Script

executing command: SET until clause

executing command: SET NEWNAME

…

Starting recover at 16-AUG-09

datafile 3 not processed because file is offline

datafile 7 not processed because file is offline

starting media recovery

…

media recovery complete

Finished recover at 16-AUG-09

…

{ Alter clone database open resetlogs;}

executing script: Memory Script

database opened

550
Part IV: RMAN in the Oracle Ecosystem

printing stored script: Memory Script

{# drop offline and skipped tablespaces

sql clone "drop tablespace USERS including contents";

# drop offline and skipped tablespaces

sql clone "drop tablespace RECO CAT including contents";

executing script: Memory Script

sql statement: drop tablespace USERS including contents

sql statement: drop tablespace RECO CAT including content

Finished Duplicate Db at 16-AUG-09

Recovery Manager complete.

Case #11: Recovering from a Lost Datafile (ARCHIVELOG

Mode) Using an Image Copy in the Flash Recovery Area

The Scenario

Tim, a senior DBA for a large manufacturing firm, has long lived by the maxim: “Just because you are paranoid doesn’t mean they aren’t out to get you.” In this vein, he has fought hard up the management chain to garner the resources to keep a full database backup on disk, instead of streaming directly to tape. There is not enough room for every datafile backup on disk, so he has chosen those datafiles that represent the data that is most important to operations and that would be the most impacted by a prolonged outage.

After the database was migrated to 11.2, Tim set up his flash recovery area and created an image copy of his most important files in the FRA. He now takes a nightly incremental backup and applies the incremental to the image copy of those files.

The Problem

Disaster strikes, as it invariably does, near the end of month, when massive data processing is taking place. Tim’s pager starts chirping only moments before a deluge of angry department heads start calling him. Tim turns off his phone and checks the database. He finds that one of the critical tablespaces has a corrupt datafile, file number 5. The file shows up on disk as 0 bytes. The end-of-month processing cannot continue until the file is recovered.

The Solution

Tim will switch to the datafile running in the FRA, which is current as of the level 1 incremental backup the night before. Then, it’s just a matter of applying archive logs to the file to bring it up to the current point in time.

The Solution Revealed
Tim’s preparation means his outage will be significantly minimized.

Here is his action plan:

1.
Switch datafile 5 to the copy in the FRA:

[oracle@dex oracle]$ rman target /

Recovery Manager: Release 11.2.0.0.0

connected to target database: V112 (DBID 2188245167)

RMAN> switch datafile 5 to copy;

Chapter 23: RMAN in the Workplace: Case Studies
551

using target database control file instead of recovery catalog

datafile 5 switched to datafile copy

"/u01/… /V102/datafile/o1 mf payroll 21v9dhtp .dbf"

2.
Recover datafile 5:

RMAN> recover datafile 5;

Starting recover at 16 AUG 09

allocated channel: ORA DISK 1

channel ORA DISK 1: sid 124 devtype DISK

allocated channel: ORA DISK 2

channel ORA DISK 2: sid 132 devtype DISK

starting media recovery

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

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

1v9hwr2 .arc

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

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

1v9hzvb .arc

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

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

1v9j3ts .arc

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

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

1v9j4jz .arc

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

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

1v9j59g .arc

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

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

1v9jc07 .arc

archive log

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

f 1 208 21v9hwr2 .arc thread 1 sequence 208

archive log

filename

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

archive log