Confessions of An Oracle Expert -- What a Fortnight

Expert: A person who has made all the conceivable mistakes and is working on the inconceivable.

This week I migrated one of our databases connected with one of the physics experiments to a new machine and 8.1.6. The day runs all proceeded without incident, but after the midnight shift change problems started occurring with client connections. I found that I could not get the database to accept a certain username and password from a client, but it would from the database machine itself through an IPC connection. I checked, double-checked, and triple-checked the setting of the TWO_TASK variable which was correct. In then tried username/password_at_alias form the client and I was in. I had forgotten that earlier that
day one I was working on wrapping sqlpus so it runs in its own shell, and the shell was
setting two_task to something different. This was not the problem the users were having,
theirs were related to switching to shared servers.

A few people came out of the woodwork saying they needed to see and in some cases update the data on the new box. Here permissions on an applications data is the responsibility of the application's owner. Obviously some database links were needed. The links needed to be shared, but couldn't be public links. I've not read about this anywhere, but you can share a database link via a view. For example user a: creates the link called
from heretothere, creates a view some_table as select * from some_table_at_heretothere,
and grants user b permissions on some_table. User b can then access "some_table_at_heretothere
as a.some_table. I learned this from a self-taught developer a few years ago.

A vendor came in this week to install a product which dumps web logs into Oracle. Two more 8.1.6 instances on a brand new box. Some flaky things started to happen, not with the database, but with the Oracle software. A reboot showed super block problems, the file systems were rebuilt and things looked okay at first. But soon got flaky. I decided to re-install the Oracle software. After using the installer to remove the software, I decided to wipeout the directories as well. This was done via the rm -rf command which should get rid of any "." files. Yet some would report back that they were not empty. An ls -la would report back nothing. The sysadmin reformatted the partition. Oracle was installed, the vendor got about 95% through his installation all looked well. Last night I rebooted the machine again to kill off an automount daemon. The system would not come back up. Again a bad super block. Another sysadmin was able to recover from this. I got the system
back sand tried to bring up Oracle using svrmgrl. Svrmgrl could not find its message translations. So whenever I got a message on another box I would issue oerr mgr "<number>
and see what it was telling me. This was a bit cumbersome so I tried sqlplus /nolog intending to connect after a sysdba. The screen behaved as if the linefeed key was permanently pressed. This was not the best situation either. Compounding the growing angst was Jared message that he couldn't get sqlplus /nolog to work. Something that had worked for me a few hours before.

I thought this was a bad disk. There were previous parity problem with the disk, but these went away when the SCSI cable with a bent pin was replaced. It was not a hardware problem at all. The sysadmin who originally built the partitions for the Oracle software and the Vendor's software overlapped them. Thus both could access some of the same disk blocks. Disk block multiplexing is not yet ready for production systems :). It was the vendor's file system which had the super block problem, and fixing it trashed part of the Oracle software install.

Ian MacGregor
Stanfored Linear Accelerator Center Received on Fri Jun 02 2000 - 19:09:36 CDT