Thursday
May262016

Oracle Business Intelligence Cloud Service Migration

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.

Wednesday
Apr132016

Try Advanced Analytics and Business Intelligence on the Oracle Cloud for Free!

Our new Oracle Test Drive is up and running! Click here to see Dan Vlamis announce the launch while at Collaborate 2016. To just go ahead and try out a test drive for yourself, click here.

Wednesday
Mar302016

Deploying Oracle Business Intelligence 12c on AWS EC2 Instance

If you have ever worked as a developer inside of a larger organization and have needed to quickly and easily create a demo or research environment you may have found it difficult to provision the necessary resources and had to wait on someone else to do it for you.  I have never been a fan of waiting or having  server admins question me about the number of processors or amount of RAM I "really" need.  

Because of the flexibility, affordability and ease of deployment and management it no longer makes sense to not use cloud based compute as a part of your dev and test infrastructure.  With the maturation and competition in the compute utility space from players such as Amazon, Google, Microsoft and even Oracle it has become extremely easy and affordable to fire up compute instances on demand that act just like any other compute resource inside of your network.  

AWS (Amazon Web Services) has built an excellent on-demand service that offers simple management of storage, compute and networking that anyone with basic developer skills can use. To their credit, the other players in the space are coming along very rapidly as well and which one(s) you use will most likely be a function of convenience.

This YouTube video and companion blog post is copied from a series of posts on my personal blog I've been working on around creating your own development environment utilizing AWS.  If you haven't tried using these resources before because you weren't quite sure how to do it I hope this post gives you the information and confidence you need to give it a try.

-Arthur

 

This is the third in a series of posts around putting together your own Oracle BI development environment on AWS.  Utilizing AWS can be a remarkably affordable (<$50/month assuming system up 50 hours/week) option for most developers.

See Deploying Oracle Database 12c on AWS EC2 Instance and Deploying Oracle APEX on EC2 against Oracle Database on EC2 for more information.  (Note - deploying APEX is not necessary for following this post but you do need a database available)

In this post, I will show you how to install Oracle Business Intelligence 12c on an Amazon Web Services EC2 instance.

First let me list some super helpful resources -

Posts that I use as the basis for what I show in this blog post -

Automating Database Startup and Shutdown on Linux

INSTALLING OBIEE 12C. PART 1: PREREQUISITES

SwapFaq and Swap

How To Install and Configure GUI for Amazon EC2 RHEL 7 Instance

Oracle WebLogic Server 12c: Creating a Boot Identity File for Easier Server Start Up

RPM for Linux Installation Notes

MobaXterm Download

Fix Firefox Already Running Error

Licensing Oracle Software in the Cloud Computing Environment

Workflow for Installing OBIEE 12c

