368
Part III: Using RMAN Effectively
List of Datafiles in backup set 211
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 0 Incr 4471684 23-FEB-06
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ROB10R2\SYSTEM01.DBF
6 0 Incr 4471684 23-FEB-06
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ROB10R2\NEWTBS01.DBF
7 0 Incr 4471684 23-FEB-06
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ROB10R2\TEST RECOVER 01.DBF
8 0 Incr 4471684 23-FEB-06
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ROB10R2\TEST RECOVER TWO 01.DBF
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
216 Incr 1 728.00K DISK 00:00:38 23-FEB-06
BP Key: 216 Status: AVAILABLE Compressed: YES
Tag: TAG20060223T144904
Piece Name:
C:\ORACLE\PRODUCT\10.2.0\FLASH RECOVERY AREA\ROB10R2\BACKUPSET\2006 02 23
\O1 MF NNND1 TAG20060223T144904 1ZW7Y40G .BKP
List of Datafiles in backup set 216
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 1 Incr 4472638 23-FEB-06
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ROB10R2\UNDOTBS01.DBF
3 1 Incr 4472638 23-FEB-06
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ROB10R2\SYSAUX01.DBF
4 1 Incr 4472638 23-FEB-06
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ROB10R2\USERS01.DBF
5 1 Incr 4472638 23-FEB-06

Some vendors support “recalling” media from a DR site to use for a local restore. The restore
database preview recall supports this functionality, allowing you to initiate a recall of the required
backup files from a remote disaster recovery site in order to perform the restore preview.
Restoring with the validate and check logical Commands
The restore command comes with some great options that allow you to verify that your database
is recoverable and that the backup itself is valid. First, you can use the validate parameter of the
backup command to cause RMAN to check the backup sets and to make sure your database is
recoverable. When you use the validate option, Oracle checks the most current backup set that
will be needed to recover your database, ensuring that it is complete. This option also checks any
datafile copies and archived redo log backup sets that will be required for recovery and ensures
that they are all complete. Additionally, the validate option does a general validation of the
backup sets to ensure that they are intact. Validation doesn’t take very long and is one way to
ensure that your database is recoverable. Here is an example of a validate operation on our
database:
RMAN> restore database validate;
Starting restore at 05-JUL-02
using channel ORA DISK 1
using channel ORA DISK 2
channel ORA DISK 1: starting validation of datafile backupset
channel ORA DISK 2: starting validation of datafile backupset
channel ORA DISK 1: restored backup piece 1
piece handle D:\BACKUP\RECOVER\BACKUP 4QDSM5IB 1 1
tag TAG20020703T221224 params NULL
channel ORA DISK 1: validation complete
channel ORA DISK 2: restored backup piece 1
piece handle D:\BACKUP\RECOVER\BACKUP 4RDSM5IC 1 1
tag TAG20020703T221224 params NULL
channel ORA DISK 2: validation complete
Finished restore at 05-JUL-02
Another, more complete check of the most current backup set is the check logical parameter

channel ORA DISK 1: restored backup piece 1
piece handle D:\BACKUP\RECOVER\BACKUP 4QDSM5IB 1 1
tag TAG20020703T221224 params NULL
channel ORA DISK 1: validation complete
channel ORA DISK 2: restored backup piece 1
piece handle D:\BACKUP\RECOVER\BACKUP 4RDSM5IC 1 1
tag TAG20020703T221224 params NULL
channel ORA DISK 2: validation complete
Finished restore at 05-JUL-02
Using the validate backupset Command
Using the restore command with the validate and/or check logical parameters only checks the
most current backup set. There may well be times that you want to check a specific backup set.
To do this, you use the validate backupset command. To use this command, you first need to
determine the backup set key that you want to back up. Each backup set, when it is made, is
assigned a unique identifier called the backup set key. To determine the key assigned to the backup
set you are interested in, you can use the list backupset command, as shown in the following
example:
RMAN> list backupset;
List of Backup Sets
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
141 Full 320K DISK 00:02:09 03-JUL-02
BP Key: 141 Status: AVAILABLE Tag: TAG20020703T221224
Piece Name: D:\BACKUP\RECOVER\BACKUP 4QDSM5IB 1 1
List of Datafiles in backup set 141
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 647435 03-JUL-02 D:\ORACLE\ORADATA\RECOVER\REVDATA.DBF
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapter 14: RMAN Advanced Recovery Topics

