Platform: Red Hat Enterprise Linux Server
Application Version: e-Business Suite 12.1+

Symptoms:

Patch runs fine until it begins to slow down and hang partway through the java loader (e.g., XDOLoader) steps for no apparent reason. There are no indications that the hang is being caused by a database performance or locking issue.

Troubleshooting:

AD patch worker log error:

Error: Error connecting to database "jdbc:oracle:thin:APPS/xxxxxx@(DESCRIPTION=(LOAD_BALANCE=YES)(FAILOVER=YES)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=YOUR_HOST)(PORT=1521)))(CONNECT_DATA=(SID=YOUR_SID)))"
Io exception: Connection reset

Run jstack on the hanging java process:

"main" prio=10 tid=0x08937000 nid=0x22ea runnable [0xf73e1000]
java.lang.Thread.State: RUNNABLE
at java.io.FileInputStream.readBytes(Native Method)
at java.io.FileInputStream.read(FileInputStream.java:199)
at java.io.BufferedInputStream.read1(BufferedInputStream.java:256)
at java.io.BufferedInputStream.read(BufferedInputStream.java:317)
- locked <0xf29b25a0> (a java.io.BufferedInputStream)
at java.io.BufferedInputStream.fill(BufferedInputStream.java:218)
at java.io.BufferedInputStream.read1(BufferedInputStream.java:258)
at java.io.BufferedInputStream.read(BufferedInputStream.java:317)
- locked <0xf29b2370> (a java.io.BufferedInputStream)
at sun.security.provider.SeedGenerator$URLSeedGenerator.getSeedByte(SeedGenerator.java:453)
at sun.security.provider.SeedGenerator.getSeedBytes(SeedGenerator.java:123)
at sun.security.provider.SeedGenerator.generateSeed(SeedGenerator.java:118)
at sun.security.provider.SecureRandom.engineGenerateSeed(SecureRandom.java:114)
at sun.security.provider.SecureRandom.engineNextBytes(SecureRandom.java:171)
- locked <0xf29b1fd0> (a sun.security.provider.SecureRandom)
at java.security.SecureRandom.nextBytes(SecureRandom.java:433)
- locked <0xf29b2250> (a java.security.SecureRandom)
at oracle.security.o5logon.O5LoginClientHelper.generateOAuthResponse(Unknown Source)
at oracle.jdbc.driver.T4CTTIoauthenticate.marshalOauth(T4CTTIoauthenticate.java:457)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:367)
at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:510)
at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:203)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:33)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:510)
at java.sql.DriverManager.getConnection(DriverManager.java:582)
at java.sql.DriverManager.getConnection(DriverManager.java:185)
at oracle.apps.xdo.oa.util.XDOLoader.initAppsContext(XDOLoader.java:558)
at oracle.apps.xdo.oa.util.XDOLoader.init(XDOLoader.java:455)
at oracle.apps.xdo.oa.util.XDOLoader.(XDOLoader.java:413)
at oracle.apps.xdo.oa.util.XDOLoader.main(XDOLoader.java:2250)

Check /dev/random entropy:

cat /proc/sys/kernel/random/entropy_avail
NOTE: Higher numbers are better. The patch will begin to slow down or hang whenever entropy is ~50 or less.

Explanation:

The java process depends on the /dev/random device to provide random numbers to the SecureRandom Java class. If /dev/random runs out of random numbers, the patch workers calling SecureRandom hang until enough random numbers are available.

Solutions:
NOTE: Pick one of the solutions below. Solution number 1 is my preferred solution, since it is specific to the e-Business Suite and should not affect other processes on the server.

1. Search for all jre/lib/security/java.security files and replace:

   securerandom.source=file:/dev/random
   with
   securerandom.source=file:/dev/urandom

2. Run the rngd daemon to seed /dev/random with random numbers:
   Install the rngd-utils package in RedHat 5 or kernel-utils in RedHat 4.
   rngd -r /dev/urandom -o /dev/random -f -t 1
3. Replace the /dev/random device with /dev/urandom. (Not recommended for security reasons.)

   sudo mv /dev/random /dev/random.bak
sudo ln -s /dev/urandom /dev/random

References:

• http://bugs.sun.com/view_bug.do?bug_id=4705093
• Java SecureRandom Issue Causing Hangs [ID 555036.1]
  NOTE: This is the original note that led me in the right direction, but it is no longer available.
