Oracle12C如何实现闪回技术

发布时间：2021-11-11 16:56:41 来源：亿速云阅读：140 作者：小新栏目：关系型数据库

这篇文章将为大家详细讲解有关Oracle12C如何实现闪回技术，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。

1.Oracle Flashback Query --闪回查询
ORACLE根据undo信息，利用undo数据，类似一致性读取方法，可以把表置于一个删除前的时间点(或SCN)，从而将数据找回。
Flashback query(闪回查询)前提：
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
其中undo_management = auto，设置自动管理
Undo_retention = n(秒),设置决定undo最多的保存时间，其值越大，就需要越多的undo表空间的支持。修改undo_retention的命令如下：
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
获取数据删除前的一个时间点或scn，如下：
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(*)

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
查询该时间点（或scn）的数据，如下：
scott@CLONEPDB_PLUGPDB> select count(*) from emp;

COUNT(*)

Elapsed: 00:00:00.01
scott@CLONEPDB_PLUGPDB> select from emp as of timestamp to_timestamp('2018-01-17 14:16:00', 'yyyy-mm-dd hh34:mi:ss');--或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        500         30
  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         7839 1981-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 1981-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
恢复
scott@CLONEPDB_PLUGPDB> insert into emp (select * from emp as of scn 5409876);--也可以用时间

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(*)

局限：

| 不能Falshback到5天以前的数据。

| 闪回查询无法恢复到表结构改变之前，因为闪回查询使用的是当前的数据字典。

| 受到undo_retention参数的影响，对于undo_retention之前的数据，Flashback不保证能Flashback成功。

| 对drop,truncate等不记录回滚的操作，不能恢复。

| 普通用户使用dbms_flashback包，必须通过管理员授权。

2.Oracle Flashback Drop Table 闪回Drop掉表
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:26:16
scott@clonepdb_plugPDB> create table test as select * from emp where rownum<2;

Table created.

Elapsed: 00:00:00.09
scott@clonepdb_plugPDB> select count(*) from test;

COUNT(*)

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:15:27:11
TEST BIN$lHb2N8coS86p8/1o8xr29A==$0 TABLE 2018-01-18:15:26: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(*)

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:26: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:15:29:52
TEST BIN$lHb2N8coS86p8/1o8xr29A==$0 TABLE 2018-01-18:15:26: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(*)

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:15:30:43
TEST BIN$lHb2N8coS86p8/1o8xr29A==$0 TABLE 2018-01-18:15:26: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:15:30:43
scott@clonepdb_plugPDB> select count(*) from test;

COUNT(*)

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)><br/" rel="nofollow">br/>scott@clonepdb_plug[PDB](10.8.5.204-12.2)><br/按表名闪回，后进先出
按RECYCLEBIN NAME可以任意恢复
可以重命名
sys表不能闪回
闪回后和drop回收站约束在但乱码（外键约束除外），索引不见了
没用undo，其他的都用了
Oracle12C如何实现闪回技术
3.Oracle Flashback Table 闪回表
scott@clonepdb_plugPDB> drop table test purge;

Table dropped.

Elapsed: 00:00:00.05
scott@clonepdb_plugPDB> create table test as select empno,ename,sal from emp;

Table created.

Elapsed: 00:00:00.07
scott@clonepdb_plugPDB> alter table test enable row movement;

Table altered.

Elapsed: 00:00:00.03
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;

TIME SCN

2018-01-18 16:08:56 5535328

Elapsed: 00:00:00.04
scott@clonepdb_plugPDB> drop table test purge
2 ;

Table dropped.

Elapsed: 00:00:00.04
scott@clonepdb_plugPDB> flashback table test to scn 5535328;
flashback table test to scn 5535328
*
ERROR at line 1:
ORA-00942: table or view does not exist

Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> create table test as select empno,ename,sal from emp;

Table created.

Elapsed: 00:00:00.04
scott@clonepdb_plugPDB> alter table test enable row movement;

Table altered.

Elapsed: 00:00:00.02
scott@clonepdb_plugPDB> select * from test;

 EMPNO ENAME             SAL

  7369 SMITH             800
  7499 ALLEN            1600
  7521 WARD             1250
  7566 JONES            2975
  7654 MARTIN           1250
  7698 BLAKE            2850
  7782 CLARK            2450
  7839 KING             5000
  7844 TURNER           1500
  7900 JAMES             950
  7902 FORD             3000

 EMPNO ENAME             SAL

  7934 MILLER           1300

12 rows selected.

Elapsed: 00:00:00.05
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;

TIME SCN

2018-01-18 16:13:45 5536324

Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> delete from test where rownum<3;

2 rows deleted.

Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> commit
2 ;

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;

