DBA stories: How a table went M.I.A. in production

A few years ago, I was attending a masterclass with some fellow DBA's at our office. At some point in the evening one of the DBA's asked for help on a problem, concerning a production system of a parcel distribution company. The gist: Somehow a table was lost... So discussion was revolving around how to restore the database back into a consistent state.

But first things first. We needed to know how this situation came to be. There was a development team that reported the problem and we went to talk to them. When we arrived, three developers approached us and started ranting that this must be a bug in the Oracle database, because "a table with foreign key relations cannot just disappear". They had just deployed a small change to production, which crashed and burned the application. That's all.

I said that something like a bug in the Oracle database is possible, but that that would be quite unlikely. At this time production was down already for 2 hours! There was tension in the room. The developers were at a loss. I almost went for the keyboard to start investigating, when I remembered something Anjo Kolk told on a Miracle Open World session I attended: "When you take the place of the developer, YOU have the problem on your hands".

I asked the developer the details on this release (scripts etcetera) and how they deployed it. There weren't any scripts available... Sorry? But you're releasing changes into production! But how are you deploying the changes then?

SQL Developer. Sure, why not. It is a great tool. I then asked him to show me exactly, what he did when he issued the last DDL statement (create index) in their dev database. He then proceeded to click the table and going through some steps to add an index. Imagine my surprise and the revelation on the developers face as he saw what he had done.

Instead of creating an index, he actually renamed the table to the name of the new index! Next we checked the production database and sure enough, there was the table that went MIA. It just had a different name. They looked at me and asked what to do next: Well, how about changing it back to its correct name? The problem was solved.

So in the end I just helped the developers solve their own problem in few minutes. I told them and the 'manager', that they really need to implement some basic form of release/delivery process. You just cannot be so nonchalant with your customers IT assets.

 

Posted with Blogsy

Calling webservice through loadbalancer with Oracle Soasuite 10gR3

We have all our services configured in some form of a cluster, like the SoaSuite (10.1.3.4), Filenet, Siebel etc.

When a new BPEL process was deployed and being used moe and more, we saw SOAP errors occurring and the BPEL process would timeout. The BPEL process instance was calling an external webservice through the Oracle ESB.

This is the error:

Exception on JaxRpc invoke: HTTP transport error: javax.xml.soap.SOAPException: java.security.PrivilegedActionException: javax.xml.soap.SOAPException: Message send failed: Premature EOF encountered

The solution was to add an option to the start parameters of the containers running SOA.

-DHTTPClient.disableKeepAlives=true

I've seen other SOAPExceptions in my search that all relate to this issue.

On Oracle support its documented as a problem concerning the http_client.jar (article id 803186.1).

As we do not see this problem in non-clustered environments, it looks like the combination of a loadbalancer and http_client using keepalive is not working well. From the viewpoint of a loadbalancer it is strange to persistent connections as it cannot do the work its supposed to do.

Add custom logfiles to 10.1.3 AS Control

Developers and testers have been asking us for the custom logfiles generated by the java application deployed on Oracle AS 10gR3. In AS Control you can watch and search the standard logfiles, but no options are available to view these extra logfiles.
Searching through metalink a teammember found a bug on 10.1.3 to fix some logfile viewing issues with AS control (metalink bug 5901925). This helped us to create the solution to our problem. The ascontrol application has two configuration files which controls which logfiles to process.

OC4J.xml - configuration for logfiles of Oracle Diagnostic logging feature
OPMN.xml - opmn logging configuration

These can be found in: $ORACLE_HOME/j2ee/home/applications/ascontrol/ascontrol/WEB-INF/config/registration

So now we would like to add the custom logfiles generated by applications and place them in the APPS viewpath in AS Control.

add the following to OC4J.xml

<log path="j2ee/%OC4J%/log/%OC4J_APP%.log" componentId="j2ee">
<logreader type="SimpleTextLog">
<property name="TimestampFormat" value="yy/MM/dd HH:mm:ss.SSS"/>
<property name="ModuleId" value="%OC4J%_%OC4J_APP%"/>
<property name="ComponentId" value="j2ee"/>
</logreader>
<logviewer LogType="OC4J_APPLICATION" ComponentName="%OC4J%"
ComponentType="OC4J">
<property name="COMPONENT_TYPE" value="APPS"/>
<property name="OC4J_APP" value="default"/>
<property name="displayPath"
value="/%COMPONENT_TYPE%/%OC4J_APP_DISPLAY%/%LOG_NAME%"/>
<property name="category" value="application|diagnostic"/>
</logviewer>
</log>


The trick is in the dynamic 'variables' which are enclosed in % symbols. These you can use to control the resulting view in AS Control. In this case the custom log should be generated with the name <application name>.log, where we use the %OC4J_APP% variable to dynamically pick up ALL custom logs of all applications in all the containers running on the Application server instance.

This way you can also add viewing of configuration files (in the logs section) of AS Control.

Btw: this is not documented and probably also not supported by Oracle :-) Applying patchsets probably overwrite your changes aswell, so beware.

Tony

