In Oracle : Oracle Flashback in 10g

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

Oracle DBA

Real Application Cluster (RAC) Maintenance tasks Backup and Recovery Database Architecture Replication Oracle Performance Tuning Oracle DBA - Other articles Oracle scripts/ Selects for DBA Oracle Errors (ORA-nnnnn)

The last articles in the site (HOT)

The most visited articles in the site

In-Oracle.com  -> Oracle DBA

-> Oracle Flashback in 10g

 

 Oracle Flashback in 10g

 

 

1. What is Oracle Flashback ?

2. How can we see if Oracle Flashback is enabled in our database ?  

3. Enabling the Oracle Flashback feature

4. Which are the features related to the Oracle Flashback ?

5. What is Oracle Recycle Bin ?

 

 

1. What is Oracle Flashback ?

 

Using a new type of recovery log (called a flashback log ), the Flashback feature allows you to perform queries that return past data, or the history of changes on a particular table, to undo undesirable changes to a particular table. With flashback feature enabled is possible also to recover a table or the entire database to a previous point in time.

2. How can we see if Oracle Flashback is enabled in our database ?

 

SELECT FLASHBACK_ON, LOG_MODE FROM GV$DATABASE;

 

Oracle Flashback

 

In this picture we can see that the Oracle Flashback is not enabled. Oracle Flashback is not enabled by default.  

 

 

3. Enabling the Oracle Flashback feature

  • Initialization Parameters

DB_RECOVERY_FILE_DEST 

Setting the location of the flashback 
recovery area.

Specifying this parameter without also specifying the DB_RECOVERY_FILE_DEST_SIZE initialization parameter is not allowed.

Syntax:

DB_RECOVERY_FILE_DEST = directory | disk group 

Default value: There is no default value. 

Modifiable ALTER SYSTEM ... SID='*' 

For Real Application Clusters uou must set this parameter for every instance, and multiple instances must have the same value. 

DB_RECOVERY_FILE_DEST_SIZE

Setting the size of the flashback 
recovery area (by default in bytes)

Syntax:

DB_RECOVERY_FILE_DEST_SIZE = integer [K | M | G] 

Default value: There is no default value. 

Modifiable ALTER SYSTEM ... SID='*' 

For Real Application Clusters you must set this parameter for every instance, and multiple instances must have the same value. 

DB_FLASHBACK_RETENTION_TARGET

Setting the retention time for flashback files (in minutes)

( OPTIONAL )

Syntax:

DB_FLASHBACK_RETENTION_TARGET = integer 

 

Default value: 1440 (minutes) 


Modifiable ALTER SYSTEM 


Range of values 0 to 4294967295

 (0 to max value represented by 32 bits) 

 

 

  In my care I use pfile initialization file. I add the following parameters to the pfile:

 

*.db_recovery_file_dest='C:/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648

  •   put the database in archivelog, enable flasback features, start the database (connected as sys )

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 62915940 bytes
Database Buffers 100663296 bytes
Redo Buffers 2945024 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.

SQL> SELECT FLASHBACK_ON, LOG_MODE FROM GV$DATABASE;

FLASHBACK_ON     LOG_MODE
---------------------          ------------
YES                              ARCHIVELOG



4. Which are the features related to the Oracle Flashback ?

  • Flashback Database: quickly rewinds an Oracle database to a previous time, to correct any problems caused by logical data corruptions or user errors. For some examples click here.

  • Flashback Table: provides the DBA the ability to recover a table or a set of tables to a specified point in time quickly, easily, and online. For some examples click here.

  • Flashback Drop: When a user drops a table, Oracle automatically places it into the Recycle Bin. If an object is still in Recycle Bin, the object can be restored.

  • Flashback Query: provides the ability to view the data as it existed in the past. For some examples click here.

  • Flashback Versions Query: provides a way to audit the rows of a table and retrieve information about the transactions that changed the rows. It retrieves all committed versions of the rows that exist or ever existed between the time the query was issued and a point in time in the past. It accomplishes this by utilizing Automatic Undo Management. For some examples click here.

  • Flashback Transaction Query: to view all changes to a row over a period of time and the associated transaction id's. This feature allows you to append VERSIONS BETWEEN clause to a SELECT statement that specifies an SCN or timestamp range between which you want to view changes to row values. For some example click here.

 

5. What is Oracle Recycle Bin ?

 

The Recycle Bin is a virtual container where all dropped objects reside. Underneath the covers, the objects are occupying the same space as when they were created. If table EMP was created in the USERS tablespace, the dropped table EMP remains in the USERS tablespace. Dropped tables and any associated objects such as indexes, constraints, nested tables, and other dependant objects are not moved, they are simply renamed with a prefix of BIN$$. You can continue to access the data in a dropped table or even use Flashback Query against it. Each user has the same rights and privileges on Recycle Bin objects before they were dropped. You can view your dropped tables by querying the new RECYCLEBIN view. Objects in the Recycle Bin will remain in the database until the owner of the dropped objects decides to permanently remove them using the new PURGE command. The Recycle Bin objects are counted against a user's quota. But Flashback Drop is a non-intrusive feature. Objects in the Recycle Bin will be automatically purged by the space reclamation process if

  • a user creates a new table or adds data that causes their quota to be exceeded.

  • the tablespace needs to extend its file size to accommodate create/insert operations.

Dropped the wrong table? No problem. Just undrop it with Flashback Drop. 

 

Click here to learn how to use Oracle Recycle Bin.

 

In-Oracle.com  -> Oracle DBA

-> Oracle Flashback in 10g

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

 

     Copyright (c) 2011-2015  www.in-oracle.com  |  Disclaimer: The views expressed on this web site are my own and do not reflect the views of Oracle Corporation. You may use the information from this site only at your risk.