BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 

 

 

Oracle Flashback Database

Oracle Tips by Burleson Consulting

This is an excerpt from "Oracle 10g New Features for Administrators" by Ahmed Baraka.

How Flashback Database Works

• Once you enable the flashback database feature, at regular intervals, a new process   RVWR (RecoveryWriter) copies images of each altered block in the datafiles from   memory (flashback buffer) to the new flashback logs.

• Oracle stores these flashback logs in the flashback recovery area.

• If you want to flashback to 8:00 A.M., it may turn out that the flashback logs nearest to   the target time were written at 7:56 A.M. To cover this gap, you must apply the changes   from archived or online redo log files pertaining to that period.

• Always remember that Oracle doesn’t guarantee that you can flashback your database   to the flashback retention target. If Oracle is running low on free space in the flash   recovery area for newly arriving archived redo log files, it will remove some flashback   logs to make room.

Flashback Database Considerations

• If a datafile was resized during the time span covered by the Flashback Database operation, you can’t flashback that datafile. Instead, you must offline that particular datafile before you start the flashback database operation.

• If a control file has been restored or re-created during the time span you want to   flashback over, you can’t use the Flashback Database feature.

• You can’t flashback a database to before a RESETLOGS operation.

• You can’t flashback a datafile that was dropped or shrunk during the time span covered   by the flashback table operation.

Configuring Flashback Database

1. Ensure that your database is in the archivelog mode.

V$DATABASE (cols: logmode)
ARCHIVE LOG LIST (in SQL*Plus)

2. Your database must be using the flash recovery area.

SELECT VALUE FROM V$PARAMETER WHERE NAME = 'db_recovery_file_dest'

3. You must set the initialization parameter DB_FLASHBACK_RETENTION_TARGET to set your flashback retention target (in minutes).

ALTER SYSTEM SET
DB_FLASHBACK_RETENTION_TARGET=1440

4. Shut down the database and restart in the MOUNT EXCLUSIVE mode.

5. Turn the flashback database feature on with the following command:

ALTER DATABASE FLASHBACK ON;

6. Use the ALTER DATABASE OPEN command to open the database.

SELECT FLASHBACK_ON FROM V$DATABASE;

Note: You can turn the feature off by using the ALTER DATABASE FLASHBACK OFF command while the database in the MOUNT EXCLUSIVE mode. When you do so, Oracle deletes all flashback database logs in the flash recovery area.

Note: If you don’t want certain tablespaces to be part of your flashback operations, issue the following command after setting the tablespace offline:

ALTER TABLESPACE USERS FLASHBACK OFF

Flashbacking a Database

1. Restart the database in the MOUNT (exclusive) mode then issue one of the commands:

FLASHBACK DATABASE TO SCN 5964663
FLASHBACK DATABASE TO BEFORE SCN 5964663
FLASHBACK DATABASE TO TIMESTAMP (SYSDATE -1/24)
FLASHBACK DATABASE TO SEQUENCE 12345

2. Open the database with READ ONLY option to check that the database flashed back to the correct time.

3. If you decide to go back further in time, you can flashback the database again.

4. If you determine that you flashed back too far into the past, you can use redo logs to roll forward.

5. Open the database with RESETLOGS option:

ALTER DATABASE OPEN RESETLOGS

6. If you want to completely undo the effects of the flashback database operation, just use the command RECOVER DATABASE to perform a complete recovery of the database.

Displaying Flashback Storage Information

In order to estimate the space you need to add to your flash recovery area for accommodating the flashback database logs:

SELECT ESTIMATED_FLASHBACK_SIZE,
RETENTION_TARGET, FLASHBACK_SIZE FROM
V$FLASHBACK_DATABASE_LOG

To really know how far back you can flashback your database at any given time, you must query the V$FLASHBACK_DATABASE_LOG in the following manner:

SELECT OLDEST_FLASHBACK_SCN,
OLDEST_FLASHBACK_TIME FROM
V$FLASHBACK_DATABASE_LOG

The view V$FLASHBACK_DATABASE_STATS helps you monitor the I/O overhead of logging flashback data.

BEGIN_TIME and END_TIME - stands for the beginning and ending hourly time intervals for which the view’s statistics were collected. Oracle collects flashback data on an hourly basis for a maximum of 24 hours. If you issue a query on the table, however, it may return 25 rows, the 25th row being for the most recent fraction of time after the last (24th) row was logged in the view.

FLASHBACK_DATA - stands for the number of bytes of flashback data written during the interval.

DB_DATA - stands for the number of bytes of database data read and written during the interval.

REDO_DATA - stands for the number of bytes of redo data written during the interval.

ESTIMATED_FLASHBACK_SIZE - is identical to the value of the  ESTIMATED_FLASHBACK_SIZE column in the V$FLASHBACK_DATABASE_LOG view.

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter