Twitter Live Feed with Oracle Database As A Service and Business Intelligence Cloud Service



In this post I am going to show how you can very quickly get a Twitter live feed streaming into your Oracle Database as a Service (DBAAS) instance.  Although this example will work on any machine with an Oracle client and for any Oracle Database instance putting it on DBAAS makes it extremely easy to utilize our streaming Twitter data inside of other Oracle Cloud applications such as Business Intelligence Cloud Service (BICS).


  1. Thank you to geniuses at Tweepy for making it so I don’t have to deal with oauth or really anything related to the Twitter API.  I don’t know who you are but I love you! ♥
  2. Twitter so I never have to wonder what Kim or Kanye are thinking.
  3. My homey Przemyslaw Piotrowski (I don’t know him either but his examples sustain my laziness in coding) for writing a super helpful series called The Mastering Oracle+Python Series which I fully intend to finish someday.
  4. Who ever this dude is ( whose example made this easy.

The great thing about DBAAS is I actually have access to the underlying server so I can take advantage of the operating system, and languages like python, as well as the database.  Some might be offended by such interloping but I’m not one of them and as I said above there isn’t any requirement to do this on the database server I’m just taking advantage of a machine that has the cycles to spare.

I will assume that you already have a DBAAS instance up and running and are familiar with how to administer it and are also familiar with using tools like MobaXterm to connect to it.

At the time of this example our Database as a Service is running Oracle Linux 6.7 and of the database with all the bells and whistles.  I needed to install 5 things to make this work:

  1. plliblzma to make it so I can get the latest EPEL repository without having to deal with “expletive” Error: xz compression not available message


yum install pyliblzma-0.5.3-3.el6.x86_64.rpm

  1. Latest EPEL repository for this version of linux


yum install epel-release-6-8.noarch.rpm

  1. Then I can install python-pip with ease

yum install python-pip

  1. And then we can fetch Tweepy with equal ease

pip install tweepy

  1. And last but not least Oracle’s python library for interacting with the database

pip install cx_Oracle

Now that we have all the chunks make sure you can import the stuff you need with python and not get any errors.

[oracle@testdrive-01 ~]$ python
Python 2.6.6 (r266:84292, Jul 23 2015, 05:13:40)
[GCC 4.4.7 20120313 (Red Hat 4.4.7-16)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import tweepy
>>> import cx_Oracle

If you have issue with cx_Oracle you most likely just need to make sure the Oracle environment variables are set via bashrc or however you like to set them.

I created a table in my database to store the JSON document that is the API response


In order to use the Twitter API you will have to register your app with them here at which point they will give you the keys to make oauth work.

Then all we need to do is write (by which I mean copy paste and modify) a little python code and we are ready to rock:

#import libraries
from tweepy import Stream
from tweepy import OAuthHandler
from tweepy.streaming import StreamListener
import cx_Oracle
import datetime
import json

#connection string for database

#get me a connection
conn =cx_Oracle.connect(conn_str)

#turn on autocommit

#object for executing sql

#clob variable

#twitter api application keys
#consumer key, consumer secret, access token, access secret.
asecret='The Password'

#listen to the stream
class listener(StreamListener):

#get some
    def on_data(self, data):

            #barf response insto json object
            all_data = json.loads(data)

            #parse out tweet text, screenname and tweet id
            tweet = all_data["text"]
            if (all_data["user"]["screen_name"]) is not None:
                username = all_data["user"]["screen_name"]
                username = 'No User'
            tid = all_data["id"]

            #set clob variable to json doc
                #create sql string with bind for clob var

                sql_str="INSERT INTO EAT_MY_TWEET (ID,USERNAME,TWEET_JSON) Values("+str(tid)+",q'["+username.encode('utf-8').strip()+"]',:EATIT)" 

                #insert into database 

            except Exception: 

            #watch tweets go by in console 

            #in case you want to print response 
        except Exception: 
def on_error(self, status): 
     print status 
     print sql_str 

#log in to twitter api 
auth = OAuthHandler(ckey, csecret) 
auth.set_access_token(atoken, asecret)
#fire it up 
twitterStream = Stream(auth, listener()) 

#what to search for (not case sensitive) 
#comma separated for words use 
# for hashtag 
#phrases are words separated by spaces (like this comment) 
twitterS.filter(track=["ProveFilterWorks,Oracle,Vlamis,OBIEE,BICS,DVCS,Data Visualization Desktop"])

I named my file so to execute I just fire it up with nohup –

nohup python -u /home/oracle/>/home/oracle/stream.out 2>/home/oracle/stream.err &

and if I tail my stream.out file with tail -f stream.out I can watch the tweets go by as they are inserted into the database


now that I am inserting the json document that the twitter api sends back to me into the database


I can use Oracle Database 12c support for json to expose the document in my table as a view using the following SQL

  SELECT cast(TO_TIMESTAMP_TZ(REPLACE(upper(b.created_on),'+0000','-00:00'),'DY MON DD HH24:MI:SS TZH:TZM YYYY')  at Time zone 'CST' as date) CST_DATE,
cast(TO_TIMESTAMP_TZ(REPLACE(upper(b.created_on),'+0000','-00:00'),'DY MON DD HH24:MI:SS TZH:TZM YYYY') as date) UTC_DATE,
cast(to_char(cast(TO_TIMESTAMP_TZ(REPLACE(upper(b.created_on),'+0000','-00:00'),'DY MON DD HH24:MI:SS TZH:TZM YYYY') as date),'HH24') as number) UTC_HOUR,
cast(to_char(cast(TO_TIMESTAMP_TZ(REPLACE(upper(b.created_on),'+0000','-00:00'),'DY MON DD HH24:MI:SS TZH:TZM YYYY') as date),'MI') as number) UTC_MINUTE,
'$' columns(
    id varchar(50) path '$.id',
    created_on varchar2(100) path '$.created_at',
    screen_name varchar2(200) path '$."user".screen_name',
    location varchar2(250) path '$."user"."location"',
    followers_cnt number path '$."user".followers_count',
    friends_cnt  number path '$."user".friends_count',
    listed_cnt  number path '$."user".listed_count',
    favourites_cnt  number path '$."user".favourites_count',
    statuses_cnt  number path '$."user".statuses_count',
    retweet_cnt number path '$.retweet_count',
    favourite_cnt number path '$.favorite_count',
     url varchar2(250) path '$."user"."url"',
    profile_image_url  varchar2(500) path '$."user".profile_image_url',
    banner_image_url varchar2(500) path '$."user".profile_banner_url',
     hashtags varchar2(500) format json with wrapper path '$.entities.hashtags[*].text',
      tweet varchar2(250) path '$.text'
    -- nested path '$.entities.hashtags[*]' columns (ind_hashtag varchar2(30) path '$.text'    )
) b

Then I can analyze the real time twitter data I’m interested in using a simple sql statement


and while being able to hit real time twitter data with SQL is cool the real goal is being able to surface that data inside of our BI tools. Here we combine Answers and Visual Analyzer on a dashboard to show latest tweets and aggregate information over time utilizing Oracle Business Intelligence Cloud Service (BICS)



Maps in OBIEE Free and Easy – Part 2 – The Database


In the database geometry objects are stored in a special column type called SDO_GEOMETRY that is specifically for geometric data.  The database stores geometry objects, such as States, as an ordered set of points, which are then connected by line segments  by Map Viewer to render the shape.


The type, and its associated metadata views and functions, are provided by the MDSYS schema which is included with your database installation.  Although the SDO_GEOMETRY type is associated with the MDSYS schema it can be used by any database user to
create geometry columns in any schema.  The only requirement is that there must be a spatial index on the table that contains the geometry type and there must be an entry in the USER_SDO_GEOM_METADATA view that tells the database that a particular table and column contain geometric data in order for functions and rendering operations to work correctly.

The geometry type is analogous to an XML type in that it is stored as a column in a table but also has its own internal structure that can be addressed by various database functions provided specifically for it.


X/Y Grid

Using an example from the documentation, if we look at the polygon named cola_b rendered on an xy grid we can see that its definition should include the points (5,1, 8,1, 8,6, 5,7, 5,1).

If we look at the COLA_MARKETS table in the database and look at the row that contains cola_b we can see that  the SHAPE column (which is an SDO_GEOMETRY type) does indeed contain those points.  Checking to see we have an entry in the USER_SDO_GEOM_METADATA view and that we have a spatial index on the table we can create a map view of our COLA_MARKETS table right inside of SQL Developer.


COLA_MARKETS table in the database

Map based objects such as a US State are merely a more complex example of a polygon that have a reference to a projection system (SRID) for rendering the earth’s 3 dimensional surface on a 2 dimensional plane.  If we look at a stored geometry for Colorado we can see that the only difference between it and the simple polygon we created for cola_b above is a reference to an SRID code and the number of points (longitude and latitude in this case) needed to render it.

That covers the basics of what you need to know about geometry and the database for now.  The SDO_GEOMETRY column type is used to store geometry definitions in the database and the USER_SDO_GEOM_METADATA view gives the database the information it needs to create spatial indexes.

I’ll show you in another post that lines, points, circles, etc. can also be associated with and rendered on maps and it’s pretty easy to create and use these objects to add even more insight to your map visualizations.  Additionally, we’ll talk about several important and useful database procedures & functions that are provided for validating (and fixing if needed) your geometric data which is especially helpful when we import freely available shapefiles into our database to create our own maps for use inside of OBIEE.



Maps in OBIEE Free and Easy – Part 1 – The Basics


Many people mistakenly believe that implementing map views in Oracle Business Intelligence (OBI) is difficult, requires additional licensing on the Oracle database and requires them to pay for content from third party map providers.  Third party provided map data and Oracle Spatial and Graph (an additional option for the Enterprise Edition of the database) may offer significant value to your organization but neither of these options are necessary to create and use maps with OBI.  Country, state or province, county, city and even neighborhood level map data is freely available and easily found in a number of places.

Oracle Locator is included with all versions of the Oracle Database.  Oracle Map Viewer is included with OBI and the Map Builder and Map Editor tools included with Map Viewer make it possible (and dare I say easy) to import and customize maps.  Maps views are produced through an integration between the Oracle Database, Map Viewer and OBI but the hard part of the integration is already done for you so all that is really required to utilize the power of maps is an understanding of the components involved in the integration and how they interact with each other.

As with most things Oracle, what at first seems complicated is actually pretty simple once you understand what’s going on.  I can’t count the number of times when first learning something new with Oracle technology I have marveled at how over complicated it seems but after taking the time to understand it have been equally impressed with how simple they have made very complicated things.  Maps are like this.  At the heart of map views we are dynamically associating dollar or quantity type measures with geometric shapes and rendering those shapes using colors, styles and other visualizations in such a way as to enhance the meaning of the underlying data.   That’s a complicated task.  Thankfully all you really need to know about it is what geometric shapes (US States as an example) do you want to visualize and what attribute of that shape (State name for example) do you want to use to associate with your measures.  Everything else involved is just giving the various components involved what they need to do their part of the task.

MapViewer1Let’s take a look at a figure from the MapViewer documentation that helps to describe the architecture involved in producing maps and then we will discuss the important things to understand at each layer.  OBI is the “Client” application in our scenario so it passes map rendering requests to MapViewer which in turn interacts with the database to get the map definition information it needs to render a map and pass it back to OBI.

MapViewer (the “Middle Tier”) needs to know what map definition to use and how that definition is linked to OBI data.  This linkage is defined in the Map Administration page of OBI and ties a field (or fields) from a subject area to a map layer.  MapViewer is a J2EE application that comes pre-deployed with the included Weblogic instance that OBI is deployed on.  Although it is collocated with OBI it can also be deployed on a standalone server running Glassfish or Tomcat.

Oracle Database stores the geometric definition of shapes and what colors, line types and text styles to use when rendering them.  Additionally, various functions can be performed on geometric data such as distance calculations or merging several states into a territory.

So that gives us a basic understanding of the moving parts involved with creating Map views inside of OBI Answers.  OBI is a client application asking Oracle MapViewer to render an interactive map for it based on some business data.  Oracle MapViewer fetches the map definition and styles from the Oracle Database and uses that information to render a map that it passes back to OBI.


Deploying Oracle Business Intelligence 12c on AWS EC2 Instance


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


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;.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
    • sudo systemctl default
    • sudo rpm -ivh
    • 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.
    • ./bi_platform-
    • cd /u01/home/oracle/Oracle/Middleware/Oracle_Home/bi/bin
  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
        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
        • add lines –
          • username=YOUR_WEBLOGIC_USER
    • 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/ “
          • runuser -l $ORACLE -c “$ORACLE_HOME/bin/ “
      • 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.



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

        # oracle owner at your site.




        export ORACLE_HOME PATH


        case $1 in


        runuser -l $ORACLE -c “$ORACLE_HOME/bin/ ”

        touch /var/lock/subsys/dbora



        runuser -l $ORACLE -c “$ORACLE_HOME/bin/ ”

        rm -f /var/lock/subsys/dbora



        echo “usage: $0 {start|stop}”






    • 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


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 -d /home/apexuser/apexlistener
    • upload oracle-instantclient12.1-basic- into /home/apexuser/orclclient
    • upload oracle-instantclient12.1-sqlplus- into /home/apexuser/orclclient
    • unzip -d /home/apexuser/ORDS
  7. Install oracle client
    • su ec2-user
    • cd /home/apexuser/orclclient
    • rpm -ivh oracle-instantclient12.1-basic-
    • rpm -ivh oracle-instantclient12.1-sqlplus-
  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
        ‘/u01/app/oracle/oradata/orcl/pdbseed/users01.dbf’ SIZE 5242880
        AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
  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
  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


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


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;.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
    • unzip
  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/
    • sudo /u01/app/oracle/product/12.1.0/dbhome_1/
  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


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