Configuration "diff" across Oracle WebCenter Sites instances

Problem Statement

With many Oracle WebCenter Sites environments - how do you know if the various configuration assets and settings are in sync across all of those environments?

Background

At Oracle we typically have a "W" shaped set of environments. 

W shapped environment architecture

For the "Production" environments we typically have a disaster recovery clone as well and sometimes additional QA environments alongside the production management environment. In the case of www.java.com we have 10 different environments.

All configuration assets/settings (CSElements, Templates, Start Menus etc..) start life on the Development Management environment and are then published downstream to other environments as part of the software development lifecycle.

Ensuring that each of these 10 environments has the same set of Templates, CSElements, StartMenus, TreeTabs etc.. is impossible to do efficiently without automation.


Solution Summary 

The solution comprises of two components.

  1. A JSON data feed from each environment.
  2. A simple HTML page that consumes these JSON data feeds. 

Data Feed:

  • Create a JSON WebService on each environment.
  • The WebService is no more than a SiteEntry + CSElement.
  • The CSElement queries various DB tables to obtain details of the assets/settings returning this data in a JSON feed.

Report:

  • Create a simple HTML page that uses JQuery to fetch the JSON feed from each environment and display the results in a table.
  • Since all assets (CSElements, Templates etc..) are published between environments they will have the same last modified date. If the last modified date of an asset is different in the JSON feed or is mising from an environment entirely then highlight that in the report table.

Example


Solution Details

Step 1: Create a Site Entry + CSElement that outputs JSON

Site Entry & CSElement Setup 

The SiteEntry should be uncached so that the most recent configuration information is returned at all times.

In the CSElement set the contenttype accordingly:

Step 2: Write the CSElement Logic

The basic logic, that we repeat for each asset or setting that we are interested in, is to query the DB using <ics:sql> and then loop over the resultset with <ics:listloop>. For example:

<ics:sql sql="SELECT name,updateddate FROM Template WHERE status != 'VO'"
    listname="TemplateList" table="Template" />

"templates": [
    <ics:listloop listname="TemplateList">
	{"name":"<ics:listget listname="TemplateList"  fieldname="name"/>",
	"modified":"<ics:listget listname="TemplateList"  fieldname="updateddate"/>"},
    </ics:listloop>
],

A comprehensive list of SQL queries to fetch each configuration asset/settings can be seen in the appendix at the end of this article.

For the generation of the JSON data structure you could use Jettison (the library ships with the 11.1.1.8 version of the product), native Java 7 capabilities or (as the above example demonstrates) you could roll-your-own JSON output but that is not advised.

Step 3: Create an HTML Report

The JavaScript logic looks something like this..

1) Create a list of JSON feeds to fetch:

ENVS['dev-mgmngt'] = 'http://dev-mngmnt.example.com/sites/ContentServer?d=&pagename=settings.json';
ENVS['dev-dlvry'] = 'http://dev-dlvry.example.com/sites/ContentServer?d=&pagename=settings.json'; 
ENVS['test-mngmnt'] = 'http://test-mngmnt.example.com/sites/ContentServer?d=&pagename=settings.json'; 
ENVS['test-dlvry'] = 'http://test-dlvry.example.com/sites/ContentServer?d=&pagename=settings.json';  

2) Create a function to get the JSON feeds:

function getDataForEnvironment(url){
  return $.ajax({
    type: 'GET',
    url: url,
    dataType: 'jsonp',
    beforeSend: function (jqXHR, settings){
      jqXHR.originalEnv = env;
      jqXHR.originalUrl = url;			
    },		
    success: function(json, status, jqXHR) {
      console.log('....success fetching: ' + jqXHR.originalUrl);
      // store the returned data in ALLDATA
      ALLDATA[jqXHR.originalEnv] = json;
    },
    error: function(jqXHR, status, e) {
      console.log('....ERROR: Failed to get data from [' + url + '] ' + status + ' ' + e);
    }
  });
}

3) Fetch each JSON feed:

for (var env in ENVS) {
  console.log('Fetching data for env [' + env +'].');
  var promisedData = getDataForEnvironment(ENVS[env]);	
  promisedData.success(function (data) {});
} 

4) For each configuration asset or setting create a table in the report

For example, CSElements:

1) Get a list of unique CSElement names from all of the returned JSON data.
2) For each unique CSElement name, create a row in the table 
3)   Select 1 environment to represent the master or ideal state
     (e.g. "Everything should be like Production Delivery")
4)   For each environment, compare the last modified date of this envs CSElement to the master.
     Highlight any differences in last modified date or missing CSElements.
5)   Repeat... 

 


Appendix

