PSU lists

All the PSU lists are available with Doc ID 756388.1 on Metalink.
In particular Oracle Database Recommended Patches are at Doc ID 756671.1.

Virtual Circuit Wait event

I have found few explanations connected with this event. It is specific for "shared server" configuration of the Oracle RDBMS.

1. Dick Goulet suggests it is connected with periods of inaccessibility of shared servers due to easy to miss short bursts of load. The solution was suggested for the database in version 9.2.0.4
2. Jonathan Lewis describes this event in similar way and suggests to check the v$reqdist view in order to get a histogram for duration of calls
3. Very detailed description is available at Igor Usoltsev site and most up-to-date as it refers to the 11g version. Most important observations are:
   
   • old event virtual circuit status was split to shared server idle wait (which is recognized as an idle one) and virtual circuit wait (which consists actually of 2 parts: idle one and network non-idle one - the event will be probably split into such 2 components in future versions of the RDBMS)
   • the virtual circuit wait event is strongly connected with the SQL*Net message from client event (which is recognized usually as an idle one) and the existence of one or the other depends on the size of the array used for fetching rows - the bigger the array the more SQL*Net message from client the less virtual circuit wait

In our case we have had this occurred mostly on long-running queries over a db link, so very inconvenient, because even if this event is "partially idle", then for us a response time always includes this event. The solution for us is to tune every query over this db link as well as possible.

Auto-start of an Oracle database

The traditional way to run/stop automatically an Oracle RDBMS is to call appropriately the script dbstart/dbshut. This script takes one optional parameter, which is an ORACLE_HOME of a listener to run. The following script does the job and should be configured to run at the proper runlevels (usually start on 3, shut down on 0 and 6).
Of course one needs to keep oratab up to date in required configuration.


#!/bin/bash

## date : 2011-11-22
## desc : starts|stops all the instances with enabled autostart named in oratab
## desc : usually to be used on dev/test environments
## os : Linux, possibly Solaris, other may need improvements
## loc : /etc/init.d

CMD=$1
LOG='log/oracle_db.log'
su -l oracle -c 'if [ ! -d $HOME/log ]; then mkdir -p $HOME/log; fi;'
case $CMD in
start)
su -l oracle -c '$ORACLE_HOME/bin/dbstart $ORACLE_HOME &> $HOME/$LOG'
;;
stop)
su -l oracle -c '$ORACLE_HOME/bin/dbshut $ORACLE_HOME &> $HOME/$LOG'
;;
*)
echo "Usage: $0 start|stop"
;;
esac
if [ $? -ne 0 ]; then
echo 'Command failed. More details in /log/oracle_db.log'
fi

exit 0


Of course Windows does the autostart in a different way - one needs to set proper flag/s in the registry.

Shrinking temporary tablespaces in an Oracle database

This new in 11g feature is very similar to resizing temporary tablespace files. The main difference is that while resizing the RDBMS is able to cut off only not allocated space, while shrinking additionally is able to deallocate space, which is free and a subject to reuse.

So there is no analogy to shrinking tables for example. There is no data movement. If in the area of a cut there exists an object used at the moment, shrinking is of course able to return back only the space between the end of a last such object and the end of a file.

GLOBAL_NAME and db links

Once we created a database for migration from other older system with some domain_name. We set a db_domain in order to be compliant with other databases we managed. However the database to be migrated worked without such setting. Thus we hit a problem when creating database links, as those new were created with some suffix even provided with double quotes and in result some code did not want to compile.

What is behind the scene?
It seems at the database creation the GLOBAL_NAME setting for this database is created as well based on db_name and db_domain settings. Any changes to those base parameter are not reflected further in this GLOBAL_NAME value. And that is GLOBAL_NAME which influence database links name - its domain part is added to every database link.

How to deal with it?
http://dbatips4u.blogspot.com/2009/04/tip21-db-link-name-suffix.html provides the details.
I add here small note to it - I was especially interested in cancelling the global_name domain part at all, which is less trivial than just setting it to something new as the simple ALTER DATABASE RENAME GLOBAL_NAME TO "{db_name}"; does not work as planned (the domain part stays in place). I followed the notes from the link above. I run UPDATE GLOBAL_NAME SET GLOBAL_NAME='{db_name}'; but even though the GLOBAL_NAME content has changed, still db links' names were creates with suffix I did not want.
The important part was a database restart, after which all started work as planned.
Btw. our GLOBAL_NAMES was set to FALSE, which is different from the settings presented in the tip from the link above - may be that is the clue, why I needed to restart.

10g to 11g upgrade

I used to think that pre-upgrade tools (ie. utlu112i.sql) are clearly for reporting purposes. I mean this script simply displays what one needs to fix in order to prepare a database to upgrade process.
Yesterday showed I was wrong.
So here is what You should not do at home ;-)
I started with direct setting of compatible parameter to 11.2.0.0.0 (which in turn was a very, very bad move). Because we upgraded quite a bunch of databases I felt quite sure. Another thing was we prepared a procedure for upgrade (based on documents from Metalink, but shortened, as we do not need to go through all the steps) and there the pre-upgrade tool was missing.
So I startup in upgrade mode and after a while I get an error that there is unknown identifier TZ_VERSION in table REGISTRY$DATABASE. The hint in script was simply to run utlu112i.sql - it became clear that there are some changes made by utlu112i.sql to the database structure.
Due to setting compatible parameter to 11.2.0.0.0 I was unable to start this database again as 10g version, so can not run utlu112i.sql. @#$$$%!
Then I tried to add a column to the problematic table. The command was successful, but the structure showed still as old due to running in upgrade mode and apparently in a different edition.
Recreating the controlfiles based on the database structure, which I still was able to query, was successful, but it is not enough because data files were changed as well (probably only headers, but this solution path was thus closed).
What I did finally was a simple ugly hack.
I edited the file $ORACLE_HOME/rdbms/admin/catupstr.sql and changed queries to the REGISTRY$DATABASE replacing TZ_VERSION calls with a value ('14' in my case). Then I just started catupgrd.sql again.

There were some issues with the SI_INFORMATN_SCHEMA schema, but not sure if this has anything to do with problems with pre-upgrade tool and as this is a test environment, it is not so clean as the production (some not compiling code pieces, etc). Eventually the problem was solved.

Concluding:

1. do not change compatible parameter to newer when upgrading - do it sometime afterwards
   
2. always run pre-upgrade tool

Hints in Oracle

Funny thing... I used to add any hints within block comments i.e. /*+ index(whatever) */, so never thought that line comment -- make it.
Does it? Sure it does --+ index(whatever). The only thing is one can not continue a SQL statement with it on the same line.