10g Oracle Flashback Database

Using Oracle Flashback Database

Block-level logging is now provided again in release 10g as Flashback Database, but in a fashion that has minimal performance overhead.

■The Different Flashback Technologies:

①Flashback Database

Flashback Database will not back out physical corruption, only logical corruption caused by user error.

②Flashback Query: Versions, Transaction, and Table

Flashback Query, in all its variations, relies on the use of UNDO segments.

③Flashback Drop

Note that Flashback Drop is specifically for the DROP command; you cannot flash back a TRUNCATE command.

■When to Use Flashback Technology

①Consider using Flashback Database only when you would also consider using incomplete recovery;

eg:

dropping a user or a tablespace;A table truncation...

②Flashback Drop will restore a table

Unlike Flashback Database, Flashback Drop does not require any configuration; it is always available, unless you specifically disable it.

③

Like Flashback Drop, the Flashback Query,Transaction, 

and Table facilities are always available without any configuration other than granting appropriate privileges.

They may, however, require some tuning of undo management.

***

In the case of media damage, such as losing a datafile, no flashback technology can help. That is what the standard backup, restore, and recovery procedures are for.

■Flashback Database Architecture

①flashback buffer；②flashback logs；③RVWR(Recovery Writer)

Once Flashback Database is enabled, images of altered blocks are copied from time to time from the database buffer cache to a new memory area within the SGA, the flashback buffer.

This flashback buffer is flushed to disk, to the flashback logs, by a new background process: the Recovery Writer, or RVWR.

There is no change to the usual routine of writing changes to the log buffer, which the LGWR then flushes to disk;

flashback logging is additional to this. Unlike the redo log, flashback logging is not a log of changes; it is a log of complete block images.

***

Unlike redo logs, the flashback logs cannot be multiplexed and are not archived. They are created and managed automatically

Flashback Database requires archivelog mode and the use of ALTER DATABASE OPEN RESETLOGS to create a new incarnation of the database.

■Configuring Flashback Database

1. Ensure that the database is in archivelog mode.

SQL> select log_mode from v$database;

2. Set up a flash recovery area for the flashback logs.

Location: DB_RECOVERY_FILE_DEST

Size:     DB_RECOVERY_FILE_DEST_SIZE

SQL> alter system set db_recovery_file_dest='/flash_recovery_area';

SQL> alter system set db_recovery_file_dest_size=8G;

3. Set the lifespan for the flashback retention target.

Reused time:DB_FLASHBACK_RETENTION_TARGET specifies a time in minutes (the default is one day)

SQL> alter system set db_flashback_retention_target=240;

if the flash recovery area is undersized, Oracle may not be able to keep to it.

4. Cleanly shut down and mount the database.

SQL> shutdown immediate;

SQL> startup mount;

5. Enable flashback logging.

SQL> alter database flashback on;

This will start the RVWR process and allocate a flashback buffer in the SGA.

6. Open the database

SQL> alter database open;

■Monitoring Flashback Database

1.Confirm that flashback monitoring is actually enabled:

SQL> select flashback_on from v$database;

2.To monitor the current flashback capability and estimate the space needed

・V$FLASHBACK_DATABASE_LOG:monitor the current flashback capability and estimate the space needed for

flashback logs

・V$FLASHBACK_DATABASE_STAT:gives a historical view of the rate of disk I/O for the datafiles, the online redo log files, and the flashback log files.

SQL> select retention_target,estimated_flashback_size,flashback_size from V$FLASHBACK_DATABASE_LOG;

SQL> select oldest_flashback_scn,oldest_flashback_time from V$FLASHBACK_DATABASE_LOG;

SQL> select BEGIN_TIME,END_TIME,FLASHBACK_DATA,DB_DATA,REDO_DATA,ESTIMATED_FLASHBACK_SIZE from V$FLASHBACK_DATABASE_STAT;

3.The size of the flashback buffer

Be outside the DBA’s control, but to see the current size

SQL> select * from v$sgastat where name like 'flashback%';

■Using Flashback Database

1. Shut down the database.

2. Mount the database.

3. Flash back to a time, an SCN, or a log switch sequence number.

4. Open the database with RESETLOGS.

・Flash Back with SQL*Plus

①SQL> shutdown abort;

②SQL> startup mount;

③SQL> flashback database to timestamp to_timestamp('20-12-04 10:00:00','dd-mm-yy hh24:mi:ss');

  SQL> alter database open read only;

④SQL> shutdown abort;

  SQL> alter database open resetlogs;

・Flash Back with RMAN

Within the Recovery Manager environment you have three options: you can flash back to a time, to an SCN, or to a log switch sequence number:

RMAN> flashback database to time = to_date('20-12-04 10:00:00','yy-mm-dd hh24:mi:ss');

RMAN> flashback database to scn=2728665;

RMAN> flashback database to sequence=2123 thread=1;

・Flash Back with Database Control

The only limitation to be aware of is that the granularity of the time-based flashback with Database Control is only to the minute, 

whereas RMAN can flash back to a second and SQL*Plus can flash back to a timestamp, which can be to a millionth of a second.

■Managing the Flash Recovery Area

The flash recovery area is a default location for all recovery-related files. 

These can include archive logs, RMAN backups, controlfile auto-backups, multiplexed controlfile and redo log copies, and the flashback log files.

All these files can be redirected elsewhere if you wish, except for the flashback logs;

・Space Usage Within the Flash Recovery Area

DB_RECOVERY_FILE_DEST_SIZE

V$RECOVERY_FILE_DEST　：Gives an overall picture of space usage。

**

The “space used” figure is space used by your database. If you have put non-database files in the flash recovery directory, Oracle will know nothing about them.

RMAN> delete obsolete;

This will physically delete all backups that RMAN no longer deems necessary according to its retention policy

RMAN> backup ... delete input;

The DELETE INPUT keywords instruct RMAN to remove files as they are backed up.

・Backing Up the Flash Recovery Area

1.

RMAN> backup recovery area;

backs up all recovery files created in the flash recovery area that can be backed up:

①Full and incremental backup sets

②Datafile and archive log file image copies

③Controlfile auto backups

④Archive logs

It will not back up the following file types:

①Flashback logs

②Current controlfile

③Online redo log files

2.

RMAN> backup recovery files;

extends the first command to include all recovery files, whether or not they are in the flash recovery area.

■Limiting the Amount of Flashback Data Generated

・Excluding Tablespaces from Flashback

SQL> alter tablespace flashback off;

which can be executed at any time.

SQL> alter tablespace flashback on;

which can be executed only when the database is in mount mode.

To view the status of flashback

SQL> select tablespace_name,flashback_on from v$tablespace;

・Flash Back When Tablespaces Are Not Logging Flashback Data

If one or more tablespaces are not generating flashback data, then before carrying out a flashback operation the files making up the tablespaces must be taken offline.