SOUG: Tampa Oracle Day

Life has been busy and I haven't been to a meeting in a while. There's a good one coming up though. If you're in the area you should make plans. RSVP here.

Here are the rest of the details from the email blast:

I wanted to give everyone an early head's up that we have a whole day of Oracle info coming up next week on Thursday, November 8th. We will have the Tampa Oracle Day and Product Fair at Raymond James Stadium from 9am-4pm. Following a brief intermission, our November Suncoast Oracle Users Group meeting will begin at 6pm in our regular Rocky Point location.

The Oracle Day event will include separate tracks with 4 sessions each for Simplify IT, Social/Mobile/Cloud, and Customer Experience. There will also be a Solutions Pavilion/Demo Grounds, breakfast and lunch, and a couple of Keynotes. In one of the Keynotes, I'll be relating some of our experiences at Nielsen in Leveraging Exadata for Consolidation and Business Growth.

You can register for the Tampa Oracle Day by calling 800.820.5592 ext.10942 or online at : http://www.oracle.com/us/dm/06495-nafm12034228mpp041-oem-1844932.html.

One of the most recent additions to Oracle's ever growing line of Engineered systems is the Oracle Database Appliance. The ODA is a 2-node RAC cluster with 4TB of storage all buttoned up in a 2U chassis. They've combined this hardware with a slick web interface management layer that allows you to stand up a 2-node RAC cluster in less than an hour with just a few clicks of the mouse. For our SOUG meeting this month, we will have one of these boxes in hand for a live demo and further discussion of the technology.

The Thursday evening SOUG meeting begins with refreshments and an opportunity for networking with your peers from 6:00-6:30pm followed by a presentation from 6:30-8:30pm. The meeting will be held in the Knowledge Services facilities at:

3031 North Rocky Point Drive West, Suite 275
Tampa, FL 33607

Hope to see you there.

Going Green

One thing I believe everyone can agree on, it's going green, saving the planet.

Oracle Corporation believes that to be the case too.

Save the planet, dump your Teradata



See the press release here.

Customer Service

Recently, I received a message via LinkedIn from a Product Manager at Oracle.

I won't say who, just because I wouldn't want to get them into any trouble.

The email was fairly general, mostly asking if I had used their product and if I had any questions. Well, I hadn't used that particular product, but I did have questions as it falls into my DW/BI realm.

So I sent my questions.

Within a day or so, I had this long, very detailed description about the differences between the 2 products and why one would choose one over the other (both products were Oracle products). It also included customer examples (sans customer names of course) of who is choosing what and when.

I love these kinds of exchanges. I love having the ability to ask questions of people who manage products because you get real insight into how they are thinking.

I believe this speaks volumes for this individual specifically and Oracle in general. I know Oracle isn't perfect by any means, but what company of that size is? This person wanted to make sure that I was informed, reached out to me, and provided me with the answer to my questions.

I certainly wish there were more people like this (not just at Oracle). After having sat in on an Exadata PoC, I can appreciate it that much more since I never get to spend the money. So thank you anonymous Oracle employee, you set a good example for quite a few people.

Thomas Roach: Oracle's Latest Acquisition

In Dan Norris fashion, I am announcing the acquisition of Thomas Roach by Oracle Corporation.

I've had the please of knowing Mr. Roach since before moving to Tampa as he was the President of the local users group, Suncoast Oracle Users Group (SOUG). He's also helped me out numerous times in both an advisory role and...well, just getting stuff done for me.

A few weeks ago I got to help him by letting him rehearse his MAA presentation on me. That apparently sealed the deal for him. Oracle Corporation flirted with acquiring him late 2008, but didn't pull the cord.

I've been teasing him the last few weeks about anouncing him as the Tom Kyte successor, as he'll be working in the Public Sector group that Mr. Kyte worked for a few years back. He said he didn't want the "Rittman Effect" so I am playing nice.

