How To Using Flashback Data Archive (Oracle Total Recall)
At first,A Flashback Data Archive provides the ability to track and store transactional changes to a table over its lifetime. A Flashback Data Archive is useful for compliance with record stage policies and audit reports.
A Flashback Data Archive consists of one or more tablespaces or parts thereof. You can have multiple Flashback Data Archives. If you are logged on as SYSDBA, you can specify a default Flashback Data Archive for the system. A Flashback Data Archive is configured with retention time. Data archived in the Flashback Data Archive is retained for the retention time.By default, flashback archiving is off for any table. You can enable flashback archiving for a table if all of these conditions are true:
-
You have the FLASHBACK ARCHIVE object privilege on the Flashback Data Archive to use for that table.
-
The table is neither nested, clustered, temporary, remote, or external.
-
The table contains neither LONG nor nested columns.
-
The table does not use any of these Flashback Data Archive reserved words as column names:
-
STARTSCN
-
ENDSCN
-
RID
-
XID
-
OP
-
OPERATION
After flashback archiving is enabled for a table, you can disable it only if you either have the FLASHBACK ARCHIVE ADMINISTER system privilege or you are logged on as SYSDBA.When choosing a Flashback Data Archive for a specific table, consider the data retention requirements for the table and the retention times of the Flashback Data Archives on which you have the FLASHBACK ARCHIVE object privilege.
-
Most of the flashback functionalities rely on UNDO data which will error out with an ORA-1555 snapshot too old if the required before image cannot be read any more. ORA-1555 occurs because it has already been overwritten in the undo tablespace. Furthermore, it is very unlikely that the old values can be reconstructed over a longer period of time, like months or even years, from data stored in the undo segments.
Luckily, this UNDO data time issue can now be remedied. With the flashback data archive functionality of 11g the Oracle database is capable of automatically tracking transactional changes to data over very long periods. Oracle achieves this by storing UNDO information in special segments within dedicated tablespaces.
The usage of this feature is completely transparent for the application and the end user, who can view historical data from the flashback archive seamlessly with regular SQL statements. This is done by utilizing traditional flashback functionalities, such as flashback query, flashback versions query, flashback transaction query, etc.
The historical information in the flashback data archive ages out automatically and Oracle automatically purges it after the specified retention period has exceeded. However, the flashback data archive provides the DBA with a central interface for the management of historical data and change tracking.
% With a flashback data archive it is possible to view data as any point in time since the flashback data archive was created. However, attempting to view data as a timestamp before the data archive is created causes the following error: ORA-01466: unable to read data - table definition has changed
The technology behind flashback data archive
With every Oracle 11g database startup, the new flashback data archiver background process, FBDA, is automatically started. This is what generates and archives the historical data.
Transactions encounter very little performance impact from flashback data archiving because Oracle only marks DML operations as candidates for archiving. A special background process then generates and archives the history information asynchronously for tables enabled for flashback archival.
SQL> SELECT PNAME
2 FROM V$PROCESS
3 WHERE PNAME IS NOT NULL
4 ORDER BY PNAME;
...........
FBDA
[root@ORACLERAC2 ~]# ps -ef|grep fbda
oracle 1719 1 0 11:03 ? 00:00:00 ora_fbda_PROD1
root 25475 25409 0 17:47 pts/4 00:00:00 grep fbda
[root@ORACLERAC2 ~]#
...........
SQL> conn / as sysdba;
Connected.
SQL> create tablespace fratas datafile '/u01/app/oracle/oradata/PROD1/fratbs.dbf' size 50m;
Tablespace created.
SQL> create user fr_admin identified by fr_admin default tablespace fratas;
User created.
SQL>
SQL>
SQL> grant connect,resource to fr_admin;
Grant succeeded.
SQL> grant flashback archive administer to fr_admin;
Grant succeeded.
SQL> select PRIVILEGE from dba_sys_privs where PRIVILEGE like '%FLASHBACK%';
PRIVILEGE
----------------------------------------
FLASHBACK ANY TABLE
FLASHBACK ANY TABLE
FLASHBACK ARCHIVE ADMINISTER
FLASHBACK ARCHIVE ADMINISTER
FLASHBACK ARCHIVE ADMINISTER
FLASHBACK ANY TABLE
6 rows selected.
SQL> conn fr_admin/fr_admin
Connected.
SQL> CREATE FLASHBACK ARCHIVE fra1 tablespace fratas RETENTION 1 YEAR;
Flashback archive created.
SQL> desc dba_flashback_archive;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER_NAME VARCHAR2(30)
FLASHBACK_ARCHIVE_NAME NOT NULL VARCHAR2(255)
FLASHBACK_ARCHIVE# NOT NULL NUMBER
RETENTION_IN_DAYS NOT NULL NUMBER
CREATE_TIME TIMESTAMP(9)
LAST_PURGE_TIME TIMESTAMP(9)
STATUS VARCHAR2(7)
SQL> select OWNER_NAME,FLASHBACK_ARCHIVE_NAME,RETENTION_IN_DAYS from dba_flashback_archive where owner_name='FR_ADMIN';
OWNER_NAME
------------------------------
FLASHBACK_ARCHIVE_NAME
--------------------------------------------------------------------------------
RETENTION_IN_DAYS
-----------------
FR_ADMIN
FRA1
365
SQL> sho user;
USER is "FR_ADMIN"
SQL> grant flashback archive on fra1 to sh;
Grant succeeded.
SQL> conn sh/sh
Connected.
SQL> alter table promotions flashback archive fra1;
SQL> conn / as sysdba;
Connected.
SQL> select * from dba_flashback_archive_tables;
TABLE_NAME OWNER_NAME
------------------------------ ------------------------------
FLASHBACK_ARCHIVE_NAME
--------------------------------------------------------------------------------
ARCHIVE_TABLE_NAME STATUS
----------------------------------------------------- -------------
PROMOTIONS SH
FRA1
SYS_FBA_HIST_88064 ENABLED
SQL> set autot trace exp
SQL> select * from sh.promotions versions between timestamp (systimestamp-interval '20' minute) and (systimestamp-interval '1' minute);
Execution Plan
----------------------------------------------------------
Plan hash value: 4106015420
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 503 | 49294 | 58 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| PROMOTIONS | 503 | 49294 | 58 (0)| 00:00:01 |
--------------------------------------------------------------------------------
SQL> ALTER TABLE sh.promotions NO FLASHBACK ARCHIVE;
Table altered.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/13750068/viewspace-2144053/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 【FLASHBACK】Oracle flashback data archive 介紹OracleHive
- Flashback Data Archive原理詳解Hive
- How To Replicate Data from Oracle to Postgres Using GoldenGate (Doc ID 1544137.1OracleGo
- Step by Step Data Replication Using Oracle GoldenGateOracleGo
- Oracle 12.2 How to Generate AWRs in Active Data Guard Standby DatabasesOracleDatabase
- How to get the description of blast hit using blastdbcmd?AST
- How to develop locally a Laravel app using LaragondevLaravelAPPGo
- ORACLE Flashback Query偽列Oracle
- How boltdb Write its Data?
- oracle 10g flashback databaseOracle 10gDatabase
- Oracle 備份恢復之 FlashbackOracle
- How to create the Gold gold using RGB color values All In OneGo
- PostgreSQL DBA(181) - Using PostgreSQL as a Data WarehouseSQL
- Oracle閃回技術--Flashback Version QueryOracle
- 【譯】Using .NET for Apache Spark to Analyze Log DataApacheSpark
- How Oracle Store Number internal(zt)Oracle
- how BabyFile app transfer files with Windows PC though data cableAPPWindows
- How to Install EMC PowerPath on Oracle VM 3.4Oracle
- 在Oracle DG Standby庫上啟用flashback database功能OracleDatabase
- ORACLE 閃回檢視v$flashback_database_log/statOracleDatabase
- Oracle data link建立Oracle
- Converting Oracle Database from Linux to Windows using RMANOracleDatabaseLinuxWindows
- [20201110]How to get the Data Guard broker configuration from a SQL query.txtSQL
- 4.1.6 Oracle Restart 與 Oracle Data Guard 整合OracleREST
- Oracle Data Guard Broker元件Oracle元件
- Oracle Data Guard簡介Oracle
- How To Deal With Split Brain In Oracle 19c RacAIOracle
- G009-ORACLE-ASK Using In-Database ArchivingOracleDatabase
- Oracle 12.2新特性: PDB級閃回資料庫(Flashback PDB)Oracle資料庫
- 【FLASHBACK】Oracle閃回及回收站相關語句參考Oracle
- 論文學習筆記 - Classifification of Hyperspectral and LiDAR Data Using Coupled CNNs筆記CNN
- 1 關於 Oracle Data GuardOracle
- 2 Oracle Data Guard 安裝Oracle
- 1 Oracle Data Guard Broker 概念Oracle
- Oracle Data Guard和Broker概述Oracle
- 【Flashback】Flashback Drop閃回刪除功能實驗
- Oracle資料泵(Oracle Data Pump) 19cOracle
- 【ASK_ORACLE】Oracle Data Guard(一)DG架構Oracle架構
- Flashback Query(轉)