平武建站

Oracle12c怎么创建与删除CDB、PDBs

本篇内容介绍了“Oracle12c怎么创建与删除CDB、PDBs”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

创新互联建站基于成都重庆香港及美国等地区分布式IDC机房数据中心构建的电信大带宽，联通大带宽，移动大带宽，多线BGP大带宽租用,是为众多客户提供专业服务器托管报价，主机托管价格性价比高，为金融证券行业雅安电信机房，ai人工智能服务器托管提供bgp线路100M独享，G口带宽及机柜租用的专业成都idc公司。

一、计划多租户环境

准备足够的内存，磁盘空间；需要考虑的问题包括如下16个方面：

1.       PDB数量(max 253) 与空间计划

2.       后台进程数量（processes参数）：PDB+CDB用户

3.       DB_NAME，global db name

4.       Spfile为CDB和PDB共用，pdb可单独设置参数，操作spfile必须在root

5.       字符集为CDB和PDB共用，推荐al32utf8/al16utf16(国家字符集)

6.       时区可以CDB和PDB统一，也可以PDB单独设置

7.       Db_block_size为CDB内统一且不能修改

8.       Online redo log 的block size，redo log和control file都是实例级共用

9.       设计合适的sysaux，分别计划CDB和PDB的

10.    默认表空间，可分别计划CDB和PDB默认表空间，共享临时表空间

11.    默认临时表空间，可为分别计划CDB和PDB

12.    Undo 表空间，每个实例只有一个active undo 表空间

13.    规划Service

14.    熟悉CDB和PDB的启动与关闭

15.    是否使用RAC环境

16.    不支持的特征需要计划避开

二、创建与删除CDB&PDB

1.   创建CDB

DBCA（推荐）、create database

CREATE DATABASE必须包含ENABLE PLUGGABLE DATABASE从句，数据库创建时会默认创建root和seed；

数据文件的位置和名字：

1.     The SEED FILE_NAME_CONVERT clause

2.     Oracle Managed Files

3.     The PDB_FILE_NAME_CONVERT initialization parameter

创建CDB

CREATE DATABASE cdb_name

DATAFILE '/u01/app/oracle/oradata/newcdb/system01.dbf' SIZE 325M REUSE

SYSAUX DATAFILE '/u01/app/oracle/oradata/newcdb/sysaux01.dbf' SIZE 325M REUSE

SEED #seed container's tbs

SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED

SYSAUX DATAFILES SIZE 100M

Example 1: Creating a CDB Without Using Oracle Managed Files

CREATE DATABASE newcdb

  USER SYS IDENTIFIED BY sys_password

  USER SYSTEM IDENTIFIED BY system_password

  LOGFILE GROUP 1 ('/u01/logs/my/redo01a.log','/u02/logs/my/redo01b.log')

             SIZE 100M BLOCKSIZE 512,

          GROUP 2 ('/u01/logs/my/redo02a.log','/u02/logs/my/redo02b.log')

             SIZE 100M BLOCKSIZE 512,

          GROUP 3 ('/u01/logs/my/redo03a.log','/u02/logs/my/redo03b.log')

             SIZE 100M BLOCKSIZE 512

  MAXLOGHISTORY 1

  MAXLOGFILES 16

  MAXLOGMEMBERS 3

  MAXDATAFILES 1024

  CHARACTER SET AL32UTF8

  NATIONAL CHARACTER SET AL16UTF16

  EXTENT MANAGEMENT LOCAL

  DATAFILE '/u01/app/oracle/oradata/newcdb/system01.dbf'

    SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED

  SYSAUX DATAFILE '/u01/app/oracle/oradata/newcdb/sysaux01.dbf'

    SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED

  DEFAULT TABLESPACE deftbs

     DATAFILE '/u01/app/oracle/oradata/newcdb/deftbs01.dbf'

     SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

  DEFAULT TEMPORARY TABLESPACE tempts1

     TEMPFILE '/u01/app/oracle/oradata/newcdb/temp01.dbf'

     SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED

  UNDO TABLESPACE undotbs1

     DATAFILE '/u01/app/oracle/oradata/newcdb/undotbs01.dbf'

     SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED

  ENABLE PLUGGABLE DATABASE

    SEED

    FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/newcdb/',

                         '/u01/app/oracle/oradata/pdbseed/')

    SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED

    SYSAUX DATAFILES SIZE 100M

  USER_DATA TABLESPACE usertbs

    DATAFILE '/u01/app/oracle/oradata/pdbseed/usertbs01.dbf'

SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Example 2: Creating a CDB Using Oracle Managed Files

parameter file:

DB_CREATE_FILE_DEST='/u01/app/oracle/oradata'

This example sets the parameter Oracle ASM storage:

DB_CREATE_FILE_DEST = +data

CREATE DATABASE newcdb

USER SYS IDENTIFIED BY sys_password

USER SYSTEM IDENTIFIED BY system_password

EXTENT MANAGEMENT LOCAL

DEFAULT TABLESPACE users

DEFAULT TEMPORARY TABLESPACE temp

UNDO TABLESPACE undotbs1

ENABLE PLUGGABLE DATABASE

   SEED

   SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED

   SYSAUX DATAFILES SIZE 100M;

执行catcdb.sql，安装CDB组件

@?/rdbms/admin/catcdb.sql

 SYSDBA 执行如下脚本：

@?/rdbms/admin/catalog.sql

@?/rdbms/admin/catproc.sql

@?/rdbms/admin/utlrp.sql

SYSTEM 用户执行：

@?/sqlplus/admin/pupbld.sql

2.   配置EM express

SYSDBA 权限执行：

exec DBMS_XDB_CONFIG.SETHTTPPORT(http_port_number);

exec DBMS_XDB_CONFIG.SETHTTPSPORT(https_port_number);

每个container必须使用一个唯一的端口，访问方法：

http://database_hostname:http_port_number/em/

https://database_hostname:https_port_number/em/

3.   创建PDB

Figure 38-1 Options for Creating a PDB

                Oracle12c怎么创建与删除CDB、PDBs                             

Technique

Description

Create a PDB by using the seed

Create a PDB in a CDB using the files of the seed. This   technique copies the files associated with the seed to a new location and   associates the copied files with the new PDB.

Create a PDB by cloning an existing PDB or non-CDB

Create a PDB by cloning a source PDB or non-CDB and plugging   the clone into the CDB. A source can be a PDB in the local CDB, a PDB in a   remote CDB, or a non-CDB. This technique copies the files associated with the   source to a new location and associates the copied files with the new PDB.

Create a PDB by plugging an unplugged PDB into a CDB

Create a PDB by using the XML metadata file that describes the   PDB and the files associated with the PDB to plug it into the CDB.

Create a PDB by using a non-CDB

Create a PDB by moving a non-CDB into a PDB. You can use   the DBMS_PDB package   to create an unplugged PDB from an Oracle Database 12c non-CDB.   You can then plug the unplugged PDB into the CDB.

1)     CREATE PLUGGABLE DATABASE语句

a.      存储限制

STORAGE (MAXSIZE 2G)

STORAGE (MAXSIZE UNLIMITED)

b.      PDB文件位置

FILE_NAME_CONVERT

DB_CREATE_FILE_DEST

PDB_FILE_NAME_CONVERT

PATH_PREFIX #pdb文件位置限制

SOURCE_FILE_NAME_CONVERT、SOURCE_FILE_DIRECTORY #插拔pdb的

c.      其他

SERVICE_NAME_CONVERT #重命名服务名

TEMPFILE REUSE #临时文件重用

USER_TABLESPACES #move non-cdb to pdb时指定TBS,ALL /ALL EXCEPT /NONE/list('tbs1','tbs4','tbs5')

LOGGING/NOLOGGING #表空间日志记录

STANDBYS=ALL/NONE #standby CDB

NO DATA #克隆pdb时，指定不克隆数据

2)     创建PDB前的准备

在创建PDB之前，必须满足先决条件。确保在创建PDB之前满足以下先决条件:

•CDB必须存在。参见创建和配置CDB。

•CDB必须处于读/写模式。

•当前用户必须是root容器的Common User。

•当前用户必须具有CREATE PLUGGABLE DATABASE系统权限。

•您必须为每个PDB确定唯一的PDB名称。每个PDB名称在单个CDB中必须是惟一的，并且每个PDB名称在其实例通过特定侦听器到达的所有CDBs的范围内必须是惟一的。

3)     用seed创建PDB

在使用SNAPSHOT COPY子句时，源PDB的所有数据文件必须存储在相同的存储类型中。

