Oracle 创建PDB的几种常用方法
Oracle 创建PDB的几种常用方法
·
PDB是运行在PDB上的一个数据库，各个PDB是独立运行的。在CDB中创建、删除、迁移PDB是非常方便的事情。不会对其它CDB和PDB产生任何影响。
CREATE PDB METHOD
创建PDB的简要说明
- 利用Seed(种子)模板来创建
默认方法，使用PDB Seed或应用程序的模板文件夹创建PDB。将模板复制到一个新的目录，并将新的文件与新的PDB进行关联。
- 复制已有的PDB/Non-CDB
通过复制PDB或Non-CDB来创建PDB。源库可以是本地CDB中的PDB、远端CDB中的PDB、本地或远端应用程序容器中的PDB、Non-CDB 。此方法将源端相关的文件复制到新的目录，然后与新的PDB进行关联。
- 将PDB迁移到另一个CDB
通过将PDB从一个CDB中迁移到另一个CDB中来进行创建新的PDB。此方法将与PDB相关的文件移动到一个新的目录，而不是复制。
- 将Unplug的PDB插入到CDB中
通过使用PDB的XML元数据文件夹来创建PDB，并将其插到CDB中。XML文件中记录了PDB的详细配置信息。
- 从Non-CDB创建PDB，并插入到CDB中
利用DBMS_PDB包将Non-CDB创建为PDB并插到CDB中。
CREATE PDB Condition
- CDB必须存在且处于读写模式
- 当前用户必须是公告用户，并且当前的容器必须是CDB root或应用程序容器。
- 当前用户必须有CREATE PLUGGABLED DATABASE权限
- PDB名称不能和已存在的PDB名称重复
- 其它额外限制。比如DataGuard环境下创建PDB，必须进行更多额外的设置。
Use Seed template to Create PDB
查看PDB数据库的默认表空间
SQL>
SQL> select tablespace_name,status,contents from dba_tablespaces;
TABLESPACE_NAME STATUS CONTENTS
------------------------------ --------- ---------------------
SYSTEM ONLINE PERMANENT
SYSAUX ONLINE PERMANENT
UNDOTBS1 ONLINE UNDO
TEMP ONLINE TEMPORARY
USERS ONLINE PERMANENT
SQL> show user;
USER is "SYS"查看默认创建的表空间的对应的数据文件
SQL> show user;
USER is "SYS"
SQL> select name from v$datafile;
NAME
----------------------------------------------------------------------------------------------------
/u02/oradata/CDB1/system01.dbf
/u02/oradata/CDB1/sysaux01.dbf
/u02/oradata/CDB1/undotbs01.dbf
/u02/oradata/CDB1/pdbseed/system01.dbf
/u02/oradata/CDB1/pdbseed/sysaux01.dbf
/u02/oradata/CDB1/users01.dbf
/u02/oradata/CDB1/pdbseed/undotbs01.dbf
/u02/oradata/CDB1/pdb1/system01.dbf
/u02/oradata/CDB1/pdb1/sysaux01.dbf
/u02/oradata/CDB1/pdb1/undotbs01.dbf
/u02/oradata/CDB1/pdb1/users01.dbf
/u02/oradata/CDB1/pdb2/system01.dbf
/u02/oradata/CDB1/pdb2/sysaux01.dbf
/u02/oradata/CDB1/pdb2/undotbs01.dbf
/u02/oradata/CDB1/pdb2/users01.dbf
15 rows selected.
SQL> [oracle@oracle-db-19c ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 29 08:51:58 2022
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> set pagesize 200
SQL> set linesize 200
SQL>
SQL> CREATE PLUGGABLE DATABASE cndbapdb
2 ADMIN USER cndbaadm IDENTIFIED BY cndbaadm
3 STORAGE (MAXSIZE 2G)
4 DEFAULT TABLESPACE cndba
5 DATAFILE '/u02/oradata/CDB1/cndbapdb/cndba01.dbf' SIZE 150M AUTOEXTEND ON
6 PATH_PREFIX = '/u02/oradata/CDB1/cndbapdb/'
7 FILE_NAME_CONVERT = ('/u02/oradata/CDB1/pdbseed/', '/u02/oradata/CDB1/cndbapdb/');
Pluggable database created.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 MOUNTED
5 CNDBAPDB MOUNTED
SQL> 上述实验创建了一个名为CNDBAPDB的PDB和一个具有PDB_DBA角色的cndbaadm管理用户，将DBA权限赋给cndbaadm管理用户，将DBA权限赋给PDB_DBA角色，限制PDB可以使用的空间大小为2GB,创建的表空间为cndba,并设置为默认表空间。
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 MOUNTED
5 CNDBAPDB MOUNTED
SQL> alter pluggable database CNDBAPDB open;
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 MOUNTED
5 CNDBAPDB READ WRITE NO
SQL> select name from v$datafile where con_id=5;
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u02/oradata/CDB1/cndbapdb/system01.dbf
/u02/oradata/CDB1/cndbapdb/sysaux01.dbf
/u02/oradata/CDB1/cndbapdb/undotbs01.dbf
/u02/oradata/CDB1/cndbapdb/cndba01.dbf
SQL> select name from v$tempfile where con_id=5;
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u02/oradata/CDB1/cndbapdb/temp012022-11-02_15-16-24-663-PM.dbf
SQL> select name,con_id,dbid from v$containers;
NAME CON_ID DBID
-------------------------------------------------------------------------------------------------------------------------------- ---------- ----------
CDB$ROOT 1 1093429351
PDB$SEED 2 2760955567
PDB1 3 2714456025
PDB2 4 3722518118
CNDBAPDB 5 874005088
SQL>
COPY PDB
复制PDB就是用已经存在的数据库作为模板创建一个结构和数据（可以不复制数据）相同的PDB。复制期间如果想要保持源数据库正常运行，则源数据库必须处于归档模式且本地UNDO模式。
- Local PDB
- Remote PDB
- Non-CDB
1.使用CREATE PLUGGABLE DATABASE 命令创建PDB
CREATE PLUGGABLE DATABASE pdb2 from pdb1
FILE_NAME_CONVERT = ('/u02/oradata/CDB1/pdb1','/u02/oradata/CDB1/pdb2');Note:若CDB没有启动local UNDO模式，则源PDB必须以只读模式打开。
若CDB是本地UNDO模式，则可以以读写模式打开。
若没有启用OMF，则需要指定FILE_NAME_CONVERT参数。
如何打开读写模式：
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 MOUNTED
5 CNDBAPDB MOUNTED
SQL> alter pluggable database CNDBAPDB open;
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 MOUNTED
5 CNDBAPDB READ WRITE NO
SQL>
如何启用OMF:
Enable Oracle Managed File (OMF) to simplify the creation of databases and database files.
SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST='/u02/oradata' SCOPE=BOTH;
System altered.Enable autostart of Pluggable databases with startup of Container Database.
SQL> ALTER PLUGGABLE DATABASE PDB1 SAVE STATE;
Pluggable database altered.如果不想复制源PDB中表的数据，又想限制PDB可使用的总空间大小，则可以使用如下语句：
SQL> CREATE PLUGGABLE DATABASE cndbapdb2 from cndbapdb
2 FILE_NAME_CONVERT = ('/u02/oradata/CDB1/cndbapdb/', '/u02/oradata/CDB1/cndbapdb2/')
3 STORAGE (MAXSIZE 2G)
4 NO DATA;
Pluggable database created.
SQL>2.利用Local PDB 快照创建PDB
1.手动创建快照
确认SNAPSHOT模式为MANUAL,语句如下：
SQL> set pagesize 200
SQL> set linesize 200
SQL> select PDB_NAME,snapshot_mode "s_mode",snapshot_interval/60 "snap_int_hrs" from DBA_PDBS;
PDB_NAME s_mode snap_int_hrs
-------------------------------------------------------------------------------------------------------------------------------- ------ ------------
PDB1 MANUAL
PDB$SEED MANUAL
PDB2 MANUAL
CNDBAPDB MANUAL
CNDBAPDB2 MANUAL
SQL>连接到cndbapdb
SQL> alter session set container=cndbapdb;
Session altered.
SQL> 创建snapshot:
SQL> ALTER PLUGGABLE DATABASE SNAPSHOT cndbapdb_snap1129;
ALTER PLUGGABLE DATABASE SNAPSHOT cndbapdb_snap1129
*
ERROR at line 1:
ORA-12754: Feature PDB SNAPSHOT CAROUSEL is disabled due to missing capability .
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 19.0.0
noncdb_compatible boolean FALSE
SQL> alter session set container=cdb$root;
Session altered.
SQL> alter system set "_exadata_feature_on"=true scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 629145392 bytes
Fixed Size 9137968 bytes
Variable Size 377487360 bytes
Database Buffers 234881024 bytes
Redo Buffers 7639040 bytes
Database mounted.
Database opened.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 MOUNTED
5 CNDBAPDB MOUNTED
7 CNDBAPDB2 MOUNTED
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 19.0.0
noncdb_compatible boolean FALSE
SQL> alter session set container=cndbapdb;
Session altered.
SQL> ALTER PLUGGABLE DATABASE SNAPSHOT cndbapdb_snap1129;
ALTER PLUGGABLE DATABASE SNAPSHOT cndbapdb_snap1129
*
ERROR at line 1:
ORA-65036: pluggable database CNDBAPDB not open in required mode
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
5 CNDBAPDB MOUNTED
SQL> alter pluggable database CNDBAPDB open;
Pluggable database altered.
SQL> ALTER PLUGGABLE DATABASE SNAPSHOT cndbapdb_snap1129;
Pluggable database altered.
SQL> (2) 利用前面创建的snapshot创建PDB;
SQL> conn / as sysdba
Connected.
SQL> show user;
USER is "SYS"
SQL> CREATE PLUGGABLE DATABASE cndbapdb3 from cndbapdb using snapshot cndbapdb_snap1129;
Pluggable database created.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 MOUNTED
5 CNDBAPDB READ WRITE NO
6 CNDBAPDB3 MOUNTED
7 CNDBAPDB2 MOUNTED
SQL> 复制remote PDB
如果复制的是远端的PDB，则必须使用DBLINK来复制，并且DBLINK是存在于本地CDB中的，而不是存在于远端CDB中的。
1.复制远端PDB的必要条件
（1） 当前操作用户必须具有CREATE PLUGGABLE DATABASE 的系统权限。
（2）源端和目标端都必须满足：
- 同样的字节存储顺序（Endian）
- 源端平台上安装的数据库组件必须和目标端平台上安装的数据库组件是一样的，或者是其子集。
4.操作示例
（1） 在主库、备库的CDB中执行如下命令，创建相同的共有用户及密码：
SQL> show user;
USER is "SYS"
SQL> create user c##maxwell identified by oracle;
User created.
SQL> grant create session,resource,create any table,unlimited tablespace to c##maxwell container=all;
Grant succeeded.
SQL> grant create pluggable database to c##maxwell container=all;
Grant succeeded.
SQL> grant sysoper to c##maxwell container=all;
Grant succeeded.
SQL>
sys@cdb$root:orclcdb> show user
USER is "SYS"
sys@cdb$root:orclcdb>
sys@cdb$root:orclcdb>
sys@cdb$root:orclcdb> create user c##maxwell identified by oracle;
User created.
sys@cdb$root:orclcdb> grant create session,resource,create any table,unlimited tablespace to c##maxwell container=all;
Grant succeeded.
sys@cdb$root:orclcdb> grant create pluggable database to c##maxwell container=all;
Grant succeeded.
sys@cdb$root:orclcdb> grant sysoper to c##maxwell container=all;
Grant succeeded.
sys@cdb$root:orclcdb> （2） 由于源端不是本地UNDO模式，因此需要以只读模式打开，代码如下：
[oracle@MaxwellDBA ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 29 16:12:11 2022
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
sys@cdb$root:orclcdb> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB1 READ WRITE NO
sys@cdb$root:orclcdb> alter pluggable database orclpdb1 close immediate;
Pluggable database altered.
sys@cdb$root:orclcdb> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB1 MOUNTED
sys@cdb$root:orclcdb> alter pluggable database orclpdb1 open read only;
Pluggable database altered.
sys@cdb$root:orclcdb> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB1 READ ONLY NO
sys@cdb$root:orclcdb> （3）在目标端创建DBLINK
-
给HR账号授予创建dblink和删除dblink的权限（必须在sys用户下授权）
SQL> show user;
USER is "SYS"
SQL>
SQL>
SQL> alter session set container=PDB1;
Session altered.
SQL> grant CREATE PUBLIC DATABASE LINK,DROP PUBLIC DATABASE LINK to hr;
Grant succeeded.
-
查看 scott 是否已经拥有dblink的相关权限
SQL> conn hr/hr@PDB1
Connected.
SQL> show user
USER is "HR"
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE PUBLIC DATABASE LINK
DROP PUBLIC DATABASE LINK
PRIVILEGE
----------------------------------------
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
16 rows selected.
SQL>-
查看dblink的方式：
SQL> set pagesize 200
SQL> set linesize 200
SQL> select owner,object_name from dba_objects where object_type='DATABASE LINK';
OWNER OBJECT_NAME
---------------------------------------- ----------------------------------------
SYS SYS_HUB
SQL> -
创建dblink
SQL>
SQL> conn hr/hr@PDB1;
Connected.
SQL>
SQL> show user;
USER is "HR"
SQL> create public database link LINK_ORCLPDB1
2 connect to hr identified by "hr"
3 using
4 '(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = ORCLPDB1)))';
Database link created.
SQL>
SQL> create public database link LINK_ORCLPDB2
2 connect to c##maxwell identified by "oracle"
3 using
4 '(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = ORCLPDB1)))';
Database link created.
SQL> show user;
USER is "C##MAXWELL"
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> - 核查dblink是否建立成功。
SQL> conn sys/sys as sysdba
Connected.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=PDB1;
Session altered.
SQL> select owner,object_name from dba_objects where object_type='DATABASE LINK';
OWNER OBJECT_NAME
---------------------------------------- ----------------------------------------
SYS SYS_HUB
PUBLIC LINK_ORCLPDB1
SQL> show user
USER is "SYS"
SQL>
SQL> conn hr/hr@PDB1;
Connected.
SQL> show user;
USER is "HR"
SQL> show con_name;
CON_NAME
------------------------------
PDB1
SQL> select * from hr.employees@LINK_ORCLPDB1;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ---------- -------------- ---------- -------------
198 Donald OConnell DOCONNEL 650.507.9833 21-JUN-07 SH_CLERK 2600 124 50
199 Douglas Grant DGRANT 650.507.9844 13-JAN-08 SH_CLERK 2600 124 50
200 Jennifer Whalen JWHALEN 515.123.4444 17-SEP-03 AD_ASST 4400 101 10
201 Michael Hartstein MHARTSTE 515.123.5555 17-FEB-04 MK_MAN 13000 100 20
202 Pat Fay PFAY 603.123.6666 17-AUG-05 MK_REP 6000 201 20
203 Susan Mavris SMAVRIS 515.123.7777 07-JUN-02 HR_REP 6500 101 40
204 Hermann Baer HBAER 515.123.8888 07-JUN-02 PR_REP 10000 101 70
205 Shelley Higgins SHIGGINS 515.123.8080 07-JUN-02 AC_MGR 12008 101 110
206 William Gietz WGIETZ 515.123.8181 07-JUN-02 AC_ACCOUNT 8300 205 110
100 Steven King SKING 515.123.4567 17-JUN-03 AD_PRES 24000 90
101 Neena Kochhar NKOCHHAR 515.123.4568 21-SEP-05 AD_VP 17000 100 90
102 Lex De Haan LDEHAAN 515.123.4569 13-JAN-01 AD_VP 17000 100 90
103 Alexander Hunold AHUNOLD 590.423.4567 03-JAN-06 IT_PROG 9000 102 60
104 Bruce Ernst BERNST 590.423.4568 21-MAY-07 IT_PROG 6000 103 60
105 David Austin DAUSTIN 590.423.4569 25-JUN-05 IT_PROG 4800 103 60
106 Valli Pataballa VPATABAL 590.423.4560 05-FEB-06 IT_PROG 4800 103 60
107 Diana Lorentz DLORENTZ 590.423.5567 07-FEB-07 IT_PROG 4200 103 60
108 Nancy Greenberg NGREENBE 515.124.4569 17-AUG-02 FI_MGR 12008 101 100
109 Daniel Faviet DFAVIET 515.124.4169 16-AUG-02 FI_ACCOUNT 9000 108 100
110 John Chen JCHEN 515.124.4269 28-SEP-05 FI_ACCOUNT 8200 108 100
111 Ismael Sciarra ISCIARRA 515.124.4369 30-SEP-05 FI_ACCOUNT 7700 108 100
112 Jose Manuel Urman JMURMAN 515.124.4469 07-MAR-06 FI_ACCOUNT 7800 108 100
113 Luis Popp LPOPP 515.124.4567 07-DEC-07 FI_ACCOUNT 6900 108 100
114 Den Raphaely DRAPHEAL 515.127.4561 07-DEC-02 PU_MAN 11000 100 30
115 Alexander Khoo AKHOO 515.127.4562 18-MAY-03 PU_CLERK 3100 114 30
116 Shelli Baida SBAIDA 515.127.4563 24-DEC-05 PU_CLERK 2900 114 30
117 Sigal Tobias STOBIAS 515.127.4564 24-JUL-05 PU_CLERK 2800 114 30
118 Guy Himuro GHIMURO 515.127.4565 15-NOV-06 PU_CLERK 2600 114 30
119 Karen Colmenares KCOLMENA 515.127.4566 10-AUG-07 PU_CLERK 2500 114 30
120 Matthew Weiss MWEISS 650.123.1234 18-JUL-04 ST_MAN 8000 100 50
121 Adam Fripp AFRIPP 650.123.2234 10-APR-05 ST_MAN 8200 100 50
122 Payam Kaufling PKAUFLIN 650.123.3234 01-MAY-03 ST_MAN 7900 100 50
123 Shanta Vollman SVOLLMAN 650.123.4234 10-OCT-05 ST_MAN 6500 100 50
124 Kevin Mourgos KMOURGOS 650.123.5234 16-NOV-07 ST_MAN 5800 100 50
125 Julia Nayer JNAYER 650.124.1214 16-JUL-05 ST_CLERK 3200 120 50
126 Irene Mikkilineni IMIKKILI 650.124.1224 28-SEP-06 ST_CLERK 2700 120 50
127 James Landry JLANDRY 650.124.1334 14-JAN-07 ST_CLERK 2400 120 50
128 Steven Markle SMARKLE 650.124.1434 08-MAR-08 ST_CLERK 2200 120 50
129 Laura Bissot LBISSOT 650.124.5234 20-AUG-05 ST_CLERK 3300 121 50
130 Mozhe Atkinson MATKINSO 650.124.6234 30-OCT-05 ST_CLERK 2800 121 50
131 James Marlow JAMRLOW 650.124.7234 16-FEB-05 ST_CLERK 2500 121 50
132 TJ Olson TJOLSON 650.124.8234 10-APR-07 ST_CLERK 2100 121 50
133 Jason Mallin JMALLIN 650.127.1934 14-JUN-04 ST_CLERK 3300 122 50
134 Michael Rogers MROGERS 650.127.1834 26-AUG-06 ST_CLERK 2900 122 50
135 Ki Gee KGEE 650.127.1734 12-DEC-07 ST_CLERK 2400 122 50
136 Hazel Philtanker HPHILTAN 650.127.1634 06-FEB-08 ST_CLERK 2200 122 50
137 Renske Ladwig RLADWIG 650.121.1234 14-JUL-03 ST_CLERK 3600 123 50
138 Stephen Stiles SSTILES 650.121.2034 26-OCT-05 ST_CLERK 3200 123 50
139 John Seo JSEO 650.121.2019 12-FEB-06 ST_CLERK 2700 123 50
140 Joshua Patel JPATEL 650.121.1834 06-APR-06 ST_CLERK 2500 123 50
141 Trenna Rajs TRAJS 650.121.8009 17-OCT-03 ST_CLERK 3500 124 50
142 Curtis Davies CDAVIES 650.121.2994 29-JAN-05 ST_CLERK 3100 124 50
143 Randall Matos RMATOS 650.121.2874 15-MAR-06 ST_CLERK 2600 124 50
144 Peter Vargas PVARGAS 650.121.2004 09-JUL-06 ST_CLERK 2500 124 50
145 John Russell JRUSSEL 011.44.1344.429268 01-OCT-04 SA_MAN 14000 .4 100 80
146 Karen Partners KPARTNER 011.44.1344.467268 05-JAN-05 SA_MAN 13500 .3 100 80
147 Alberto Errazuriz AERRAZUR 011.44.1344.429278 10-MAR-05 SA_MAN 12000 .3 100 80
148 Gerald Cambrault GCAMBRAU 011.44.1344.619268 15-OCT-07 SA_MAN 11000 .3 100 80
149 Eleni Zlotkey EZLOTKEY 011.44.1344.429018 29-JAN-08 SA_MAN 10500 .2 100 80
150 Peter Tucker PTUCKER 011.44.1344.129268 30-JAN-05 SA_REP 10000 .3 145 80
151 David Bernstein DBERNSTE 011.44.1344.345268 24-MAR-05 SA_REP 9500 .25 145 80
152 Peter Hall PHALL 011.44.1344.478968 20-AUG-05 SA_REP 9000 .25 145 80
153 Christopher Olsen COLSEN 011.44.1344.498718 30-MAR-06 SA_REP 8000 .2 145 80
154 Nanette Cambrault NCAMBRAU 011.44.1344.987668 09-DEC-06 SA_REP 7500 .2 145 80
155 Oliver Tuvault OTUVAULT 011.44.1344.486508 23-NOV-07 SA_REP 7000 .15 145 80
156 Janette King JKING 011.44.1345.429268 30-JAN-04 SA_REP 10000 .35 146 80
157 Patrick Sully PSULLY 011.44.1345.929268 04-MAR-04 SA_REP 9500 .35 146 80
158 Allan McEwen AMCEWEN 011.44.1345.829268 01-AUG-04 SA_REP 9000 .35 146 80
159 Lindsey Smith LSMITH 011.44.1345.729268 10-MAR-05 SA_REP 8000 .3 146 80
160 Louise Doran LDORAN 011.44.1345.629268 15-DEC-05 SA_REP 7500 .3 146 80
161 Sarath Sewall SSEWALL 011.44.1345.529268 03-NOV-06 SA_REP 7000 .25 146 80
162 Clara Vishney CVISHNEY 011.44.1346.129268 11-NOV-05 SA_REP 10500 .25 147 80
163 Danielle Greene DGREENE 011.44.1346.229268 19-MAR-07 SA_REP 9500 .15 147 80
164 Mattea Marvins MMARVINS 011.44.1346.329268 24-JAN-08 SA_REP 7200 .1 147 80
165 David Lee DLEE 011.44.1346.529268 23-FEB-08 SA_REP 6800 .1 147 80
166 Sundar Ande SANDE 011.44.1346.629268 24-MAR-08 SA_REP 6400 .1 147 80
167 Amit Banda ABANDA 011.44.1346.729268 21-APR-08 SA_REP 6200 .1 147 80
168 Lisa Ozer LOZER 011.44.1343.929268 11-MAR-05 SA_REP 11500 .25 148 80
169 Harrison Bloom HBLOOM 011.44.1343.829268 23-MAR-06 SA_REP 10000 .2 148 80
170 Tayler Fox TFOX 011.44.1343.729268 24-JAN-06 SA_REP 9600 .2 148 80
171 William Smith WSMITH 011.44.1343.629268 23-FEB-07 SA_REP 7400 .15 148 80
172 Elizabeth Bates EBATES 011.44.1343.529268 24-MAR-07 SA_REP 7300 .15 148 80
173 Sundita Kumar SKUMAR 011.44.1343.329268 21-APR-08 SA_REP 6100 .1 148 80
174 Ellen Abel EABEL 011.44.1644.429267 11-MAY-04 SA_REP 11000 .3 149 80
175 Alyssa Hutton AHUTTON 011.44.1644.429266 19-MAR-05 SA_REP 8800 .25 149 80
176 Jonathon Taylor JTAYLOR 011.44.1644.429265 24-MAR-06 SA_REP 8600 .2 149 80
177 Jack Livingston JLIVINGS 011.44.1644.429264 23-APR-06 SA_REP 8400 .2 149 80
178 Kimberely Grant KGRANT 011.44.1644.429263 24-MAY-07 SA_REP 7000 .15 149
179 Charles Johnson CJOHNSON 011.44.1644.429262 04-JAN-08 SA_REP 6200 .1 149 80
180 Winston Taylor WTAYLOR 650.507.9876 24-JAN-06 SH_CLERK 3200 120 50
181 Jean Fleaur JFLEAUR 650.507.9877 23-FEB-06 SH_CLERK 3100 120 50
182 Martha Sullivan MSULLIVA 650.507.9878 21-JUN-07 SH_CLERK 2500 120 50
183 Girard Geoni GGEONI 650.507.9879 03-FEB-08 SH_CLERK 2800 120 50
184 Nandita Sarchand NSARCHAN 650.509.1876 27-JAN-04 SH_CLERK 4200 121 50
185 Alexis Bull ABULL 650.509.2876 20-FEB-05 SH_CLERK 4100 121 50
186 Julia Dellinger JDELLING 650.509.3876 24-JUN-06 SH_CLERK 3400 121 50
187 Anthony Cabrio ACABRIO 650.509.4876 07-FEB-07 SH_CLERK 3000 121 50
188 Kelly Chung KCHUNG 650.505.1876 14-JUN-05 SH_CLERK 3800 122 50
189 Jennifer Dilly JDILLY 650.505.2876 13-AUG-05 SH_CLERK 3600 122 50
190 Timothy Gates TGATES 650.505.3876 11-JUL-06 SH_CLERK 2900 122 50
191 Randall Perkins RPERKINS 650.505.4876 19-DEC-07 SH_CLERK 2500 122 50
192 Sarah Bell SBELL 650.501.1876 04-FEB-04 SH_CLERK 4000 123 50
193 Britney Everett BEVERETT 650.501.2876 03-MAR-05 SH_CLERK 3900 123 50
194 Samuel McCain SMCCAIN 650.501.3876 01-JUL-06 SH_CLERK 3200 123 50
195 Vance Jones VJONES 650.501.4876 17-MAR-07 SH_CLERK 2800 123 50
196 Alana Walsh AWALSH 650.507.9811 24-APR-06 SH_CLERK 3100 124 50
197 Kevin Feeney KFEENEY 650.507.9822 23-MAY-06 SH_CLERK 3000 124 50
107 rows selected.
SQL> (4)执行复制PDB语句，
CREATE PLUGGABLE DATABASE cndbapdb4 from ORCLPDB1@LINK_ORCLPDB2
FILE_NAME_CONVERT = ('/opt/oracle/oradata/ORCLCDB/ORCLPDB1','/u02/oradata/CDB1/cndbapdb4');SQL> show user;
USER is "C##MAXWELL"
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> CREATE PLUGGABLE DATABASE cndbapdb4 from ORCLPDB1@LINK_ORCLPDB2
2 FILE_NAME_CONVERT = ('/opt/oracle/oradata/ORCLCDB/ORCLPDB1','/u02/oradata/CDB1/cndbapdb4');
Pluggable database created.
SQL> 检验是否复制成功，从下方结果看是成功的。
SQL> col owner for a20
SQL> column object_name for a20
SQL> select owner,object_name from dba_objects where object_type='DATABASE LINK';
OWNER OBJECT_NAME
-------------------- --------------------
SYS SYS_HUB
PUBLIC LINK_ORCLPDB2
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 MOUNTED
5 CNDBAPDB MOUNTED
6 CNDBAPDB3 MOUNTED
7 CNDBAPDB2 MOUNTED
8 CNDBAPDB4 MOUNTED
SQL> alter pluggable database cndbapdb4 open;
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 MOUNTED
5 CNDBAPDB MOUNTED
6 CNDBAPDB3 MOUNTED
7 CNDBAPDB2 MOUNTED
8 CNDBAPDB4 READ WRITE NO
SQL> 
开放原子开发者工作坊旨在鼓励更多人参与开源活动，与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动，如meetup、训练营等，主打技术交流，干货满满，真诚地邀请各位开发者共同参与！
更多推荐
0
0- 0
已为社区贡献10条内容
所有评论(0)