网站模板版权,网站建设电话咨询,辽宁工程建设信息网站,wordpress wechatOracle-创建PDB
创建PDB的方式
从PDB$SEED新建PDB克隆已存在的PDB 本地PDB克隆到同一个CDB中将远程PDB克隆到CDB中将非CDB插入或克隆到CDB中通过插拔的方式创建PDB sql 命令语法
条件
CDB必须open并且read write模式连接CDB$ROOT 用户并且具有CREATEPLUGGABLEDATABASE系统权…Oracle-创建PDB
创建PDB的方式
从PDB$SEED新建PDB克隆已存在的PDB 本地PDB克隆到同一个CDB中将远程PDB克隆到CDB中将非CDB插入或克隆到CDB中通过插拔的方式创建PDB sql 命令语法
条件
CDB必须open并且read write模式连接CDB$ROOT 用户并且具有CREATEPLUGGABLEDATABASE系统权限
具体实现
从PDB$SEED新建PDB 示例
CREATE PLUGGABLE DATABASE salespdbADMIN USER salesadm IDENTIFIED BY passwordROLES (dba)DEFAULT TABLESPACE salesDATAFILE /disk1/oracle/dbs/salespdb/sales01.dbf SIZE 250M AUTOEXTEND ONFILE_NAME_CONVERT (/disk1/oracle/dbs/pdbseed/,/disk1/oracle/dbs/salespdb/)STORAGE (MAXSIZE 2G)PATH_PREFIX /disk1/oracle/dbs/salespdb/;参数说明
关键字描述ADMIN USER用于执行管理任务的本地用户STORAGE (MAXSIZE 2G)指定了PDB可用的最大空间DEFAULT TABLESPACE为PDB指定默认的TablespacePATH_PREFIX用来限制directory objects/Oracle XML/Create pfile/Oracle wallets所在的目录FILE_NAME_CONVERT设置子容器和数据文件副本的位置
create pluggable database命令创建PDB2
-- 12.1
create pluggable database pdb2 admin user pdb2_admin identified by oracle roles(connect) file_name_convert(/u01/app/oracle/oradata/CDB3/pdbseed,/u01/app/oracle/oradata/CDB3/pdb2);-- 12.2
create pluggable database pdb2 admin user pdbadmin identified by oracle create_file_destDATA;
打开PDB2
alter pluggable database pdb2 open instancesall;
alter pluggable database pdb2 save state instancesall;
show pdbs;set lines 168 pages 99
col name for a16
col open_mode for a16
col restricted for a12
select inst_id, con_id, name, open_mode, restricted from gv$containers;克隆已存在的PDB
克隆本地PDB 在12.1版本中在创建PDB时SOURCE PDB必须处于read only状态在12.2版本中因为undo local mode新特性的推出在创建PDB时SOURCE PDB在read write状态也可以创建。而且在12.2版本中Oracle推出了refresh PDB特性具有对SOURCE PDB进行增量同步的功能。
注意事项
如果CDB为shared undoPDB必须为READ-ONLY状态如果CDB不是归档模式那么PDB必须为READ-ONLY状态如果是Oracle Database 版本为12.1那么PDB只能为READ-ONLY状态因在12.1中undo模式还只能选择shared undo使用的用户必须拥有 CREATE PLUGGABLE DATABASE 的权限
示例
CREATE PLUGGABLE DATABASE newpdb FROM salespdbFILE_NAME_CONVERT (/disk1/oracle/dbs/salespdb/, /disk1/oracle/dbs/newpdb/)PATH_PREFIX /disk1/oracle/dbs/newpdb;检查undo模式
COL PROPERTY_NAME FOR A24
COL PROPERTY_VALUE FOR A16
SELECT property_name, property_value
FROM database_properties
WHERE property_nameLOCAL_UNDO_ENABLED;克隆方式创建PDB
create pluggable database pdb3 from pdb2 file_name_convert(pdb2,pdb3);打开PDB3
alter pluggable database pdb3 open instancesall;
alter pluggable database pdb3 save state instancesall;
show pdbs;set lines 168 pages 99
col name for a16
col open_mode for a16
col restricted for a12
select inst_id, con_id, name, open_mode, restricted from gv$containers;克隆远程PDB或non-CDB 注意事项
如果PDB被Clone到的CDB的字符集不是AL32UTF8那么源与目标字符集必须兼容。源端与目标端的字节顺序必须相同.连接的用户在CDB中必须拥有CREATE PLUGGABLE DATABASE的权限源PDB不可以是关闭状态如果远端CDB为shared undo源PDB必须为READ-ONLY状态如果远端CDB不是归档模式源PDB必须为READ-ONLY状态
准备
查看源端与目标的字符集
col parameter for a30
col value for a30
select * from nls_database_parameters where parameterNLS_CHARACTERSET
or parameterNLS_LANGUAGE or parameterNLS_NCHAR_CHARACTERSET;检查字节序
col platform_name for a32
SELECT d.inst_id, t.platform_id, t.platform_name, t.endian_format, d.name FROM v$transportable_platform t, gv$database d
WHERE t.platform_name d.platform_name;检查undo模式
COL PROPERTY_NAME FOR A24
COL PROPERTY_VALUE FOR A16
SELECT property_name, property_value
FROM database_properties
WHERE property_nameLOCAL_UNDO_ENABLED;源库启动只读模式
startup open read only;
select open_mode from v$database;目标CDB中配置
配置tnsnames.ora文件
cd $ORACLE_HOME/network/admin
cat tnsnames.ora EOF
ORCL (DESCRIPTION (ADDRESS (PROTOCOL TCP)(HOST 192.168.10.181)(PORT 1533))(LOAD_BALANCE YES)(FAILOVER ON)(CONNECT_DATA (SERVER DEDICATED)(SERVICE_NAME ORCL)(FAILOVER_MODE (TYPE select)(METHOD basic)(RETRIES 180)(DELAY 5))))
EOF创建dblink
create database link dblink1 connect to sys identified by oracle using orcl;使用的用户必须拥有 CREATE PLUGGABLE DATABASE 的权限 验证dblink连接
select sysdate from dualdblink1;克隆方式创建PDB
-- non-CDB
create pluggable database pdb4 from non$cdbdblink1 file_name_convert(/u01/app/oracle/oradata/ORCL,/u01/app/oracle/oradata/CDB3/pdb4);-- remote pdb
create pluggable database pdb4 from pdb1dblink1 file_name_convert(/u01/app/oracle/oradata/ORCL,/u01/app/oracle/oradata/CDB3/pdb4);non-CDB转换pdb(可选)
alter session set containerpdb4;
show pdbs
?/rdbms/admin/noncdb_to_pdb.sql打开PDB
alter pluggable database pdb4 open instancesall;
alter pluggable database pdb4 save state instancesall;
show pdbs;set lines 168 pages 99
col name for a16
col open_mode for a16
col restricted for a12
select inst_id, con_id, name, open_mode, restricted from gv$containers;拔插PDB
拔出PDB 关闭PDB
alter pluggable database pdb1 close immediate instancesall;拔出PDB
alter pluggable database pdb1 unplug into /tmp/pdb1.xml;删除PDB
drop pluggable database pdb1 keep datafiles;keep datafiles保留数据文件不删除数据文件 将拔插的PDB插入CDB 检查兼容性 使用DBMS_PDB.CHECK_PLUG_COMPATIBILITY函数检查拔出的pdb1与cdb1是否兼容 SET SERVEROUTPUT ON
DECLAREcompatible CONSTANT VARCHAR2(3) :CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file /tmp/pdb1.xml,pdb_name PDB1)WHEN TRUE THEN YESELSE NO
END;
BEGINDBMS_OUTPUT.PUT_LINE(compatible);
END;
/示例
CREATE PLUGGABLE DATABASE salespdbUSING /disk1/usr/salespdb.xmlSOURCE_FILE_NAME_CONVERT (/disk1/oracle/dbs/salespdb/, /disk2/oracle/dbs/salespdb/)NOCOPYSTORAGE (MAXSIZE 2G)TEMPFILE REUSE;插入CDB1
create pluggable database pdb1 using /tmp/pdb1.xml nocopy;COPY - Copy the tablespace files to the new location.MOVE - Move the tablespace files to the new location.NOCOPY - Do not copy or move the tablespace files to the new location. 打开PDB
alter pluggable database pdb1 open instancesall;
alter pluggable database pdb1 save state instancesall;
show pdbs;set lines 168 pages 99
col name for a16
col open_mode for a16
col restricted for a12
select inst_id, con_id, name, open_mode, restricted from gv$containers;
