Simple Test of Oracle 11g flash back Archiving

Simple Test of Oracle 11g flash back Archiving

The new features in Oracle 11g flash back archive provide a possibility for custom query of historical data. After learning this feature, I found that it is clear and easy to use. If used properly, it is still a good feature.
Let's simulate an example.
First, create an independent tablespace. The attribute must be segment space management auto. Because the default attribute is the same, we simplify the statement.
SQL> create tablespace fbarch datafile '/U01/app/oracle/oradata/actvdb/fbarch. dbf' size 10 M;
Tablespace created.
Then we create a flashback data archive area. You can specify multiple tablespaces or change the retention period.
SQL> create flashback archive fda tablespace fbarch retention 1 month;
Flashback archive created.
Create a test table
SQL> create table test_fbarch as select * from dba_objects;
Table created.
Then modify the table attributes and specify the flash back archive area. Of course, this area does not correspond to the flash back data archive area.
SQL> alter table test_fbarch flashback archive fbarch;
Alter table test_fbarch flashback archive fbarch
*
ERROR at line 1:
ORA-55605: Incorrect Flashback Archive is specified
We deleted the original flash back data archive and re-created one.
SQL> drop flashback archive fda;
Flashback archive dropped.
Re-create the flash back data archive Area
SQL> create flashback archive fbarch tablespace fbarch retention 1 month;
Flashback archive created.
Modify the table attribute again and specify it as fbarch.
SQL> alter table test_fbarch flashback archive fbarch;
Table altered.
You can use the flash back function to find historical data, which can be based on Timestamp or SCN.
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
380440310
Assume that the query is based on SCN
SQL> select count (*) from test_fbarch as of scn 380440310;
COUNT (*)
----------
86840
Here, we intentionally want to refresh the undo data space and greatly increase the frequency of data changes.
Use the following pl/SQL to fl undo
SQL> begin
2 for I in 1 .. 100 loop
3 update test_fbarch set object_id = object_id + 1;
4 commit;
5 end loop;
6 end;
7/
Of course, at this time, statements will be continuously executed in the background to refresh the data. Of course, the original flash back to the Data Archiving area is definitely not enough.
The following error is displayed in the alert Log.
Wed Apr 13 22:43:23 2016
ORA-1688: unable to extend table CYDBA. sys_fba_hist_2000035 partition HIGH_PART by 1024 in tablespace FBARCH
Of course, this error does not seem very clear. Let's analyze it briefly.
SQL> col segment_name format a30
SQL> select segment_name, segment_type from user_segments;
SEGMENT_NAME SEGMENT_TYPE
------------------------------------------------
TEST_FBARCH TABLE
Sys_fba_ddl_colmap_2000035 TABLE
Sys_fba_tcrv_2000035 TABLE
Sys_fba_hist_2000035 TABLE PARTITION
Sys_fba_tcrv_idx_2000035 INDEX
We can see that there are four more segment objects, three are the background data table, and the other is the index.
You can also guess the implementation principles of flash back archiving Based on the field information.
SQL> desc sys_fba_tcrv_2000035 -- this table records Records for specific operations (OP) within a specified time range.
Name Null? Type
-----------------------------------------------------------------------------
RID VARCHAR2 (4000)
STARTSCN NUMBER
ENDSCN NUMBER
Xid raw (8)
OP VARCHAR2 (1)

SQL> desc sys_fba_hist_2000035 -- this table records the details of data changes within a specified time range. You can see all fields in the original table.
Name Null? Type
-----------------------------------------------------------------------------
RID VARCHAR2 (4000)
STARTSCN NUMBER
ENDSCN NUMBER
Xid raw (8)
OPERATION VARCHAR2 (1)
OWNER VARCHAR2 (30)
OBJECT_NAME VARCHAR2 (128)
SUBOBJECT_NAME VARCHAR2 (30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2 (19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2 (19)
STATUS VARCHAR2 (7)
TEMPORARY VARCHAR2 (1)
GENERATED VARCHAR2 (1)
SECONDARY VARCHAR2 (1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2 (30)

SQL> desc sys_fba_ddl_colmap_2000035 -- this table records the DDL related records within the specified time range.
Name Null? Type
-----------------------------------------------------------------------------
STARTSCN NUMBER
ENDSCN NUMBER
Xid raw (8)
OPERATION VARCHAR2 (1)
COLUMN_NAME VARCHAR2 (255)
TYPE VARCHAR2 (255)
HISTORICAL_COLUMN_NAME VARCHAR2 (255)
Of course, for flash archiving, if DDL involves the drop operation on table fields, there will be restrictions, and the operation on adding fields can be supported.
How can we understand 239635? Let's take a look at object_id.
SQL> select object_id, object_name from dba_objects where object_id = 239635;
OBJECT_ID OBJECT_NAME
----------------------------------------
239635 TEST_FBARCH
At this time, check the background process and you will find that the flash back archive actually has another background process fbda.
SQL>! Ps-ef | grep fb
Oracle 26606 1 59? 00:07:24 ora_fbda_actvdb
Oracle 26924 26849 0 00:00:00 pts/1/bin/bash-c ps-ef | grep fb
Oracle 26926 26924 0 00:00:00 pts/1 grep fb