番外：如何克隆可刷新的PDB

基于版本：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. 检查源库环境

检查是否为归档模式

[[email?protected] ~]$ 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

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

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