Deploying Oracle Business Intelligence 12c on AWS EC2 Instance

Standard

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 –

  1. Make sure you have a database – See my post: Deploying Oracle Database 12c on AWS EC2 Instance
  2. Provision AWS ol7 Instance
  3. Attach volumes
    • 5GB Swap
    • 15GB /u01
    • 15GB /inv
    • 15GB Root
  4. Login via MobaXterm as ec2-user and change password
    • sudo passwd ec2-user
  5. 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)
  6. 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)
  7. 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&gt;.ssh/authorized_keys (copy public key to file)
    • log out and login as oracle user
  8. 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
  9. 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.
  10. 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)
  11. Install SQL Developer
    • su root
    • rpm -ivh
    • find -name sqldeveloper*
  12. 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
  13. 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
  14. 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

Deploying Oracle APEX on EC2 against Oracle Database on EC2

Standard

YouTube Playlist – Oracle Database and APEX on AWS EC2

AWS (Amazon Web Services) supports some versions of APEX on its RDS service but if you really want to be able to have the full power and ability of the latest versions you need to learn how to install the database on an EC2 instance ja(as I show in this post – DEPLOYING ORACLE DATABASE 12C ON AWS EC2 INSTANCE) and install and configure APEX and ORDS on a separate EC2 instance.

In the accompanying YouTube video I will show the steps to delete APEX from the container 12c Database and then install on a pluggable database.  Lastly I’ll show how to configure and run the ORDS listener on a separate EC2 instance

First let me list some super helpful resources –

Posts from my new hero Tim Hall and other random stuff I encountered

Multitenant : Uninstall APEX from the CDB in Oracle Database 12c Release 1 (12.1)

Oracle Application Express (APEX) 5.0 Installation

Connection error after upgrade to 4.1

Register DB Service to Listener

REST Data Services Installation and Configuration Guide

Application Express Installation Guide

Workflow for Installing APEX and ORDS

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

  1. Set up Ec2 instance using OL7.2-x86_64-HVM-2015-12-10 Amazon Machine Image (AMI)
  2. Login via MobaXterm as ec2-user and change password
    • sudo passwd ec2-user
  3. Install packages needed for instance
    • sudo yum install wget zip unzip -y
    • sudo yum install perl-libwww-perl.noarch -y
    • sudo yum install java-1.8.0-openjdk.x86_64 -y
    • sudo yum install updates
  4. Create apex user and make it so user can connect
  5. Make directories for apexlistener, oracle client, and ORDS
    • mkdir /home/apexuser/apexlistener
    • mkdir /home/apexuser/orclclient
    • mkdir /home/apexuser/ORDS
  6. Upload files and unzip in respective directories
    • unzip apex_5.0.3_en.zip -d /home/apexuser/apexlistener
    • upload oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm into /home/apexuser/orclclient
    • upload oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64.rpm into /home/apexuser/orclclient
    • unzip ords.3.0.3.351.13.24.zip -d /home/apexuser/ORDS
  7. Install oracle client
    • su ec2-user
    • cd /home/apexuser/orclclient
    • rpm -ivh oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm
    • rpm -ivh oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64.rpm
  8. Update bash profile of apex user for client
    • su apexuser
    • cd ~
    • vi ~/.bash_profile
      • export LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib:$LD_LIBRARY_PATH
      • export PATH=/usr/lib/oracle/12.1/client64/bin:$PATH
    • source ~/.bash_profile
    • sqlplus SYS/PASSWORD@HOSTNAME:1521/orcl as sysdba
  9. Go to your database server & uninstall apex from container database
    • !!!!!!!Make sure you uninstall or unplug any pluggable databases before you do this!!!!!!
    • /u01/app/oracle/product/12.1.0/dbhome_1/apex
    • sqlplus / sys as sysdba
    • @apxremov_con.sql
  10. Create pluggable database & add USERS tablespace
    • Go to SQL Developer for these tasks
    • Create pluggable database and open read / write
    • Connect to pluggable database
    • Create Users tablespace on pluggable database
      • CREATE TABLESPACE “USERS” DATAFILE
        ‘/u01/app/oracle/oradata/orcl/pdbseed/users01.dbf’ SIZE 5242880
        AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
        LOGGING ONLINE PERMANENT BLOCKSIZE 8192
        EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
        NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
  11. Install apex on your container database, change apex admin user and create rest data services user
    • ALTER SESSION SET CONTAINER =PDBORCL (your pluggable database name)
    • @apexins.sql SYSAUX SYSAUX TEMP /i/
    • @apxchpwd.sql
    • @apex_rest_config.sql
    • ALTER USER APEX_PUBLIC_USER IDENTIFIED BY Password1 ACCOUNT UNLOCK;
  12. Go to your Apex server and Run ORDS set up
    • cd /home/apexuser/ORDS
    • java -jar ords.war
    • /home/apexuser/ORDS/config/ (configuration directory)
    • /home/apexuser/apex/images (static resources directory)
    • add tcp rule for 8080 (or port you used if you haven’t already when you created server)
    • stop server using ctrl c and start with nohup
      • nohup java -jar ords.war &
    • if you need to stop server issue top command and find java process running under apexuser and kill process number
      • top -u apexuser
      • kill [process]
  13. At this point your APEX UI should be available at http://HOSTNAME:8080/ords

