Installing a SSD for Faster GIS

Running ESRI software is inevitable as I dive deeper into Geographic Information Systems. If I decide to get a Masters in GIS from the Penn State World Campus, then running ESRI ArcGIS is mandatory. That is a problem because my house is “Windows-free” and all ESRI software is Windows-only.

I did some searching and found that running ArcGIS on a Windows VM is not uncommon. The only gotcha I discovered is that you must turn off 3-D acceleration. I also learned that if I hold out long enough it’s rumored that a Mac version of ArcGIS may be coming.

To get the best possible performance with my existing computer I replaced my boot drive with a Samsung model 840 120 GB SSD for $99.99. Even though my computer is a bit long in the tooth, it should be able to run a Windows VM reasonably well, especially now that it has the SSD.

With the SSD Ubuntu boots incredibly fast! I shouldn’t have to replace my hardware for the foreseeable future, even when I do start running ArcGIS on a Windows VM.

Today was a good geek day.

ORA-65049: creation of local user or role is not allowed in CDB$ROOT

I really hate the response “RTFM” in the forums, but sadly it was appropriate in my case. Let me save you some grief when you start playing with 12c. If you do not need to use a CDB (container database) or PDB (pluggable database), then uncheck the “Create as Container database” check box during the installation.

If you leave that checked you will quickly learn that you can’t create a local user in the root container.

$ sqlplus sys as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Wed Jun 26 07:08:11 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> GRANT DBA, CONNECT, RESOURCE TO STEVE IDENTIFIED BY dbl-secret-pwd;
GRANT DBA, CONNECT, RESOURCE TO STEVE IDENTIFIED BY dbl-secret-pwd
*
ERROR at line 1:
ORA-65049: creation of local user or role is not allowed in CDB$ROOT

Installing Oracle Spatial and Graph 12c on OEL 6.4

Oracle Spatial and Graph 12c has Arrived

Jean Ihm posted on Google+ today that Oracle Spatial and Graph 12c is available. This post is an update to my post earlier this month How to Install Oracle Spatial and Graph (11g) on OEL 6.4. Bear in mind that the context of my blog is a home spatial learning lab written by a Java developer, not a DBA, so please take what I say with a grain of salt.

Oracle 12c Gotcha

The Oracle 12c installation has “Create as Container database” checked by default. Container databases and PDB (Pluggable Database) didn’t exist in 11g and are a real paradigm shift. Unless you mean to add that complexity to your installation, be sure to uncheck the box circled in red below on the fifth screen show.

Complete the Pre-install Steps for Oracle Spatial and Graph 12c

To start out I cloned an Oracle VirtualBox snapshot I took of OEL 6.4 (Oracle Enterprise Linux) just prior to installing Oracle 11g. I performed the prerequisite steps as documented my 11g install post:

1. Define the hostname.
2. Install the dependencies. I didn’t find a 12c pre-install RPM for OEL, so I used the 11g RPM: oracle-rdbms-server-11gR2-preinstall.
3. Change the secure Linux policy in /etc/selinux/config to permissive.

Install Oracle Spatial and Graph 12c

Download the two Linux zip files from the Oracle Spatial and Graph 12c page.

As root, create the directory shown below, and unzip both files into that directory.

# mkdir /home/OraDB12c/
$ cp [your path]/linuxamd64_12c_database_1of2.zip /home/OraDB12c/ 
$ cp [your path]/linuxamd64_12c_database_2of2.zip /home/OraDB12c/ 
$ cd /home/OraDB12c/ 
$ unzip linuxamd64_12c_database_1of2.zip
$ unzip linuxamd64_12c_database_2of2.zip

Add Permission for Oracle User to xhost

Next, to avoid a common display error during the Oracle 11g installation run the following command as root.

# xhost +SI:localuser:oracle 
  localuser:oracle being added to access control list

Edit /home/oracle/.bash_profile with your preferred editor and add the following environment variables (substituting your host name and other installation preferences).

# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_HOSTNAME=gps12c.localdomain 
ORACLE_UNQNAME=orcl 
ORACLE_BASE=/home/oracle/app/oracle
ORACLE_HOME=/home/oracle/app/oracle/product/12.1.0/dbhome_1
ORACLE_SID=orcl
export ORACLE_SID ORACLE_HOME ORACLE_BASE ORACLE_UNQNAME ORACLE_HOSTNAME
PATH=$ORACLE_HOME/bin:/usr/sbin:$PATH
export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; 
export CLASSPATH LD_LIBRARY_PATH

Switch to the oracle user and change to the OraDB12c directory to run the install.

# su - oracle
# cd /home/OraDB12c/database 
# ./runInstaller 

Since mine is a home machine I accepted the default values on most screens. I’ve included all the screenshots below.

Leave the first-page blank unless you have an Oracle Support account.

This page is different from 11g. I skipped the software updates since I had none to install.

This option will launch the database configuration assistant after the software is installed.

A desktop class is sufficient for a home spatial learning lab.

Be sure to deselect “Create as Container database” unless you need to manage PDB (Pluggable Databases). If you leave this op

Take the default inventory directory.

With the three pre-install steps noted above, my system passed all the prerequisite checks and skipped directly to the summary page.

Run the two scripts shown as root.

Go have a cup of coffee. The database configuration takes awhile. When it’s complete the following window will appear.

Test the Oracle Installation

Try connecting to the database while still signed in as the user oracle using the commands shown below. Run the test query shown to pull back the database SID.

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jun 25 22:01:38 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
orcl

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Wrapping Up

That is good enough for tonight. In my future posts, I’ll be using 12c instead of 11g, despite the fact that it will probably be a long while before we upgrade at work. I want access to the latest spatial features as I am learning.

Installing Oracle SQL Developer

This  short post covers the installation of Oracle SQL Developer and the IP tables change required on OEL 6.4 to open up port 1521 so SQL Developer can connect to the Oracle database.

The illustration below from my earlier post, Host to Install OEL 6.4 as a VirtualBox Guest, shows that I run my Oracle 11g Enterprise Edition spatial learning lab on VirtualBox, but my tools run on the host OS, Ubuntu:

Configure the Hosts File

Add the host name of the Oracle database server to /etc/hosts on the machine running SQL Developer.

Open Port 1521

SQL Developer needs to connect to the TNS listener on the Oracle database server over port 1521 (or some other configured port), so Linux needs to accept traffic on port 1521. Use Telnet to test whether the port is open on the database server. From the machine running SQL Developer issue the following command substituting your Oracle database server host name and listener port.

$ telnet gps11g 1521
Trying 192.168.1.39...
telnet: Unable to connect to remote host: No route to host

If telnet cannot get through on that port check the /etc/sysconfig/iptables file on the Oracle database server.  Confirm that port 1521 is open. I had to add the rule outlined in red. Please talk to your Systems Administrator about the risks of making this change. My house is behind a firewall appliance and port 1521 is blocked to the outside world, so I felt secure making this change.

If you make changes to /etc/sysconfig/iptables run the following command to restart iptables afterwards:

$ /etc/init.d/iptables restart
iptables: Flushing firewall rules:                         [  OK  ]
iptables: Setting chains to policy ACCEPT: filter          [  OK  ]
iptables: Unloading modules:                               [  OK  ]
iptables: Applying firewall rules:                         [  OK  ]

Download SQL Developer

Download SQL Developer from here: http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html. Pick the appropriate distribution for your system. For OEL 6.4 that is Other Platforms.

Install SQL Developer

The installation itself is very well documented here: https://forums.oracle.com/forums/thread.jspa?threadID=2302774. Follow the instructions found on that page to install SQL Developer.

The SQL Developer software requires Java. The link above includes a step where you have to add the Java installation location to a configuration file. I’ve had to do that in the past on other machines, but I did not have to do that on this machine. Since I’m a Java developer and need to use an older Sun distribution of Java for compatibility reasons I did a separate set-up step not mentioned before in my blog. That included added JAVA_HOME to my .bashrc file.

Add a Database Connection

After installing and launching SQL Developer click the green plus sign to add a new database connection:

Supply the information displayed when the TNS Listener was started.

Connect to the Oracle Database Server

