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

【Oracle 12c資料庫支援閃回庫功能】實驗

相關文章