channel ORA DISK 1: starting validation of datafile backupset
channel ORA DISK 1: restored backup piece 1
piece handle D:\BACKUP\RECOVER\BACKUP 4QDSM5IB 1 1
tag TAG20020703T221224 params NULL
channel ORA DISK 1: validation complete
Call the Movers! Cross-Platform Database
Movement and RMAN
Oracle Database supports manually moving databases across different platforms, even those of
different endian formats. The endian formats relate to byte ordering, and there are two different
formats, big endian and little endian. If you want to move a database between databases of
different endian byte formats, then you have to do so manually, using the RMAN convert datafile
or convert tablespace command along the way to convert the datafiles being transported to the
correct endian format.
In this section, we quickly cover cross-platform transportable tablespaces. We then discuss
the different endian byte-ordering formats. Next, we discuss converting tablespaces for transport
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
372
Part III: Using RMAN Effectively
to platforms with different endian byte formats. We finish with a discussion of using RMAN to
move databases between different platforms with the same endian byte format.
Introduction to Cross-Platform Transportable Tablespaces
On several occasions, we, as DBAs, really wanted to be able to move our tablespaces between
our development NT Oracle database and our production Sun Oracle database. We have had
cases where we really wanted to move them between a Sun platform and an AIX platform. Until
Oracle Database 10g, this was just a dream.
Now, Oracle supports transporting tablespaces across almost all platforms of the Oracle
database family. This has a number of benefits, including:
Efficient publication of data between different content providers
Easy movement of data between data warehouses, data marts, and OLTP systems
Easy migration of databases across platforms

Chapter 14: RMAN Advanced Recovery Topics
373
In this case, the system we are on is using the little endian format. Thus, if the query returns
the same result on both systems, you have a compatible datafile format; if it does not, you need
to use RMAN and the compatible parameter to transport the tablespaces.
Converting the Tablespace Endian Format with RMAN
If you need to convert a tablespace for another platform, RMAN is the tool you use. First, create
the directory that the converted file will be copied to. In our example, we will use the directory
path c:\oracle\oradata\betatwo. Next, make the tablespace that you wish to convert read-only.
Then, simply start RMAN and use the new convert tablespace command, as shown in this example:
Rman target /
RMAN> convert tablespace users
to platform ' AIX-Based Systems (64-bit)'
db file name convert 'c:\oracle\oradata\betatwo',
'c:\oracle\admin\transport aix';
You can also convert datafiles at the destination site:
Rman target /
RMAN> convert datafile c:\oracle\oradata\betatwo\*'
from platform ' AIX-Based Systems (64-bit)'
db file name convert 'c:\oracle\oradata\betatwo',
'c:\oracle\admin\transport aix';
The platform name that we use comes from the PLATFORM_NAME column of the
V$TRANSPORTABLE_PLATFORM view. Oracle is very picky about putting the name in just right.
Once you have completed the conversion, you may complete the move by manually moving the
datafiles/tablespaces using transportable tablespaces. Note that in cases where the endian format is
different, RMAN will not be able to help you. If the endian format is the same, read the next section
to see if you can use RMAN’s new feature that moves your database across platforms for you!
We Like to Move It! Move It!
RMAN in Oracle Database 10g offers a brand-new feature to assist you in moving your databases
across platforms of the same endian byte format. The convert database command, in combination

