Oracle RMAN 11g Backup and Recovery (133 page)

Step 2.
View the list of tables. Note that WOODSCREW is no longer in the list. Click the Recycle Bin link in the lower-right corner of the screen to access the Recycle Bin, shown in the following screen shot. First, under Search, enter the Schema Name of the owner of the WOODSCREW table and click Go.

Note in the screen shot that the table WOODSCREW is in the Recycle Bin, along with the primary key index and the WOODSCREW_IDENTITY index. Also worth noting is the View Content button on the right, which allows you to look at the rows in the dropped table to determine if it is the correct object to be flashback dropped.

Step 3.
Check the box next to the WOODSCREW table, and then click the Flashback Drop button.

Step 4.
The next page enables you to rename the undropped object. After you click Next, you get the Impact Analysis from OEM explaining what exactly will be flashback dropped, as shown next.

Chapter 15: Surviving User Errors: Flashback Technologies
393

Step 5.
Click Submit. A confirmation screen tells you the selected tables have been restored from the Recycle Bin. You can confirm this by navigating back to OEM and choosing Administration |

Schema | Tables and choosing the schema of the WOODSCREW owner. The WOODSCREW table will be back in place.

Step 6.
Delete the WOODSCREW table again. Click Recycle Bin and find the dropped table again, as outlined in Step 2. Check the box next to WOODSCREW, and then click Purge (next to Flashback Drop).

Step 7.
A confirmation screen asks you if you want to purge the selected objects and their dependents. Click Continue. The Recycle Bin of the WOOD user is now empty of the WOODSCREW table and its indices. They have been permanently removed from the database.

Flashback Database

The most revolutionary Flashback Technology may also be the one that gets used the least often.

Flashback Database provides the ability to quickly rewind the entire database to a previous point in time. This operation has the same end result as you would get from doing point-in-time recovery using RMAN or user-managed recovery. However, Flashback Database does not require the restore of all of the database’s datafiles from the most recent backup, followed by a roll-forward using all the archive logs that have accumulated since that backup. By avoiding these costly operations, Flashback Database can perform a point-in-time recovery in a fraction of the time typically required for such an operation.

Flashback Database works by incrementally recording all blocks that have changed at a timed interval. These flashback “checkpoints” then provide the points to which the database can be

“rewound.” After rolling back to the flashback checkpoint, you can use archive logs to then roll forward to the exact time or SCN specified by the
flashback database
command. So, the operation uses new technology as well as that old standby, the archive logs, to provide a fast way to perform point-in-time recovery.

Typically, there are fewer archive logs to be applied after a flashback checkpoint than must be applied to the last backup (typically taken every night, versus every few minutes for flashback logs), so the recovery stage of flashback is very quick.

Flashback Logs

Flashback Database implements a new type of log, called the
flashback log.
Flashback logs are generated by the database at regular intervals and accumulate in the flash recovery area (FRA). You must have an FRA for Flashback Database; the logs cannot be created anywhere else. The flashback log contains a copied image of every block that has been changed since the last flashback log was generated. These blocks can then be reinstated into the database when a
flashback database
command is issued to rewind the database back to its state at the time specified in the
flashback
command.

Because entire blocks are being dumped to the flashback logs, they can accumulate very quickly in extremely active databases. Setting an appropriately sized FRA is crucial to the success of meeting your Flashback Database needs. In addition, you can manually turn off flashback logging, as follows, for certain tablespaces that could be manually re-created after a Flashback Database operation, and thereby decrease the amount of logging that occurs: alter tablespace ws app idx flashback off;

394
Part III: Using RMAN Effectively

You can turn flashback logging back on at any time, as follows, but it is worth noting that you cannot rewind backward through a flashback logging gap for the tablespace you turned off: alter tablespace sales idx flashback on;

Any tablespace that has flashback logging turned off for any period within the
flashback
database
command would need to be offlined prior to performing the Flashback Database operation.

Flashback Retention Target

The lifespan of flashback logs correlates directly to how far back in time you would like to have the Flashback Database option. By default, the flashback logs are kept long enough so that you can always flashback 24 hours from the current time. If this is too long or too short a time, you can change it with an initialization parameter:

alter system set db flashback retention target 720;

The value is specified in minutes (720 would be 12 hours).

RMAN Workshop:
Configure for Flashback Database

Workshop Notes

This workshop walks you through the primary steps required to configure the database initially for using flashback logging for Flashback Database operations.

Step 1.
Shut down the database and startup mount. The database must be mounted but not open.

SQL> select status from v$instance;

In addition, check to make sure the database is in ARCHIVELOG mode, which is required for Flashback Database:

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE DB RECOVERY FILE DEST

Oldest online log sequence 62

Next log sequence to archive 64

Current log sequence 64

Step 2.
Set the flashback retention target to your desired value. We will use 12 hours as the window.

alter system set db flashback retention target 720

SCOPE BOTH SID '*';

Step 3.
Set the values for DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE

(FRA parameters). Note that if you have already set these for your RMAN backup strategy, you should review the parameters now. Flashback logs increase FRA usage significantly. It would behoove you to at least double the given size of the FRA.

Chapter 15: Surviving User Errors: Flashback Technologies
395

SQL> ALTER SYSTEM SET DB RECOVERY FILE DEST SIZE 2335825920

SCOPE BOTH SID '*';

SQL> ALTER SYSTEM SET DB RECOVERY FILE DEST '/u02/fra/'

SCOPE BOTH SID '*';

Step 4.
Turn flashback logging on. This is done in the same fashion as turning ARCHIVELOG

mode on—with an
alter database
command when the database is mounted but not open: alter database flashback on;

Step 5.
Turn flashback logging off for any tablespaces that you deem do not require it: alter tablespace sales idx flashback off;

Step 6.
Open the database:

alter database open;

Flashback Database: Tuning and Tweaking

So, you’ve determined that Flashback Database provides you with a fallback position you desire for your database, and you have determined how far back you want your fallback position to be.

You’ve set your DB_FLASHBACK_RETENTION_TARGET. Now, the questions come up: “How do I know if I have enough space in my FRA to handle the volume of flashback logs being generated?

And, for that matter, how much flashback logging is occurring?” The following sections answer those questions.

Using V$FLASHBACK_DATABASE_LOG

One thing at a time. First, Oracle provides built-in analysis for you to use in determining if you need to increase the size of your FRA. After you enable flashback logging, Oracle begins to keep track of the amount of flashback logging that is occurring, and stores it in the view V$FLASHBACK_

DATABASE_LOG. This view actually provides an estimate for the total flashback size: select estimated flashback size from v$flashback database log;

Note that this view gives the size for flashback logs, not for all users in the FRA, so you need to add this value to whatever size you need for archive logs and RMAN backups. This estimated value only gets better with age, meaning that as the database runs through its day-to-day (and then month-to-month) operations, Oracle can provide a better estimate of the size. So, it is a good idea to check back in with this estimator to find out if you still have the right specifications in place.

V$FLASHBACK_DATABASE_LOG also provides you with the actual oldest time that you can flashback the database to, given the current size of the FRA and the currently available flashback logs. You can use this as another indicator of space issues in the FRA. The following
select
statement will provide you with a basic understanding of the state of the flashback logs: select oldest flashback scn, oldest flashback time