TIME SCN

2018-01-18 16:14:57 5536483

Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> delete from test where rownum<3;

2 rows deleted.

Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> commit;

Commit complete.

Elapsed: 00:00:00.00
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;

TIME SCN

2018-01-18 16:15:14 5536518

Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> delete from test;

8 rows deleted.

Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> commit;

Commit complete.

Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> flashback table test to scn 5536518;

Flashback complete.

Elapsed: 00:00:00.55
scott@clonepdb_plugPDB> select count(*) from test;

COUNT(*)

Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> flashback table test to scn 5536324;

Flashback complete.

Elapsed: 00:00:00.09
scott@clonepdb_plugPDB> select count(*) from test;

COUNT(*)

Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> flashback table test to scn 5536483;

Flashback complete.

Elapsed: 00:00:00.10
scott@clonepdb_plugPDB> select count(*) from test;

COUNT(*)

Elapsed: 00:00:00.01

purge表不能闪回
闪回不分先后顺序
需要启动行移动alter table test enable row movement;
system表不能闪回
4.FLASHBACK ARCHIVE 闪回归档
sys@clonepdb_plugPDB> create tablespace fda datafile 'C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\fda01.dbf' size 5m;

Tablespace created.

Elapsed: 00:00:00.56
sys@clonepdb_plugPDB> select name from v$datafile;

NAME

C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\SYSTEM01.DBF
C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\SYSAUX01.DBF
C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\UNDOTBS01.DBF
C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\USERS01.DBF
C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\FDA01.DBF

Elapsed: 00:00:00.04
sys@clonepdb_plugPDB> create flashback archive fla1 tablespace fda retention 3 year;

Flashback archive created.

Elapsed: 00:00:00.19

sys@clonepdb_plugPDB> grant flashback archive on fla1 to scott;

Grant succeeded.
scott@clonepdb_plugPDB> select * from test;

 EMPNO ENAME             SAL

  7521 WARD             1250
  7566 JONES            2975
  7654 MARTIN           1250
  7698 BLAKE            2850
  7782 CLARK            2450
  7839 KING             5000
  7844 TURNER           1500
  7900 JAMES             950
  7902 FORD             3000
  7934 MILLER           1300

10 rows selected.

Elapsed: 00:00:00.04
scott@clonepdb_plugPDB> alter table test flashback archive fla1;

Table altered.

Elapsed: 00:00:00.04
scott@clonepdb_plugPDB> alter table test drop column SAL;

Table altered.

Elapsed: 00:00:06.97
scott@clonepdb_plugPDB> select * from test;

 EMPNO ENAME

  7521 WARD
  7566 JONES
  7654 MARTIN
  7698 BLAKE
  7782 CLARK
  7839 KING
  7844 TURNER
  7900 JAMES
  7902 FORD
  7934 MILLER

10 rows selected.

Elapsed: 00:00:00.03
scott@clonepdb_plugPDB> desc test
Name Null? Type

EMPNO NUMBER(4)
ENAME VARCHAR2(10)

scott@clonepdb_plugPDB> truncate table test;

Table truncated.

Elapsed: 00:00:08.37
scott@clonepdb_plugPDB> drop table test;
drop table test
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

Elapsed: 00:00:00.04
scott@clonepdb_plugPDB> insert into test select empno,ename from emp;

12 rows created.

Elapsed: 00:00:00.03
scott@clonepdb_plugPDB> commit
2 ;

Commit complete.

Elapsed: 00:00:00.23
scott@clonepdb_plugPDB> select count(*) from test;

COUNT(*)

Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> select count(*) from test as of timestamp to_timestamp('2018-01-19 07:00:00','YYYY-MM-DD HH24:MI:SS');

COUNT(*)

scott@clonepdb_plugPDB> delete from test;

12 rows deleted.

Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> commit;

Commit complete.

Elapsed: 00:00:00.10
scott@clonepdb_plugPDB> select count(*) from test as of timestamp to_timestamp('2018-01-19 09:40:00','YYYY-MM-DD HH24:MI:SS');

COUNT(*)

scott@clonepdb_plugPDB> insert into test select empno,ename from test as of timestamp to_timestamp('2018-01-19 09:40:00','YYYY-MM-DD HH24:MI:SS');

12 rows created.

Elapsed: 00:00:00.05
scott@clonepdb_plugPDB> commit;

Commit complete.

Elapsed: 00:00:00.04
scott@clonepdb_plugPDB> update test set empno=7969 where empno=7369;
update test set empno=7969 where empno=7369
*
ERROR at line 1:
ORA-55617: Flashback Archive "FLA1" runs out of space and tracking on "TEST" is suspended
SYS扩空间
SQL> alter database datafile 'C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\fda01.dbf' autoextend on next 32M maxsize 2048M;

