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

Post Views: 5,214
Thumbnail [60x60]
by
Oracle Team