As long as your database is started, your listener is listening to port 1521, and iptables allows traffic on port 1521 you should now be able to connect to the SPATIAL_LEARNING database.

In my next post, I will setup Oracle Map Viewer.

How to Install Oracle Spatial and Graph on OEL 6.4

In my previous post, How to Install OEL 6.4 as a VirtualBox Guest, we got Oracle Linux up and running on VirtualBox. In this post, we continue with the Oracle database. Installing Oracle on Linux can be frustrating if you don’t do all the necessary pre-installation steps, but with the right preparation, it’s a piece of cake.

Audience

Damn it, Jim, I’m a software engineer, not a systems administrator!

This post is for developers who want to learn about Oracle Spatial and Graph. It assumes that you are comfortable using Linux and that you are familiar with RDBMS software. Many of the steps in this post are done at a Linux command prompt. This post looks long because I included all the screenshots, but in reality ,there are not that many steps to perform.

Let’s Get Started

Oracle Spatial and Graph is built into Oracle Database Enterprise Edition. It is not part of the Standard Edition. At the time of this writing Oracle 12c has not been released, so we’ll be using 11g. I’ll do an addendum post when 12c is available. Most of what you need to know about installing Oracle 11g Enterprise Edition on Oracle Enterprise Linux 6.4 can be found at the following link. Take a moment to browse that article and search for some of the other installation instructions that are out there.

http://www.oracle-base.com/articles/11g/oracle-db-11gr2-installation-on-oracle-linux-6.php

The remained of this post shows the steps that worked for me following the post above and other web resources.

Preparing the OS for 11g

There are a number of steps necessary to prepare for the Oracle installation, including setting up the hosts file, installing dependency libraries, adding the oracle user and group, adding directories, defining environment variables and changing the SELINUX permission. Much of this work is handled by a pre-installation package released by Oracle. If you do all the prep work correctly the database installation is straightforward. I had a few false starts because of missing some of the prerequisites described below.

Host Name

Use the hostname command to check your hostname. If you want to change it, now is the time. Whether or not you change the name, make sure that the system hostname is correctly defined in /etc/hosts/ for the loopback test during the database installation.

Edit the /etc/sysconfig/network file as the root user if you want to switch the host name   Set the HOSTNAME property to the desired name.

NETWORKING=yes
HOSTNAME=gps11g.localdomain

Be sure that the hostname is defined in the /etc/hosts file. I’m using a static IP address, so that is what I put in the hosts file. If you are using DHCP then add the host name to the first two lines instead.

127.0.0.1    localhost localhost.localdomain
::1          localhost localhost.localdomain
192.168.1.39 gps11g gps11g.localdomain 

Installing Library Dependencies

There are numerous library dependencies that must be installed to support 11g on OEL 6.4. Oracle released an RPM package that takes care of these for you, as well as creates the oracle user and home directory. The RPM is documented here:

http://www.oracle.com/technetwork/articles/servers-storage-admin/ginnydbinstallonlinux6-1845247.html

Switch to the root account and run the command below to install the preinstall package.

# su - root
# yum install oracle-rdbms-server-11gR2-preinstall 
  ... 
  Complete!

When that completes, do a full update just for good measure.

# yum update 

Assign a password to the oracle user.

# passwd oracle 

Change the secure Linux policy in /etc/selinux/config to permissive. Do not change any another setting in the file. I emphasize the point because I carelessly changed the wrong property which caused a kernel panic after I rebooted.  

# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=permissive
# SELINUXTYPE= can take one of these two values:
#     targeted - Targeted processes are protected,
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted

Reboot the server after completing this change.

Install Oracle Enterprise Edition 11g

Download the two installation zip files for Oracle EE 11g: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-linx8664soft-100572.html.
As root, create the directory shown below, and unzip both files into that directory.

# mkdir /home/OraDB11g/
# cp [your path]/linux.x64_11gR2_database_1of2.zip /home/OraDB11g/ 
# cp [your path]/linux.x64_11gR2_database_2of2.zip /home/OraDB11g/ 
# cd /home/OraDB11g/ 
# unzip linux.x64_11gR2_database_1of2.zip 
# unzip linux.x64_11gR2_database_2of2.zip

