3.Oracle Flash back feature (Flashback Query, Flashback Table)

Reprinted from: 6112607

An Oracle flashback query is a query that queries a particular table for data changes over a specific time period to determine whether to flash the table back to a particular moment to ensure that the data does not corruption.
This feature greatly reduces the amount of effort required to recover at point-in-time, and how long the database takes offline. Flashback queries are typically divided into Flashback query (flashback queries), Flashback table query (Flash-back tables), Flashback version query (flash back), Flashback Transaction Query (Flashback transaction query). This article focuses on Flashback query (flashback queries), Flashback table query (Flash-back form queries). The remaining flash reciprocate refer to subsequent articles.

One, Flashback query (Flash back)

Typically used to retrieve all versions of a record, to reverse a single transaction or to reverse all changes to a particular table since the specified time flashback the all forms of the query depend on the undo table space, for Udno tablespace refer to: Oracle rollback (ROLLBACK) and undo (                            UNDO)--1. Flash back query (Flashback query) syntax SELECT <column_name_list>from <table_name>as of <SCN> --use as of Scn[where <filter_conditions>][group by <unaggregated columns>][having <group_fi Lter>][order by <column_positions_or_name>]select <column_name_list>from <table_name>AS of < Timestamp>--using as of Timestamp[where <filter_conditions>][group by <unaggregated columns& gt;] [Having <group_filter>] [ORDER by <column_positions_or_name>]--2. Demo Flashback query A. Demo using as of timestamp for flashback query [email protected]> Create    Table TB1 as select Empno,ename,job,deptno from Scott.emp where 1=0; [email protected]> INSERT INTO TB1 2 Select Empno,ename,job,deptno 3 from Scott.emp where Empno in (73    69,7499,7521,7566);    [email protected]> commit;[email protected]> select * from TB1;             EMPNO ename JOB DEPTNO---------------------------------------7369 SMITH Clerk      7499 ALLEN salesman 7521 WARD salesman 7566 JONES    MANAGER [email protected]> Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual; To_char (sysdate, ' YY '-------------------2010-10-25 17:26:08 [email protected]> Delete from tb1 where job    = ' salesman ';    [email protected]> commit;         [email protected]> select * from TB1;             EMPNO ename JOB DEPTNO---------------------------------------7369 SMITH Clerk 7566 JONES MANAGER [email protected]> select * from tb1 as of Timestam         P 2 to_timestamp (' 2010-10-25 17:26:08 ', ' yyyy-mm-dd hh24:mi:ss ');           EMPNO ename JOBDEPTNO---------------------------------------7369 SMITH clerk 7499 ALLEN        Salesman 7521 WARD salesman 7566 JONES MANAGER 20 [email protected]> SELECT * from TB1 as of timestamp 2 to_timestamp (' 2010-10-25 17:26:08 ', ' Yyyy-mm-dd hh24:m         I:ss ') 3 minus select * from TB1;          EMPNO ename JOB DEPTNO---------------------------------------7499 ALLEN salesman 7521 WARD salesman 30b. Demo using as of SCN for flashback query [email protected]> Select current_s    CN from V$database;         CURRENT_SCN-----------2032782 [email protected]> select * from TB1;             EMPNO ename JOB DEPTNO---------------------------------------7369 Henry Clerk 7566 JONES MANAGER [email protected]> Delete from TB1 where empno=7369;    [email protected]> commit;         [email protected]> SELECT * from tb1 as of SCN 2032782;             EMPNO ename JOB DEPTNO---------------------------------------7369 Henry Clerk 7566 JONES MANAGER 20 from the above, we can obtain the required record information through a flashback query, then construct a new DML statement and implement its operation to ensure the integrity of the data.

Second, Flashback table query (flash back)

Second, the Flashback table query (Flash-back) queries the undo segment to extract all the changed record details, and then constructs and executes a statement that can reverse these changes to the table flashback by performing a regressive change in the statement and the execution is a transaction, All common rules work on the transaction. When the table is flashed back, the trigger on the table is disabled by default, that is, the DML trigger on the table is temporarily invalidated and you can specify whether the trigger is invalidated on flashback. The table flashback needs to enable the record Transfer option on the table--1. Here's how to get the table Flashback Flashback table <schema_name.table_name>to SCN <scn_number>-SCN-based Table Flash back [<enable | disable> triggers]flashback Table <schema_name.table_name>to TIMESTAMP <timestamp>--based on TIMESTAMP's Flash back [ <enable | disable> triggers]flashback TABLE <schema_name.table_name>to restore Point <restore_point>-based on restore Table flashback for Point [<enable |          Disable> triggers]--2. Demonstration of SCN-based table flashback the following demo first creates the table tb_tables and inserts data into the table several times, recording its SCN number for subsequent flash back to the CREATE table Tb_emp as, before completing the insert --Create Demo table Tb_empselect Empno,ename,job,deptno from scott.emp where 1=0;select table_name,row_movement from User_tables;                         --View the ROW movement behavior of the table, default to Disabletable_name row_move--------------------------------------tb_emp Disabledselect Current_scn,systimestamp from V$database; --Get the system current SCNCURRENT_SCN systimestamp-------------------------------------------------661490 01-jan-11 10.56.28.733000 PM +08:00insert into Tb_emp--insert DEPTNO 10 employee Select Empno,ename,job,deptno from Scott.emp where DEP  Tno=10;commit;select Current_scn,systimestamp from V$database; --Get the system current SCNCURRENT_SCN systimestamp-------------------------------------------------661510 01-jan-11 10.56.56.546000 PM +08:00insert into Tb_emp--insert DEPTNO 20 employee Select Empno,ename,job,deptno from Scott.emp where dept  No=20;commit;select Current_scn,systimestamp from V$database; --Get the system current SCNCURRENT_SCN systimestamp-------------------------------------------------661521 01-jan-11 10.57.17.358000 PM +08:00 INSERT INTO tb_emp--insert DEPTNO 30 employee Select Empno,ename,job,deptno from Scott.emp where dept No=30;commit;select Current_scn,systimestamp from V$database; --Get the system current SCNCURRENT_SCN systimestamp-------------------------------------------------661539 01-JAN-11 10.57.37.843000 PM +08:00select deptno,count (*) from TB_EMP GROUP by deptno ORDER by 1; DEPTNO COUNT (*)--------------------3 5 6flashback Table Tb_em    p to SCN 661521; --flash the table back to the SCN, that is, before inserting a record of the department number flashback table Tb_emp to SCN 661521--flashback failed, received an error prompt, did not open row movement *error at Line 1:ora-08189:cannot flashback the table because row movement are not enabled ALTER TABLE tb_emp enable row movemen   T    --Open the row movement function of the Table Tb_emp table Flashback table Tb_emp to SCN 661521;  --Re-implement flashback, Flash back success Select Deptno,count (*) from TB_EMP GROUP by deptno ORDER by 1; --No record of 30 records DEPTNO COUNT (*)--------------------3 5flashback table Tb_emp    to SCN 661510;  --flash the table back to the SCN, i.e. insert the record of the department number 20 before select Deptno,count (*) from TB_EMP GROUP by deptno ORDER by 1; DEPTNO COUNT (*)--------------------10 3--3 for records that do not have a department 20. Demonstrates timestamp-based table flashback using the to timestamp for table flashback, continuing to make With the above createdTable to flash back-use timestamp to flash back the table to insert Department Number 10 before flashback table Tb_emp to timestamp to_timestamp (' 01-jan-11 10.56.28.733000     ‘); Flashback table Tb_emp to timestamp to_timestamp (' 01-jan-11 10.56.28.733000 ')--Receive error message * * error at L  Ine 1:ora-01466:unable to read data-table definition had changed-table structure changed [email protected]> flashback  Table Tb_emp to SCN 661539; --You can flash the table back to the record inserted in the department number 30 and Flashback complete. The error is received in the demo here, note that for the table flashback, you can use the same type of flashback multiple times, you can go forward, once the forward flash back, you can also back to the flashback. However, the cross-flashback hints that the table definition has changed. Flash back failed. We can recreate a similar new table for the flashback based on timestamp, unlike the flashback SCN, where the timestamp is used, as this demonstration is omitted here. --4. To demonstrate a restore point-based table flashback based on restore point table flashback the first thing to create the appropriate flashback points is to create a RESTORE points point_name; for the flash back to succeed,  The useless flashback point can be deleted in time, and the way to delete the Flash back point is: drop table Tb_emp Purge is presented under the point_name of the restore points flash-back under drops restore --Delete previously created table Tb_empcreate table Tb_emp--Create demo table tb_empenable row Movementas Select Empno,ename,job,deptno from Scott.emp whe   Re 1=0;create restore point Zero; --Create a flashback point Zeroinsert into Tb_eMP--Insert DEPTNO 10 employee Select Empno,ename,job,deptno from scott.emp where deptno=10;commit;create restore point one; --Create flashback point Oneinsert into Tb_emp--insert Deptno for 20 employee select Empno,ename,job,deptno from Scott.emp where Deptno=20;co    Mmit;create restore point; --Create flashback point Twoinsert into Tb_emp--insert Deptno for 30 employee select Empno,ename,job,deptno from Scott.emp where deptno=30;commit    ; Select Deptno,count (*) from TB_EMP GROUP by deptno ORDER by 1; DEPTNO COUNT (*)--------------------3 5 6flashback Table Tb_em   p to restore point;   --Flash back to the flashback point before the Select Deptno,count (*) from the Tb_emp group by Deptno ORDER by 1;flashback table Tb_emp to restore points one;    --Flash back to Flashback point one before select Deptno,count (*) from TB_EMP GROUP by deptno ORDER by 1;   DEPTNO COUNT (*)--------------------3 drop restore point; --Delete the created Flashback points drop restore point one;drop-restore, the table flash back of the reference relationshipThere is a foreign key relationship between table tb1 and table TB2 in Account flasher, table Tb1 Deptno Referring to the Deptno column account in table TB2 flasher the table tb1 and scott.emp have the same table structure, table TB2 has the same table structure as table scott.dept The following is a new TB2 for table Deptno and a new record for table TB1 create Table TB1--Create tables based on table scott.emp tb1enable row Movementas select * from scott.emp; CREATE table TB2-based on table scott.dept To create a table tb2enable row movementas select * from Scott.dept;alter table TB1--Add PRIMARY KEY constraint for table TB1 add constraint tb1_empno_pk Prim        ary Key (EMPNO); ALTER TABLE TB2--Add a PRIMARY KEY constraint for the TB2 of a table add constraint TB2_DEPTNO_PK primary key (DEPTNO); ALTER TABLE TB1 --Add a FOREIGN KEY constraint to the table TB1 Add constraint TB1_TB2_DEPTNO_FK foreign key (DEPTNO) references TB2 (DEPTNO); insert into TB2--insert one for table TB2 A new department Select, ' Customer ', ' Landon ' from Dual;insert to TB1 (EMPNO,ENAME,JOB,DEPTNO)--Insert a new employee for table TB1 Select 8000, '   Robinson ', ' Clerk ', from Dual;commit;select CURRENT_SCN to V$database;     --Obtain the current SCN---687444delete from tb1 where empno=8000;      --Delete the previously added department delete from TB2 where deptno=50;    --Delete the previously added Employee Commit;flashback table tb1 to SCN 687444; --Flash back to previously deleted employees  /*error at line 1:ora-02091:transaction rolled back--prompting the transaction to rollback, foreign key not found Ora-02291:integrity constraint (flasher.   TB1_TB2_DEPTNO_FK) violated-parent key not found */flashback table tb1,tb2 to SCN 687444;      --Flash Two tables back to select Empno,ename,deptno,dname at the same time--the new employee was flashed back and the department was also flashed back from the Tb1inner join TB2 using (DEPTNO) where deptno=50; EMPNO ename DEPTNO dname--------------------------------------------8000 Robinson Omer--6. Several common problems with table Flashback A. If a key value, such as a primary key, is reused before the flashback delete operation, the primary key constraint is violated and the flashback fails. B. If the undo information required for the flashback does not exist, the Ora-08180:no snapshot found based on specified time (no snapshot based on the specified period is found) Error C. If the record affected by the flashback is locked by another user, Ora-00054:resource busy and acquire with NOWAIT specified (resource busy) will be raised Error D. Table definition cannot change during flashback, otherwise causes ora-01466:unable to read data-table definition have changed (table definition has changed) error e. Row movement was not enabled before the flash-back, You will receive Ora-08189:cannot flashback the table because row movement is not enabled error F. For cases where a reference relationship exists, it is recommended that the primary table be implemented together with a flashback, otherwise you will receive a ORA-02091 : Transaction rolled back,ora-02291 Error in G.sys mode table cannot use the table flashback technique

　　

3.Oracle Flash back feature (Flashback Query, Flashback Table)