Oracle RMAN 11g Backup and Recovery (132 page)

Chapter 15: Surviving User Errors: Flashback Technologies
389

Step 5.
Click the linked Transaction ID from the available table of transactions, and you can view the details of each change vector associated with this transaction. This provides you with the specific SQL statement that was used to perform each of the undesirable actions and provides a roadmap for undoing just a specific subset of the whole transaction. Click OK to go back to the Flashback Transaction Wizard.

Step 6.
Click Next, and the Flashback Transaction will finalize the operations that will be required to complete the rollback. This may take a few moments to complete. When it’s completed, you will get a final review page of the operations, as well as a box in which to run any follow-up SQL that may be required to confirm the changes, prior to a commit. Then, click Finish.

Step 7.
OEM provides a confirmation that the selected transaction has been backed out completely. Click OK and then you are put back in the Tables page, where you can View Data on WOODSCREW again to confirm that the delete has been backed out.

Flashback Drop

Flashback Drop allows you to “undrop” database objects. No longer will you have users desperate for the entire database to be restored to a point in the past because they thought they were on the DEV instance instead of PROD.

There’s nothing all that dramatic about how Flashback Drop has been implemented. In Oracle Database 10
g,
when you drop a table, it merely gets renamed to a system-identifiable string, but the segment remains in the tablespace it was dropped from. It will remain there until you undrop the object or purge it manually, or until the tablespace runs out of space for regular objects. If space pressure exists in the tablespace, Oracle will begin to age out dropped objects from oldest to newest.

When you drop an object, Oracle doesn’t just rename that object. All dependent objects move to the Recycle Bin as well: indices, triggers, and constraints. Therefore, when you undrop the table, its entire dependent chain comes back with it.

The Recycle Bin

The Recycle Bin is a virtual directory of all dropped objects in the database—simply a list of objects that have been dropped but not purged. The Recycle Bin is a logical container and does not require a specific storage location; actual storage for all dropped objects is in the tablespace

390
Part III: Using RMAN Effectively

the object was in prior to being dropped. Consider an example. User matt drops the table WS_

APP.WOODSCREWS. The WOODSCREWS table is in the tablespace WS_APP_DATA, but its two indices are in the WS_APP_IDX tablespace. When WOODSCREWS is dropped, the table is renamed to an internal name, and so are the two indices that existed on the table. Both appear in the DBA_RECYCLEBIN view. However, the actual WOODSCREWS table segment still exists in the WS_APP_DATA tablespace, and the indices still exist in the WS_APP_IDX tablespace. They are logically part of the Recycle Bin, but physically exist in the same place they always have.

The Recycle Bin is quickly viewed via the following two data dictionary views:

■ USER_RECYCLEBIN

■ DBA_RECYCLEBIN

Purging the Recycle Bin

Manually eliminating dropped objects from the Recycle Bin is not necessary. Objects are purged from the Recycle Bin as the space is required by other segments in the tablespace. In other words, dropped objects continue to take up space in a tablespace until other objects in that tablespace run out of free space elsewhere. Then, the first dropped object is the first object to be purged.

Oracle automatically looks to purge indices before tables so that actual data is the last thing to be lost. Recycle Bin objects will also be dropped before a tablespace autoextends, if autoextend is on.

The new
purge
command exists to purge the Recycle Bin. You can purge by user, by object, by tablespace, or purge the entire Recycle Bin:

purge table matt.woodscrews;

purge index matt.woodscrews pk idx;

purge tablespace sales;

purge recyclebin;

How Long Do Objects Live in the Recycle Bin?

A valid question, but the answer, of course, is: it depends. No, really. It depends. The real question you probably want to ask is, “Can I control how long an object lives in the Recycle Bin?” The answer to this question is no.

You cannot force an object to remain in the Recycle Bin if space pressure exists in the tablespace of the dropped object. Even with autoextend on, the dropped object is purged before the tablespace extends. So, if you want to determine a certain lifespan on objects in the Recycle Bin, you are left with two choices: either make the tablespace overly large to accommodate drops, or manually manage the Recycle Bin and purge those objects you don’t want to keep, to leave space for those you do want to keep.

You can, therefore, shorten the stay of an object in the Recycle Bin. But you cannot force something to remain, given a shortage of tablespace room.

Chapter 15: Surviving User Errors: Flashback Technologies
391

Undropping Objects in the Recycle Bin

Getting objects back from the Recycle Bin is pretty simple—a simple SQL command renames the object back to its original name, along with any dependent objects:

flashback table ws app.woodscrews to before drop;

Of course, sometimes it’s not that simple. For instance, if you have multiple dropped objects with the same name, then you would have to refer to the object by its new and improved Recycle Bin name:

SQL> select object name, original name, droptime,

dropscn from user recyclebin;

OBJECT NAME ORIGINAL NAME

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

DROPTIME DROPSCN

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

RB$$48623$INDEX$0 PK WOODSCREW

2004-01-12:15:21:26 1241651

RB$$48622$TABLE$0
WOODSCREW

2004-01-12:15:21:26 1241652

SQL> flashback table " RB$$48622$TABLE$0" to before drop;

Note the quotes around the Recycle Bin object name. These are required due to special symbols in the name.

If you have dropped an object and then created a new object with the same name, you can still flashback the first object. There is syntax in the flashback SQL to rename the object when you pull it from the Recycle Bin:

flashback table ws app.woodscrews to before drop rename to woodscrews history;
RMAN Workshop:
Explore Flashback Drop

and the Recycle Bin

Workshop Notes

It’s time to drop our WOODSCREW table and review the Recycle Bin contents. Although you can do this at the command line, this workshop shows you how to undrop a table by using OEM.

Step 1.
Drop the WOODSCREW table. From OEM, choose Schema | Tables and change the schema to the owner of the WOODSCREW table. Select the WOODSCREW table from the list of tables, and then click the Delete with Options button. A screen offers multiple delete options—

drop the table, delete the rows, or truncate. Choose the first option (DROP) and click Yes.

392
Part III: Using RMAN Effectively