Oracle 11g Flashback Data Archive (flash back Data archiving)

Oracle 11g Flashback Data Archive (flash back Data archiving) UNDO tablespace record rollback information although it can provide flash back query, but for a long time, this information will be overwritten. In fact, as long as the transaction is committed, it will become a writable object. Therefore, when performing a transient query, we will receive the 1555 error because we cannot find the undo block, and Flashback Data Archive is introduced in the 11G. It is used to store all the changes in Data and the time is set by yourself, this feature consumes more disk space. 1. Create a flashback data archive 1. To create a flashback data archive, you must have the DBA role or have system permissions flashback archive administer.

sys@MYDB> select * from dba_sys_privs where privilege like '%FLASH%';GRANTEE                        PRIVILEGE                                ADM------------------------------ ---------------------------------------- ---SYS                            FLASHBACK ANY TABLE                      NODBA                            FLASHBACK ANY TABLE                      YESSYS                            FLASHBACK ARCHIVE ADMINISTER             NODBA                            FLASHBACK ARCHIVE ADMINISTER             YESsys@MYDB> grant flashback archive administer to gyj;Grant succeeded.

2. Create a tablespace

sys@MYDB> create tablespace flash_tbs1 datafile '/u01/app/oracle/oradata/mydb/flash_tbs1.dbf' size 20480M;Tablespace created.

3. create a flashback archive sys @ MYDB> create Flashback archive flash1 tablespace flash_tbs1 quota 1024 M retention 5 year; flashback archive created. 2. Change the Flashback data archive

Sys @ MYDB> alter flashback archive flash1 set default; Flashback archive altered. sys @ MYDB> alter flashback archive flash1 add tablespace tp1; -- add the tablespace Flashback archive altered. sys @ MYDB> alter flashback archive flash1 remove tablespace tp1; -- delete the tablespace Flashback archive altered. sys @ MYDB> alter flashback archive flash1 modify tablespace flash_tbs1 quota 2048 M; -- add the quota Flashback archive altered. sys @ MYDB> alter flashback archive flash1 modify retention 3 year; Flashback archive altered. sys @ MYDB> alter flashback archive flash1 purge all; -- clear all Flashback archive altered. sys @ MYDB> alter flashback archive flash1 purge before timestamp (effecimestamp-interval '2' day); -- clears Flashback archive altered two days ago. sys @ MYDB> alter flashback archive flash1 purge before scn 123344; Flashback archive altered.

3. Enable and disable flash back data archiving 1. Enable the flash back log gyj @ MYDB> create table t1 (id int, name varchar2 (10) when creating a table )) flashback archive flash1; Table created. 2. You can also enable the table's flashback log alter table t1 flashback archive after creating the table; -- enable the flashback data archiving for the table, if this parameter is not specified, the default alter table t1 flashback archive flash1; -- enable flashback data archiving for the table, specify to store table changes in a specific flash back data archive. 3. The database will archive table T1 data to the default flash back data archive. gyj @ MYDB> select * from dba_flashback_archive_tables; TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS ----------- brief -------------- ------------- T1 GYJ FLASH1 ENABLED 4. You must set default flashback Data Archiving gyj @ MYDB> select nation, status from ENABLED; FLASHBACK_ARCHIVE_NAME STATUS ------------------------------------ FLASH1 DEFAULT 5. Disable flashback Data Archiving gyj @ MYDB> alter table t1 no flashback archive; Table altered. iv. Flashback Restrictions on Data Archiving include some restrictions in the Process of flash back archiving. For tables with flashback enabled, you cannot use the DDL command drop column (11r2 can drop column), but you can add column. The only way to delete a table that enables flashback Data Archiving is to disable the flashback archiving function first. However, this will delete all flash back archive data. 1. alter table: Drops, renames, or modifies a column (11GR2 is acceptable) Performs partition or subpartition operationsConverts a LONG column to a LOB columnIncludes an upgrade table clause, with or without an including data clause2, DROP TABLE3, truncate table (11GR2 is acceptable) 4, rename table (11GR2 is also acceptable)

gyj@MYDB> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Productiongyj@MYDB> alter table t1 drop column name;Table altered.gyj@MYDB> alter table t1 add(name varchar2(100));Table altered.gyj@MYDB> alter table t1 rename to t10;Table altered.gyj@MYDB> truncate table t10;Table truncated.gyj@MYDB> drop table t10;drop table t10           *ERROR at line 1:ORA-55610: Invalid DDL statement on history-tracked tablegyj@MYDB> alter table t10 no flashback archive;Table altered.gyj@MYDB> drop table t10;Table dropped.

 

5. Check which tables have enabled flashback Data Archiving gyj @ MYDB> select * from dba_flashback_archive_tables; TABLE_NAME OWNER_NAME revoke ARCHIVE_TABLE_NAME STATUS ----------------- begin ---------------- ------------- T1 GYJ FLASH1 ENABLED 2. query all the flashback data archives in the database gyj @ MYDB> select region, retention_in_days from expiration; FLASHBACK_ARCHIVE_NAME RETENTION_IN_DAYS ------------------------------------------------------------- FLASH1 1095 3. query information about the tablespace used for flash data archiving.

gyj@MYDB>  select flashback_archive_name,tablespace_name,quota_in_mb from dba_flashback_archive_ts;FLASHBACK_ARCHIVE_NAME        TABLESPACE_NAME                QUOTA_IN_MB---------------------------- ----------------------- -----------------FLASH1                          FLASH_TBS1                     2048

 

6. Use flashback Data Archiving: Example

Gyj @ MYDB> create table test_gyj (id int, name varchar2 (10); Table created. gyj @ MYDB> alter table test_gyj flashback archive flash1; Table altered. gyj @ MYDB> begin 2 for I in 1 .. 100 loop 3 insert into test_gyj values (I, 'gyj' | I); 4 commit; 5 end loop; 6 end; 7/PL/SQL procedure successfully completed. gyj @ MYDB> select count (*) from test_gyj; COUNT (*) ---------- 100gyj @ MYDB> col FLASHBACK_ARCHIVE_NAME fo R a10gyj @ MYDB> col TABLE_NAME for a10gyj @ MYDB> col ARCHIVE_TABLE_NAME for a20gyj @ MYDB> col OWNER_NAME for a5gyj @ MYDB> select * from dba_flashback_archive_tables; TABLE_NAME owner flashback _ ARCHIVE_TABLE_NAME STATUS ---------- ----- ---------- -------------------- ------------- T1 GYJ FLASH1 ?enabledt10 GYJ FLASH1 =enabledtest_gyj GYJ FLASH1 =enab LEDgyj @ MYDB> select count (*) from nation; COUNT (*) ---------- 0gyj @ MYDB> select current_scn from v $ database; CURRENT_SCN ----------- 2353743gyj @ MYDB> delete from test_gyj; 100 rows deleted. gyj @ MYDB> commit; Commit complete. gyj @ MYDB> select current_scn from v $ database; CURRENT_SCN ----------- 2353790gyj @ MYDB> select count (*) from test_gyj as of scn 2353743; COUNT (*) ---------- 100gyj @ MYDB> sele Ct count (*) from test_gyj as of scn 2353790; COUNT (*) ---------- 0gyj @ MYDB> select count (*) from SYS_FBA_HIST_17908; COUNT (*) ---------- 0gyj @ MYDB> select count (*) from SYS_FBA_HIST_17908; -- refresh the data slowly and wait patiently !!! COUNT (*) ---------- 200

 

7. Delete flashback archive data drop flashback archive flash1;