Add Permission for Oracle User to xhost

Next, to avoid a common display error during the Oracle 11g installation run the following command as root.

# xhost +SI:localuser:oracle 
  localuser:oracle being added to access control list

Edit /home/oracle/.bash_profile with your preferred editor and add the following environment variables.

# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_HOSTNAME=gps11g.localdomain 
ORACLE_UNQNAME=orcl 
ORACLE_BASE=/home/oracle/app/oracle
ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1
ORACLE_SID=orcl
export ORACLE_SID ORACLE_HOME ORACLE_BASE ORACLE_UNQNAME ORACLE_HOSTNAME
PATH=$ORACLE_HOME/bin:/usr/sbin:$PATH
export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; 
export CLASSPATH LD_LIBRARY_PATH

Switch to the oracle user and change to the OraDB11g directory to run the install.

# su - oracle
# cd /home/OraDB11g/database 
# ./runInstaller 

Once the installer opens you will be presented with the screens below. Since we are building an instance just for learning you may simply accept the default values on most screens. Leave the first-page blank unless you have an Oracle Support account.

Select the option to create and configure a database.

Choose Desktop Class on the next page.

Provide an administrative password, and accept the default values in the other fields.

Use the default inventory location.

Despite having installed the prerequisite RPM provided by Oracle, you will still be warned of missing packages. I did a little digging, and in my case, it said I was missing libaio-0.3.105, but my system had libaio-0.3.107. I selected the “Ignore All” option at the top of the page and continued.

When the summary page appears, click Save Response File to keep a record of what is being installed on your system and then continue.

Now is a good time to go get some coffee. The installation will run for awhile.

If you picked the configure database option on the second page then next you will see the configuration assistant launch. This too will run for quite awhile.

When the configure assistant finishes you will see this page showing your database SID and other information.

The final step is to switch back to root and run the two scripts shown.

The final page provides the Enterprise Manager Database Control URL.

Test the Oracle Installation

Try connecting to the database while still signed in as the user oracle using the commands shown below. Run the test query shown to pull back the database SID.

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jun 1 10:30:44 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
orcl

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting Oracle

With no further changes, Oracle will not start automatically after a reboot. The example below shows how to start Oracle from SQLPLUS using the startup command. You can shutdown Oracle from SQLPLUS with the shutdown command.

# su - oracle

# sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jun 1 13:12:36 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  776646656 bytes
Fixed Size            2217384 bytes
Variable Size          482347608 bytes
Database Buffers      289406976 bytes
Redo Buffers            2674688 bytes
Database mounted.
Database opened.
SQL>

Automating Restart

Setting up the automatic restart of Oracle is beyond the scope of this post. Instructions can be found here:

http://docs.oracle.com/cd/E11882_01/server.112/e25494/restart.htm#BABGIGDB

What’s Next

In my next post, we’ll set-up SQL Developer and Oracle Map Viewer and test out some basic spatial SQL.

How to Install OEL 6.4 as a VitualBox Guest

Do you have much study time available at your job? Too often general research time is a luxury unless it is required for your current project. The best chance for me to expand my knowledge of spatial beyond what I’m already using at work is at home.

This weekend I set out to build a test platform at home using Oracle 11g. I already had a development machine running Linux Mint, but I decided that I would use OEL 6.4 (Oracle Enterprise Linux) instead to reduce the chance of OS incompatibilities with 11g. I re-imaged my computer with OEL but soon discovered that OEL is a better server OS than it is a desktop OS. For starters, my favorite web browser, Chrome, doesn’t currently work on OEL 6.4.

That made me switch to Ubuntu 12.04 as my base OS, with Oracle Enterprise Linux running as a guest under Oracle VirtualBox. Virtualization has the advantage of supporting  multiple database versions, which will come in handy when Oracle 12c is released. It also allows me to support other images as needed, like PostGIS running under Centos 6.4, as pictured below.

I created custom partitions for the Ubuntu installation, in part because my machine has two drives, a 160 GB boot drive and a 500 GB storage drive, and in part to be able to preserve and reuse my /home and /usr/local partitions from one Linux installation to the next.

