Flash back data archiving experiment-oracle total recall

Flash back data archiving experiment-new features provided by oracle total recall ORACLE 11G. With this feature, ORACLE archives UNDO data to provide comprehensive historical data query. Similar to archiving logs, the new background process FBDA (flashback data archiver process) is used to archive and write the flash data. ORACLE can specify the storage time of flash back archive data, and reduce space usage through internal partitioning and compression algorithms. Flash back data archiving requires independent storage. before using this feature, you need to create an independent ASSM tablespace. DDL cannot be used on the table tracked by FBDA (add column, rename, and grant ). Start experiment: 1. create the tablespace required by the FBDA process-flash back data archive 21:44:32 SQL> create tablespace bys_flashback datafile '/u01/app/oracle/oradata/bys001/bys_flashback.dbf 'size 10 m autoextend off; tablespace created.21: 45: 35 SQL> show userUSER is "SYS" Default FBDA can be created only after you log on to sysdba, and only one Default FBDA21: 45: 39 SQL> create flashback archive default fbda1 tablespace bys_flashback retention 7 day; Flashback archive create D.21: 46: 36 SQL> create flashback archive fbda2 tablespace bys_flashback quota 7 m retention 10 day; Flashback archive created.2. create a table and specify the flashback archive attribute to record SCN21: 47: 52 SQL> conn bys/bysConnected.21: 49: 54 SQL> create table test6 (abc varchar2 (9) flashback archive; Table created. you can use alter table test6 flashback archive fbda2 to change or cancel the flash archive of an existing table. -- check the name of the created flash archive table online? Alter table test6 no flashback archive; insert data, omitting the demo. 21:51:27 SQL> select * from test6; ABC---------12321: 51: 32 SQL> select current_scn from v $ database; CURRENT_SCN ----------- 137222321: 51: 44 SQL> delete from test6 where abc = 3; 1 row deleted.21: 52: 12 SQL> commit; Commit complete.21: 52: 14 SQL> host the ALERT Log here is a soft link from the source ALERT Log under the TRACE directory, easy to view logs. Aa. t back1.sh unzip topalert_bys001.log back2-20130623-1144.log fullback. sharchback. sh back2.sh rmanlogback0.sh cumulative3. view the creation of the flashback archive tablespace and the startup of the FBDA process from the alert Log [oracle @ oel-01 ~] $ Tail alert_bys001.log SUPLOG: unique = OFF, foreign key = OFF, all column = OFFSUPLOG: procedural replication = OFFCompleted: alter database add supplemental log dataSun Jun 23 21:45:33 2013 create tablespace bys_flashback datafile '/u01/app/oracle/oradata/bys001/bys_flashback.dbf' size 10 m autoextend offCompleted: create tablespace bys_flashback datafile '/u01/app/oracle/oradata/bys001/bys_flashb Ack. dbf 'size 10 m autoextend offSun Jun 23 21:46:36 2013 Starting background process FBDASun Jun 23 21:46:36 2013 FBDA started with pid = 33, OS id = 3961 [oracle @ oel-01 ~] $ Exitexit 21:54:08 SQL> show parameter undoNAME type value =----------- export undo_management string AUTOundo_retention integer 900undo_tablespace string UNDOTBS121: 56: 08 SQL> col name for a4021: 56: 20 SQL> select file #, name from v $ datafile where name like '% test %'; FILE # NAME ---------- ------------------------------------------ 7/u01/app/or Drop tablespace undotbs1 including contents and datafiles; 4. Use a small UNDO tablespace to facilitate the experiment. At 21:56:21 SQL> alter system set undo_tablespace = 'test1 _ undo '; System altered.21: 58: 12 SQL> show parameter undoNAME type value =------------- interval undo_management string AUTOundo_retention integer 900undo_tablespace string test1_undo clear buffer data 21:58:19 SQL> alter system flush buffer_cache; System altered.22: 00: 55 SQL> select * from test6; ABC -------- -SQL> set autotrace on explain5. the execution plan shows that the query is from SYS_FBA_TCRV_75320. This is a flashback archive tablespace used to record the flashback data. 22:03:14 SQL> select * from test6 as of scn 1372223; ABC---------312 Execution Plan -------------------------------------------------------- Plan hash value: 2569713660 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop | latency | 0 | select statement | 5 | 30 | 9 (12) | 00:00:01 | 1 | VIEW | 5 | 30 | 9 (12) | 00:00:01 | 2 | UNION-ALL | 3 | partition range single | 1 | 32 | 3 (0) | 00:00:01 | 1 | 1 | * 4 | table access full | SYS_FBA_HIST_75320 | 1 | 32 | 3 (0) | 00:00:01 | 1 | 1 | * 5 | FILTER | * 6 | hash join outer | 4 | 8184 | 6 (17) | 00:00:01 | * 7 | table access full | TEST6 | 4 | 72 | 2 (0) | 00:00:01 | * 8 | table access full | SYS_FBA_TCRV_75320 | 3 | 6084 | 3 (0) | 00:00:01 | descripredicate Information (identified by operation id ): ----------------------------------------------- 4-filter ("ENDSCN"> 1372223 AND "ENDSCN" <= 1372730 AND ("STARTSCN" is null or "STARTSCN" <= 1372223 )) 5-filter ("STARTSCN" <= 1372223 OR "STARTSCN" is null) 6-access ("T ". ROWID = CHARTOROWID ("RID" (+) 7-filter ("T ". "VERSIONS_STARTSCN" is null) 8-filter ("ENDSCN" (+) is null or "ENDSCN" (+)> 1372730) AND ("STARTSCN" (+) isnull or "STARTSCN" (+) <1372730) Note ------dynamic sampling used for this statement (level = 2)