• 12.1.1 UPGRADE FROM 11.5.10.2 VERY SLOW XDOLOADER TASK HANGS [ID 1065393.1]
• Wfxload.Class is Running Very Slow When Upgrading to 12.1.1 [ID 1158024.1]
• How to increase entropy pool on a 2.6 kernel RHEL/Fedora system without keyboard/mouse.

* The EBS boot volumes must be EBS Snapshots, which cost more than regular EBS volumes. (http://aws.amazon.com/ec2/#pricing)
* The EBS boot volumes currently do not work within the Virtual Private Cloud (VPC) infrastructure. (http://aws.amazon.com/vpc/faqs/#45)

To work around these two issues, it is possible to “pivot” from a normal AMI’s local root volume (/dev/sda1) to an arbitrary EBS volume that contains a full OS installation. An additional benefit is that a very small AMI may be used to launch the EBS backed instance, so that the instance launches much faster than a large AMI.

The goal of this post is to provide instructions on how to build a small AMI that is capable of launching a full-blown persistent Linux installation that is backed on EBS storage. BusyBox, “The Swiss Army Knife of Embedded Systems”, provides a solid foundation for our small AMI. To build a BusyBox AMI and an instance backed by EBS, simply follow these directions:

1. Start an instance from an AMI that is running the desired kernel and software configuration. (You can use an existing public AMI or a custom AMI that you created. Personally, I prefer to create my own AMI’s, so I know what is in them.)

2. Log into the instance.

3. Download the latest version of BusyBox.

wget “http://busybox.net/downloads/busybox-1.15.3.tar.bz2″

4. Create a busyroot directory.

mkdir busyroot

5. Extract BusyBox.

bunzip2 busybox-1.15.3.tar.bz2
tar xvf busybox-1.15.3.tar

6. Configure BusyBox. (You can experiment here to reduce the image size, but the configuration listed below works.)

cd busybox-1.15.3
make config
Select “y” for the STATIC option and the default values for everything else.
Build BusyBox as a static binary (no shared libs) (STATIC) [N/y/?] y

7. Make and install BusyBox.

make CONFIG_PREFIX=$HOME/busyroot install
chmod 4755 $HOME/busyroot/bin/busybox

8. Create required directories.

cd $HOME/busyroot
mkdir dev sys etc proc mnt mnt/new-root

9. Create the necessary devices. (We will use /dev/sdj for the EBS volume, but this could be any block device not used by the normal AMI.)

MAKEDEV -d $HOME/busyroot/dev -x sdj
MAKEDEV -d $HOME/busyroot/dev -x console
MAKEDEV -d $HOME/busyroot/dev -x null
MAKEDEV -d $HOME/busyroot/dev -x zero

10. Create the init file.

mv $HOME/busyroot/sbin/init $HOME/busyroot/sbin/init.orig
cat <<’EOL’ > $HOME/busyroot/sbin/init
#!/bin/busybox sh
PATH=/bin:/usr/bin:/sbin:/usr/sbin
NEWDEV=”/dev/sdj”
NEWTYP=”ext3″
NEWMNT=”/mnt/new-root”
OLDMNT=”/mnt/old-root”
OPTIONS=”noatime,ro”
SLEEP=10

echo “Remounting writable.”
mount -o remount,rw /
[ ! -d $NEWMNT ] && echo “Creating directory $NEWMNT.” && mkdir -p $NEWMNT

while true ; do
echo “sleeping…”
sleep $SLEEP
echo “Trying to mount $NEWDEV writable.”
mount -t $NEWTYP -o rw $NEWDEV $NEWMNT || continue
echo “Mounted.”
break;
done

[ ! -d $NEWMNT/$OLDMNT ] && echo “Creating directory $NEWMNT/$OLDMNT.” && mkdir -p $NEWMNT/$OLDMNT

echo “Remounting $NEWMNT $OPTIONS.”
mount -o remount,$OPTIONS $NEWMNT

echo “Trying to pivot.”
cd $NEWMNT
pivot_root . ./$OLDMNT

for dir in /dev /proc /sys; do
echo “Moving mounted file system ${OLDMNT}${dir} to $dir.”
mount –move ./${OLDMNT}${dir} ${dir}
done

echo “Trying to chroot.”
exec chroot . /bin/sh -c “umount ./$OLDMNT; exec /sbin/init $*” < /dev/console > /dev/console 2>&1
EOL

chmod 755 $HOME/busyroot/sbin/init

11. Create the fstab file.

cat <<’EOL’ > $HOME/busyroot/etc/fstab
/dev/sda1 / ext3 defaults 1 1
none /dev/pts devpts gid=5,mode=620 0 0
none /proc proc defaults 0 0
none /sys sysfs defaults 0 0
EOL

12. Create a 4MB loopback file.

cd
dd if=/dev/zero of=busybox.fs bs=1M count=4
mkfs.ext3 busybox.fs

13. Mount the loopback file.

mkdir $HOME/busyimg
mount -o loop $HOME/busybox.fs $HOME/busyimg

14. Copy the staged files and directories to the image. (Technically, the BusyBox image could have been built directly in $HOME/busyimg, but we were not sure how big the image was going to be.)

cp -rp $HOME/busyroot/* $HOME/busyimg

15. Un-mount the image.

sync
umount -d $HOME/busyimg

16. Set environment variables.

export EC2_HOME=/opt/ec2-api-tools
export EC2_CERT=/path/to/your/cert.pem
export EC2_PRIVATE_KEY=/path/to/your/pk.pem
export AWS_ACCOUNT_NUMBER=”NNNN-NNNN-NNNN”
export AWS_ACCESS_KEY_ID=your_key
export AWS_SECRET_ACCESS_KEY=your_secret_key
export EC2_BUCKET=”your_bucket”
export JAVA_HOME=/usr/java/default
export ARCH=`uname -i`
export AKI=`curl -s http://169.254.169.254/latest/meta-data/kernel-id`
export ARI=`curl -s http://169.254.169.254/latest/meta-data/ramdisk-id`
export INSTANCE_ID=`curl -s http://169.254.169.254/latest/meta-data/instance-id`
export AVAIL_ZONE=`curl -s http://169.254.169.254/latest/meta-data/placement/availability-zone`
export SEC_GROUP=`curl -s http://169.254.169.254/latest/meta-data/security-groups`
export PUB_KEY=`wget -q -O – “http://169.254.169.254/latest/meta-data/public-keys” | awk -F= ‘{print $2}’`

17. Bundle the image.

ec2-bundle-image -i $HOME/busybox.fs -d /tmp -k $EC2_PRIVATE_KEY -c $EC2_CERT -u $AWS_ACCOUNT_NUMBER -r $ARCH –kernel $AKI –ramdisk $ARI

18. Upload the image.

ec2-upload-bundle -b $EC2_BUCKET -m /tmp/busybox.fs.manifest.xml -a $AWS_ACCESS_KEY_ID -s $AWS_SECRET_ACCESS_KEY

19. Register the AMI.

BUSYBOX_AMI=`ec2-register “$EC2_BUCKET/busybox.fs.manifest.xml” | awk ‘{print $2}’`
echo “BUSYBOX_AMI: $BUSYBOX_AMI”

20. Create an EBS volume of the desired size (10G or more) in the desired availability zone.

VOLUME_ID=`ec2-create-volume -s 10 -z $AVAIL_ZONE | awk ‘{print $2}’`
echo “VOLUME_ID: $VOLUME_ID”

21. Attach the volume to the current instance as /dev/sdj.

ec2-attach-volume $VOLUME_ID -i $INSTANCE_ID -d /dev/sdj

22. Create an EXT3 file system on /dev/sdj.

mkfs.ext3 /dev/sdj

22. Mount the EBS volume.

mkdir /mnt/ebs_boot
mount /dev/sdj /mnt/ebs_boot

23. Copy the current AMI to the EBS volume.

rsync -avHx / /mnt/ebs_boot

24. Fix the /etc/fstab file.

vi /mnt/ebs_boot/etc/fstab
Remove the local file systems.
/dev/sda1 / ext3 defaults 1 1
/dev/sdb /mnt ext3 defaults 1 2
/dev/sda3 swap swap defaults 0 0
Add the /dev/sdj file system.
/dev/sdj / ext3 defaults 1 1

25. Fix the /etc/inittab file. The cloud AMI’s are normally configured for runlevel 4.

vi /mnt/ebs_boot/etc/inittab
Edit the following line if necessary:
id:4:initdefault:

26. Un-mount the EBS volume.

sync
umount /mnt/ebs_boot

27. Detach the volume.

ec2-detach-volume $VOLUME_ID -i $INSTANCE_ID -d /dev/sdj

28. Create a new instance running the BusyBox AMI.

BUSYBOX_ID=`ec2-run-instances $BUSYBOX_AMI -z $AVAIL_ZONE -k $PUB_KEY -g $SEC_GROUP | awk ‘{print $6}’`

29. Wait until the instance is running…

ec2-describe-instances $BUSYBOX_ID

30. Attach the EBS volume to the BusyBox instance as /dev/sdj.

ec2-attach-volume $VOLUME_ID -i $BUSYBOX_ID -d /dev/sdj

31. Reboot the BusyBox instance to make sure it picks up the new device.

ec2-reboot-instances $BUSYBOX_ID

32. Check the BusyBox instance’s console output to make sure it came up as expected.

ec2-get-console-output $BUSYBOX_ID

33. Log into the new EBS backed instance.

That should be it. You now have a persistent instance that is backed by EBS storage!

Related posts:

1. s3fox does not create valid export manifest files
2. Metalink wget download script

Every running Oracle installation has several directories and files that need to be rotated and/or purged. Surprisingly, or not, Oracle has not included this basic maintenance in their software. I have come across the oraclean utility in the past, but the script does not do everything I need.

To achieve what I required, I recently hacked together a single script that does the following things:

• Cleans audit_dump_dest.
• Cleans background_dump_dest.
• Cleans core_dump_dest.
• Cleans user_dump_dest.
• Cleans Oracle Clusterware log files.
• Rotates and purges alert log files.
• Rotates and purges listener log files.

The script has been tested on Solaris 9 and 10 with Oracle database versions 9i and 10g. It has also been tested with Oracle Clusterware and ASM 11g. The script can be scheduled on each server having one or more Oracle homes installed, and it will clean all of them up using the retention policy specified. The limitation is that log file retention is specified per server, not per instance. However, I find that placing a single crontab entry on each database server is easier than setting up separate log purge processes for each one.

The script finds all unique Oracle Homes listed in the oratab file and retrieves the list of running Oracle instances and listeners. Once the script knows that information, it rotates and cleans the trace, dump, and log files.

Download: cleanhouse.sh

Usage: cleanhouse.sh -d DAYS [-a DAYS] [-b DAYS] [-c DAYS] [-n DAYS] [-r DAYS] [-u DAYS] [-t] [-h]
   -d = Mandatory default number of days to keep log files that are not explicitly passed as parameters.
   -a = Optional number of days to keep audit logs.
   -b = Optional number of days to keep background dumps.
   -c = Optional number of days to keep core dumps.
   -n = Optional number of days to keep network log files.
   -r = Optional number of days to keep clusterware log files.
   -u = Optional number of days to keep user dumps.
   -h = Optional help mode.
   -t = Optional test mode. Does not delete any files.

After several attempts at using SQL*Loader to move the 350 tables, a colleague suggested Oracle’s Generic Connectivity. After coordinating with several other groups, this is the process that finally worked:

1. Have a DB2 account created, so that the data can be queried.
2. Install the DB2 Connect client on the UNIX server on which the Oracle database resides.
3. Configure the DB2 Connect client.
   – The DB2 administrator and UNIX administrator coordinated on this, so
   I do not have the specifics.
4. Test the DB2 connection
   . /export/home/db2inst1/sqllib/cfg/db2profile
db2 connect to MYDB2DATABASE user <username>
db2 => select current time as DB2_TIME from sysibm.sysdummy1
db2 => terminate
5. Install the unixODBC package on the Oracle database server.
6. Configure the odbc.ini file (usually located in /usr/local/etc/odbc.ini).
   Example:
   [DB2DATABASE]
Description = DB2 Driver
Driver = /export/home/db2inst1/sqllib/lib/libdb2.so
7. Test the unixODBC connection.
   isql -v MYDB2DATABASE username password
SQL> select current time as DB2_TIME from sysibm.sysdummy1
SQL> quit
8. Create an initialization file for Oracle Generic Connectivity.
   Example:
   cd $ORACLE_HOME/hs/admin
vi initMYDB2DATABASE.ora
#
# HS init parameters
#
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = MYDB2DATABASE
HS_FDS_TRACE_LEVEL = debug
HS_FDS_SHAREABLE_NAME = /usr/local/lib/libodbc.so
 
#
# ODBC specific environment variables
#
set ODBCINI=/usr/local/etc/odbc.ini
 
#
# Environment variables required for the non-Oracle system
#
set DB2INSTANCE=db2inst1
9. Create a listener entry in the Oracle listener.ora.
   Example:
   (SID_DESC =
(ORACLE_HOME = /path/to/your/oracle/home)
(SID_NAME = MYDB2DATABASE)
(PROGRAM = hsodbc)
(ENVS=LD_LIBRARY_PATH=/path/to/your/oracle/home/lib:/export/home/db2inst1/sqllib/lib:/u
sr/lib)
)
10. Ensure the listener connection timeout is unlimited in the listener.ora.
    Example:
    INBOUND_CONNECT_TIMEOUT_YOUR_LISTENER=0
11. Ensure the connection timeout is unlimited in the sqlnet.ora.
    Example:
    SQLNET.INBOUND_CONNECT_TIMEOUT = 0
12. Restart the database listener.
    lsnrctl stop listener_name; lsnrctl start listener_name
13. Add a tnsnames.ora entry for the HS listener.
    Example:
    MYDB2DATABASE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521>))
)
(CONNECT_DATA =
(SERVICE_NAME = MYDB2DATABASE)
)
(HS = OK)
)
14. Log into the Oracle database as a user that has the CREATE DATABASE LINK privilege.
15. Create a database link to the DB2 database.
    CREATE DATABASE LINK "MYDB2DATABASE" CONNECT TO "DB2_USERNAME" IDENTIFIED by "DB2_PASSWORD" USING 'MYDB2DATABASE';
16. Test the database link.
    select current time as DB2_TIME from sysibm.sysdummy1@MYDB2DATABASE;
17. Move as many tables as possible using:
    create table table_name as select * from db2_schema.db2_table_name@MYDB2DATABASE;
18. Some tables will fall out due to “ORA-00997: illegal use of LONG datatype”.
    Workaround:
    SET ARRAYSIZE 1000
SET COPYCOMMIT 1
COPY FROM username/password@ORACLE_SID TO username/password@ORACLE_SID -
CREATE table_name USING SELECT * from db2_schema.db2_table_name@MYDB2DATABASE;

Known Issues:

1. ORA-28511: lost RPC connection to heterogeneous remote agent using
   Solution: Set the connections to not timeout.
   listener.ora: INBOUND_CONNECT_TIMEOUT_YOUR_LISTENER=0
   sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT=0
2. ORA-00997: illegal use of LONG datatype
   Solution: Use the SQL*Plus COPY command.
3. Error when running SQL*Plus COPY command.
   ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
   [Generic Connectivity Using ODBC]DRV_BlobRead: DB_ODBC_ENGINE (1489): ;
   [unixODBC][IBM][CLI Driver][DB2] SQL0805N Package
   “MYDB2DATABASE.NULLID.SYSLH203.5359534C564C3031″ was not found. SQLSTATE=51002
   (SQL State: 51002; SQL Code: -805)
   Solution: This error is due to packages missing on the DB2 side. I had the DB2 database admin create the missing package.

4. ORA-01400: cannot insert NULL into (“oracle_schema”.”table_name”.”column_name”)
   Solution: Create an empty table and alter the column to accept NULL.
   COPY FROM username/password@ORACLE_SID TO username/password@ORACLE_SID -
CREATE table_name USING SELECT * from db2_schema.db2_table_name@MYDB2DATABASE WHERE 1=2;
ALTER TABLE table_name MODIFY column_name NULL;
COPY FROM username/password@ORACLE_SID TO username/password@ORACLE_SID -
APPEND table_name USING SELECT * from db2_schema.db2_table_name@MYDB2DATABASE;

5. Enable DB2 ODBC driver tracing.
   Solution: Edit the db2cli.ini file.
   [COMMON]
Trace=1
TraceFileName=/tmp/db2cli_trace.log

References:
Metalink Note:375624.1 – How to Configure Generic Connectivity (HSODBC) on Linux 32 bit using DB2Connect