I created the swap, /usr/local, and root partition on the boot drive.

Mount	Approximate Partition Size
/swap	8 GB
/usr/local	5 GB
/	147 GB

I allocated the entire second drive to /home because that is where VirtualBox stores virtual storage files. In other words, the OEL 6.4 image, and any other OS images that I create will be stored under the /home directory.

Mount	ApproximatePartition Size
/home	500 GB

VirtualBox is available as a free download from the Ubuntu Software Center as pictured here.

Launch VirtualBox and click New. VitualBox was smart enough to default the OS to Linux and the version to Oracle as soon as I typed “OEL” in the name.

Assign memory to the virtual machine. The minimum memory is 1 GB, but 2 GB is recommended. OEL 6.4 requires a minimum of 1 GB of memory. I gave the OEL Guest 2 GB of memory.

Create a new virtual hard disk file.

Accept the default file type.

Accept the default virtual storage option to dynamically grow the virtual disk file only as needed.

Oracle Database EE requires about 5 GB of storage. Since this database is just for learning purposes it will never house much data. I decided to give the OEL 6.4 guest image a maximum of 40 GB, allocated only as needed. That gives me plenty of physical disk space for multiple image snapshots, database versions, and other operating system images.

VirtualBox gives you a chance to review your settings before creating the guest image.

After creating the OEL guest I opened Network setting and changed the “Attached to” setting to Bridged Adapter. This allowed me to configure my  firewall’s DHCP server to assign a static IP address to OEL using its MAC address. At this point, I also edited the MAC address assigned to the guest to match one setup on my firewall during a previous install this weekend.

Bridged Network Connection

Here is an example of the setting on my firewall’s DHCP server to assign static IP 192.168.1.39 to MAC address 08:00:27:A8:CA:DC. The OEL NIC is setup as DHCP but always gets the same IP address.

Static IP Settings

All that remains is to mount the OEL 6.4 ISO images. The smaller image is the boot disk. The 3.5 GB image contains the installation source. Simply attach both ISO images to the IDE Controller. Ignore the black box that says “SATA Controller.” I didn’t mean to capture that in the screen shot.

Installing OEL 4.6

Start the VitualBox guest and the following screen will appear. Pick the first option.

Press TAB ENTER to Skip the media test.

The installer will detect the second mounted ISO image and the installation will continue.

On the next couple of screens, not shown, choose a language for the installation process and choose a keyboard type for the system.

Select the device type for the installation.

There are two important setting on the next page. Assign a hosts name for the virtual machine, and then click the Configure Network button to edit eth0. You’ll want to select Connect automatically.

Pick a time zone for your server.

Assign a password for root.

If you are using a newly created virtual storage file it is safe to select the first option to use all space.

Pick the installation type. I choose a desktop just because I thought it would be easier for a learning machine.

Okay, time for a cup of coffee while all the packages get installed.

I just happened to capture the message above about setting the SELinux policy. We’ll change that when we get to the database setup.

You successfully installed an OEL 6.4 guest on Oracle VirtualBox.

Install Guest Additions

You are not done yet, that is if you want the convenience of a shared clipboard, shared folders, and quality display settings. To get those things you need install VBox Additions. Start the OEL 6.4 guest and hover over the guest window’s toolbar to get the VirtualBox menu to appear at the top of the Ubuntu desktop. Move the mouse over the VirtualBox Devices menu and then go to the Install Guest Additions sub-menu. Follow the prompts and VirtualBox will mount a VBOX Additions ISO image on the OEL desktop.

Sign into your guest and switch to the root user. Before you can run the installation you need to install the following packages in OEL 6.4.

# yum update
# yum install kernel* dkms gcc
# reboot

Now you can double-click on the VirtualBox Additions CD-ROM image on the OEL desktop.

When it is done you should see output similar to this.

You can verify the Virtualbox Guest Additions by copying something to the clipboard in Ubuntu and pasting it in OEL. Likewise, if you option the VirtualBox guest settings  and define a shared folder the with the auto-mount option, then in OEL you should now find the shared folder mounted under the /shared/media directory.

In my next post, we’ll install Oracle Database Enterprise Edition on our OEL 6.4 guest.