How to find the offending query?

[Syed Naseer_1]

Hi,

One of our DB went down with ORA-470. We know that the system killed some oracle processes when it ran out of swap.

How can i determine which were the queries running at the time this happened? Does the DB keep history of all queries that were run? The DB has since been restarted. Can we still obtain the info i need?

Thanks,
Naseer.

[Fred Ruffet · Accepted solution]

You will not get any info unless your DB has sql_trace parameter set to true. This is not the default, and setting it to true may imply a overhead in resources consumption.

All info you will get are in directory specified by background_dump_dest, user_dump_dest and core_dump_dest parameters. In background_dump_dest you may found info in alert_.log file. In User_dump_dest you can also have info logged by user processes if they had to. Look at files having mtime near to crash.

Regards,

Fred

[Steven E. Protter]

If your system is actually running out of swap:

swapinfo -tam

make sure thats true and deal with that issue as well. If you are using oracle and swapping with a high page rate(vmstat) then you should consider adding memory.

Oracle loves memory, eats it for lunch and asks for more.

SEP

[Jean-Luc Oudart]

If the database has been restarted you won't have any information from the SGA.

In the future, you can run attached script to interrogate the SGA for active query.

Regards,
Jean-Luc

[Fred Ruffet]

Note that ORA-470 error reports an error from log writer. You may found a dump file for lgwr in background_dump_dest directory about this error that could give you more informations.

This dump file may be named like :
_lgwr_.trc

Regards,

Fred

[Mel_12]

Since, it is narrowed down to the DB writer, the last respondent is right about looking for the associated trace file in the udump directory. If it is actually the database writer (Archiver) that gave up on you, it would take the system down. The same applies to all other background processes such as log writer, ckpt, smon, pmon and the rest of them.

Hope this helps.
Mel

[Indira Aramandla]

Hi Naseer,

Normally ORA-470 signals that the LGWR process terminated due to an error. And hence PMON terminated the instance due to error ORA-470.

It's hard to say exactly what the problem is at this point. When one background process terminates abnormally, it's termination is detected by another background process who will report the error and terminate the instance (basically, if one background process fails, they all fail...).

You look in the alert.log and find no errors or trace files referenced. When you check the bdump directory, you do find three trace files (DBWR, LGWR and PMON trace files. The trace file for the LGWR process should provide information as to what the error was that LGWR encountered. If it for some reason points to another background process dying, you need to check the trace file for that background process, and so on, until you find the error that started it all.

The DBWR trace file contains the following errors:
ora-00447 fatal error in background process
ora-00470 LGWR process terminated with error

In the LGWR trace file you find:
ora-00447 fatal error in background process
ora-00313 open failed for members of log group of thread
ora-00312 online log thread :
ora-07362 sfifi: open error, unable to open file.
ora-00470 LGWR process terminated with error

In the PMON trace file you find:
ora-00447 fatal error in background process
ora-00470 LGWR process terminated with error

When no trace files are written you may be out of file descriptors (NFILE kernel parameter). If you have recently added new files to the database then you might have your System Administrator check for any OS specific errors and increase the 'NFILES' parameter on the Operating System. The OS kernel parameter 'NFILES' parameter is set too low. When Oracle is trying to open an online log file, it is failing due to operating system restraints.

Or you can put a trace for the session that queries or set trace on for the database.


Indira A

[Syed Naseer_1]

Fred, Steven, Jean-Luc, Mel and Indira

Many thanks for spending your valuable time in replying to my query.

In summary, i will need to enable sql_trace at the DB level to capture the info i need. However, this is quite expensive in terms of performance.
Will discuss this option with the customer's DBA.

Thanks,
Naseer.

[Syed Naseer_1]

Considering turning on sql_trace, to capture the offending sql in case the problem recurrs.

Thanks to all who replied.