This YouTube video follows the workflow I’ve created below –

 

 Make sure you have a database - See my post: Deploying Oracle Database 12c on AWS EC2 Instance

  1. Provision AWS ol7 Instance
  2. Attach volumes
    • 5GB Swap
    • 15GB /u01
    • 15GB /inv
    • 15GB Root
  3. Login via MobaXterm as ec2-user and change password
    • sudo passwd ec2-user
  4. Install packages needed for instance
    • sudo yum install wget zip unzip -y
    • sudo yum install perl-libwww-perl.noarch -y
    • sudo yum install oracle-rdbms-server-12cR1-preinstall -y (this is used for installing database but works wonderfully for OBIEE too)
  5. Make Swap and mount volumes
    • df -h
    • lsblk
    • sudo mkswap /dev/xvdb (the volume id (xvdb here) is instance dependent)
    • sudo swapon /dev/xvdb
    • sudo vi /etc/fstab
    • /dev/xvdb none swap defaults 0 0
    • sudo mkfs -t ext4 /dev/xvdc (the volume id (xvdd here) is instance dependent)
    • sudo mount /dev/xvdc /u01
    • /dev/xvdc /u01 ext4 defaults 0 0
    • /dev/xvdf /inv ext4 defaults 0 0 (this is for software install files, you may need to format a drive separately for this)
    • sudo mount -a (remount everything to make sure it worked)
  6. Change password for oracle user and make it possible for user to connect remotely (Managing User Accounts on Your Linux Instance)
    • sudo passwd oracle
    • sudo chown -R oracle.oinstall /u01
    • sudo chown -R oracle.oinstall /inv
    • su oracle (switch to oracle user)
    • cd ~(make sure your are in oracle user home)
    • mkdir .ssh (create location for key file)
    • chmod 700 .ssh (set permissions)
    • touch .ssh/authorized_keys (create file)
    • chmod 600 .ssh/authorized_keys (set permissions)
    • GET http://169.254.169.254/latest/meta-data/public-keys/0/openssh-key>.ssh/authorized_keys (copy public key to file)
    • log out and login as oracle user
  7. Install desktop (borrowed from this post at DevOpsCube)
    • su root
    • sudo yum groupinstall -y "Server with GUI"
    • sudo systemctl set-default graphical.target
    • sudo systemctl default
    • sudo rpm -ivh http://li.nux.ro/download/nux/dextop/el7/x86_64/nux-dextop-release-0-1.el7.nux.noarch.rpm
    • sudo yum install -y xrdp tigervnc-server
    • sudo chcon --type=bin_t /usr/sbin/xrdp
    • sudo chcon --type=bin_t /usr/sbin/xrdp-sesman
    • sudo systemctl start xrdp
    • sudo systemctl enable xrdp
    • sudo firewall-cmd --permanent --add-port=3389/tcp
    • For OBIEE go ahead and open 9500 and 9502 as well
      • sudo firewall-cmd --permanent --add-port=9500/tcp
      • sudo firewall-cmd --permanent --add-port=9502/tcp
    • sudo firewall-cmd --reload
  8. Install gconf-editor and disable lock screen
    • yum -y install gconf-editor
    • alt-f2 gconf-editor
    • To disable the lock screen and log out functions, set the /desktop/gnome/lockdown/disable_lockscreen_and_logout key to true.
  9. Install java & set JAVA_HOME in bash profile
    • su root
    • rpm -ivh jdk-8u73-linux-x64.rpm
    • vi /home/oracle/.bash_profile
    • export JAVA_HOME=/usr/java/jdk1.8.0_73
    • export PATH=$JAVA_HOME/bin:$PATH
    • source ~/.bash_profile (reloads profile)
  10. Install SQL Developer
    • su root
    • rpm -ivh
    • find -name sqldeveloper*
  11. Install Infrastructure and OBI (borrowed from series of posts starting here by Red Stack Tech)
    • $JAVA_HOME/bin/java -d64 -jar fmw_12.2.1.0.0_infrastructure.jar
    • ./bi_platform-12.2.1.0.0_linux64.bin
    • cd /u01/home/oracle/Oracle/Middleware/Oracle_Home/bi/bin
      ./config.sh
  12. Setup auto start for your pluggable database
    • To set up your pluggable database to autostart on the database you can use a trigger.  Login as the SYS user on the container database and execute the following -
      • create or replace trigger sys.after_startup
        after startup on database
        begin
        execute immediate 'alter pluggable database YOUR_PDB_NAME open read write';
        end after_startup;
      • I have links to Tim Hall's post on how to autostart your database in this post on Deploying Oracle Database 12c on AWS EC2 Instance
  13. Setup auto start for OBIEE 12c
    • To make sure your weblogic instance doesn't prompt for user name and password set up a boot identity file
      • cd /u01/home/oracle/Oracle/Middleware/Oracle_Home/user_projects/domains/bi/servers/AdminServer
      • mkdir security
      • cd security
      • vi boot.properties
        • add lines -
          • username=YOUR_WEBLOGIC_USER
            password=YOUR_WEBLOGIC_PASSWORD
    • To make your OBIEE instance auto-start when you start the machine instance I have borrowed from Tim Hall's post on auto-starting the Oracle database look under the heading The "rsh" Command for the auto-start script for a database
    • Create a file called "/etc/init.d/dbora" as the root user and copy the script on Tim Halls page to the file
      • Change the line ORACLE_HOME line to your OBI startup script location
        • ORACLE_HOME=/u01/home/oracle/Oracle/Middleware/Oracle_Home/user_projects/domains/bi/bitools
        • and the dbstart and dbshut lines to reference the start and stop scripts respectively
          • runuser -l $ORACLE -c "$ORACLE_HOME/bin/start.sh "
          • runuser -l $ORACLE -c "$ORACLE_HOME/bin/stop.sh "
      • Your final script should look something like the following
      • #!/bin/sh# chkconfig: 345 99 10# description: Oracle auto start-stop script.## Change the value of ORACLE_HOME to specify the correct Oracle home

        # directory for your installation.

        ORACLE_HOME=/u01/home/oracle/Oracle/Middleware/Oracle_Home/user_projects/domains/bi/bitools

        #

        # Change the value of ORACLE to the login name of the

        # oracle owner at your site.

        #

        ORACLE=oracle

        PATH=${PATH}:$ORACLE_HOME/bin

        export ORACLE_HOME PATH

        #

        case $1 in

        'start')

        runuser -l $ORACLE -c "$ORACLE_HOME/bin/start.sh "

        touch /var/lock/subsys/dbora

        ;;

        'stop')

        runuser -l $ORACLE -c "$ORACLE_HOME/bin/stop.sh "

        rm -f /var/lock/subsys/dbora

        ;;

        *)

        echo "usage: $0 {start|stop}"

        exit

        ;;

        esac

        #

        exit

    • Lastly change the permissions and add entry for startup
      • chmod 750 /etc/init.d/dbora
      • chkconfig --add dbora
Friday
Mar182016

Vlamis March 2016 Newsletter Distributed

We published our March 2016 Newsletter that included the following articles:

Oracle Advanced Analytics iSeminar
New Release of BI Cloud Services (BICS)
New OU Predictive Analytics Course Now Available
Tim to Teach OU’s Data Mining and R Courses
Vlamis Presenting at Upcoming Conferences
We're Hiring Consultants

Click on any of the links to see our content.  Send us an email at cgillespie@vlamis.com if you want to receive this directly or if you want to unsubscribe

Monday
Mar142016

Oracle Advanced Analytics iSeminar March 23rd at 2:00 PM Eastern 11:00 AM Pacific

Click here to Register

Join me for an in-depth exploration into predictive analytics using Oracle Data Mining and Oracle R Enterprise.

If you’ve been wanting to expose your colleagues to the power of using the Oracle Database as a platform for predictive analytics, this special session will be perfect for you. We’ll use a combination of live demos and business use cases to explain that predictive analytics doesn’t require an advanced degree in mathematics, just a database and a few good business questions. We’ll share how to leverage the work you’ve done in building a data warehouse and collecting business data sets into solid evidence for business decisions. Predictive analytics is all about looking forward into the future and leveraging data to assess and evaluate alternative courses of action. Too often, executives and managers rely on gut instinct without using the data they already have to make better decisions.

Here’s the outline for the session:

  • Key issues in leveraging the power of analytics
  • Using Oracle database as an analytics platform.
  • Oracle Advanced Analytics overview
    • Oracle Data Mining
    • Oracle R Enterprise
  • Common use cases for predictive analytics
  • Where to start when developing your analytics capabilities

Bring your questions and your colleagues and start thinking about where you want to start with preditive analytics.

Click here to Register