首页 » ORACLE » Oracle 12c R2 新特性: Per-PDB Character Sets (同一CDB中PDB可以使用不同的字符集)

Oracle 12c R2 新特性: Per-PDB Character Sets (同一CDB中PDB可以使用不同的字符集)

在oracle12.1版本中,同一CDB中的所有PDB使用的都是相同的字符集,并且Plug-in时PDB也要和目标CDB相同字符集或者是子集,否则plug-in时会失败在PDB_PLUG_IN_VIOLATIONS视图提示,这样影响了PDB的迁移灵活性,在MOS Note 1968706.1摘录

In Oracle Database 12c, all pluggable databases (PDBs) in a container database (CDB) must have
* the same Database character set (NLS_CHARACTERSET) or the NLS_CHARACTERSET need to be a (Plug-in compatible) binary subset of the CDB NLS_CHARACTERSET
* the same National character set (NLS_NCHAR_CHARACTERSET)
  as the CDB’s root container
in order to be able to plug in.

If PDB NLS_CHARACTERSET is a (Plug-in compatible) binary subset of the CDB’s NLS_CHARACTERSET the NLS_CHARACTERSET of the PDB is automatically changed to the NLS_CHARACTERSET of the CDB at the first open.

The character sets of the root container are considered the character sets of the whole CDB.

 

从12.2起引入新特性同一CDB中每个PDB可以使用不同的字符集,前提CDB是AL32UTF8。 如CDB是AL32UTF8,其中的PDB1为WE8ISO8859P1 , PDB2为ZHS16GBK, 从其它CDB plug-in一个JA16EUC字符集的PDB也是允许的,在plug-in PDB的过程中PDB会原封不动的进入目标CDB,过程中并不会转换字符集,并且以后新insert的数据是直接使用的原PDB的字符集。

Per-PDB Character Set的有几个前提条件:
1) CDB must be AL32UTF8
2) Application Container requires single character set
3) National character set also supported per PDB
4) Truncation of data can occur in cross-container queries if data conversion to UNICODE causes expansion

