19Ccdb同pdb字符集不同，导致pdb无法启动

jiawang · 发表于 2022-8-23 17:09:44

[oracle@sztech orcl]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 23 16:02:55 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show pdbs

CON_ID CON_NAME                      OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
      2 PDB$SEED                      READ ONLY  NO
      5 ORCL                         MOUNTED
SQL>  alter pluggable database orcl open;

Warning: PDB altered with errors.

orcl实例虽然启动，但是只能在独占模式下启动，显然是存在问题的，我们通过alert日志查找更多的信息，发现启动时候，出现如下信息
SQL> show pdbs

CON_ID CON_NAME                      OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
      2 PDB$SEED                      READ ONLY  NO
      5 ORCL                         READ WRITE YES

查看alter日志：

2022-08-23T16:53:30.247285+08:00
ORCL(5):Undo initialization recovery: err:0 start: 23930466 end: 23930472 diff: 6 ms (0.0 seconds)
ORCL(5):[30050] Successfully onlined Undo Tablespace 2.
ORCL(5):Undo initialization online undo segments: err:0 start: 23930472 end: 23931152 diff: 680 ms (0.7 seconds)
ORCL(5):Undo initialization finished serial:0 start:23930466 end:23931179 diff:713 ms (0.7 seconds)
2022-08-23T16:53:32.460876+08:00
Violations: Type: 1, Count: 1
Violations: Type: 2, Count: 2
ORCL(5):***************************************************************
ORCL(5):WARNING: Pluggable Database ORCL with pdb id - 5 is
ORCL(5):       altered with errors or warnings. Please look into
ORCL(5):       PDB_PLUG_IN_VIOLATIONS view for more details.
ORCL(5):***************************************************************
2022-08-23T16:53:44.728077+08:00
ORCL(5):Opening pdb with no Resource Manager plan active
ORCL(5):joxcsys_required_dirobj_exists: directory object exists with required path /u01/app/oracle/product/19.3.0/db_1/javavm/admin/, pid 30050 cid 5
2022-08-23T16:53:46.648573+08:00
Pluggable database ORCL opened read write
Completed:  alter pluggable database orcl open

提示很清楚，叫我们去查询视图PDB_PLUG_IN_VIOLATIONS，该视图需要在CDB环境下去查询
SQL>  select name,type,message from  PDB_PLUG_IN_VIOLATIONs order by name;

NAME                TYPE    MESSAGE
-------------------- --------- ----------------------------------------------------------------------
ORCL                ERROR    Character set mismatch: PDB character set WE8MSWIN1252. CDB character
                           set ZHS16GBK.

ORCL                WARNING Database option DV mismatch: PDB installed version NULL. CDB installed
                              version 19.0.0.0.0.

ORCL                WARNING Database option OLS mismatch: PDB installed version NULL. CDB installe
                           d version 19.0.0.0.0.

ERROR提示，信息很明确，提示的是PDB的字符集为WE8MSWIN1252,而CDB的字符集为ZHS16GBK,看来在19C的情况下，CDB必须同其包含的PDB保持一致的字符集，否则无法启动。

郑全 · 发表于 2022-8-24 11:06:08

如果PDB字符集想和CDB不一样，cdb$ROOT的字符集必须是AL32UTF8 字符集才行。

jiawang · 发表于 2022-8-24 15:46:46

本帖最后由 jiawang 于 2022-8-24 15:53 编辑

郑全发表于 2022-8-24 11:06
如果PDB字符集想和CDB不一样，cdb$ROOT的字符集必须是AL32UTF8 字符集才行。

SQL> select value,parameter from NLS_DATABASE_PARAMETERS where PARAMETER like '%CHARACTERSET';

VALUE                         PARAMETER
------------------------------ ------------------------------
AL16UTF16                   NLS_NCHAR_CHARACTERSET
ZHS16GBK                      NLS_CHARACTERSET

SQL>
SQL>
SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK

SQL> select * from nls_database_parameters;

PARAMETER                   VALUE
------------------------------ ------------------------------
NLS_RDBMS_VERSION             19.0.0.0.0
NLS_NCHAR_CONV_EXCP          FALSE
NLS_LENGTH_SEMANTICS          BYTE
NLS_COMP                      BINARY
NLS_DUAL_CURRENCY             $
NLS_TIMESTAMP_TZ_FORMAT       DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT          HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT          DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT             HH.MI.SSXFF AM
NLS_SORT                      BINARY
NLS_DATE_LANGUAGE             AMERICAN

