We ran into some interesting migration issues while migrating our Oracle Cloud accounts around. We had a stand-alone Business Intelligence Cloud Service (BICS) account. We used it for some client development and internal training. We had also made use of the Application Express (APEX) and Schema as a Service to develop an internal time entry system. We reported and analyzed our employee utilization through the Business Intelligence front-end. We had another account with more Oracle Cloud services and it included BICS also, so we wanted to contract down to one service account.

However, we have a demonstration system for BICS (try it out at www.vlamis.com/td) already in place at the destination account. We were going to need to merge our BICS data models and the catalog of items created in BICS.

Because the destination BICS account was already in a production use, we could not simply migrate using a bar file. A bar file would overwrite everything in the destination account. Additionally, there was a large amount of development and testing done in the source system that we did not want to bring to the new system. Ultimately we needed to:

• Migrate some of the data in our Schema as a Service
• Migrate one APEX application
• Migrate one subject area from BICS
• Migrate a selection of analyses and dashboards from the BICS catalog relating to the migrated subject area.

Data Migration
We found no easy and direct way to move the specific data and structures needed from Schema as a Service to Schema as a Service. Oracle has provided several ways to move data around, but most are meant to be used from On Premises to Cloud and back. We ended up using this fairly complex, but reliable process to move our data from Schema as a Service to Schema as a Service::

1. Export the DDL of the tables, views, triggers, indexes, and so on by way of the APEX SQL Commands application in the source system.
2. Create a SQL Script from that DDL within the destination system in APEX and execute it.
3. Export a .DMP file of the entirety of the data from the source system (Which included an extraneous and large amount of data from Sample App.)
4. Import the .DMP file into an On Premises Oracle database.
5. Disable the database triggers on the destination Schema as a Service.
6. Move the data from the specific tables we wished to move via the SQL Developer Cart system into the destination Schema as a Service
7. Enable the database triggers.

It is not a straight-forward process, but it worked very well for our specific use case.

APEX Application Migration
We exported our timesheet application via the APEX admin page on the source system and imported it into the destination. It was very simple and just worked like nothing had changed when we enabled it.

BI Data Model
There appears to be no way to move a Business Intelligence data model on its own from one Oracle Cloud instance to another. We ended up just recreating it by having it open in the source system in one browser, and the destination open in another and clicking through the various settings in both systems until they matched. We have been told by Oracle that they are working on a way to merge data models, but that it simply isn’t possible yet.

Catalog Objects
Migration of BICS Catalog Objects between instances can be very tricky. BICS internally names the subject areas P0, P1, P2, etc. and does not use the subject area name itself. On our source system, the subject area we wished to migrate was named P0. The destination system calls our same subject area P1. In the on premises version of OBI you can just edit the XML of the analysis and change the subject area. However, in BICS, the XML is read only and this is not possible.

We did find that we could import the catalog objects into an On Premises OBI instance and edit the XML to change the subject areas in them to point from P0 to P1, save them, and export the catalog objects from that On Premises system and import them into the destination BICS system. Since that worked, we tried exporting an entire folder of objects we wanted to migrate and used the OBI Catalog Manager to do a full search/replace of P1 to P0 and import that edited object. That worked as well to fix the subject area issue.

Ultimately, we are up and running on our new system and are very pleased with the Cloud Services Oracle has provided us. If you have any questions about the process we went through, or have a related question about your own migration, please feel free to leave a comment, or email me at jclark@vlamis.com.