Part 3 - Oracle 12c for Pro Newbies Plug and Play

Start Here

Get in touch with a
TriCore Solutions specialist

Blog | Jul 21, 2015

Oracle 12c for Pro Newbies Plug and Play (Part 3): DMU


In the third part of the Oracle 12c for Pro Newbies Plug and Play (Part 3), we will learn about Database Migration Assistant for Unicode (DMU). So, let's get started!

 


 “The devil is in the detail”

So far Ravi and I have upgraded our test database orcl from 11.1.0.6 to 11.1.0.7 and then to 12.1.0.2 non-CDB. We enjoyed this long journey. However, Ravi still has his target pending which was to attach this orcl non-CDB to an already existing CDB (cdb12ct) as a PDB. Let me help him do it while you enjoy reading this blog in continuation.

Me: Ravi, we are supposed to obtain an XML file which contains the metadata required to create a PDB. We will check this out in detail after some time. For now, I have obtained this XML file and named it orclncdb.xml.

(To start with we open the database orcl in read-only mode & run the following in it to obtain its metadata)


SQL> BEGIN

DBMS_PDB.DESCRIBE(pdb_descr_file => '/u01/app/orclncdb.xml');

END; 

/

PL/SQL procedure successfully completed.

We should check the compatibility of this non-CDB on our CDB cdb12ct with the XML file that we have. We do this because when we plug a database into some other container, the compatibility will decide for the possibility of plugging in.

(We run the following inside cdb12ct)


SET SERVEROUTPUT ON

DECLARE

compatible CONSTANT VARCHAR2(3) :=

CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(

pdb_descr_file => '/u01/app/orclncdb.xml',

pdb_name => 'orclpdb')

WHEN TRUE THEN 'YES'

ELSE 'NO'

END;

BEGIN

DBMS_OUTPUT.PUT_LINE(compatible);

END;

/

PL/SQL procedure successfully completed. 

Output: NO

Let’s check for the non-conformities by querying the PDB_PLUG_IN_VIOLATIONS view. We faced many error messages here one of those was the character set incompatibility.

Message:

Character set mismatch: PDB character set WE8MSWIN1252. CDB character set AL32UTF8.

Action:

Convert the character set of the PDB to match the CDB or plug the PDB in a CDB with a compatible character set.

The message makes it all clear. We have a CDB with a character set AL32UTF8 which is the recommended character set for any new CDB creation and we are trying to plug in a PDB with an incompatible character set. The action suggested asks us to either create a new CDB in the same character set (which may be the case in several business needs where we are not supposed to change the character set of our database) or change the character set of our database to make it compatible to the CDB character set.

We chose the latter one here. We decided to convert the orcl non-CDB database to the CDB character set. So we checked the possibility on MOS and found Doc ID 1968706.1. ORA-65116/65119: incompatible database/national character set (Character set mismatch: PDB character set CDB character set).

We decided to use DMU Tool for the character set migration. DMU is a GUI based tool which helps in automating the process of converting NLS_CHARACTERSET of an Oracle Database to UTF8 or AL32UTF8.  The DMU 2.0 client (User Interface) is included in Oracle database 12.1.0.2. The Database Migration Assistant for Unicode (DMU) Tool (Doc ID 1272374.1) Readers should also consider reviewing [Changing or Choosing the Database Character Set (NLS_CHARACTERSET) (Doc ID 225912.1)]

DMU utilty can be found under 12c ORACLE_HOME.

cd $ORACLE_HOME/dmu

chmod u+x dmu.sh

 

To run the DMU, SYS.DBMS_DUMA_INTERNAL package needs to be created by running the script prvtdumi.plb


SQL> @/u01/app/oracle/product/12.1.0.1/db_1/rdbms/admin/prvtdumi.plb


 If you do not create this package, it will flash the following error. 


DMU-00004

Pro_3_

 

Run the dmu.sh to start the tool. Connect to the database which you want to convert.

pro_3_1


Install the DMU repository.

pro_3_2


Select the target character set for migration (either AL32UTF8 or UTF8).

pro_3.3

Once the repository is installed, scan the database for conversion issues. Resolve the migration issues after the scanning is complete.

pro_3_4

pro_3_5

pro_3_miss

pro_3_7

 

pro_3_8

pro_3_9

pro_3_10

pro_3_11

pro_3_12

 


DMU-00115, DMU-00119


pro_3_14

 

pro_3_15

pro_3_16

pro_3_17

pro_3_18-1

 

Check the character set of the database using the query below:

SQL> select value, parameter from NLS_DATABASE_PARAMETERS where PARAMETER like '%CHARACTERSET';

VALUE                                    PARAMETER

---------------------------------------- --------------------------------------------------------------------------------

AL16UTF16                                NLS_NCHAR_CHARACTERSET

AL32UTF8                                 NLS_CHARACTERSET

So we have successfully converted the character set of our pluggable database to fit with the container. Next step is to check for other compatibility issues and plug in the database. Stay tuned till then!

For any comments and queries please click below.

Feedback