Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to achieve flashback technology in Oracle12C. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

1.Oracle Flashback Query-flashback query

Based on undo information, ORACLE uses undo data, similar to a consistent read method, to place the table at a point in time (or SCN) before deletion, thus retrieving the data.

Flashback query (flashback query) premise:

Sys@CLONEPDB_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 automatic management

Undo_retention = n (seconds), the setting determines the maximum save time for undo, and the higher the value, the more undo tablespace support is required. The command to modify undo_retention is as follows:

Sys@CLONEPDB_PLUGPDB > alter system set undo_retention = 3600

System altered.

Elapsed: 00:00:00.06

Sys@CLONEPDB_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:

Sys@CLONEPDB_PLUGPDB > select to_char (sysdate, 'yyyy-mm-dd hh34: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

Sys@CLONEPDB_PLUGPDB > conn scott/tiger@clonepdb_plug

Connected.

Scott@CLONEPDB_PLUGPDB > select count (*) from emp

COUNT (*) 12

Elapsed: 00:00:00.03

Scott@CLONEPDB_PLUGPDB > delete from emp

12 rows deleted.

Elapsed: 00:00:00.04

Scott@CLONEPDB_PLUGPDB > commit

Commit complete.

Elapsed: 00:00:00.01

Scott@CLONEPDB_PLUGPDB > select to_char (sysdate, 'yyyy-mm-dd hh34:mi:ss') time, to_char (dbms_flashback.get_system_change_number) scn from dual

Select to_char (sysdate, 'yyyy-mm-dd hh34: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 data at this point in time (or scn) as follows:

Scott@CLONEPDB_PLUGPDB > select count (*) from emp

COUNT (*) 0

Elapsed: 00:00:00.01

Scott@CLONEPDB_PLUGPDB > select from emp as of timestamp to_timestamp ('2018-01-17 14-14 yyyy-mm-dd hh34:mi:ss' 16-00);-- 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 800 20 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 7566 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 7698 BLAKE MANAGER 78391981-05-01 00:00:00 2850 30 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 7900 JAMES CLERK 7698 2450 12-03 00:00:00 950 30 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10

12 rows selected.

Elapsed: 00:00:00.15

Restore

Scott@CLONEPDB_PLUGPDB > insert into emp (select * from emp as of scn 5409876);-- it can also take time

12 rows created.

Elapsed: 00:00:00.04

Scott@CLONEPDB_PLUGPDB > commit

Commit complete.

Elapsed: 00:00:00.01

Scott@CLONEPDB_PLUGPDB > select count (*) from emp

COUNT (*) 12

Limitations:

| cannot Falshback data from 5 days ago.

| the flashback query cannot be restored until the table structure is changed, because the flashback query uses the current data dictionary.

| due to the influence of undo_retention parameters, Flashback does not guarantee the success of Flashback for data before undo_retention.

| rollback operations such as drop,truncate are not recorded and cannot be resumed.

| ordinary users use dbms_flashback packages, which must be authorized by the administrator.

2.Oracle Flashback Drop Table flashback Drop drop table

Scott@clonepdb_plugPDB > show recyclebin

Scott@clonepdb_plugPDB > select * from test

No rows selected

Elapsed: 00:00:00.00

Scott@clonepdb_plugPDB > drop table test

Table dropped.

Elapsed: 00:00:00.06

Scott@clonepdb_plugPDB > show recyclebin

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

TEST BIN$lHb2N8coS86p8/1o8xr29A==$0 TABLE 2018-01-18 15 14 26 15 16

Scott@clonepdb_plugPDB > create table test as select * from emp where rownum select count (*) from test

COUNT (*) 1

Elapsed: 00:00:00.01

Scott@clonepdb_plugPDB > drop table test

Table dropped.

Elapsed: 00:00:00.03

Scott@clonepdb_plugPDB > show recyclebin

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

TEST BIN$Kqeo3hZ/RA+w3PdIGKlt6Q==$0 TABLE 2018-01-18 purge 15 purl 27 purl 11

TEST BIN$lHb2N8coS86p8/1o8xr29A==$0 TABLE 2018-01-18 15 14 26 15 16

Scott@clonepdb_plugPDB > flashback table test to before drop

Flashback complete.

Elapsed: 00:00:00.06

Scott@clonepdb_plugPDB > select count (*) from test

COUNT (*) 1

Elapsed: 00:00:00.03

Scott@clonepdb_plugPDB > show recyclebin

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

TEST BIN$lHb2N8coS86p8/1o8xr29A==$0 TABLE 2018-01-18 15 14 26 15 16

Scott@clonepdb_plugPDB > drop table test

Table dropped.

Elapsed: 00:00:00.04

Scott@clonepdb_plugPDB > show recyclebin

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

TEST BIN$WZphkGyLQjqqgTNlaFN6jA==$0 TABLE 2018-01-18 1514 2915 52

TEST BIN$lHb2N8coS86p8/1o8xr29A==$0 TABLE 2018-01-18 15 14 26 15 16

Scott@clonepdb_plugPDB > flashback table "BIN$WZphkGyLQjqqgTNlaFN6jA==$0" to before drop

Flashback complete.

Elapsed: 00:00:00.02

Scott@clonepdb_plugPDB > select count (*) from test

COUNT (*) 1

Elapsed: 00:00:00.03

Scott@clonepdb_plugPDB > drop table test

Table dropped.

Elapsed: 00:00:00.03

Scott@clonepdb_plugPDB > show recyclebin

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

TEST BIN$Avlh8rB/Q22J0WciRhx58g==$0 TABLE 2018-01-18 purge 15 purl 30 purl 43

TEST BIN$lHb2N8coS86p8/1o8xr29A==$0 TABLE 2018-01-18 15 14 26 15 16

Scott@clonepdb_plugPDB > flashback table "BIN$lHb2N8coS86p8/1o8xr29A==$0" to before drop

Flashback complete.

Elapsed: 00:00:00.02

Scott@clonepdb_plugPDB > show recyclebin

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

TEST BIN$Avlh8rB/Q22J0WciRhx58g==$0 TABLE 2018-01-18 purge 15 purl 30 purl 43

Scott@clonepdb_plugPDB > select count (*) from test

COUNT (*) 0

Scott@clonepdb_plugPDB > flashback table "BIN$Avlh8rB/Q22J0WciRhx58g==$0" to before drop rename to test_new

Flashback complete.

Elapsed: 00:00:00.02

Scott@clonepdb_plugPDB > show recyclebinscott@clonepdb_ plug [PDB] (10.8.5.204-12.2) >

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.