Mr. Roach is a great guy and this position (whether Mr. Kyte's old job or not) is very well earned. Congratulations Mr. Roach. Perhaps you can help out Mr. Kyte with his small niche site...since you share the same name.

The "Database is a Bucket" Mentality

Front and center again...I just woke up from a nap, I'm grumpy, so I must write. Besides, I haven't had a good rant in quite some time.

Friend of mine asked me last week for some advice, specifically asking if there was a tool to convert Oracle SQL Syntax to the ANSI SQL syntax. (A quick search turned up this (it was the first result), if you're interested).

I had to ask why.

Client is switching to an open source database, i.e. "free." Oracle licensing is way too pricey.

I'm sure Oracle costs a lot of money, it's pretty darn good software. Quite possibly the best in the world especially in the database realm. I've written about the incredibly feature rich goodness that is the Oracle database here here...actually, just trust me. It's in my name.

Why is there even a comparison?

Could it be that everyone out there believes that the sole purpose of a database is to store data? That it can't do anything else? The storage and retrieval of data...that's all it does of course.

It's like saying the Democrats and Republicans are the same...at face value, perhaps, but the devil is in the details.

This, this "Bit Bucket" mentality is what is so incredibly frustrating.

I am no position to argue the differences between the various flavors of database, I lack the experience. But if I were using SQL Server, I would leverage the shit out of it's capabilities. If I were using MySQL, I would leverage the shit out of it's capabilities. If I were using Firebird, I would leverage the shit out of it's capabilities. Same goes for every single flavor out there. Get my point here?

The database is NOT a bit bucket!

Do I need to use more 4-letter words?

I know that Oracle is feature rich and that 99% percent of your code can live in the database...think APEX and PL/SQL. You could probably put ALL of your code inside the database if you wanted to put the javascript in BLOBs as well.

Please, please please quit telling me they are the same...they are not.

Follow up rant by Mr. O'Neill can be found on this following post Everything is a Bit Bucket

Connect to HP Neoview using Heterogenous Services

Being the lazy sort that I am and not wanting to pay for a SQL client, I decided to use my local Oracle instance to access a HP Neoview database. Please don't ask me any questions about it, because like much of the world, I don't know either.

Hat(s) off to Tak Tang. I've used his guide a number of times throughout the years. If he blogs, I can't find it, so if you know about it, please link him up. The first time I used it was back in aught (sp?) 5 to connect to a DB2 instance.

Setup
Database (source): Oracle 10gR2
OS: Windows Vista Ultimate running as a VirtualBox Guest on Ubuntu
Database (target): HP Neoview 2.4 (or something)
OS: Doesn't matter

Materials
HP Neoview driver which can be found here. (Thanks Christian)

Steps
Download the HP Neoview driver and install it. Since it is windows, just click away accepting all the defaults.

Next, configure an ODBC Datasource. I won't go into the gory details, but it's pretty easy. When you are done, you should see something like this:



Now comes the fun part.

In your <ORACLE_HOME>\hs\admin directory you should see the following:



Open up the inithsodbc.ora file and set the parameters as follows:

HS_FDS_CONNECT_INFO = NEOVIEW
HS_FDS_TRACE_LEVEL = 1

Save the file as initNEOVIEW.ora in the same directory.

Now traverse to <ORACLE_HOME>\network\admin and open up your listener.ora file. Add a SID_DESC so that your file now looks like this:

LISTENER=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=testing)
      (ORACLE_HOME=c:\oracle)
      (SID_NAME=testing))
    (SID_DESC=
      (SID_NAME=plsextproc)
      (ORACLE_HOME=c:\oracle)
      (PROGRAM=extproc))
    (SID_DESC=
      (SID_NAME=NEOVIEW)
      (ORACLE_HOME=c:\oracle)
      (PROGRAM=hsodbc)))

Reload or stop and start your listener.

Now open up your tnsnames.ora file and add an entry for NEOVIEW. It should look like this:

NEOVIEW =
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=
        (PROTOCOL=TCP)
        (HOST=localhost)
        (PORT=1521)
      )
    )
    (CONNECT_DATA=(SID=NEOVIEW))
    (HS=OK)
  )

The last step is to create a database link.

CREATE DATABASE LINK neoview
  CONNECT TO "username"
  IDENTIFIED BY "password"
  USING 'NEOVIEW';

You should be all set now. You can test it out by issuing a simple

SELECT * FROM dual

If that works, you're done.

If you have problems, check out Tak's Troubleshooting section.

Database Security?

I'm not sure what are the question marks are lately...I can't think of better titles so perhaps it's me guessing at the title.

I'm sadly dependent on SQL*Plus...and when I mean "sadly" I mean I go to great lengths to get the Windows version which was obsolesced (sp?) in 11gR1. I could never get it quite right, the output I mean, but it was just so easy. Type in your SELECT statement, hit Enter and go. Easy. Yes, yes, I know, you can use the shell version too...I just liked the Windows version despite all it's quirks.

Anyway, as part of my login.sql script, I have the following 2 queries:

prompt ***roles granted***
SELECT role FROM session_roles;
prompt
prompt ***system privileges granted***
SELECT privilege 
FROM user_sys_privs
UNION
SELECT privilege
FROM role_sys_privs
WHERE role IN ( SELECT role FROM session_roles );

Each time I login or connect to a different database, this script runs. The results of those 2 queries, the first shows the roles I have been granted and the second query returns the system privileges I (or one of my granted roles) have. I'm sure I got this from Tom Kyte...I've been using this for years and I can't remember when or where I began to use it.

Having changed jobs quite a few times over the past few years, I've gotten to see many different environments. These scripts give me a quick glance at what I am able to do in a particular database, which is very helpful.

Where am I going? Not really sure.

I know that system level privileges are usually a no-no. CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, those are the basic items that you need to build objects in your own schema. CREATE ANY TABLE, CREATE ANY VIEW and CREATE ANY PROCEDURE will allow you create said objects in any schema (except SYS...I think, I don't have my power cable thus I can't start up my vm which contains my database).

Having read AskTom for so many years, I know or understand that system level privileges are bad, very bad.

At one former job, in the production database, not only did I have CREATE ANY TRIGGER, I had EXECUTE ANY PROCEDURE. I won't go into why or how those are very, very bad, you can research those yourself. Or you can just buy David Litchfield's The Oracle Hacker's Handbook: Hacking and Defending Oracle.

Pointing those types of things out though can be tricky...to put it mildly. Lots of politics involved. Trust me on this.

Hopefully you can glean a point or at least some information from this post. My mind is not all there due to the massive brain infection I have.

EBS Install Guide - Part 2

By John Piwowar [ blog | twitter ]
This is the second post in my "Installing E-Business Suite" series, where we actually install the software. Hopefully by now you've read part 1, downloaded the software, and configured your Linux server. Thanks again to Chet for the opportunity to share on his blog; I'm enjoying this guest spot on ORACLENERD Inc's New Media Empire (2.0!). And now, welcome to screenshot village; we hope you enjoy your stay.

