How to repoint the Data Workspace post clone?

2833
3
Jump to solution
10-13-2015 12:33 PM
AlisonGaiser1
Occasional Contributor

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

0 Kudos
1 Solution

Accepted Solutions
AlisonGaiser1
Occasional Contributor

So my suspicions from above were correct. I do need to update that value. It was much easier than I suspected. See below.

UPDATE "WMX"."JTX_DATABASES"

SET CONNECTION_PROPERTIES = '<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.3''><PropertyArray xsi:type=''typens:ArrayOfPropertySetProperty''><PropertySetProperty xsi:type=''typens:PropertySetProperty''><Key>DBCLIENT</Key><Value xsi:type=''xs:string''>oracle</Value></PropertySetProperty><PropertySetProperty xsi:type=''typens:PropertySetProperty''><Key>DB_CONNECTION_PROPERTIES</Key><Value xsi:type=''xs:string''>sde:oracle11g:egised4</Value></PropertySetProperty><PropertySetProperty xsi:type=''typens:PropertySetProperty''><Key>PROJECT_INSTANCE</Key><Value xsi:type=''xs:string''>SDE</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>USER</Key><Value xsi:type=''xs:string''>wmx</Value></PropertySetProperty><PropertySetProperty xsi:type=''typens:PropertySetProperty''><Key>PASSWORD</Key><Value xsi:type=''xs:string''>biglongstringwhichmeansnothingtoanyone</Value></PropertySetProperty><PropertySetProperty xsi:type=''typens:PropertySetProperty''><Key>AUTHENTICATION_MODE</Key><Value xsi:type=''xs:string''>DBMS</Value></PropertySetProperty></PropertyArray></PropertySet>'

WHERE DB_NAME = 'ED4_ind_ora11';

View solution in original post

0 Kudos
3 Replies
AlisonGaiser1
Occasional Contributor

We have finally upgraded our development and production environments to 10.3.1 and this problem has become more urgent. Just hoping someone out there might have some suggestions.

At the moment the only workaround is to manually update our development environment workspace properties using WMX Administrator every Monday morning.

Any ideas out there?

Alison

0 Kudos
DanPatterson_Retired
MVP Emeritus

Hi Alison... knowing I prefer to work local, I was curious as to what workflow manager was about... I presume you have gone through the help links What's new in ArcGIS Workflow Manager—Help | ArcGIS for Desktop   although for 10.4, you can check 10.3 there as well.. such things as Workflow Manager Advanced Settings—Help | ArcGIS for Desktop are buried within.  Maybe you will have to call in some of the locals

0 Kudos
AlisonGaiser1
Occasional Contributor

So my suspicions from above were correct. I do need to update that value. It was much easier than I suspected. See below.

UPDATE "WMX"."JTX_DATABASES"

SET CONNECTION_PROPERTIES = '<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.3''><PropertyArray xsi:type=''typens:ArrayOfPropertySetProperty''><PropertySetProperty xsi:type=''typens:PropertySetProperty''><Key>DBCLIENT</Key><Value xsi:type=''xs:string''>oracle</Value></PropertySetProperty><PropertySetProperty xsi:type=''typens:PropertySetProperty''><Key>DB_CONNECTION_PROPERTIES</Key><Value xsi:type=''xs:string''>sde:oracle11g:egised4</Value></PropertySetProperty><PropertySetProperty xsi:type=''typens:PropertySetProperty''><Key>PROJECT_INSTANCE</Key><Value xsi:type=''xs:string''>SDE</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>USER</Key><Value xsi:type=''xs:string''>wmx</Value></PropertySetProperty><PropertySetProperty xsi:type=''typens:PropertySetProperty''><Key>PASSWORD</Key><Value xsi:type=''xs:string''>biglongstringwhichmeansnothingtoanyone</Value></PropertySetProperty><PropertySetProperty xsi:type=''typens:PropertySetProperty''><Key>AUTHENTICATION_MODE</Key><Value xsi:type=''xs:string''>DBMS</Value></PropertySetProperty></PropertyArray></PropertySet>'

WHERE DB_NAME = 'ED4_ind_ora11';

0 Kudos