PARAMETER                   VALUE
------------------------------ ------------------------------
NLS_DATE_FORMAT             DD-MON-RR
NLS_CALENDAR                GREGORIAN
NLS_NUMERIC_CHARACTERS       .,
NLS_NCHAR_CHARACTERSET       AL16UTF16
NLS_CHARACTERSET             ZHS16GBK
NLS_ISO_CURRENCY             AMERICA
NLS_CURRENCY                $
NLS_TERRITORY                AMERICA
NLS_LANGUAGE                AMERICAN

20 rows selected.

修改字符集
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> alter session set sql_trace=true;
alter session set sql_trace=true
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

SQL> satrtup mount;
SP2-0734: unknown command beginning "satrtup mo..." - rest of line ignored.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1241510080 bytes
Fixed Size                9134272 bytes
Variable Size          452984832 bytes
Database Buffers       771751936 bytes
Redo Buffers             7639040 bytes
Database mounted.
SQL> alter session set sql_trace=true;

Session altered.

SQL> alter system enable restricted session;

System altered.

SQL> alter system set job_queue_processes=0;

System altered.

SQL> alter system set aq_tm_processes=0;

System altered.

SQL> ALTER DATABASE character set INTERNAL_USE AL32UTF8;
ALTER DATABASE character set INTERNAL_USE AL32UTF8
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01109: database not open

SQL> alter database open;

Database altered.

SQL> ALTER DATABASE character set INTERNAL_USE AL32UTF8;

Database altered.

SQL>  select * from nls_database_parameters;

PARAMETER                   VALUE
------------------------------ ------------------------------
NLS_RDBMS_VERSION             19.0.0.0.0
NLS_NCHAR_CONV_EXCP          FALSE
NLS_LENGTH_SEMANTICS          BYTE
NLS_COMP                      BINARY
NLS_DUAL_CURRENCY             $
NLS_TIMESTAMP_TZ_FORMAT       DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT          HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT          DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT             HH.MI.SSXFF AM
NLS_SORT                      BINARY
NLS_DATE_LANGUAGE             AMERICAN

PARAMETER                   VALUE
------------------------------ ------------------------------
NLS_DATE_FORMAT             DD-MON-RR
NLS_CALENDAR                GREGORIAN
NLS_NUMERIC_CHARACTERS       .,
NLS_NCHAR_CHARACTERSET       AL16UTF16
NLS_CHARACTERSET             AL32UTF8
NLS_ISO_CURRENCY             AMERICA
NLS_CURRENCY                $
NLS_TERRITORY                AMERICA
NLS_LANGUAGE                AMERICAN

20 rows selected.

SQL>

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 1241510080 bytes
Fixed Size                9134272 bytes
Variable Size          452984832 bytes
Database Buffers       771751936 bytes
Redo Buffers             7639040 bytes
Database mounted.
Database opened.
SQL> show pdbs

CON_ID CON_NAME                      OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
      2 PDB$SEED                      READ ONLY  NO
      5 ORCL                         MOUNTED

SQL>  alter pluggable database orcl open;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME                      OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
      2 PDB$SEED                      READ ONLY  NO
      5 ORCL                         READ WRITE NO

验证字符集

SQL> alter session set container=orcl;

Session altered.

SQL>  select value,parameter from NLS_DATABASE_PARAMETERS where PARAMETER like '%CHARACTERSET'
  2  ;

VALUE             PARAMETER
-------------------- ------------------------------
AL16UTF16          NLS_NCHAR_CHARACTERSET
WE8MSWIN1252       NLS_CHARACTERSET

SQL> alter session set container=PDB$SEED ;

Session altered.

SQL> select value,parameter from NLS_DATABASE_PARAMETERS where PARAMETER like '%CHARACTERSET';

VALUE             PARAMETER
-------------------- ------------------------------
AL16UTF16          NLS_NCHAR_CHARACTERSET
ZHS16GBK          NLS_CHARACTERSET

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> r
  1* select value,parameter from NLS_DATABASE_PARAMETERS where PARAMETER like '%CHARACTERSET'

VALUE             PARAMETER
-------------------- ------------------------------
AL16UTF16          NLS_NCHAR_CHARACTERSET
AL32UTF8          NLS_CHARACTERSET

事实证明只要cdb$ROOT的字符集是AL32UTF8,PDB和CDB的字符集可以不一样。

jiawang · 发表于 2022-8-24 15:53:45

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

jiawang · 发表于 2022-8-24 16:05:01

从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

帐号		自动登录	找回密码
密码			注册