Oracle flashback flash back topic

Oracle flashback flash back topic

1. Introduction to flashback:
Flashback is used at the following frequency:
1> flashback query records that are flashed back to the table for data table record recovery;
2> falshabck Table flashback data Table;
3> flashback drop recycles database tables, used for recovery after tables are accidentally deleted;
4> flashback database flashes back to the database and restores the database to a certain time point.
2. System dictionary table related to flashback:
-- View the recycle bin
SELECT * FROM DBA_RECYCLEBIN order by droptime DESC;
SELECT * from recyclebin order by droptime DESC; -- the current user's recycle bin
-- Query the correspondence between SCN and time
Select scn, to_char (time_dp, 'yyyy-mm-dd hh24: mi: ss') from sys. smon_scn_time order by time_dp DESC;
Select max (SCN) FROM sys. smon_scn_time; -- maximum/latest SCN number
SELECT CURRENT_SCN from v $ DATABASE; -- query the current scn
3. Flashback table records:
/**************************** Flashback query ******** ******************/
-- Restore deleted records in the table
Delete from yag_xtyhxx WHERE yhmc LIKE 'rz % '; -- operation
Select count (*) FROM yag_xtyhxx WHERE yhmc LIKE 'rz % ';
-- Specify the as of timestamp clause for query after the table name
Select count (*) FROM yag_xtyhxx as of timestamp to_date ('2017-06-12 ', 'yyyy-MM-DD hh24: mi: ss') WHERE yhmc LIKE 'rz % ';
-- Specify the as of SCN clause query after the table name
Select count (*) FROM yag_xtyhxx as of scn 12049699 WHERE yhmc LIKE 'rz % ';

-- Disable a table trigger
SELECT * FROM DBA_TRIGGERS WHERE instr (LOWER (trigger_name), 'yag _ xtyhxx')> 0;
-- Alter trigger xxxx disable;
-- Restore a table trigger
-- Alter trigger xxxx enable;
-- Insert data
Insert into YAG_XTYHXX
Select count (*)
FROM YAG_XTYHXX as of timestamp TO_DATE ('2017-06-12 ', 'yyyy-MM-DD hh24: mi: ss'); -- to_date () You can also use to_timestamp ()
-- FROM yag_xtyhxx as of scn 12049699
Where yhmc like 'rz % ';
4. Flashback data table:
/**************************** Flashback table ******** ******************/
Delete from yag_xtyhxx WHERE yhmc LIKE 'rz % '; -- operation
Select count (*) FROM yag_xtyhxx WHERE yhmc LIKE 'rz % ';
-- Activate the row moving feature
Alter table yag_xtyhxx enable row movement;
-- Uses the flash back feature to directly restore to the time point before the deletion.
Flashback table yag_xtyhxx to timestamp to_date ('2017-06-12 ', 'yyyy-MM-DD hh24: mi: ss'); -- time points can be set
5. Flashback drop:
/**************************** Flashback drop ******** ******************/
-- Restore the deleted table
Drop table yag_xtyhxx;
-- Purge TABLE yag_xtyhxx; -- clear the yag TABLE in the recycle bin
Flashback table yag_xtyhxx to before drop;
6. flash back to database:
/**************************** Flashback database ******** ******************/
The following conditions must be met when using flashback database:
1> flashback has been set for the database to flash back to the recovery zone;
Check whether the flash recovery zone is Enabled: SQL> show parameter db_recovery_file;
2> archive is enabled for the database;
Check whether archive is Enabled: archive log list;
3> flashback database is enabled;
Flashback database is disabled by default. Check the flashback database enabling status: select flashback_on from v $ database;
-- Restore with SCN
Flashback database to SCN number;
-- Restore with timestamp
Flashback database to timestamp to_date ('2017-06-12 ', 'yyyy-MM-DD hh24: mi: ss'); -- to_date () You can also use to_timestamp ()

Oracle 11g Flashback Data Archive (flash back Data archiving)

Oracle Flashback flash back Mechanism

Oracle Flashback database

Flashback table quick recovery of accidentally deleted data

Oracle backup recovery: Flashback flash back