Deploying Oracle Database 12c on AWS EC2 Instance

Standard

YouTube Playlist – Oracle Database and APEX on AWS EC2

See companion post Deploying Oracle APEX on EC2 against Oracle Database on EC2

So if you have ever used AWS (Amazon Web Services) before you probably know they have an RDS service that will allow you to auto-deploy several flavors of database.  In this youtube playlist I show my students how to do just that.

But if you have deployed Oracle databases via RDS you may have also noticed that you don’t really get the kind of access (no root and no sysdba) to the instance you really need if you want to truly be able to utilize the full power of the database.

So what are you to do?

Well thankfully AWS also allows you to deploy EC2 instances of various OS (Operating System) flavors which are really just VM’s (Virtual Machines).  Additionally, lots of helpful people have created VM’s that have OS’s, like Oracle Linux, already installed that you can use as the basis of new instances.  In this post I explore how to accomplish this feat using lots of great resources I’ve found around the net.  If you have never touched a Linux OS or AWS or an Oracle Database then don’t be scared this post is for you!  If you have done these things, but not on AWS, then this post is also for you but I will definitely do my best to assume you are a beginner.

This example is done on a t2.small instance size (1 vCPU + 2GB RAM) which is one step above AWS’s free tier but still a very reasonable 2 cents an hour for powered on instances (no charge when they are off).  It seems to run just fine and I have used even smaller instances to run APEX development work spaces for 15 students.  Makes me wonder about just how over licensed / resourced a lot of people might be.

First let me list some super helpful resources –

A post by Tim Hall explaining Installation of Oracle on EC2 and it’s companion Oracle Database 12c Release 1 (12.1) Installation On Oracle Linux 7 (OL7) which are really the basis for most of what I show you but with a few key changes, by me, for beginners.

Other posts by Tim Hall for auto-starting database and pdb’s –

See heading ‘The “runuser” Command’ on this post for auto-starting 12c

See ‘Pluggable Database (PDB) Automatic Startup’ on this post for auto-starting PDB’s

Also uber helpful –

MobaXterm Download

AWS On-Demand Instance Prices (remarkably cheap)

Managing User Accounts on Your Linux Instance

Retrieving the Public Key for Your Key Pair on Linux

Making an Amazon EBS Volume Available for Use

Oracle Database Software Downloads

20 Linux YUM (Yellowdog Updater, Modified) Commands for Package Management

YUM COMMAND CHEAT SHEET

Linux Add a Swap File – Howto

Amazon EC2 Pricing

Cannot connect to Oracle DB on EC2 instance (Firewall NOT the issue)

And finally a video on how to create AWS account I’ve used with my students in case you need it.

