Oracle MVA

While doing a routine checkup I found that four processes are eating up CPU’s on one node of a two (2) node RAC cluster. These processes are ora_q000_SID2, ora_Q001_SID2, ora_Q002_SID2 & ora_Q003_SID2. This suggests there is some extra activity with AQ on the SID2 instance. (This is a 10.2.0.4 RDBMS RAC running on 64-bit OEL 4.7, CRS and ASM on 11.1.0.7)

So first thing I investigated were the queues:

Read the rest of this entry »

Every now and then there’s another discussion about how to create startup scripts for a database. Frits Hoogland just made an interesting post, somehow I get the feeling this information is new for most people. Being brought up with Linux instead of an old unix starts getting more and more useful

Some time ago I wrote that EUS stopped working. Today I was finally able to spend some time on this issue. Obviously I could have know that the solution was easy: the default password policy was blocking the account. Creating a new password policy with an enormous password expiry date for the cn=OracleContext,dc=acme,dc=com subtree solved all EUS problems.

P.S. if you happen to create your own custom password policy, please follow the documentation carefully. The right-click-create-like method does not work.

Yesterday I went to the 11G R2 release party of Oracle the Netherlands. It was great to see the usual suspects of the Dutch DBA crowd. Obviously this was a marketing fling, but Lucas Jellema‘s presentation about Edition Based Redefinition was most interesting. Alex Nuijten is presenting about the subject on planboard, which is yet another reason to visit that seminar (sorry, dutch only).

Today I spent some time on fixing performance issues with the BPEL dehydration store. The database is a three (3) node RAC cluster, running RBDMS 10.2.0.4 on ASM 11.1.0.7. BPEL connects to a service name that has one (1) preferred instance and two (2) available instances.

HW contention usually means: trouble with lobs. Just to be sure, I started to check metalink. First of all I discovered I had to set event 44951 to fix bug 6376915 (setting an event without restarting the database means setting events =, next set the event in the spfile). This didn’t clear my HW contention problems though.  What did solve the problems, was moving the lobsegments to a separate tablespace. Here’s the script I used to move the lobsegments (with some extra segment space shrinking):

Read the rest of this entry »

Suddenly I found myself migrating a database from 8.1.7 to 10.1.0.5 on Windows (don’t ask ). Migration steps were rather easy:

• Set NLS_LANG to proper value
• Create export of user (it was only one user)
• Check privileges and tablespaces
• Create new database (not in archivelog mode for performance reasons)
• Create tablespaces
• Create user and grant privileges
• Import user
• Enable archiving and schedule rman backup *ERROR!!!*

All of the sudden I found myself being able to create a database, being member of ORA_DBA privilege group, but not able to connect without the listener. This means that when you shutdown the database (before you can put it in mount phase) your connection is *gone*.

Now what !? I’m no Windows-man. Never have been, never will be.

After a cup of coffee is tried just close the database (alter database close), enable archivelog mode (alter database archivelog) and restart the Windows service. Jeez this is dirty, but it works. Time to have a colleague find out what’s wrong with the domain user I used

For one of my clients I am assisting on a EUSimplementation with RDBMS 10.2.0.4 and OID 10.1.4.3 all on OEL 4.7. After implementing EUS and enjoying using my personal credentials instead of working as sys or system all worked like a charm. Customer happy, me happy, everybody happy.

After some time all of the sudden EUS stoped functioning for certain databases. Since the number of databases that EUS was not working for is growing, I was called to find out what was going on. I checked the login by setting event 28033:

alter system set events ’28033 trace name context forever, level 9′;

Next i tried to login and I read the tracefile in $ORACLE_ADMIN/ORACLE_SID/udump:

/u01/app/oracle/admin/ORACLE_SID/udump/DB_NAME_ora_8058.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_200
System name: Linux
Node name: SERVER.DOMAIN_NAME
Release: 2.6.9-78.0.8.2.2.ELlargesmp
Version: #1 SMP Mon Dec 22 02:43:08 EST 2008
Machine: x86_64
Instance name: ORACLE_SID
Redo thread mounted by this instance: 2
Oracle process number: 67
Unix process pid: 8058, image: oracle@SERVER.DOMAIN_NAME

