Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Tuesday, September 29, 2015

Administer flashback data archive and schema evolution

Using Flashback Data Archive (Oracle Total Recall)
A Flashback Data Archive provides the ability to track and store transactional changes to a table over its lifetime. A Flashback Data Archive is useful for compliance with record stage policies and audit reports

You can specify a default Flashback Data Archive for the system. A Flashback Data Archive is configured with retention time. Data archived in the Flashback Data Archive is retained for the retention time.

Conditions for Data Archive:
  • You have the FLASHBACK ARCHIVE object privilege on the Flashback Data Archive that you want to use for that table.
  • The table is neither nested, clustered, temporary, remote, or external.
  • The table contains neither LONG nor nested columns.

To disable:
  • FLASHBACK ARCHIVE ADMINISTER system privilege or
  • As SYSDBA

Creating a Flashback Data Archive

Creating a default Flashback Data Archive that uses up to 1 G of tablespace tbs1, whose data are retained for 100 years:

SHAIKDB>create flashback archive default flash_arch_default tablespace tbs1 quota 1g retention 100 year;

Flashback archive created.


SHAIKDB>create flashback archive flash_days tablespace tbs2 quota 100m retention 10 day;

Flashback archive created.

SHAIKDB>alter flashback archive flash_days set default;

Flashback archive altered.

SHAIKDB>create flashback archive flash_months tablespace tbs3 quota 1g retention 1 month;

Flashback archive created.

Add space:

SHAIKDB>alter flashback archive flash_days add tablespace tbs3;

Flashback archive altered.

SHAIKDB>alter flashback archive flash_days modify retention 45 day;

Flashback archive altered.

SHAIKDB>alter flashback archive flash_days modify tablespace tbs3 quota 10g;

Flashback archive altered.


SHAIKDB>col FLASHBACK_ARCHIVE_NAME for a20
SHAIKDB>select * from dba_flashback_archive_ts;

FLASHBACK_ARCHIVE_NA FLASHBACK_ARCHIVE# TABLESPACE_NAME            QUOTA_IN_MB
-------------------- ------------------ ------------------------------ ----------------------------------------
FLASH_ARCH_DEFAULT             1 TBS1                  1024
FLASH_DAYS                 2 TBS2                  100
FLASH_MONTHS                 3 TBS3                  1024
FLASH_DAYS                 2 TBS3                  10240

SHAIKDB>select * from dba_flashback_Archive;

OWNER_NAME              FLASHBACK_ARCHIVE_NA FLASHBACK_ARCHIVE# RETENTION_IN_DAYS CREATE_TIME                        LAST_PURGE_TIME                                STATUS
------------------------------ -------------------- ------------------ ----------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- -------
SYS                  FLASH_ARCH_DEFAULT            1           36500 28-SEP-15 09.24.57.000000000 PM            28-SEP-15 09.24.57.000000000 PM
SYS                  FLASH_DAYS                2             45 28-SEP-15 09.29.39.000000000 PM            28-SEP-15 09.29.39.000000000 PM                        DEFAULT
SYS                  FLASH_MONTHS                3             30 28-SEP-15 09.30.28.000000000 PM            28-SEP-15 09.30.28.000000000 PM


undo_management             string     AUTO

SHAIKDB>alter flashback archive flash_months add tablespace tbs2;

Flashback archive altered.

SHAIKDB>alter flashback archive flash_months remove tablespace tbs3;
alter flashback archive flash_months remove tablespace tbs3
                                                      *
ERROR at line 1:
ORA-55626: Cannot remove the Flashback Archive's primary tablespace

If it is not the default tablespace that you created the archive with then you can remove the tablespace using the remove command from flashback archive.

SHAIKDB>alter flashback archive flash_months remove tablespace tbs2;

Flashback archive altered.

SHAIKDB>alter flashback archive flash_months set default;

Flashback archive altered.

SHAIKDB>create user flash identified by flash  default tablespace tbs1;

User created.

SHAIKDB>grant create session,create table to flash;

Grant succeeded.

SHAIKDB>grant flashback archive on flash_months to flash;

Grant succeeded.

SHAIKDB>grant flashback archive on flash_arch_default to flash;

Grant succeeded.

SHAIKDB>alter user flash quota unlimited on tbs1;

User altered.

SHAIKDB>alter user flash quota unlimited on tbs2;

User altered.

SHAIKDB>alter user flash quota unlimited on tbs3;

User altered.

Create table flash and store the historical data in the default Flashback Data Archive:

SHAIKDB>show user
USER is "FLASH"

SHAIKDB>create table flash1 (id number,name varchar2(10)) flashback archive;

Table created.

SHAIKDB>begin
    for i in 1..100 loop
    insert into flash1 values(i,'LOVE');
    commit;
    end loop;
    end;
    /

PL/SQL procedure successfully completed.

SHAIKDB>select * from flash1 where rownum <=10;

   ID NAME
---------- ----------
    1 LOVE
    2 LOVE
    3 LOVE
    4 LOVE
    5 LOVE
    6 LOVE
    7 LOVE
    8 LOVE
    9 LOVE
   10 LOVE

10 rows selected.


SHAIKDB>col ARCHIVE_TABLE_NAME for a20
SHAIKDB>col OWNER_NAME for a15
SHAIKDB>col table_name for a10
SHAIKDB>select * from dba_flashback_archive_tables;

TABLE_NAME OWNER_NAME       FLASHBACK_ARCHIVE_NA ARCHIVE_TABLE_NAME   STATUS
---------- --------------- -------------------- -------------------- --------
FLASH1       FLASH               FLASH_MONTHS     SYS_FBA_HIST_74868    ENABLED

SHAIKDB>show user
USER is "SYS"