Part 1 --> Part 2 --> Part 3 --> Part 4

The Oracle Applications Rapid Install Wizard, more commonly called RapidWiz, drives the EBS installation process. If you're accustomed to using the Oracle Universal Installer, you'll find that RapidWiz "feels" a bit different, but it still has that comforting grey, "Hi, I'm a Java-based Oracle software installer!" look to it.

Step 1: Launching RapidWiz
Log in to your Linux server's desktop as root, not oracle. Open a terminal window and change to the startCD/Disk1/rapidwiz subdirectory of the software staging area

Run the rapidwiz utility in that directory:

[root@londo R12stage]# cd startCD/Disk1/rapidwiz/
[root@londo rapidwiz]# ./rapidwiz

You should see a few launch messages in your terminal window, and the welcome screen of the installer will appear. Click Next:



Step 2: Select installation type
In the Wizard Operations window, make sure that "Install Oracle Applications" is selected, but do not check the Express Install box. Click Next:



Step 3: OCM configuration
In the Oracle Configuration Manager window, leave all the fields and options blank, and click Next. OCM is not a complication we need right now. :-)



Step 4: Configuration Choice
Since this is a new install, select the first option ("Create a new configuration") in the Configuration Options window and click Next.



Step 5: Port pool assignment
Select a number between 0 and 99 from the port pool list in the Global System Settings window, then click Next. Doesn't matter which number; choose your favorite. In past releases I've had issues where selecting 0, 1, or 99 has caused strange problems later, but those were pretty obscure and not consistently repeatable, so don't let that stop you from living on the edge. ;-)



Step 6: Database node configuration
Now we're finally getting to the fun bits! In the Database Node Configuration window, verify that the following fields have correct values:

• Database type is "Vision Demo Database"
• Hostname and domain name match the host and domain names on your Linux server. If you've configured this correctly in /etc/hosts and /etc/sysconfig/network, RapidWiz should pull the correct values.
• The Operating System value should match your server's platform (Linux x86 or Linux x86-64)

Change the following fields as necessary:

• Database SID: You can leave this at the default (VIS), or change it if you prefer.   
• Database OS user: The OS user you created in part 1 as the owner of the Oracle software (e.g. oravis)
• Database OS group: Change to oinstall
• Base Directory: This should be set to a directory in the Very Large Mount Point(tm) that you created in part 1. RapidWiz doesn't exactly conform to the OFA standard, but if you're picky about such things, you can click the "Edit Paths" button for finer control over where things go. I'm usually picky, but for the sake of saving you a few more screenshots, I'm letting RapidWiz decide where to file everything this time. :-)

Note: If your specified base directory doesn't exist, you'll need to create it. Um, whoops. Actually, I intentionally skipped that step just so you could get an example of what error conditions you might encounter during configuration. That's me, taking one for the team. It's just the sort of guy I am.



Make sure to change the ownership, and to grant write permissions to the oinstall group:

[root@londo rapidwiz]# cd /u01
[root@londo u01]# mkdir -m 775 -p ebs/R12VIS
[root@londo u01]# chown -R oravis:oinstall ebs
[root@londo u01]# ls -lR ebs

Click OK once you've created the base directory, make sure the installer has retained your other configuration settings, and click Next.

Step 7: Applications node configuration
The next window, "Primary Applications Node Configuration," takes most of its values from the database node configuration. You'll need to change the values of Apps OS user and Apps OS group to match the ones created in part 1 of this series. Everything else should be fine; you'll just need to verify. Again, if you want to tinker with specific locations in the installation, you can click the Edit Paths button, but that's beyond the scope of these instructions. Oh yeah, and click Next when you're done.



Step 8: Node information review
The Node Information window presents a review of the servers you've configured so far, and offers the option to add an additional server. Adding a server would be useful in the case of multi-node installations, but for now, just click (what else?) Next:



Step 9: Pre-install system checks
RapidWiz will pop up a window showing the system checks it is performing prior to installation. Basically, it's making sure that the necessary ports are open for the applications and database to run, that there's enough disk space to install, that it can write to all its directories, etc. After the checks are done, a Pre-Install Checks results window will appear. Hopefully you'll see all green checkmarks, as below. If you see red X's next to any of the test categories, click on the X to see a log from the test run, find and fix the errors, and click the Retry button to re-run the tests. Once all tests have been run successfully, click Next.





Step 10: The actual installation part
Click Next at the Install Review window, then Yes in the inevitable "are you sure you want to install?" box, and you're off to the races! Or maybe off to run some errands while blue bars crawl across the installer windows for a few hours, whichever. You'll notice that the terminal window from which you launched RapidWiz will display some information about where it's writing out log files. Hopefully we won't need those, because the installation will just go swimmingly, right?





Step 11: Post-install checks
After a while (probably a few hours, depending on how speedy your server is) the installation will complete, and post-install checks will begin. If some of the post-install checks are listed as failed initially, don't be too worried. The initial startup of E-Business Suite can pretty resource-intensive, and it's not unusual for some of the post-install checks to time out waiting for responses from web pages, or for application server components themselves to time out when starting up. Keep in mind that you're installing software intended to run on server-class hardware, and your server might be under spec somewhere (disk speed, CPU, memory).



