DBA Hub

📋Steps in this guide1/5

Multitenant : PDBs With Different Character Sets to the CDB in Oracle Database 12c Release 2 (12.2)

A PDB can use a different character set to the CDB in Oracle Database 12c Release 2 (12.2).

oracle 12cconfigurationintermediate
by OracleDba
22 views
1

Check the Destination CDB Character Set

Connect to the destination root container and run the following query to display the default character set of database. We can see the default character set of the root container is AL32UTF8, which means it can hold PDBs with different character sets.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CONN / AS SYSDBA

COLUMN parameter FORMAT A30
COLUMN value FORMAT A30

SELECT *
FROM   nls_database_parameters
WHERE  parameter = 'NLS_CHARACTERSET';

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET               AL32UTF8

SQL>
2

Create a Source CDB and PDB

First we must create a CDB with the WE8ISO8859P1 character set so we have a suitable source CDB and PDB. The following command creates a CDB called cdb3 with a PDB called pdb5 We make the source CDB use Oracle Managed Files (OMF) and switch it to archivelog mode.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
dbca -silent -createDatabase \
 -templateName General_Purpose.dbc \
 -gdbname cdb3 -sid cdb3 -responseFile NO_VALUE \
 -characterSet WE8ISO8859P1 \
 -sysPassword OraPasswd1 \
 -systemPassword OraPasswd1 \
 -createAsContainerDatabase true \
 -numberOfPDBs 1 \
 -pdbName pdb5 \
 -pdbAdminPassword OraPasswd1 \
 -databaseType MULTIPURPOSE \
 -memoryMgmtType auto_sga \
 -totalMemory 2048 \
 -storageType FS \
 -datafileDestination "/u02/app/oracle/oradata/" \
 -redoLogFileSize 50 \
 -emConfiguration NONE \
 -ignorePreReqs

export ORAENV_ASK=NO
export ORACLE_SID=cdb3
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba <<EOF

ALTER SYSTEM SET db_create_file_dest = '/u02/app/oracle/oradata';

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

ALTER PLUGGABLE DATABASE pdb5 OPEN;
ALTER PLUGGABLE DATABASE pdb5 SAVE STATE;

EXIT;
EOF
3

Hot Clone the Source PDB

To prove we can house a database of a different character set in our destination CDB, we will be doing a hot clone. The setup required for this is described in the following article. Once you've completed the setup, you can perform a regular hot clone. Connect to the destination CDB. Clone the source PDB (pdb5) to create the destination PDB (pdb5new). Open the PDB for the first time. If you have any problems, check the view. When I first wrote this article against an instance on Oracle Cloud I did not see any violations. On the on-prem 12.2.0.1 I see the following Unicode violation, but this doesn't stop the new PDB from working.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
export ORAENV_ASK=NO
export ORACLE_SID=cdb1
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba

CREATE PLUGGABLE DATABASE pdb5new FROM pdb5@clone_link;

SHOW PDBS

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB5NEW                        MOUNTED
SQL>

ALTER PLUGGABLE DATABASE pdb5new OPEN;

SHOW PDBS

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

SET LINESIZE 200

COLUMN time FORMAT A30
COLUMN name FORMAT A30
COLUMN cause FORMAT A30
COLUMN message FORMAT A30

SELECT time, name, cause, message
FROM   pdb_plug_in_violations
WHERE  time > TRUNC(SYSTIMESTAMP)
ORDER BY time;

TIME                           NAME                           CAUSE                          MESSAGE
------------------------------ ------------------------------ ------------------------------ ------------------------------
12-SEP-17 15.55.16.636705      PDB5NEW                        Parameter                      CDB parameter pga_aggregate_ta
                                                                                             rget mismatch: Previous 512M C
                                                                                             urrent 384M

12-SEP-17 15.55.16.637023      PDB5NEW                        PDB not Unicode                Character set mismatch: PDB ch
                                                                                             aracter set WE8ISO8859P1. CDB
                                                                                             character set AL32UTF8.


SQL>
4

Check the Destination PDB

Compare the character set of the CDB and the new pluggable database. We can see we have a pluggable database with a different character set to that of the root container.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
CONN / AS SYSDBA

COLUMN parameter FORMAT A30
COLUMN value FORMAT A30

SELECT *
FROM   nls_database_parameters
WHERE  parameter = 'NLS_CHARACTERSET';

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET               AL32UTF8

SQL>


ALTER SESSION SET CONTAINER=pdb5new;

COLUMN parameter FORMAT A30
COLUMN value FORMAT A30

SELECT *
FROM   nls_database_parameters
WHERE  parameter = 'NLS_CHARACTERSET';

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET               WE8ISO8859P1

SQL>
5

Miscellaneous

- The root container must use to the AL32UTF8 character set if you need it to hold PDBs with differing character sets. - The character set and national character set of an application container and all its application PDBs must match. - New PDBs, cloned from the seed database, always match the CDB character set. There is no way to create a new PDB with a different character set directly. You can use Database Migration Assistant for Unicode (DMU) to convert the character set of a PDB. - As seen in this article, cloning can be used to create a PDB with a different character set, as can unplug/plugin. - LogMiner supports PDBs with different character sets compared to their CDB. - Data Guard support PDBs with different character sets compared to their CDB for rolling upgrades. For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!