|
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;
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
| 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
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
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
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
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
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.
More information
about this subject (
Oracle Flashback (10g) ) you can get from
www.in-oracle.com
Oracle Flashback Technology is a group of Oracle Database features that
that let you view past states of database objects or to return database
objects to a previous state without using point-in-time media recovery.
With flashback features, you can do the following:
- Perform queries that return past data
- Perform queries that return metadata that shows a detailed history of
changes to the database
- Recover tables or rows to a previous point in time
- Automatically track and archive transactional data changes
- Roll back a transaction and its dependent transactions while the
database remains online
Oracle Oracle Flashback features use the Automatic Undo Management (AUM)
system to obtain metadata and historical data for transactions. They
rely on undo data, which are records of the effects of individual
transactions. For example, if a user executes an UPDATE statement to
change a salary from 1000 to 1100, then Oracle Database stores the value
1000 in the undo data.
Undo data is persistent and survives a database shutdown. By using
flashback features, you can use undo data to query past data or recover
from logical damage. Besides using it in flashback features, Oracle
Database uses undo data to perform the following actions:
- Roll back active transactions
- Recover terminated transactions by using database or process recovery
- Provide read consistency for SQL queries
More information on this subject you can get from
here.
|