[Oracle] Flashback flash back Mechanism

[Oracle] the Flashback flash back mechanism Flashback is intended to be difficult if you have misperformed data and submitted the data before it has been Flashback. Some people may say that the backup can be used to restore the system to a previous error, but the correct operation data is also lost. The only possible method is log mining, but log mining is complicated and difficult to locate. Therefore, Oracle has launched the Flashback Technology to restore misoperation. Flashback: Flashback Database Table level: Flashback Drop, Flashback Table, and Flashback Data Archive record level: invalid Version Query and Flashback Transaction Query Flashback family Flashback Log Tablespace Recycle Bin UNDO function Flashback Database Yes rollback Database Flashback Drop Yes restore tables accidentally deleted Flashback Version Query Yes restore misoperation data Flashback Transaction Query yes same as Flashback Table Yes same as Flashback Technology Development History Flashback Version/Transac Tion Query and Flashback TableFlashback Query and Flashback Table both use undo to implement rollback. When you need to flash back to a certain time point in the past, use Flashback Query to Query and confirm the returned SCN or Timestamp, then, use the Flashback Table to implement Flashback. 1. 9i Flashback Query9i Flashback Query can Query the object status at a certain time point in the past. The test is as follows: a105024 @ O02DMS1> alter session set nls_date_format = 'yyyy-mm-dd: hh24: mi: ss'; A105024 @ O02DMS1> select sysdate from dual; SYSDATE-------------------2011-12-16: 02: 51: there is a piece of data in the 16test table: A105024 @ O02DMS1> select * from test; ID ---------- 1 delete the data: A105024 @ O02DMS1> delete from test; A105024 @ O02DMS1> commit; in this case, the test table has no data: A105024 @ O02DMS1> select * from t Est; no rows selected, but you can use the Flashback Query to Query and delete the previous data: A105024 @ O02DMS1> select * from test as of timestamp to_timestamp ('2017-12-16: 02: 51: 16 ', 'yyyy-mm-dd: hh24: mi: ss'); ID ---------- 1 data can be recovered if necessary: a105024 @ O02DMS1> insert into test select * from test as of timestamp to_timestamp ('2017-12-16: 02: 51: 16', 'yyyy-mm-dd: hh24: mi: ss'); A105024 @ O02DMS1> commit; 2. the new Version Query introduced by Flashback Version Query10g can be seen in a certain period of time. Data changes, that is, the evolution history of the data. For this reason, 10 Gb also introduces a new pseudo column ORA_ROWSCN: records the SCN of the last modification of the data. A105024 @ O02DMS1> create table test (id number, name varchar (10); A105024 @ O02DMS1> insert into test values (1, 'first'); A105024 @ O02DMS1> commit; a105024 @ O02DMS1> update test set name = 'second' where id = 1; A105024 @ O02DMS1> commit; A105024 @ O02DMS1> update test set name = 'third' where id = 1; a105024 @ O02DMS1> commit; we created a test table, inserted a piece of data, and updated the data twice. The following describes how to use Version Query to obtain the data evolution history: a105024 @ O02DMS1> select versions_xid, versions _ Startscn, versions_endscn, versions_operation, id, name from test versions between scn minvalue and maxvalue where id = 1; VERSIONS_XID VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_OPERATION id name ---------------- ----------------- ------------ 12000900D0720000 6629455993 U 1 Jun 6629455988 U 1 Jun 6629455993 66 29455988 I 1 first looks at the data from the bottom up, which exactly corresponds to our previous operation history on the data. From the black line above, we can see that during the period of SCN 6629455888 and 6629455993, the data status is (1, second), and the corresponding operation is update. 3. Flashback Transaction QueryTransaction Query allows you to view the Transaction evolution history. To use this function, you need to access the flashback_transaction_query view. A105024 @ O02DMS1> create table test (id number); start the first transaction: A105024 @ O02DMS1> insert into test values (1 ); a105024 @ O02DMS1> update test set id = 11 where id = 1; A105024 @ O02DMS1> commit; start the second transaction: A105024 @ O02DMS1> insert into test values (2 ); a105024 @ O02DMS1> update test set id = 22 where id = 2; A105024 @ O02DMS1> commit; view the history of transactions in the prepare view: A105024 @ O02DMS1> select XID, OPERATION, COMMIT_SCN, UNDO_ SQL from flas Hback_transaction_query where xid in 2 (select versions_xid from test versions between scn minvalue and maxvalue); xid operation COMMIT_SCN UNDO_ SQL -------------- ---------- explain when UPDATE 6629456273 update "A105024 ". "TEST" set "ID" = '2' where ROWID = 'acsnzaaeaaabdnab'; 2017d000c720000 INSERT 6629456273 delete f Rom "A105024 ". "TEST" where ROWID = 'acsnzaaeaaabdnab'; 2017d000c720000 BEGIN 66294562730C0002002A720000 UPDATE 6629456260 update "A105024 ". "TEST" set "ID" = '1' where ROWID = 'aacsnzaaeaaabsna'; 0C0002002A720000 INSERT 6629456260 delete from "A105024 ". "TEST" where ROWID = 'acsnzaaeaaabsna'; 0C0002002A720000 BEGIN 6629456260 4. based on the Evolution History of the Flashback Query, the Flashback Table can determine the time point for rollback and then use Flashb. The ack Table function implements rollback. Note: You must enable row movement before rollback. Flashback DropFlashback drop is a function available from Oracle 10 Gb. The principle is that each tablespace has a logical region of the strict recycle bin. When it is dropped, the deleted table and its associated objects are not physically deleted, it is only transferred to the recycle bin to provide users with a possibility of recovery. Note the following when using Flashback drop: 1. invalid system tablespace 2. the sqlplus version cannot be less than 10 Gb; otherwise, many commands cannot use the following test: SQL> create table test as select * from dba_objects; SQL> drop table test; SQL> show recyclebin; original name recyclebin name object type drop time ------------------ hour ------------------------- test bin $ S5L + hour = $0 TABLE 2011-12-15: 22: 38: 30SQL> create table test as select * From dba_objects where 2 = 1; SQL> drop table test; SQL> show recyclebin; original name recyclebin name object type drop time ------------------ hour ------------------------- test bin $ XHwrz1OaQaSeq/NQIE85hw = $0 TABLE 2011-12-15: 22: 39: 34 test bin $ S5L + aNpzQmOScn8VfpJBAA = $0 TABLE 2011-12-15: 22: 38: 30 now there are two test tables in recyclebin to check the data count of the two tables: SQL> select count (*) from "BIN $ XHwrz1OaQa Seq/NQIE85hw = $0 "; COUNT (*) ---------- 0 SQL> select count (*) from" BIN $ S5L + aNpzQmOScn8VfpJBAA = $0 "; COUNT (*) ---------- 1000000 assume that you want to restore the last table: SQL> flashback table "BIN $ S5L + aNpzQmOScn8VfpJBAA = $0" to before drop; Flashback complete. SQL> select count (*) from test; COUNT (*) ---------- 1000000 assume that you want to restore the previous table and rename it test2: SQL> flashback table "BIN $ XHwrz1OaQaSeq/NQIE85hw ===$ 0" to before drop rename to test2; Flas Hback complete. SQL> select count (*) from test2; COUNT (*) ---------- 0 Flashback Database1. configure Flashback Database1) the Flashback function is disabled by default: SQL> select name, flashback_on from v $ database; NAME FLASHBACK_ON --------- ---------------- O01DMS0 NO2) Configure Flash recovery area: SQL> alter system set lifecycle = 2G scope = both; SQL> alter system set db_recovery_file_dest = 'H: \ flashback 'scope = both; 3) Enable Flashb Ack Database function: SQL> shutdown immediateSQL> startup mountSQL> alter database flashback on; SQL> select name, flashback_on from v $ database; NAME FLASHBACK_ON --------- define O01DMS0 YES4) settings: SQL> alter system set db_flashback_retention_target = 1440 scope = both; 5) open the database: SQL> alter database open; 2. flashback Database operation 1) simulate data loss: SQL> create table test as select * from Dba_objects; Table created. SQL> select count (*) from test; COUNT (*) ---------- 10318 SQL> truncate table test; Table truncated. SQL> select count (*) from test; COUNT (*) ---------- 0 2) determine the earliest time when the recovery time point can be rolled back, depending on the number of Flashback database logs retained, you can view from v $ flashback_database_log: SQL> select to_char (latency, 'yyyy-mm-dd hh24: mi: ss') from v $ flashback_database_log; TO_CHAR (OLDEST_FLAS ------------------- 2011 02:41:48 3) restore data to the specified time point SQL> shutdown immediate; SQL> startup mount; SQL> flashback database to timestamp to_timestamp ('2017-12-15 02:43:00 ', 'yyyy-mm-dd hh24: mi: ss'); Flashback complete. after the recovery is successful, it is best to open the database in readonly mode to confirm that the recovery has reached the expected level. If it has not reached the expected level, you can restore the database again: SQL> alter database open read only; database altered. SQL> select count (*) from test; select count (*) from test * ERROR at line 1: ORA-00942: table or view Does not exist SQL> shutdown immediate; SQL> startup mount; SQL> flashback database to timestamp to_timestamp ('2017-12-15 02:49:00 ', 'yyyy-mm-dd hh24: mi: ss '); Flashback complete. SQL> alter database open read only; Database altered. SQL> select count (*) from test; COUNT (*) ---------- 103184) after the database is successfully restored, open the database in resetlog mode: SQL> shutdown immediate; SQL> startup mountSQL> alter database open resetlogs; Flashb Ack Data ArchiveOracle11g brings another new member to the flashback family: flashback data archive. It seems that flashback data archive and flashback query are not much different. They can query previous data through the as of, but their implementation mechanisms are different. Flashback query constructs old data by Directly Reading information from undo, so there is a restriction that the information in undo cannot be overwritten. While the undo segment is used cyclically. As long as the transaction is committed, the previous undo information may be overwritten. Although undo_retention and other parameters can be used to prolong the survival of undo, however, this parameter affects all transactions. setting too large may result in rapid expansion of undo tablespace. The Falshback data archive feature saves the changed data to the created flashback archive and distinguishes it from undo. In this way, you can set a survival policy for the flashback archive, this allows you to return to the old data before the specified time without affecting the undo policy. In addition, you can specify which database objects need to save historical change data as needed, rather than saving the changed data of all objects in the database, which can greatly reduce the space requirements.