Some issues about ADRCI in Oracle 11g

In Oracle11g Oracle introduced a new feature called Automatic Diagnostic Repository (ADR). All the diagnostic information including the found incidents will be stored in the repository. The repository is actually an OS directory. Along with this feature, Oracle also introduced a new command line utility called adrci (Automatic Diagnostic Repository Command Interpreter) to make the info. retrieval  from the ADR easy. There are already some articles around talking a lot of ADR. Here is a good one: ADR So I am not going to get in depth about ADR itself. Instead I am going to talk about some issues I encountered using 'adrci'.

Some reported there was an editor issue using adrci 'show alert'. This can be easily solved by 'set editor'. While the error messages may appear different on UNIX and Windows, 'set editor' works on both platforms.

adrci> show alert

ADR Home = /app/oracle/diag/rdbms/chooyu/chooyu:
*************************************************************************
Output the results to file: /tmp/alert_12361_1_chooyu_1.ado
screen: Unknown terminal type
I don't know what kind of terminal you are on - all I have is 'screen'.
[Using open mode]
"/tmp/alert_12361_1_chooyu_1.ado" 989 lines, 43823 characters
2007-11-13 12:27:30.840000 +08:00
:q
Additional information: 256
Additional information: 13

adrci> set editor vim

Another issue is related to the OS permissions. Usually nobody will encounter this issue. However, if you create 2 OS groups, one is for Oracle Software Owner and the other is for DBA, you will probably hit this issue while signing in under DBA group. This is because Oracle sets all the directories under the owner's control during the installation. In my case, I created 2 groups 'oinstall' and 'dba' as well as 2 users 'oracle' and 'swong'. I use 'oracle' as the Software owner as always. The ownership of all the directories would be oracle:oinstall. If I sign in as 'swong' which I put under 'dba', I won't be able to find any home but only listener. How to solve the issue then? In Solaris, I need to use 'setfacl' to allow 'dba' group to access DIAGNOSTIC_DEST. After that you'll be able to see several homes using 'show home' adrci command. You may need to 'set base' first. Please see below.

$ adrci

ADRCI: Release 11.1.0.6.0 - Beta on Fri Nov 16 14:18:17 2007

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

No ADR base is set
adrci> set base /app/oracle
adrci> show home
ADR Homes:
diag/rdbms/orcl/orcl
diag/clients/user_unknown/host_411310321_11
diag/tnslsnr/myhost/listener
diag/diagtool/user_swong/host_2847951770_11

Blogged with Flock

Oracle11gR1 installation on Solaris 10

Generally speaking, the Oracle11gR1 installation on Solaris 10 is pretty smooth. I didn't encounter any problem during the installation. Neverthless burning a DVD for Solaris 64-bit really frustrated me.

I downloaded the Oracle11gR1 for Sparc Solaris 64-bit into my windows laptop. At the 1st time I burned a DVD-R with the default option (ISO9660 + Joliet). I realized it's wrong and doesn't work when using it to install the Oracle11g into my SUN Ultra 25 (SPARC architecture) on the next day. File names have been shortened. Afterwards I burned another DVD-RW with the carefully chosen option (ISO9660:1999) since this guarantees a file name longer than 250 characters. I thought this one would work but it still didn't work even worse. My U25 even doesn't recognize this DVD. What's wrong? I have no idea. I may need to figure out later. At last I FTPed the downloaded Oracle11gR1 ZIP file to the U25 and install Oracle11g from the hard disk natively.

The installation procedure is pretty much like the previous versions. An OS user and an OS group need to be created as the Oracle software owner. Depending on your security preference, you can create only one group 'dba' as both software owner and DBA group; you also can create 2 groups. One is the software owner and the other is the DBA group. For me, I created 2 groups. One is called 'oinstall' as the software owner and the other is called 'dba' as the DBA group. An user 'oracle' has also been created. Put some basic environment variables into 'oracle' profile. They are all similar to the previous versions. Here are mine:

ORACLE_BASE=/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/11.1
ORACLE_SID=orcl
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/lib:/usr/sfw/lib:/usr/local/lib:$ORACLE_HOME/lib
PATH=$PATH:/usr/ucb:/usr/ccs/bin:/usr/sbin:$ORACLE_HOME/bin

You may want to pre-create some directories and change the ownership.

The next step is to check your /etc/system to see if all the necessary IPC parameters are configured or not. Otherwise you won't be able to create a database since Share Memory is required. If configured already, you are ready to install; otherwise you need to reboot your machine to take /etc/system changes effective. Here are mine:

set semsys:seminfo_semmni=100
set semsys:seminfo_semmns=5000
set semsys:seminfo_semmap=5000
set semsys:seminfo_semmsl=50
set semsys:seminfo_semmnu=100
set semsys:seminfo_semume=25
set semsys:seminfo_semopm=50

set shmsys:shminfo_shmmax=4294967296
set shmsys:shminfo_shmmni=256
set shmsys:shminfo_shmseg=64
set shmsys:shminfo_shmmin=1

set msgsys:msginfo_msgmap=258
set msgsys:msginfo_msgmnb=65535
set msgsys:msginfo_msgseg=8192
set msgsys:msginfo_msgtql=512

We are ready to go! Sign in as 'oracle' and execute 'runInstaller'. All the pre-check has been passed by the installer. I chosen a customized installation and followed most of the default options. As I mentioned earlier, the installation was pretty smooth and no problem was encountered.

At last I'd like to give my machine configurations.

Model: SUN Sparc Ultra 25
CPU Model: Sparc Ultra IIIi
RAM: 2GB
Hard disk: 2x80GB, 2x250GB
OS: SunOS 5.10 Generic_125100-07

Blogged with Flock

Install BeleniX (OpenSolaris Live CD) into a USB thumb drive

BeleniX Live CD works just fine. But when you try to install it into a USB thumb drive following the installation instruction, you may get frustrated. The installer doesn't work! At least it doesn't work for me. I had to debug the installer script step by step to see where the problems result from. Finally, I worked out and successfully installed it into a USB drive. There are two places in the script to which you need to pay attention.

• the command at line 211

# rmformat -s /tmp/slices /dev/rdsk/c0t0d0p0

This command simply doesn't work in my case. Maybe the slices information in /tmp/slices generated by the script are not correct, maybe not. Since there was no error returned, I have no idea why it failed. After running this command, 2 slices are supposed to be created and labeled. However, if you run the following command

# newfs /dev/rdsk/c0t0d0s0 < /dev/null

You will get error "no such device" sth. like that. In this case, you can run the command below instead to create the slices and label them based on /tmp/slices

# format -e

It's interactive. You just follow the instructions to do so.

• The command at line 281

# installgrub -mf stage1 stage2 $devs > /dev/null

You will get error "can not read stage1 file stage1" if you follow the provided installer. The reason is there is no such a file called stage1 in /boot/grub directory. Where you can get the required stage1? Don't run

# umount /mnt/belenix

at line 274. You need to run the commands below first:

# cd /mnt/belenix/boot/grub
# installgrub -mf stage1 stage2 /dev/rdsk/c0t0d0s0 > /dev/null
# umount /mnt/belenix
# lofiadm -d $BELENIX_ISO_PATH

All the required files to run installgrub are in /mnt/belenix/boot/grub.

A successful installation! You can enjoy the LiveUSB boot from now on!

Oracle bug 3744836 while using table function in 9205

Recently I use a pipelined table function in a package to tabularize multiple rows into columns. It's my first time to write pipelined table function. It's very cool.

However, after refine the package and recompile it several times, I start to hit an Oracle bug 3744836. The symptom is:

While compiling the package at 1st time in a session, I encountered ORA-04043 error. The error message is something like: object SYS_PLSQL_84730_110_1 does not exist. If I attempt to recompile it again and again in the same session, ORA-00600 internal error will appear. You won't be able to drop the package either.

After searching on Oracle metalink, I found out the Note:3744836.8 that matches the symptom. Actually, it's an Oracle bug 3744836. In the note, the bug is said to be fixed in several Oracle patchsets including 9207. Since I don't wanna apply Oracle 9207 patchset at the time, I downloaded the interim patch for the bug as there is and applied to the problematic 9205 server. After applying the patch, the problem seemed to be solved as I was able to either recompile/drop the package. But it's not true actually! Some time later, while trying to recompile the package, I got ORA-04043 and ORA-00600 errors again! I felt very frustrated of getting these errors after the patch had already been applied. It's supposed to work but it doesn't! Still, I don't wanna apply the patchset 9207 yet due to some reasons. Finally, I figure out a workaround.

The root cause of ORA-04043 is from the PLSQL TYPEs (Record, Table) defined in the package which are used by the pipelined table function. Every time the package is compiled, Oracle will generate a new version of these TYPEs. It's so-called Versioned Objects. These versioned objects can be selected from user_objects.

select * from user_objects where object_type = 'TYPE';

