Skip to content

Character Sets and Oracle 21c PDBs : How It Works

By Mouhamadou Diaw

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
oracle@oraadserver:/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)
oracle@oraadserver:/home/oracle/ [CDBPROD (CDB$ROOT)]

Create the dblink

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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 dual@cloneiso;
D
-
X
SQL>

And then clone the DBISO PDB in CDBPROD

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SQL> select name from v$database;
NAME
---------
CDBPROD
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> create pluggable database DB21ISO from DB21ISO@cloneiso;
Pluggable database created.
SQL> alter pluggable database DB21ISO open;
Pluggable database altered.

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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

1
2
3
4
5
6
7
8
9
10
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

Let’s work together

Ready to operate at
the next level?

Tell us about your challenge. We’ll bring the depth, the expertise, and the Swiss precision.

Oracle Team

Oracle Team

Posts published under the Oracle Team account are collective contributions by the dbi services Oracle consultants. The practice covers the full Oracle Database lifecycle: architecture, migration and upgrade, high availability with RAC and Data Guard, backup and recovery, performance tuning, and engineered systems such as Oracle Database Appliance and Exadata. The team holds Oracle certifications up to Certified Master level and operates customer platforms under service level agreements.View Author posts

1,009 posts since December 2010

Most read in Oracle

The Oracle posts our readers open most: the all-time favourites, and what is trending right now.

Leave a Comment

Your email address will not be published. Required fields are marked *