重庆思庄Oracle、Redhat认证学习论坛

标题: 19Ccdb同pdb字符集不同,导致pdb无法启动 [打印本页]

作者: jiawang    时间: 2022-8-23 17:09
标题: 19Ccdb同pdb字符集不同,导致pdb无法启动
[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
如果PDB字符集想和CDB不一样,cdb$ROOT的字符集必须是AL32UTF8 字符集才行。

作者: jiawang    时间: 2022-8-24 15: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
在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
从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





欢迎光临 重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/) Powered by Discuz! X3.2