Here's what my post-install checks looked like the first time around. Ouch.  As with the pre-install checks, you can click on the icons to see more about the failed checks. I'll spare you all 5 screenshots, but in this case, all the messages have the same theme: HTTP server taking too long to respond.



Step 12: A troubleshooting interlude
My poor 2-CPU "server" had a load average above 10 when the post-install checks first ran, and a ridiculous level of I/O wait (thanks, 5400RPM hard drive!), so the first thing I did was to wait until the system has settled down a bit, and then click the Retry button. Unfortunately, the outcome of the post-install checks didn't change, so I had to look into fixing the web server. What follows may not be applicable to your install situation, but it does provide a small taste of basic Oracle Applications troubleshooting, so please bear with me. If you get bored, or if your post-install checks were all successful, you can always skip ahead to step 14. ;-)

The first thing I did was to log in as the owner of the Oracle Applications software, applvis in this case. Then, I set up the OS environment to be able to better navigate the various sections of the Apps filesystem. You'll often hear this called "sourcing the Apps environment."

[root@londo ~]# su - applvis
[applvis@londo ~]$ . /u01/ebs/R12VIS/apps/apps_st/appl/R12VIS_londo.env

If you don't feel like digging through the documentation to find out where the Apps environment file should be for your system, you can find the value from the result of the "Environment File" post-install check. The output from that check lists a number of environment files; the one that's relevant here is the "APPL_TOP environment file." Using the newly-defined environment variables, I checked the status of the 10g Application Server components and tried to restart the web server:

[applvis@londo ~]$ $ADMIN_SCRIPTS_HOME/adopmnctl.sh status
You are running adopmnctl.sh version 120.6
Checking status of OPMN managed processes...
Processes in Instance: R12VIS_londo.londo.local
---------------------------------+--------------------+---------+---------
ias-component                    | process-type       |     pid | status
---------------------------------+--------------------+---------+---------
OC4JGroup:default_group          | OC4J:oafm          |   10677 | Alive
OC4JGroup:default_group          | OC4J:forms         |   12390 | Alive
OC4JGroup:default_group          | OC4J:oacore        |   10505 | Alive
HTTP_Server                  | HTTP_Server     |    N/A | Down    

adopmnctl.sh: exiting with status 0
adopmnctl.sh: check the logfile 
/u01/ebs/R12VIS/inst/apps/R12VIS_londo/logs/appl/admin/log/adopmnctl.txt for more information ...

This confirms that the web server is the only component that's currently failing, and the other app server components of the system are starting as expected.

[applvis@londo ~]$ $ADMIN_SCRIPTS_HOME adapcctl.sh start
You are running adapcctl.sh version 120.7.12010000.2
Starting OPMN managed Oracle HTTP Server (OHS) instance ...
opmnctl: opmn is already running.
opmnctl: starting opmn managed processes...
================================================================================
opmn id=londo.local:6255
0 of 1 processes started.
ias-instance id=R12VIS_londo.londo.local
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--------------------------------------------------------------------------------
ias-component/process-type/process-set:
HTTP_Server/HTTP_Server/HTTP_Server/
Error
--> Process (index=1,uid=1625958549,pid=26727)
failed to start a managed process after the maximum retry limit
Log:/u01/ebs/R12VIS/inst/apps/R12VIS_londo/logs/ora/10.1.3/opmn/HTTP_Server~1.log

adapcctl.sh: exiting with status 0adapcctl.sh: check the logfile 
/u01/ebs/R12VIS/inst/apps/R12VIS_londo/logs/appl/admin/log/adapcctl.txt for more information ...

That doesn't look so great, but at least I have a log file to review:

[applvis@londo ~]$ tail /u01/ebs/R12VIS/inst/apps/R12VIS_londo/logs/ora/10.1.3/opmn/HTTP_Server~1.log
--------
09/12/05 19:28:55 Start process
--------
/u01/ebs/R12VIS/inst/apps/R12VIS_londo/ora/10.1.3/Apache/Apache/bin/apachectl startssl: execing httpd
/u01/ebs/R12VIS/apps/tech_st/10.1.3/Apache/Apache/bin/httpd: 
error while loading shared libraries: 
libdb.so.2: cannot open shared object file: No such file or directory

That missing shared object library file is the most likely culprit. This often, but not always, means that there's a required OS package missing. In this case, a review of the installed packages on my server didn't show anything missing, so I turned to Google and My Oracle Support. I quickly found a handful of references in OTN forums and Oracle's non-EBS documentation to a quirk of installing R12.1 on 64-bit Oracle Enterprise Linux 5. One symlink later, and I was on my way:

[root@londo ~]# ln -s /usr/lib/libgdbm.so.2.0.0 /usr/lib/libdb.so.2
[applvis@londo ~]$ $ADMIN_SCRIPTS_HOME/adapcctl.sh start
You are running adapcctl.sh version 120.7.12010000.2
Starting OPMN managed Oracle HTTP Server (OHS) instance ...
opmnctl: opmn is already running.
opmnctl: starting opmn managed processes...
adapcctl.sh: exiting with status 0
adapcctl.sh: check the logfile 
/u01/ebs/R12VIS/inst/apps/R12VIS_londo/logs/appl/admin/log/adapcctl.txt for more information ...
[applvis@londo ~]$ $ADMIN_SCRIPTS_HOME/adopmnctl.sh status
You are running adopmnctl.sh version 120.6
Checking status of OPMN managed processes...Processes in Instance: R12VIS_londo.londo.local
---------------------------------+--------------------+---------+---------
ias-component                    | process-type       |     pid | status
---------------------------------+--------------------+---------+---------
OC4JGroup:default_group          | OC4J:oafm          |   10677 | Alive
OC4JGroup:default_group          | OC4J:forms         |   12390 | Alive
OC4JGroup:default_group          | OC4J:oacore        |   10505 | Alive
HTTP_Server                  | HTTP_Server     |  29299 | Alive  

