We're here to help!

Migrating CLOBs to NCLOBS on Target

Problem Description

I just completed a series of tests using Replicate 2.7.14 with  a source db with characterset WE8MSWIN1252 and target database using UTF8 and determined a recommendation for your CLOB to NCLOB column conversion for a migration use case.

If you configure our Replicate product for the SCHEMA replication and instantiate the TARGET database as you would normally you can then do the conversion for the CLOB column to NCLOB on the TARGET either BEFORE you start the replication process(MINE/APPLY) or after you have ceased transactions on your SOURCE to complete your migration on to the TARGET database just before your enable user access.


The steps would be the same just the timing is which ever you prefer. For my example I have a SOURCE/TARGET table named TEST with this definition and performed the below on the TARGET.

Test Table definition at SOURCE database:

create table TEST (
  id number(4),
  clob\_col CLOB,
  nclob\_col NCLOB

Then put some data in the above table on SOURCE database & defined REPLICATE configuration via the Setup Wizard & instantiated TARGET database using single\_scn, expdp for example.

After running all.sh (-all.bat on Windows) first to configure the metadata so replication is aware of where you are starting from.

Followed this by running the APPLY.sh (APPLY.bat on Windows) script to perform the export from SOURCE database & import/populate the TARGET database with data to start replication.

Once the import was complete I performed the below to alter the TEST table CLOB\_COL from type CLOB to type NCLOB.

So, on TARGET database:

Connect to SQL\*Plus as schema owner:

$ sqlplus <schema\_user>/<schema\_password) 
  1. Make column modification on TARGET SERVER ONLY by first renaming current CLOB\_COL to CLOB\_COL\_OLD in the table TEST.
    SQL> ALTER TABLE test RENAME COLUMN clob\_col TO clob\_col\_old;
    Table altered.
  2. Add CLOB\_COL back to table with different definition (NCLOB vs CLOB)
    SQL> ALTER TABLE test ADD (clob\_col NCLOB);
    Table altered.
  3. Update the replace CLOB\_COL(type NCLOB now) with the existing CLOB\_COL\_OLD data.
    SQL> UPDATE test SET clob\_col = clob\_col\_old;
    4 rows updated.
  4. Drop the former CLOB\_CLOB\_OLD so columns are same now as in SOURCE except only type is different.
    SOURCE: clob\_col is type CLOB
    TARGET: clob\_col is type NCLOB
    SQL> ALTER TABLE test DROP COLUMN clob\_col\_old;
    Table altered.

    On TARGET database, show table description & data (not SOURCE table remains unchanged):

    SQL> desc test
    Name        Null? Type
    ----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
    ID        NOT NULL NUMBER(4)
    Check data on Target - all set
    SQL> select \* from test3 order by id; 
    ID         CLOB\_COL             NCLOB\_COL
    ---------- -------------------- --------------------
     1 Initial value
     2 Initial value
     3 Initial value
    11 nclob conversion XYZ nclob conversion XYZ

Then start MINE/APPLY until your migration cutover.

Have more questions? Submit a request