Migrating CLOBs to NCLOBS on Target

Owned by Clive Otten (Unlicensed)
Jan 22, 2020
2 min read

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.

Solution

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 ); ALTER TABLE test ADD CONSTRAINT PK\_ID PRIMARY KEY(ID); commit;

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)

  3. Update the replace CLOB\_COL(type NCLOB now) with the existing CLOB\_COL\_OLD data.

  4. Drop the former CLOB\_CLOB\_OLD so columns are same now as in SOURCE except only type is different.

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

    Check data on Target - all set

Then start MINE/APPLY until your migration cutover.