ANBOB™

Technically there is no schema level flashback feature available. and flashback database is a binary operation, the bits and bytes of the flashed back data are identical to what they used to be, it is not a ‘logical’ flashback, it is a physical flashback. However, provided your recovery period does not include any data dictionary changes DDL) then you could actually use datapump or exp utility to flashback export the schema and import it back in.

flashback schame of success on the premise that make sure the UNDO-Tablespace is large enough to handle all changes for the retention period for the instance before use.

To achieve this you would use syntax like …

    exp anbob/anbob tables=testfls file=t.dmp flashback_time=\"to_timestamp\(\'2013-03-14 17:32:35\',\'yyyy-mm-dd hh24:mi:ss\'\)\";
or 
    expdp username/password directory=data_pump_dir dumpfile=data_pump_dir:dumpfile.dmp logfile=data_pump_dir:logfile.log schemas=SCHEMA flashback_time=\"to_timestamp

('timestamp', 'yyyy-mm-dd hh24:mi')\" 

The import process would use syntax like …

    imp  username/password file=t.dmp  fromuser=xxx touser=xxx 
or
    impdp username/password directory=data_pump_dir dumpfile=DUMPFILE.DMP remap_schema=SCHEMA:SCHEMA 

In this instance, I’m remapping the schema to another schema, so I can keep both in play for comparison purposes. You may choose to overwrite it instead.

other way(only flashback table)

The following pseudocode is a simple implementation of Flashback Schema :

declare
cursor c is all enable constraints foreign key;
cursor c is all disable constraints foreign key;
cursor c is all table disable row movement;
cursor c is all table enable row movement;

begin
open c;
loop

— disable fk
— enable table row movement;
— flashback table to timestamp
— disable table row movement;
— enable fk
end loop;

loop

打赏

微信扫一扫，打赏作者吧～