ORACLE12C Flash Back Technology

1.Oracle Flashback query-Flash back
Based on the Undo information, Oracle uses the undo data, similar to the consistent read method, to place the table at a point in time (or SCN) before it is deleted to retrieve the data.
Flashback query (Flash back) premise:
[Email protected]_plugpdb> show parameter undo

NAME TYPE VALUE

temp_undo_enabled Boolean FALSE
Undo_management string AUTO
Undo_retention Integer 900
Undo_tablespace string UNDOTBS1
where undo_management = auto, set up automatic management
Undo_retention = n (seconds), setting determines the maximum save time for undo, and the larger the value, the more support for undo tablespace is required. The command to modify Undo_retention is as follows:
[Email protected]_plugpdb> alter system set undo_retention = 3600;

System altered.

elapsed:00:00:00.06
[Email protected]_plugpdb> show parameter undo

NAME TYPE VALUE

temp_undo_enabled Boolean FALSE
Undo_management string AUTO
Undo_retention Integer 3600
Undo_tablespace string UNDOTBS1
Get a point-in-time or SCN before data deletion, as follows:
[Email protected]_plugpdb> Select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss ') time, To_char (dbms_flashback.get_ System_change_number) SCN from dual;

Time SCN

2018-01-17 14:16:00 5409876

elapsed:00:00:00.11
[Email protected]_plugpdb> Conn Scott/[email Protected]_plug
Connected.
[Email protected]_plugpdb> Select COUNT (*) from EMP;

COUNT (*)

    12

elapsed:00:00:00.03
[Email protected]_plugpdb> Delete from EMP;

Rows deleted.

elapsed:00:00:00.04
[Email protected]_plugpdb> commit;

Commit complete.

elapsed:00:00:00.01
[Email protected]_plugpdb> Select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss ') time, To_char (dbms_flashback.get_ System_change_number) SCN from dual;
Select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss ') time, To_char (Dbms_flashback.get_system_change_number) SCN from dual
*
ERROR at line 1:
Ora-00904:dbms_flashback:invalid identifier

elapsed:00:00:00.02
Query the point-in-time (or SCN) data as follows:
[Email protected]_plugpdb> Select COUNT (*) from EMP;

COUNT (*)

     0

elapsed:00:00:00.01
[Email protected]_plugpdb> Select from emp as of timestamp to_timestamp (' 2018-01-17 14:16:00 ', ' Yyyy-mm-dd hh24:mi : SS ');--or select from emp as of SCN 5409876;

 EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO

  7369 SMITH Clerk 7902 1980-12-17 00:00:00 7499 ALLEN salesman       7698 1981-02-20 00:00:00 7521 WARD salesman 7698 1981-02-22 00:00:00  1250 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20         7654 MARTIN salesman 7698 1981-09-28 00:00:00 1250 1400-7698 BLAKE MANAGER       7839 1981-05-01 00:00:00 2850 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 7839 KING President 1981-11-17 00:00:00 5000 10 78 7698 TURNER salesman 1981-09-08 00:00:00 0 7900 JAMES Clerk 7 698 1981-12-03 00:00:00 950 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3 000 20 EMPNO ename JOB MGR hiredate SAL COMM DEPTNO 

  7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10

Rows selected.

elapsed:00:00:00.15
Recovery
[Email protected]_plugpdb> INSERT INTO EMP (SELECT * from emp as of SCN 5409876);--can also use time

Rows created.

elapsed:00:00:00.04
[Email protected]_plugpdb> commit;

Commit complete.

elapsed:00:00:00.01
[Email protected]_plugpdb> Select COUNT (*) from EMP;

COUNT (*)

    12            局限：

| Data that cannot be falshback to 5 days ago.

| The flashback query cannot be recovered until the table structure changes because the flashback query uses the current data dictionary.

| Affected by the Undo_retention parameter, flashback is not guaranteed to flashback success for undo_retention data.

| Operations that do not record rollback, such as drop,truncate, cannot be recovered.

| Ordinary users use the Dbms_flashback package and must be authorized by the Administrator.
2. Flash Back

ORACLE12C Flash Back technology