番外：如何克隆可刷新的PDB（Refreshable PDB）
2019-08-31 08:43 askscuti 阅读(914) 评论(0) 编辑 收藏 举报基于版本：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
