Oracle 12c 数据库支持闪回库功能， r1 仅支持 cdb 级别的闪回库， r2 可以支持到 pdb 级别的闪回库，闪回到闪回点，时间点， scn 几种方式。当然在闪回库操作时， share undo 和 local undo 模式使用的语句是不同的。（ share undo 模式需要使用到辅助实例）

一、采用 local undo 模式

SYS@orcl>SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'LOCAL_UNDO_ENABLED';

------------------------------------------------------------ -----

LOCAL_UNDO_ENABLED TRUE

数据库版本

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

数据库 scott.it 输入数据，记录 scn ；

16:55:24 SYS@orcl>conn scott/oracle@pdb1

Connected.

16:55:40 SCOTT@pdb1>select * from it;

ID

----------

1

2

16:55:46 SCOTT@pdb1>insert into it values(100);

1 row created.

16:55:57 SCOTT@pdb1>commit;

Commit complete.

16:55:59 SCOTT@pdb1>select * from it;

ID

----------

1

2

100

16:56:25 SYS@pdb1>select current_scn from v$database;

CURRENT_SCN

-----------

5120605

3 、删除 scott.it 表，并闪回 pdb1 到 scn 5120605

16:57:02 SYS@pdb1>drop table scott.it purge;

Table dropped.

16:57:20 SYS@pdb1>shutdown immediate;

Pluggable Database closed.

16:57:31 SYS@pdb1>show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

3 PDB1 MOUNTED

16:57:33 SYS@pdb1>conn / as sysdba

Connected.

16:57:41 SYS@orcl>show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 PDB1 MOUNTED

16:57:44 SYS@orcl>flashback pluggable database pdb1 to scn 5120605;

Flashback complete.

16:58:07 SYS@orcl>alter pluggable database pdb1 open resetlogs;

Pluggable database altered.

16:58:30 SYS@orcl>show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 PDB1 READ WRITE NO

16:58:57 SYS@orcl>conn scott/oracle@pdb1

Connected.

16:59:15 SCOTT@pdb1>select count(*) from it;

COUNT(*)

----------

3

16:59:25 SCOTT@pdb1>select * from it;

ID

----------

1

2

100

二、采用 share undo 模式

1 、将 local undo 修改成 share undo 模式

查看 local undo 是否开启，默认是开启的；

SYS@orcl>SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'LOCAL_UNDO_ENABLED';

------------------------------------------------------------ -----

LOCAL_UNDO_ENABLED TRUE

以 upgrade 模式重启数据库；

20:33:32 SYS@orcl>show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 PDB1 READ WRITE NO

20:33:38 SYS@orcl>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

20:34:20 SYS@orcl>startup upgrade;

ORACLE instance started.

Total System Global Area 2466250400 bytes

Fixed Size 9137824 bytes

Variable Size 587202560 bytes

Database Buffers 1862270976 bytes

Redo Buffers 7639040 bytes

Database mounted.

Database opened.

关闭 local undo ；

20:34:50 SYS@orcl>alter database local undo off;

Database altered.

20:35:01 SYS@orcl>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

20:36:01 SYS@orcl>startup;

ORACLE instance started.

Total System Global Area 2466250400 bytes

Fixed Size 9137824 bytes

Variable Size 587202560 bytes

Database Buffers 1862270976 bytes

Redo Buffers 7639040 bytes

Database mounted.

Database opened.

20:36:41 SYS@orcl>show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 PDB1 READ WRITE NO

20:36:47 SYS@orcl>SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME LOCAL_UNDO_ENABLED

-----------------------------------------------------------------

PROPERTY_VALUE FALSE

20:36:58 SYS@orcl>show parameter undo;

NAME TYPE VALUE

------------------------------------ ----------- -----------------

temp_undo_enabled boolean FALSE

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS1

20:37:08 SYS@orcl>conn sys/oracle@pdb1 as sysdba

Connected.

20:37:46 SYS@pdb1>select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

------------------------------

SYSTEM

SYSAUX

UNDOTBS1

TEMP01

DATA

20:37:55 SYS@pdb1>drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

20:38:17 SYS@pdb1>select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

------------------------------

SYSTEM

SYSAUX

TEMP01

DATA

备注： share undo 转化为 local undo 模式，打开 pdb01 时会自动重建，无须人工干预，如果需要在 pdb01 中重建 pdb 则需要 undo 管理方式使用 manual 方式或者在 pdb01 中新建 undo 然后再替换的方式。

录入数据，记录 scn

20:47:53 SCOTT@pdb1>insert into it values(200);

1 row created.

20:47:59 SCOTT@pdb1>commit;

Commit complete.

20:48:01 SCOTT@pdb1>select * from it;

ID

----------

1

2

200

100

20:48:08 SCOTT@pdb1>conn sys/oracle@pdb1 as sysdba

Connected.

20:48:29 SYS@pdb1>select current_scn from v$database;

CURRENT_SCN

-----------

5181471

20:48:37 SYS@pdb1>drop table scott.it;

Table dropped.

3 、闪回数据库。

RMAN> flashback pluggable database pdb1 to scn 5181471 auxiliary destination '/home/oracle/aux';

[oracle@db1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Aug 14 21:24:31 2022

Version 19.3.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SYS@orcl>alter pluggable database pdb1 open resetlogs;

Pluggable database altered.

SYS@orcl>show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 PDB1 READ WRITE NO

SYS@orcl>conn scott/oracle@pdb1

Connected.

SCOTT@pdb1>select * from it;

ID

----------

1

2

200

100

总结：数据库闪回功能基于已有的备份数据以及归档日志将 pdb 库恢复到某个节点， 12c 上使用 local undo 和 share undo 方式使用命令是不一样的，数据库闪回操作意味着数据的丢失，所以操作时需要注意，是否可以只读的方式来看看闪回数据是否正常呢？

Yicheng16
22.08.14

-- The End --

ITPub博客

【Oracle 12c数据库支持闪回库功能】实验

最新文章