How to create PDBs with different Character Sets in the same CDB

Introduction

Oracle Multitenant enables you to migrate and consolidate multiple databases as PDBs into one CDB. Starting with Oracle Database release 12.2.0.1, PDBs within the same CDB can be of different character sets, provided the CDB is created with the AL32UTF8 character set. If you are using Application Containers, then all application PDBs under the same Application Root must have the same character set.

The main reason for providing this capability is to facilitate the consolidation of existing databases. Newly created applications and PDBs should rather use the recommended AL32UTF8 character set. Workarounds described below for creating new PDBs with different character sets should only be used when absolutely necessary.

By the way, starting with 19c, you can create 3 PDBs per CDB even if you are not licensed for Oracle Multitenant.

The Environment

I’m using an EE database release 19.9 with AL32UTF8 character set.

SQL> SELECT value FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';

VALUE
----------
AL32UTF8

Creating New PDBs

Let’s create a new PDB with the WE8ISO8859P1 character set:

SQL> create pluggable database ...

Ups! But how to indicate the desired character set??? There is no clause in the SQL syntax for that!

Well, if we think about it, creating a new PDB is actually just cloning the PDB$SEED, which has the same character set as CDB$ROOT. Hence, there is no way to create a new PDB from PDB$SEED with a different character set. This is neither a bug nor missing functionality. This is how it’s designed and it makes perfect sense.

Usually, you won’t need that, as in most cases you’ll consolidate existing PDBs and 12c non-CDB databases by cloning (or unplug&plug) them into the target CDB.

However, there might be few use cases where you will need to create a new “empty” PDB with a different character set, e.g. migrating an 11g (or lower – I hope you don’t still have one of those) database directly into a PDB using Data Pump export and import while keeping your source character set.

So, how to do it? I think this is really easy to guess.

Remote Cloning

Create a new CDB and PDB with your desired character set, in this example WE8ISO8859P1, and then create a remote clone from that PDB into your target (production) CDB:

SQL> create database link DBLINK_TO_ISO connect to C##SYSOPER identified by <sysoper_password> using '<connection_string_to_ISOCDB>';

Database link created.

SQL> create pluggable database PDB002 from PDBISO@DBLINK_TO_ISO keystore identified by "<TDE_Wallet_Password>";

Pluggable database created.

Open the new PDB and check the character set:

SQL> alter pluggable database PDB002 open;

Pluggable database altered.

SQL> alter session set container=PDB002;

Session altered.

SQL> SELECT value FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';

VALUE
------------
WE8ISO8859P1

To be sure that everything is fine, check the PDB_PLUG_IN_VIOLATIONS view:

set lines 300
col status for a10
col type for a10
col cause for a20
col message for a90
SQL> SELECT status, type, cause, message FROM pdb_plug_in_violations;

STATUS     TYPE       CAUSE                MESSAGE
---------- ---------- -------------------- ------------------------------------------------------------------------------------------
PENDING    WARNING    PDB not Unicode      Character set mismatch: PDB character set WE8ISO8859P1. CDB character set AL32UTF8.

Close and re-open the PDB again to get STATUS=RESOLVED.

Now we can drop the source CDB created just for the cloning purpose.

Long Term Solution

If you intend to create new PDBs with a different character set more frequently, then you’ll probably look for a simpler and faster approach.

Option 1

Keep the cloned PDB with the ISO character set as “template” in your target CDB and create further PDBs from that PDB as local clones.

Pro: the template PDB is already there and you just create local clones immediately.

Con: you manage an additional PDB you actually don’t really need. Even worse if you need many of them for further different character sets.

Option 2

If you want to create multiple PDBs of different character sets on multiple CDBs, then the solution described in “option 1” would become inappropriate. In this case, create one CDB to hold all your templates, and then remote clone your PDBs from there.

Pro: the template PDBs are already there and your just create remote clones immediately.

Con: you have to manage an additional CDB.

Option 3

Unplug the template PDBs with the different character sets and keep them for future use on your file system. Unplug into PDB archive (.pdb instead of .xml ending) is available since 12.2.0.1

-- create a CDB with ISO character set
SQL> alter session set container=PDBISO;
SQL> administer key management export keys with secret "<PW_to_protect_this_file>" to '/home/oracle/pdbiso.p12' force keystore identified by "<TDE_wallet_PW>";
SQL> alter session set container=CDB$ROOT;
SQL> alter pluggable database PDBISO close immediate instances=all;
SQL> alter pluggable database PDBISO unplug into '/home/oracle/PDBISO.pdb';
SQL> drop pluggable database PDBISO including datafiles;
-- terminate the entire CDB if not needed

-- copy the files to another server if needed

SQL> create pluggable database PDB003 as clone using '/home/oracle/PDBISO.pdb';
SQL> alter pluggable database PDB003 open instances=all;
SQL> alter session set container=PDB003;
SQL> SELECT status, type, cause, message FROM pdb_plug_in_violations;
SQL> administer key management import keys with secret "<PW_protecting_this_file>" from '/home/oracle/pdbiso.p12' force keystore identified by "<TDE_wallet_PW>" with backup;
SQL> alter pluggable database PDB003 close immediate instances=all;
SQL> alter pluggable database PDB003 open instances=all;
SQL> SELECT status, type, cause, message FROM pdb_plug_in_violations;

Pro: you are able to create your PDBs immediately via a single SQL command. You don’t need to manage any additional CDBs or PDBs in your environment.

Con: not really, it’s just the space needed for the unplugged PDB files, which should be quite small as the PDBs do not have any user data. In my case, it’s less than 200MB per PDB.

If you are using ASM in 12.2.0.1, then be aware of Bug 25715167 (pdb archive file does not include datafile on ASM diskgroup). This is fixed in July 2017 RU and in 18c (12.2.0.2).

Conclusion

Starting with database release version 12.2.0.1 you are able to consolidate PDBs of different character sets into one target CDB using PDB unplug/plug or PDB cloning.

There is no option to determine a character set while creating a new PDB. The new PDB is a clone of the PDB$SEED, and hence has the same character set as PDB$SEED and CDB$ROOT. This works as designed. But there are different easy options to come around that if you need a new “empty” PDB of a different character set like remote cloning or keeping an unplugged PDB as a template on the file system.

Having a look at these options shows again how powerful, flexible, and easy Oracle Multitenant is.

Finally, for new applications and PDBs better follow the recommendations and use AL32UTF8 character set instead of using workarounds.

How to create PDBs with different Character Sets in the same CDB

Introduction

The Environment

Creating New PDBs

Remote Cloning

Long Term Solution

Option 1

Option 2

Option 3

Conclusion

Further Reading

Subscribe

1 comment for “How to create PDBs with different Character Sets in the same CDB”

Introduction

The Environment

Creating New PDBs

Remote Cloning

Long Term Solution

Option 1

Option 2

Option 3

Conclusion

Further Reading

Subscribe

Share this:

Related

Post navigation

1 comment for “How to create PDBs with different Character Sets in the same CDB”