This section contains various SQL statements that can be used to retrieve configuration settings from the DB. 

Templates 

<ics:sql
      sql="SELECT name,updateddate FROM Template WHERE status != 'VO'"
      listname="TemplateList"
      table="Template" />

CSElements

<ics:sql
      sql="SELECT name,updateddate FROM CSElement WHERE status != 'VO'"
      listname="CSEList"
      table="CSElement" />

Start Menus

<ics:sql sql="select sm.id, sm.cs_name, sm.cs_description, sm.cs_assettype,
      sm.cs_assetsubtype, sm.cs_itemtype, smr.cs_rolename, p.name
      from StartMenu sm, StartMenu_Sites sms, StartMenu_Roles smr, Publication p
      where sm.id=sms.ownerid and sm.id=smr.cs_ownerid and sms.pubid=p.id
      order by sm.id"
      listname="startList"
      table="Publication,StartMenu,StartMenu_Roles,StartMenu_Sites"/> 

Publishing Configurations

<ics:sql
      sql="select id, name, description, type, dest, factors from PubTarget"
      listname="pubTargetList"
      table="PubTarget" />

Tree Tabs

<ics:sql
      sql="select tt.id, tt.title, tt.tooltip, p.name as pubname, ttr.cs_rolename, ttsect.name as sectname
      from TreeTabs tt, TreeTabs_Roles ttr, TreeTabs_Sect ttsect,TreeTabs_Sites ttsites
      LEFT JOIN Publication p  on p.id=ttsites.pubid
      where p.id is not null and tt.id=ttsites.ownerid and ttsites.pubid=p.id and
      tt.id=ttr.cs_ownerid and tt.id=ttsect.ownerid
      order by tt.id"
      listname="treeTabList"
      table="TreeTabs,TreeTabs_Roles,TreeTabs_Sect,TreeTabs_Sites,Publication" /> 

Filters

<ics:sql
      sql="select name,description,classname from Filters"
      listname="filtersList"
      table="Filters" />

Attribute Types

<ics:sql
      sql="select id,valuetype,name,updateddate from AttrTypes where status != 'VO'"
      listname="AttrList"
      table="AttrTypes" />

WebReference Patterns

<ics:sql
      sql="select id,webroot,pattern,assettype,name,params,publication from WebReferencesPatterns"
      listname="WebRefList"
      table="WebReferencesPatterns" />

Device Groups

<ics:sql
      sql="select id,devicegroupsuffix,updateddate,name from DeviceGroup"
      listname="DeviceList"
      table="DeviceGroup" />

Site Entries

<ics:sql
      sql="select se.id,se.name,se.pagename,se.cselement_id,se.updateddate,cse.rootelement from SiteEntry se
            LEFT JOIN CSElement cse on cse.id = se.cselement_id where se.status != 'VO'"
      listname="SiteList"
      table="SiteEntry,CSElement" />

Webroots

<ics:sql
      sql="select id,name,rooturl,updatedby,updateddate from WebRoot"
      listname="webrootList"
      table="WebRoot" />

Page Definitions

<ics:sql
      sql="select pd.id, pd.name, pd.updatedby, pd.updateddate, pd.description, pdt.attributeid,
            pa.name as nameattr, pdt.requiredflag, pdt.ordinal 
      from PageDefinition pd, PageDefinition_TAttr pdt, PageAttribute pa where pd.status != 'VO'
            and pa.id=pdt.attributeid and pdt.ownerid=pd.id order by pd.id,pdt.ordinal"
      listname="pageDefList"
      table="PageDefinition,PageAttribute,PageDefinition_TAttr" />

FW_Application

<ics:sql
      sql="select id,name,updateddate from FW_Application where status != 'VO'"
      listname="FWList" table="FW_Application" />

Custom Elements

<ics:sql
      sql="select elementname from ElementCatalog where elementname like 'CustomElements%'"
      listname="elementList"
      table="ElementCatalog" />

Comments:

comparing DB schema using tools in any DB tool like SQL Developer can do this easily.

Posted by guest on June 29, 2015 at 03:02 AM CDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Oracle Product Development IT Collaborative Applications Services (CAS) is responsible for Collaboration and Content Management based systems at Oracle.

The PDIT-CAS Blog aggregates and organizes content produced by PDIT CAS team members responsible for delivery of major corporate websites like oracle.com, java.com, cloud.oracle.com, my.oracle.com, community.oracle.com etc.

Content in this blog captures best practices, tips and tricks and guidance that the team members gain from leveraging Oracle technology to solve real world problems being faced by Oracle IT.

Search

Archives
« August 2015
SunMonTueWedThuFriSat
      
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
     
Today