Taking Systemstate Dumps when You cannot Connect to Oracle [ID 121779.1]

rongshiyuan發表於2012-08-22
Oracle
Taking Systemstate Dumps when You cannot Connect to Oracle [ID 121779.1]
 
"Checked for relevance on 15-May-2011" PURPOSE ------- The purpose of this note is to provide a method for taking system state dumps on the very rare occasions when connect internal is not possible. SCOPE & APPLICATION ------------------- This document is intended for Oracle database administrators and support analysts. Taking System State Dumps without Connecting to Oracle ------------------------------------------------------ If you are encountering a database hanging situation, you need to take system state dumps so that Oracle Support can begin to diagnose the cause of the problem. Whenever you take such dumps for a hang it is important to take at least 3 of them a few minutes apart, on all instances of your database. That way there is evidence showing whether a resource is still being held from one time to the next. Ordinarily, you can take a systemstate dump in the case of a hanging situation in one of two ways: 1. alter session set events 'immediate trace name SYSTEMSTATE level 10'; or 2. $ sqlplus connect sys/passwd as sysdba oradebug setospid oradebug unlimit oradebug dump systemstate 10 (Note, do not use any semi colons in oradebug. If using a version older than 9, you will need to use svrmgrl; connect internal) When using one of these two methods, be sure to disconnect and connect internal again in between dumps. That way each dump will be in a separate ora_.trc trace file in your user_dump_dest. On very rare occasions it is not possible to make the svrmgrl or sqlplus connection necessary for issuing these commands. In that event, there is still a back door method of getting the systemstate dump using a debugger like dbx, if that is available on your system. The process you attach to will most likely dump core and be killed, so do not attach to an Oracle background process. Here is the syntax: dbx -a PID (where PID = any oracle shadow process) dbx() print ksudss(10) ...return value printed here dbx() detach First, you will need to find a shadow process: (saki) % ps -ef |grep sqlplus osupport 78526 154096 0 12:11:05 pts/1 0:00 sqlplus scott/tiger osupport 94130 84332 1 12:11:20 pts/3 0:00 grep sqlplus (saki) % ps -ef |grep 78526 osupport 28348 78526 0 12:11:05 - 0:00 oracles734 (DESCRIPTION=(LOCAL osupport 78526 154096 0 12:11:05 pts/1 0:00 sqlplus scott/tiger osupport 94132 84332 1 12:11:38 pts/3 0:00 grep 78526 You will attach to shadow PID 28348, enter the print ksudss (10) command when the prompt comes back, and detach: (saki) % dbx -a 28348 Waiting to attach to process 28348 ... Successfully attached to oracle. warning: Directory containing oracle could not be determined. Apply 'use' command to initialize source path. Type 'help' for help. reading symbolic information ... stopped in read at 0xd016fdf0 0xd016fdf0 (read+0x114) 80410014 lwz r2,0x14(r1) (dbx) print ksudss(10) 2 (dbx) detach You will find a systemstate in the user_dump_dest directory under the PID trace (and probably a useless core file as well). (saki) % ls -lrt *28348* -rw-r----- 1 osupport dba 46922 Oct 10 12:12 ora_28348.trc core_28348: total 72 -rw-r--r-- 1 osupport dba 16567 Oct 10 12:12 core drwxr-xr-x 7 osupport dba 12288 Oct 10 12:12 ../ drwxr-x--- 2 osupport dba 512 Oct 10 12:12 ./ You will find the usual header information in the trace file. On 7.3.4 OPS systems this will be followed by some lock information. Then the system state begins. On Oracle8 OPS and non-OPS systems, and on 7.3.4 non-OPS systems the system state immediately follows the header information. Here is the beginning of our dump: Dump file /oracle/mpp/734/rdbms/log/ora_28348.trc Oracle7 Server Release 7.3.4.4.1 - Production With the distributed, replication, parallel query, Parallel Server and Spatial Data options PL/SQL Release 2.3.4.4.1 - Production ORACLE_HOME = /oracle/mpp/734 System name: AIX Node name: saki Release: 3 Version: 4 Machine: 000089914C00 Instance name: s734 Redo thread mounted by this instance: 2 Oracle process number: 0 Unix process pid: 28348, image: ksinfy: nfytype = 0x5 ksinfy: calling scggra(&se) scggra: SCG_PROCESS_LOCKING not defined scggra: calling lk_group_attach() ksinfy: returning *** SESSION ID:(12.15) 2000.10.10.12.11.06.000 ksqcmi: get or convert ksqcmi: get or convert *** 2000.10.10.12.12.08.000 =================================================== SYSTEM STATE ..... Make sure there is an END OF SYSTEM STATE in the file. You can grep for it or do a search in vi. If it is not there then the dump is not complete, possibly because max_dump_file size is too small in the init.ora. Update for 10g and higher: In some cases, no connections are allowed on the instance (in some ORA-20 situations for example). As of 10.1.x, there is a new option with SQL*Plus to allow access to an instance to generate traces. sqlplus -prelim / as sysdba For example export ORACLE_SID=PROD ## Replace PROD with the SID you want to trace sqlplus -prelim / as sysdba oradebug setmypid oradebug unlimit; oradebug dump systemstate 10 RELATED DOCUMENTS ----------------- Note:61552.1 Diagnosing Database Hanging Issues

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17252115/viewspace-741552/，如需轉載，請註明出處，否則將追究法律責任。

相關文章