SHAIKDB>create flashback archive flash_days tablespace tbs2 quota 1g retention 1 day;

Flashback archive created.


SHAIKDB>grant flashback archive on flash_days to flash;

Grant succeeded.



Create table flash2 and store the historical data in the default_flash_arch Flashback Data Archive:

SHAIKDB>create table flash2 (id number,name2 varchar2(10)) flashback archive  flash_days;

Table created.


SHAIKDB>begin
    for i in 1..100 loop
    insert into flash2 values(i,'FLASH2');
    commit;
    end loop;
    end;
    /  2    3    4    5    6    7  

PL/SQL procedure successfully completed.

SHAIKDB>select * from flash2 where rownum <=10;

   ID NAME2
---------- ----------
    1 FLASH2
    2 FLASH2
    3 FLASH2
    4 FLASH2
    5 FLASH2
    6 FLASH2
    7 FLASH2
    8 FLASH2
    9 FLASH2
   10 FLASH2

10 rows selected.


SHAIKDB>select * from dba_flashback_archive_tables;

TABLE_NAME OWNER_NAME       FLASHBACK_ARCHIVE_NA ARCHIVE_TABLE_NAME   STATUS
---------- --------------- -------------------- -------------------- --------
FLASH1       FLASH       FLASH_MONTHS     SYS_FBA_HIST_74868   ENABLED
FLASH2       FLASH       FLASH_DAYS       SYS_FBA_HIST_74878   ENABLED




SHAIKDB>alter table flash1 no flashback archive;
alter table flash1 no flashback archive
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive    
SHAIKDB>show user
USER is "SYS"

or grant flashback archive administer privilege to manage the flashback archives.

SHAIKDB>alter table flash.flash1 no flashback archive;

Table altered.


SHAIKDB>select * from dba_flashback_archive_tables;

TABLE_NAME OWNER_NAME       FLASHBACK_ARCHIVE_NA ARCHIVE_TABLE_NAME   STATUS
---------- --------------- -------------------- -------------------- --------
FLASH2       FLASH       FLASH_DAYS       SYS_FBA_HIST_74878   ENABLED

SHAIKDB>alter table flash1 flashback archive flash_days;

Table altered


TABLE_NAME OWNER_NAME       FLASHBACK_ARCHIVE_NA ARCHIVE_TABLE_NAME   STATUS
---------- --------------- -------------------- -------------------- --------
FLASH1       FLASH       FLASH_DAYS       SYS_FBA_HIST_74868   ENABLED
FLASH2       FLASH       FLASH_DAYS       SYS_FBA_HIST_74878   ENABLED


SHAIKDB>alter flashback archive flash_days modify retention 7 day;

Flashback archive altered.

19:20:19 SHAIKDB>alter session set nls_date_format='mm/dd/yyyy hh24:mi:ss';

Session altered.

19:20:40 SHAIKDB>select sysdate from dual;

SYSDATE
-------------------
09/29/2015 19:20:47

SHAIKDB>insert into flash1 select * from flash2;

200 rows created.

SHAIKDB>commit;

Commit complete.

SHAIKDB>insert into flash2 select * from flash1;

300 rows created.

SHAIKDB>commit;

Commit complete.

19:20:47 SHAIKDB>select count(*) from flash1;

 COUNT(*)
----------
      300

19:29:54 SHAIKDB>select count(*) from flash1 as of timestamp to_timestamp('09-29-2015 10:00:00','mm-dd-yyyy hh24:mi:ss');

 COUNT(*)
----------
      100

19:32:11 SHAIKDB>select count(*) from flash1 as of timestamp to_timestamp('09-29-2015 19:30:00','mm-dd-yyyy hh24:mi:ss');

 COUNT(*)
----------
      300


19:47:18 SHAIKDB>select distinct name from flash1;

NAME
----------
FLASH2
LOVE

19:47:25 SHAIKDB>delete flash1 where name='LOVE';

200 rows deleted.

19:47:45 SHAIKDB>commit;

Commit complete.

19:47:48 SHAIKDB>select count(*) from flash1;

 COUNT(*)
----------
      100

19:47:54 SHAIKDB>select count(*) from flash1 as of timestamp ( systimestamp - interval '5' minute);

 COUNT(*)
----------
      300

19:48:44 SHAIKDB>select count(*) from flash1 as of timestamp ( systimestamp - interval '1' hour);

 COUNT(*)
----------
      100


Lets get the data back:

19:48:55 SHAIKDB>select count(*) from flash1 as of timestamp ( systimestamp - interval '10' minute) where name='LOVE';

 COUNT(*)
----------
      200

19:50:44 SHAIKDB>insert into flash1 select * from flash1 as of timestamp(systimestamp - interval '10' minute) where name='LOVE';

200 rows created.

19:51:21 SHAIKDB>commit;

Commit complete.

19:51:23 SHAIKDB>select count(*) from flash1;

 COUNT(*)
----------
      300





If you must use unsupported DDL statements on a table enabled for Flashback Data Archive, use the DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA procedure to disassociate the base table from its Flashback Data Archive. To reassociate the Flashback Data Archive with the base table afterward, use the DBMS_FLASHBACK_ARCHIVE.REASSOCIATE_FBA procedure.



View
Description
*_FLASHBACK_ARCHIVE
Displays information about Flashback Data Archive files.
*_FLASHBACK_ARCHIVE_TS
Displays tablespaces of Flashback Data Archive files.
*_FLASHBACK_ARCHIVE_TABLES
Displays information about tables that are enabled for Data Flashback Archive files.



Documentation:
Oracle® Database Advanced Application Developer's Guide 11g Release 2 (11.2) → 12 Using Oracle Flashback Technology → Using Flashback Data Archive (Oracle Total Recall)

No comments: