8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Multitenant : PDBs With Different Character Sets to the CDB in Oracle Database 12c Release 2 (12.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).
If you are using 12.2.0.1, make sure you've applied a patch for bug 25054064 (marked as fixed in 18.1) before doing anything serious with this feature. Thanks to Patrick Jolliffe for pointing this out.
- Check the Destination CDB Character Set
- Create a Source CDB and PDB
- Hot Clone the Source PDB
- Check the Destination PDB
- Miscellaneous
Related articles.
- Multitenant : All Articles
- Multitenant : Hot Clone a Remote PDB or Non-CDB in Oracle Database 12c Release 2 (12.2)
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 \ -memoryMgmtType auto_sga \ -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. 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.
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>
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.
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:
- Choosing a Database Character Set for a Multitenant Container Database
- Multitenant : All Articles
- Multitenant : Hot Clone a Remote PDB or Non-CDB in Oracle Database 12c Release 2 (12.2)
Hope this helps. Regards Tim...