Only Four Left...

Learn about R12 integration with OID and OAM for the price of a latte!

jpiwowar — Tue, 05 Mar 2013 06:33:22 +0000

How often do you hear, "I sat down to write a blog post, and ended up with an eBook?" Probably not every week. But that's just what one of the the Oracle EBS Team Leads at Pythian has done. My colleague Subhajit has just published an eBook that covers the steps required to integrate Oracle Applications Release 12.1 with Oracle Internet Directory 11.1.1.6 and Oracle Access Manager 11.1.2.

At over 300 pages, this is a comprehensive guide to integrating 3 large and increasingly important pieces of the Oracle Applications technology stack, complete with screenshots and blow-by-blow examples of an actual configuration and deployment. No hand-waving theoretical stuff here; you get to watch the sandbox environment spring to life as you read! I'm still in the "browse the steps and download the software" phase, myself, but so far everything is clear and easy to follow. Subhajit has also offered up some troubleshooting tips for common issues encountered during deployment.

If you're an Apps DBA who's trying to get up to speed on the new look of identity management for E-Business Suite environments, you owe it to yourself to check out this eBook. Think of it as buying Subhajit a coffee (or a beer) in return for saving you the work of researching all those pre-requisites and puzzling out the various dependencies by yourself. This represents a serious jump-start to any effort to update your knowledge of E-Business Suite application server integration from the old 10gAS stack to the new 11g Fusion Middleware components.

Subhajit also promises more fun stuff in the near future, so stay tuned to his corner of the Pythian blog to see what's coming down the line! If you've been limping along on a barely-supported version of 10g SSO and OID to for your E-Business Suite identity management, his promised upgrade guide is sure to be of interest.

Finally here: Oracle Instant Client 11gR2 for OS X

jpiwowar — Fri, 01 Feb 2013 05:22:48 +0000

See the blog post over at Oracle for details, and be sure to send Christopher Jones an email (via the address in his post) stating you're an active user of the client. As Christopher mentions in his post,

You can really help us justify resources for the OS X bundle if you email me letting me know you are a fan. Since Instant Client is a free download, it is tricky for us to know how widely it gets used.

Jump to OTN to download, but please remember to email Christopher as well, if you're interested in seeing the client for future releases.

Build an 11gR2 RAC cluster in VirtualBox in 1 Hour using OVM templates

jpiwowar — Thu, 27 Dec 2012 08:00:09 +0000

[I originally posted this over at the Pythian blog. If you're not following it, you should! Way more content, by far smarter people than lil ol' me.]

After reviewing my blog post about running EBS OVM templates in VirtualBox, two of my teammates suggested that I work on something with potentially broader appeal. Their basic message was, "This is really cool for us EBS nerds, but what about the Core DBAs?"

So how does "11gR2 RAC in an hour" sound? In this post, I'll demonstrate how to deploy the pre-built Oracle VM templates to create a two-node 11gR2 RAC cluster in Oracle VirtualBox.

Why do this?

There are already several high-quality "How to run RAC on your workstation" HOW-TO's out there, including the well-known RAC Attack (by Pythian's own Jeremy Schneider, and others) and Tim Hall's super-straightforward article on ORACLE-BASE. Does the internet really need another screenshot-heavy blog post about installing Oracle RAC? Maybe not, but I'm doing it anyway, because:

The OVM templates come with the software pre-installed/patched, and scripts that configure the networking, Grid Infrastructure, and database for you. Less fiddling around reduces the possibility of error, and you still have a RAC cluster at the end!
I claimed in my earlier blog post that it should be possible to convert other OVM templates, so it seemed like a good idea to actually test that claim.
I wanted an excuse to play around a bit more with the command-line interface to VirtualBox.

Some readers might point out that installing and configuring the software is a good way to learn how things work, and that breaking and fixing things along the way helps one learn even more. I actually agree with that sentiment in general, since I'm a "learn by ~~failing~~ doing" kind of guy. On the other hand, Oracle is selling a line of high-end products that are supposed to take all of the hard work out of configuring RAC, so why shouldn't we have a bit of fun?

Ingredients

You will need:

RAM. Lots of RAM. The OVM template docs specify 2GB *per RAC NODE*, and that is probably on the small side for any serious work. If you want to do anything else with your workstation while this is running, you will not be able to proceed without at least 6GB of RAM on the host machine. This is less resource-intensive than building your own OVM server, but it is not a lightweight endeavor.
80-100GB of disk space, depending on how you size your ASM disks
A recent version of VirtualBox. An old one might do, but I didn't test on an old version.
DNS service for the SCAN interface. You might be able to get away without it, but I can't guarantee that the Oracle-supplied cluster build scripts will work if you try to fake it. Tim Hall has a great post on a minimal DNS setup for SCAN, or you can use dnsmasq to convert your local hosts file into a DNS service. I opted for dnsmasq; it's pretty cool.
A Linux install ISO image (or physical CD, if you're into that sort of thing). I used Oracle Enterprise Linux 5, Update 6, but any relatively recent OEL or RHEL install image should do the job here.
An understanding of some basic Linux systems administration tasks
Familiarity with configuring storage and network options in Virtualbox

Important notes and thank-you's

Nothing you're about to read in this post is supported by anyone. Not me, not Pythian, and certainly not Oracle. If you're thinking about using the techniques described here for any sort of production or QA deployment, please stop and question your sanity. Then call a few colleagues over to your desk and ask them to question your sanity.

Please be mindful of your licensing and support status before working with these templates. Content from Oracle's Software Delivery Cloud is subject to a far more restrictive licensing than the more-familiar OTN development license. [Thanks to Don Seiler (@dtseiler) for reminding me of this.]

So far, this is just a proof-of-concept. I haven't done extensive work to validate the RAC cluster I built from these instructions. There may be resource limitations that I have not yet discovered in this system, or more artifacts specific to the Oracle VM template that could be removed. "Do not be too proud of this technological terror you've constructed."

As always, I'm "standing on the shoulders of giants" to make this post happen. Huge thanks to Tim Hall (aka ORACLE-BASE) for his concise how-to documents that served as a springboard for this project; to the creators of dnsmasq for the easy local DNS option; to the clever folks at Oracle who built the VM cluster deployment script; and to my Pythian teammates and a handful of Twitter followers for encouraging me to blog about this.

HOWTO: The short version

The basic steps are as follows, with details in the next section.

Set up your local DNS with IP addresses for both nodes in your future RAC cluster.
Download the "Oracle RAC 11.2.0.1.4 for x86_64 (64 bit) with Oracle Linux 5.5 " OVM templates from the Oracle Software Delivery Cloud, and unzip (and unzip again!) the files
Create a single VM in Virtualbox to be the first node in the RAC cluster
Convert the OVM disk image files to VDI format, and attach them to your VM
Boot the VM in rescue mode from a Linux install ISO, install a non-Xen version of the kernel, and make some config file adjustments
Clone the VM to create the second node of the RAC cluster
Create shared disks and attach to both VMs
Boot both VMs and run the cluster configuration script
Start playing with your new Virtualbox RAC cluster! (or watch your workstation swap itself to death, if you didn't heed my "lots of RAM" warning, above)

HOWTO: The long version

Set up DNS entries for your RAC cluster

Complete details on DNS setup are beyond the scope of this post; instead, I've provided external references above to point you in a good direction. Here are the IPs and hostnames that I will be using in my example deployment. I'm using two separate host-only networks (vboxnet0 and vboxnet1) for the public and private interfaces, and the subnets (192.168.56.x and 192.168.57.x) were chosen automatically for me by Virtualbox. I try to keep things simple.

#RAC stuff
#Pub
192.168.56.11 thing1.local.org thing1
192.168.56.12 thing2.local.org thing2
#Priv
192.168.57.11 thing1-priv.local.org thing1-priv
192.168.57.12 thing2-priv.local.org thing2-priv
#VIP
192.168.56.21 thing1-vip.local.org thing1-vip
192.168.56.22 thing2-vip.local.org thing2-vip
#SCAN
192.168.56.31 clu-scan.local.org clu-scan
192.168.56.32 clu-scan.local.org clu-scan
192.168.56.33 clu-scan.local.org clu-scan

Be sure to test that the new IPs resolve to the expected hostnames on your host machine. In particular, it's a good idea to check whether your SCAN IPs are round-robining:

zathras:OVMRACTempl jpiwowar$ nslookup clu-scan
Server:		127.0.0.1
Address:	127.0.0.1#53

Name:	clu-scan
Address: 192.168.56.31
Name:	clu-scan
Address: 192.168.56.32
Name:	clu-scan
Address: 192.168.56.33

zathras:OVMRACTempl jpiwowar$ dig clu-scan

; <<>> DiG 9.7.6-P1 <<>> clu-scan
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 43681
;; flags: qr aa rd ra; QUERY: 1, ANSWER: 3, AUTHORITY: 0, ADDITIONAL: 0

;; QUESTION SECTION:
;clu-scan.			IN	A

;; ANSWER SECTION:
clu-scan.		0	IN	A	192.168.56.32
clu-scan.		0	IN	A	192.168.56.33
clu-scan.		0	IN	A	192.168.56.31

;; Query time: 2 msec
;; SERVER: 127.0.0.1#53(127.0.0.1)
;; WHEN: Thu Dec 20 21:28:19 2012
;; MSG SIZE  rcvd: 74

zathras:OVMRACTempl jpiwowar$ dig clu-scan

; <<>> DiG 9.7.6-P1 <<>> clu-scan
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 15944
;; flags: qr aa rd ra; QUERY: 1, ANSWER: 3, AUTHORITY: 0, ADDITIONAL: 0

;; QUESTION SECTION:
;clu-scan.			IN	A

;; ANSWER SECTION:
clu-scan.		0	IN	A	192.168.56.33
clu-scan.		0	IN	A	192.168.56.31
clu-scan.		0	IN	A	192.168.56.32

;; Query time: 0 msec
;; SERVER: 127.0.0.1#53(127.0.0.1)
;; WHEN: Thu Dec 20 21:28:27 2012
;; MSG SIZE  rcvd: 74

Download the 11.2.0.1 11gR2 EL5.5 templates
Connect to Oracle's Software Delivery Cloud and download the files listed under Oracle VM Templates for Oracle RAC 11gR2 Media Pack for x86_64 (64 bit). You'll need the two files for "Oracle RAC 11.2.0.1.4 for x86_64 (64 bit) with Oracle Linux 5.5" (V25916-01.zip and V25917-01.zip). I also recommend clicking the "View Digest"" button near the top of the download page, and running md5sum on each of the downloaded zip files to make sure the checksums match that list.
Extract the templates
- Unzip the two files you just downloaded (V25916-01.zip and V25917-01.zip). You'll get two .tgz files, OVM_EL5U5_X86_64_11201RAC_PVM-1of2.tgz and OVM_EL5U5_X86_64_11201RAC_PVM-2of2.tgz
- Unpack the two zipped tar files (tar zxpf OVM_EL5U5_X86_64_11201RAC_PVM*.tgz). This will create a directory called OVM_EL5U5_X86_64_11201RAC_PVM, and that's where we'll be doing all of our work.

Convert the OVM disk images to VDI format

Open a command/terminal window and use the VBoxManage utility to convert the raw disk images (.img) in OVM_EL5U5_X86_64_11201RAC_PVM to .vdi files. This utility is installed with VirtualBox; you may need to find it first and add it to your path (location varies by host platform). Timings listed in the examples below are provided to set expectations for how long you'll need to wait for the conversion to complete.

Note: I'm running VirtualBox on OS X, and the installer dropped VBoxManage into /usr/bin for me, so it's already in my path. Presumably you'll find a similar situation in Linux. If you're on Windows, and haven't customized your install, you should be able to find VBoxManage.exe in Program Files/Oracle/VirtualBox.

zathras:OVMRACTempl jpiwowar$ mkdir OVM_EL5U5_X86_64_11201RAC_PVM/Thing1
zathras:OVMRACTempl jpiwowar$ time VBoxManage convertfromraw OVM_EL5U5_X86_64_11201RAC_PVM/System.img OVM_EL5U5_X86_64_11201RAC_PVM/Thing1/RacRoot.vdi
Converting from raw image file="OVM_EL5U5_X86_64_11201RAC_PVM/System.img" to file="OVM_EL5U5_X86_64_11201RAC_PVM/Thing1/RacRoot.vdi"...
Creating dynamic image with size 13316728320 bytes (12700MB)...

real	5m12.042s
user	0m6.336s
sys	0m12.783s
zathras:OVMRACTempl jpiwowar$ time VBoxManage convertfromraw OVM_EL5U5_X86_64_11201RAC_PVM/Oracle11201RAC_x86_64-xvdb.img OVM_EL5U5_X86_64_11201RAC_PVM/Thing1/RacORCL.vdi

Converting from raw image file="OVM_EL5U5_X86_64_11201RAC_PVM/Oracle11201RAC_x86_64-xvdb.img" to file="OVM_EL5U5_X86_64_11201RAC_PVM/Thing1/RacORCL.vdi"...
Creating dynamic image with size 17179869184 bytes (16384MB)...

real	9m1.932s
user	0m7.424s
sys	0m20.825s

Create a VM for the first node of your RAC cluster

The VM will need to be configured as follows:

OS: Oracle Linux (64-bit)
Three (3) NICs: The first two attached to separate Host-only networks (vboxnet0 and vboxnet1), and the third configured to use NAT.
One (1) CPU
2GB of RAM
Device boot order: CD-ROM, then Hard Disk
Storage: Attach the two .VDI files to the SATA controller (Root disk first), and attach the Linux install ISO to the virtual DVD drive

Rather than just present a screenshot of the configuration, here's a listing of the VBoxManage showvminfo command for my first VM (Thing1):

Name:            Thing1
Groups:          /
Guest OS:        Oracle (64 bit)
UUID:            f7108f32-190f-431e-8310-19179ce73909
Config file:     /Users/jpiwowar/VMs/OVMRACTempl/OVM_EL5U5_X86_64_11201RAC_PVM/Thing1/Thing1.vbox
Snapshot folder: /Users/jpiwowar/VMs/OVMRACTempl/OVM_EL5U5_X86_64_11201RAC_PVM/Thing1/Snapshots
Log folder:      /Users/jpiwowar/VMs/OVMRACTempl/OVM_EL5U5_X86_64_11201RAC_PVM/Thing1/Logs
Hardware UUID:   f7108f32-190f-431e-8310-19179ce73909
Memory size:     2048MB
Page Fusion:     off
VRAM size:       8MB
CPU exec cap:    100%
HPET:            off
Chipset:         piix3
Firmware:        BIOS
Number of CPUs:  1
Synthetic Cpu:   off
CPUID overrides: None
Boot menu mode:  message and menu
Boot Device (1): DVD
Boot Device (2): HardDisk
Boot Device (3): Not Assigned
Boot Device (4): Not Assigned
ACPI:            on
IOAPIC:          on
PAE:             on
Time offset:     0ms
RTC:             local time
Hardw. virt.ext: on
Hardw. virt.ext exclusive: off
Nested Paging:   on
Large Pages:     on
VT-x VPID:       on
State:           powered off (since 2012-12-19T07:57:37.202000000)
Monitor count:   1
3D Acceleration: off
2D Video Acceleration: off
Teleporter Enabled: off
Teleporter Port: 0
Teleporter Address: 
Teleporter Password: 
Tracing Enabled: off
Allow Tracing to Access VM: off
Tracing Configuration: 
Autostart Enabled: off
Autostart Delay: 0
Storage Controller Name (0):            IDE Controller
Storage Controller Type (0):            PIIX4
Storage Controller Instance Number (0): 0
Storage Controller Max Port Count (0):  2
Storage Controller Port Count (0):      2
Storage Controller Bootable (0):        on
Storage Controller Name (1):            SATA Controller
Storage Controller Type (1):            IntelAhci
Storage Controller Instance Number (1): 0
Storage Controller Max Port Count (1):  30
Storage Controller Port Count (1):      10
Storage Controller Bootable (1):        on
IDE (0, 0): /Users/jpiwowar/Downloads/Enterprise-R5-U6-Server-x86_64-dvd.iso (UUID: 43f3022e-fc22-44d0-bc86-8d82e3732d09)
SATA (0, 0): /Users/jpiwowar/VMs/OVMRACTempl/OVM_EL5U5_X86_64_11201RAC_PVM/Thing1/RacRoot.vdi (UUID: d3894bf3-aa74-4d61-b2f7-86b30f1a61db)
SATA (1, 0): /Users/jpiwowar/VMs/OVMRACTempl/OVM_EL5U5_X86_64_11201RAC_PVM/Thing1/RacORCL.vdi (UUID: 22565328-fe92-4467-b0a1-98d0bb71879d)
NIC 1:           MAC: 08002768FFC0, Attachment: Host-only Interface 'vboxnet0', Cable connected: on, Trace: off (file: none), Type: 82540EM, Reported speed: 0 Mbps, Boot priority: 0, Promisc Policy: deny, Bandwidth group: none
NIC 2:           MAC: 0800274C8D14, Attachment: Host-only Interface 'vboxnet1', Cable connected: on, Trace: off (file: none), Type: 82540EM, Reported speed: 0 Mbps, Boot priority: 0, Promisc Policy: deny, Bandwidth group: none
NIC 3:           MAC: 08002758D099, Attachment: NAT, Cable connected: on, Trace: off (file: none), Type: 82540EM, Reported speed: 0 Mbps, Boot priority: 0, Promisc Policy: deny, Bandwidth group: none
NIC 3 Settings:  MTU: 0, Socket (send: 64, receive: 64), TCP Window (send:64, receive: 64)
NIC 4:           disabled
NIC 5:           disabled
NIC 6:           disabled
NIC 7:           disabled
NIC 8:           disabled
Pointing Device: PS/2 Mouse
Keyboard Device: PS/2 Keyboard
UART 1:          disabled
UART 2:          disabled
LPT 1:           disabled

Boot the new VM (Thing1) in rescue mode from the install CD
Enter “linux rescue” at the the boot: prompt to enter rescue mode:

Select the keyboard and language preferences that suit you, and enable two network interfaces: eth0 and eth2 (for now, just select “use IPv4" and “DHCP” when configuring). There is no need to enable eth1, since only one of the host-only interface needs to be active for this exercise:

(repeat the steps above for the NAT interface, eth2)
After setting up the network interfaces, progress therough the menus (“Continue” and “OK” in my case) until you get to a linux prompt, and switch to the root volume as instructed: # chroot /mnt/sysimage
Optional step: start the sshd service and connect to the VM from your host via ssh, instead of performing the next few steps from the console of the VM. Use ‘ifconfig eth0' to find the IP address to use. (Note: the root password for both VMs is ‘ovsroot’)
# service sshd start

Update a few configuration files

The kernel modules that are loaded to support the Xen kernel are not going to work with the non-Xen kernel, so we need to update modprobe.conf to match our target kernel version:

[root@localhost ~]# vi /etc/modprobe.conf
"/etc/modprobe.conf" 3L, 77C written
[root@localhost ~]# cat /etc/modprobe.conf
alias eth0 e1000
alias scsi_hostadapter ata_piix
alias scsi_hostadapter ahci

Prevent the server from repeatedly trying to spawn a console on a non-existent OVM server:

[root@localhost ~]# perl -pi.orig -e 's/^(co)/#\1/' /etc/inittab
[root@localhost ~]# tail /etc/inittab
3:2345:respawn:/sbin/mingetty tty3
4:2345:respawn:/sbin/mingetty tty4
5:2345:respawn:/sbin/mingetty tty5
6:2345:respawn:/sbin/mingetty tty6

# Run xdm in runlevel 5
x:5:respawn:/etc/X11/prefdm -nodaemon

# Run a getty on the virtual console
#co:2345:respawn:/sbin/agetty xvc0 9600 vt100-nav

Remove the link to the init script that builds the VM template. We don't want that happening at boot time:

[root@localhost ~]# rm /etc/rc3.d/S99oraclevm-template
rm: remove symbolic link `/etc/rc3.d/S99oraclevm-template'? yes

Update /etc/fstab and the cluster configuration scripts with correct references to disk device names (including a few disks we have't configured yet; that's coming)

[root@localhost ~]# perl -pi.orig -e 's/xvd/sd/g' /etc/fstab
[root@localhost ~]# cd /u01/racovm
[root@localhost racovm]# perl -pi.orig -e 's/xvd/sd/g' params.ini netconfig.ini diskconfig.sh

Install a new kernel and modify grub.conf

This VM is configured with a Xen version of the Oracle Linux 5.5 kernel, so we need to grab a "vanilla" version of that kernel. We'll use the Oracle public yum server to accomplish this; that’s why we’ve configured and activated the NAT interface. Since you've set up your host to act as a DNS server already, you should not need to add a nameserver entry to resolv.conf. In my case, the VM was able to resolve the address for public-yum.oracle.com without any further configuration changes. If you have issues, try replacing the "nameserver" line in /etc/resolv.conf with "nameserver 8.8.8.8"

[root@localhost ~]#  cd /etc/yum.repos.d/
[root@localhost yum.repos.d]# cat /etc/resolv.conf
nameserver 10.0.4.2 --This worked for me, if it doesn't for you, try 8.8.8.8
[root@localhost yum.repos.d]# wget http://public-yum.oracle.com/public-yum-el5.repo
--2012-12-18 15:59:56--  http://public-yum.oracle.com/public-yum-el5.repo
Resolving public-yum.oracle.com... 141.146.44.34
Connecting to public-yum.oracle.com|141.146.44.34|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 3974 (3.9K) 1
Saving to: `public-yum-el5.repo'

100%[==========================================================================================>] 3,974       --.-K/s   in 0s

Update the public-yum-el5.repo file and set enabled=0 for every source except ol5_u6_base:

[root@localhost yum.repos.d]# vi public-yum-el5.repo 
"public-yum-el5.repo" 111L, 3974C written
[root@localhost yum.repos.d]# grep -B5 'enabled=1' public-yum-el5.repo | grep ']'
[el5_u5_base]

Install the OEL5.5 kernel and kernel-devel packages from the Oracle puclic yum server. We'll need kernel-devel to install VirtualBox guest additions later

[root@localhost yum.repos.d]# yum install kernel-2.6.18-194.el5 kernel-devel-2.6.18-194.el5
Loaded plugins: security
el5_u5_base                                                                                                  | 1.1 kB     00:00    
el5_u5_base/primary                                                                                          | 1.1 MB     00:02    
el5_u5_base                                                                                                               4372/4372
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package kernel.x86_64 0:2.6.18-194.el5 set to be installed
---> Package kernel-devel.x86_64 0:2.6.18-194.el5 set to be installed
--> Finished Dependency Resolution

Dependencies Resolved

====================================================================================================================================
 Package                         Arch                      Version                             Repository                      Size
====================================================================================================================================
Installing:
 kernel                          x86_64                    2.6.18-194.el5                      el5_u5_base                     20 M
 kernel-devel                    x86_64                    2.6.18-194.el5                      el5_u5_base                    5.5 M

Transaction Summary
====================================================================================================================================
Install       2 Package(s)
Upgrade       0 Package(s)

Total download size: 25 M
Is this ok [y/N]: y
Downloading Packages:
(1/2): kernel-devel-2.6.18-194.el5.x86_64.rpm                                                                | 5.5 MB     00:20    
(2/2): kernel-2.6.18-194.el5.x86_64.rpm                                                                      |  20 MB     01:03    
------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                               266 kB/s |  25 MB     01:37    
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing     : kernel                                                                                                       1/2
  Installing     : kernel-devel                                                                                                 2/2

Installed:
  kernel.x86_64 0:2.6.18-194.el5                                kernel-devel.x86_64 0:2.6.18-194.el5                              

Complete!

Create an initrd for the new kernel; this should also add a new stanza to grub.conf:

[root@localhost yum.repos.d]# mkinitrd -v -f /boot/initrd-2.6.18-194.el5.img 2.6.18-194.el5
Creating initramfs
Looking for deps of module ehci-hcd
Looking for deps of module ohci-hcd
Looking for deps of module uhci-hcd
Looking for deps of module ext3: jbd
Looking for deps of module jbd
Found root device sda2 for LABEL=/
Looking for driver for device sda2
Looking for deps of module pci:v00008086d00002829sv00000000sd00000000bc01sc06i01: scsi_mod libata ahci scsi_mod libata ahci
Looking for deps of module scsi_mod
Looking for deps of module sd_mod: scsi_mod
Looking for deps of module libata: scsi_mod
Looking for deps of module ahci: scsi_mod libata
Looking for driver for device sda3
Looking for deps of module pci:v00008086d00002829sv00000000sd00000000bc01sc06i01: scsi_mod libata ahci scsi_mod libata ahci
Looking for deps of module ata_piix: scsi_mod libata
Looking for deps of module ide-disk
Looking for deps of module dm-mem-cache
Looking for deps of module dm-region_hash: dm-mod dm-log
Looking for deps of module dm-mod
Looking for deps of module dm-log: dm-mod
Looking for deps of module dm-message
Looking for deps of module dm-raid45: dm-message dm-mod dm-mem-cache dm-log dm-region_hash
Using modules:  /lib/modules/2.6.18-194.el5/kernel/drivers/usb/host/ehci-hcd.ko /lib/modules/2.6.18-194.el5/kernel/drivers/usb/host/ohci-hcd.ko /lib/modules/2.6.18-194.el5/kernel/drivers/usb/host/uhci-hcd.ko /lib/modules/2.6.18-194.el5/kernel/fs/jbd/jbd.ko /lib/modules/2.6.18-194.el5/kernel/fs/ext3/ext3.ko /lib/modules/2.6.18-194.el5/kernel/drivers/scsi/scsi_mod.ko /lib/modules/2.6.18-194.el5/kernel/drivers/scsi/sd_mod.ko /lib/modules/2.6.18-194.el5/kernel/drivers/ata/libata.ko /lib/modules/2.6.18-194.el5/kernel/drivers/ata/ahci.ko /lib/modules/2.6.18-194.el5/kernel/drivers/ata/ata_piix.ko /lib/modules/2.6.18-194.el5/kernel/drivers/md/dm-mem-cache.ko /lib/modules/2.6.18-194.el5/kernel/drivers/md/dm-mod.ko /lib/modules/2.6.18-194.el5/kernel/drivers/md/dm-log.ko /lib/modules/2.6.18-194.el5/kernel/drivers/md/dm-region_hash.ko /lib/modules/2.6.18-194.el5/kernel/drivers/md/dm-message.ko /lib/modules/2.6.18-194.el5/kernel/drivers/md/dm-raid45.ko
/sbin/nash -> /tmp/initrd.Tu1724/bin/nash
/sbin/insmod.static -> /tmp/initrd.Tu1724/bin/insmod
copy from `/lib/modules/2.6.18-194.el5/kernel/drivers/usb/host/ehci-hcd.ko' [elf64-x86-64] to `/tmp/initrd.Tu1724/lib/ehci-hcd.ko' [elf64-x86-64]
copy from `/lib/modules/2.6.18-194.el5/kernel/drivers/usb/host/ohci-hcd.ko' [elf64-x86-64] to `/tmp/initrd.Tu1724/lib/ohci-hcd.ko' [elf64-x86-64]
copy from `/lib/modules/2.6.18-194.el5/kernel/drivers/usb/host/uhci-hcd.ko' [elf64-x86-64] to `/tmp/initrd.Tu1724/lib/uhci-hcd.ko' [elf64-x86-64]
copy from `/lib/modules/2.6.18-194.el5/kernel/fs/jbd/jbd.ko' [elf64-x86-64] to `/tmp/initrd.Tu1724/lib/jbd.ko' [elf64-x86-64]
copy from `/lib/modules/2.6.18-194.el5/kernel/fs/ext3/ext3.ko' [elf64-x86-64] to `/tmp/initrd.Tu1724/lib/ext3.ko' [elf64-x86-64]
copy from `/lib/modules/2.6.18-194.el5/kernel/drivers/scsi/scsi_mod.ko' [elf64-x86-64] to `/tmp/initrd.Tu1724/lib/scsi_mod.ko' [elf64-x86-64]
copy from `/lib/modules/2.6.18-194.el5/kernel/drivers/scsi/sd_mod.ko' [elf64-x86-64] to `/tmp/initrd.Tu1724/lib/sd_mod.ko' [elf64-x86-64]
copy from `/lib/modules/2.6.18-194.el5/kernel/drivers/ata/libata.ko' [elf64-x86-64] to `/tmp/initrd.Tu1724/lib/libata.ko' [elf64-x86-64]
copy from `/lib/modules/2.6.18-194.el5/kernel/drivers/ata/ahci.ko' [elf64-x86-64] to `/tmp/initrd.Tu1724/lib/ahci.ko' [elf64-x86-64]
copy from `/lib/modules/2.6.18-194.el5/kernel/drivers/ata/ata_piix.ko' [elf64-x86-64] to `/tmp/initrd.Tu1724/lib/ata_piix.ko' [elf64-x86-64]
copy from `/lib/modules/2.6.18-194.el5/kernel/drivers/md/dm-mem-cache.ko' [elf64-x86-64] to `/tmp/initrd.Tu1724/lib/dm-mem-cache.ko' [elf64-x86-64]
copy from `/lib/modules/2.6.18-194.el5/kernel/drivers/md/dm-mod.ko' [elf64-x86-64] to `/tmp/initrd.Tu1724/lib/dm-mod.ko' [elf64-x86-64]
copy from `/lib/modules/2.6.18-194.el5/kernel/drivers/md/dm-log.ko' [elf64-x86-64] to `/tmp/initrd.Tu1724/lib/dm-log.ko' [elf64-x86-64]
copy from `/lib/modules/2.6.18-194.el5/kernel/drivers/md/dm-region_hash.ko' [elf64-x86-64] to `/tmp/initrd.Tu1724/lib/dm-region_hash.ko' [elf64-x86-64]
copy from `/lib/modules/2.6.18-194.el5/kernel/drivers/md/dm-message.ko' [elf64-x86-64] to `/tmp/initrd.Tu1724/lib/dm-message.ko' [elf64-x86-64]
copy from `/lib/modules/2.6.18-194.el5/kernel/drivers/md/dm-raid45.ko' [elf64-x86-64] to `/tmp/initrd.Tu1724/lib/dm-raid45.ko' [elf64-x86-64]
/sbin/dmraid.static -> /tmp/initrd.Tu1724/bin/dmraid
/sbin/kpartx.static -> /tmp/initrd.Tu1724/bin/kpartx
Adding module ehci-hcd
Adding module ohci-hcd
Adding module uhci-hcd
Adding module jbd
Adding module ext3
Adding module scsi_mod
Adding module sd_mod
Adding module libata
Adding module ahci
Adding module ata_piix
Adding module dm-mem-cache
Adding module dm-mod
Adding module dm-log
Adding module dm-region_hash
Adding module dm-message
Adding module dm-raid45
[root@localhost yum.repos.d]# head -30 /boot/grub/grub.conf
# grub.conf generated by anaconda
#
# Note that you do not have to rerun grub after making changes to this file
# NOTICE:  You have a /boot partition.  This means that
#          all kernel and initrd paths are relative to /boot/, eg.
#          root (hd0,0)
#          kernel /vmlinuz-version ro root=/dev/xvda2
#          initrd /initrd-version.img
#boot=/dev/xvda
timeout=9
splashimage=(hd0,0)/grub/splash.xpm.gz
hiddenmenu
title Enterprise Linux Enterprise Linux Server (2.6.18-194.el5)
     root (hd0,0)
     kernel /vmlinuz-2.6.18-194.el5 ro root=LABEL=/ numa=off
     initrd /initrd-2.6.18-194.el5.img
title Enterprise Linux Enterprise Linux Server (2.6.18-194.0.0.0.3.el5xen)
     root (hd0,0)
     kernel /vmlinuz-2.6.18-194.0.0.0.3.el5xen ro root=LABEL=/ numa=off
     initrd /initrd-2.6.18-194.0.0.0.3.el5xen.img

Finally, add "divider=10" to the boot parameters in grub.conf to improve VM performance. This is often recommended as a way to reduce host CPU utilization when a VM is idle, but it also improves overall guest performance. When I tried my first run-through of this process without this parameter enabled, the cluster configuration script bogged down terribly, and failed midway through creating the database.

[root@localhost yum.repos.d]# perl -pi.orig -e 's/(numa=off)/\1 divider=10/' /boot/grub/grub.conf
[root@localhost yum.repos.d]# head -30 /boot/grub/grub.conf
# grub.conf generated by anaconda
#
# Note that you do not have to rerun grub after making changes to this file
# NOTICE:  You have a /boot partition.  This means that
#          all kernel and initrd paths are relative to /boot/, eg.
#          root (hd0,0)
#          kernel /vmlinuz-version ro root=/dev/xvda2
#          initrd /initrd-version.img
#boot=/dev/xvda
timeout=9
splashimage=(hd0,0)/grub/splash.xpm.gz
hiddenmenu
title Enterprise Linux Enterprise Linux Server (2.6.18-194.el5)
     root (hd0,0)
     kernel /vmlinuz-2.6.18-194.el5 ro root=LABEL=/ numa=off divider=10
     initrd /initrd-2.6.18-194.el5.img
title Enterprise Linux Enterprise Linux Server (2.6.18-194.0.0.0.3.el5xen)
     root (hd0,0)
     kernel /vmlinuz-2.6.18-194.0.0.0.3.el5xen ro root=LABEL=/ numa=off divider=10
     initrd /initrd-2.6.18-194.0.0.0.3.el5xen.img

Reboot and install VirtualBox Guest Additions
This is another recommended item to improve performance in your running VMs.
- From the Devices menu, remove the Linux boot iso from the DVD drive
- Reboot the VM by typing 'exit' twice in the console
- Log in to the VM as root (password ovsroot)
- Select "Install Guest Additions" from the Devices menu
- Mount the Guest Additions media (attached to /dev/cdrom) from the console of the guest
- Execute the VBoxLinuxAdditions.run script (add the --nox11 option, since we're not using X at this point)
- After the Guest additions are installed (see screenshot below), shut down the guest (shutdown -h now)

Clone the first VM to a second, and add the shared disks

Big shout-out to Tim Hall here; apart from some minor variations, these are essentially his steps for adding shared disks to the cluster VMs. The primary difference is that we can use the clonevm functionality of VBoxManage, because we aren't attaching the (unformatted) shared disks until afterward. I've partially scripted these steps, to reduce the amount of copy/paste required.

Please note that these commands assume a Unix-like shell. If you're running Windows and using a normal command shell (instead of something like Cygwin), you are no doubt a) exceedingly brave, b) very smart, and c) able to translate these commands to fit your environment.

First, clone the "Thing1" VM to its partner, "Thing2":

zathras:OVMRACTempl jpiwowar$ BASE_DIR=OVM_EL5U5_X86_64_11201RAC_PVM #this needs to be the location of your VM files, as specified in earlier steps!
zathras:OVMRACTempl jpiwowar$ VMNAME1=Thing1
zathras:OVMRACTempl jpiwowar$ VMNAME2=Thing2
zathras:OVMRACTempl jpiwowar$ ASM_DISK_DIR=ASM  #the subdirectory under BASE_DIR where the shared disk files will be stored
zathras:OVMRACTempl jpiwowar$ ASM_DISK_MB=4096  #size of each ASM disk, in MB. 
zathras:OVMRACTempl jpiwowar$ ASM_DISK_CNT=5    #number of ASM disks, must be 5 to match the OVM template cluster build script
#Clone the VM...
zathras:OVMRACTempl jpiwowar$ time VBoxManage clonevm $VMNAME1 --name $VMNAME2 --register --basefolder $BASE_DIR
0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%
Machine has been successfully cloned as "Thing2"

real	10m19.323s
user	0m1.673s
sys	0m2.312s

Next, create and attach the shared disks:

zathras:OVMRACTempl jpiwowar$ mkdir ${BASE_DIR}/${ASM_DISK_DIR}
zathras:OVMRACTempl jpiwowar$ for i in `seq 1 $ASM_DISK_CNT`
> do
>    date
>    VBoxManage createhd --filename ${BASE_DIR}/${ASM_DISK_DIR}/asm${i}.vdi --size $ASM_DISK_MB --format VDI --variant Fixed
>    echo "Disk $i complete"
> done
Wed Dec 19 13:30:30 PST 2012
0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%
Disk image created. UUID: 03cfd4f9-0f0b-4135-a9be-7e1a7f2dbfff
Disk 1 complete
Wed Dec 19 13:31:13 PST 2012
0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%
Disk image created. UUID: e0bbcdf9-8ac6-4971-bc2c-f796196008e0
Disk 2 complete
Wed Dec 19 13:32:20 PST 2012
0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%
Disk image created. UUID: 375212e5-5ce1-4b63-942e-14ea0a7c4450
Disk 3 complete
Wed Dec 19 13:33:15 PST 2012
0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%
Disk image created. UUID: 2818b7cb-83e8-45c7-a005-a3d01abd901e
Disk 4 complete
Wed Dec 19 13:34:36 PST 2012
0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%
Disk image created. UUID: 392f5b00-32ea-4201-b253-0bf6d11a60e7
Disk 5 complete
zathras:OVMRACTempl jpiwowar$ for i in `seq 1 $ASM_DISK_CNT`
> do
> VBoxManage modifyhd ${BASE_DIR}/${ASM_DISK_DIR}/asm${i}.vdi --type shareable
> VBoxManage storageattach $VMNAME1 --storagectl "SATA Controller" --port $((i+1)) --device 0 --type hdd --medium ${BASE_DIR}/${ASM_DISK_DIR}/asm${i}.vdi --mtype shareable
> VBoxManage storageattach $VMNAME2 --storagectl "SATA Controller" --port $((i+1)) --device 0 --type hdd --medium ${BASE_DIR}/${ASM_DISK_DIR}/asm${i}.vdi --mtype shareable
> done
#No output expected, unless something goes wrong.

You can run VBoxManage showvminfo $VMNAME1 and VBoxManage showvminfo $VMNAME2 to display configurations of both machines; they should match, and you should see new disks attached to the SATA controller:

IDE (0, 0): /Applications/VirtualBox.app/Contents/MacOS/VBoxGuestAdditions.iso (UUID: edb51511-86a5-4f28-ac63-34a811446229)
SATA (0, 0): /Users/jpiwowar/VMs/OVMRACTempl/OVM_EL5U5_X86_64_11201RAC_PVM/Thing1/RacRoot.vdi (UUID: d3894bf3-aa74-4d61-b2f7-86b30f1a61db)
SATA (1, 0): /Users/jpiwowar/VMs/OVMRACTempl/OVM_EL5U5_X86_64_11201RAC_PVM/Thing1/RacORCL.vdi (UUID: 22565328-fe92-4467-b0a1-98d0bb71879d)
SATA (2, 0): /Users/jpiwowar/VMs/OVMRACTempl/OVM_EL5U5_X86_64_11201RAC_PVM/ASM/asm1.vdi (UUID: 03cfd4f9-0f0b-4135-a9be-7e1a7f2dbfff)
SATA (3, 0): /Users/jpiwowar/VMs/OVMRACTempl/OVM_EL5U5_X86_64_11201RAC_PVM/ASM/asm2.vdi (UUID: e0bbcdf9-8ac6-4971-bc2c-f796196008e0)
SATA (4, 0): /Users/jpiwowar/VMs/OVMRACTempl/OVM_EL5U5_X86_64_11201RAC_PVM/ASM/asm3.vdi (UUID: 375212e5-5ce1-4b63-942e-14ea0a7c4450)
SATA (5, 0): /Users/jpiwowar/VMs/OVMRACTempl/OVM_EL5U5_X86_64_11201RAC_PVM/ASM/asm4.vdi (UUID: 2818b7cb-83e8-45c7-a005-a3d01abd901e)
SATA (6, 0): /Users/jpiwowar/VMs/OVMRACTempl/OVM_EL5U5_X86_64_11201RAC_PVM/ASM/asm5.vdi (UUID: 392f5b00-32ea-4201-b253-0bf6d11a60e7)

Finally, we get to build the RAC cluster!
Start both VMs, and log in as root. Right now, neither machine has an "identity" on the network, so we need to configure them. On each node, execute the script /u01/racovm/netconfig.sh. These need to be run at the same time; the first node will wait until you've started the script on the second node. Answer 'YES' to "Is this the first node" question on node 1, 'NO' on node 2:

Complete the network configuration "interview" on node1 to configure, and wait for changes to be propagated to node 2:

At this point, both machines should be reachable from your host, via the hostnames and IPs you configured earlier. Now we can run the script that builds the RAC cluster and creates a small database called (what else?) ORCL. Connect to Thing1 (your first node) as root and run the script /u01/racovm/buildcluster.sh. I prefer to do this from an ssh session so I can keep track of the output, but you can just as easily run the script from the console. The build script took about 30 minutes on my laptop; your experience will probably be different.

[root@thing1 ~]# /u01/racovm/buildcluster.sh
Are you sure you want to install RAC?
Do not run if software is already installed and/or running..  [yes|no]? yes
Invoking on thing1 as root...
   Oracle RAC 11gR2 OneCommand (v1.2) for Oracle VM - (c) 2010-2011 Oracle Corporation
   Cksum: [1170221909 255000 racovm.sh] at Wed Dec 19 19:10:08 EST 2012
   Kernel: 2.6.18-194.el5 (x86_64) [1 processor(s)] 2011 MB
   Step(s): buildcluster
[30-ish minutes later...]

INFO (node:thing1): Running on: thing1 as oracle: export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1; /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvctl status database -d ORCL
Instance ORCL1 is running on node thing1
Instance ORCL2 is running on node thing2

INFO (node:thing1): Running on: thing1 as root: /u01/app/11.2.0/grid/bin/crsctl status resource -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS      
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       thing1                                      
               ONLINE  ONLINE       thing2                                      
ora.LISTENER.lsnr
               ONLINE  ONLINE       thing1                                      
               ONLINE  ONLINE       thing2                                      
ora.asm
               ONLINE  ONLINE       thing1                   Started            
               ONLINE  ONLINE       thing2                   Started            
ora.eons
               ONLINE  ONLINE       thing1                                      
               ONLINE  ONLINE       thing2                                      
ora.gsd
               OFFLINE OFFLINE      thing1                                      
               OFFLINE OFFLINE      thing2                                      
ora.net1.network
               ONLINE  ONLINE       thing1                                      
               ONLINE  ONLINE       thing2                                      
ora.ons
               ONLINE  ONLINE       thing1                                      
               ONLINE  ONLINE       thing2                                      
ora.registry.acfs
               ONLINE  ONLINE       thing1                                      
               ONLINE  ONLINE       thing2                                      
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       thing2                                      
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       thing1                                      
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       thing1                                      
ora.oc4j
      1        OFFLINE OFFLINE                                                  
ora.orcl.db
      1        ONLINE  ONLINE       thing1                   Open               
      2        ONLINE  ONLINE       thing2                   Open               
ora.scan1.vip
      1        ONLINE  ONLINE       thing2                                      
ora.scan2.vip
      1        ONLINE  ONLINE       thing1                                      
ora.scan3.vip
      1        ONLINE  ONLINE       thing1                                      
ora.thing1.vip
      1        ONLINE  ONLINE       thing1                                      
ora.thing2.vip
      1        ONLINE  ONLINE       thing2                                      

INFO (node:thing1): For an explanation on resources in OFFLINE state, see Note:1068835.1
2012-12-19 19:42:46:[clusterstate:Time :thing1] Completed successfully in 4 seconds (0h:00m:04s)
2012-12-19 19:42:46:[buildcluster:Done :thing1] Build 11gR2 RAC Cluster
2012-12-19 19:42:46:[buildcluster:Time :thing1] Completed successfully in 1958 seconds (0h:32m:38s)

INFO (node:thing1): This entire build was logged in logfile: /u01/racovm/buildcluster1.log

...And we're done!
Since this is still in the proof-of-concept phase for me, I haven't taken this cluster for a long test drive yet, just brief tours through asmctl and the ORCL database. As far as I can see, I have a healthy, happy RAC cluster on my workstation, but I welcome hearing about your experiences and any tweaks or optimizations. Please start a conversation in the comments!

crontab backups: a simple, time-saving holiday gift idea for DBAs and sysadmins

jpiwowar — Mon, 24 Dec 2012 08:00:34 +0000

[I originally posted this over at the Pythian blog. If you're not following it, you should! Way more content, by far smarter people than lil ol' me.]

It's holiday season in many parts of the world, but it's not all parties and egg-nog. Caretakers of critical IT systems often have significant work to do as those systems roll over into a new year. In some cases, we're just monitoring to make sure nothing unusual happens. In other cases, however, we're making the unusual happen: running those batch jobs and backups that only fire once per year.

So what happens when you update the crontab on a critical system to accommodate year-end processing? What happens when, despite all your diligence and devotion to human reliability guidelines, you perform a simple slip, and instead of typing crontab -e, you type crontab -r? Well, the documentation tells you what happens:

     -r      Remove the current crontab.

     -e      Edit the current crontab

(Incidentally, if there isn't a rule of software design that requires you to LOOK DOWN AT YOUR KEYBOARD WHEN CHOOSING FLAGS FOR YOUR UTILITIES, there damn well should be. Seriously, who thought that combinations of options was a good idea???)

What happens next is up to you. You can:

Pray that you typed crontab -l first, and can recover the listing from your terminal logs
Search feverishly through email, old trouble tickets, and other internal documentation to reconstruct the contents of the crontab
Reach out to your sysadmin (assuming that's not you, and that the sysadmin isn't already on holiday) for help in restoring the contents from backup, and won't that be a fun conversation?
Restore from a backup that you've been making yourself, all along.

I'm sure that most people would pick option #4, and it's very easy to implement. You can give yourself the gift of "crontab insurance" by adding a simple line to the beginning of each of your crontabs:

01 00 * * * /usr/bin/crontab -l > ~/.crons/cronbak.`date +\%u` 2>&1

You'll also need to create the .crons directory first, and double-check the path to the crontab executable on your system. But the result should be a one-week rotation of crontab backups, named cronbak.[1-7]. If you can't be certain that you'll notice something's wrong with your crontab in 1 week, you can easily get additional "insurance" by changing %u to %d (day of month) or even %j (day of year).

While this may seem like an inconsequential thing, it's not. This little backup technique has saved me and other members of my team significant amounts of work and precious client time. I have personally had to execute 3 of the four solutions listed above (thankfully, not #3...yet ), and having this backup on hand is by far the most preferable, stress-free option. No matter how good we are at what we do, it's important to remember that even maximum human reliability is not infallibility. Take the time to do this simple thing; someday you'll be glad you did.

Build an E-Business Suite 12.1.3 sandbox in VirtualBox with one hour of work

jpiwowar — Mon, 10 Dec 2012 08:00:38 +0000

[I originally posted this over at the Pythian blog. If you're not following it, you should! Way more content, by far smarter people than lil ol' me.]

Self-directed learning about a product as complex as Oracle E-Business Suite can be challenging. Oracle helpfully provides a pre-built environment (called 'Vision') for training purposes. Configuring a viable training or "sandbox" environment on a workstation can be pretty daunting, however, considering:

The memory and storage resources required to run a "small" Vision instance
The need to install and configure the OS and a list of pre-requisite patches and packages
Learning about the installer itself, which is straightforward but can be a little quirky, especially without careful attention to item #2!
Learning enough about basic EBS administration to turn the darn thing on and start playing

Over time, improved specs for workstations have made item #1 less of a problem. There is ample documentation to cover items 2-4, and several people (including yours truly) have written basic bootstrap guides to help people get up and running quickly. Then Oracle went a step further and released Oracle VM templates for EBS (and updated them earlier this year), which eliminate the need for all of the fiddly stuff in steps 2-3, except for one small detail: They only run in Oracle VM Server, and maintaining an Oracle VM environment is a resource-intensive endeavor by itself.

This blog post aims to change all that. Are you ready to learn how to deploy the Oracle VM templates in Oracle VirtualBox, so you can run them on your own workstation without the need to run Oracle VM Server? I hope you are, because let me tell you, I've done a lot of typing. Whew.

Oh, and that "One hour of work" line in the title is not a mere advertising gimmick! There are long steps at the beginning, but they're mostly hands-free (downloading and converting the OVM templates, steps 1-3 below). After you've gone through that, you really can have a working EBS 12.1.3 Vision instance up and running in a little over an hour. Let's begin...

Ingredients

You will need:

At least 3.5 GB of free memory (2 GB for the database server, 1.5 for the application server). You may be ok to use 1.5GB and 1GB, respectively, but I have not done rigorous testing on useable lower limits. Bottom line: If you have 4GB of RAM in your workstation, this may still work, but it will be a tight fit.
A recent version of Oracle VirtualBox. Virtualbox installation instructions are beyond the scope of this post, but it's a straighforward install.
A lot of storage, for reasons that will become clear later. ~700GB. Fortunately, 1TB USB drives are pretty cheap these days, and you can reclaim some of the space when you're done with this exercise.
A Linux install ISO image (or physical CD, if you're into that sort of thing). I used Oracle Enterprise Linux 5, Update 6, but any relatively recent OEL or RHEL install image should do the job here.
An understanding of some basic Linux systems administration tasks
Familiarity with configuring storage and network options in Virtualbox
Familiarity with the EBS Rapid Clone (adcfgclone) utility, or willingness to blindly follow instructions and cross your fingers.
Patience. There are large downloads involved, and then operations on some pretty big files.

Important notes

Nothing you're about to read in this post is supported by anyone. Not me, not Pythian, and certainly not Oracle. If you're thinking about using the techniques described here for any sort of production or QA deployment, please stop and question your sanity. Then call a few colleagues over to your desk and ask them to question your sanity.

Please be mindful of your licensing and support status for Oracle E-Business Suite when experimenting with your "sandbox" E-Business Suite instances. EBS is subject to a far more restrictive evaluation license than the more-familiar OTN development license.

Presumably, the basic method described in this post could be applied to convert other OVM templates. I haven't tried this yet, but there is no EBS-specific magic here. For that matter, with a bit of adjustment, one should also be able to deploy these and other OVM templates under VMware workstation products.

As with many things you'll find on the intertubes, this is not an original idea. I took my inspiration from the following blog posts, and am grateful to their authors for sharing:

Finally, you'll note a Unix-y bias to my instructions. I've done my VirtualBox work on OS X, but all the principles are the same for Windows and Linux hosts. You'll need to be able to concatenate and uncompress some files, an ssh client to connect to your VMs, and use a command-line utility that comes with your VirtualBox install (VBoxManage). That's about it from the host (workstation) side. The guests (VMs) are all Linux, of course, there's no getting around that!

HOWTO: The short version

Here are the basic steps, so you know what you're in for. This may actually be enough to get some readers 95% of the way there, but there are a few tweaks required along the way. I advise checking the more detailed instructions below, as well, particularly steps 6,9, 11, and 14.

Download the OVM EBS 12.1 Vision database and app tier templates from the Oracle Software Cloud thingy (formerly E-Delivery)
Unzip all the zip files, and unzip those contents as well
Convert the disk image files from the two VM templates to .vdi format
Boot the database server from the Linux ISO in rescue mode, and install a non-Xen version of the Linux kernel
Reboot and configure the EBS database
Boot the application server from the Linux ISO in rescue mode, and install a non-Xen version of the Linux kernel
Reboot and configure the EBS application server
Play on!

HOWTO: The long version (with screenshots and everything!)

Download the software
Connect to Oracle's Software Delivery Cloud and download the files listed under Oracle VM Templates for Oracle E-Business Suite Release 12.1.3 Vision Media Pack for x86 (64 bit). You'll need all 11 files (V25323-1.zip through V25333-01.zip). I also recommend clicking the "View Digest " button near the top of the download page, and running md5sum on each of the downloaded zip files to make sure the checksums match that list.

Extract the templates
Note: If you're using Windows and don't have a Unix-like shell environment like Cygwin available, you may have to translate some of these steps to their Windows-y equivalents. Since we're just concatenating and uncompressing files, I will assume you can do that.

Unzip all the files you just downloaded (unzip 'V25*.zip'). This will produce two sets of files: OVM_OL5U6_X86_64_EBIZ12.1.3_DB_VIS_PVM.tgz.[0-7] (database tier template) and OVM_OL5U6_X86_64_EBIZ12.1.3_APPS_VIS_PVM.tgz.[0-2] (apps tier template).

Concatenate and uncompress the two sets of files:

zathras:OVM jpiwowar$ time cat /Volumes/Software/OVM1213Templ/OVM_OL5U6_X86_64_EBIZ12.1.3_DB_VIS_PVM.tgz.* | tar xvz
x OVM_OL5U6_X86_64_EBIZ12.1.3_DB_VIS_PVM/
x OVM_OL5U6_X86_64_EBIZ12.1.3_DB_VIS_PVM/ebs1211db.img
x OVM_OL5U6_X86_64_EBIZ12.1.3_DB_VIS_PVM/System.img
x OVM_OL5U6_X86_64_EBIZ12.1.3_DB_VIS_PVM/vm.cfg
x OVM_OL5U6_X86_64_EBIZ12.1.3_DB_VIS_PVM/README.txt

real	84m53.974s
user	12m44.096s
sys	5m13.680s
zathras:OVM jpiwowar$ time cat /Volumes/Software/OVM1213Templ/OVM_OL5U6_X86_64_EBIZ12.1.3_APPS_VIS_PVM.tgz.* | tar xvz
x OVM_OL5U6_X86_64_EBIZ12.1.3_APPS_VIS_PVM/
x OVM_OL5U6_X86_64_EBIZ12.1.3_APPS_VIS_PVM/vm.cfg
x OVM_OL5U6_X86_64_EBIZ12.1.3_APPS_VIS_PVM/ebs1211apps.img
x OVM_OL5U6_X86_64_EBIZ12.1.3_APPS_VIS_PVM/README.txt
x OVM_OL5U6_X86_64_EBIZ12.1.3_APPS_VIS_PVM/System.img

real	16m11.327s
user	2m42.887s
sys	1m11.960s

When complete, you should have two directories:

zathras:Valen jpiwowar$ ls -lh OVM_OL5U6_X86_64_EBIZ12.1.3_*
OVM_OL5U6_X86_64_EBIZ12.1.3_APPS_VIS_PVM:
total 105194496
-rwxrwxrwx  1 jpiwowar  jpiwowar    20K Nov  4 19:44 README.txt
-rwxrwxrwx  1 jpiwowar  jpiwowar   5.2G Nov  4 19:47 System.img
-rwxrwxrwx  1 jpiwowar  jpiwowar    45G Nov  4 19:44 ebs1211apps.img
-rwxrwxrwx  1 jpiwowar  jpiwowar   448B Nov  4 19:15 vm.cfg

OVM_OL5U6_X86_64_EBIZ12.1.3_DB_VIS_PVM:
total 514145024
-rwxrwxrwx  1 jpiwowar  jpiwowar    20K Nov  4 22:28 README.txt
-rwxrwxrwx  1 jpiwowar  jpiwowar   5.2G Nov 13 02:59 System.img
-rwxrwxrwx  1 jpiwowar  jpiwowar   240G Nov  4 22:24 ebs1211db.img
-rwxrwxrwx  1 jpiwowar  jpiwowar   434B Nov  4 22:28 vm.cfg

Incidentally, the README files contain a few interesting tidbits about the environment you're building. I recommend reviewing them while waiting for Step 3 to complete.

Convert the disk images to .vdi format

Open a command/terminal window and use the VBoxManage utility to convert the raw disk images (.img) to vdi files. The utility is installed with VirtualBox; you may need to find the utility first and add it to your path (location varies by host platform). Timings listed in the examples below are provided to set expectations for how long you'll need to wait for the conversion to complete. Note that I was doing this on a slow 5400rpm drive on my laptop; you may get better performance. To save yourself some confusion later when attaching the converted disks to your VMs, I strongly recommend changing the name of 'System' image files to something uniquely identifiable, as shown below.

zathras:OVM_OL5U6_X86_64_EBIZ12.1.3_APPS_VIS_PVM jpiwowar$ time VBoxManage convertfromraw /Volumes/Io/OVM/OVM_OL5U6_X86_64_EBIZ12.1.3_APPS_VIS_PVM/System.img EBS121AppRoot.vdi; time VBoxManage convertfromraw /Volumes/Io/OVM/OVM_OL5U6_X86_64_EBIZ12.1.3_APPS_VIS_PVM/ebs1211apps.img ebs1211apps.vdi
Converting from raw image file="/Volumes/Io/OVM/OVM_OL5U6_X86_64_EBIZ12.1.3_APPS_VIS_PVM/System.img" to file="EBS121AppRoot.vdi"...
Creating dynamic image with size 5535613440 bytes (5280MB)...

real 3m39.852s
user 0m1.814s
sys 0m7.244s
Converting from raw image file="/Volumes/Io/OVM/OVM_OL5U6_X86_64_EBIZ12.1.3_APPS_VIS_PVM/ebs1211apps.img" to file="ebs1211apps.vdi"...
Creating dynamic image with size 48323520000 bytes (46085MB)...

real 32m22.239s
user 0m14.037s
sys 1m22.806s

zathras:OVM_OL5U6_X86_64_EBIZ12.1.3_DB_VIS_PVM jpiwowar$ time VBoxManage convertfromraw /Volumes/Io/OVM/OVM_OL5U6_X86_64_EBIZ12.1.3_DB_VIS_PVM/System.img EBS121DBRoot.vdi; time VBoxManage convertfromraw /Volumes/Io/OVM/OVM_OL5U6_X86_64_EBIZ12.1.3_DB_VIS_PVM/ebs1211db.img ebs1211db.img.vdi
Converting from raw image file="/Volumes/Io/OVM/OVM_OL5U6_X86_64_EBIZ12.1.3_DB_VIS_PVM/System.img" to file="EBS121DBRoot.vdi"...
Creating dynamic image with size 5535613440 bytes (5280MB)...

real 3m54.990s
user 0m1.561s
sys 0m7.082s
Converting from raw image file="/Volumes/Io/OVM/OVM_OL5U6_X86_64_EBIZ12.1.3_DB_VIS_PVM/ebs1211db.img" to file="ebs1211db.img.vdi"...
Creating dynamic image with size 257706247680 bytes (245768MB)...

real 193m12.402s
user 1m10.258s
sys 10m15.028s

Create two new Virtualbox VMs
You'll need one VM for the apps server and one for the database server. I'm assuming you know how to create VMs in Virtualbox already, so I will not go into excruciating, screenshot-laden detail. Here are the requirements for each VM...
- OS: Oracle Linux, 64-bit
- CPUs: 1
- Device boot order: CD-ROM, Hard Disk
- Memory: 2GB for db server, 1GB for app server
- Storage: Attach Linux installation ISO to CD/DVD drive on the IDE controller, and attach the two vdi files to the SATA controller
- Network: Two network interfaces - 1 host-only (vboxnet0), and 1 NAT. I set this up to allow the VMs to communicate between themselves and get out to the internet for patches and updates. If you have a favorite preferred configuration for your VMs that also achieves these goals, please feel free to use that instead.
The screenshots below illustrate the final configurations of the db and app servers:
Boot the database server VM in rescue mode from the install CD
Enter "linux rescue" at the the boot: prompt to enter rescue mode:

Select the keyboard and language preferences that suit you, and enable both network interfaces (for now, just select "use IPv4" and "DHCP" when configuring eth0 and eth1, unless you have strong opinions. These settings are temporary):

(repeat the steps above for the second interface, eth1)

After setting up the network interfaces, progress therough the menus ("Continue" and "OK" in my case) until you get to a linux prompt, and switch to the root volume as instructed: # chroot /mnt/sysimage
Optional step: start the sshd service and connect to the VM from your host via ssh, instead of performing the next few steps from the console of the VM. Use 'ifconfig eth0' to find the IP address to use. (Note: as listed in the README in the template directory, the root password for both VMs is 'ovsroot')
# service sshd start

Update configuration files
The kernel modules that are loaded to support the Xen kernel are not going to work with the non-Xen kernel, so we need to update modprobe.conf to match our target kernel version:

[root@localhost ~]# cp /etc/modprobe.conf /etc/modprobe.conf.xen
[root@localhost ~]# cat /etc/modprobe.conf
alias eth0 xennet
alias scsi_hostadapter xenblk
[root@localhost ~]# vi /etc/modprobe.conf
"/etc/modprobe.conf" 3L, 78C written
[root@localhost ~]# diff /etc/modprobe.conf /etc/modprobe.conf.xen
1,3c1,2
< alias eth0 e1000
< alias scsi_hostadapter ata_piix
< alias scsi_hostadapter1 ahci
---
> alias eth0 xennet
> alias scsi_hostadapter xenblk
[root@localhost ~]# cat /etc/modprobe.conf
alias eth0 e1000
alias scsi_hostadapter ata_piix
alias scsi_hostadapter1 ahci

Update /etc/fstab to disable automatic fsck on /u01, or you'll be sitting for a little while at boot time:

[root@localhost ~]# vi /etc/fstab
"/etc/fstab" 8L, 608C written
[root@localhost ~]# grep /u01 /etc/fstab
LABEL=ebs1211db         /u01                    ext3    defaults        1 0

Finally, edit /etc/sysconfig/oraclevm-template to disable template configuration at boot time. Otherwise, you'll have to run through all these steps on the console, no matter what else happens at boot time, and that's annoying.

[root@localhost ~]# vi /etc/sysconfig/oraclevm-template
"/etc/sysconfig/oraclevm-template" 27L, 929C written
[root@localhost ~]# grep RUN_ /etc/sysconfig/oraclevm-template
#RUN_TEMPLATE_CONF=YES
RUN_TEMPLATE_CONF=NO

Install a new kernel (this isn't as scary as it sounds...it's not the late 90's anymore)

Virtualbox can't boot the Xen kernel that supports para-virtualized operation under OVM, so we need to replace it with a standard kernel of the same version. One way to do this is to install the correct kernel version from the install media, but just in case you're using media that isn't the same as what's installed in the OVM template (OEL5 update 6), we'll use the Oracle public yum server instead (that's why we've configured and activated the NAT interface):

[root@localhost etc]# cd /etc/yum.repos.d

[root@localhost yum.repos.d]# wget http://public-yum.oracle.com/public-yum-el5.repo
--2012-11-15 00:25:26--  http://public-yum.oracle.com/public-yum-el5.repo
Resolving public-yum.oracle.com... 141.146.44.34
Connecting to public-yum.oracle.com|141.146.44.34|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 3974 (3.9K) 
Saving to: `public-yum-el5.repo'

100%[==========================================================================================> 3,974       --.-K/s   in 0s     

2012-11-15 00:25:26 (316 MB/s) - `public-yum-el5.repo' saved [3974/3974]

Update the public-yum-el5.repo file and set enabled=0 for every source except ol5_u6_base:

[root@localhost yum.repos.d]# vi public-yum-el5.repo   
"public-yum-el5.repo" 111L, 3974C written
[root@localhost yum.repos.d]# grep -B5 'enabled=1' public-yum-el5.repo | grep ']'
[ol5_u6_base]

Install the OEL5 update 6 (2.6.18-238) kernel form the Oracle public yum server:

[root@localhost yum.repos.d]# yum install kernel-2.6.18-238.el5
ol5_u6_base                                                                                                  | 1.1 kB     00:00    
ol5_u6_base/primary                                                                                          | 1.5 MB     00:02    
ol5_u6_base                                                                                                               4551/4551
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package kernel.x86_64 0:2.6.18-238.el5 set to be installed
--> Finished Dependency Resolution

Dependencies Resolved

====================================================================================================================================
 Package                     Arch                        Version                             Repository                        Size
====================================================================================================================================
Installing:
 kernel                      x86_64                      2.6.18-238.el5                      ol5_u6_base                       19 M

Transaction Summary
====================================================================================================================================
Install       1 Package(s)
Upgrade       0 Package(s)

Total download size: 19 M
Is this ok [y/N]: y
Downloading Packages:
kernel-2.6.18-238.el5.x86_64.rpm                                                                             |  19 MB     00:23    
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing     : kernel                                                                                                       1/1

Installed:
  kernel.x86_64 0:2.6.18-238.el5                                                                                                   

Complete!

Create an initrd for the new kernel; this should also add a new stanza to grub.conf:

[root@localhost yum.repos.d]# mkinitrd -v -f /boot/initrd-2.6.18-238.el5.img 2.6.18-238.el5
Creating initramfs
Looking for deps of module ehci-hcd
Looking for deps of module ohci-hcd
Looking for deps of module uhci-hcd
Looking for deps of module ext3: jbd
Looking for deps of module jbd
Found root device sda2 for LABEL=/
Looking for driver for device sda2
Looking for deps of module pci:v00008086d00002829sv00000000sd00000000bc01sc06i01: scsi_mod libata ahci scsi_mod libata ahci
Looking for deps of module scsi_mod
Looking for deps of module sd_mod: scsi_mod
Looking for deps of module libata: scsi_mod
Looking for deps of module ahci: scsi_mod libata
Looking for driver for device sda3
Looking for deps of module pci:v00008086d00002829sv00000000sd00000000bc01sc06i01: scsi_mod libata ahci scsi_mod libata ahci
Looking for deps of module ata_piix: scsi_mod libata
Looking for deps of module ide-disk
Looking for deps of module dm-mem-cache
Looking for deps of module dm-region_hash: dm-mod dm-log
Looking for deps of module dm-mod
Looking for deps of module dm-log: dm-mod
Looking for deps of module dm-message
Looking for deps of module dm-raid45: dm-message dm-mod dm-mem-cache dm-log dm-region_hash
Using modules:  /lib/modules/2.6.18-238.el5/kernel/drivers/usb/host/ehci-hcd.ko /lib/modules/2.6.18-238.el5/kernel/drivers/usb/host/ohci-hcd.ko /lib/modules/2.6.18-238.el5/kernel/drivers/usb/host/uhci-hcd.ko /lib/modules/2.6.18-238.el5/kernel/fs/jbd/jbd.ko /lib/modules/2.6.18-238.el5/kernel/fs/ext3/ext3.ko /lib/modules/2.6.18-238.el5/kernel/drivers/scsi/scsi_mod.ko /lib/modules/2.6.18-238.el5/kernel/drivers/scsi/sd_mod.ko /lib/modules/2.6.18-238.el5/kernel/drivers/ata/libata.ko /lib/modules/2.6.18-238.el5/kernel/drivers/ata/ahci.ko /lib/modules/2.6.18-238.el5/kernel/drivers/ata/ata_piix.ko /lib/modules/2.6.18-238.el5/kernel/drivers/md/dm-mem-cache.ko /lib/modules/2.6.18-238.el5/kernel/drivers/md/dm-mod.ko /lib/modules/2.6.18-238.el5/kernel/drivers/md/dm-log.ko /lib/modules/2.6.18-238.el5/kernel/drivers/md/dm-region_hash.ko /lib/modules/2.6.18-238.el5/kernel/drivers/md/dm-message.ko /lib/modules/2.6.18-238.el5/kernel/drivers/md/dm-raid45.ko
/sbin/nash -> /tmp/initrd.TY1796/bin/nash
/sbin/insmod.static -> /tmp/initrd.TY1796/bin/insmod
/etc/udev/rules.d/05-udev-early.rules -> /tmp/initrd.TY1796/etc/udev/rules.d/05-udev-early.rules
/sbin/firmware_helper.static -> /tmp/initrd.TY1796/sbin/firmware_helper
/sbin/udevd.static -> /tmp/initrd.TY1796/sbin/udevd
copy from `/lib/modules/2.6.18-238.el5/kernel/drivers/usb/host/ehci-hcd.ko' [elf64-x86-64] to `/tmp/initrd.TY1796/lib/ehci-hcd.ko' [elf64-x86-64]
copy from `/lib/modules/2.6.18-238.el5/kernel/drivers/usb/host/ohci-hcd.ko' [elf64-x86-64] to `/tmp/initrd.TY1796/lib/ohci-hcd.ko' [elf64-x86-64]
copy from `/lib/modules/2.6.18-238.el5/kernel/drivers/usb/host/uhci-hcd.ko' [elf64-x86-64] to `/tmp/initrd.TY1796/lib/uhci-hcd.ko' [elf64-x86-64]
copy from `/lib/modules/2.6.18-238.el5/kernel/fs/jbd/jbd.ko' [elf64-x86-64] to `/tmp/initrd.TY1796/lib/jbd.ko' [elf64-x86-64]
copy from `/lib/modules/2.6.18-238.el5/kernel/fs/ext3/ext3.ko' [elf64-x86-64] to `/tmp/initrd.TY1796/lib/ext3.ko' [elf64-x86-64]
copy from `/lib/modules/2.6.18-238.el5/kernel/drivers/scsi/scsi_mod.ko' [elf64-x86-64] to `/tmp/initrd.TY1796/lib/scsi_mod.ko' [elf64-x86-64]
copy from `/lib/modules/2.6.18-238.el5/kernel/drivers/scsi/sd_mod.ko' [elf64-x86-64] to `/tmp/initrd.TY1796/lib/sd_mod.ko' [elf64-x86-64]
copy from `/lib/modules/2.6.18-238.el5/kernel/drivers/ata/libata.ko' [elf64-x86-64] to `/tmp/initrd.TY1796/lib/libata.ko' [elf64-x86-64]
copy from `/lib/modules/2.6.18-238.el5/kernel/drivers/ata/ahci.ko' [elf64-x86-64] to `/tmp/initrd.TY1796/lib/ahci.ko' [elf64-x86-64]
copy from `/lib/modules/2.6.18-238.el5/kernel/drivers/ata/ata_piix.ko' [elf64-x86-64] to `/tmp/initrd.TY1796/lib/ata_piix.ko' [elf64-x86-64]
copy from `/lib/modules/2.6.18-238.el5/kernel/drivers/md/dm-mem-cache.ko' [elf64-x86-64] to `/tmp/initrd.TY1796/lib/dm-mem-cache.ko' [elf64-x86-64]
copy from `/lib/modules/2.6.18-238.el5/kernel/drivers/md/dm-mod.ko' [elf64-x86-64] to `/tmp/initrd.TY1796/lib/dm-mod.ko' [elf64-x86-64]
copy from `/lib/modules/2.6.18-238.el5/kernel/drivers/md/dm-log.ko' [elf64-x86-64] to `/tmp/initrd.TY1796/lib/dm-log.ko' [elf64-x86-64]
copy from `/lib/modules/2.6.18-238.el5/kernel/drivers/md/dm-region_hash.ko' [elf64-x86-64] to `/tmp/initrd.TY1796/lib/dm-region_hash.ko' [elf64-x86-64]
copy from `/lib/modules/2.6.18-238.el5/kernel/drivers/md/dm-message.ko' [elf64-x86-64] to `/tmp/initrd.TY1796/lib/dm-message.ko' [elf64-x86-64]
copy from `/lib/modules/2.6.18-238.el5/kernel/drivers/md/dm-raid45.ko' [elf64-x86-64] to `/tmp/initrd.TY1796/lib/dm-raid45.ko' [elf64-x86-64]
/sbin/dmraid.static -> /tmp/initrd.TY1796/bin/dmraid
/sbin/kpartx.static -> /tmp/initrd.TY1796/bin/kpartx
Adding module ehci-hcd
Adding module ohci-hcd
Adding module uhci-hcd
Adding module jbd
Adding module ext3
Adding module scsi_mod
Adding module sd_mod
Adding module libata
Adding module ahci
Adding module ata_piix
Adding module dm-mem-cache
Adding module dm-mod
Adding module dm-log
Adding module dm-region_hash
Adding module dm-message
Adding module dm-raid45

[root@localhost yum.repos.d]# cat /boot/grub/grub.conf
# grub.conf generated by anaconda
#
# Note that you do not have to rerun grub after making changes to this file
# NOTICE:  You have a /boot partition.  This means that
#          all kernel and initrd paths are relative to /boot/, eg.
#          root (hd0,0)
#          kernel /vmlinuz-version ro root=/dev/xvda2
#          initrd /initrd-version.img
#boot=/dev/xvda
timeout=5
splashimage=(hd0,0)/grub/splash.xpm.gz
hiddenmenu
default=0
title Oracle Linux Server (2.6.18-238.el5)
     root (hd0,0)
     kernel /vmlinuz-2.6.18-238.el5 ro root=LABEL=/ numa=off
     initrd /initrd-2.6.18-238.el5.img
title Oracle Linux Server (2.6.18-238.0.0.0.1.el5xen)
     root (hd0,0)
     kernel /vmlinuz-2.6.18-238.0.0.0.1.el5xen ro root=LABEL=/ numa=off
     initrd /initrd-2.6.18-238.0.0.0.1.el5xen.img
title Oracle Linux Server (2.6.32-100.26.2.el5)
     root (hd0,0)
     kernel /vmlinuz-2.6.32-100.26.2.el5 ro root=LABEL=/ numa=off
     initrd /initrd-2.6.32-100.26.2.el5.img
title Enterprise Linux Enterprise Linux Server (2.6.32-100.0.19.el5)
     root (hd0,0)
     kernel /vmlinuz-2.6.32-100.0.19.el5 ro root=LABEL=/ numa=off
     initrd /initrd-2.6.32-100.0.19.el5.img
title Enterprise Linux Enterprise Linux Server (2.6.18-128.0.0.0.2.el5xen)
     root (hd0,0)
     kernel /vmlinuz-2.6.18-128.0.0.0.2.el5xen ro root=LABEL=/ numa=off
     initrd /initrd-2.6.18-128.0.0.0.2.el5xen.img

Side note: Astute readers will notice (by inspection of grub.conf or by running rpm -q) that there are a number of other kernels already installed in this VM, including some "vanilla" OEL6/UEK kernels and an earlier OEL5 Xen kernel. I chose to stick with OEL 5.6 because it matches the version of the template's Xen kernel, and therefore best fits the most recent configuration of the VM. Besides, at the time of this writing, there are still some components of E-Business Suite that are not yet supported under OEL6/RHEL6 (see "Unsupported Products" in My Oracle Support Note 761566.1 )

[root@localhost ~]# rpm -qa | grep kernel
kernel-uek-headers-2.6.32-100.26.2.el5
kernel-xen-2.6.18-128.0.0.0.2.el5
kernel-2.6.32-100.0.19.el5
kernel-xen-2.6.18-238.0.0.0.1.el5
kernel-firmware-2.6.32-100.0.19.el5
kernel-uek-firmware-2.6.32-100.26.2.el5
kernel-uek-2.6.32-100.26.2.el5

Reboot and prepare for next steps
- Detach the boot ISO from the CD drive of the database server VM (Devices menu -> Remove disk from virtual drive)
- Reboot the database server VM.
- Connect as the root user (the password, as listed in the template README file, is 'ovsroot')

Configure the Vision database tier
Oracle provides a helpful script that, when run as root, will configure your network interfaces and the E-Business Suite Vision database tier. It should need to be run only once. First, we need to re-enable the script before we invoke it, by setting RUN_TEMPLATE_CONF=YES in /etc/sysconfig/oraclevm-template:

[root@localhost ~]# vi /etc/sysconfig/oraclevm-template
"/etc/sysconfig/oraclevm-template" 26L, 907C written
[root@localhost ~]# grep RUN_ /etc/sysconfig/oraclevm-template
RUN_TEMPLATE_CONF=YES

Now start the config script. The first part will help you configure networking. For my setup of the first network interface (eth0), I chose an IP address that was unused in the range supported by my host-only network, and was also not part of the range of DHCP addresses for the host-only network. Pick anything you like for the hostname, as long as it follows the three-part 'host.domain.com' scheme required by the configuration script. The sometimes-popular host.localdomain format won't work in this case. For DNS server, choose one that will work for you. I like Google's; it's easy to remember. Note that the script also picked my NAT interface to use as a gateway. This didn't cause me any problems so far, so I'm living with it. Warning: this script stops and restarts network interfaces! To minimize risk of failure, run these steps from the console of your VM. If you choose to connect to the VM via ssh, I recommend using 'screen' before running the oraclevm-template script, to insulate yourself from dropped network connections.

[root@localhost ~]# /etc/init.d/oraclevm-template start
Starting oraclevm-template...
Regenerating SSH host keys.
Stopping sshd:                                             [  OK  ]
Generating SSH1 RSA host key:                              [  OK  ]
Generating SSH2 RSA host key:                              [  OK  ]
Generating SSH2 DSA host key:                              [  OK  ]
Starting sshd:                                             [  OK  ]
Setting Oracle validated configuration parameters.
Configuring Oracle E-Business Suite...

Configuring network interface.
  Network device: eth0
  Hardware address: 08:00:27:5A:17:F4

Enter static IP address: 192.168.56.42
Enter netmask: [255.255.255.0] 
Enter gateway: 192.168.56.1
Enter DNS server: 8.8.8.8

Shutting down interface eth0:                              [  OK  ]
Shutting down interface eth1:                              [  OK  ]
Shutting down loopback interface:                          [  OK  ]

Configuring network settings.
  IP configuration: Static IP address

Bringing up loopback interface:                            [  OK  ]
Bringing up interface eth0:                                [  OK  ]
Bringing up interface eth1: 
Determining IP information for eth1... done.
                                                           [  OK  ]

Enter hostname (e.g, host.domain.com): gkar.local.org

Network configuration changed successfully.
  IP configuration: Static IP address
  IP address:       192.168.56.42
  Netmask:          255.255.255.0
  Gateway:          10.0.3.2
  DNS server:       8.8.8.8
  Hostname:         gkar.local.org

Once the network is set up, the script invokes the Rapid Clone utility to set up the EBS datbaase tier. You can get valid values from the README file that came with the template, or follow the example below.

                     Copyright (c) 2002 Oracle Corporation
                        Redwood Shores, California, USA

                        Oracle Applications Rapid Clone

                                 Version 12.0.0

                      adcfgclone Version 120.31.12010000.8

Enter the APPS password : apps

Running:
/u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/clone/bin/../jre/bin/java -Xmx600M -cp /u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/clone/jlib/java:/u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/clone/jlib/xmlparserv2.jar:/u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/clone/jlib/ojdbc5.jar oracle.apps.ad.context.CloneContext -e /u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/clone/bin/../context/db/CTXORIG.xml -validate -pairsfile /tmp/adpairsfile_7558.lst -stage /u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/clone  2> /tmp/adcfgclone_7558.err; echo $? > /tmp/adcfgclone_7558.res

Log file located at /u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/clone/bin/CloneContext_1115072133.log

Provide the values required for creation of the new Database Context file.

Target System Hostname (virtual or normal) [gkar] : 

Target Instance is RAC (y/n) [n] : 

Target System Database SID : VIS 

Target System Base Directory : /u01/E-BIZ

Target System utl_file_dir Directory List : /usr/tmp

Number of DATA_TOP's on the Target System [1] : 

Target System DATA_TOP Directory 1 [/u01/E-BIZ/db/apps_st/data] : 

Target System RDBMS ORACLE_HOME Directory [/u01/E-BIZ/db/tech_st/11.1.0] : /u01/E-BIZ/db/tech_st/11.2.0.2

Do you want to preserve the Display [atgtxk05:0.0] (y/n)  : n

Target System Display [gkar:0.0] : 

Do you want the the target system to have the same port values as the source system (y/n) [y] ? : n

Target System Port Pool [0-99] : 42

Checking the port pool 42
done: Port Pool 42 is free
Report file located at /u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/temp/portpool.lst
Complete port information available at /u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/temp/portpool.lst

Creating the new Database Context file from :
  /u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/template/adxdbctx.tmp

The new database context file has been created :
  /u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/VIS_gkar.xml

Log file located at /u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/clone/bin/CloneContext_1115072133.log
Check Clone Context logfile /u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/clone/bin/CloneContext_1115072133.log for details.

Running Rapid Clone with command:
perl /u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/clone/bin/adclone.pl java=/u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/clone/bin/../jre mode=apply stage=/u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/clone component=dbTier method=CUSTOM dbctxtg=/u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/VIS_gkar.xml showProgress contextValidated=true
Running:
perl /u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/clone/bin/adclone.pl java=/u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/clone/bin/../jre mode=apply stage=/u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/clone component=dbTier method=CUSTOM dbctxtg=/u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/VIS_gkar.xml showProgress contextValidated=true
APPS Password :

Beginning database tier Apply - Thu Nov 15 07:24:40 2012

/u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/clone/bin/../jre/bin/java -Xmx600M -DCONTEXT_VALIDATED=true  -Doracle.installer.oui_loc=/u01/E-BIZ/db/tech_st/11.2.0.2/oui -classpath /u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/clone/jlib/xmlparserv2.jar:/u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/clone/jlib/ojdbc5.jar:/u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/clone/jlib/java:/u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/clone/jlib/oui/OraInstaller.jar:/u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/clone/jlib/oui/ewt3.jar:/u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/clone/jlib/oui/share.jar:/u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/clone/jlib/oui/srvm.jar:/u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/clone/jlib/ojmisc.jar   oracle.apps.ad.clone.ApplyDBTier -e /u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/VIS_gkar.xml -stage /u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/clone   -showProgress
APPS Password : Log file located at /u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/log/VIS_gkar/ApplyDBTier_11150724.log
  \     15% completed      

Completed Apply...
Thu Nov 15 07:43:22 2012

Starting database listener for VIS:
Running:
/u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/scripts/VIS_gkar/addlnctl.sh start VIS
Logfile: /u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/log/VIS_gkar/addlnctl.txt

You are running addlnctl.sh version 120.1.12010000.4


Starting listener process VIS ...


Listener VIS has already been started.


addlnctl.sh: exiting with status 0

addlnctl.sh: check the logfile /u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/log/VIS_gkar/addlnctl.txt for more information ... 

Template configuration disabled.
                                                           [  OK  ]
[root@localhost ~]# ps -ef | grep pmon
oracle   14565     1  0 07:36 ?        00:00:00 ora_pmon_VIS
root     15375  2319  0 07:45 pts/0    00:00:00 grep pmon

OK, we're done with the first part! Keep the database server running (not optional, required), and let's move on to the apps tier!

Boot the app server VM in rescue mode from the install CD
Start the application server VM in rescue mode ("linux rescue" at the boot: prompt). Follow all the same steps as you did for the database server, including enabling the network interfaces, and chroot /mnt/sysimage. I'll spare you the screenshots this time.

Update configuration files
As with the database server, adjust /etc/modprobe.conf, /etc/fstab, and /etc/sysconfig/oraclevm-template:

[root@localhost ~]# cat /etc/modprobe.conf
alias eth0 e1000
alias scsi_hostadapter ata_piix
alias scsi_hostadapter1 ahci

[root@localhost ~]# grep ebs1211apps /etc/fstab
LABEL=ebs1211apps       /u01                    ext3    defaults        1 0

[root@localhost ~]# grep RUN /etc/sysconfig/oraclevm-template
#RUN_TEMPLATE_CONF=YES
RUN_TEMPLATE_CONF=NO

Install a new kernel
Same idea as before, with a few exceptions: The app server VM doesn't have a nameserver entry in resolv.conf, so we need to add it before we reach out the to the yum server. We do not, however, need to download the repository config file:

[root@localhost ~]# cd /etc/yum.repos.d 
[root@localhost yum.repos.d]# cat /etc/resolv.conf
cat: /etc/resolv.conf: No such file or directory
[root@localhost yum.repos.d]# echo "nameserver 8.8.8.8" > /etc/resolv.conf
[root@localhost yum.repos.d]# cat /etc/resolv.conf
nameserver 8.8.8.8
[root@localhost yum.repos.d]# nslookup public-yum.oracle.com
Server:          8.8.8.8
Address:     8.8.8.8#53

Non-authoritative answer:
public-yum.oracle.com     canonical name = bigip-public-yum.oracle.com.
Name:     bigip-public-yum.oracle.com
Address: 141.146.44.34

[root@localhost yum.repos.d]# ls -l public-yum-el5.repo
-rw-r--r-- 1 root root 2308 Feb  1  2011 public-yum-el5.repo
"public-yum-el5.repo" 69L, 2308C written
[root@localhost yum.repos.d]# vi public-yum-el5.repo
[root@localhost yum.repos.d]# grep -B5 'enabled=1' public-yum-el5.repo | grep ']'
[ol5_u6_base]
[root@localhost yum.repos.d]# yum install kernel-2.6.18-238.el5
ol5_u6_base                                                                                                  | 1.1 kB     00:00    
ol5_u6_base/primary                                                                                          | 1.5 MB     00:02    
ol5_u6_base                                                                                                               4551/4551
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package kernel.x86_64 0:2.6.18-238.el5 set to be installed
--> Finished Dependency Resolution

Dependencies Resolved

====================================================================================================================================
 Package                     Arch                        Version                             Repository                        Size
====================================================================================================================================
Installing:
 kernel                      x86_64                      2.6.18-238.el5                      ol5_u6_base                       19 M

Transaction Summary
====================================================================================================================================
Install       1 Package(s)
Upgrade       0 Package(s)

Total download size: 19 M
Is this ok [y/N]: y
Downloading Packages:
kernel-2.6.18-238.el5.x86_64.rpm                                                                             |  19 MB     00:23    
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing     : kernel                                                                                                       1/1

Installed:
  kernel.x86_64 0:2.6.18-238.el5                                                                                                   

Complete!


[root@localhost yum.repos.d]# mkinitrd -v -f /boot/initrd-2.6.18-238.el5.img 2.6.18-238.el5
[mkinitrd output snipped this time.  You're welcome]
[root@localhost yum.repos.d]# head -15 /boot/grub/grub.conf
# grub.conf generated by anaconda
#
# Note that you do not have to rerun grub after making changes to this file
# NOTICE:  You have a /boot partition.  This means that
#          all kernel and initrd paths are relative to /boot/, eg.
#          root (hd0,0)
#          kernel /vmlinuz-version ro root=/dev/xvda2
#          initrd /initrd-version.img
#boot=/dev/xvda
timeout=5
splashimage=(hd0,0)/grub/splash.xpm.gz
hiddenmenu
default=0
title Oracle Linux Server (2.6.18-238.el5)
     root (hd0,0)

Reboot and prepare for next steps
- Detach the boot ISO from the CD drive of the app server VM (Devices menu -> Remove disk from virtual drive)
- Reboot the app server VM.
- Connect as the root user (the password, as listed in the template README file, is 'ovsroot')

Configure the Vision applications tier
There are some important differences here from the database server setup. Please read carefully. The first part, however, is the same: re-enable and run the oraclevm-template script, setting up the same network options as before (different IP/hostname for the app server, of course). As with the database server, make sure to run the config script in the VM console or in a screen session to avoid network interruptions!

[root@localhost ~]# grep RUN /etc/sysconfig/oraclevm-template
RUN_TEMPLATE_CONF=YES

[root@localhost ~]# /etc/init.d/oraclevm-template start
Starting oraclevm-template...
Regenerating SSH host keys.
Stopping sshd:                                             [  OK  ]
Generating SSH1 RSA host key:                              [  OK  ]
Generating SSH2 RSA host key:                              [  OK  ]
Generating SSH2 DSA host key:                              [  OK  ]
Starting sshd:                                             [  OK  ]
Setting Oracle validated configuration parameters.
Configuring Oracle E-Business Suite...

Configuring network interface.
  Network device: eth0
  Hardware address: 08:00:27:3D:41:3B

Enter static IP address: 192.168.56.43
Enter netmask: [255.255.255.0] 
Enter gateway: 192.168.56.1
Enter DNS server: 8.8.8.8

Shutting down interface eth0:                              [  OK  ]
Shutting down interface eth1:                              [  OK  ]
Shutting down loopback interface:                          [  OK  ]

Configuring network settings.
  IP configuration: Static IP address

Bringing up loopback interface:                            [  OK  ]
Bringing up interface eth0:                                [  OK  ]
Bringing up interface eth1: 
Determining IP information for eth1... done.
                                                           [  OK  ]

Enter hostname (e.g, host.domain.com): natoth.local.org

Network configuration changed successfully.
  IP configuration: Static IP address
  IP address:       192.168.56.43
  Netmask:          255.255.255.0
  Gateway:          10.0.3.2
  DNS server:       8.8.8.8
  Hostname:         natoth.local.org

***IMPORTANT*** AFTER network configuration, but BEFORE proceeding with any Rapid Clone steps, start a second session (do NOT interrupt the template configuration script) to add the IP and hostname for your database server to /etc/hosts. Otherwise, Rapid Clone won't be able to find the db server to complete its configuration. It's not helpful to try to "cheat forward" and make the /etc/hosts adjustments before starting the template configuration script, because the script overwrites /etc/hosts. So:

root@192.168.56.43's password: #you should be able to access the VM via the IP you specified earlier
Last login: Tue Nov 20 21:51:13 2012 from 192.168.56.1
[root@natoth ~]# cat /etc/hosts
127.0.0.1       localhost.localdomain   localhost
::1     localhost6.localdomain6 localhost6
192.168.56.43     natoth.local.org natoth
[root@natoth ~]# echo "192.168.56.42 gkar.local.org gkar" >> /etc/hosts
[root@natoth ~]# cat /etc/hosts
127.0.0.1       localhost.localdomain   localhost
::1     localhost6.localdomain6 localhost6
192.168.56.43     natoth.local.org natoth
192.168.56.42 gkar.local.org gkar
[root@natoth ~]# ping gkar  #confirm that app server can see the database server
PING gkar.local.org (192.168.56.42) 56(84) bytes of data.
64 bytes from gkar.local.org (192.168.56.42): icmp_seq=1 ttl=64 time=1.94 ms
64 bytes from gkar.local.org (192.168.56.42): icmp_seq=2 ttl=64 time=0.505 ms

--- gkar.local.org ping statistics ---

***RESUME Rapid Clone run in the first session*** (it should be waiting for you to enter the APPS user password)

                     Copyright (c) 2002 Oracle Corporation
                        Redwood Shores, California, USA

                        Oracle Applications Rapid Clone

                                 Version 12.0.0

                      adcfgclone Version 120.31.12010000.8

Enter the APPS password : apps

Running:
/u01/E-BIZ/apps/apps_st/comn/clone/bin/../jre/bin/java -Xmx600M -cp /u01/E-BIZ/apps/apps_st/comn/clone/jlib/java:/u01/E-BIZ/apps/apps_st/comn/clone/jlib/xmlparserv2.jar:/u01/E-BIZ/apps/apps_st/comn/clone/jlib/ojdbc14.jar oracle.apps.ad.context.CloneContext -e /u01/E-BIZ/apps/apps_st/comn/clone/bin/../context/apps/CTXORIG.xml -validate -pairsfile /tmp/adpairsfile_21022.lst -stage /u01/E-BIZ/apps/apps_st/comn/clone  2> /tmp/adcfgclone_21022.err; echo $? > /tmp/adcfgclone_21022.res

Log file located at /u01/E-BIZ/apps/apps_st/comn/clone/bin/CloneContext_1120230342.log

Provide the values required for creation of the new APPL_TOP Context file.

Target System Hostname (virtual or normal) [natoth] : 

Target System Database SID : VIS

Target System Database Server Node [natoth] : gkar

Target System Database Domain Name [local.org] : 

Target System Base Directory : /u01/E-BIZ

Target System Tools ORACLE_HOME Directory [/u01/E-BIZ/apps/tech_st/10.1.2] : 

Target System Web ORACLE_HOME Directory [/u01/E-BIZ/apps/tech_st/10.1.3] : 

Target System APPL_TOP Directory [/u01/E-BIZ/apps/apps_st/appl] : 

Target System COMMON_TOP Directory [/u01/E-BIZ/apps/apps_st/comn] : 

Target System Instance Home Directory [/u01/E-BIZ/inst] : 

Target System Root Service [enabled] : 

Target System Web Entry Point Services [enabled] : 

Target System Web Application Services [enabled] : 

Target System Batch Processing Services [enabled] : 

Target System Other Services [disabled] : 

Do you want to preserve the Display [atgtxk-09:0.0] (y/n)  : n

Target System Display [natoth:0.0] : 

Do you want the the target system to have the same port values as the source system (y/n) [y] ? : n

Target System Port Pool [0-99] : 42

Checking the port pool 42
done: Port Pool 42 is free
Report file located at /u01/E-BIZ/inst/apps/VIS_natoth/temp/portpool.lst
Complete port information available at /u01/E-BIZ/inst/apps/VIS_natoth/temp/portpool.lst

UTL_FILE_DIR on database tier consists of the following directories.

1. /usr/tmp
2. /usr/tmp
3. /u01/E-BIZ/db/tech_st/11.2.0.2/appsutil/outbound/VIS_gkar
4. /usr/tmp
Choose a value which will be set as APPLPTMP value on the target node [1] : 1

Backing up /u01/E-BIZ/inst/apps/VIS_natoth/appl/admin/VIS_natoth.xml to /u01/E-BIZ/inst/apps/VIS_natoth/appl/admin/VIS_natoth.xml.bak

Creating the new APPL_TOP Context file from :
  /u01/E-BIZ/apps/apps_st/appl/ad/12.0.0/admin/template/adxmlctx.tmp

The new APPL_TOP context file has been created :
  /u01/E-BIZ/inst/apps/VIS_natoth/appl/admin/VIS_natoth.xml

Log file located at /u01/E-BIZ/apps/apps_st/comn/clone/bin/CloneContext_1120230342.log
Check Clone Context logfile /u01/E-BIZ/apps/apps_st/comn/clone/bin/CloneContext_1120230342.log for details.

Running Rapid Clone with command:
perl /u01/E-BIZ/apps/apps_st/comn/clone/bin/adclone.pl java=/u01/E-BIZ/apps/apps_st/comn/clone/bin/../jre mode=apply stage=/u01/E-BIZ/apps/apps_st/comn/clone component=appsTier method=CUSTOM appctxtg=/u01/E-BIZ/inst/apps/VIS_natoth/appl/admin/VIS_natoth.xml showProgress contextValidated=true
Running:
perl /u01/E-BIZ/apps/apps_st/comn/clone/bin/adclone.pl java=/u01/E-BIZ/apps/apps_st/comn/clone/bin/../jre mode=apply stage=/u01/E-BIZ/apps/apps_st/comn/clone component=appsTier method=CUSTOM appctxtg=/u01/E-BIZ/inst/apps/VIS_natoth/appl/admin/VIS_natoth.xml showProgress contextValidated=true
APPS Password :

Beginning application tier Apply - Tue Nov 20 23:04:58 2012

/u01/E-BIZ/apps/apps_st/comn/clone/bin/../jre/bin/java -Xmx600M -DCONTEXT_VALIDATED=true  -Doracle.installer.oui_loc=/oui -classpath /u01/E-BIZ/apps/apps_st/comn/clone/jlib/xmlparserv2.jar:/u01/E-BIZ/apps/apps_st/comn/clone/jlib/ojdbc14.jar:/u01/E-BIZ/apps/apps_st/comn/clone/jlib/java:/u01/E-BIZ/apps/apps_st/comn/clone/jlib/oui/OraInstaller.jar:/u01/E-BIZ/apps/apps_st/comn/clone/jlib/oui/ewt3.jar:/u01/E-BIZ/apps/apps_st/comn/clone/jlib/oui/share.jar:/u01/E-BIZ/apps/apps_st/comn/clone/jlib/oui/srvm.jar:/u01/E-BIZ/apps/apps_st/comn/clone/jlib/ojmisc.jar  oracle.apps.ad.clone.ApplyAppsTier -e /u01/E-BIZ/inst/apps/VIS_natoth/appl/admin/VIS_natoth.xml -stage /u01/E-BIZ/apps/apps_st/comn/clone    -showProgress
APPS Password : Log file located at /u01/E-BIZ/inst/apps/VIS_natoth/admin/log/ApplyAppsTier_11202304.log
  \     73% completed      

Completed Apply...
Tue Nov 20 23:13:40 2012


Do you want to startup the Application Services for VIS? (y/n) [y] : y

Starting application Services for VIS:
Running:
/u01/E-BIZ/inst/apps/VIS_natoth/admin/scripts/adstrtal.sh -nopromptmsg

You are running adstrtal.sh version 120.15.12010000.3

The logfile for this session is located at /u01/E-BIZ/inst/apps/VIS_natoth/logs/appl/admin/log/adstrtal.log
Executing service control script:
/u01/E-BIZ/inst/apps/VIS_natoth/admin/scripts/adopmnctl.sh start
script returned:
****************************************************

You are running adopmnctl.sh version 120.6.12010000.5

Starting Oracle Process Manager (OPMN) ...
opmnctl: opmn started.

adopmnctl.sh: exiting with status 0

adopmnctl.sh: check the logfile /u01/E-BIZ/inst/apps/VIS_natoth/logs/appl/admin/log/adopmnctl.txt for more information ...  


.end std out.

.end err out.

****************************************************


Executing service control script:
/u01/E-BIZ/inst/apps/VIS_natoth/admin/scripts/adalnctl.sh start
script returned:
****************************************************

adalnctl.sh version 120.3

Checking for FNDFS executable.
Starting listener process APPS_VIS.

adalnctl.sh: exiting with status 0


adalnctl.sh: check the logfile /u01/E-BIZ/inst/apps/VIS_natoth/logs/appl/admin/log/adalnctl.txt for more information ...  


.end std out.

.end err out.

****************************************************


Executing service control script:
/u01/E-BIZ/inst/apps/VIS_natoth/admin/scripts/adapcctl.sh start
script returned:
****************************************************

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/E-BIZ/inst/apps/VIS_natoth/logs/appl/admin/log/adapcctl.txt for more information ...  


.end std out.

.end err out.

****************************************************


Executing service control script:
/u01/E-BIZ/inst/apps/VIS_natoth/admin/scripts/adoacorectl.sh start
script returned:
****************************************************

You are running adoacorectl.sh version 120.13

Starting OPMN managed OACORE OC4J instance  ...

adoacorectl.sh: exiting with status 0

adoacorectl.sh: check the logfile /u01/E-BIZ/inst/apps/VIS_natoth/logs/appl/admin/log/adoacorectl.txt for more information ...  


.end std out.

.end err out.

****************************************************


Executing service control script:
/u01/E-BIZ/inst/apps/VIS_natoth/admin/scripts/adformsctl.sh start
script returned:
****************************************************

You are running adformsctl.sh  version 120.16.12010000.3

Starting OPMN managed FORMS OC4J instance  ...
Calling txkChkFormsDeployment.pl to check whether latest FORMSAPP.EAR is deployed...
Program : /u01/E-BIZ/apps/apps_st/appl/fnd/12.0.0/patch/115/bin/txkChkFormsDeployment.pl started @ Thu Nov 29 07:20:21 2012

*** Log File = /u01/E-BIZ/inst/apps/VIS_natoth/logs/appl/rgf/TXK/txkChkFormsDeployment_Thu_Nov_29_07_20_20_2012/txkChkFormsDeployment_Thu_Nov_29_07_20_20_2012.log 

File "/u01/E-BIZ/apps/tech_st/10.1.3/j2ee/forms/applications/forms/formsweb/WEB-INF/lib/frmsrv.jar" exists. Proceeding to check the size...

=============================================
*** Latest formsapp.ear has been deployed ***
=============================================


Program : /u01/E-BIZ/apps/apps_st/appl/fnd/12.0.0/patch/115/bin/txkChkFormsDeployment.pl completed @ Thu Nov 29 07:20:21 2012

Perl script txkChkFormsDeployment.pl got executed successfully



adformsctl.sh: exiting with status 0

adformsctl.sh: check the logfile /u01/E-BIZ/inst/apps/VIS_natoth/logs/appl/admin/log/adformsctl.txt for more information ...  


.end std out.
*** ALL THE FOLLOWING FILES ARE REQUIRED FOR RESOLVING RUNTIME ERRORS
*** Log File = /u01/E-BIZ/inst/apps/VIS_natoth/logs/appl/rgf/TXK/txkChkFormsDeployment_Thu_Nov_29_07_20_20_2012/txkChkFormsDeployment_Thu_Nov_29_07_20_20_2012.log 

.end err out.

****************************************************


Executing service control script:
/u01/E-BIZ/inst/apps/VIS_natoth/admin/scripts/adoafmctl.sh start
script returned:
****************************************************

You are running adoafmctl.sh version 120.8

Starting OPMN managed OAFM OC4J instance  ...

adoafmctl.sh: exiting with status 0

adoafmctl.sh: check the logfile /u01/E-BIZ/inst/apps/VIS_natoth/logs/appl/admin/log/adoafmctl.txt for more information ...  


.end std out.

.end err out.

****************************************************


Executing service control script:
/u01/E-BIZ/inst/apps/VIS_natoth/admin/scripts/adcmctl.sh start
script returned:
****************************************************

You are running adcmctl.sh version 120.17.12010000.5

Starting concurrent manager for VIS ...
Starting VIS_1129@VIS Internal Concurrent Manager
Default printer is noprint

adcmctl.sh: exiting with status 0


adcmctl.sh: check the logfile /u01/E-BIZ/inst/apps/VIS_natoth/logs/appl/admin/log/adcmctl.txt for more information ...  


.end std out.

.end err out.

****************************************************


Executing service control script:
/u01/E-BIZ/inst/apps/VIS_natoth/admin/scripts/jtffmctl.sh start
script returned:
****************************************************

You are running jtffmctl.sh version 120.3

Validating Fulfillment patch level via /u01/E-BIZ/apps/apps_st/comn/java/classes
Fulfillment patch level validated.
Starting Fulfillment Server for VIS on port 9342 ...

jtffmctl.sh: exiting with status 0


.end std out.

.end err out.

****************************************************


All enabled services for this node are started.

adstrtal.sh: Exiting with status 0

adstrtal.sh: check the logfile /u01/E-BIZ/inst/apps/VIS_natoth/logs/appl/admin/log/adstrtal.log for more information ...  

Template configuration disabled.

[Apps DBAs with experience running Vision might be a bit worried about this last step, because of the number of services that can be started by default. I found that the version delivered with the template is reasonably trim, with only a handful of concurrent managers configured. It could probably still be pared down, but the default config won't make your VM choke and fall over.]

That's it! Almost...

A few more notes to get you on your way:

Access the front-end of your shiny new E-Business Suite Release 12.1.3 instance at http://your_hostname:80nn, where nn is the value of the port pool that you chose during the Rapid Clone run (http://natoth.local.org:8042 in my example). The login to use is sysadmin, and the password is the same (but they're pronounced differently)
If you have JRE 7 installed on your workstation, you are going to have problems when launching the Oracle Forms interface, but the web interface ("Self Service") should work without issues. If you're interested in giving the Forms interface a whirl, you will need to roll back to JRE 6.
To start and stop the apps the "quick and easy" way, you can use the scripts found in /u01 on each server: startapps.sh and stopapps.sh on the app server, and startdb.shs and stopdb.sh on the database server. These scripts need to be run as the software owner (oracle, in both cases), not root!

The oracle user accounts are configured to prompt you to change default passwords whenever you log in:

**************************ATTENTION**************************************
This is the first time you are logging in to the system as oracle user   
Oracle recommends that you change the default password for the oracle user
Would you like to change the password now (YES/NO)?
*************************************************************************

Enter YES or NO :

It's not a bad idea to change the paswords, of course. If you're happy with the defaults, however, then just answer NO to all the prompts, and issue the command touch /home/oracle/.passchanged to avoid being prompted on subsequent logins.

To make sure that your environment variables are set correctly when connecting to the apps and db servers as oracle, add the following lines to the .bash_profile:

App server:
echo ". /u01/E-BIZ/apps/apps_st/appl/APPSVIS_`hostname -s`.env" >> ~/.bash_profile
DB Server:
echo ". /u01/E-BIZ/db/tech_st/11.2.0.2/VIS_`hostname -s`.env" >> ~/.bash_profile

More than likely, if you're bothering to go through with all of this nonsense, you're already somewhat familiar with E-Business Suite. If you're new to the administrative side of EBS, however, I humbly recommend a review of Part 3 and Part 4 of the EBS install guide I wrote for oraclenerd.com a while back. It barely scratches the surface, but there's enough there for some basic orientation. After that, you have lots of documentation reading ahead of you!
Finally, if you aren't going to use them in an Oracle VM server, and you're satisfied with the state of your new Vision instance, you can now delete the original disk .img files and reclaim about 300GB of disk space. Just don't delete the .vdi versions by mistake!

Oracle Apps 11i: Back to the future

jpiwowar — Wed, 14 Sep 2011 12:00:39 +0000

A while ago, I posted a few references for installing an 11i Vision instance, noting that it was "something you'll hopefully only need to do once." The fun doesn't stop with installing the software, though. Once you're done with the easy part you're left with:

A desupported version of the database
A desupported version of the Developer 6i tools (yes, even more obsolete than 6i's inherent obsolescence)
JInitiator, which is also, you guessed it, desupported in favor of the standard Sun JDK.

What's the big deal? After all, 11i is in Extended Support mode, so why should it matter if an installed Vision instance has technical components that are a bit long in the tooth? Extended Support or not, there are probably one or two (hah) 11i instances still stubbornly kicking around out there. Presumably those instances have been patched up at least a little bit. If you're installing an 11i Vision instance for educational purposes, it might help to have something that (hopefully) more closely matches the modern state of affairs.

Below are some of my notes from when I dragged my 11i Vision instance forward from 2005 to 2011. Well, sort of. It is still on 32-bit Linux (OEL 5.5 in this case) . But the latest Developer 6i patch set has been applied, the database is at 11.2.0.2, Jinitiator's been replaced by JDK 6, and the AD tools are a bit more up-to-date. If you decide to use these notes, please understand that I intend them to be a rough guide, to save you some research time. I can't commit to detailed support of your effort. This is a reference, not a cookbook.

Reference Notes

Not surprisingly, upgrading 11i components to this decade requires a lot of preparatory reading. Even if you're really familiar with the contents, it's worth revisiting these docs; some of them were changed as recently as July 2011. Here's a list that should cover everything for 32-bit OEL:

Database

Apps

Note 233044.1: About Oracle Applications DBA Minipack 11i.AD.I
Note 783600.1: About Oracle Applications Technology 11i.ATG_PF.H.delta.7 (RUP 7)
Note 858801.1: Known Issues On Top of 11i.ATG_PF.H.delta.7 (RUP7) - 6241631
Note 165195.1: Using AutoConfig to Manage System Configurations with Oracle Applications 11i
Note 316806.1: Oracle Applications Installation Update Notes, Release 11i (11.5.10.2) -- pay particular attention to the post-installation steps for relinking ASCP modules if you're using OEL/Redhat 5.4 and above. You may have already done this when installing Vision, but it's likely that a patch will overwrite adrelinknew.sh.

Forms, Java, and friends

Patches

While you're reading, if you want to get a jump on downloading the patches listed in these documents, I've listed them below. Please validate that the patch numbers still match the documents; the Developer 6i patch document in particular are subject to relatively frequent updates.

Database

4547809 (9.2.0.8)
6880880 (9.2.0.8)
5495695 (9.2.0.8)
5391326 (9.2.0.8)
10098816 (11.2.0.2 RDBMS, parts 1, 2 and 6 only)
10149223
10229719
10165223
9776940

Apps

Forms, Java, and friends

Order of operations

Tying all those notes and patches together looks daunting, but it's not actually that bad. Here's a quick summary of what needs to happen. Make sure to reference the relevant notes for detail. Please recall that we're working on a Vision "playground" here; some of my suggestions for speeding things up may not be comfortable in production.

Start downloading all those fun patches! Here's a script to get you started, though you might need to make adjustments if patch requirements have changed.
Upgrade the database from version 9.2.0.6 to 9.2.0.8, per note 216550.1 to make the 11gR2 upgrade smoother. Doesn't take long, and while the scripts are running, you can work on the next five steps. Just do the database pieces (Section 2, steps 3-9 & 12 should be sufficient) for now.
Set s_discostatus=disabled in the context file to be picked up by the next Autoconfig run; this is 2011 for heaven's sake.
Install the JDK version 6 rpm as root.
Install 11.2.0.2 software and examples, per Note 881505.1
Create NLS data directory per note 881505.1
Apply database patches (9776940,10149223, 10165223, and 10229719) to the 11gR2 ORACLE_HOME.
Bring the newly-patched 9.2.0.8 database up and apply the AD.I.7 patch set. Use the nocompileb option; you have a utlrp step coming later.
Adjust adrelinknew.sh script, per Note 316806.1.
To avoid an error during the next round of patches, make an adjustment to the IGW.IGW_AWARD_BUDGET_S sequence. This can also be fixed with a patch, but why go through the trouble of getting a password-protected patch for this exercise?
```
SQL> alter sequence IGW.IGW_AWARD_BUDGET_S increment by 10000;
SQL> select  IGW.IGW_AWARD_BUDGET_S.nextval from dual;
SQL> alter sequence IGW.IGW_AWARD_BUDGET_S minvalue 10000;
```
Apply the rest of the Apps patches (Merge them all. Yes, seriously). Again, use the nocompiledb option, and noautoconfig as well; the JDK step below will take care of that for you.
Apply Forms 19 patchset and the small pile of related Forms and 8.0.6 ORACLE_HOME patches as documented in Doc ID 125767.1 -- don't forget the relink steps afterward
Run txkrun.pl per section 5.1 of note 401561.1 (use jdk_top=/usr/java/latest)
Regenerate jar files for jdk6 (use force option)
Deploy the JRE to the apps tier per note 290807.1
Perform the database upgrade per note 881505.1. There's a lot to do there; follow the note closely.
Find something else to do with your day; recompiling 140K invalid objects might take a while.

Next steps ("Exercise for the Reader")

What, you thought you were done? Now that all of the technical bits are more-or-less up-to-date, you can turn your attention to security patches and applications updates. That may seem like a lot for a playground Vision instance, and maybe it is. It's also too much to cover in this blog post, but check the following notes if you're an overachiever:

Database CPU/PSU patching and Oracle Applications CPU patches. As an example of what you're looking for is Note 1315202.1: Oracle E-Business Suite Releases 11i and 12 Critical Patch Update Knowledge Document (July 2011). This is a quarterly "moving target," so that link will eventually be stale; check My Oracle Support for updates.
Oracle Applications Extended Support Baseline patches. If you want to be really, really up-to-date, visit Note 883202.1: Patch Requirements for Extended Support of Oracle E-Business Suite Release 11.5.10 .

Changes

jpiwowar — Mon, 12 Sep 2011 05:00:39 +0000

Fall is my favorite time of year. In part, it's because of the weather where I grew up: muggy Pennsylvania summer and late-summer thunderstorms give way to crisp mornings, sometimes-warmish afternoons, and cool evenings. The air smells of wood smoke and dried leaves, and the colors of the sky and the trees are fantastic.

Autumn weather is a bit less epic where I live now, but there's another, non-weather-related reason that I like this time of year: for me, it's a time of new starts. I know springtime is supposed to be the time when everything's renewed, but for me it's all about fall. I was one of those insufferably nerdy kids who actually looked forward to the start of school, from grade 1 through university: new books, new courses, new people (okay, maybe that last one is a bit of a stretch ). That carried through to my so-called adult life as well: A lot of the biggest and best changes in the last decade or two (with two very notable exceptions) have also taken place at this time of year, including the starts of many of my jobs.

Hey look, I'm getting to the point after only two paragraphs! New record! This fall, I'm making another transition: Starting today, I begin the "on-boarding" process at Pythian, as a member of one of their ERP teams. In a way, it feels as much like starting school as it does a new job. This move represents my biggest career challenge to date, and I look forward to learning a ton (and contributing a ton) in the company of a lot of smart, talented people. Since I'm going to be packing my brain full of new things over the next 3 weeks, I expect that the only blog activity here will be from pre-scheduled posts, drafts that I'd been putting off finishing until recently. Once I'm up to speed on the new system, you'll hopefully be hearing more from me on the Pythian blog!

Man, this is exciting. I sure hope the big kids don't shove me in a locker or something.

Fun with EBS APIs, part II: Users and responsbilities (FND_USER_PKG)

jpiwowar — Thu, 25 Aug 2011 23:00:51 +0000

In my previous post, I demo'ed some simple scripts to make changes to E-Business Suite profile options, using the API provided by the FND_PROFILE package. This post will look at the FND_USER_PKG package (no relation to the "ABS brake system"). While my examples only reference user creation and responsibility assignment, it's really worth reviewing the contents of the rest of the package; there's lots of useful stuff in there.

First things first

Disclaimer time! Are you surprised? I hope you're not surprised. Am I really copying this section from the previous post? Yup, I am!

The example code presented in this series of blog posts uses APIs written by Oracle to manipulate data in the E-Business Suite database. This does NOT imply that the code in these posts is endorsed or supported by Oracle.
The examples in this series of blog posts are presented as demonstration code only, and are neither guaranteed to work in your environment, nor in all cases. Since my environments are not the same as yours, I cannot offer support in the event that this code does not work for you. You are encouraged to make whatever modifications are necessary to make these examples work for you.
The scripts used in these examples were developed and implemented in test and development environments, where change management rules are often more lax. Be sure to test thoroughly, and to consult your organization's change management procedures, before attempting to use this code in production.

Short form: There's data-changing code in these posts. Oracle won't support you if you use the code; I can't support you either. Before running this code in production without testing and approval, you should probably prepare a resumé and, optionally, schedule an appointment with a psychiatrist.

Example 1: Duplicating a user's responsibilities

This is probably most relevant in test/dev/CRP environments, where it's not uncommon to be asked, "Can you please set up Fred to have the same responsibilities as Jane in the UAT environment?" or "Can you please set up 12 users in the Training environment, all with the same responsibilities?" I've written a script called ebs_dupresp that will duplicate all of a user's directly-assigned responsibilities to a target user, optionally creating the user record:

/* ebs_dupresp.sql Contact: John Piwowar Purpose: Grant one user's responsbilities to another, optionally creating the target user Notes: ... */ SET serveroutput on size 1000000 format word_wrapped SET verify off feedback off --Readability improvements. First time for everything. DEFINE src=&1 DEFINE targ=&2 PROMPT Source user: &&src PROMPT Target user: &&targ PROMPT CTRL-C might be a good idea if these are incorrect/reversed. ACCEPT create_user_pref prompt 'Create &&targ if non-existent? (y/N) ' DECLARE src_user APPLSYS.FND_USER.USER_NAME%TYPE := upper('&&src'); targ_user APPLSYS.FND_USER.USER_NAME%TYPE := upper('&&targ'); create_flag BOOLEAN := FALSE; targ_userid APPLSYS.FND_USER.USER_ID%TYPE := NULL; --Cursor to pull source user's responsbilities --Note: does not pull end-dated responsibilities, since they were -- presumably end-dated for a reason. Also retains forward-looking -- end dates CURSOR cGetResps (user_in APPLSYS.FND_USER.USER_NAME%TYPE) IS SELECT a.application_short_name app , r.responsibility_key key , t.responsibility_name respname , s.security_group_key secgrp , g.end_date FROM apps.fnd_user_resp_groups_direct g , applsys.fnd_responsibility r , applsys.fnd_user u , applsys.fnd_application a , applsys.fnd_security_groups s , applsys.fnd_responsibility_tl t WHERE g.responsibility_id = r.responsibility_id AND g.security_group_id = s.security_group_id AND g.responsibility_application_id = r.application_id AND g.responsibility_application_id=t.application_id AND g.responsibility_id=t.responsibility_id AND r.application_id = a.application_id AND u.user_name = user_in AND g.user_id = u.user_id AND (g.end_date > SYSDATE or g.end_date is null); BEGIN IF upper(substr('&&create_user_pref',1,1)) = 'Y' THEN create_flag := TRUE; END IF; --create_user_pref=Y --Create user if needed IF create_flag AND NOT (apps.fnd_user_pkg.userExists(targ_user)) THEN targ_userid := apps.fnd_user_pkg.createuserid ( x_user_name => targ_user , x_owner => 'CUST' , x_unencrypted_password => 'chang3m3' , x_description => 'Autocreated by ebs_dupresps.sql' ); --Probably won't ever get to this point, but just to be safe... IF targ_userid IS NULL THEN dbms_output.put_line('Failed to create ' || targ_user || ', cannot continue' ); RETURN; ELSE dbms_output.put_line('User ' || targ_user || ' created with id ' || targ_userid); END IF; --targ_userid ELSIF create_flag AND (apps.fnd_user_pkg.userExists(targ_user)) THEN dbms_output.put_line('User ' || targ_user || ' already exists.'); ELSIF NOT create_flag AND NOT (apps.fnd_user_pkg.userExists(targ_user)) THEN dbms_output.put_line('User ' || targ_user || ' does not exist and create flag not specified.'); RETURN; END IF; --create_flag --Add responsibilities FOR new_resp IN cGetResps(src_user) LOOP dbms_output.put_line('Adding responsibility ' || new_resp.respname); apps.fnd_user_pkg.AddResp( username => targ_user , resp_app => new_resp.app , resp_key => new_resp.key , security_group => new_resp.secgrp , description => 'Added by ebs_dupresps.sql' , start_date => SYSDATE , end_date => new_resp.end_date ); END LOOP; --new_resp END; --ebs_dupresps /

Here's a two-part demo. For the first part, I've assumed that I just got the following email from my boss:

John, Please create an EBS account in the test environment for the new DBA, Devnul Larson. Just give him the same responsibilities that you have, for now.

APPS@R12VIS(11.1.0.7)>@ebs_dupresp johnp devnull
Source user: johnp
Target user: devnull
CTRL-C might be a good idea if these are incorrect/reversed.
Create devnull if non-existent? (y/N) y
User DEVNULL created with id 1013423
Adding responsibility System Administrator
Adding responsibility Oracle Diagnostics Tool
APPS@R12VIS(11.1.0.7)>@lresp johnp
Currently active responsibilities for user johnp

APP		     RESPNAME				      END_DATE
-------------------- ---------------------------------------- ---------------
FND		     Application Diagnostics		      NONE
FND		     Application Diagnostics		      NONE
JTF		     Oracle Diagnostics Tool		      NONE
SYSADMIN	     System Administrator		      NONE
APPS@R12VIS(11.1.0.7)>@lresp devnull
Currently active responsibilities for user devnull

APP		     RESPNAME				      END_DATE
-------------------- ---------------------------------------- ---------------
FND		     Application Diagnostics		      NONE
FND		     Application Diagnostics		      NONE
JTF		     Oracle Diagnostics Tool		      NONE
SYSADMIN	     System Administrator		      NONE

Later that day, another email arrives:

On second thought, just assign all of SYSADMIN's responsibilities to the new guy. BTW, why are you assigning all the new tickets to DEVNULL?

APPS@R12VIS(11.1.0.7)>@ebs_dupresp sysadmin devnull
Source user: sysadmin
Target user: devnull
CTRL-C might be a good idea if these are incorrect/reversed.
Create devnull if non-existent? (y/N) y
User DEVNULL already exists.
Adding responsibility Application Developer
Adding responsibility System Administrator
Adding responsibility Application Developer Common Modules
Adding responsibility System Administration
Adding responsibility Preferences SSWA
Adding responsibility Workflow
(I'll spare you the full list)

It's worth noting that this is a fairly primitive approach, particularly when it comes to the user creation piece (no LDAP integration, no association of user record with an HR person/party record, etc). The ebs_dupresp script also doesn't touch things like profile options, so it shouldn't be considered a "user clone" script. You could probably accomplish this work with FNDLOAD or a third-party tool like DataLoader, but I'd rather write SQL.

Example 2: Adding a list of responsibilities to a user

Another common need is adding responsibilities to multiple users. Maybe you want to add the Application Developer responsibility to a handful of new consultants, or grant the Oracle Diagnostics responsibility to some freshly trained business analysts and power users. With a little bit of setup, it's pretty easy to do bulk responsibility assignments in a way that's easily repeated across multiple instances. Here's an example script, ebs_bulkresp, that does the job:

/* ebs_bulkresp.sql Contact: John Piwowar Purpose: Add 1+ responsibilities to 1+ users Requires: desired action (validate or apply) as script argument Table xxrespload, of the format: Name Type ----------------------------- -------------------- USER_NAME VARCHAR2(100) APP_SHORTNAME VARCHAR2(50) RESP_NAME VARCHAR2(100) END_DATE DATE Notes: Actually, validation happens no matter what, but it never hurts to be explicit */ SET serveroutput on size 1000000 format word_wrapped SET verify off feedback off col user_name for a20 col app for a10 col end_date for a15 col resp_name for a35 PROMPT Working with following records in table xxrespload: SELECT user_name , app_shortname app , resp_name , nvl(to_char(end_date,'DD-MON-YYYY'),'NONE') end_date FROM xxrespload ORDER BY user_name , resp_name / DECLARE action VARCHAR2(8) := upper('&1'); TYPE RespPair IS RECORD ( app APPLSYS.FND_APPLICATION.APPLICATION_SHORT_NAME%TYPE , resp APPLSYS.FND_RESPONSIBILITY_TL.RESPONSIBILITY_NAME %TYPE ); TYPE UserList IS TABLE OF APPLSYS.FND_USER.USER_NAME%TYPE; TYPE RespList IS TABLE OF RespPair; BadUsers UserList; BadResps RespList; --N.B. Annoying security_group_id kludge. NULL=STANDARD, woohoo! CURSOR cGetBulkResps IS SELECT x.user_name , x.app_shortname , x.resp_name , r.responsibility_key resp_key , s.security_group_key secgrp , x.end_date FROM xxrespload x JOIN applsys.fnd_application a on (a.application_short_name=x.app_shortname) JOIN applsys.fnd_responsibility_tl t on ( a.application_id=t.application_id and x.resp_name=t.responsibility_name) JOIN applsys.fnd_responsibility r on ( t.application_id=r.application_id and t.responsibility_id=r.responsibility_id) JOIN applsys.fnd_security_groups s on (nvl(r.security_group_id,0)=s.security_group_id) WHERE x.user_name is not null; BEGIN dbms_output.put_line('Starting validation...'); --Validate supplied users SELECT DISTINCT user_name BULK COLLECT INTO BadUsers FROM xxrespload x WHERE NOT EXISTS (SELECT user_name FROM fnd_user u WHERE u.user_name = x.user_name AND (u.end_date is null OR u.end_date > SYSDATE) ); --Validate app-resp pairs SELECT DISTINCT app_shortname , resp_name BULK COLLECT INTO BadResps FROM xxrespload x WHERE NOT EXISTS ( SELECT a.application_short_name , r.responsibility_name FROM applsys.fnd_responsibility_tl r JOIN applsys.fnd_application a using (application_id) WHERE a.application_short_name = x.app_shortname AND r.responsibility_name = x.resp_name ); IF BadUsers.COUNT > 0 THEN dbms_output.put_line('Found ' || BadUsers.COUNT || ' invalid user names.'); FOR i IN BadUsers.FIRST .. BadUsers.LAST LOOP dbms_output.put_line('User ' || nvl(BadUsers(i),'') || ' does not exist or is end-dated.' ); END LOOP; --BadUsers; ELSE dbms_output.put_line('All users valid.'); END IF; --BadUsers.COUNT IF BadResps.COUNT > 0 THEN dbms_output.put_line('Found ' || BadResps.COUNT || ' invalid app-responsibility pairs.'); FOR i IN BadResps.FIRST .. BadResps.LAST LOOP dbms_output.put_line('Responsibility ' || nvl(BadResps(i).resp,'') || ' invalid for app ' || nvl(BadResps(i).app,'') || ', or vice versa.' ); END LOOP; --BadResps ELSE dbms_output.put_line('All app-responsibility pairs valid'); END IF; --BadResps.COUNT --Okay, now let's actually do stuff IF action = 'APPLY' THEN dbms_output.put_line('Adding responsibilities...'); FOR new_resp IN cGetBulkResps LOOP dbms_output.put_line('User: ' || new_resp.user_name || ' App: ' || new_resp.app_shortname || ' Resp: ' || new_resp.resp_name ); apps.fnd_user_pkg.AddResp( username => new_resp.user_name , resp_app => new_resp.app_shortname , resp_key => new_resp.resp_key , security_group => new_resp.secgrp , description => 'Added by ebs_bulkresps.sql' , start_date => SYSDATE , end_date => new_resp.end_date ); END LOOP; --cGetBulkResps ELSE dbms_output.put_line('Invoke script with APPLY to add responsibilities'); END IF; --action='APPLY'; END; --ebs_bulkresp /

As a demo, here's another theoretical work request:

Please assign Application Developer and Oracle Diagnostics responsibilities to users SBERG, YWATSON, and WPARKER. SBERG will also need Functional Administrator. WPARKER will be rolling off the project at the end of the year, so please end-date his responsibilities accordingly. These changes are approved for both development environments and all three CRP instances.

After loading the necessary information into a "staging table" (actually a simply-formatted external table), away we go:

APPS@R12VIS(11.1.0.7)>@ebs_bulkresp validate
Working with following records in table xxrespload:

USER_NAME	     APP	RESP_NAME			    END_DATE
-------------------- ---------- ----------------------------------- ---------------
SBERG		     FND	Application Developer		    NONE
SBERG		     FND	Functional Administrator	    NONE
SBERG		     JTF	Oracle Diagnostics Tool 	    NONE
WPARKER 	     FND	Application Developer		    31-DEC-2011
WPARKER 	     JTF	Oracle Diagnostics Tool 	    31-DEC-2011
YWATSON 	     FND	Application Developer		    NONE
YWATSON 	     JTF	Oracle Diagnostics Tool 	    NONE
Starting validation...
All users valid.
All app-responsibility pairs valid
Invoke script with APPLY to add responsibilities
APPS@R12VIS(11.1.0.7)>@ebs_bulkresp apply
Working with following records in table xxrespload:

USER_NAME	     APP	RESP_NAME			    END_DATE
-------------------- ---------- ----------------------------------- ---------------
SBERG		     FND	Application Developer		    NONE
SBERG		     FND	Functional Administrator	    NONE
SBERG		     JTF	Oracle Diagnostics Tool 	    NONE
WPARKER 	     FND	Application Developer		    31-DEC-2011
WPARKER 	     JTF	Oracle Diagnostics Tool 	    31-DEC-2011
YWATSON 	     FND	Application Developer		    NONE
YWATSON 	     JTF	Oracle Diagnostics Tool 	    NONE
Starting validation...
All users valid.
All app-responsibility pairs valid
Adding responsibilities...
User: YWATSON App: FND Resp: Application Developer
User: WPARKER App: FND Resp: Application Developer
User: SBERG App: FND Resp: Application Developer
User: SBERG App: FND Resp: Functional Administrator
User: YWATSON App: JTF Resp: Oracle Diagnostics Tool
User: WPARKER App: JTF Resp: Oracle Diagnostics Tool
User: SBERG App: JTF Resp: Oracle Diagnostics Tool
APPS@R12VIS(11.1.0.7)>@lresp ywatson
Currently active responsibilities for user ywatson

APP		     RESPNAME				      END_DATE
-------------------- ---------------------------------------- ---------------
FND		     Application Developer		      NONE
FND		     Application Diagnostics		      NONE
PSB		     Budget User - Accounting Dept, Progress  NONE
SQLGL		     General Ledger Super User, Progress S&L  NONE
ICX		     Internet Procurement, Progress S&L       NONE
JTF		     Oracle Diagnostics Tool		      NONE
SQLAP		     Payables Manager, Progress S&L	      NONE
ICX		     Preferences SSWA			      NONE
FND		     Workflow User, Progress S&L	      NONE
APPS@R12VIS(11.1.0.7)>@lresp sberg
Currently active responsibilities for user sberg

APP		     RESPNAME				      END_DATE
-------------------- ---------------------------------------- ---------------
FND		     Application Developer		      NONE
FND		     Application Diagnostics		      NONE
FND		     Functional Administrator		      NONE
IBE		     IBE_CUSTOMER			      NONE
JTF		     Oracle Diagnostics Tool		      NONE
ICX		     Preferences SSWA			      NONE
APPS@R12VIS(11.1.0.7)>@lresp wparker
Currently active responsibilities for user wparker

APP		     RESPNAME				      END_DATE
-------------------- ---------------------------------------- ---------------
FND		     Application Developer		      31-DEC-2011
FND		     Application Diagnostics		      31-DEC-2011
JTF		     Oracle Diagnostics Tool		      31-DEC-2011
ICX		     Preferences SSWA			      NONE
WMS		     Whse Mgmt Mobile User, Vision Operations NONE
		      (USA)

It's rare that you'll get a request that also includes the name of the application to which a responsibility belongs. If you don't keep a mental inventory of valid pairs of application and responsibility names (or maybe that's just me?), here's a quick way to look up that sort of info:

/* iresp.sql Contact: John Piwowar Purpose: List useful identifier for EBS responsibilities Notes: Provide fragment of responsbility name */ col responsibility_name for a50 col app_name for a10 col app_id for 999999 col resp_id for 999999 set pagesize 9999 verify off select application_id app_id , a.application_short_name app_name , r.responsibility_id resp_id , r.responsibility_name from applsys.fnd_responsibility_tl r join applsys.fnd_application a using (application_id) where upper(r.responsibility_name) like upper('%&1%') order by responsibility_name /

Fun with EBS APIs, part I: Profile options (FND_PROFILE)

jpiwowar — Wed, 24 Aug 2011 22:30:36 +0000

The E-Business Suite APIs are not just the realm of developers and data migration specialists. There are a number of APIs, particularly in the FND module, that can make life a lot easier for Apps DBAs. In my next two (maybe three, if I'm feeling ambitious) posts, I'll provide some examples of how to use PL/SQL to perform tasks that normally require slogging around in the EBS Forms interface.

First things first

Disclaimer time! Are you surprised? I hope you're not surprised.

The example code presented in this series of blog posts uses APIs written by Oracle to manipulate data in the E-Business Suite database. This does NOT imply that the code in these posts is endorsed or supported by Oracle.
The examples in this series of blog posts are presented as demonstration code only, and are neither guaranteed to work in your environment, nor in all cases. Since my environments are not the same as yours, I cannot offer support in the event that this code does not work for you. You are encouraged to make whatever modifications are necessary to make these examples work for you.
The scripts used in these examples were developed and implemented in test and development environments, where change management rules are often more lax. Be sure to test thoroughly, and to consult your organization's change management procedures, before attempting to use this code in production.

Okay, moving on…

Now that I have all that out of the way, here are two basic examples demonstrating the use of FND_PROFILE. As with most PL/SQL code in the E-Business Suite database, FND_PROFILE is owned by the APPS schema. Browse through it, there's interesting stuff in there. Please note that the set_ebs_profile function referenced in these examples is my hackwork (defined in ebs_def_setprof.sql), not part of the Oracle-supplied API.

Example 1: Setting profile options after cloning

Autoconfig takes care of setting a lot of site-level profile options after cloning, but there may be a few instances when you need to change settings manually before releasing the instance to users. The following script is a template that I use, adding calls to set_ebs_profile as needed.

Example run:

APPS@R12VIS(11.1.0.7)>@ebs_postclone_prof
New SID (e.g. MYCLONE,NEWVIS, etc): JPR12VIS
New Color Scheme (TEAL, RED, KHAKI, OLIVE, PURPLE, TITANIUM): PURPLE
Instance type (e.g. Test, Dev, Sandbox): Visionary
Data source note (e.g. 'Prod clone, data as of DD-MON-YYYY'): Timeless data
Previous value of FND_COLOR_SCHEME: KHAKI
SUCCESS: Set FND_COLOR_SCHEME to PURPLE
Previous value of SITENAME: Vision Applications
SUCCESS: Set SITENAME to Visionary (JPR12VIS) - Timeless data

Example 2: Setting multiple profile options for a user

In a development environment, you may be asked to set debug profile options for particular modules. It can be handy to have a script that does this for a particular user, ensure that no one "accidentally" sets debug options at the site level (oh, I know, it's rare, but still…). It's ridiculously easy to put together a quick script to take care of this:

Example run:

APPS@R12VIS(11.1.0.7)>@om_debug jpiwowar 5
Setting OM debug for jpiwowar
Previous value of OE_DEBUG_LOG_DIRECTORY: /usr/tmp
Skipping OE_DEBUG_LOG_DIRECTORY, no change
Target user: jpiwowar
Previous value of ONT_DEBUG_LEVEL: 0
SUCCESS: Set ONT_DEBUG_LEVEL to 5

The interesting bits

The code that defines the procedure set_ebs_profile can be found below. I've purposely written the script to be plugged into anonymous PL/SQL blocks instead of creating a procedure, because deployment of custom code is not always permitted.

Note that set_ebs_profile expects the internal name for a profile option, instead of the "user-readable" name you'd normally see in the Forms interface. It's useful to become familiar with a query similar to the following. Pay attention to the user_enabled_flag, or you might spend time wondering why your changes aren't sticking.

/* iprof.sql Contact: John Piwowar Purpose: List useful identifiers for EBS profile options Notes: Prompts for fragment of responsibility name */ col prof_name for a40 col prof_code for a25 col user_enabled for a7 col resp_id for 999999 set pagesize 9999 verify off select profile_option_name prof_code , t.user_profile_option_name prof_name , o.user_enabled_flag user_enabled from fnd_profile_options o join fnd_profile_options_tl t using (profile_option_name) where upper(t.user_profile_option_name) like upper('%&1%') order by user_profile_option_name /

APPS@R12VIS(11.1.0.7)>@iprof "OM: Debug"

PROF_CODE	              PROF_NAME                                USER_EN
------------------------- ---------------------------------------- -------
ONT_DEBUG_LEVEL           OM: Debug Level                          Y
OE_DEBUG_LOG_DIRECTORY    OM: Debug Log Directory                  N

Oracle Instant Client on OS X Lion: 32-bit only, for now

jpiwowar — Mon, 01 Aug 2011 21:50:32 +0000

This is already making the rounds, but I figured I'd add my voice to the chorus, to maximize the likelihood that people find the information that they need.

If you're an ORACLENERD and a relatively early adopter of new versions of Mac OS X, you might notice that the 64-bit version of the OS X Instant Client does not work under OS X Lion. Attempts to use the 64-bit sqlplus binary and libraries result in a segmentation fault. Boo. The only known solutions for command-line sqlplus from Lion at the this point seem to be use the 32-bit client, or stay on Snow Leopard. We're still in the early days of general availability for Lion, so perhaps another solution will surface.

I suspect that this doesn't mean good things for the full-blown database install, but I haven't checked that out yet (and I don't know when/if I will; my native OS X install of 10g had been gathering dust for quite a while, and did not make the move to my new MBP).

References:

Raimonds' (who else?) post on the oracle-enhanced Ruby adapter Google group
OTN forum thread
Download links: 32-bit Instant Client Basic and SQL*Plus bundles