They are something like SYS_PLSQL_%s_%c_%v. The last substiute variable is the VERSION. Oracle manages the relationship between packages and their verioned objects. Due to the bug, the relationship is somehow broken. Even the versioned objects are there in user_objects, Oracle would tell you they don't exist while dropping/compiling the package. e.g. before applying the interim patch, SYS_PLSQL_%s_%c_1 and SYS_PLSQL_%s_%c_2 could co-exist and Oracle would tell you SYS_PLSQL_%s_%c_1 doesn't exist when trying to drop/compile the package; after applying the interim patch, there is only SYS_PLSQL_%s_%c_2. It's getting better coz Oracle knows how to drop the version 1 objects. Unfortunately, Oracle still associates the package with SYS_PLSQL_%s_%c_1 rather than SYS_PLSQL_%s_%c_2. Since the version 1 objects have been dropped and are not there any more, Oracle will still stubbornly tell you SYS_PLSQL_%s_%c_1 doesn't exist and you won't be able to compile/drop the package.

Here is a simple way to bypass the bug before 9207 patchset is applied. Convert those PLSQL TYPEs which are used by table function in the package to Oracle Stored TYPEs. This way, you take control on packages, types and their relationships. Oracle won't version the stored TYPEs automatically as it does for the packaged TYPEs.

Working with SVN on windows

Before using the version control, I encourage you to read the documentation first. Here you can get it: SubVersion

To start your journey on version control, you may need to install some client software first if you are using windows platform. If you are using Linux/Unix, things become very simple. Depending on your experience and preference, the requirements may vary. Here I'd like to give you several solutions.

1. Check-in/out locally on SVN server and map the directory to your local drive on windows probably using SAMBA
This is the one I am using currently. :-) Coz I like to use VIM editor in windows more than the one in Solaris. It's more colorful.

2. Install cygwin package including both SSH & SVN on your windows platform
You may need to generate RSA/DSA keys and install the public key into your directory on SVN server. Otherwise, you will probably get frustrated to input your password coz it will prompt you to input the password many times.
Below is how to check-out remotely over SSH protocol in a cygwin shell

$ svn co svn+ssh://scott@myhost/svn_repository/myproj/trunk

3. Using a SVN GUI client called TortoiseSVN

The well-known free SSH client PUTTY is required. You can obtain the package here: PUTTY . Please make sure you get the whole package but not the PUTTY.exe only! Especially plink.exe which we are going to use with the SVN client.

You can get the GUI client here. TortoiseSVN

First, install the SVN client and reboot your windows, you'll see the options integrated into your context menu in the window explorer.
In TortoiseSVN -> Settings -> Network, configure SSH client to use plink.exe which you installed in the previous step
Seems like using plink.exe without the public-key authentication doesn't work, so you need to generate RSA/DSA keys using puttygen.exe and install the public part into your directory on the SVN server
Finally, in the local directory into which you want to check-out, choose SVN checkout ... in the pop-up context menu and type in the URL, what you want to check-out should be put in the local directory. Everything is done.
URL: svn+ssh://scott@myhost/svn_repository/myproj/trunk

Replace tape and tape drive using Veritas NetBackup

To replace a tape drive:

• Physically replace it
• Synchronize the S/N of the new one with the database

# tpautoconf -report_disc
# tpautoconf -replace_drive drive_name -path drive_path

To replace a tape:

• Expire it manually first
  

# bpexpdate -m A00001 -d 0 -h host

• Delete the volume using Veritas Netbackup GUI
• Remove it's label and replace it with a new one attached to the old label
• Import the new tape back into the library
  

It's difficult to do things in a proactive way

As things get older, it becomes more & more difficult to identify what is the root cause of the problem whenever there is a problem. The problem could happen at any point or multiple points down to the whole path. You have to isolate them from each other and fix them one by one. Sometimes it's just not possible to figure out the root cause. You don't know which is caused by which since they are all correlated. It's pretty much like us, human-beings. It may take several days or even several weeks to fix all the issues and get the problematic system back to normal. The system availability drops a lot during this period, of course. So proactive is always much better than reactive. Sounds like everybody knows this.

However, in a real world, usually it's hard to actively replace things until it has a problem. People would say "why do we need to replace it as there is no problem at all? look, it's running well!". Or sometimes they would say "I understand your concern. But you know, we currently have a tight budget. It's hard to get approved if we raise the request. After all, things are still running well. Let's play by ear!" But, when a serious problem happens and the system becomes unavailable, these guys just get nervous. "You must fix it ASAP! You must bring the system back by today! I don't care how you do it, you gotta get this done!" Sounds pretty similar? This happens in our life almost every day.