When creating a database in a multitenant environment we must understand how character set works for CDB and PDBS.

In Oracle documentation we can find following lines

-When the character set of the CDB root is AL32UTF8, PDBs that are plugged into the CDB can have a different character set from the CDB root.
PDBs that you create from PDB$SEED inherit the AL32UTF8 character set from it, but you can migrate the PDB to a different character set.

-When the character set of the root is not AL32UTF8, all PDBs in the CDB use the character set of the CDB root.

Let’s see how it works. Suppose I have 2 non-CDB databases DB11G and DB12C and I want to migrate them in an Oracle 21c container.

As specified in the documentation we have to create a container with AL32UTF8 as character set. If not, we will not be able to create a PDB with AL32UTF8 as character set.

Ok so the first step is to create the CDB CDBPROD.

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> select name from v$database;

NAME
---------
CDBPROD

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

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

SQL>

And in this container CDBPROD let’s create the DB21UTF from seed

SQL> CREATE PLUGGABLE DATABASE DB21UTF ADMIN USER db21admin identified by root;

Pluggable database created.

SQL> alter pluggable database DB21UTF open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 DB21UTF                        READ WRITE NO
SQL>

And in DB21UTF the character set will be same as CDB$ROOT

SQL> alter session set container=DB21UTF;

Session altered.

SQL> show con_name;

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

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

SQL>

And then in DB21UTF, I can import data of DB11G.

But how can I create the PDB DB21ISO in CDBPROD with WE8ISO8859P15 as character set. One way is to
-create another CDB CDBISO with the character set WE8ISO8859P15
-create the PDB DBISO in this new CDB CDBISO
-Clone this DBISO in the CDBPROD

Ok let’s do that, below information of the new ISO CDB with the PDB

SQL> select name from v$database;

NAME
---------
CDBISO

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 DB21ISO                        READ WRITE NO

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

VALUE                          PARAMETER
------------------------------ ------------------------------
AL16UTF16                      NLS_NCHAR_CHARACTERSET
WE8ISO8859P15                  NLS_CHARACTERSET

SQL>

Now let’s clone DBISO in CDBPROD

On CDBISO let’s create a user we will use for a database link

SQL> show con_name

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

SQL> create user c##clone_user identified by root default tablespace users temporary tablespace temp container=ALL;

User created.

SQL> grant create session, create pluggable database, sysoper to c##clone_user container=ALL;

Grant succeeded.

SQL>

On CDBPROD let’s create a database link

Verify your TNS alias

[email protected]:/home/oracle/ [CDBPROD (CDB$ROOT)] tnsping cdbiso

TNS Ping Utility for Linux: Version 21.0.0.0.0 - Production on 10-FEB-2022 15:44:03

Copyright (c) 1997, 2021, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oraadserver)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CDBISO)))
OK (0 msec)
[email protected]:/home/oracle/ [CDBPROD (CDB$ROOT)]

Create the dblink

SQL> select name from v$database;

NAME
---------
CDBPROD

SQL> show con_name

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

SQL> create database link cloneiso connect to c##clone_user identified by root using 'CDBISO';

Database link created.

SQL> select * from [email protected];

D
-
X

SQL>

And then clone the DBISO PDB in CDBPROD

SQL> select name from v$database;

NAME
---------
CDBPROD

SQL> show con_name

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

SQL> create pluggable database DB21ISO from [email protected];

Pluggable database created.

SQL> alter pluggable database DB21ISO open;

Pluggable database altered.

Now let’s verify the state of my PDBs in CDBPROD

SQL> select name from v$database;

NAME
---------
CDBPROD

SQL>

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 DB21UTF                        READ WRITE NO
         7 DB21ISO                        READ WRITE NO
SQL>

Let’s verify the DB21ISO character set

SQL> select name from v$database;

NAME
---------
CDBPROD

SQL> alter session set container=DB21ISO;

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


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

VALUE                               PARAMETER
----------------------------------- -----------------------------------
AL16UTF16                           NLS_NCHAR_CHARACTERSET
WE8ISO8859P15                       NLS_CHARACTERSET

SQL>

And the CDBPROD as seen before has AL32UTF8 as character set

SQL> conn / as sysdba
Connected.
SQL> select value,parameter from NLS_DATABASE_PARAMETERS where PARAMETER like '%CHARACTERSET';

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

SQL>

Conclusion

Always use AL32UTF8 for your CDB if possible, in this way you will be able to have PDBs with different character set


Thumbnail [60x60]
by
Mouhamadou Diaw