do the move, but creates only the files that are used for the move.
CONVERT DATABASE NEW DATABASE 'copydb'
transport script 'c:\oracle\copydb\copyscripts'
to platform 'Microsoft Windows IA (32-bit)';
The optional db_file_name_convert parameter allows you to define the directory filenames
for datafiles that need to be converted. Here is an example:
CONVERT DATABASE NEW DATABASE 'copydb'
transport script 'c:\oracle\copydb\copyscripts'
to platform 'Microsoft Windows IA (32-bit)'
db file name convert
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ROB10R2','C:\ORACLE\NEWDBDEST';
Once the command completes, simply follow the directions that RMAN will give you to
complete the conversion process on the target database.
Sometimes Things Just Go Wrong
Sometimes you just cannot get a break. On some rare occasions, RMAN just will not work like it’s
supposed to. So, what do you do? Here are some suggestions we suggest you try if your RMAN
restore/recover is not quite going the way you want it to.
If the restore is not working successfully, execute the crosscheck command, and make sure
that all the backup sets that RMAN thinks are available are actually available. The crosscheck
command is used to make sure that the RMAN catalog and control file are in synch with what is
actually on disk. We discuss the crosscheck command in detail in Chapter 17. In the case where
a restore command is not working, or is picking up a wrong backup set, run these commands:
crosscheck backup of database;
crosscheck backup of archivelog all;
crosscheck archivelog all;
If any of the backup sets show up with a status of EXPIRED, then you may be missing backup
sets that are needed to perform the restore. This can be due to many reasons—loss of a disk drive,
or perhaps a well-meaning system administrator has backed them up to tape for you.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapter 14: RMAN Advanced Recovery Topics

-- This tells us the online redo log sequence numbers.
select a.sequence#, a.first time, b.member
from v$log a, v$logfile b
where a.group# b.group#
order by a.sequence#;
5. The recover database command will indicate which archived redo log it wishes to recover.
In some cases, you may need to enter the name of the online redo log to recover the
database, because the redo contained in it had not been archived yet. In this case, we
will apply the redo from online redo log C:\ORACLE\ORADATA\BETA1\REDO02.LOG:
SQL> recover database
ORA-00279: change 5071334 generated at 08/17/2008 15:35:51 needed for
thread 1
ORA-00289: suggestion :
/oracle01/flash recovery area/ORCL/archivelog/2008 08 17/
o1 mf 1 5 4bk6onh8 .arcORA-00280:
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
376
Part III: Using RMAN Effectively
change 5071334 for thread 1 is in sequence #5
Specify log: {<RET> suggested | filename | AUTO | CANCEL}
C:\ORACLE\ORADATA\BETA1\REDO02.LOG
Log applied.
Media recovery complete.
6. Then simply open the database:
SQL> alter database open;
Database altered.
Summary
In this chapter, we have explored point-in-time recoveries that are available in RMAN. Time-
based, SCN-based, and cancel-based recoveries are all supported by RMAN. This chapter also
touched on RMAN’s ability to recover through the resetlogs command, a welcome feature to us

disasters.
Prepared for the Inevitable: Flashback Technology
When it comes to logical errors, media recovery should not be our first line of attack. It frequently
is the line of attack, but this leads to massive outages. Typically, user error is not something that
we can recover from, because the action is not interpreted as an error by the database. “Delete *
from scott.emp” is not an error; it’s a perfectly legitimate DML statement that is duly recorded in
the redo stream. So if you restore the datafile and then perform recovery, all you will do is, well,
delete * from scott.emp again. Point-in-time recovery can be a solution, but not for the DBA who
is committed to avoiding full restore of the database—way too much outage. Tablespace point-in-
time recovery (TSPITR) offers a toned-down version of media recovery for user error, but it still
requires a full outage on the tablespace, has huge space demands for a temporary clone instance,
and has object-level limitations (think advanced queuing tables).
To assist with user error recovery, and to complement RMAN’s media recovery excellence,
Oracle introduced in Oracle Database 10g the concept of Flashback Technology. Flashback
Technology refers to a suite of features that give you a multitude of different ways to survive user
errors. These features have as a unifying concept only the simple idea that user errors occur and
recovering from them should be simple and fast. The Flashback features are
Flashback Query
Flashback Table
Flashback Transaction—new in 11g
Flashback Drop
Flashback Database
Flashback Data Archive—new in 11g
■
■
■
■
■
■
M

