Database 12c: Convert Non-CDB with different character set to PDB


Today I want to discuss converting of a Non-CDB with different character set to a PDB. I think this a very interesting part, because it is possible. So you are able to provide multiple Character Sets in one database (maybe). Like in every characterset migration it depends on the character set you want to migrate to.

Here the limits for PDB migration:

  • The character set is the same as the national character set of the CDB. In this case, the plugging operation succeeds (as far as the national character set is concerned).
  • The character set is not the same as the national character set of the CDB. In this case, the new PDB can only be opened in restricted mode for administrative tasks and cannot be used for production. Unless you have a tool that can migrate the national character set of the new PDB to the character set of the CDB, the new PDB is unusable.

“If you cannot migrate your existing databases prior to consolidation, then you have to partition them into sets with plug-in compatible database character sets and plug each set into a separate CDB with the appropriate superset character set”:

  • US7ASCII, WE8ISO8859P1, and WE8MSWIN1252 into a WE8MSWIN1252 CDB
  • WE8ISO8859P15 into a WE8ISO8859P15 CDB
  • JA16SJISTILDE into a JA16SJISTILDE CDB
  • JA16EUC into a JA16EUC CDB
  • KO16KSC5601, KO16MSWIN949 into a KO16MSWIN949 CDB
  • UTF8 and AL32UTF8 into an AL32UTF8 CDB

Here an example of PDB_PLUG_IN_VIOLATIONS for a compatible PDB:

NAME                           CAUSE                  TYPE      MESSAGE                                                                                              STATUS
------------------------------ ---------------------- --------- ---------------------------------------------------------------------------------------------------- ---------
DB12CEE4                       Database CHARACTER SET WARNING   Character set mismatch: PDB character set UTF8 CDB character set AL32UTF8.                           PENDING
DB12CEE4                       Non-CDB to PDB         WARNING   PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run.                                      PENDING
DB12CEE4                       Parameter              WARNING   CDB parameter sga_target mismatch: Previous 788529152 Current 2768240640                             PENDING
DB12CEE4                       Parameter              WARNING   CDB parameter pga_aggregate_target mismatch: Previous 262144000 Current 917504000                    PENDING

Migrating from UTF8 to AL32UTF8, after plug-in the database characterset has been changed automatical (it seems to be -> maybe during dictionary check):

SQL> select * from nls_database_parameters where parameter like '%CHARACTERSET';

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

Here an example of PDB_PLUG_IN_VIOLATIONS for a non compatible PDB:

NAME                           CAUSE                  TYPE      MESSAGE                                                                                              STATUS
------------------------------ ---------------------- --------- ---------------------------------------------------------------------------------------------------- ---------
DB12CEE1                       Database CHARACTER SET ERROR     Character set mismatch: PDB character set WE8MSWIN1252 CDB character set AL32UTF8.                   PENDING
DB12CEE1                       Non-CDB to PDB         WARNING   PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run.                                      PENDING
DB12CEE1                       Parameter              WARNING   CDB parameter sga_target mismatch: Previous 788529152 Current 2768240640                             PENDING
DB12CEE1                       Parameter              WARNING   CDB parameter pga_aggregate_target mismatch: Previous 262144000 Current 917504000                    PENDING

Migrating from WE8MSWIN1252 to AL32UTF8, after plug-in the database characterset nothing changed:

SQL> select * from nls_database_parameters where parameter like '%CHARACTERSET';

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

As documentated plug-in and convert of both were possible. Example 1 successfully and example 2 with errors in cause of the characterset. Notice: Productive use of the second example is not supported, but possible. Why?

  • R/W open only in restricted session mode –> Maybe user connect as SYSDBA?
  • R/O open possible –> Maybe you can use it for Reporting, without changing characterset?
  • RMAN Backup is possible

Why do I mention this? If you doesn’t take care on the CS during migration to PDB the way back is not as easy as it should be. Of course in this case I have to export and import in another PDB, but I can’t physically migrate back. I have to two options to get to an usable result (WE8MSWIN1252 –>AL32UTF8)

  1. AL32UTF8 is a logical superset and migrationable with export/import procedure
  2. Migration with replication

Directly convert with plug-in is possible but only a usable step for this case, because I’vh to do the migration again in a logical form.

Conslusion: Take care on the characterset before migration to PDB. Not convertable charactersets need an convert be for an logical and no physical or partly physical migration way.

3 thoughts on “Database 12c: Convert Non-CDB with different character set to PDB

  1. Hello,
    I’m trying to PLUG a non-cdb to a cdb. The non CDB use UTF8 encoding and the CDB use AL32UTF8.
    Executing the script : noncdb_to_pdb.sql
    Give this error:
    DECLARE
    *
    ERROR at line 1:
    ORA-06552: PL/SQL: Compilation unit analysis terminated
    ORA-06553: PLS-553: character set name is not recognized
    ORA-06512: at line 27
    And if I try to open the PDB:
    SQL> alter pluggable database PDV_ORCL2 open;
    alter pluggable database PDV_ORCL2 open
    *
    ERROR at line 1:
    ORA-00603: ORACLE server session terminated by fatal error
    ORA-00600: internal error code, arguments: [ktcpoptx:!cmt top lvl], [], [], [], [], [], [], [], [], [], [], []
    ORA-00600: internal error code, arguments: [kqlobjlod-no-result-from-proc$], [1403], [1506], [], [], [], [], [], [], [], [], []
    Process ID: 30326
    Session ID: 28 Serial number: 63856
    Is posible to convert the carácter set of the PDB to AL32UTF8?
    Thanks
    Arturo

    • Hi Arturo,
      normally not. You should not plugin the database as long as the cs is not matching the CDB once.

      Interessting is:
      PLS-553 –> Normally advises that the CS is your DB is not consistent and you may used different ones?

      ORA-600 [ktcpoptx:!cmt top lvl]: Is there maybe a problem with the global UNDO?

      ORA-600 [kqlobjlod-no-result-from-proc$]: There are two BUG in 12.1.0.1.0 Release

      Bug 17817656 – ORA-600 [kqlobjlod-no-result-from-proc$] executing noncdb_to_pdb.sql in a PDB that does not need it (Doc ID 17817656.8)
      Bug 17439871 – ORA-600 [kqlobjlod-no-result-from-proc$] from DROP of a common USER in PDB (Doc ID 17439871.8)

      >> I don’t know your database version but if it is 12.1.0.1 than please upgrade to 12.1.0.2

      Kind Rgds,
      Daniel

  2. Thanks Daniel for the help. Yes I ‘ve 12.1.0.2 installed.
    I’m trying to reproduce a may be real world scenario. Where te databases use differents CS. In this case from UTF8 to AL32UTF8 might be reasonable to work. Any way, I’ll continue testing.

    Thanks
    Arturo

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s