Oracle RMAN 11g Backup and Recovery (129 page)

To recover manually using the SQL*Plus prompt, do the following:

1.
Perform the normal RMAN
restore
command. It’s fine to use the
until time
clause.

2.
Perform the normal RMAN
recover
command.

3.
If Step 2 fails, start SQL*Plus from a separate terminal window.

4.
Determine which archived redo logs are required for your restore. You can use the V$ARCHIVED_LOG and V$LOG views to determine the location of the archived redo logs, the time they were created, and their associated log sequence number. Here is an example of queries against the V$ARCHIVED_LOG, V$LOG, and V$LOGFILE views that give us some important information that we will need to complete our recovery:

-- This tells us the last archived redo log file sequence number.

select max(sequence#), max(resetlogs time), max(resetlogs change#)

from v$log history

where resetlogs time (select max(resetlogs time) from v$log history);

-- 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:

376
Part III: Using RMAN Effectively

change 5071334 for thread 1 is in sequence #5

Specify log: { 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 old-time DBAs who have struggled with this issue.

This chapter also covered some miscellaneous recovery topics. We touched on things like archived redo log recoveries, read-only tablespace recovery considerations, and block-level recovery. In short, we covered the wide gamut of RMAN’s recovery toolbox.

Finally, in this chapter, we explored tablespace point-in-time recoveries. Specifically with tablespace point-in-time recovery, you can run into complexities for various reasons, and thus, it might well be that you will need to reference the Oracle documentation for implementation details that relate to your case.

CHAPTER

15

Surviving User Errors:

Flashback Technologies

378
Part III: Using RMAN Effectively

edia recovery with RMAN provides critical safeguards against all kinds of unforeseeable problems—block corruption, hardware failure, even complete
M
database loss. But so far, this book has ignored the largest cause of media recovery operations: user error.

User errors can be roughly defined as errors caused by a human mistake (rather than a software or hardware malfunction), such as a table updated with wrong values, a table dropped, or a table truncated. Such errors are far more common than hardware failures (although, let’s face it, human errors get
called
hardware errors all the time). In general, user errors are classified as logical errors—the error is logical, within the data itself, and a correction that is done using media recovery options will typically be very expensive.

In this chapter, we will discuss the new means in Oracle Database 11
g
to programmatically prepare for and recover from logical errors. This includes some functionality that existed already in the Oracle10
g
Database, but has been extended (and made less painful). This also includes brand-new functionality that can radically change the time it takes to recover from user-induced 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 10
g
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 11
g

■ Flashback Drop

■ Flashback Database

■ Flashback Data Archive—
new
in 11
g

Chapter 15: Surviving User Errors: Flashback Technologies
379

Flashback Query

If you think you recognize Flashback Query from earlier versions of the RDBMS, you’re right: there was some Flashback Query functionality that existed as early as 9
i.
Since version 10
.
2, that functionality has been expanded and simplified to allow you better access. By better access, we mean you don’t rely on the PL/SQL interface. Now, it’s all built into SQL (and sometimes RMAN!), so you don’t have to program a PL/SQL block to look at historical versions of a row.

Flashback and the Undo Segment: A Love Story

The first two types of flashback—Flashback Query and Flashback Table—have their functionality based entirely on technology that has existed in the Oracle Database for years: the undo segments (the segments formerly known as rollback). Undo segments exist to undo transactions that have not been committed. In the past, a committed transaction could not be undone because the associated “before” image of the row in the rollback segment was immediately freed up to be overwritten—so the before images could not be reliably found later on.

This is still true: when you commit a transaction, the extent in the undo segment that contains the before image of the row is freed up to be overwritten. However, changes in the way undo space was used in 9
i
mean that all new transactions look for unused space in the undo tablespace before overwriting previously used segments. Even then, the transaction always goes to the oldest remaining extents first. This means that “before” images of rows in the database last far longer than they ever have in the past—we can reliably find undo segments from past transactions.

This is all very good news, and in 9
i
and later, Oracle put it to use with the flashback query.

Now, we can actually control how long we want the undo extents to remain before they are overwritten. After doing so, we can put undo to good use—to help us undo committed transactions that were mistakes.

The ability to query or change objects back to a certain time in the past is predicated on how long our undo extents can remain in the undo tablespace before they are overwritten. Undo extents are used by new transactions based on space pressure in the undo tablespace. Basically, Oracle will not overwrite undo extents until it has exhausted all other possibilities first—that is, 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

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.