Workflow for creating Oracle EC2 database instance:

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

  1. Download MobaXterm
  2. Set up Ec2 instance using OL7.2-x86_64-HVM-2015-12-10 Amazon Machine Image (AMI) with three additional volumes of 5, 8 and 10 gb (this will make your life easier).
  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
  5. Mount volumes, format disks, mkdir
    1. sudo mkfs -t ext4 /dev/xvdb (be super careful here)
    2. sudo mkfs -t ext4 /dev/xvdc (be super careful here)
    3. sudo mkdir -p /swapfile1
    4. sudo mount /dev/xvdb /swapfile1 (swap file)
    5. sudo mkdir -p /u01/software
    6. sudo mount /dev/xvdf /u01/software (zip files)
    7. sudo mkdir -p /u01/app/oracle/oradata/orcl
    8. sudo mount /dev/xvdc /u01/app/oracle/oradata/orcl (data files)
  6. Add swap file
    • sudo dd if=/dev/zero of=/swapfile1/swapfile  bs=1024 count=3145728 (creates 3G swapfile)
    • sudo chown root:root /swapfile1/swapfile
    • sudo chmod 0600 /swapfile1/swapfile
    • sudo  mkswap /swapfile1/swapfile
    • sudo swapon /swapfile1/swapfile
    • free -m
  7. Make fstab entries
    • lsblk (gives names of volumes)
    • sudo cp /etc/fstab /etc/fstab.orig (back up fstab)
    • sudo vi /etc/fstab
    • /swapfile1 none swap sw 0 0
    • /dev/xvdc /u01/app/oracle/oradata/orcl ext4 defaults 0 0
    • /dev/xvdf /u01/software ext4 defaults 0 0
    • sudo mount -a (remount everything to make sure it worked)
  8.  Give oracle user ownership of directories
    • sudo chown -R oracle.oinstall /u01/app/oracle/oradata/orcl
    • sudo chown -R oracle.oinstall  /u01/software
    • sudo chown -R oracle.oinstall /u01
  9. Change hostfile for instance by adding hostname to localhost
    • hostname
    • sudo vi /etc/hosts
  10. Change password for oracle user and make it possible for user to connect remotely (Managing User Accounts on Your Linux Instance)
    • sudo passwd oracle
    • 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&gt;.ssh/authorized_keys” (copy public key to file)
    • log out and login as oracle user
  11. Upload database zip files into /u01/software (this takes a while)
  12. Unzip files
    • cd /u01/software
    • unzip linuxamd64_12102_database_1of2.zip
    • unzip linuxamd64_12102_database_2of2.zip
  13. Start install
    • cd /ora_software/database
    • ./runInstaller
  14. Run scripts as root (careful here you need to open separate instance of MobaXterm)
    • sudo /u01/app/oraInventory/orainstRoot.sh
    • sudo /u01/app/oracle/product/12.1.0/dbhome_1/root.sh
  15. Say OK to run dbca to create a database
  16. Update tnsnames.ora, listener.ora files by replacing localhost with actual host name
    • cd /u01
    • find -name tnsnames.ora
    • find -name listener.ora
    • hostname
    • vi ./app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora
    • vi ./app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
  17. Restart listener and database
    • . oraenv
    • lsnrctl stop
    • lsnrctl start
    • sqlplus sys as sysdba
    • shutdown immediate;
    • startup;
  18. At this point you should be able to connect to your database remotely with SQL Developer or your favorite tool, remember if you are shutting down your EC2 instance to first stop the listener and the database.

 

 

APEX Basics

Standard

In this series of YouTube videos I show how to perform basic operations inside of the Oracle Apex (4.1 deployed on AWS) interface.

Included in the videos:

  • How to create tables inside of Oracle APEX
  • How to create an application inside of Oracle APEX
  • How to create application pages inside of Oracle APEX
  • How to create lists of values inside of Oracle APEX
  • How to create reports inside of Oracle APEX

 

How to Deploy Metadata Dictionary in OBIEE

Standard

One of the frustrating things for users who build reports in Oracle Business Intelligence (OBI) is not knowing the data that could be available to them or where it comes from.

One way to help with this frustration is to deploy the metadata dictionary in OBI which allows you to drill down on any presentation layer field or table to the objects in the RPD that are related to that object even if they aren’t visible in the front end.

In the YouTube video below I show how to deploy and use the metadata dictionary in OBI 11g.

Sources of free map data

Standard

When implementing maps in Oracle Business Intelligence a lot of people may have the mistaken impression that they must purchase map data or use external map providers to create background maps or create themes for use with their business data.  In truth there are numerous free sources of shape files that can be used under open source licenses for your business.  These files are easily imported into your Oracle database using Oracle Map Builder.

Following is a list of free map data I have compiled and will continue to add to over time.

One fantastic source of United States shape files is the United States Census Bureau Tiger/Line Shapefiles.  They make available files from the state level down to the zip code as well as many features such as Landmarks and Roads.

Canada also has a Census Program and provides many files that can accessed via the Statistics Canada Geography page.

Another immense source of freely available and usable shape files is Natural Earth which is a “collaboration involving many volunteer NACIS members and cartographers around the globe”.