当使用SNAPSHOT COPY子句创建源PDB的克隆且CLONEDB初始化参数设置为FALSE时，源PDB文件的底层文件系统必须支持存储快照。这些文件系统包括Oracle自动存储管理集群文件系统(Oracle ACFS)和直接NFS客户端存储。

当你使用SNAPSHOT COPY来创建一个克隆，CLONEDB初始化参数设置为TRUE，底层文件系统源码PDB文件可以是任何的本地文件系统、网络文件系统(NFS)，或集群文件系统，直接启用NFS。但是，只要存在克隆，源PDB必须保持开放只读模式。

Example 38-18 Creating a PDB Using No Clauses

CREATE PLUGGABLE DATABASE salespdb ADMIN USER salesadm IDENTIFIED BY password;

Example 38-19 Creating a PDB and Granting Predefined Oracle Roles to the PDB Administrator

CREATE PLUGGABLE DATABASE salespdb ADMIN USER salesadm IDENTIFIED BY password

  ROLES=(DBA);

Example 38-20 Creating a PDB Using the STORAGE, DEFAULT TABLESPACE, PATH_PREFIX, and FILE_NAME_CONVERT Clauses

CREATE PLUGGABLE DATABASE salespdb ADMIN USER salesadm IDENTIFIED BY password

  STORAGE (MAXSIZE 2G)

  DEFAULT TABLESPACE sales

    DATAFILE '/disk1/oracle/dbs/salespdb/sales01.dbf' SIZE 250M AUTOEXTEND ON

  PATH_PREFIX = '/disk1/oracle/dbs/salespdb/'

  FILE_NAME_CONVERT = ('/disk1/oracle/dbs/pdbseed/', '/disk1/oracle/dbs/salespdb/');

4)     克隆PDB或者non-CBD创建PDB

A.       克隆Local PDB

Example 38-21 Cloning a Local PDB Using No Clauses

CREATE PLUGGABLE DATABASE pdb2 FROM pdb1;

Example 38-22 Cloning a Local PDB With the PATH_PREFIX, FILE_NAME_CONVERT, and SERVICE_NAME_CONVERT Clauses

CREATE PLUGGABLE DATABASE pdb2 FROM pdb1

  PATH_PREFIX = '/disk2/oracle/pdb2'

  FILE_NAME_CONVERT = ('/disk1/oracle/pdb1/', '/disk2/oracle/pdb2/')

  SERVICE_NAME_CONVERT = ('salesrep','salesperson','orders','orderentry')

  NOLOGGING;

Example 38-23 Cloning a Local PDB Using the FILE_NAME_CONVERT, STORAGE, and SERVICE_NAME_CONVERT Clauses

CREATE PLUGGABLE DATABASE pdb2 FROM pdb1

  FILE_NAME_CONVERT = ('/disk1/oracle/pdb1/', '/disk2/oracle/pdb2/')

  STORAGE (MAXSIZE 2G)

  SERVICE_NAME_CONVERT = ('salesrep','salesperson','orders','orderentry');

Example 38-24 Cloning a Local PDB Without Cloning Its Data

ALTER PLUGGABLE DATABASE pdb1 OPEN READ ONLY;

CREATE PLUGGABLE DATABASE pdb2 FROM pdb1 NO DATA;

ALTER PLUGGABLE DATABASE pdb2 OPEN;

B.       克隆远程PDB 或者non-CDB

Example 38-25 Creating a PDB by Cloning a Remote PDB Using No Clauses

CREATE PLUGGABLE DATABASE pdb2 FROM pdb1@pdb1_link;

Example 38-26 Creating a PDB by Cloning a Remote Non-CDB

CREATE PLUGGABLE DATABASE pdb2 FROM mydb@mydb_link;
@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

5)     插入PDB的方法创建PDB

BEGIN

  DBMS_PDB.DESCRIBE(

    pdb_descr_file => '/disk1/oracle/salespdb.xml',

    pdb_name       => 'SALESPDB');

END;

/

SET SERVEROUTPUT ON

DECLARE

  compatible CONSTANT VARCHAR2(3) :=

    CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(

           pdb_descr_file => '/disk1/usr/salespdb.xml',

           pdb_name       => 'SALESPDB')

    WHEN TRUE THEN 'YES'

    ELSE 'NO'

END;

BEGIN

  DBMS_OUTPUT.PUT_LINE(compatible);

END;

/

Example 38-27 Plugging In an Unplugged PDB Using the NOCOPY Clause