Database altered.
scott@clonepdb_plugPDB> update test set ename=7969 where empno=7369;

1 row updated.

Elapsed: 00:00:00.00
scott@clonepdb_plugPDB> commit;

Commit complete.

Elapsed: 00:00:00.14
scott@clonepdb_plugPDB> update test set ENAME =(select ename from test as of timestamp to_timestamp('2018-01-19 10:40:00','YYYY-MM-DD HH24:MI:SS') where empno=7369) where empno=7369;

1 row updated.

Elapsed: 00:00:00.04
scott@clonepdb_plugPDB> commit;

Commit complete.

Elapsed: 00:00:00.07
scott@clonepdb_plugPDB> select * from test where empno=7369;

 EMPNO ENAME

  7369 SMITH

Elapsed: 00:00:00.01

sys@newtestCDB> select * from v$flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID

CONTROL FILE 0 0 0 0
REDO LOG 0 0 0 0
ARCHIVED LOG 0 0 0 0
BACKUP PIECE 0 0 0 0
IMAGE COPY 0 0 0 0
FLASHBACK LOG 98.54 0 5 0
FOREIGN ARCHIVED LOG 0 0 0 0
AUXILIARY DATAFILE COPY 0 0 0 0

8 rows selected.
SQL> show parameter DB_RECOVERY_FILE_DEST_SIZE

NAME TYPE VALUE

db_recovery_file_dest_size big integer 300M
sys@newtestCDB> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=2G;

System altered.

Elapsed: 00:00:00.06
SQL> ALTER FLASHBACK ARCHIVE fla1 MODIFY RETENTION 2 YEAR; --更改保留时间

Flashback archive altered.
SQL> ALTER FLASHBACK ARCHIVE fla1 PURGE BEFORE
2 TIMESTAMP(SYSTIMESTAMP - INTERVAL '1' day);
从闪回数据归档FLA1中清除了一天前的所有历史记录数据。通常会在保留时间到期后的第一天执行自动清除。也可以覆盖此设置以进行临时清除。
Flashback archive altered.
scott@clonepdb_plugPDB> col table_name format A10
scott@clonepdb_plugPDB> col owner_name format A10
scott@clonepdb_plugPDB> select table_name,owner_name,status from dba_flashback_archive_tables;

TABLE_NAME OWNER_NAME STATUS

TEST SCOTT ENABLED

Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> alter table test no flashback archive;
alter table test no flashback archive
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive
SQL> grant flashback archive administer to scott;

Grant succeeded.
scott@clonepdb_plugPDB> alter table test no flashback archive;

Table altered.

Elapsed: 00:00:00.03
scott@clonepdb_plugPDB> select table_name,owner_name,status from dba_flashback_archive_tables;

TABLE_NAME OWNER_NAME STATUS

TEST SCOTT DISABLED
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;

TIME SCN

2018-01-19 14:16:51 5757544

Elapsed: 00:00:00.07
scott@clonepdb_plugPDB> truncate table test;

Table truncated.

Elapsed: 00:00:08.32
scott@clonepdb_plugPDB> select * from test as of timestamp to_timestamp('2018-01-19 14:16:51','yyyy-mm-dd hh34:mi:ss');

 EMPNO ENAME

  7369 SMITH
  7499 ALLEN
  7521 WARD
  7566 JONES
  7654 MARTIN
  7698 BLAKE
  7782 CLARK
  7839 KING
  7844 TURNER
  7900 JAMES
  7902 FORD

 EMPNO ENAME

  7934 MILLER

12 rows selected.

Elapsed: 00:00:00.12
scott@clonepdb_plugPDB> insert into table test (select from test as of timestamp to_timestamp('2018-01-19 14:16:51','yyyy-mm-dd hh34:mi:ss'));
insert into table test (select from test as of timestamp to_timestamp('2018-01-19 14:16:51','yyyy-mm-dd hh34:mi:ss'))
*
ERROR at line 1:
ORA-00903: invalid table name

Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> insert into test (select * from test as of timestamp to_timestamp('2018-01-19 14:16:51','yyyy-mm-dd hh34:mi:ss'));

12 rows created.

Elapsed: 00:00:00.06
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;

TIME SCN

2018-01-19 14:23:58 5759385

Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> alter table test rename to test_01;

Table altered.

Elapsed: 00:00:08.36
scott@clonepdb_plugPDB> select table_name,owner_name,status from dba_flashback_archive_tables;

TABLE_NAME OWNER_NAME STATUS

TEST_01 SCOTT ENABLED

Elapsed: 00:00:00.01
scott@clonepdb_plugPDB> drop flashback archive fla1;

Flashback archive dropped.

Elapsed: 00:00:00.04
truncate 表表能恢复
flashback archive administer //授予用户创建，修改或删除闪回回档 flashback archive //授予用户对表进行归档。
可以删除列，truncate 表，rename 表名与11g R1不同
不能drop表
插入不能闪回，删除，更新可以

ORA-55617 直接resize不行建议用autoextend on next 32M maxsize 2048M;

5.Oracle Flashback Version Query 闪回版本查询
Flashback Version Query引入了一些数据表“伪列”，可以提供对数据版本的操作和检索。
scott@CLONEPDB_PLUGPDB> create table test as select empno, ename, sal from emp where rownum<3;

Table created.

Elapsed: 00:00:00.17
scott@CLONEPDB_PLUGPDB> select * from test;

 EMPNO ENAME             SAL

  7369 SMITH             800
  7499 ALLEN            1600

Elapsed: 00:00:00.07
scott@CLONEPDB_PLUGPDB> select versions_xid xid, versions_startscn start_scn, versions_endscn endscn, versions_operation operation, empno from test versions between scn minvalue and maxvalue;

XID START_SCN ENDSCN O EMPNO

                                           7369
                                           7499

Elapsed: 00:00:00.08
scott@CLONEPDB_PLUGPDB> update test set sal=200 where empno=7369;

1 row updated.

Elapsed: 00:00:00.01
scott@CLONEPDB_PLUGPDB> select versions_xid xid, versions_startscn start_scn, versions_endscn endscn, versions_operation operation, empno from test versions between scn minvalue and maxvalue;

XID START_SCN ENDSCN O EMPNO

                                           7369
                                           7499

Elapsed: 00:00:00.03
scott@CLONEPDB_PLUGPDB> commit;

Commit complete.

XID START_SCN ENDSCN O EMPNO

07001E000D070000 5415641 U 7369
5415641 7369
7499

Elapsed: 00:00:00.02
U表示数据修改后的版本数据。如果删除数据，如下操作：
scott@CLONEPDB_PLUGPDB> delete test where empno=7499;

1 row deleted.

Elapsed: 00:00:00.01
scott@CLONEPDB_PLUGPDB> commit;

Commit complete.

Elapsed: 00:00:00.00
scott@CLONEPDB_PLUGPDB> select versions_xid xid, versions_startscn start_scn, versions_endscn endscn, versions_operation operation, empno from test versions between scn minvalue and maxvalue;

XID START_SCN ENDSCN O EMPNO

09000800EE060000 5415674 D 7499
07001E000D070000 5415641 U 7369
5415641 7369
5415674 7499
scott@clonepdb_plugPDB> delete test;

1 rows deleted.

scott@clonepdb_plugPDB> commit;

scott@clonepdb_plugPDB> select versions_xid xid, versions_startscn start_scn, versions_endscn endscn, versions_operation operation, empno from test versions between scn minvalue and maxvalue;

XID START_SCN ENDSCN O EMPNO

01001C005D070000 5415969 D 7369
09000800EE060000 5415674 D 7499
07001E000D070000 5415641 5415969 U 7369
5415641 7369
5415674 7499
scott@clonepdb_plugPDB> SELECT xid, start_scn, commit_scn, operation, logon_user, undo_sql
2 FROM flashback_transaction_query
3 WHERE xid = HEXTORAW('07001E000D070000')
4 /
FROM flashback_transaction_query
*
ERROR at line 2:
ORA-01031: insufficient privileges
sys@clonepdb_plugPDB> grant select on flashback_transaction_query to scott;

Grant succeeded.

Elapsed: 00:00:00.04
sys@clonepdb_plugPDB> conn scott/tiger@clonepdb_plug
Connected.
scott@clonepdb_plugPDB> SELECT xid, start_scn, commit_scn, operation, logon_user, undo_sql
2 FROM flashback_transaction_query
3 WHERE xid = HEXTORAW('07001E000D070000')
4 /
FROM flashback_transaction_query
*
ERROR at line 2:
ORA-01031: insufficient privileges
sys@clonepdb_plugPDB> grant execute on dbms_flashback to scott;

Grant succeeded.

Elapsed: 00:00:00.05
sys@clonepdb_plugPDB> conn scott/tiger@clonepdb_plug
Connected.
scott@clonepdb_plugPDB> exec dbms_flashback.transaction_backout(numtxns=>1,xids=>sys.xid_array('01001C005D070000'));
BEGIN dbms_flashback.transaction_backout(numtxns=>1,xids=>sys.xid_array('01001C005D070000')); END;

ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70
ORA-06512: at line 1
也失败
在cdb建c##scott用户
c##scott@newtestCDB> select count() from emp;

