1 Overview

• In order to recover Oracle database from misoperation quickly, Oracle has introduced flashback technology.
• This blog mainly shares the flashback query, flashback transaction query, flashback version query, flashback table and flashback deletion in the flashback technology. For the Flashback Database and flashback file, the impact range is too wide and it is generally not recommended to use.

Query current system timestamp and SCN

SELECT to_char(SYSDATE, 'YYYY-MM-HH hh24:mi:ss:mm') timestamp,
       timestamp_to_scn(SYSDATE) scn
  FROM dual;

 

2 example description

2.1 flashback query

• Function: query the data before a certain time in the past
• Note: commit time

Syntax:

select *
  from table_name
       [as of timestamp | scn expression]
[where condition]

 

Basic data: insert 3 records and flash back query

CREATE TABLE flashback_select_test (
  ID          NUMBER,
  NAME        VARCHAR2(30),
  create_date DATE,
  create_scn  NUMBER
);
-- Set the time format for easy viewing
ALTER SESSION SET nls_date_format = 'YYYY/MM/DD HH24:MI:SS';
-- insert Statement, the execution interval is a few seconds, which is convenient for testing
INSERT INTO flashback_select_test
   (id, NAME, create_date, create_scn)
VALUES
   (1, 'a', SYSDATE, timestamp_to_scn(SYSDATE));
COMMIT;
INSERT INTO flashback_select_test
   (id, NAME, create_date, create_scn)
VALUES
   (2, 'b', SYSDATE, timestamp_to_scn(SYSDATE));
COMMIT;
INSERT INTO flashback_select_test
   (id, NAME, create_date, create_scn)
VALUES
   (3, 'c', SYSDATE, timestamp_to_scn(SYSDATE));
COMMIT;

 

Data validation:

SELECT * FROM flashback_select_test;
SELECT * FROM flashback_select_test AS OF TIMESTAMP to_timestamp('2020/8/3 22:21:04', 'YYYY/MM/DD HH24:MI:SS');
SELECT * FROM flashback_select_test AS OF SCN 12614250;

 

Query screenshot:

2.2 flashback version query

