Oracle 11g flash back archiving Technology

Source: Internet
Author: User

Oracle 11g flash back archiving Technology

Oracle flash back Data Archiving

Oracle flash back data archiving can be used to automatically track and archive data in tables with flash back data archiving enabled. This ensures that the flashback query can obtain the SQL-level access permission for the database object version without the error of too old snapshots.
With the flash back data archiving function, you can track and store all transaction changes to the table throughout the lifecycle of the tracked table. You no longer need to build this smart feature into applications. The flash back data archiving function is very useful for complying with regulations, audit reports, data analysis and decision support systems. The background process of flash back data archiving starts with the database.

Flash back data archiving contains one or more tablespaces. You can have multiple flash back data archives, all of which are configured with a retention time. You should create different flash back data archives according to the retention time requirements. For example, to create a flash back data archive for all records that must be retained for two years, create another flash data archive for all records that must be retained for five years. The database will automatically clear all history information on the first day after the retention period expires.

Oracle 11g Flashback Data Archive (flash back Data archiving)

Oracle Flashback flash back Mechanism

Oracle Flashback database

Flashback table quick recovery of accidentally deleted data

Oracle backup recovery: Flashback flash back

Flash back data archiving process:

1. Create a flashback data archive.
2. Specify the default flash back data archiving.
3. Enable flash back data archiving.
4. view the archived data.

-- Create the Flashback Data Archive

Create flashback archive default fla1 TABLESPACE tbs1 QUOTA 10g retention 5 YEAR;

-- Specify the default Flashback Data Archive
Alter flashback archive fla1 set default;

-- Enable Flashback Data Archive
Alter table inventory flashback archive;

Alter table stock_data flashback archive;

SELECT product_number, product_name, count
FROM inventory
As of timestamp TO_TIMESTAMP ('2017-01-01 00:00:00 ', 'yyyy-MM-DD HH24: MI: ss ');

You can choose to add space:
Alter flashback archive fla1
Add tablespace tbs3 QUOTA 5G;

You can choose to change the retention time:
Alter flashback archive fla1 modify retention 2 YEAR;

You can choose to clear data:
Alter flashback archive fla1 purge before timestamp (SYSTIMESTAMP-INTERVAL '1' day );

You can choose to delete the flashback Data Archiving:
Drop flashback archive fla1;

View flashback Data Archiving:

View name (DBA/USER) Description
* _ FLASHBACK_ARCHIVE: displays information about flash back data archiving.
* _ FLASHBACK_ARCHIVE_TS: display the tablespace that flash back to Data Archiving
* _ FLASHBACK_ARCHIVE_TABLES: displays information about tables with flash back archiving enabled.

You can use the dynamic data dictionary view to view the tracked tables and flash back Data Archive Metadata. To access the USER_FLASHBACK _ * view, you must have the table ownership. To check

DBA_FLASHBACK _ * view. You need the SYSDBA permission.

DDL restrictions on flash back data archiving:

An ORA-55610 error occurs when you execute any of the following DDL statements on a table that has flash back data archiving enabled:

1. Execute the alter table statement for the following operations:
-- Delete, rename, or modify a column
-- Perform partition or subpartition operations
-- Convert LONG columns to LOB Columns
-- Includes the upgrade table clause, with or without the including data clause

2. drop table statement

3. rename table statement

4. truncate table statement

For more details, please continue to read the highlights on the next page:

  • 1
  • 2
  • Next Page

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.