DDL on Tables Enabled for Flashback Data Archive

On 11gR2, Flashback Data Archive supports many DDL statements. A Flashback Data Archive is feature on 11g.

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

Table created.

SQL> alter table tb_recall flashback archive fla1;

Table altered.

SQL> insert into tb_recall values (1,'surachart');

1 row created.

SQL> commit;

Commit complete.

SQL> alter table tb_recall add (address varchar2(100));

Table altered.

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

SQL> alter table tb_recall rename to tb_recall_new;
alter table tb_recall rename to tb_recall_new
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

can't use "TRUNCATE", "RENAME" table on 11gR1, then on 11gR2:

SQL> create tablespace tbs_fla1 datafile size 1G;

Tablespace created.

SQL> create flashback archive fla1 tablespace tbs_fla1 retention 1 year;

Flashback archive created.

SQL> insert into tb_recall values ('1','surachart');

1 row created.

SQL> commit;

Commit complete.

SQL> !date
Thu Jun 3 15:45:00 ICT 2010

SQL> alter table tb_recall add (address varchar2(100));

Table altered.

SQL> truncate table tb_recall;

Table truncated.

SQL> alter table tb_recall rename to tb_recall_new;

Table altered.

SQL> select count(*) from tb_recall_new;

COUNT(*)
----------
0

SQL> select count(*) from tb_recall_new as of timestamp to_timestamp ('2010-06-03:15:45:00', 'yyyy-mm-dd:hh24:mi:ss');

COUNT(*)
----------
1

We can use truncate and rename table.

SQL> alter table tb_recall_new add (zip varchar2(10));

Table altered.

SQL> insert into tb_recall_new values(1,'surachart','999','10400');

1 row created.

SQL> commit;

Commit complete.

SQL> !date
Thu Jun 3 16:01:41 ICT 2010

SQL> alter table tb_recall_new drop column zip;

Table altered.

SQL> select * from tb_recall_new;

ID NAME ADDRESS
---------- ------------------------------ ---------------
1 surachart 999

didn't see column(ZIP), then checked at '2010-06-03:16:02:00'

SQL> select * from tb_recall_new as of timestamp to_timestamp ('2010-06-03:16:02:00', 'yyyy-mm-dd:hh24:mi:ss');

ID NAME ADDRESS ZIP
---------- ------------------------------ --------------- ----------
1 surachart 999 10400

How? if we re- add column (old name).

SQL> select * from tb_recall_new ;

ID NAME ADDRESS A
---------- ------------------------------ --------------- ----------
1 surachart 999

SQL> update tb_recall_new set A=100;

1 row updated.

SQL> commit;

Commit complete.

SQL> !date
Thu Jun 3 16:17:46 ICT 2010

SQL> alter table tb_recall_new drop column A;

Table altered.

SQL> select * from tb_recall_new;

ID NAME ADDRESS
---------- ------------------------------ ---------------
1 surachart 999

SQL> select * from tb_recall_new as of timestamp to_timestamp ('2010-06-03:16:17:46', 'yyyy-mm-dd:hh24:mi:ss');

ID NAME ADDRESS A ZIP
---------- ------------------------------ --------------- ---------- ----------
1 surachart 999 100

then added old column name.

SQL> alter table tb_recall_new add (a number);

Table altered.

SQL> select * from tb_recall_new;

ID NAME ADDRESS A
---------- ------------------------------ --------------- ----------
1 surachart 999

SQL> select * from tb_recall_new as of timestamp to_timestamp ('2010-06-03:16:17:46', 'yyyy-mm-dd:hh24:mi:ss');

ID NAME ADDRESS A ZIP
---------- ------------------------------ --------------- ---------- ----------
1 surachart 999

After dropped and re- added column (old name), that made us don't see old data... Any Idea?

Some DDL statements cause error ORA-55610:
- ALTER TABLE statement that includes an UPGRADE TABLE clause, with or without an INCLUDING DATA clause
- ALTER TABLE statement that moves or exchanges a partition or subpartition operation
- DROP TABLE statement

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

-)

Flashback Transaction Backout

The dbms_flashback.transaction_backout procedure uses logminer data to backout transactions, so your data in that transaction was rolled back.
Requirements:
1. Database must be in Archive Mode

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 48
Next log sequence to archive 50
Current log sequence 50

2. Enable supplemental logging at Database level

SQL> alter database add supplemental log data;

Database altered.

Example:

SQL> delete from tb_test where object_id=11112;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from tb_test where object_id=11112;

COUNT(*)
----------
0

SQL> select versions_xid , object_id from tb_test versions between scn minvalue and maxvalue where object_id=11112;

VERSIONS_XID OBJECT_ID
---------------- ----------
08001600CC040000 11112

SQL> declare
v_xid sys.xid_array;
begin
v_xid := sys.xid_array('08001600CC040000');
dbms_flashback.transaction_backout(numtxns=>1,
xids=>v_xid, options=>dbms_flashback.cascade);
end;
/
declare
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70
ORA-06512: at line 5

Problem:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70

Solve:
grant create any table to user_name;

SQL> declare
v_xid sys.xid_array;
begin
v_xid := sys.xid_array('08001600CC040000');
dbms_flashback.transaction_backout(numtxns=>1,
xids=>v_xid, options=>dbms_flashback.cascade);
end;
/

PL/SQL procedure successfully completed.

SQL> select count(*) from tb_test where object_id=11112;

COUNT(*)
----------
1

Now... data was rolled back. We can review on...
- [DBA, USER]_FLASHBACK_TXN_STATE
- [DBA, USER]_FLASHBACK_TXN_REPORT

SQL> SELECT * FROM USER_FLASHBACK_TXN_STATE;

COMPENSATING_XID XID DEPENDENT_XID BACKOUT_MODE
---------------- ---------------- ---------------- ----------------
09000B00D6040000 08001600CC040000 CASCADE

SQL> SELECT * FROM USER_FLASHBACK_TXN_REPORT;

read more

Can't alter database flashback on, ORA-38777 on cluster

$ srvctl stop database -d db -o immediate
$ srvctl start database -d db -o mount
$ env | grep SID
ORACLE_SID=db1
$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Mar 29 17:32:12 2006

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> alter database flashback on
2 ;
alter database flashback on
*
ERROR at line 1:
ORA-38777: database must not be started in any other instance.


SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options


-------------So resolve this problem----------------
Start on node


$ srvctl stop database -d db -o immediate
$ srvctl start instance -d db -i db1 -o mount
$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Mar 29 17:34:09 2006

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> alter database flashback on;

Database altered.

SQL> alter database open
2 ;

Database altered.