until every extent in the undo tablespace has been utilized. Then, it finds the oldest extent and
overwrites it. The threshold for how far back you can use a flashback query/table is set by how
long Oracle can go from the time a transaction is committed until the time that undo extents for
that transaction get overwritten. The period from committed transaction to undo extent being
overwritten is the flashback window.
Plenty of factors go into determining the flashback window, but the most important is your
transaction load. You can view statistics for undo usage with the view V$UNDOSTAT. Each row
in this view represents the number of undo blocks utilized for a ten-minute period. Running a few
analyses of this view through peak usage should provide a decent template to guide your settings
for undo.
Setting Undo Parameters for Flashback Query and Flashback Table
The guidelines for using Flashback Query demand that you first have automatic undo enabled—
no rollback segments are allowed. (Okay, that’s a lie. It is feasible to use flashback operations with
old-school rollback segments, but Oracle discourages it and so do we. There is no reason to try to
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
380
Part III: Using RMAN Effectively
set up rollback segments manually anymore.) Oracle is best left to control undo management by
using new algorithms that emphasize retention of transactional history—algorithms that do not
exist in rollback segments. Therefore, you need to set UNDO_MANAGEMENT = AUTO in the
PFILE or SPFILE. Second, set your UNDO_TABLESPACE parameter to point to which tablespace
will handle undo duties. Finally, set UNDO_RETENTION = value in seconds. This sets the desired
length of time to keep undo segments around.
Performing Flashback Query
Performing a flashback query of a table is simple, now that it has been integrated into SQL. All you
need to know is the point in time in the past for which you would like to view the contents of a
table, and then you plug it into your query:
select scr id, head config from ws app.woodscrew as of timestamp
to timestamp('2009-06-27 04:27:00','YYYY-MM-DD HH:MI:SS')
where scr id 1001;

This workshop has you build a few tables and populate them with a few dummy rows so that
you can watch Flashback Versions Query in action. The following is the DDL and DML for the
WOODSCREW table and indices. This code also builds a secondary table with rows for future
use in Flashback Drop and Flashback Database. You are obviously not compelled to use our
simplistic little test here and could easily test with existing dummy tables in your system.
create table woodscrew (
scr id number not null,
manufactr id varchar2(20) not null,
scr type varchar2(20),
thread cnt number,
length number,
head config varchar2(20));
alter table woodscrew add primary key
(scr id, manufactr id) using index;
create index woodscrew identity on woodscrew
(scr type, thread cnt, length, head config);
create table woodscrew inventory (
scr id number not null,
manufactr id varchar2(20) not null,
warehouse id number not null,
locale varchar2(20),
count number,
lot price number);
insert into woodscrew values
(1000, 'Tommy Hardware', 'Finish', 30, 1.5, 'Phillips');
insert into woodscrew values
(1000, 'Balaji Parts, Inc.', 'Finish', 30, 1.5, 'Phillips');
insert into woodscrew values
(1001, 'Tommy Hardware', 'Finish', 30, 1, 'Phillips');
insert into woodscrew values

update woodscrew set head config 'Slot'
where scr id 1001;
commit;
Step 3.
View the new data in the table. From Tables, select the WOODSCREW table, choose
View Data from the Actions drop-down list, and then click Go. Note in the following illustration
that two rows now have Slot instead of Phillips.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapter 15: Surviving User Errors: Flashback Technologies
383
Step 4.
Within the woodscrew business organization, it was determined that the screws with scr_id
1001 are not slot-headed, but rather Phillips. There has been a logical corruption introduced into the
database. Let’s review a single row and see what versions the row has been through. From the Tables
view, select the WOODSCREW table, choose Flashback Versions Query from the Actions list, and
then click Go. This takes you to the Perform Object Level Recovery Wizard.
Step 5.
In the wizard, we need to provide the parameters of our flashback query. First, choose
all columns by selecting Move All under Step 1 of the wizard. Click the Next button from the right
side of the page. Under Step 2, specify a clause that isolates a single row. We will use the following
WHERE clause:
where scr id 1001 and manufactr id 'Tommy Hardware'
After specifying the clause and clicking the Next button, we see the different versions of the
row, along with the option to select which SCN to recover back in time to (should we so decide).
Here, we see the insert and the update as two transactions. We can click the Transaction ID to view
the specific transactions (we’ll discuss the function Flashback Transaction later in this chapter).
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
384
Part III: Using RMAN Effectively
Step 6.

With row movement enabled, you can move forward with normal operations on the table. Then,
when a user-induced corruption occurs in the table, you can use SQL at the command line to
perform the Flashback Table operation:
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapter 15: Surviving User Errors: Flashback Technologies
385
flashback table matt.woodscrew to timestamp
to timestamp('2009-06-29 13:30:00','YYYY-MM-DD HH24:MI:SS')
Alternatively, you can use the SCN if you have been able to determine the SCN (through
investigation via Flashback Query, for example):
flashback table matt.woodscrew to scn 751652;
Like Flashback Query, the performance of a Flashback Table operation depends on the amount
of data that has to be rewound, and how far back you are rewinding. The more data that has to be
undone, the longer the operation will take. But this will always be faster than trying to perform a
point-in-time recovery of the table by other methods: you can try TSPITR, or you can try to restore
the tablespaces to a different instance and then export the table from the clone instance and import
back into production. Nothing can come close to Flashback Table in terms of performance.
Flashback Table with Oracle Enterprise Manager
The added strength of OEM for Flashback Table is the ability to first explore the table via Flashback
Versions Query to determine exactly what time you want to flashback to. If you already know
the exact time for flashback, using SQL at the command line would be just as simple as using the
Flashback Table Wizard in OEM. OEM does, however, provide a way to determine what
dependencies are at play, as described in the following RMAN Workshop.
Enabling Row Movement and Flashback Table
It is critical that you foresee possible Flashback Table candidates and enable row movement
as soon as possible. You cannot enable row movement and then flashback the table to a point
prior to enabling row movement. Such an operation will result in the following error:
ORA-08189: cannot flashback the table because row movement is not enabled.
In other words, you cannot wait until you need to flashback a table, and then enable row
movement as part of the flashback operation.

row to review here. We will use the same row as in previous workshops:
where scr id 1001 and manufactr id 'Tommy Hardware'
Now we can see the information about the DELETE operation that whacked our poor
WOODSCREW table.
Step 5.
You can click the Transaction ID to review the entire delete. However, of more
importance to us is the Flashback SCN column, which shows us the SCN to set to undo the
DELETE operation. With the appropriate DELETE transaction checked from the list, simply click
Next to automatically choose the flashback SCN specified on this screen.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Chapter 15: Surviving User Errors: Flashback Technologies
387
Step 6.
In Step 4 of 7 in the Flashback Table Wizard, Oracle allows you to specify any logically
related objects that should be rewound to the same SCN as this table. Oracle automatically
honors all constraints that exist for the table, but you may have logically related tables that should
be flashed back. If so, this is your opportunity to specify them. In our example, we do not have
any related tables, so click Next.
Step 7.
Voilà! We are magically transported to Step 7 of 7, where we see the summary of the
action that will take place. Of most use here are the Show Row Changes and Show SQL buttons,
which will show, respectively, what rows will be changed and what SQL will be executed by
OEM on your behalf. Click Submit. If there is any problem, or if you just feel better about it, you
can cut the OEM-generated SQL into a SQL*Plus session to run the Flashback Table operation:
FLASHBACK TABLE MATT.WOODSCREW TO SCN 804109;
Flashback Transaction
There’s always more than one way to organize a hunt for bad data in the database. Flashback
Transaction allows you to look at all changes made by a specific transaction, or all transactions
in a certain timeframe. Then you can go in and undo an error at the transaction level, instead of
rolling back the entire table. This focused level of flashback allows you to keep all other changes