After that, the post-install checks all came back green. Which is good, because I hadn't clicked Next in a while:



Step 14: Grand Finale
(what, you thought I'd risk the whole exercise by ending on step 13?)
And now, after hours of downloads and nigh-endless unzipping, the EBS install is done! That tempting "Connect to Oracle Applications Release 12.1.1" button may work, but if it doesn't, don't despair. Click Finish, open a web browser, and go the URL http://server.domain:80nn. The value for nn is the "port pool" value you selected in the early stages of the installation. Hopefully you know what "server" and "domain" are. :-) For example, when I browse to http://londo.local:8055, I am redirected to the main login page for EBS:





If you want to start looking around a little bit, the "superuser" for Oracle Applications is SYSADMIN, and the default password for the account is the same as the username. If you attempt to launch any of the Oracle Forms-based parts of the Apps interface from a browser on your Linux server, you may run into trouble. The EBS Forms interface is famously finicky on Linux-based browsers; I usually access EBS from browser on a Windows or OS X desktop, rather than from Linux, but the OTN forums are full of tips on how to get EBS Forms working on Linux. I'll try to dig up some links for the next post, or you can chime in with comments if you've already been down that road.

Thanks for following along! I hope that your installation experience is even more painless than this one. In part 3, I'll cover a few basic topics following the theme, "great, it's installed, now what do I do?"

References

• Oracle Applications Installation Guide: Using Rapid Install - The manual so nice, I've linked it twice!
• Oracle Applications Maintenance Procedures - Reading ahead for the next post in the series. Also covers some of the utilities I used above in Step 12.
  
• Oracle Applications Maintenance Utilities - Reading ahead for the next post. Also offers an explanation of the environment files briefly referenced in Step 12.
  
• An OTN forum thread that helped me resolve my little web server issue. Two lessons here: 1) OTN forums are immensely valuable, and 2) I should've read the documentation a bit more closely. D'oh.
• Not EBS-related, but I thought I'd plug my favorite lightweight screenshot annotation app for the Mac: Flysketch.

EBS Install Guide - Part 1

By John Piwowar [ blog | twitter ]
Chet, the oraclenerd, recently suggested that I write about installing Oracle E-Business Suite, aka Oracle Applications. He's also been encouraging me, in his low-key sort of way, to guest post on his blog. Here's the result. Thanks for the opportunity, Chet! Hope I don't drive too many of your readers away. ;-) Each post in this series will have a companion piece on my blog, Only Four Left, with additional details, discussion, and errata, in case you just can't get enough on this topic. Hey, it could happen.

Editor's Note
You can now get the entire series, including John's supplementary posts, in one convenient PDF file!   

Part I --> Part 2 --> Part 3 --> Part 4

Cheesy radio-commercial style intro
So you're a fan of tinkering with Oracle technology. You might even label yourself an oraclenerd. You have a few spare hours this weekend, and you're thinking about installing Oracle E-Business Suite to see what the fuss is all about, since people seem to talk about it a lot. But the installation and configuration guide is ridiculously, nap-inducingly long, and you can't even find the software on OTN. Where to start? Wonder no more! Start here!

This series of posts will cover a "Vision" install of Oracle Applications 12.1.1 on Linux. In this installment, I'll discuss requirements and acquiring the software. Subsequent posts will cover the installation of the software and actually using your shiny new E-Business Suite system.

Requirements

1. A Linux machine (physical or virtual).
2. At least 2GB of memory. 3GB would be better. 4GB would make me jealous. ;-) If your machine is on the low end of this list, don't expect to use it for anything else while EBS is running.
3. Lots of disk space. At least 300GB. If you want reasonable breathing room, set aside 350GB.
4. Access to My Oracle Support (formerly Metalink). I wish there was a way around this, but I'll be making references to a handful of My Oracle Support documents, and quoting extensively from those documents is a huge no-no. I'll do my best to make these instructions usable for those without access, but there may be bits that you'll miss.

Recommendations

1. Install on a version of Linux that is supported/certified for use with EBS. I'm going to be using Oracle Enterprise Linux 5, Update 4 (64-bit) for this install, but there are other options, listed in My Oracle Support Doc ID 761566.1: Oracle Applications Installation and Upgrade Notes Release 12 (12.1.1) for Linux x86-64 (32-bit Linux users can check out My Oracle Support Note 761564.1) It may be possible to install EBS on your favorite flavor of Linux, but that's not what this particular adventure is all about.
   
2. Consider installing EBS in a virtual machine, using VMware or Virtualbox. There are serious advantages to doing this, in my opinion, particularly if, like me, you'll be installing on your primary workstation.
3. Get familiar with some of the documentation while you're downloading the massive software distribution and building and/or patching your Linux environment to get up to spec. You'll find a reference list at the end of this post.

