To start with, this feature is not available for Oracle standard, personal and Express Editions.
From Oracle 10G, flashback feature mainly depends on undo segments (other than flashback database as it uses flashback logs). Thus it has limitation up to how we can retrieve data and it depends on undo data in undo tablespace. To avoid this, Oracle has provided new feature as
FLASHBACK DATA ARCHIVE.
It is a database object (must be confused with object, but yes it is!) that holds histotrical data for one or many tables. Additional advantage of Flashback Data Archive that it has space retention and purging policy also.
For Flashback Data Archive, oracle has background process FDBA to deal with all flashback related work.
It should be something like ora_fbda_padwsdpr. Where PADWSDPR is the name of the instance for which this process is attached.
Let see how to create and use flashback data archive.
To deal with flashback data archive, user must have FLASHBACK ARCHIVE ADMINISTER privilege.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> select * from dba_sys_privs where privilege like '%FLASH%';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
SYS FLASHBACK ANY TABLE NO
DBA FLASHBACK ANY TABLE YES
SYS FLASHBACK ARCHIVE ADMINISTER NO
DBA FLASHBACK ARCHIVE ADMINISTER YES
MDSYS FLASHBACK ANY TABLE NO
SQL> grant flashback archive administer to jay_flash;
Grant succeeded.
Two main parameters that is used with the create statement of flashback data are rentention and quota. Retention confirms the maximum time holding of flashback data and quota will confirm for the space it will be used on provided tablespace.
Once reaching the quota, oracle will issue out-of-space alert.
SQL> create flashback archive flash2
2 tablespace users quota 1024M retention 5 year;
Flashback archive created.
Other option available with alteration of flashback data are:
alter flashback archive flash2 set default
alter flashback archive flash2 add tablespace user2;
alter tablespace archive flash2 modify tablespace user2;
Now let’s see how to use this flashback data with tables.
SQL> create table test11 ( name varchar2(30), address varchar2(50))
2 flashback archive flash2;
Table created.
Already created can be altered this way:
SQL> alter table test12 flashback archive flash2;
Table altered.
Checking the flashback data of the tables:
SQL> select * from dba_flashback_archive_tables;
TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS
------------------------- ------------------------- ------------------------- ------------------------- -------------------------
TEST11 DBCHECK FLASH2 SYS_FBA_HIST_366239 ENABLED
SQL> select flashback_archive_name,retention_in_days from dba_flashback_archive;
FLASHBACK_ARCHIVE_NAME RETENTION_IN_DAYS
------------------------- -----------------
FLASH2 1825
SQL>
Now table is created with flashback data, let’s try to use it. Here I will update the created table and try to read from flashback data for the updated column.
SQL> select * from test11;
NAME ADDRESS
------------------------------ --------------------------------------------------
JAY A
JAY A
JAY A
JAY A
JAY A
SQL>
SQL> !date
Wed Dec 21 05:14:17 EST 2011
SQL> !date
Wed Dec 21 05:15:35 EST 2011
SQL> update test11 set address='B' where name='JAY';
5 rows updated.
SQL> commit;
Commit complete.
SQL> select * from test11;
NAME ADDRESS
------------------------------ --------------------------------------------------
JAY B
JAY B
JAY B
JAY B
JAY B
Now the before image must be available with flashback data, so checking that.
SQL> select * from SYS_FBA_HIST_366239;
no rows selected
SQL> /
no rows selected.
It should be available but unfortunately not…!! Here I understand why. Background process FDBA wakes up at system determined interval (default is 5 mins) and there it copies all the corresponding undo data for archive. So next time if changes won’t reflect immediately won’t be surprise.
Let’s try again:
SQL> !date
Wed Dec 21 05:45:59 EST 2011
SQL> select NAME,ADDRESS from SYS_FBA_HIST_366239;
NAME ADDRESS
------------------------------ --------------------------------------------------
JAY A
JAY A
JAY A
JAY A
JAY A
SQL> select * from test11;
NAME ADDRESS
------------------------------ --------------------------------------------------
JAY B
JAY B
JAY B
JAY B
JAY B
Now the data is available in flashback archive. One more point to add, we cannot modify the flashback data. It is only allowed for read-only access.
Now if needed, we can also think to purge the flash data.
SQL> alter flashback archive FLASH2 purge all;
Flashback archive altered.
Other option are also available for purging, some of them are:
alter flashback archive flash1 purge before timestamp (systimestamp – interval '2' day);
alter flashback archive flash1 purge before scn 123456;
Next section, we will take a short look of some limitation of this feature.
All the tables enabled for the flashback archive feature will not be allowed to perform below funtions:
- ALTER TABLE statement that does any of the following
-Drops, renames, or modifies a column
-Performs partition or subpartition operations
-Converts a LONG column to a LOB column
-Includes an UPGRADE TABLE ,with or without an INCLUDING DATA clause
- DROP TABLE statement
- RENAME TABLE statement
- TRUNCATE TABLE statement
Attempted to drop for the table which is enabled for faskback data archive:
SQL> drop table test11;
drop table test11
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
Solution:
SQL> alter table test11 no flashback archive;
Table altered.
SQL> drop table test11;
Table dropped.
Adding also to drop the flashback data archive.
SQL> drop flashback archive FLASH2;
Flashback archive dropped.
SQL>
Also please read Oracle document more information.
Thanks
Jay Vardhan
21-12-2011
No comments:
Post a Comment