Creating and Configuring a CDB_一个服务器是否只能对应一个cdb-CSDN博客

本文链接：https://blog.csdn.net/linsuhangoracle/article/details/119536342

Oracle使用create database或dbca来创建数据库实例，只是现在分为CDB实例与non-CDB实例，单台服务器可安装多个实例，它们间互不影响

Note：

A CDB can contain up to 4,096 PDBs(including PDBs, application roots, application PDBs, application seeds, and proxy PDBs. This number does not include the CDB root and the CDB seed), but you can limit the CDB to a smaller number of PDBs by setting the MAX_PDBS initialization parameter.
CDB字符集建议设置AL32UTF8, national character set设置为AL16UTF6：

When the character set of the root is AL32UTF8, PDBs that are plugged into the CDB or cloned can have a different character set than the root. PDBs that are created from the CDB seed inherit the AL32UTF8 from it, but you can migrate the PDB to a different character set.

When the character set of the root is not AL32UTF8, all of the PDBs in the CDB use the character set of the root.

Oracle recommends AL32UTF8 for the CDB database character set and AL16UTF6 for the CDB national character set because they provide the most flexibility.

When moving a non-CDB to a CDB, it is best to migrate the non-CDB to AL32UTF8 first. You can use Oracle Database Migration Assistant for Unicode (DMU) to migrate a non-CDB to AL32UTF8. After a CDB is created, you cannot migrate the CDB's character set using DMU.

The standard block size applies to the entire CDB.
CDB共用redo log及contorlfile; undo表空间可以为local或share; 其它表空间各用各的

使用CREATE DATABASE创建说明

cdb$seed创建之后不能更改
cdb$root及pdb$seed均包含数据文件，create database语句中直接指定root数据文件属性，seed数据文件使用seed子句指定，如果不指定默认继承root数据文件属性及位置，所以不使用OMF需要使用进行路径转化。可以用下面三种方指定seed数据文件位置，如果指定了多种方式，则优先顺序如下，如指定了三种方式，则seed file_name_convert生效

The SEED FILE_NAME_CONVERT clause

Oracle Managed Files

The PDB_FILE_NAME_CONVERT initialization parameter

seed只可以更改继承的SYSTEM及SYSAUX表空间(除数据文件的位置，名字及reuse属性)

The syntax of the tablespace_datafile clauses is the same as the syntax for a data file specification, excluding the name and location of the data file and the REUSE attribute.

如create database中root数据文件使用：

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的system与sysaux继承了root数据文件的reuse属性，但有不同的大小，autoextend属性

SEED

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

SYSAUX DATAFILES SIZE 100M

UNDO_MODE, 建议使用local

undo有两种方式：共享或各自单独使用，如果不指定undo_mode_clause则默认为共享

When you choose to have one active undo tablespace for the entire CDB, shared undo is used, and local undo is disabled. In this configuration, there is one active undo tablespace for a single-instance CDB. When local undo is enabled, there is one undo tablespace for each container in a single instance configuration. For an Oracle RAC CDB, each PDB has one undo tablespace in each node in which it is open. With shared undo, only a common user who has the appropriate privileges and whose current container is the root can create an undo tablespace.

The best practice is to use local undo for a CDB. Shared undo is supported primarily for upgrade and transitional purposes only. Although there is minor overhead associated with local undo when compared with shared undo, the benefits of local undo make it preferable in most environments. Local undo makes unplug operations and point in time recovery faster, and it is required for some features, such as relocating a PDB. By default, DBCA creates new CDBs with local undo enabled.

In a CDB, the UNDO_MANAGEMENT initialization parameter must be set to AUTO, and an undo tablespace is required to manage the undo data.

When local undo is not enabled, undo tablespaces are visible in static data dictionary views and dynamic performance (V$) views when the current container is the root. Undo tablespaces are visible only in dynamic performance views when the current container is a PDB.

Also, when local undo is disabled, Oracle Database silently ignores undo tablespace and rollback segment operations when the current container is a PDB.

查询当前库是否为UNDO LOCAL:

select PROPERTY_NAME,PROPERTY_VALUE from database_properties where property_name = 'LOCAL_UNDO_ENABLED';

使用OMF

需要在pfile中配置DB_CREATE_FILE_DEST 参数

创建的CDB及PDB$SEED的数据文件均以OMF方式命令，create database不用指定文件名，也不用指定seed file_name_convert子句

CREATE DATABASE orcl

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;

使用SEED FILE_NAME_CONVERT clause

语法: SEED FILE_NAME_CONVERT = ('string1', 'string2','string3', 'string4'...)

这里string2替代string1, string4替代string3

注：下面目录要提前创建，create database不会创建不存在的目录，如果失败需要删除目录中创建的文件重新执行

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/')

LOCAL UNDO ON;

这里关于日志块大小建议不设置：

The block size for the redo logs is set to 512 bytes, the same size as physical sectors on disk. The BLOCKSIZE clause is optional if block size is to be the same as physical sector size (the default). Typical sector size and thus typical block size is 512. Permissible values for BLOCKSIZE are 512, 1024, and 4096. For newer disks with a 4K sector size, optionally specify BLOCKSIZE as 4096.

MAXDATAFILES建议设置最大值：

MAXDATAFILES specifies the maximum number of data files that can be open in the CDB. This number affects the initial sizing of the control file. For a CDB, set MAXDATAFILES to a high number that anticipates the aggregate number of data files for all containers, in addition to the CDB root files.

一些限制设置可能跟OS限制相关：

You can set several limits during CDB creation. Some of these limits are limited by and affected by operating system limits. For example, if you set MAXDATAFILES, then Oracle Database allocates enough space in the control file to store MAXDATAFILES file names, even if the CDB has only one data file initially. However, because the maximum control file size is limited and operating system dependent, you might not be able to set all CREATE DATABASE parameters at their theoretical maximums.