ANBOB™

在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)

打赏

微信扫一扫，打赏作者吧～