Introduction to Oracle 11g flash back Data Archiving

The following articles mainly introduce the Oracle 11g flash back data archiving. The Oracle 11g flash back data archiving function can archive UNDO data in practice. The following describes the relevant content. I hope you will gain some benefits after browsing.

Although ORA-01555 errors can be avoided and reduced through various means, but with the passage of time, the UNDO information will always be lost, then can save the information, so that the database can be traced back within a certain period of history, so that we can see a data table segment at any historical point in time?

Starting from Oracle Database 11g, Oracle provides the following function: flash back to Data Archiving Flashback Data Archive ). With this feature, Oracle databases can archive UNDO data to provide comprehensive historical data queries. Therefore, Oracle introduces a new concept, Oracle Total Recall, that is, Oracle comprehensive Recall.

Flash back data archiving can be compared with the log archiving that we have been familiar with. The log archiving records the Historical Status of Redo, which is used to ensure the continuity of recovery. Flash back archive records the Historical Status of UNDO, it can be used to perform flash tracing query on data. The background process LGWR is used to write Redo information to the log file, and the ARCH process is responsible for archiving logs. in Oracle 11g, the new background Process FBDAFlashback Data Archiver Process is used to archive and write the flash Data:

 
 

  
  
[oracle@sp3: ~]$ps -ef | grep fbda | grep -v grep  
  
  
oracle 3251 1 0 Jan07 ? 00:00:11 ora_fbda_ccdb  
 
 

Flash back archive data can even be saved on a yearly basis. Oracle 11 GB can reduce space consumption through internal partitioning and compression algorithms. This feature is especially useful for environments that require auditing and historical data partitioning, however, for busy database environments, flash back data storage obviously consumes more storage space. Of course, you can flash back data archiving for some tables as needed to meet specific business needs.

Because flash back data archiving requires independent storage, you must create an independent ASSM automatic segment space management before using this feature.) tablespace:

 
 

  
  
sys@TQGZS11G> create tablespace fbda datafile 
'/oracle/oradata/tqgzs11g/FBDA.dbf' size 200M
 segment space management auto;  
  
  
Tablespace created.  
 
 

Then, you can create a FLASHBACK data ARCHIVE area based on the tablespace. The flashback archive administer system permission is required to create a FLASHBACK data ARCHIVE, which is performed by the SYS user here:

 
 

  
  

sys@TQGZS11G> create flashback archive fda tablespace fbda retention 1 month;  
  
  
Flashback archive created.  
 
 

This archive area can be used to record the data volume of the data table. To facilitate testing, change the UNDO tablespace to a smaller one so that the UNDO data can be aged as soon as possible:

 
 

  
  
sys@TQGZS11G> create undo tablespace UNDOTBS2_SMALL datafile '/oracle/oradata/tqgzs11g/UNDOTBS2_SMALL.dbf' size 20M autoextend off;  
  
  
Tablespace created.  
  
  
sys@TQGZS11G> alter system set undo_tablespace= UNDOTBS2_SMALL;  
  
  
System altered.  
  
  
sys@TQGZS11G> show parameter undo  
  
  
NAME TYPE VALUE  
  
  
undo_management string AUTO  
  
  
undo_retention integer 900  
  
  
undo_tablespace string UNDOTBS2_SMALL  
 
 

Next, use the test user connection to perform the flash back ARCHIVE setting for the test table. The flashback archive object permission is required to enable the historical data tracing:

 
 

  
  
sys@TQGZS11G> conn tq/tq  
  
  
Connected.  
  
  
tq@TQGZS11G> select TABLE_NAME from user_tables;  
  
  
TABLE_NAME  
  
  
T  
  
  
EMP  
  
  
tq@TQGZS11G> alter table t flashback archive fda;  
  
  
Table altered.  
 
 

To cancel the flash archive of a data table, run the following command:

 
 

  
  
alter table table_name no flashback archive; 
 
 

The above content is the description of Oracle 11g flash back data archiving, hope to bring you some help in this regard.