Every Sunday night we clone our production workflow manager database (EP4) back to our development WMX database (ED4).
Before our upgrade from 10.0 to 10.2.2 we used to run the following SQL post database clone (this information was supplied to us from Esri Technical support):
update wmx.JTX_DATABASES set INSTANCE='sde:oracle10g:ED4' where OBJECTID=65;
update wmx.JTX_DATABASES set db_name='ED4_ind_ora10' where OBJECTID=65;
This used to work beautifully. Unfortunately, post upgrade, it only partially works. At first I thought the problem was the fact that we are no longer using 10g but instead 11g, so I changed the SQL to the following:
update wmx.JTX_DATABASES set INSTANCE='sde:oracle11g:ED4' where OBJECTID=65;
update wmx.JTX_DATABASES set db_name='ED4_ind_ora11' where OBJECTID=65;
This change worked fine on the Workflow Manager side. I can create new jobs and they successfully go into the dev database. The problem shows itself when I run a step like launch ArcMap. Then my map document is not switched properly. The system does not recognize that there is a database change required (it sees the username and version - if required).
When I look at the value of the connection_properties field in the JTX_Databases table, I see that the INSTANCE value is not switched.
<PropertySet xsi:type='typens:PropertySet' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:xs='http://www.w3.org/2001/XMLSchema' xmlns:typens='http://www.esri.com/schemas/ArcGIS/10.1'><PropertyArray xsi:type='typens:ArrayOfPropertySetProperty'><PropertySetProperty xsi:type='typens:PropertySetProperty'><Key>SERVER</Key><Value xsi:type='xs:string'></Value></PropertySetProperty><PropertySetProperty xsi:type='typens:PropertySetProperty'><Key>INSTANCE</Key><Value xsi:type='xs:string'>sde:oracle10g:ep4</Value></PropertySetProperty><PropertySetProperty xsi:type='typens:PropertySetProperty'><Key>DATABASE</Key><Value xsi:type='xs:string'></Value></PropertySetProperty><PropertySetProperty xsi:type='typens:PropertySetProperty'><Key>VERSION</Key><Value xsi:type='xs:string'>SDE.DEFAULT</Value></PropertySetProperty><PropertySetProperty xsi:type='typens:PropertySetProperty'><Key>AUTHENTICATION_MODE</Key><Value xsi:type='xs:string'>DBMS</Value></PropertySetProperty></PropertyArray></PropertySet>
If I go into the application and manually reconnect to our dev database it appears to update the connection_properties to point to the dev database.
<Key>INSTANCE</Key><Value xsi:type='xs:string'>sde:oracle11g:ed4</Value>
Once this change has been made we have no problems connecting to the correct database.
Can I do a search and replace in the CLOB field? Or am I playing with fire? What do others do with their cloned databases?
Thanks,
Alison