11g feature: Flashback Data Archive Guide. (Doc ID 470199.1)
文章簡單介紹了FDA,如何配置和使用FDA
Purpose
What is FDA (Flashback Data Archive) ?
How to enable Flashback Data Archive features?
What is the role of the new background process FBDA?
What is the Flashback Data Archive Workflow an Example?
How to configure a Default Flashback Data Archive as Example?
Scope
This feature can be used by DBA's and Support Analysts to understand and
configure Flashback Data Archive
Details
FDA的中文可以翻譯為閃回資料歸檔
What is FDA (Flashback Data Archive) ?
A new database object, a flashback data archive is a logical container for storing historical information. It is stored in one or more tablespaces and tracks the history for one or more tables.
FDA,一個新的資料庫物件,是一個用來儲存歷史資料資訊的邏輯容器。FDA儲存在一個或者多個表空間中,可以跟蹤多個錶的歷史資料,儲存一定的時限。
You specify retention duration for each flashback data archive (could be # of years).
SQL> create flashback archive fla1 tablespace tbs1 retention 2 year;
---上述SQL建立一個 FDA物件fla1,使用表空間tbs1 來儲存歷史資料,資料保留2年
With the "Oracle Total Recall" option, Oracle database 11g has been specifically enhanced to track history with minimal performance impact and to store historical data in compressed form to minimize storage requirements, completely transparent to applications, easy to setup . This efficiency cannot be duplicated by your own triggers, which also cost time and effort to set up and maintain.
To satisfy long-retention requirements that exceed the undo retention, create a flashback data archive.
How to enable Flashback Data Archive features?
To satisfy long-retention requirements, use flashback data archives. Begin with the following steps:
# For your archive administrator:
1:Create one or more tablespaces for data archives and grant QUOTA on the tablespaces.
2: Grant the FLASHBACK ARCHIVE ADMINISTER system privilege to create and maintain flashback archives.
# For archive users:
Grant the FLASHBACK ARCHIVE object privilege (to enable history tracking for specific tables in the given flashback archives).
>SCOTT (Archive user)
SQL> alter table emp flashback archive fla1;
alter table emp flashback archive fla1
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive
As SYS use or Archive Administrator (who granted FLASHBACK ARCHIVE ADMINISTER system privilege)
SQL> grant flashback archive on fla1 to scott;
Grant succeeded
Note:
Grant the FLASHBACK ARCHIVE ADMINISTER system privilege to your archive administrator,to allow execution of the following statements:
- CREATE FLASHBACK ARCHIVE
- ALTER FLASHBACK ARCHIVE
- DROP FLASHBACK ARCHIVE
What is the role of the new background process FBDA?
The Flashback Data Archive background process, FBDA, starts with the database.
1. FBDA operates first on the undo in the buffer cache.
2. In case the undo has already left the buffer cache, FBDA could also read the required values from the undo segments.
3. FBDA consolidates the modified rows of flashback archive???enabled tables and writes them into the appropriate history tables, which make up the flashback data
archive.
You can find the internally assigned names of the history tables by querying the *_FLASHBACK_ARCHIVE_TABLES view. History tables are compressed and internally partitioned. The database automatically purges all historical information on the day after the retention period expires. (It deletes the data, but does not destroy the flashback data archive.) For example, if the retention period is 10 days, then every day after the tenth day, the oldest information is deleted; thus
leaving only 10 days of information in the archive. This is a way to implement digital shredding.
Waht is the Flashback Data Archive Workflow an Exmaple?
1. Create the flashback data archive:
The archive administrator creates a flashback data archive named fla1, which uses up to unlimited of the tbs1 tablespace and whose data will be retained for two years. If you want to set up your database so that information in the fla1 is automatically deleted, the day after 2 years is complete.
SQL> create flashback archive fla1 tablespace tbs1 retention 2 year; << data will be retained for 2 years.!
Flashback archive created.
2- Grant Flashback Archive objects privilege on Flashback data archive (fla1) to Archive User (SCOTT).
SQL> grant flashback archive on fla1 to Scott;
Grant succeeded
3- Enable history tracking for a table in the FLA1 archive:
????As Flashback Archive User (SCOTT)
SQL> alter table EMP flashback archive fla1;
Table altered.
4- View the historical data:
SQL> select to_timestamp(sysdate) from dual;
TO_TIMESTAMP(SYSDATE)
---------------------------------------------------------------------------
02-DEC-07 12.00.00 AM
SQL> alter table EMP flashback archive fla1;
Table altered.
SQL> update emp set sal=sal+100 where empno=7788;
1 row updated.
SQL> select sal from emp as of timestamp to_timestamp('2007-12-02 00:00:00','yyyy-mm-dd hh24:mi:ss') 3 where empno=7788;
SAL
----------
3100
SQL> select sal from emp where empno=7788;
SAL
----------
3200
Note:
To use the flashback_archive_clause to enable/disable historical tracking for the table, you must have the FLASHBACK ARCHIVE ADMINISTER system privilege or you must be logged in as SYSDBA.
Flashback archiving is handled by fbda background process, and it checks for tablespace quota every 1 hour. If you run out of flashback archival space and must extend the tablespace you may also need to reset the quota for the tablespace.
總結下步驟如下:
---step1 create tablespace for FDA, this is optional,but recommened
create tablespace fdatbs
datafile '/u01/app/oradata/ORCL/flashbackarchive.dbf'
size 100M autoextend on maxsize 1G;
---step2 create FDA
create flashback archive fdone
tablespace fdatbs quota 900M retention 2 year;
--step3 grant privilege to user ANDREW
grant flashback archive on fdone to ANDREW;
--step4 enable track table emp
alter table andrew.emp flashback archive fdone;
--step5 do-some-DML to emp and then view historical data
begin
update andrew.emp e set e.sal=20000 where e.ename='KING';
commit;
end;
/
select empno,ename,job,mgr,hiredate,sal,comm,deptno
from andrew.emp
as of timestamp systimestamp-interval '10' minute
where ename='KING';
Purpose
What is FDA (Flashback Data Archive) ?
How to enable Flashback Data Archive features?
What is the role of the new background process FBDA?
What is the Flashback Data Archive Workflow an Example?
How to configure a Default Flashback Data Archive as Example?
Scope
This feature can be used by DBA's and Support Analysts to understand and
configure Flashback Data Archive
Details
FDA的中文可以翻譯為閃回資料歸檔
What is FDA (Flashback Data Archive) ?
A new database object, a flashback data archive is a logical container for storing historical information. It is stored in one or more tablespaces and tracks the history for one or more tables.
FDA,一個新的資料庫物件,是一個用來儲存歷史資料資訊的邏輯容器。FDA儲存在一個或者多個表空間中,可以跟蹤多個錶的歷史資料,儲存一定的時限。
You specify retention duration for each flashback data archive (could be # of years).
SQL> create flashback archive fla1 tablespace tbs1 retention 2 year;
---上述SQL建立一個 FDA物件fla1,使用表空間tbs1 來儲存歷史資料,資料保留2年
With the "Oracle Total Recall" option, Oracle database 11g has been specifically enhanced to track history with minimal performance impact and to store historical data in compressed form to minimize storage requirements, completely transparent to applications, easy to setup . This efficiency cannot be duplicated by your own triggers, which also cost time and effort to set up and maintain.
To satisfy long-retention requirements that exceed the undo retention, create a flashback data archive.
How to enable Flashback Data Archive features?
To satisfy long-retention requirements, use flashback data archives. Begin with the following steps:
# For your archive administrator:
1:Create one or more tablespaces for data archives and grant QUOTA on the tablespaces.
2: Grant the FLASHBACK ARCHIVE ADMINISTER system privilege to create and maintain flashback archives.
# For archive users:
Grant the FLASHBACK ARCHIVE object privilege (to enable history tracking for specific tables in the given flashback archives).
>SCOTT (Archive user)
SQL> alter table emp flashback archive fla1;
alter table emp flashback archive fla1
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive
As SYS use or Archive Administrator (who granted FLASHBACK ARCHIVE ADMINISTER system privilege)
SQL> grant flashback archive on fla1 to scott;
Grant succeeded
Note:
Grant the FLASHBACK ARCHIVE ADMINISTER system privilege to your archive administrator,to allow execution of the following statements:
- CREATE FLASHBACK ARCHIVE
- ALTER FLASHBACK ARCHIVE
- DROP FLASHBACK ARCHIVE
What is the role of the new background process FBDA?
The Flashback Data Archive background process, FBDA, starts with the database.
1. FBDA operates first on the undo in the buffer cache.
2. In case the undo has already left the buffer cache, FBDA could also read the required values from the undo segments.
3. FBDA consolidates the modified rows of flashback archive???enabled tables and writes them into the appropriate history tables, which make up the flashback data
archive.
You can find the internally assigned names of the history tables by querying the *_FLASHBACK_ARCHIVE_TABLES view. History tables are compressed and internally partitioned. The database automatically purges all historical information on the day after the retention period expires. (It deletes the data, but does not destroy the flashback data archive.) For example, if the retention period is 10 days, then every day after the tenth day, the oldest information is deleted; thus
leaving only 10 days of information in the archive. This is a way to implement digital shredding.
Waht is the Flashback Data Archive Workflow an Exmaple?
1. Create the flashback data archive:
The archive administrator creates a flashback data archive named fla1, which uses up to unlimited of the tbs1 tablespace and whose data will be retained for two years. If you want to set up your database so that information in the fla1 is automatically deleted, the day after 2 years is complete.
SQL> create flashback archive fla1 tablespace tbs1 retention 2 year; << data will be retained for 2 years.!
Flashback archive created.
2- Grant Flashback Archive objects privilege on Flashback data archive (fla1) to Archive User (SCOTT).
SQL> grant flashback archive on fla1 to Scott;
Grant succeeded
3- Enable history tracking for a table in the FLA1 archive:
????As Flashback Archive User (SCOTT)
SQL> alter table EMP flashback archive fla1;
Table altered.
4- View the historical data:
SQL> select to_timestamp(sysdate) from dual;
TO_TIMESTAMP(SYSDATE)
---------------------------------------------------------------------------
02-DEC-07 12.00.00 AM
SQL> alter table EMP flashback archive fla1;
Table altered.
SQL> update emp set sal=sal+100 where empno=7788;
1 row updated.
SQL> select sal from emp as of timestamp to_timestamp('2007-12-02 00:00:00','yyyy-mm-dd hh24:mi:ss') 3 where empno=7788;
SAL
----------
3100
SQL> select sal from emp where empno=7788;
SAL
----------
3200
Note:
To use the flashback_archive_clause to enable/disable historical tracking for the table, you must have the FLASHBACK ARCHIVE ADMINISTER system privilege or you must be logged in as SYSDBA.
Flashback archiving is handled by fbda background process, and it checks for tablespace quota every 1 hour. If you run out of flashback archival space and must extend the tablespace you may also need to reset the quota for the tablespace.
總結下步驟如下:
---step1 create tablespace for FDA, this is optional,but recommened
create tablespace fdatbs
datafile '/u01/app/oradata/ORCL/flashbackarchive.dbf'
size 100M autoextend on maxsize 1G;
---step2 create FDA
create flashback archive fdone
tablespace fdatbs quota 900M retention 2 year;
--step3 grant privilege to user ANDREW
grant flashback archive on fdone to ANDREW;
--step4 enable track table emp
alter table andrew.emp flashback archive fdone;
--step5 do-some-DML to emp and then view historical data
begin
update andrew.emp e set e.sal=20000 where e.ename='KING';
commit;
end;
/
select empno,ename,job,mgr,hiredate,sal,comm,deptno
from andrew.emp
as of timestamp systimestamp-interval '10' minute
where ename='KING';
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/21754115/viewspace-1179133/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 11G Flashback Data Archive新特性的研究Hive
- Oracle Flashback Data ArchiveOracleHive
- Flashback Data Archive RequirementsHiveUIREM
- 【FLASHBACK】Oracle flashback data archive 介紹OracleHive
- oracle 11g 新特性 Flashback Data Archive 說明OracleHive
- Flashback Data Archive原理詳解Hive
- Flashback Data Archive (Oracle Total Recall)HiveOracle
- oracle11g flashback archive feature新特性OracleHive
- ORACLE 11G FLASHBACK FEATUREOracle
- 11G新特性:FLASHBACK ARCHIVEHive
- 11g New Feature: Health monitor (Doc ID 466920.1)
- 11G flashback data archive 導致產生大量歸檔日誌Hive
- 11G新特性:FLASHBACK ARCHIVE - 01Hive
- 11G新特性:FLASHBACK ARCHIVE – 02Hive
- Oracle 11g新特性--閃回資料歸檔(flashback data archive)[zt]OracleHive
- How To Using Flashback Data Archive (Oracle Total Recall)HiveOracle
- 閃回資料歸檔-- Flashback Data ArchiveHive
- Playing with Flashback Data Archive on 11GR2Hive
- Oracle OCP IZ0-053 Q166(Flashback Data Archive)OracleHive
- Oracle OCP 1Z0-053 Q672(Flashback Data Archive)OracleHive
- Oracle OCP 1Z0 053 Q143(Flashback Data Archive)OracleHive
- flashback drop/query/table/database/archiveDatabaseHive
- RMAN 'Duplicate Database' Feature in 11G [ID 452868.1]Database
- Managing multiple archive log destinations with RMAN (Doc ID 443814.1)Hive
- Does Goldengate Support The New Oracle 10g Flashback Feature? [ID 966212.1]GoOracle 10g
- Data Guard Broker High Availability (Doc ID 275977.1)AI
- Oracle Flashback Archive——Oracle閃迴歸檔(上)OracleHive
- Oracle Flashback Archive——Oracle閃迴歸檔(中)OracleHive
- Oracle Flashback Archive——Oracle閃迴歸檔(下)OracleHive
- Oracle Database Change Data Capture featureOracleDatabaseAPT
- Oracle Data Server Interim Patch Installation (OPatch) (Doc ID 189489.1)OracleServer
- flashback database 結合 data guardDatabase
- Oracle10g New Feature -- 4. Flashback DatabaseOracleDatabase
- Oracle10g New Feature -- 3.Flashback TableOracle
- Oracle10g New Feature -- 2.Flashback RecordsOracle
- 11g New Feature: Health monitor
- Top six reasons to archive your SAP data:Hive
- Data guard archive GAP 故障處理案例Hive