CREATE PLUGGABLE DATABASE salespdb USING '/disk1/usr/salespdb.xml'

  NOCOPY

  TEMPFILE REUSE;

Example 38-28 Plugging In an Unplugged PDB Using the AS CLONE and NOCOPY Clauses

CREATE PLUGGABLE DATABASE salespdb AS CLONE USING '/disk1/usr/salespdb.xml'

  NOCOPY

  TEMPFILE REUSE;

Example 38-29 Plugging In an Unplugged PDB Using the SOURCE_FILE_NAME_CONVERT, NOCOPY, and STORAGE Clauses

CREATE PLUGGABLE DATABASE salespdb USING '/disk1/usr/salespdb.xml'

  SOURCE_FILE_NAME_CONVERT = ('/disk1/oracle/sales/', '/disk2/oracle/sales/')

  NOCOPY

  STORAGE (MAXSIZE 2G)

  TEMPFILE REUSE;

Example 38-30 Plugging In an Unplugged PDB With the COPY, PATH_PREFIX, and FILE_NAME_CONVERT Clauses

CREATE PLUGGABLE DATABASE salespdb USING '/disk1/usr/salespdb.xml'

  COPY

  PATH_PREFIX = '/disk2/oracle/sales/'

  FILE_NAME_CONVERT = ('/disk1/oracle/sales/', '/disk2/oracle/sales/');

Example 38-31 Plugging In an Unplugged PDB Using the SOURCE_FILE_NAME_CONVERT, MOVE, FILE_NAME_CONVERT, and STORAGE Clauses

CREATE PLUGGABLE DATABASE salespdb USING '/disk1/usr/salespdb.xml'

  SOURCE_FILE_NAME_CONVERT = ('/disk1/oracle/sales/', '/disk2/oracle/sales/')

  MOVE

  FILE_NAME_CONVERT = ('/disk2/oracle/sales/', '/disk3/oracle/sales/')

  STORAGE (MAXSIZE 2G);

Example 38-32 Plugging In an Unplugged PDB Using the SOURCE_FILE_DIRECTORY, MOVE, FILE_NAME_CONVERT, and STORAGE Clauses

CREATE PLUGGABLE DATABASE salespdb USING '/disk1/usr/salespdb.xml'

  SOURCE_FILE_DIRECTORY = '/disk2/oracle/sales/' 

  MOVE

  FILE_NAME_CONVERT = ('/disk2/oracle/sales/', '/disk3/oracle/sales/')

  STORAGE (MAXSIZE 2G);

6)     用Non-CDB创建PDB

BEGIN

  DBMS_PDB.DESCRIBE(

    pdb_descr_file => '/disk1/oracle/ncdb.xml');

END;

/

SET SERVEROUTPUT ON

DECLARE

  compatible CONSTANT VARCHAR2(3) :=

    CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(

           pdb_descr_file => '/disk1/oracle/ncdb.xml',

           pdb_name       => 'NCDB')

    WHEN TRUE THEN 'YES'

    ELSE 'NO'

END;

BEGIN

  DBMS_OUTPUT.PUT_LINE(compatible);

END;

/

CREATE PLUGGABLE DATABASE ncdb USING '/disk1/oracle/ncdb.xml'

  COPY

  FILE_NAME_CONVERT = ('/disk1/oracle/dbs/', '/disk2/oracle/ncdb/')

  USER_TABLESPACES=('tbs3');

@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

4.   Unplugging PDB

Example 38-33 Unplugging PDB salespdb

ALTER PLUGGABLE DATABASE salespdb UNPLUG INTO '/oracle/data/salespdb.xml';

5.   删除PDB

删除PDB时，将修改CDB的控制文件，以消除对删除的PDB的所有引用。与PDB关联的归档重做日志文件和备份不会被删除，但是您可以使用Oracle Recovery Manager (RMAN)删除它们。

Example 38-34 Dropping PDB salespdb While Keeping Its Data Files

DROP PLUGGABLE DATABASE salespdb KEEP DATAFILES;

Example 38-35 Dropping PDB salespdb and Its Data Files

DROP PLUGGABLE DATABASE salespdb INCLUDING DATAFILES;

“Oracle12c怎么创建与删除CDB、PDBs”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注创新互联网站，小编将为大家输出更多高质量的实用文章！


标题名称：Oracle12c怎么创建与删除CDB、PDBs
URL网址：http://pwwzsj.com/article/jjgisj.html

其他资讯