OVS: Add a new disk to a guest

Running out of disk space is something that happens all to soon in today's (database) systems. Even on my laptop! So I needed more disks. How do I do that when working with OVS and guests?

That's actually quite easy to accomplish on linuxy OS'es. As every device is treated as a 'file' you can therefore create new devices by making a file and connect it to a device driver.

In our case we need to add a disk to a linux guest from OVS. Following are the steps to create the device and present it to the guest.

1. create a file in Domain0 for the guest (e.g.: in the running_pool)
   dd if=/dev/zero of=disk2.img bs=1M count=(1024*#GB_needed)
2. change the disk configuration of the guest
   edit your vm.cfg and add the disk to the disk parameter.
   disk = ['file:/OVS/vm1/running_pool/system.img,hda,w','file:/OVS/vm1/running_pool/disk2.img,hdb,w']
   
3. reboot the guest
   xm shutdown guest
   xm create guest
4. logon to guest
   ssh hostname
5. partition the new disk
   fdisk /dev/hdb
6. create the desired filesystem on the partition(s). Example ext3
   mkfs -t ext3 /dev/hdb1
7. Add filesystem to /etc/fstab for automatic mounting at boot
   /dev/hdb1 /u02 ext3 defaults 0 0
   

That's all!

adding NFS to Oracle VM Server

After setting up OVS, I wanted to install some software in one of my guest (11g database). But I didn't have any access to shares/mounts with all the ISO's and software trees in Dom0 from my guests! The first solution is to actually copy (scp) the software from Dom0 to the guest. This ofcourse works, but is tedious and as again unnecessary.
Why not expose the mountpoints to my guests with NFS! We need to setup Dom0 as an NFS server and the guest as clients.

NFS Server configuration

• What to make available through NFS:
  
  create the file: /etc/exports
  format: directory client-ip(options)
  directory=Which directory to export
  client-ip=hostname/range,ip-address/range
  (options)
  -ro=readonly for client
  -rw=read/write for client
  -no_root_squash=client as root gets root access to nfs aswell, instead of as nobody
  -sync
  
  example
  /OVS/remote *.vanesch.nl(rw,sync,no_root_squash)
  /mnt/share/software *.vanesch.nl(ro,sync)
  
  
• How to make NFS available
  We need to activate two services: portmapper and nfs(which start several daemons)
  
  service nfs start
  service portmap start
  
  Let's make sure these services are started across reboots:
  chkconfig --level 35 nfs on
  chkconfig --level 35 portmap on
  

NFS client configuration

• What to have availableWe need to edit: /etc/fstab
  format: source mountpoint nsf options 0 0
  Options: rw,bg,intr,hard,timeo=600,wsize=32768,rsize=32768,nfsvers=3,tcp
  
  
  I will add both exports from my NFS Server (dom0.vanesch.nl) to this guest:
  
  dom0.vanesch.nl:/OVS/remote /mnt/nfs nfs rw,bg,intr,hard,timeo=600,wsize=32768,rsize=32768,nfsvers=3,tcp 0 0
  
  dom0.vanesch.nl:/mnt/share/software /mnt/software nfs ro,bg,intr,hard,timeo=600,wsize=32768,rsize=32768,nfsvers=3,tcp 0 0
  
  
• How to make NFS available
  Again we need to start the same two services
  
  service nfs start
  service portmap start
  
  Let's make sure these services are started across reboots:
  chkconfig --level 35 nfs on
  chkconfig --level 35 portmap on

One other thing is important! Turn off the firewalls in Dom0 and your guests.

service iptables stop
chkconfig iptables off

Now you can run installations (or mount ISO's) from your NFS shares.

Native Oracle VM Server on a laptop

Together with my DBA collegues we work on our Oracle skills by getting our hands dirty. To be able to practice all kinds of scenarios, everybody runs vmware with linux guests on their Windows laptop. For some time Oracle has a virtualisation solution with Oracle VM Server and we wanted to experiment with this product aswell.
Our setup consisted of Windows with vmware in which we run Oracle VM Server (OVS). In OVS we then create guests for our scenarios. Not really an ideal situation, due to the limited resources of laptops in general and also the unnecessary overhead. As a DBA I have a need/desire to remove unnecessary resource consumption whenever possible. And I wanted to keep my original Windows install intact (which I need for my wireless connectivity).

Originally this was a small and simple instruction for me and my collegues in Dutch. Some sections have been removed/cut for not being relevant.

Setup/necessities

• laptop/machine prefferably with Hardware Virtualization capable CPU & BIOS.
• partition magic (not free, but easy to use) or parted (linux and free)
• downloads from edelivery.oracle.com

- Oracle VM Server (burn on a bootable cd)
- vmmanager iso
- DVD iso of Oracle Enterprise Linux 5 update 2 32 bit (V15098-10)
- templates (PVM's)

• ovm console plugin(http://oss.oracle.com/oraclevm/manager/RPMS)
• patience

Execution plan
This is the general plan for getting OVS running on a laptop with Windows already installed.

1. activate Hardware Virtualisation in bios
2. redefine partitions
3. create dualboot configuration
4. installation of OVS
5. transfer ovs bootsector for dualboot
6. installation of GUI in dom0
7. installation of firefox
8. installation OVS console plugin

1. activate Hardware Virtualisation in bios
Current laptop models have CPU's capable of running with Hardware Virtualisation. This gives some extra possibilities, like running guests without PV drivers (HVM's). Often HV is not enabled by default. Refer to your vendor's manuals on how to enter the bios to adjust this setting.

2. redefine partitions
To be able to boot OVS from the same internal disk, you need to create the boot partition within the first 1024 cylinders of the disk. As I had Windows already pre-installed I needed to juggle with my partitions. The trick is to create a small partition of 101MB at the beginning of the disk (cylinder 1). With Pmagic I moved my Windows partition (C:), to create the necessary space.

When installing OVS will try to create four partitions.

1. /boot
2. /
3. /OVS
4. swap

We already have one partition with windows (primary partition) and we need one primary partition for /boot. If you let OVS do the work it will try to create all four partitions as primary partitions. This will fail as you can have only four primary partitions on one disk.

Solution: Create an extended partition, which can hold logical partitions.

I also created an extra FAT32 partition as a shared disk between my Windows and OVS installation. This could have been NTFS, but writing on an NTFS partition from Linux seems not to be stable enough yet (this is not verified by me, btw).

3. create dualboot configuration Edit your c:\boot.ini and add the bold line as shown below.

[boot loader] timeout=30 default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS [operating systems] multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Microsoft Windows XP Professional" /noexecute=optin /fastdetect
c:\ovs_boot.dos="Oracle Virtual Server"

4. Installation of OVS Boot your laptop from the OVS boot cd.

In the bootscreen just hit [enter]
When you get the question to select a partiotn layout choose: use all empty partitions.

PS: If you get errors stating there is not enough room, you probably didn't create the extended partition.

When asked where to write the bootloader, DON'T select MBR!!! Select to write bootstrap on linux bootpartition.

When OVS has installed it will want to reboot. Now you need to boot from de OVS cd again!

5. transfer OVS bootsector
You rebooted from the OVS cd again, right? Good! We created a second OS, but we can't boot to OVS yet. We need to get the bootsector onto the Windows bootpartition as a file.

Linux to the rescue! As we booted from the OVS cd, we have the option of starting linux right from cd.

On the prompt type: linux resuce
network: not needed
mounting system image: choose 'skip'

When linux has finished booting, we need to identify two partitions
1> source: /boot partition of OVS (it starts with cylinder 1)
2> the Windows boot partition (or FAT32 partition) to write the bootsector to

#Use fdisk to print the partition table
fdisk /dev/sda
p

#Create a mountpoint to mount windows partition
mkdir /mnt/share
mount /mnt/share

#Extract bootsector to file
dd if=bootpartition bs=512 count=1 of=/mnt/share/ovs_boot.dos

#we can now reboot again (remove the cd)
exit

If you didn't create ovs_boot.dos directly onto the c: partition, you need to boot into Windows and copy ovs_boot.dos to c:\

You should now be able to boot directly to OVS.

When booting to OVS, you get five boot options. Just select the default which is probably xen-64 (third option).

6. Installation of GUI in dom0
When you have booted into OVS, you should now be on the console. Log onto the console with root/ovsroot.

In linux the GUI consist of X11 and a desktop manager like Gnome or KDE. We will install Gnome. The packages to install these products are located on the Oracle Enterpise Linux 5 update 2 DVD iso image, so we need to mount it.

#create mountpoint & mount iso
mkdir /mnt/cdrom
mount pathto/dvd_el5u2.iso /mnt/cdrom -o loop,ro

We don't want to use rpm to install all the required packages, because it doesn't resolve dependencies for you. That's what yum is for. Before we can use yum, we need to setup a yum repository. This is a file with a reference to some xml files on the DVD image.

cd /etc/yum.repos.d/
vi EL5.repo

Contents of EL5.repo

[EL5u2]
name=Oracle enterprise Linux 5
baseurl=file:///mnt/cdrom/Server

We need to import a key to make yum work properly.

rpm --import /etc/pki/rpm-gpg/RPM-GPG-KEY*

Now lets install all required components.

yum groupinstall "X Window System"
yum groupinstall "GNOME Desktop Environment"

7. Installation of firefox
Installing firefox canbe done with rpm.

rpm -ivf /mnt/cdrom/Server/fire*.rpm

8. Installation of OVS plugin
This plugin will enable you to logon to the console from the vmmanager webinterface.

#dependency: install package from EL5u2 DVD iso
rpm -ivh /mnt/cdrom/Server/m4*.rpm

#install plugin
rpm -ivh /ovm-console-1.0.0-2.i386.rpm
cp /opt/ovm-console/etc/mozpluggerrc /etc/
cp /opt/ovm-console/bin/* /usr/bin

9. starting GUI
You can now start the GUI

startx

10. play!
Exercise: Create a VM and install vmmanager.

Have fun! Any comments are welcome.

regards, Tony