Oracle Flash Back

Oracle Flashback: Automatic disk-based backup and recovery that restores a table to a point in time or SCN.

1. View the Flash back zone

Sql> Show parameter db_recovery_file_dest; View the details of the Flash back zone

Sql> select flashback_on from V$database; Check to see if the flashback zone is turned on

2. Change the Flashback function

sql> startup Mount; Database Boot to mount

Sql> alterdatabase Flashback on (off); Turn off the flash back zone

Note: If you encounter a 38706 event error, it may be due to an incorrect archive. Enable archiving first.

sql> ALTER DATABASE Archivelog;

Sql> alter Systemset db_flashback_retention_target=2880;

To modify the flashback time, the default is 1440 minutes (1 days), and the flashback time is the time that the database can be rolled back.

3. Flashback Use Example: (Note: The SYS user is not available for Flash back)

3.1 Flash-back drop table

Creating indexes on tables and tables under non-SYS users, inserting related data

CREATE TABLE ABC (Fnumber (9));

CREATE INDEX Idx_teston ABC (f);

INSERT INTO ABC values (1);

INSERT INTO ABC values (2);

INSERT INTO ABC values (3);

3.2 Deleting a table

DROP TABLE ABC;

3.3 After you delete a table, the table is not actually deleted, but it is renamed and placed in the Recycle Bin under the same user, and the indexes on the table are treated as such. The Recycle Bin is fully managed by the FIFO principle.

View table statements select* from tab;

View index Statements Select Index_name, INDEX_TYPE, table_name from IND;

View Recycle Bin statement show RecycleBin

Emptying the Recycle Bin statement PURGE RecycleBin;

Note: Tables that are not put into the Recycle Bin cannot be flashed back

3.4 After the flashback, you can see the table again, but the corresponding index after the table flash back needs to be recompiled.

Flashback table ABC to before drop;

3.5 Flash back DML statements

Flashback table ABC to timestamp to_timestamp (' 2014-02-14 16:00:00 ', ' yyyy-mm-dd hh24:mi:ss ')

Flashback may encounter a 08189 error, execute the following statement

ALTER TABLE test varchar enable row movement;

This means that Oracle is allowed to modify ROWID. When Oracle inserts data, it assigns a unique rowid to the data and does not change, to enable the flashback function must turn on the modifiable rowID function

Flashback DML data is recovered using rollback segments

Attached: query all actions that can be undone, which depend on undo_retention

SELECT Versions_xidas XID,

VERSIONS_STARTSCN,

VERSIONS_ENDSCN,

Versions_operation

From scott.abcversions between SCN MinValue and MaxValue

Query Undo_retention Parameters

This parameter holds the seconds that the rollback segment data was saved and cannot be flashed back beyond that time. Default is 15 minutes

Modify the parameter alter system set UNDO_RETENTION=3600;

This article from "Flower of Xu's blog" blog, declined reproduced!

Oracle Flash Back