Preparing the OS
When laying out the disk space for your Linux server, allocate your preferred amount of space for swap and the operating system (I usually set aside about 10GB), 260-275GB for the /u01 partition (where we'll be installing the EBS software and database), and 50GB for the stage directory for the installer (I usually mount this to /mnt/R12stage). Needless to say, a production system's layout would be more refined/detailed, but if you're reading this guide as a roadmap to a production deployment, you need to stop immediately and have your head examined. ;-)

Install packages and set kernel parameters according to the appropriate section in the Release Notes referenced above. If you don't have access to My Oracle Support, you're in a bit of a tough spot, but here are some thoughts:

• Version R12.1 of EBS comprises Oracle RDBMS 11.1.0.7, Oracle Developer 10g, and two versions of Oracle Application Server 10g (ahem, excuse me, Fusion Middleware): 10.1.2.3 and 10.1.3.4. Presumably, combining the installation requirements for those products will get you most of the way to a full set of required packages and kernel parameters.
• Similarly, installing all the packages that come with your OS distribution should get you almost all of the installation requirements.
• The oracle-validated RPM seems to cover many, but not all, of the software and kernel parameter requirements for Oracle Enterprise Linux.
• One point that bears mentioning (and has been discussed pretty openly in the OTN forums, so it's not like I'm giving away any deep My Oracle Support secrets) is that an old version of openmotif libraries is required for this install. The name of the package varies by OS distribution, but the version number is what's important: it has to be version 2.1. If you go with the install-everything route, be sure to remove any competing openmotif packages.

Create two OS users, one to own the Oracle RDBMS software, and one to own the Apps software. Oracle recommends oraSID and applSID, respectively, where SID is all or part of the database SID that you designate during installation. For example, since we'll be doing a Vision install, it would be appropriate to create users oravis and applvis. It's a good idea to make sure that both users share the same primary group, usually the oinstall group. Also, create a dba group and assign it to the Oracle RDBMS software owner as an additional (not primary) group.

Acquiring and staging the software
E-Business Suite software can't be found on Oracle Technet (OTN). You can either purchase the media from the Oracle Store, or download it from Oracle's E-Delivery site. I'll assume you'll be downloading from E-Delivery. Two things worth noting here:

1. E-Delivery is decoupled from other Oracle web properties like My Oracle Support and OTN. You'll need to register with new credentials to download software here.
2. Software downloaded from E-Delivery is not covered under the OTN "Free to download, free to learn, unlimited evaluation" license. Instead, it's a thirty-day trial, after which you'll need to uninstall or purchase a license.

After logging in to E-Delivery, select "E-Business Suite" in the Product Pack dropdown, and select your desired installation platform (Linux x86 or Linux x86-64). Click Go to search, and click the "Oracle E-Business Suite Release 12.1.1 Media Pack" link to get to the downloads page. Don't pick the "Upgrade Media" link, unless you really intend to upgrade, in which case you really shouldn't be reading this. ;-)



You don't need to download all of the files listed on the page, but you're still going to be grabbing about 45GB. You might want to fix yourself a snack while you wait. The media is separated as if to be burned to DVD. For this exercise, however, we won't be burning DVDs. Be sure to grab all of the parts labelled:

• "Oracle E-Business Suite Release 12.1.1 for Linux x86-64 Rapid Install Start Here"
• "Rapid Install RDBMS - Disk 1" "Rapid Install Databases - Disk 1" through "Rapid Install Databases - Disk 9," inclusive
• "Oracle E-Business Suite Release 12.1.1 for Linux x86-64 Rapid Install Tools - Disk 1"
• "Oracle E-Business Suite Release 12.1.1 for Linux x86-64 Rapid Install APPL_TOP - Disk 1"
• "Oracle E-Business Suite Release 12.1.1 for Linux x86-64 Rapid Install APPL_TOP - Disk 2"

When I sent these instructions to Chet, he did the smart thing and created a spreadsheet to track his downloads. He's been kind enough to share it on Google Docs. Note that your part numbers may be different if you decide to go with 64-bit Linux instead of 32-bit.

Once you have all of the media, unzip the files into the stage directory on your Linux server. When the unzip utility asks if you want to replace dvd.label or component.label files, answer yes. If you get any other errors from unzip, you may have a corrupt download, and should re-download that zip file. Don't assume you can work around it. Once you've unpacked all of the files, your stage directory should look like this:

[root@londo R12stage]# ls
oraAppDB  oraApps  oraAS  oraDB  startCD

At this point, you'll be ready to start the installation (finally!). Hopefully, by the time you're done building your Linux server and downloading and staging the EBS software, the next installment in this series will be up. ;-)

Additional References

• A short OTN forum thread about laptop/workstation sizing for an EBS install
• Oracle Applications Release Notes, Release 12.1.1 (Doc ID 798258.1)
• Oracle E-Business Suite Documentation Library OTN-hosted documentation for Release 12.1
• Oracle Applications Installation Guide: Using Rapid Install Consider this your homework prior to the next post in this series.

Caveats, disclaimers, and assumptions

