Sandesh Bangera

If you have generate oracle memory dump, the following command options can be used Dump Error Stack oradebug unlimit oradebug tracefile_name oradebug dump errorstack 3 Global Hanganalyze and Systemstates sqlplus "/ as sysdba" oradebug setmypid oradebug tracefile_name oradebug unlimit oradebug -g all hanganalyze 3 oradebug -g all dump systemstate 266 oradebug dump errorstack 3

When a Streams apply process hits an error processing a transaction, the details of it are stored in the DBA_APPLY_ERROR table.  The key columns to look out for apart from the ERROR_MESSAGE is the local_transaction_id, MESSAGE_COUNT and MESSAGE_NUMBER . The column MESSAGE_COUNT provides the total number of transaction contained in each LCR and the MESSAGE_NUMBER column indicate which one of those transactions failed. Click on the link  Fetch LCR Payload    to download the .sql file  Executing the attached sql produces the DML output which Streams would have run has it not run into a issue.  Note: This DML produced by the script is not suitable for LOB columns

Execute the following query to obtain the list of locked or in use database objects in hierarchical format.  set linesize 10000 col username format a25 col owner format a15 col object_name format a30 col object_type format a15 col session_id format 99999 col locked_mode format a15 col machine format a25 /* query to check locks */  SELECT LPAD('|---',DECODE(l.xidusn,0,3,0))||l.oracle_username "UserName",         o.owner,         object_name,         o.object_type,         l.session_id,         s.serial#,         decode(locked_mode, 0, '0-None',1,'1-Null',2,'2-Row-S (SS)',3,        '3-Row-X (SX)',4,'Share',5,'5-S/Row-X (SSX)',6,'6-Exclusive',locked_mode) locked_mode,        s.status,         s.osuser,        s.machine FROM ...

Use the shell script from  here  to obtain the details of the Oracle sessions using high CPU.  Usage: Log in as oracle user and navigate to the folder where the script has been copied into. Do not forget to grant execute permissions to the script prior to invoking it. $ ./top_oracle_sessions.sh [cpu_threshold]  e.g.1  $ ./top_oracle_sessions.sh 90       This will gather information if overall CPU usage is above 90% e.g.1  $ ./top_oracle_sessions.sh        This will gather information if overall CPU usage is above 50%. This is the default setting. Notes: Use the attached script to obtain the details of the Oracle sessions using high CPU. The script works by follows Takes 2 snapshots using Linux "top" command with a interval of 2 seconds between snaps Uses the data from the second snapshot to determine if the overall CPU usage is above a set threshold. The threshold is a conf...