基于版本：19c （12.2.0.3） AskScuti
创建方法：克隆创建
对应路径：属于克隆。PDB类型为：Refreshable
相关系列请参考《Oracle创建PDB列表文章》
注意：创建可刷新的PDB，源库必须处于归档模式和本地UNDO模式
内容总览
1. 环境概述
2. 检查源库环境
3. 源库创建用户并授权
4. 目标库编辑TNS
5. 目标库创建DBLink
6. 目标库创建可刷新PDB
7. 目标库打开可刷新PDB
8. 可刷新PDB测试
1. 环境概述
为了概念理解统一，提前约定下：
远程CDB2中有个ERP1，我们称远程CDB2为“源CDB”，IP：192.168.1.14
本地CDB1中有个PDB1，我们称本地CDB1为“目标CDB”，IP：192.168.1.12
注意：源CDB和目标CDB是相对而言。就是被克隆的对象叫“源”，准备克隆出来的对象叫“目标”。因此，下面就是要通过源CDB2中的ERP1，远程克隆出来一个可刷新的PDB，放在目标CDB1中，名称为PDB_REF。
2. 检查源库环境
检查是否为归档模式
[oracle@henry ~]$ rlwrap sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Aug 31 07:27:48 2019
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- --------- ---------- ----------
2 PDB$SEED READ ONLY NO
3 ERP1 READ WRITE NO
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/arch1
Oldest online log sequence 5
Next log sequence to archive 7
Current log sequence 7
检查是否为本地UNDO
SQL> select property_name,property_value from database_properties where property_name='LOCAL_UNDO_ENABLED';
PROPERTY_NAME PROPERTY_VALUE
-------------------- --------------------
LOCAL_UNDO_ENABLED TRUE
3. 源库创建用户并授权
SQL> create user c##u1 identified by oracle;
User created.
SQL> grant create session,create pluggable database,sysoper to c##u1 container=all;
Grant succeeded.
4. 目标库编辑TNS
CDB2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.14)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CDB2)
)
)目标库编辑tnsnames别名，指向源库。
5. 目标库创建DBLink
SQL> create database link cdb2_dblink connect to c##u1 identified by oracle using 'CDB2';
Database link created.
#测试DBLINK
SQL> select * from tab@cdb2_dblink;
no rows selected
6. 目标库创建可刷新PDB
注意：刷新模式子句只支持 CREATE PLUGGABLE DATABASE ... FROM 语句。
6.0 基于默认 REFRESH MODE NONE
默认缺省情况下，创建普通PDB（非刷新PDB）
6.1 基于手工刷新 REFRESH MODE MANUAL
SQL> !mkdir /u01/app/oracle/oradata/CDB1/pdbref
SQL> create pluggable database pdb_ref from erp1@cdb2_dblink create_file_dest='/u01/app/oracle/oradata/CDB1/pdbref' refresh mode manual;
6.2 基于自动刷新 REFRESH MODE EVERY number_of_minutes MINUTES
SQL> !mkdir /u01/app/oracle/oradata/CDB1/pdbrefauto
SQL> create pluggable database pdb_refauto from erp1@cdb2_dblink create_file_dest='/u01/app/oracle/oradata/CDB1/pdbrefauto' refresh mode every 60 minutes;
查看
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- --------- ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 PDB_REF MOUNTED
5 PDB_REFAUTO MOUNTED
7. 目标库打开可刷新PDB
可刷新PDB为了保证和源库一致，必须只能以 READ ONLY 只读模式打开。
SQL> alter pluggable database pdb_ref open read only;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- --------- ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 PDB_REF READ ONLY NO
5 PDB_REFAUTO MOUNTED无法以读写模式打开
SQL> alter pluggable database pdb_ref open;
alter pluggable database pdb_ref open
*
ERROR at line 1:
ORA-65341: cannot open pluggable database in read/write mode
8. 可刷新PDB测试
8.1 源库创建测试数据
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- --------- ---------- ----------
2 PDB$SEED READ ONLY NO
3 ERP1 READ WRITE NO
SQL> alter session set container=erp1;
Session altered.
SQL> create table test_ref(id number);
Table created.
SQL> insert into test_ref select object_id from dba_objects where rownum<501;
500 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from test_ref;
COUNT(*)
----------
500
8.2 目标库刷新
刷新前查看
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- --------- ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 PDB_REF READ ONLY NO
5 PDB_REFAUTO MOUNTED
SQL> alter session set container=pdb_ref;
Session altered.
SQL> select count(*) from test_ref;
select count(*) from test_ref
*
ERROR at line 1:
ORA-00942: table or view does not exist
目标库关闭PDB_REF进行手工刷新
注意：执行刷新时，必须关闭可刷新的PDB。如果尝试自动刷新时未关闭可刷新的PDB，则将刷新延迟到下一次计划。如果执行手动刷新时未关闭可刷新的PDB，则会报告错误。自动刷新模式的PDB也可以执行手工刷新。
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- --------- ---------- ----------
4 PDB_REF READ ONLY NO
SQL> alter pluggable database pdb_ref close immediate;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- --------- ---------- ----------
4 PDB_REF MOUNTED
SQL> alter pluggable database pdb_ref refresh;
Pluggable database altered.
8.3 目标库刷新后查看
SQL> alter pluggable database pdb_ref open read only;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- --------- ---------- ----------
4 PDB_REF READ ONLY NO
SQL> select count(*) from test_ref;
COUNT(*)
----------
500
