ODI 12.2.1 Exporting Master/Work Schema DMP from one database to another.

ODI 12.2.1 Exporting Master/Work Schema from one database to another.

In The below example we are cloning ODI Schema from Oracle 12c Database to Oracle 11 Database.

We Exported the STG_ODI_REPO schema from one DB environment using the export utility as dump file and imported into another DB environment.

Instead of exporting the Master and work repository, which takes a lot of time, this approach was used for better turnaround time.

Able to connect to Master repository and navigate to Topology tab and view the Work Repository but when trying to connect gets the error ODI-23018 error

I have carried out the following steps to fix this error

.      

     Connect to the STG_ODI_REPO schema in the database

2.    Run the following query

select i_txt,full_txt from snp_mtxt where i_txt
in (select I_TXT_JAVA_URL from snp_connect where con_name = 'STG_WORKREP');

My work repository name is ‘STG_WORKREP’

You will see the output like below

I_TXT	FULL_TXT
394	jdbc:oracle:thin:@(description=(address=(host=OLDIP) (protocol=tcp)(port=OLDPORT))(connect_data=(service_name=OLDSERVICE) (server=dedicated)))

Observe that the full_txt field contains the old connection details

3.   

        Update the full_txt field with the new connection details

update snp_mtxt
set full_txt = 'jdbc:oracle:thin:@(description=(address=(host=NEWIP)(protocol=tcp)(port=NEWPORT))(connect_data=(service_name=NEWSERVICE)(server=dedicated)))'
where i_txt = 394;

COMMIT;

  

Now open ODI Studio and you should be able to connect to your work repository.

Adjust the contexts and Agents before running any jobs

This approach is tested on ODI 12.2.1 with 12C database as source and ODI 12.2.1 with 11G database as target. 

Incidentally these two databases were setup with different character sets but it worked perfectly fine.

Other important note is to specify VERSION=11.2 during the expdp command