Posts Tagged Total Recall

Flashback Data Archive in #Oracle 12c

This article highlights some 12c changes of Flashback Data Archive (FDA). See here for an introduction. Remarkably, the feature is now included in all editions. I will focus here on the 12c enhancement that an application can be registered to FDA. That has two major benefits:

1. FDA can be turned on and off for all tables of that application with one command

2. Protection (better than READ ONLY) can be turned on and off for all tables of that application with one command

The playing field:

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0
PL/SQL Release 12.1.0.1.0 - Production                                                    0
CORE    12.1.0.1.0      Production                                                        0
TNS for Linux: Version 12.1.0.1.0 - Production                                            0
NLSRTL Version 12.1.0.1.0 - Production                                                    0
SQL> grant dba to quaid identified by quaid;

Grant succeeded.

SQL> connect quaid/quaid
Connected.
SQL> create tablespace flatbs datafile '/home/oracle/prima/flatbs01.dbf' size 50m;

Tablespace created.

SQL> create table t1 as select * from dual;

Table created.

SQL> create table t2 as select * from dual;

Table created.

SQL> create flashback archive fla_10y tablespace flatbs retention 10 year;

Flashback archive created.

So far no new feature as the same can be done with 11g. No I register the application – that is just declarative:

SQL> begin
     dbms_flashback_archive.register_application
     (application_name=>'MYAPP',
     flashback_archive_name=>'FLA_10Y');
     end;
     /  

PL/SQL procedure successfully completed.

Now I add all tables of that application:

SQL> begin
     dbms_flashback_archive.add_table_to_application
     (application_name=>'MYAPP',
     table_name=>'t1',
     schema_name=>'QUAID');
     end;
     /  

PL/SQL procedure successfully completed.

SQL> begin
     dbms_flashback_archive.add_table_to_application
     (application_name=>'MYAPP',
     table_name=>'t2',
     schema_name=>'QUAID');
     end;
     /  

PL/SQL procedure successfully completed.

I can now turn FDA on and off for all the tables with one command:

SQL> select table_name,status from user_flashback_archive_tables;

no rows selected

SQL> exec dbms_flashback_archive.enable_application(application_name=>'MYAPP')

PL/SQL procedure successfully completed.

SQL> select table_name,status from user_flashback_archive_tables;

TABLE_NAME STATUS
---------- ----------
T1         ENABLED
T2         ENABLED

SQL> exec dbms_flashback_archive.disable_application(application_name=>'MYAPP')

PL/SQL procedure successfully completed.

SQL> select table_name,status from user_flashback_archive_tables;

no rows selected

Furthermore, I can now protect these tables with one command against modification and drop:

SQL> exec dbms_flashback_archive.lock_down_application(application_name=>'MYAPP')

PL/SQL procedure successfully completed.

SQL> delete from t1;
delete from t1
            *
ERROR at line 1:
ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on table "QUAID"."T1"

SQL> drop table t1;
drop table t1
           *
ERROR at line 1:
ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on table "QUAID"."T1"

This is a much better protection than just making tables READ ONLY:

SQL> create table t3 as select * from dual;

Table created.

SQL> alter table t3 read only;

Table altered.

SQL> drop table t3;

Table dropped.

Please notice also that the tables need not to be in FDA mode to be protected by the lock_down_application call. The doc by the way doesn’t mention the unlock procedure:

SQL> exec dbms_flashback_archive.unlock_application(application_name=>'MYAPP')

PL/SQL procedure successfully completed.

Conclusion: The 12c enhancements of Flashback Data Archive enable the fast change of the FDA mode for multiple tables. Furthermore, it is now easily possible to prevent tables from being modified and dropped.

About these ads

, , ,

5 Comments

“Total Recall”: Brief introduction into Flashback Data Archive

With Oracle Database 11g, we have a new Option available, called Total Recall. This option extends the possibility to do Flashback Query, introduced in 9i already.

We can now designate particular tables for being able to track back all the changes on them even after years. This option addresses especially legal requirements to keep history for certain data for several years. With Total Recall resp. Flashback Data Archive, this can be achieved quite comfortable and efficient. In order to demonstrate that, I create a demo user, giving him the DBA role (kind of “quick & dirty” of course, but makes the demo easier):

SQL> grant dba to quaid identified by quaid;

Grant succeeded.

SQL> connect quaid/quaid
Connected.

SQL> create tablespace flatbs
 datafile '/u01/app/oracle/oradata/orcl/flatbs01.dbf' size 50m;

Tablespace created.

This tablespace is just an ordinary tablespace, but I am going to use it to hold the Flashback Archive, created now:

SQL> create flashback archive fla_10y
 tablespace flatbs retention 10 year;

Flashback archive created.

If I designate a table to fla_10y, I will be able to do Flashback Query on that table even after 10 years – provided I have the space to hold that history in the tablespace(s), associated to that Flashback Archive. Following creates two ordinary tables with ordinary rows in them:

SQL> create table norecall
 (id number, name varchar2(50));

Table created.

SQL> create table totalrecall
 (id number, name varchar2(50));

Table created.

SQL> insert into norecall values (1,'QUAID');

1 row created.

SQL> insert into  totalrecall values (1,'QUAID');

1 row created.

SQL> commit;

Commit complete.

I want to be able to keep the history of the second table for 10 years:

SQL> alter table totalrecall flashback archive fla_10y;

Table altered.

SQL> select to_char(systimestamp,'yyyy-mm-dd:hh24:mi:ss')
 from dual;

TO_CHAR(SYSTIMESTAM
-------------------
2009-10-21:10:55:47

I will do DML on the two tables now. As you probably know, this leads to the creation of before images in the undo tablespace, which we can use to look into the past of the tables – but most likely not after 10 years, because the before images in the undo tablespace are getting overwritten somewhen.

SQL> delete from norecall;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from norecall as of timestamp
 to_timestamp('2009-10-21:10:55:47','yyyy-mm-dd:hh24:mi:ss');

 ID NAME
---------- --------------------------------------------------
 1 QUAID

SQL>  delete from  totalrecall;

1 row deleted.

SQL> commit;

Commit complete.

The select above is a Flashback Query, as it is possible since 9i. I will now make sure, that there are no before images left in the undo tablespace by creating a new one and dropping the old one.

SQL> create undo tablespace undonew datafile
 '/u01/app/oracle/oradata/orcl/undonew01.dbf' size 50m;
Tablespace created.
SQL> alter system set undo_tablespace='UNDONEW';
System altered.
SQL> select * from norecall as of timestamp
 to_timestamp('2009-10-21:10:55:47','yyyy-mm-dd:hh24:mi:ss');
 ID NAME
---------- --------------------------------------------------
 1 QUAID

As long as the old undo tablespace is still there, it may still get used for a conventional Flashback Query. But no longer after the drop tablespace. The before images of the history-tracked table, though, are saved into the Flashback Archive (getting compressed during the transfer), which would be the same, if the content of the old undo tablespace gets overwritten by new before images. That’s why I still can do Flashback Query with totalrecall:

SQL> connect / as sysdba
Connected.
SQL> shutdown immediate

SQL> startup

SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.
SQL> connect quaid/quaid
Connected.

SQL> select * from norecall as of timestamp
 to_timestamp('2009-10-21:10:55:47','yyyy-mm-dd:hh24:mi:ss');  
select * from norecall as of timestamp
 *
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number  with name "" too small

SQL> select * from totalrecall as of timestamp
 to_timestamp('2009-10-21:10:55:47','yyyy-mm-dd:hh24:mi:ss');   

 ID NAME
---------- --------------------------------------------------
 1 QUAID

I will insert a new row into the ordinary table to demonstrate further differences between it and the history tracked table:

SQL> insert into norecall values (2,'John Doe');
1 row created.
SQL> commit;
Commit complete.
SQL> select to_char(sysdate,'yyyy-mm-dd:hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2009-10-23:02:14:28

Upto this point of the story, the shown features are the same in 11g release 1 already available. Following are new features of 11g release 2: We are now able to do DDL statements on the history-tracked tables and can still do Flashback Query!

SQL> alter table norecall drop column name;

Table altered.

SQL> select * from norecall  as of timestamp
 2  to_timestamp('2009-10-23:02:14:28','yyyy-mm-dd:hh24:mi:ss');
select * from norecall  as of timestamp
 *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

SQL> select to_char(sysdate,'yyyy-mm-dd:hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2009-10-23:02:17:06

SQL> truncate table norecall;

Table truncated.

SQL> select * from norecall  as of timestamp
 2  to_timestamp('2009-10-23:02:17:06','yyyy-mm-dd:hh24:mi:ss');
select * from norecall  as of timestamp
 *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

Above demonstrated that DDL on an ordinary table makes Flashback Query behind the DDL impossible. That was nothing new. New is:

SQL> alter table totalrecall drop column name;

Table altered.

SQL> select * from totalrecall as of timestamp
 to_timestamp('2009-10-21:10:55:47','yyyy-mm-dd:hh24:mi:ss');  

 ID NAME
---------- --------------------------------------------------
 1 QUAID

SQL> truncate table totalrecall;

Table truncated.

SQL> select * from totalrecall as of timestamp
 to_timestamp('2009-10-21:10:55:47','yyyy-mm-dd:hh24:mi:ss');   

 ID NAME
---------- --------------------------------------------------
 1 QUAID

With 11g release 2, we can do Flashback Query behind DDL on tables in the Flashback Archive! In 11g release 1, DDL on history-tracked tables was prohibited, which was a show-stopper for many customers.  One DDL, we still can’t get through is of course:

SQL> drop table totalrecall;
drop table totalrecall
 *
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

, , ,

12 Comments

Follow

Get every new post delivered to your Inbox.

Join 2,429 other followers

%d bloggers like this: