Oracle12C如何实现闪回技术

网友投稿 324 2023-12-31

Oracle12C如何实现闪回技术

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

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; EMPNOENAME JOB MGR HIREDATE SAL COMM DEPTNO7369 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/>scott@clonepdb_plug[PDB](10.8.5.204-12.2)>

按RECYCLEBIN NAME可以任意恢复

可以重命名

sys表不能闪回

闪回后和drop回收站约束在但乱码（外键约束除外），索引不见了

没用undo，其他的都用了

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.

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

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 快照

归档日志（或当前日志)

逻辑恢复

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 例程已经启动。

Total System Global Area 2768240640 bytes

Fixed Size 8922760 bytes

Variable Size 704645496 bytes

Database Buffers 2046820352 bytes

Redo Buffers 7852032 bytes

数据库装载完毕。

sys@newtestCDB> alter database open resetlogs;

数据库已更改。

已用时间: 00: 02: 15.47

PDB级闪回

PDB闪回有几个基本的前提条件：

a.enable local undo

b.enable archivelog mode

c.enable flashback database;

sys@newtestCDB> show pdbsCON_IDCON_NAME OPEN MODE RESTRICTED2 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 pdbsCON_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 例程已经启动。

Total System Global Area 2768240640 bytes

Fixed Size 8922760 bytes

Variable Size 704645496 bytes

Database Buffers 2046820352 bytes

Redo Buffers 7852032 bytes

数据库装载完毕。

数据库已经打开。

sys@newtestCDB> alter database local undo on;

数据库已更改。

已用时间: 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

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; SCNTIME 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

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

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

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条记录。

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

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

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

标签：配置数据管理参数

暂时没有评论，来抢沙发吧~

Oracle12C如何实现闪回技术

Oracle12C如何实现闪回技术

COUNT(*)

COUNT(*)

COUNT(*)

COUNT(*)

COUNT(*)

COUNT(*)

COUNT(*)

COUNT(*)

COUNT(*)

COUNT(*)

NAME

COUNT(*)

COUNT(*)

COUNT(*)

COUNT(*)

FLASHBACK_ON

FILE_NAME

最近发表

更多内容

推荐文章

解决方案

热评文章