Thursday, 1 June 2017

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

In the previous release the character set for the root container and all pluggable databases associated with it had to be the same. This could limit the movement of PDBs and make consolidation difficult where a non-standard character set was required.

In Oracle Database 12c Release 2 (12.2) a PDB can use a different character set to the CDB, provided the character set of the CDB is AL32UTF8, which is now the default character set when using the Database Configuration Assistant (DBCA).

  • 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.

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>
We can see the default character set of the root container is AL32UTF8, which means it can hold PDBs with different character sets.

  • 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

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 \
 -automaticMemoryManagement false \
 -totalMemory 2048 \
 -storageType FS \
 -datafileDestination "/u02/app/oracle/oradata/" \
 -redoLogFileSize 50 \
 -emConfiguration NONE \
 -ignorePreReqs

We make the source CDB use Oracle Managed Files (OMF) and switch it to archivelog mode.

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

  • 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.

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

sqlplus / as sysdba

Clone the source PDB (pdb5) to create the destination PDB (pdb5new).

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>

Open the PDB for the first time.

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>

If you have any problems, check the PDB_PLUG_IN_VIOLATIONS view.

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;

  • Check the Destination PDB


Compare the character set of the CDB and the new pluggable database.

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>

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