When using the Oracle Configurator of Oracle E-Business Suite usually a dedicated “development” environment is used to create and update the configuration models. From this Configurator Development environment the configuration projects (called models) can be published to different E-Business Suite instances – usually first to a testing and then to the production environment. Let’s have a look at the life-cycle operations on such an instance.
Keeping the Configurator Development Environment up-to-date
Due to its nature the Configurator Development Environment (often named “CZ”) is a “mixture” between a regular Dev/Test-Environment – which for E-Business Suite is usually replaced with a new Production to Test (P2T) copy regularly -and a real production environment -which is never refreshed but tightly backed up and patched. This gives two approaches to manage an E-Business Suite Configurator Environment:
- Handle it like your production: so back it up using RMAN regularly and apply all patches applied to PROD also to the CZ instance.
- Handle it like other dev/test systems: replace it with a new copy of PROD whenever major patches where applied.
Often a mix of the two approaches is used. For one of our customers though this led to a completely unpatched instance: The database was still on 11.2.0.3 and the Application on 12.1.3 without any Recommended Patch Collections. Applying all those Database and Application Upgrades would have been a lot of work. That’s why we wanted to create a new regular P2T copy CZ2 and then make this instance the new Configurator Development instance.
Create a Clone
On OCI creating the P2T Copy is trivial and done with just a push of a button. On that Clone we had to allow .jar-file upload (with FND_FILE_MIME_TYPES_PKG). Furthermore we un-scheduled most of our normally running concurrent programs and performed a quick test, if everything works as usual – especially the Configurator runtime environment.
Enable as Configurator Development Instance
First of all we had to assign the “Oracle Configurator Administrator” and the “Configurator Developer” responsibilities. But when accessing the responsibility we saw the following error: “You cannot run Oracle Configurator Developer on a database that is defined as a publication target instance”. This is expected as described here:
https://docs.oracle.com/cd/E18727_01/doc.121/e14322/T440679BHADDIBH.htm#CHDDDCBJ. This can easily be fixed by running the “Convert Publication Target To Development” concurrent program.
Cleaning the instance
In our case the Configurator Scheme of the P2T Copy was corrupted in many places. That’s why we developed – together with and approved by Oracle Support – the following cleanup script:
set serveroutput on;
DECLARE
l_return_status VARCHAR2(1);
l_msg_count NUMBER;
l_msg_data VARCHAR2(32000);
BEGIN
FOR i IN(SELECT ui_def_id FROM CZ_UI_DEFS
WHERE ui_style='7' AND seeded_flag='0')
LOOP
FOR k IN (SELECT jrad_doc FROM CZ_UI_PAGES
WHERE ui_def_id = i.ui_def_id and seeded_flag='0')
LOOP
IF jdr_docbuilder.documentexists(k.jrad_doc)=TRUE THEN
dbms_output.put_line ('deleting ' || k.jrad_doc||'....');
jdr_docbuilder.deleteDocument(k.jrad_doc);
END IF;
COMMIT;
END LOOP;
END LOOP;
FOR i IN(SELECT jrad_doc FROM cz_ui_templates
WHERE seeded_flag='0')
LOOP
IF jdr_docbuilder.documentexists(i.jrad_doc)=TRUE THEN
jdr_docbuilder.deleteDocument(i.jrad_doc);
dbms_output.put_line ('deleting ' || i.jrad_doc||'....');
END IF;
COMMIT;
END LOOP;
END;
/
--Script to delete all CZ user data
DECLARE
num_of_tables NUMBER := 0;
CURSOR cz_tables IS
SELECT table_name
FROM all_tables
WHERE owner = 'CZ'
AND table_name NOT IN
('CZ_TYPE_RELATIONSHIPS', 'CZ_LOOKUP_VALUES', 'CZ_LOOKUP_VALUES_TL',
'CZ_SERVERS', 'CZ_XFR_FIELDS', 'CZ_DB_SETTINGS', 'CZ_XFR_TABLES')
and (table_name not like 'CZ_CONFIG%' or :p_clear_configs = 'Y')
and table_name not in (select table_name from dba_tab_columns where owner='CZ' and column_name='SEEDED_FLAG');
CURSOR cz_tables_delete IS
SELECT table_name
FROM all_tables
WHERE owner = 'CZ'
AND table_name NOT IN
('CZ_TYPE_RELATIONSHIPS', 'CZ_LOOKUP_VALUES', 'CZ_LOOKUP_VALUES_TL',
'CZ_SERVERS', 'CZ_XFR_FIELDS', 'CZ_DB_SETTINGS', 'CZ_XFR_TABLES')
and (table_name not like 'CZ_CONFIG%' or :p_clear_configs = 'Y')
and table_name in (select table_name from dba_tab_columns where owner='CZ' and column_name='SEEDED_FLAG');
BEGIN
dbms_output.put_line ('Starting to Delete the data from the tables....');
FOR rec_tables IN cz_tables LOOP
dbms_output.put_line ('truncating data from ' || rec_tables.table_name||'....');
EXECUTE IMMEDIATE 'TRUNCATE TABLE CZ.' || rec_tables.table_name || ' ';
num_of_tables := num_of_tables + 1;
END LOOP;
dbms_output.put_line ('truncated data from ' || num_of_tables || ' tables.');
num_of_tables:=0;
FOR rec_tables IN cz_tables_delete LOOP
dbms_output.put_line ('Removing non-seeded data from ' || rec_tables.table_name||'....');
EXECUTE IMMEDIATE 'DELETE FROM CZ.' || rec_tables.table_name || ' where seeded_flag=0';
num_of_tables := num_of_tables + 1;
END LOOP;
dbms_output.put_line ('deleted non-seeded data from ' || num_of_tables || ' tables.');
Insert into CZ_MODEL_USAGES (MODEL_USAGE_ID,NAME,DESCRIPTION,NOTE,IN_USE,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN) values ('-1','Any Usage','This usage is seeded as a way of facilitating the implementation of "publication to any usage"',null,'1','-173',to_date('30.08.01','DD.MM.RR'),'-173',to_date('30.08.01','DD.MM.RR'),null);
Insert into CZ_MODEL_USAGES_TL (MODEL_USAGE_ID,LANGUAGE,SOURCE_LANG,DESCRIPTION) values ('-1','D','D','Diese Verwendung ist vordefiniert, um die Implementierung von "Veröffentlichung für beliebige Verwendung" zu ermöglichen');
Insert into CZ_MODEL_USAGES_TL (MODEL_USAGE_ID,LANGUAGE,SOURCE_LANG,DESCRIPTION) values ('-1','US','US','This usage is seeded as a way of facilitating the implementation of "publication to any usage"');
dbms_output.put_line ('Done with the clean up. Please proceed with your next action....');
COMMIT;
END;
/
Migrating the Models
The next step is to migrate the still relevant (we had a lot of outdated data) Configurator Projects from the old CZ instance to the new “CZ2” instance. For this define CZ3 as a Remote Server on CZ,then select the model you want to migrate. Select “migrate” and chose, where you want to migrate the model to. Finally run the “Migrate Models” concurrent program to perform the actual migration.
In our case we ran into issues regarding
- having UTF-8 multi-byte-characters in names of objects: This seems to be a bug; Oracle is still working on it, in the meantime we tidied those multi-byte-characters
- CZ_ITEM_TYPES, CZ_ITEM_TYPE_PROPERTIES, CZ_ITEM_MASTERS, and CZ_ITEM_PROPERTY_VALUES weren’t migrated correctly. This, too, is a bug still worked on , as a workaround we copied CZ_ITEM_TYPE_PROPERTIES manually.
Publication to Runtime Environment
Before publishing the models for the first time from the new CZ3 to the regular E-Business Suite DEV/TEST/PROD environment the above script should be run there as well. But with p_clear_configs=N – otherwise all the Configurator Runtime data is wiped!
Test
Definitely this operation needs to be tested thoroughly. For us it caused no issues both with old and with new models; we forced everything to be upgraded to the latest model on re-opening of old models though.
Backing up the CZ Environment
The CZ Development Environment can be backed up using regular RMAN means. We decided against that though since it would be many Terrabytes to backup. Again in cooperation with Oracle Support we went for an approach to backup just the CZ schema and some JDR-tables in the APPLSYS Schema.
Summary
Even though we ran into some issues the above approach was way easier to implement than manually upgrading our old CZ instance. The only drawback so far is, that now our CZ3 instance is a lot bigger in size than the old CZ environment has been (due to the huge amount of additional data on PROD). During the process we also tried to:
- migrate the entire CZ scheme using Database Export-Import
- migrate using the “Migrate Configurator Data” concurrent program. This is described here: https://docs.oracle.com/cd/E18727_01/doc.121/e14322/T440679BABHHEEC.htm
With both approaches – even though described by Oracle Support – we ran into a lot of issues; probably this is due to the long history of our CZ instance having initially been on some early R12 release.