• This guide is intended as a roadmap for people who are self-directed enough to tinker if things go wrong. I'm presenting a series of steps that have worked for me, written up as I performed them, so it's all worked at least once. No, really. Honest.
• If you use this guide to deploy a system intended for production use, then your project timeline and maybe even your job are at risk. In other words: "No guarantees, warranties, express or implied, proceed at your own risk, blahblah fake legalese etc"
• There are individuals and organizations out there that provide thorough, professional-grade training on installing and maintaining Oracle Applications. Please do not make the mistake of equating their excellent, polished work with a handful of blog posts by some random dude on the Internet. :-)
• Similarly, part or all of the material presented here may have been covered on other blogs. Please share links to your favorite resources in the comments.
• This guide assumes some level of familiarity with Linux, or at least the ability to use Google to find instructions on installing, configuring, and working with Linux. Extensive Linux tutorials are beyond the scope of this guide; heaven knows I'm verbose enough as it is.

APEX: Install 3.2.1 on 11gR2

Yes, I know it comes pre-installed with 11g. No, I didn't bother to check what the default version was either. I needed practice so I removed it and re-installed it. This follows my recent install of 11gR2 on OEL v5.4.

First up, get the latest version from apex.oracle.com. I unzipped the file in a shared folder (which was then mounted to OEL) in the following directory (from OEL):

/mnt/software/oracle/apex/apex

In another window, I open up the installation doc.

From the database server, I moved to that directory and logged in as SYS. Here's the list of files in that directory:

[oracle@oracledb apex]$ ls -l
apex_epg_config_core.sql
apex_epg_config.sql
apexins.sql
apexvalidate.sql
apxchpwd.sql
apxconf.sql
apxdbmig.sql
apxdevrm.sql
apxdvins.sql
apxe101.sql
apxe102.sql
apxe111.sql
apxldimg.sql
apxpatch.sql
apxrelod.sql
apxremov.sql
apxrtins.sql
apxsqler.sql
apxxemig.sql
apxxepwd.sql
catapx.sql
coreins.sql
devins.sql
endins.sql
load_trans.sql

Since it's already installed, I want to remove it. To do so, I need to run apxremov.sql. I run it and a few minutes later its gone. I confirm this by looking in the DBA_REGISTRY view:

SELECT comp_name, version
FROM dba_registry
ORDER BY comp_name;

There are 4 basic installation scenarios:

1. Downloading from OTN and configuring the embedded PL/SQL gateway (11g)
2. Downloading from OTN and configuring Oracle HTTP Server (9.2.0.3 -- 11gR2)
3. Installing from the database and configuring the embedded PL/SQL gateway (11g)
4. Installing from the database and configuring the Oracle HTTP server (11g)

I'm going with #1.

I've removed the default installation of APEX, now I'm going to install it. I'm still logged in to SYS so I just need to run the apexins.sql script.

SQL>@apexins.sql SYSAUX SYSAUX temp /i/

The first parameter SYSAUX is the tablespace where APEX user will ive. The second SYSAUX is where the APEX files will live. TEMP is the name of the temporary tablespace and /i/ is the image (virtual) directory. Installation on my standalone machine took about 30 minutes.

The installation script logs you out at the end, so for the next step, you have to login as SYS account. Now we'll be setting the ADMIN account password. This password will be used here:



or here if you use INTERNAL as the workspace name (thanks John!):



Script to change the ADMIN password: apxchpwd.sql

Since this is technically an upgrade, I need to run apxldimg.sql

SQL> @apxldimg /mnt/software/oracle/apex

PL/SQL procedure successfully completed.

Directory created.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Commit complete.
timing for: Load Images
Elapsed: 00:02:33.48

Directory dropped.

Verify that the ANONYMOUS account is unlocked, if it isn't unlock it.

ALTER USER ANONYMOUS ACCOUNT UNLOCK;

We need to verify the EPG port now:

SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;

If a 0 (zero) is returned, EPG is not set-up yet. I'm going to use port 8080 because I'm too lazy to figure out, for now, how to set it up on port 80.

EXEC dbms_epg.sethttpport( 8080 );

Finally we have to enable network services. Since this is my own sandbox, security is not a concern. I'm going with the default as provided by the documentation:

DECLARE
  ACL_PATH  VARCHAR2(4000);
  ACL_ID    RAW(16);
BEGIN
  SELECT ACL 
  INTO ACL_PATH 
  FROM DBA_NETWORK_ACLS
  WHERE HOST = '*' 
    AND LOWER_PORT IS NULL 
    AND UPPER_PORT IS NULL;

  SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) 
  INTO ACL_ID
  FROM 
    XDB.XDB$ACL A, 
    PATH_VIEW P
  WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) 
    AND EQUALS_PATH(P.RES, ACL_PATH) = 1;

  DBMS_XDBZ.ValidateACL(ACL_ID);
  IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_030200', 
     'connect') IS NULL 
  THEN 
    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH, 
    'APEX_030200', TRUE, 'connect'); 
  END IF;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL
      ( 'power_users.xml',
        'ACL that lets power users to connect to everywhere',
        'APEX_030200', 
        TRUE, 
        'connect' );
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

You should be ready to go. The url will be http://localhost:8080/apex/apex_admin

If you are prompted with a XDB login window like this:



You may have forgotten to unlock the ANONYMOUS account. (I did, naturally).

DBMS_UTILITY.WAIT_ON_PENDING_DML

I've been reading up on Edition-Based Redefinition (which I can't link to because the docs seem to be missing right now).

In my readings I ran across DBMS_UTILITY.WAIT_ON_PENDING_DML which appears to be new to 11gR2 (there is no entry for it in the 11gR1 docs, which again, I can't link up currently). Based on my reading (I have the docs locally), this function appears to be used specifically for the new editioning feature.

It's listed as a procedure in the docs (don't ask for the link):



