Oracle 11g Flash-back file technology

Flash Back Data archive

Use the Flashback data archiving feature to automatically track and archive data in tables with a Flashback data archive enabled. This ensures that the flashback query is able to gain access to the database object version

SQL-level access without the error that the snapshot is too old.

With the Flashback data archiving feature, all transactional changes to the table can be tracked and stored throughout the lifetime of the tracked table. It is no longer necessary to put this smart feature inside

to the application. The Flashback data archiving feature is useful for compliance, audit reporting, data analysis, and decision support systems. Flash back data archive background process with number

Start with the library.

The Flashback data archive contains one or more table spaces. You can have multiple Flashback data archives, which are configured with retention time. Depending on the retention time requirements, you should create different

Flash data archive, for example, to create a flashback data archive for all records that must be retained for two years, and another flashback for all records that must be retained for five years

File. The database automatically clears all history information on the first day after the retention period expires.

The process of flash back data archiving:

1. Create a flashback data archive.
2. Specify the default flash-back data archive.
3. Enable the Flashback data archive.
4. View the Flashback data archive 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 (' 2007-01-01 00:00:00 ', ' yyyy-mm-dd HH24:MI:SS ');

You can choose to add more 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 the data:
ALTER FLASHBACK ARCHIVE fla1 PURGE before TIMESTAMP (systimestamp-interval ' 1 ' Day);

You can choose to delete the Flashback data archive:
DROP FLASHBACK ARCHIVE fla1;

To view the Flashback data archive:

View name (dba/user) description
*_flashback_archive displays information about the Flashback data archive
*_flashback_archive_ts Show the tablespace of the Flashback data archive
*_flashback_archive_tables display information about a table with a flashback file enabled

You can use the Dynamic Data dictionary view to view tracked tables and flashback data Archive metadata. To access the user_flashback_* view, you must have ownership of the table. To check

Dba_flashback_* view, you need SYSDBA permissions.

DDL limits for Flashback data archiving:

Executing any of the following DDL statements against a table with a flashback data archive enabled will produce a ORA-55610 error:

1. ALTER TABLE statement that performs the following actions:
--Delete, rename, or modify a column
--Perform a partition or sub-partition operation
--Convert a LONG column to a LOB column
--Includes UPGRADE TABLE clause with or without including DATA clause

2.DROP TABLE Statement

3.RENAME TABLE Statement

4.TRUNCATE TABLE Statement
Experiment: Flash back file

1. Create test data:
Create tablespace arch_tbs datafile '/u01/app/oracle/oradata/prod/arch_tbs.dbf ' size 100m autoextend on

MaxSize 1G;

2. Create a test user and authorize:
Create user archive_admin identified by archive_admin default Tablespace Arch_tbs;

Grant Dba,flashback archive administer to archive_admin;

3. Create a flashback file (the SYS user is required to set the default flashback file)
Conn Archive_admin/archive_admin

Create Flashback archive fda1 tablespace Arch_tbs Quota 10m retention 1 year;

Alter FLASHBACK archive FDB1 set default;
--sys users under

Create Flashback archive Default FDA1 tablespace Arch_tbs quota 10m retention 1 year;
--or set the default flashback file (SYS user) directly

4. Set the flash regression file for the table
ALTER TABLE TEST_USER1.EMP Flashback archive;

5. Verification
Conn Test_user1/test

Desc Dba_flashback_archive_tables;

Select Table_name,archive_table_name from Dba_flashback_archive_tables;
--View the history tracking table related to the current set of flash regression files

Select Owner,table_name,tablespace_name from Dba_tables where table_name= ' sys_fba_hist_88707 ';
--Determine the name, owner, and location of the history table

Insert, update, and delete operations on the EMP table, and then view the history trace table contents:

SELECT * from EMP;

[email protected]>begin
2 for I in 5..10
3 loop
4 INSERT INTO EMP values (i, ' TEST ', sysdate+i);
5 end Loop;
6 end;
7/

PL/SQL procedure successfully completed.


Delete from emp ...

Update emp ...

SELECT * from sys_fba_hist_88707;

Select COUNT (*) from sys_fba_hist_88707;
--View historical tracking table contents;

DDL Operations (DDL statements can be used when raw data should not be table)

ALTER TABLE EMP add DD number;

Select COUNT (*) from sys_fba_hist_88707;

ALTER TABLE EMP Modify name VARCHAR2 (30);

6. Turn off the Flash return file
Conn Archive_admin/archive_admin

ALTER TABLE TEST_USER1.EMP no flashback archive;

drop table test_user1.emp;

Desc dbms_flashback_archive;

7. Disassociate and rebuild the table from the Historical tracking table (and step 6 is independent of each other)

exec dbms_flashback_archive.disassociate_fba (' test_tser1 ', ' EMP ');

exec dbms_flashback_archive.reassociate_fba (' test_tser1 ', ' EMP ');

-When some DDL statements cannot be executed, they need to be canceled and rebuilt;

8. Flash Back File
SELECT * from Test_user1.emp as of timestamp (timestamp-interval ' minute);

Oracle 11g Flash-back file technology