• When recording changes, the commit time happens to be recorded, which can be queried by flashing back (generally, we don't specifically record this time)
• If it is not recorded, use the flashback version to query
• Function: you can query all submitted records

Syntax:

select *
  from table_name
versions between scn expr | timestamp minvalue
             and expr | maxvalue [as of scn | timestamp expr];
1. between ... and: time slot
2. scn | timestamp: Time format(System change number | time stamp)
3. minvalue | maxvalue: Time value(minimum value | Maximum)
4. Available pseudo columns
  (1) versions_starttime : Lower bound of valid range of version based on time
  (2) versions_startscn  : be based on SCN Lower bound of valid range of version
  (3) versions_endtime   : Upper bound of time-based version valid range
  (4) versions_endscn    : be based on SCN Upper bound of valid range of version
  (5) versions_xid       : Operational transactions ID
  (6) versions_operation : Type of operation performed. I: insert,U: delete,D: DELETE

 

Demonstration: (when the DML statement is executed, the interval is a few seconds for easy observation)

CREATE TABLE flashback_versions_test (
  ID   NUMBER,
  NAME VARCHAR2(30)
);
-- The following statements are executed at intervals of several seconds for easy observation
INSERT INTO flashback_versions_test(ID, NAME) VALUES(1, 'Yao Yao');
COMMIT;
INSERT INTO flashback_versions_test(ID, NAME) VALUES(2, 'pleasing');
COMMIT;
UPDATE flashback_versions_test t SET t.name = 'Qianqian 123' WHERE t.id = 2;
COMMIT;
INSERT INTO flashback_versions_test(ID, NAME) VALUES(3, 'gentle');
COMMIT;

 

Flashback version query statement:

SELECT id,
       NAME,
       to_char(versions_starttime, 'YYYY-MM-DD HH24:MI:SS') versions_starttime,
       to_char(versions_endtime, 'YYYY-MM-DD HH24:MI:SS') versions_endtime,
       versions_startscn,
       versions_endscn,
       versions_xid,
       versions_operation
  FROM flashback_versions_test versions BETWEEN TIMESTAMP minvalue AND maxvalue
 ORDER BY versions_starttime;

 

Query results:

2.3 flashback transaction query

• Flashback transaction query is actually an extension of flashback version query, which can audit a transaction or even revoke a committed transaction.

Prerequisite knowledge:

SELECT t.*
  FROM flashback_transaction_query t
 WHERE t.logon_user = 'SYSTEM'; -- The user you are logged in to
-- GRANT SELECT ANY TRANSACTION TO scott; -- Test the user. If the permission is insufficient, authorization is required
-- Add supplemental log (If not undo_sql Empty)
SELECT t.supplemental_log_data_min, t.* FROM v$database t; -- YSE: open
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; -- Open statement

 

Basic data preparation: suppose you forget to bring the where condition when updating

CREATE TABLE flashback_transaction_query_ts (
   ID          NUMBER,
   NAME        VARCHAR2(30),
   create_date DATE
);
-- insert Statement, with an interval of a few seconds for easy observation
INSERT INTO flashback_transaction_query_ts
   (id, NAME, create_date)
VALUES
   (1, 'Little wanderer', SYSDATE);
COMMIT;
INSERT INTO flashback_transaction_query_ts
   (id, NAME, create_date)
VALUES
   (2, 'Xiaoyouzi', SYSDATE);
COMMIT;
INSERT INTO flashback_transaction_query_ts
   (id, NAME, create_date)
VALUES
   (3, 'Xiaoqianzi', SYSDATE);
COMMIT;
-- Suppose I wanted to update it id = 2 Record the result and forget to write the condition where id = 2 
UPDATE flashback_transaction_query_ts t SET t.name = 'Xiaoyouzi 22';
COMMIT;

 

Data validation:

1. First, use the flashback version query to query the transaction id
SELECT id,
       NAME,
       to_char(versions_starttime, 'YYYY-MM-DD HH24:MI:SS') versions_starttime,
       to_char(versions_endtime, 'YYYY-MM-DD HH24:MI:SS') versions_endtime,
       versions_startscn,
       versions_endscn,
       versions_xid,
       versions_operation
  FROM flashback_transaction_query_ts versions BETWEEN TIMESTAMP minvalue AND maxvalue
 ORDER BY versions_starttime;
2. According to transaction id，obtain undo_sql
SELECT t.*
  FROM flashback_transaction_query t
 WHERE t.logon_user = 'SYSTEM'
   AND t.xid = '08001C00AB1F0000';

 

Test results:

-- undo_sql Copy as follows:
1 update "SYSTEM"."FLASHBACK_TRANSACTION_QUERY_TS" set "NAME" = 'Xiaoqianzi' where ROWID = 'AAAVUzAABAAAVxpAAC';
2 update "SYSTEM"."FLASHBACK_TRANSACTION_QUERY_TS" set "NAME" = 'Xiaoyouzi' where ROWID = 'AAAVUzAABAAAVxpAAB';
3 update "SYSTEM"."FLASHBACK_TRANSACTION_QUERY_TS" set "NAME" = 'Little wanderer' where ROWID = 'AAAVUzAABAAAVxpAAA';

 

2.4 flashback table

• Restore the amount data deleted by delete
• Row migration needs to be enabled
• Note undo_retention time in retention

Basic data:

CREATE TABLE flashback_table_test (
  ID   NUMBER,
  NAME VARCHAR2(30)
);
INSERT INTO flashback_table_test (ID, NAME) VALUES(1, 'Yao Yao');
INSERT INTO flashback_table_test (ID, NAME) VALUES(2, 'pleasing');
INSERT INTO flashback_table_test (ID, NAME) VALUES(3, 'gentle');
COMMIT;

 

Flashback table Demo:

-- Query first
SELECT t.*, ROWID FROM flashback_table_test t;
SELECT to_char(SYSDATE, 'YYYY-MM-HH hh24:mi:ss:mm') time,
       timestamp_to_scn(SYSDATE) scn
  FROM dual;

 

-- Delete again
DELETE FROM flashback_table_test t WHERE t.id = 2;
COMMIT;
-- Flashback command start
ALTER TABLE flashback_table_test ENABLE ROW MOVEMENT; -- Open table row movement(Regenerate rowid)
FLASHBACK TABLE flashback_table_test TO SCN 12442453;
-- Flashback command end
SELECT t.*, ROWID FROM flashback_table_test t;

 

2.5 flashback deletion

• Can restore: tables deleted using drop
• Cannot restore: tables under the administrator user (such as system, sys) or truncate d tables

Syntax:

FLASHBACK TABLE table_name 
		TO BEFORE DROP [RENAME TO table_name2] -- Delete the table and modify the table name at the same time
		[ENABLE|DISABLE] triggers; -- Enable while deleting the table|Disable trigger

 

Basic data preparation:

CREATE TABLE scott.flashback_drop_test (
   ID   NUMBER,
   NAME VARCHAR2(30)
);
SELECT * FROM scott.flashback_drop_test;
DROP TABLE scott.flashback_drop_test;

 

Case 1: General flashback

SELECT * FROM dba_recyclebin t WHERE t.owner = 'SCOTT'; -- or user_recyclebin 
FLASHBACK TABLE scott.flashback_drop_test TO BEFORE DROP;

 

Case 2: the table name in the recycle bin already exists in the database (duplicate name):

• Solution: Rename

SELECT * FROM dba_recyclebin t WHERE t.owner = 'SCOTT' ORDER BY t.droptime DESC; -- or user_recyclebin 
Mode 1:
FLASHBACK TABLE scott.flashback_drop_test TO BEFORE DROP RENAME TO flashback_drop_test2;
Mode 2: (recommended, prevented) "A table can be deleted multiple times" (in case of)
-- SELECT * FROM scott."BIN$hr4mmwLmTAyvSvOcqvFO+A==$0";
FLASHBACK TABLE scott."BIN$hr4mmwLmTAyvSvOcqvFO+A==$0" TO BEFORE DROP RENAME TO flashback_drop_test3;

 

Case 3: one table is deleted multiple times

• Please note: the last deleted table is restored

Basic data preparation:

CREATE TABLE scott.flashback_drop_test (
   ID   NUMBER,
   NAME VARCHAR2(30)
);
DROP TABLE scott.flashback_drop_test;
-- The above creation and deletion operations shall be performed at least twice to facilitate demonstration
SELECT * FROM user_recyclebin t ORDER BY t.droptime DESC;

 

demonstration:

FLASHBACK TABLE flashback_drop_test TO BEFORE DROP;

 

3 expansion

1. Show recycle bin( sqlplus (command)
> show recyclebin
2. Empty recycle bin( sqlplus (command)
> purge recyclebin
3. Query recycle bin
select * from dba_recyclebin; -- user_recyclebin
4. Delete table and empty in Recycle Bin
drop table table_name purge;
5. Empty the table data and empty it in the recycle bin
truncate table table_name;