COUNT(*)

Elapsed: 00:00:00.02
c##scott@newtestCDB> create table test as select empno, ename, sal from emp where rownum<3;

Table created.

Elapsed: 00:00:00.25
c##scott@newtestCDB> select * from test;

 EMPNO ENAME             SAL

  7369 SMITH             800
  7499 ALLEN            1600

Elapsed: 00:00:00.05
c##scott@newtestCDB> select versions_xid xid, versions_startscn start_scn, versions_endscn endscn, versions_operation operation, empno from test versions between scn minvalue and maxvalue;

XID START_SCN ENDSCN O EMPNO

                                           7369
                                           7499

Elapsed: 00:00:00.01
c##scott@newtestCDB> update test set sal=200 where empno=7369;

1 row updated.

Elapsed: 00:00:00.02
c##scott@newtestCDB> select versions_xid xid, versions_startscn start_scn, versions_endscn endscn, versions_operation operation, empno from test versions between scn minvalue and maxvalue;

XID START_SCN ENDSCN O EMPNO

                                           7369
                                           7499

Elapsed: 00:00:00.01
c##scott@newtestCDB> commit;

Commit complete.

Elapsed: 00:00:00.01
c##scott@newtestCDB> select versions_xid xid, versions_startscn start_scn, versions_endscn endscn, versions_operation operation, empno from test versions between scn minvalue and maxvalue;

XID START_SCN ENDSCN O EMPNO

080010009B0E0000 5502537 U 7369
5502537 7369
7499
c##scott@newtestCDB> select * from test;

 EMPNO ENAME             SAL

  7369 SMITH             200
  7499 ALLEN            1600

6.闪回数据库
flashback log 快照
+
归档日志（或当前日志)
Oracle12C如何实现闪回技术
逻辑恢复
a.配置归档方式
sys@newtestCDB> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination c:\app\Administrator\virtual\archivelog
Oldest online log sequence 125
Next log sequence to archive 127
Current log sequence 127
b.配置闪回恢复区
sys@newtestCDB> show parameter db_recovery

NAME TYPE VALUE

db_recovery_file_dest string C:\app\Administrator\virtual\F
lashRecovery
db_recovery_file_dest_size big integer 2G
c.配置闪回保留时间
sys@newtestCDB> show parameter db_flashback_retention_target

NAME TYPE VALUE

db_flashback_retention_target integer 1440
1440 单位分钟
d.查询是否启用
sys@newtestCDB> select flashback_on from v$database;

FLASHBACK_ON

YES

Elapsed: 00:00:00.02
select from v$process where pname='RVWR';
或select from v$bgprocess where name=upper('rvwr');

例子：
增加一个表空间，然后闪回
sys@newtestCDB> select * from v$tablespace;

   TS# NAME                           INC BIG FLA ENC     CON_ID

     1 SYSAUX                         YES NO  YES              1
     0 SYSTEM                         YES NO  YES              1
     2 UNDOTBS1                       YES NO  YES              1
     4 USERS                          YES NO  YES              1
     3 TEMP                           NO  NO  YES              1
     0 SYSTEM                         YES NO  YES              2
     1 SYSAUX                         YES NO  YES              2
     2 UNDOTBS1                       YES NO  YES              2
     3 TEMP                           NO  NO  YES              2
     0 SYSTEM                         YES NO  YES              3
     1 SYSAUX                         YES NO  YES              3

   TS# NAME                           INC BIG FLA ENC     CON_ID

     2 UNDOTBS1                       YES NO  YES              3
     3 TEMP                           NO  NO  YES              3
     5 USERS                          YES NO  YES              3
     0 SYSTEM                         YES NO  YES              4
     1 SYSAUX                         YES NO  YES              4
     2 UNDOTBS1                       YES NO  YES              4
     3 TEMP                           NO  NO  YES              4
     5 USERS                          YES NO  YES              4
     6 FDA                            YES NO  YES              4
             sys@newtest[CDB](10.8.5.204-12.2)> 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-22 10:07:05 6730082
sys@newtestCDB> create tablespace abce datafile 'C:\app\Administrator\virtual\oradata\newtest\abcd01.dbf' size 50m;

Tablespace created.