在CDB必须是AL32UTF8时(DBCA 时12.2的默认项),虽然同一个CDB中每个PDB可以使用不同的PDB,但是发现当前版本12.2.0.1对于新创建PDB时并没有办法指定PDB 的字符集,对于已存在的PDB 可以使用hot clone,或Relocate PDB online 方式直接plug-in, 像我之前的笔记[Oracle 12c R2 新特性: Online PDB Relocate (PDB hot move)http://www.anbob.com/archives/2833.html]中,也是直接把ZHS16GBK 字符集的PDB Plug-in 到了AL32UTF8的CDB中。

如为了以后同一个CDB中的PDB可以使用不同字符集的PDB, 前期规划时建议CDB直接选择AL32UTF8字符集, 但是如果目前想创建一个ZHS16GBK的空PDB时怎么办呢? 目前没有提供创建PDB指定字符集的选项。这种情况下,我目前能想到两种方式。

1, 第一种迁移;创建一个ZHS16GBK 的CDB, 同时自带ZHS16GBK的PDB, 然后通过HOT CLONE PDB或Relocate PDB的方式迁移到另一个AL32UTF8字符集的CDB中。
该操作可以在同一台数据库服务器上创建,因为同一server是可以创建不同的CDB的。具体的方法就不再重复,可以参考ANBOB.com上我之前的clone,relocate的笔记。

2, 第二种转换;可以创建一个AL32UTF8的PDB 然后转换为ZHS16GBK,下面使用了internal_use,这个方法在ORACLE是不太建议的。

下面CDB 是AL32UTF8 并且PDBANBOB 也是AL32UTF8后来转换为ZHS16GBK。 字符集的可用值可以查询V$NLS_VALID_VALUES 视图, 当前的值可以查询V$NLS_PARAMETERS 视图。

Single Instance

--lang.sql:
col PROPERTY_NAME for a30
col PROPERTY_VALUE for a30
col DESCRIPTION for a30
select * from database_properties where PROPERTY_NAME='NLS_CHARACTERSET';

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBANBOB                       MOUNTED
         5 PDBTEST2_PROXY                 MOUNTED
         6 PDBWEEJAR                      MOUNTED
SQL> @lang
PROPERTY_NAME                  PROPERTY_VALUE                 DESCRIPTION
------------------------------ ------------------------------ ------------------------------
NLS_CHARACTERSET               AL32UTF8                       Character set

SQL> alter pluggable database PDBANBOB open;
Pluggable database altered.

SQL> alter session set container=pdbanbob;
Session altered.

SQL> @lang
PROPERTY_NAME                  PROPERTY_VALUE                 DESCRIPTION
------------------------------ ------------------------------ ------------------------------
NLS_CHARACTERSET               AL32UTF8                       Character set

SQL> alter database character set ZHS16GBK;
alter database character set ZHS16GBK
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set

SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK;
ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK
*
ERROR at line 1:
ORA-12719: operation requires database is in RESTRICTED mode


SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
System altered.

SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK;
Database altered.


SQL> @lang 
PROPERTY_NAME                  PROPERTY_VALUE                 DESCRIPTION
------------------------------ ------------------------------ ------------------------------
NLS_CHARACTERSET               ZHS16GBK                       Character set

 

RAC Instance

rac实例修改时和单实例有点不多,需要先停提所有其它instance,修改后再打开。

alter pluggable databse pdbanbob close instances=all;

alter pluggable database  pdbanbob open read write  restricted;
-- in container pdbanbob
alter database character set internal_use zhs16gbk;
alter pluggable databse pdbanbob close;

alter pluggable database  pdbanbob open instances=all;

已经把pdbanbob 字符集修改成了ZHS16GBK, 同样在12C的版本中推出了Oracle Database Migration Assistant for Unicode(DMU)工具, 如果有兴趣可以去官方免费下载测试

 

You can use Oracle Database Migration Assistant for Unicode (DMU) to migrate a non-CDB NLS_CHARACTERSET to AL32UTF8. Note 1272374.1 The Database Migration Assistant for Unicode (DMU) Tool

There is a few restrictions with DMU  :

1- It does not support the migration of Oracle Database 12c Pluggable Databases (PDBs) to NLS_CHARACTERSET other than Unicode.

Oracle strongly recommends migrating the database character set to Unicode (AL32UTF8) NLS_CHARACTERSET before consolidating your databases.

2- You will receive the following error upon Initiating step 4 “convert database” using the DMU while being connected to PDB:

conversion feasibility test failed . the migration target character set is different from the CDB root character set.

To go from a non-Unicode CDB with (also non-Unicode) PDB’s the steps are:
* Create a new Unicode (AL32UTF8) CDB
* Use the DMU to scan the non-Unicode PDB’s and resolve any reported convertibility issues while it is still plugged into the original non-Unicode CDB.
* Unplug the PDB to be migrated and plug it into the target Unicode AL32UTF8 CDB (this will put the PDB into restricted mode due to the character set incompatibility).
* Use the DMU tool to convert the non-Unicode PDB to Unicode once plugged in the target Unicode AL32UTF8 CDB (= finish the conversion).

3- DMU does not support the migration of Oracle Database 12c container database (CDB) to any NLS_CHARACTERSET ( as this is not needed).

You will receive the following error upon connecting to CDB using the DMU :

Encountered an error while checking database’s compatibility: Migration of container databases (CDB) is not supported.

The export/import migration methods could be used to overcome these limitations.

 

References:

12c Multitenant Container Databases (CDB) and Pluggable Databases (PDB) Character set restrictions / ORA-65116/65119: incompatible database/national character set ( Character set mismatch: PDB character set CDB character set ) (文档 ID 1968706.1)

打赏

, ,

对不起,这篇文章暂时关闭评论。