Yasar MORAN | Oracle Blog

In this post i’m going to try to explain flasback Technologies.

Flashback technologies are applicable such as, Erroneous update, delete or insert transactions, Erroneous transactions affecting one table or a set of tables, DROP TABLE statements, Series of database-wide malicious transactions, Erroneous running code affecting many tables or an unknown set of tables, Drop tablespace without removing the physical datafiles. Flashback technologies cannot be used for media failure.

Flashback Table

Flashback Table can be used for quickly recover a table to a point in time in the past without restoring a backup. The time which the table can be flashed back is dependent on undo data in the system.

For using Flashback table, you must have FLASHBACK ANY TABLE system privilege or FLASHBACK object privilege on the table. Also you must have SELECT, INSERT, DELETE, and ALTER object privileges on the table. Row movement must be enabled for all tables in the Flashback list.

During an Flashback Table operation, Oracle acquires exclusive DML locks on all the tables specified in the Flashback list. When you use flashback table list, either all of the tables revert to the earlier state or none of them do. FLASHBACK TABLE TO SCN or TIMESTAMP does not preserve rowids, and FLASHBACK TABLE TO BEFORE DROP does not recover referential constraints.

Flashback table operation not revert statistics associated with table to their earlier form. Indexes on table that exist currently are reverted and reflect the state of the table at the Flashback point. If the index exists now but did not yet exist at the Flashback point, then the database updates the index to reflect the state of the table at the Flashback point. However, indexes that were dropped during the interval between the Flashback point and the current time are not restored. Oracle Database cannot restore a table to an earlier state across any DDL operations that change the structure of the table. You cannot roll back a FLASHBACK TABLE statement. Flashback table changes are propagated to physical and logical standby databases.

Flashback to SCN

Flashback SCN is used to return the table to valid SCN.

ex:

SQL> alter table deneme.deneme enable row movement;
Table altered.

SQL> select * from deneme.deneme;

AD SOYAD
——————— ——————————
b b
a a

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
————————
2903015

SQL> insert into deneme.deneme select * from deneme.deneme;

2 rows created.

SQL> commit;

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
————————
2903114

SQL> select * from deneme.deneme;

AD SOYAD
———————- ——————————
b b
a a
b b
a a

SQL> flashback table deneme.deneme to scn 2903015;

Flashback complete.

SQL> select * from deneme.deneme;

AD SOYAD
——————— ——————————
b b
a a

Flashback to TIMESTAMP

Flashed back table to valid time.

SQL> select * from deneme.deneme;

AD SOYAD
——————— ——————————
b b
a a

SQL> SELECT SYSTIMESTAMP from v$database;

SYSTIMESTAMP
—————————————————————————
02-JAN-10 08.23.31.625000 PM +02:00

SQL> insert into deneme.deneme select * from deneme.deneme;

2 rows created.

SQL> commit;

Commit complete.

SQL> select * from deneme.deneme;

AD SOYAD
—————————— ——————————
b b
a a
b b
a a

SQL> SELECT SYSTIMESTAMP from v$database;

SYSTIMESTAMP
—————————————————————————
02-JAN-10 08.28.40.312000 PM +02:00

SQL> FLASHBACK TABLE deneme.deneme TO TIMESTAMP (SYSTIMESTAMP – INTERVAL ’10′ minute);

Flashback complete.

SQL> select * from deneme.deneme;

AD SOYAD
—————————— ——————————
b b
a a

now flashback to another format example

SQL> FLASHBACK TABLE deneme.deneme TO TIMESTAMP TO_DATE(’02.01.10 20.28.40′,’dd.mm.yy h24:mi:ss’);

Flashback complete.

SQL> select * from deneme.deneme;

AD SOYAD
—————————— ——————————
b b
a a
b b
a a

Flashback To Restore Point
Flashed back table to user defined point.

SQL> select * from deneme.deneme;

AD SOYAD
—————————— ——————————
b b
a a

SQL> CREATE RESTORE POINT baslangic;

Restore point created.

SQL> insert into deneme.deneme select * from deneme.deneme;

2 rows created.

SQL> commit;

Commit complete.

SQL> select * from deneme.deneme;

AD SOYAD
—————————— ——————————
b b
a a
b b
a a

SQL> flashback table deneme.deneme TO RESTORE POINT baslangic;

Flashback complete.

SQL> select * from deneme.deneme;

AD SOYAD
—————————— ——————————
b b
a a

References
1- Oracle® Database SQL Reference 10g Release 2 (10.2)
2- http://www.psoug.org
3- Oracle® Database High Availability Architecture and Best Practices 10g Release 1 (10.1)
4- Note 270535.1 – Restrictions on Flashback Table Feature

First of all, excuse me for my english.
As you know, there isn’t retention notion on Oracle 8i. So, if you want to delete old backup you have to deal. i will explain how to delete old backup on 8i. Following case tested on oracle 8i.

1- To find old backup, login catalog database using catalog owner user.
rman: catalog owner
BCK : Catalog database alias
dbid : DBID of the database which will be deleted. to find dbid you can use v$database.
completion_time: backup completion time. following code deletes backup older than 31 days.

$ sqlplus rman/rman@BCK

select ‘change backuppiece ‘||bp.bp_key||’ delete;’
from rc_backup_piece bp,rc_database db
where db.dbid=’1234567890′
and bp.db_id = db.dbid
and bp.completion_time < (sysdate-31) order by bp.COMPLETION_TIME desc;

2- to delete old backup, we must login target and catalog.

$ rman target sys/sys@TEST rcvcat rman/rman@BCK

3- Now, after allocating delete channel, we can use output of step1 .

rman> allocate channel for delete type ‘SBT_TAPE’;
rman> change backuppiece 4360762 delete;

Regards,