Pythian Group

The AQ notification process has changed from release 10gR2 to 11gR1. The most notable change is the switch from using DBMS_JOB jobs to DBMS_SCHEDULER jobs.

In 10gR2 the notification process goes like this:

1. Message gets enqueued to the User queue.
2. The EMON process is notified that a message has been placed in the queue.
3. EMON enqueues a message into the internal queue SYS.AQ_SRVNTFN_TABLE_Q.
4. EMON creates a DBMS_JOB job to dequeue from the internal queue.
5. The job is run, and dequeues from the internal queue, and then runs the notification callback procedure associated with the User queue.
6. The notification procedure then does its business which can include de-queuing the original message from the User queue.

The number of available jobs to run the notifications is limited to the number of JOB_QUEUE_PROCESSES that have been set up for that instance, and each job dequeues one message.

In 11gR1 the notification process is very similar but instead of creating a DBMS_JOB job it creates a DBMS_SCHEDULER job.

This, in itself, is not very different but here’s the big difference: Read the rest of this entry . . .

Recently I was looking into a long-running statement and noticed a curious thing. One moment, I had just over 4 billion buffer gets, and the next I had around 2 million.

The statement was still processing and the number of physical reads was still rising.

I think the reason for this is that the internal place holder for this value is simply a 32-bit unsigned integer that has the largest number of 4294967295. But my version of Oracle is 64-bit; I thought that maybe it would use 64-bit integers.

So, beware that if you have had a statement running for some time, you cannot necessarily rely on the buffer_gets column in v$sql—it may be that it has run over the limit, been recycled, and is counting from zero again.

The evidence. Read the rest of this entry . . .

Is your database in archive log mode?

Is your database in flashback mode?

Have you defaulted your archive location to the flash recovery area?

Is your ORACLE_SID lowercase?

Do you use SQL BackTrack?

If your answer is yes to all these questions then beware!

There is a bug in SQL BackTrack (at least in version 6.8) that prevents your backing up of the archive logs from the flash recovery area. This will result in the inability to recover from any online backups that you have taken if you somehow lose those archive logs.

You do not receive any obvious errors when doing the backup but, if you are observant, you may spot the fact that the backup report does not show that it backed up any archive logs. To really check whether you are suffering from this problem then you need to check the $DTBASE/obacktrack/log/datatools.log. This is the log that gets written whenever any SQL BackTrack command is run. You will see an error such as

17:29:16.61 obacktrack[839826](2835/oramisc.cpp): Unable to open directory: '/home/oracle/data/test/test/archivelog'

You will know if you have been caught by this bug when you try to restore and you get messages that look something like this.

BMCBKO4802051E: The following Oracle error occurred during
BMCBKO4802051E: SQL-BackTrack processing : 'ORA-01195: online backup of file 1 needs more recovery to be consistent'
BMCBKO4802051E: The following Oracle error occurred during
BMCBKO4802051E: SQL-BackTrack processing : 'ORA-01110: data file 1: '/home/oracle/data/test/system01.dbf''
BMCBKO4805906E: Unable to perform recovery.

The problem arises when Oracle creates a directory in the flash recovery area that correspond to the database name. This directory is in uppercase. So, in my case, my ORACLE_SID is test and the init.ora parameter db_recovery_file_dest='/home/oracle/data/test'.

Oracle creates the directory
/home/oracle/data/test/TEST.

However when SQL BackTrack sees that the archive location is defaulted to the flash recovery area it tries to look in the directory
/home/oracle/data/test/test.

This fails … silently!

There are 2 workarounds to this issue that I have tested.

1. Create a symbolic(soft) link to point to the real directory using ln -s
2. Explicitly set the archive location using the init.ora parameter log_archive_dest_n

The workaround suggested by BMC (the supplier of SQL BackTrack) is to change the database name to uppercase. This will work too, but I haven’t tested it.

Beware this parameter can prevent your database from starting. Indeed it can prevent your instance from starting!

There are two dynamic views v$system_fix_control and v$session_fix_control which were introduced in 10.2 and control whether fixes for bugs in the optimizer can be turned on or off. This can also be controlled using the _fix_control initialization parameter.

If the parameter _fix_control is set incorrectly i.e. with invalid bug ids then, when starting the database, you may get the following error

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Wed May 7 10:55:43 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount
ORA-00940: invalid ALTER command

There are no error messages in the alert log and the instance has refused to start.

And look at that helpful error message. No doubt behind the scenes it doing some sort of ALTER command but still …

If you get this error then check the pfile or spfile and see if there is some spurious _fix_control setting. This is especially valid for the upgrading of Oracle. I came across this error upgrading 10.2.0.2.0 to 10.2.0.3.0.

Also beware if setting this parameter in a running database. You can prevent further logins if you get it wrong or indeed try to unset it. Do not do

ALTER SYSTEM SET "_fix_control" = '';

This will require a bounce to fix. Once, of course, the parameter has been removed from the initialization files.

The moral of the story is be very careful with undocumented parameters when upgrading!