and a function if you do a describe on dbms_utility.

This procedure waits until all transactions (other than the caller's own) that have locks on the listed tables and began prior to the specified SCN have either committed or been rolled back.

So I wanted to see how it works.

First, I create the table:

CREATE TABLE s
(
  x NUMBER
);

I then open up a second session. In that session, I will run the WAIT_ON_PENDING_DML function which should monitor session 1.

Session 1:

BEGIN
  FOR i IN 1..100 LOOP
    INSERT INTO s ( x ) 
    VALUES ( DBMS_RANDOM.VALUE );
    dbms_lock.sleep( .5 );
  END LOOP;
  COMMIT;
END;
/

I'm inserting 100 records with a wait of half a second between inserts. This should take a little over 50 seconds.

Session 2

Note I've set the timeout to 30 seconds so that it will finish prior to Session 1

DECLARE
  l_bool BOOLEAN;
  l_scn NUMBER;
BEGIN
  l_bool := dbms_utility.wait_on_pending_dml
              ( tables => 'cjustice.s',
                timeout => 30,
                scn => l_scn );

  dbms_output.put_line( 'scn:  ' || l_scn );
  IF l_bool THEN
    dbms_output.put_line( 'true' );
  ELSE
    dbms_output.put_line( 'false' );
  END IF;
END;
/

scn:  924643
false

PL/SQL procedure successfully completed.

Elapsed: 00:00:30.03

The return value of "false" means that Session 1 is still inserting, which I confirmed visually (I should really create a video for this). Once Session 1 completed, I reran Session 2's anonymous block and received these results:

CJUSTICE@TESTING>/
scn:  924773
true

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

In regards to editioning, you would poll the database using this utility to see if there is, well, any pending DML. One aspect of Editioning allows you to create temporary triggers that will help you to migrate your application to the latest version without worrying about locking or timeouts. Pretty cool stuff.

I'll have more Editioning soon.

11gR2: Installfest

I installed Oracle Enterprise Linux 5.4 a few weeks ago in a VirtualBox virtual machine...I just didn't have the inclination to install the database.

Way back in June, I installed VirtualBox, OEL (5.3), 11gR1 and ApEx. To do that, I followed this guide on OTN by John Smiley (thanks John!). This time, I was going to try to do it from the docs. I can't say it was easy to read, so I was back at John's article before to long.

First up was preparing OEL for the database install. This included setting up kernel parameters and loading some extra libraries. There were slight differences between OEL 5.3 and 5.4, but I had the docs handy...and the installer complained loudly if something was amiss.

Next up, mount a shared folder in the virtual machine so that I could keep it to a relatively small size, 15 GB to be exact. Plus, I wanted to use the shared folders functionality. So I created a directory in the /mnt directory called software (as root of course), then mounted the shared folder.

root@oracledb#mkdir software
root@oracledb#mount -t vboxsf software /mnt/software

"software" was the name I gave the shared folder through the VirtualBox interface, that mapped to /opt/software on my host machine (ubuntu 9.04).

Then I downloaded the 11gR2 software, 2 files, linux_11gR2_database_1of2.zip and linux_11gR2_database_2of2.zip. I then used Archive Manager to extract the files. Strangely, it created 2 folders, "database" and "database(2)." That can't be right.

Back to OEL and I cd'd to the directory, /mnt/software/11gR2/database and ran the installer.



I did not provide my email address and I unchecked the box that would send me updates from Oracle Support.



Selected Yes to continue.



I'm just accepting the defaults here.



Not really sure what the difference is between a Desktop Class and Server Class install, so I again accepted the default (Desktop).



Now I have to fill in 4 fields:
* OSDBA Group (drop down)
* Global database name
* Administrative password
* Confirm password



* OSDBA Group = oracle (for me)
* Global database name = testing
* Administrative password = testing, whoops, strong password rules, not testing
* Confirm password = same as above



I chose "oracle" as the oraInventory Group Name.



Some prerequisite checks, to make sure I have the right libraries and the kernel parameters are OK.



This is when I realized the values were different between 11gR1 and 11gR2 and went back to the docs to get the correct ones. I realized afterwards though, that if you select the particular failure, it will tell you what the values should be. Nice.

After I went back and changed the values, I only had a failure on the memory check, but I checked the "Ignore All" checkbox and proceeded forward.



(I missed a screen shot the second go-around, so ignore the OS Kernel Parameters and Package errors.



Confirmation screen.



And were off!



I like to take a lot of pictures.



Creating the database...



Screen behind the screen.



Almost done.



Post-installation tasks.



Running /u01/app/oraInventory/orainstRoot.sh



Running /u01/app/oracle/product/11.2.0/db_home_1/root.sh



Done.



And done.



I can login from the virtual machine...



And I can login from the host machine.



Enterprise Manager from the host machine.



And my new account from the host machine.

Easy right?

Special thanks to Tim Hall as well, he's got a great primer for installing 11gR2, Oracle Database 11g Release 2 (11.2) Installation On Enterprise Linux 5.0.

Almost forgot, my problem with Archive Manager creating "database" and "database(2)"? Solved by twitter naturally:





So I unzipped the 2 files from a terminal window and it (apparently) put everything in the same folder. Win.