- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- How to find the offending query?
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-03-2004 12:18 AM
тАО11-03-2004 12:18 AM
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.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-03-2004 12:39 AM
тАО11-03-2004 12:39 AM
SolutionAll 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_
Regards,
Fred
"Reality is just a point of view." (P. K. D.)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-03-2004 12:39 AM
тАО11-03-2004 12:39 AM
Re: How to find the offending query?
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
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-03-2004 12:50 AM
тАО11-03-2004 12:50 AM
Re: How to find the offending query?
In the future, you can run attached script to interrogate the SGA for active query.
Regards,
Jean-Luc
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-03-2004 12:59 AM
тАО11-03-2004 12:59 AM
Re: How to find the offending query?
This dump file may be named like :
Regards,
Fred
"Reality is just a point of view." (P. K. D.)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-03-2004 11:48 AM
тАО11-03-2004 11:48 AM
Re: How to find the offending query?
Hope this helps.
Mel
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-03-2004 12:38 PM
тАО11-03-2004 12:38 PM
Re: How to find the offending query?
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
ora-00312 online log
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-03-2004 01:03 PM
тАО11-03-2004 01:03 PM
Re: How to find the offending query?
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-03-2004 01:06 PM
тАО11-03-2004 01:06 PM
Re: How to find the offending query?
Thanks to all who replied.