*** ACTION NAME:() 2009-09-16 14:06:03.492
*** MODULE NAME:(sqlplus@SERVER.DOMAIN_NAME (TNS V1-V3)) 2009-09-16 14:06:03.492
*** SERVICE NAME:(ORACLE_SID) 2009-09-16 14:06:03.492
*** SESSION ID:(527.12773) 2009-09-16 14:06:03.492
kzld_discover received ldaptype: OID
kzld found pwd in wallet
KZLD_ERR: Failed to bind to LDAP server. Err=49
KZLD_ERR: 49
KZLD is doing LDAP unbind
KZLD_ERR: found err from kzldini.

Ldap error 49 is invalid credentials, but I know for sure that my credentials are correct! I even use them to logon to the machine that the database is running using OAS4OS (which was somewhat challenging too, since OAS4OS as provided by Oracle is far from enterprise ready).

Knowing my userentry is correct, I checked the database entry in the oid. To my surprise I noticed that pwdexpirationwarned and pwdgraceusetime was set for the database entry (see cn=DB_NAME,cn=OracleContext,dc=domain,dc=acme). This suggests that the passwordpolicy was enforced for databases too, even though the effective subtree was set to cn=users,dc=domain,dc=acme. Simply removing the attributes for all databases in the OID solved the issue for now:

$ORACLE_HOME/ldap/bin/bulkmodify basedn=”cn=DB_NAME,cn=OracleContext,dc=DOMAIN,dc=nl” attribute=”pwdgraceusetime” value=”" replace=true filter=”objectclass=orclDBServer”
$ORACLE_HOME/ldap/bin/bulkmodify basedn=”cn=DB_NAME,cn=OracleContext,dc=DOMAIN,dc=nl” attribute=”pwdexpirationwarned” value=”" replace=true filter=”objectclass=orclDBServer”

Or for the die-hards:

delete from ds_attrstore
 where entryid in ( select entryid
                      from ct_dn
                     where rdn = ‘cn=db_name’ )
   and attrname in (‘pwdgraceusetime’,'pwdexpirationwarned’);

Please keep in mind that Oracle doesn’t support direct querying on the OID.

Let’s find out what Oracle Support is going to give me for a more permanent solution

Oracle RDBMS 11 R2 is out. Open World is not until October 11th, I wonder what the big announcement will be. Maybe Tim Hall is right after all

After reading up on sqlnet, learning how to trace sqlnet trafic of jdbc-thin database sessions and a lot of agony, Oracle support finally solved the problem with RCU and 11g. A small testprogram in java gave a clue. It turns out the 11g database is too slow in accepting the session. Adding JDBC_LOGON_TIMEOUT with a value larger then 15 in $RCU_HOME/rcu/config/rcu.properties solved the issue.

Sometimes VMWare on my laptop is far from ideal.

When I was still young, you could easily trace a sqlplus session by adding some parameters to the appropriate sqlnet.ora file e.g.:

TRACE_LEVEL_CLIENT=16
TRACE_DIRECTORY_CLIENT=/home/oracle
TRACE_FILE_CLIENT=client
TRACE_UNIQUE_CLIENT=ON
TRACE_TIMESTAMP_CLIENT=ON

When I tried to trace a session with an 11g client, the trace-file didn’t appear in the expected TRACE_DIRECTORY_CLIENT. After a quick look around, it turned out that the trace went to $ORACLE_BASE/diag/clients/user_oracle/host_$NUMBER/trace. Documentation seems clear, so I figured I was going crazy.

My personal lesson for today was: never ever trust your knowledge when you move to a new version of Oracle. Old-school (Oracle 10) tricks are useless, when Oracle introduces a new sqlnet parameter called DIAG_ADR_ENABLED and sets the default to ON. This setting causes Oracle to ignore the TRACE_*_CLIENT parameters.

What is even more sad is that I was not the only person to fall for this caveat.

---

While reading up some more about tracing I ended up on this page, which provides a utillity for non intrusive jdbc tracing.