Elapsed: 00:00:01.06
sys@newtestCDB> select * from v$tablespace order by con_id;

   TS# NAME                           INC BIG FLA ENC     CON_ID

     4 USERS                          YES NO  YES              1
     2 UNDOTBS1                       YES NO  YES              1
     0 SYSTEM                         YES NO  YES              1
     5 ABCE                           YES NO  YES              1
     1 SYSAUX                         YES NO  YES              1
     3 TEMP                           NO  NO  YES              1
     1 SYSAUX                         YES NO  YES              2
     2 UNDOTBS1                       YES NO  YES              2
     3 TEMP                           NO  NO  YES              2
     0 SYSTEM                         YES NO  YES              2
     5 USERS                          YES NO  YES              3

   TS# NAME                           INC BIG FLA ENC     CON_ID

     3 TEMP                           NO  NO  YES              3
     2 UNDOTBS1                       YES NO  YES              3
     0 SYSTEM                         YES NO  YES              3
     1 SYSAUX                         YES NO  YES              3
     1 SYSAUX                         YES NO  YES              4
     2 UNDOTBS1                       YES NO  YES              4
     3 TEMP                           NO  NO  YES              4
     5 USERS                          YES NO  YES              4
     6 FDA                            YES NO  YES              4
     0 SYSTEM                         YES NO  YES              4

21 rows selected.

Elapsed: 00:00:00.05
sys@newtestCDB> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
sys@newtestCDB> startup mount
ORACLE 例程已经启动。

Total System Global Area 2768240640 bytes
Fixed Size 8922760 bytes
Variable Size 704645496 bytes
Database Buffers 2046820352 bytes
Redo Buffers 7852032 bytes
数据库装载完毕。
sys@newtestCDB> flashback database to scn 6730082;

闪回完成。

已用时间: 00: 00: 13.49

sys@newtestCDB> alter database open read only;

数据库已更改。

已用时间: 00: 00: 08.89

sys@newtestCDB> select * from v$tablespace order by con_id;

   TS# NAME                           INC BIG FLA ENC     CON_ID

     1 SYSAUX                         YES NO  YES              1
     0 SYSTEM                         YES NO  YES              1
     2 UNDOTBS1                       YES NO  YES              1
     4 USERS                          YES NO  YES              1
     3 TEMP                           NO  NO  YES              1
     0 SYSTEM                         YES NO  YES              2
     1 SYSAUX                         YES NO  YES              2
     2 UNDOTBS1                       YES NO  YES              2
     3 TEMP                           NO  NO  YES              2
     0 SYSTEM                         YES NO  YES              3
     5 USERS                          YES NO  YES              3

   TS# NAME                           INC BIG FLA ENC     CON_ID

     3 TEMP                           NO  NO  YES              3
     2 UNDOTBS1                       YES NO  YES              3
     1 SYSAUX                         YES NO  YES              3
     0 SYSTEM                         YES NO  YES              4
     5 USERS                          YES NO  YES              4
     3 TEMP                           NO  NO  YES              4
     2 UNDOTBS1                       YES NO  YES              4
     1 SYSAUX                         YES NO  YES              4
     6 FDA                            YES NO  YES              4

20 rows selected.

Elapsed: 00:00:00.05
SQL> select file#,checkpoint_change#,con_id from v$datafile order by con_id;

 FILE# CHECKPOINT_CHANGE#     CON_ID

     1            6730083          1
     3            6730083          1
     5            6730083          1
     7            6730083          1
     2            1525489          2
     4            1525489          2
     6            1525489          2
     8            6730083          3
    11            6730083          3
    10            6730083          3
     9            6730083          3
    40            6730083          4
    43            6730083          4
    42            6730083          4
    41            6730083          4
    45            6730083          4

16 rows selected
sys@newtestCDB> startup force mount
ORACLE 例程已经启动。

数据库已更改。

已用时间: 00: 02: 15.47

PDB级闪回
PDB闪回有几个基本的前提条件：
a.enable local undo
b.enable archivelog mode
c.enable flashback database;
sys@newtestCDB> show pdbs

CON_ID CON_NAME                       OPEN MODE  RESTRICTED

     2 PDB$SEED                       READ ONLY  NO
     3 PDBTEST                        MOUNTED
     4 CLONEPDB_PLUG                  MOUNTED

sys@newtestCDB> alter pluggable database CLONEPDB_PLUG open;

Pluggable database altered.

Elapsed: 00:00:06.46
sys@newtestCDB> show pdbs

CON_ID CON_NAME                       OPEN MODE  RESTRICTED

     2 PDB$SEED                       READ ONLY  NO
     3 PDBTEST                        MOUNTED
     4 CLONEPDB_PLUG                  READ WRITE NO

sys@newtestCDB> alter database local undo on;
alter database local undo on
*
ERROR at line 1:
ORA-65192: database must be in UPGRADE mode for this operation

Elapsed: 00:00:00.03
sys@newtestCDB> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
sys@newtestCDB> startup UPGRADE
ORACLE 例程已经启动。

数据库已更改。

已用时间: 00: 00: 00.34
sys@newtestCDB> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
sys@newtestCDB> startup
ORACLE 例程已经启动。

Total System Global Area 2768240640 bytes
Fixed Size 8922760 bytes
Variable Size 704645496 bytes
Database Buffers 2046820352 bytes
Redo Buffers 7852032 bytes
数据库装载完毕。
数据库已经打开。
sys@newtestCDB> col PROPERTY_NAME for a25;
sys@newtestCDB> col PROPERTY_VALUE for a25;
sys@newtestCDB> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where property_name='LOCAL_UNDO_ENABLED';

PROPERTY_NAME PROPERTY_VALUE

LOCAL_UNDO_ENABLED TRUE

Elapsed: 00:00:00.02
select a.CON_ID,a.TABLESPACE_NAME,b.FILE_NAME
from cdb_tablespaces a,CDB_DATA_FILES b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME and a.con_id=b.con_id and a.CONTENTS='UNDO';
CON_ID TABLESPACE_NAME

FILE_NAME

     1 UNDOTBS1

C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\UNDOTBS01.DBF

sys@newtestCDB> alter pluggable database CLONEPDB_PLUG open;

Pluggable database altered.

Elapsed: 00:00:12.05
sys@newtestCDB> alter session set container=CLONEPDB_PLUG;

Session altered.

Elapsed: 00:00:00.12
sys@newtestCDB> 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-22 11:02:17 6736984

Elapsed: 00:00:00.38
sys@newtestCDB> create restore point CLONEPDB_PLUG_20180122 GUARANTEE FLASHBACK DATABASE;

Restore point created.

Elapsed: 00:00:00.12
sys@newtestCDB> create tablespace abce datafile 'C:\app\Administrator\virtual\oradata\newtest\CLONEPDB_PLUG\abcd01.dbf' size 50m;

Tablespace created.

Elapsed: 00:00:01.39
Oracle12C如何实现闪回技术
sys@newtestCDB> select * from v$tablespace;

   TS# NAME                           INC BIG FLA ENC     CON_ID

     0 SYSTEM                         YES NO  YES              4
     1 SYSAUX                         YES NO  YES              4
     2 UNDOTBS1                       YES NO  YES              4
     3 TEMP                           NO  NO  YES              4
     5 USERS                          YES NO  YES              4
     6 FDA                            YES NO  YES              4
     7 ABCE                           YES NO  YES              4

7 rows selected.

Elapsed: 00:00:00.05
sys@newtestCDB> col name for a30
sys@newtestCDB> select SCN,to_char(time,'mm-dd hh34:mi') time,NAME,CLEAN_PDB_RESTORE_POINT IS_CLEAN,CON_ID from v$restore_point;

   SCN TIME        NAME                           IS_     CON_ID

6737077 01-22 11:03 CLONEPDB_PLUG_20180122 NO 4

Elapsed: 00:00:00.01

sys@newtestCDB> flashback pluggable database clonepdb_plug to scn 6736984;

Flashback complete.

Elapsed: 00:00:03.18
sys@newtestCDB> alter pluggable database clonepdb_plug open read only;

Pluggable database altered.

Elapsed: 00:00:04.06
sys@newtestCDB> select * from v$tablespace;

   TS# NAME                           INC BIG FLA ENC     CON_ID

     0 SYSTEM                         YES NO  YES              4
     1 SYSAUX                         YES NO  YES              4
     2 UNDOTBS1                       YES NO  YES              4
     3 TEMP                           NO  NO  YES              4
     5 USERS                          YES NO  YES              4
     6 FDA                            YES NO  YES              4
     7 ABCE                           YES NO  YES              4

7 rows selected.

Elapsed: 00:00:00.07
sys@newtestCDB> alter pluggable database CLONEPDB_PLUG close;

Pluggable database altered.

Elapsed: 00:00:00.16
sys@newtestCDB> flashback pluggable database clonepdb_plug TO RESTORE POINT CLONEPDB_PLUG_20180122;

Flashback complete.

Elapsed: 00:00:01.41
sys@newtestCDB> alter pluggable database clonepdb_plug open read only;

Pluggable database altered.

Elapsed: 00:00:04.02
sys@newtestCDB> select * from v$tablespace;

   TS# NAME                           INC BIG FLA ENC     CON_ID

     0 SYSTEM                         YES NO  YES              4
     1 SYSAUX                         YES NO  YES              4
     2 UNDOTBS1                       YES NO  YES              4
     3 TEMP                           NO  NO  YES              4
     5 USERS                          YES NO  YES              4
     6 FDA                            YES NO  YES              4
     7 ABCE                           YES NO  YES              4

7 rows selected.

Elapsed: 00:00:00.06
sys@newtestCDB> alter pluggable database clonepdb_plug close;

Pluggable database altered.

Elapsed: 00:00:00.18
sys@newtestCDB> alter pluggable database clonepdb_plug open resetlogs;

Pluggable database altered.

Elapsed: 00:00:11.50
sys@newtestCDB> select * from v$tablespace;

   TS# NAME                           INC BIG FLA ENC     CON_ID

     0 SYSTEM                         YES NO  YES              4
     1 SYSAUX                         YES NO  YES              4
     2 UNDOTBS1                       YES NO  YES              4
     3 TEMP                           NO  NO  YES              4
     5 USERS                          YES NO  YES              4
     6 FDA                            YES NO  YES              4

6 rows selected.

Elapsed: 00:00:00.08
看来pdb不能用readonly 来检查
sys@newtestCDB> select INCARNATION#,RESETLOGS_TIME from v$database_incarnation;

INCARNATION# RESETLOGS_TIME

       1 2017-03-08 15:57:31
       2 2017-12-13 17:22:26
       3 2018-01-22 10:24:24

Elapsed: 00:00:00.06
sys@newtestCDB> select DB_INCARNATION#,PDB_INCARNATION#,INCARNATION_TIME,con_id from v$pdb_incarnation order by 3;

DB_INCARNATION# PDB_INCARNATION# INCARNATION_TIME CON_ID

          2                0 2017-12-13 17:22:26          4
          3                0 2018-01-22 10:24:24          4
          3                1 2018-01-22 11:03:02          4

Elapsed: 00:00:00.04
PDB级别的闪回，并没有改变整个数据库的INCARNATION，从新增加的v$pdb_incarnation视图可以确认刚才的闪回操作只是在PDB（con_id is 3）增加了对应的2条记录。
Oracle12C如何实现闪回技术
C:\app\Administrator\virtual\oradata\newtest\clonepdb_plug>rman target sys/zncg3
008_ZNCG@clonepdb_plug

恢复管理器: Release 12.2.0.1.0 - Production on 星期一 1月 22 12:33:42 2018

已连接到目标数据库: NEWTEST:CLONEPDB_PLUG (DBID=50957894, 未打开)

RMAN> flashback pluggable database CLONEPDB_PLUG to scn 6749827;

从位于 22-1月 -18 的 flashback 开始
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=28 设备类型=DISK

正在开始介质的恢复
介质恢复完成, 用时: 00:00:03

在 22-1月 -18 完成了 flashback
sys@newtestCDB> alter pluggable database clonepdb_plug open read only;

Pluggable database altered.

Elapsed: 00:00:04.33
sys@newtestCDB> select * from v$tablespace;

   TS# NAME                           INC BIG FLA ENC     CON_ID

     0 SYSTEM                         YES NO  YES              4
     1 SYSAUX                         YES NO  YES              4
     2 UNDOTBS1                       YES NO  YES              4
     3 TEMP                           NO  NO  YES              4
     5 USERS                          YES NO  YES              4
     6 FDA                            YES NO  YES              4
     7 ABCE                           YES NO  YES              4

7 rows selected.

Elapsed: 00:00:00.06
sys@newtestCDB> alter pluggable database clonepdb_plug close;

Pluggable database altered.
sys@newtestCDB> alter pluggable database clonepdb_plug open resetlogs;

Pluggable database altered.

Elapsed: 00:00:10.53
sys@newtestCDB> select * from v$tablespace;

   TS# NAME                           INC BIG FLA ENC     CON_ID

     0 SYSTEM                         YES NO  YES              4
     1 SYSAUX                         YES NO  YES              4
     2 UNDOTBS1                       YES NO  YES              4
     3 TEMP                           NO  NO  YES              4
     5 USERS                          YES NO  YES              4
     6 FDA                            YES NO  YES              4

6 rows selected.

Elapsed: 00:00:00.09
flashback backup在12.2中有rman和SQL两种方式闪回，当使用shared undo里需要使用rman，前提需要在pdb close immediate后创建clean resotre point, 过程中会自动创建辅助实例CDB和PDB PITR；使用local undo时,就可以使用SQL命令更佳快速，而且只是pdb 数据文件原位置闪回，并应用undo，在做之前创建任意一种restore point都可以，也不需要辅助实例。

关于“Oracle12C如何实现闪回技术”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。

向AI问一下细节

Oracle12C如何实现闪回技术

COUNT(*)

COUNT(*)

COUNT(*)

COUNT(*)

COUNT(*)

COUNT(*)

COUNT(*)

COUNT(*)

COUNT(*)

COUNT(*)

NAME

COUNT(*)

COUNT(*)

COUNT(*)

COUNT(*)

FLASHBACK_ON

FILE_NAME

猜你喜欢

最新资讯

相关推荐

相关标签