Oracle 12.2多租户增强：Refreshable PDB

本文主要是介绍Oracle 12.2多租户增强：Refreshable PDB，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

简介

Refreshable PDB，也称为Online Fast Refresh Synchronization。技术上是以PDB Hot Clone为基础的，关于PDB Hot Clone，参见文章：Oracle 12.2多租户增强：PDB Hot Clone。
本实验将基于ORCLCDB中的orclpdb1在目标数据库中ORCLCDB2中建立可刷新的orclpdb2。
源数据库：

SQL> select name from v$database;NAME
---------
ORCLCDBSQL> show pdbs;CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED                       READ ONLY  NO3 ORCLPDB1                       READ WRITE NO

目标数据库：

SQL> select name from v$database;NAME
---------
ORCLCDB2SQL> show pdbs;CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED                       READ ONLY  NO

试验环境

在同一主机上有两个CDB: ORCLCDB和ORCLCDB2。ORCLCDB是通过vagrant box创建的，ORCLCDB2是通过dbca创建，如下：

time dbca -silent -createDatabase \-templateName General_Purpose.dbc \-gdbname orclcdb2 -sid orclcdb2 -responseFile NO_VALUE \-characterSet AL32UTF8 \-sysPassword Welcome1 \-systemPassword Welcome1 \-createAsContainerDatabase true \-pdbAdminPassword Welcome1 \-databaseType MULTIPURPOSE \-automaticMemoryManagement false \-totalMemory 2048 \-storageType FS \-datafileDestination "/opt/oracle/oradata" \-redoLogFileSize 50 \-emConfiguration NONE \-ignorePreReqsPrepare for db operation
10% complete
Copying database files
40% complete
Creating and starting Oracle instance
42% complete
46% complete
52% complete
56% complete
60% complete
Completing Database Creation
66% complete
69% complete
70% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:/opt/oracle/cfgtoollogs/dbca/orclcdb2.
Database Information:
Global Database Name:orclcdb2
System Identifier(SID):orclcdb2
Look at the log file "/opt/oracle/cfgtoollogs/dbca/orclcdb2/orclcdb21.log" for further details.real    19m21.641s
user    0m32.503s
sys     0m4.545s

源数据库ORCLCDB已开启归档，配置克隆用户，启用local undo。在目标数据库中已创建Database Link。具体参见:Oracle 12.2多租户增强：PDB Hot Clone。
另外，Database Link除了创建数据库需要用，后续的同步也需要使用，参见这里：

For a PDB refresh copy to be in sync with its source, redo is accessed from the source PDB over a database link. Sometimes the source PDB or the CDB to which the source PDB belongs is not accessible when the refresh copy needs to be updated. In those cases, if this parameter is set, an attempt will be made to read archive log files from the directory specified by this parameter.

创建Refreshable PDB

使用的是手工刷新，还可以使用定期刷新(例如REFRESH MODE EVERY 120 MINUTES)；

SQL> CREATE PLUGGABLE DATABASE orclpdb2 FROM orclpdb1@clone_link FILE_NAME_CONVERT=('ORCLPDB1', 'ORCLPDB2') REFRESH MODE MANUAL;Pluggable database created.
SQL> show pdbs;CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED                       READ ONLY  NO5 ORCLPDB2                       MOUNTED

Refreshable PDB打开后只能处于read-only状态。

SQL> ALTER PLUGGABLE DATABASE orclpdb2 OPEN READ ONLY;Pluggable database altered.SQL> show pdbs;CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED                       READ ONLY  NO5 ORCLPDB2                       READ ONLY  NO
SQL> SELECT last_refresh_scn FROM   dba_pdbs WHERE  pdb_name = 'ORCLPDB2';LAST_REFRESH_SCN
----------------3265877

在源数据库制造一些变化：

SQL> alter session set container=orclpdb1;Session altered.SQL> create table t1(a int);Table created.SQL> insert into t1 values(1);1 row created.SQL> commit;Commit complete.

因为是手工刷新，此时在目标数据库看不到数据：

SQL> select * from t1;
select * from t1*
ERROR at line 1:
ORA-00942: table or view does not exist

刷新数据库：

SQL> alter session set container=orclpdb2;Session altered.SQL> show pdbs;CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------5 ORCLPDB2                       READ ONLY  NO
SQL> ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;Pluggable database altered.SQL> ALTER PLUGGABLE DATABASE REFRESH;Pluggable database altered.SQL> ALTER PLUGGABLE DATABASE OPEN READ ONLY;Pluggable database altered.
SQL> SELECT last_refresh_scn FROM   dba_pdbs WHERE  pdb_name = 'ORCLPDB2';LAST_REFRESH_SCN
----------------3268805

此时可以看到数据：

SQL> select * from t1;A
----------1

常用语句

查看刷新设置：

SQL> col pdb_name format a10
SQL> SELECT pdb_id, pdb_name, refresh_mode, refresh_interval FROM   dba_pdbs;PDB_ID PDB_NAME   REFRES REFRESH_INTERVAL
---------- ---------- ------ ----------------5 ORCLPDB2   MANUALSQL> ALTER PLUGGABLE DATABASE orclpdb2 REFRESH MODE EVERY 60 MINUTES;Pluggable database altered.SQL> SELECT pdb_id, pdb_name, refresh_mode, refresh_interval FROM   dba_pdbs;PDB_ID PDB_NAME   REFRES REFRESH_INTERVAL
---------- ---------- ------ ----------------5 ORCLPDB2   AUTO                 60

改为不可刷新状态：

SQL> ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;Pluggable database altered.
SQL> alter session set container=cdb$root;Session altered.SQL> ALTER PLUGGABLE DATABASE orclpdb2 REFRESH MODE NONE;Pluggable database altered.SQL> ALTER PLUGGABLE DATABASE orclpdb2 OPEN;Pluggable database altered.SQL> SELECT pdb_id, pdb_name, refresh_mode, refresh_interval FROM   dba_pdbs;PDB_ID PDB_NAME   REFRES REFRESH_INTERVAL
---------- ---------- ------ ----------------2 PDB$SEED   NONE5 ORCLPDB2   NONE

结论

Refreshable PDB是基于Hot Clone的，不同的是，Hot Clone是一次性的，目标可写。而Refreshable PDB是持续性的，目标只读，可以基于Refreshable PDB再建立可写的PDB。
Refreshable PDB不依赖于共享存储，而是依赖于Database Link。