Flashback scripts for Cool performance tests

20 Jun 2007

First of all, once the tables are created we should enable row movement on them and grant the flashback privilege to the user.

From doc :

Prerequisites

To flash back a table to an earlier SCN or timestamp, you must have either the FLASHBACK object privilege on the table or the FLASHBACK ANY TABLE system privilege. In addition, you must have the SELECT, INSERT, DELETE, and ALTER object privileges on the table.

Row movement must be enabled for all tables in the Flashback list unless you are flashing back the table TO BEFORE DROP. That operation is called a flashback drop operation, and it uses dropped data in the recyclebin rather than undo data. Please refer to row_movement_clause for information on enabling row movement.

To flash back a table to a restore point, you must have the SELECT ANY DICTIONARY or FLASHBACK ANY TABLE system privilege or the SELECT_CATALOG_ROLE role.

To flash back a table to before a DROP TABLE operation, you need only the privileges necessary to drop the table.

TO RESTORE POINT Clause

Specify a restore point to which you want to flash back the table. The restore point must already have been created.

See Also: CREATE RESTORE POINT for information on creating restore points

Note: Oracle strongly recommends that you run your database in automatic undo mode by setting the UNDO_MANAGEMENT initialization parameter to AUTO. In addition, set the UNDO_RETENTION initialization parameter to an interval large enough to include the oldest data you anticipate needing. For more information please refer to the documentation on the UNDO_MANAGEMENT and UNDO_RETENTION initialization parameters.

SQL> grant FLASHBACK ANY TABLE to USER;

Grant succeeded.

SQL> ALTER TABLE Table1 enable row movement;

Table altered.

SQL> CREATE RESTORE POINT good_data;

Restore point created.

SQL> FLASHBACK TABLE Table1;

Flashback complete.

21 Jun 2007

After successfully creating the scripts we spotted a big performance issue. For big tables the process takes way too much time.

SQL> @FLASHBACK_PDBST001

Flashback complete.

Elapsed: 00:38:59.80

SQL> select sum(bytes)/power(2,30)||' GB' "PDBST001 size" from user_extents where segment_name like 'PDBST001%';

PDBST001 size
------------------------------------------
33.99652099609375 GB

Now let's try to do the same on one sample table with modifications and compare the hand-made reverse tool to flashback table.

25 Jun 2007

Actually the performance tests done by Gianni are usually using only 1 folder, so it would be a lot quicker if we flashback only a few tables.

SQL> flashback table pdbst005_f0001_iovs, pdbst005_f0001_iovs_seq, pdbst005_f0001_channels to restore point test;

Flashback complete.

Elapsed: 00:03:19.62

SQL> delete from PDBST005_F0001_IOVS where iov_since > 12960300006;

600000 rows deleted.

Elapsed: 00:00:41.83

The flashback technology still takes more time but perhaps it is not an issue anymore since usually only one table is affected by the test.

So the flashback solution is still possible.

-- RomainBasset - 25-Jul-2007