Sandesh Bangera

The following set of diagnostic data collection routines must be run when database is not responding to TNS connection request and queries to views like v$session, etc hang. The symptoms of this hang are Unable to connect using tnsnames to local instance. E.g. if ssh on node 01A, then cannot sqlplus to instance hosted on that server. E.g. sqlplus username/password@connectstring Unable to connect using tnsname .e.g sqlplus username/password@connectstring_active Bequeath connections are successful. E.g. sqlplus / as sysdba Querying certain V$ views causes the session to hang. Diagnostic Data set 1:   SQL> set lines 250 pages 1000   SQL> spool xle_xkclfx.out   SQL> select * from x$le;   SQL> select * from x$kclfx;   SQL> exec dbms_lock.sleep(60);   SQL> select * from x$le;   SQL> select * from x$kclfx;   SQL> exec dbms_lock.sleep(60);   SQL> select * from x$le;   SQL> select ...

At times while resolving streams replication issues, it might be faster to generate individual queries associated with an failed LCR instead of relying on routines provided by the DBMS_APPLY_ADM package.In addition the script attached to this blog can be used to clear LCR back log in parallel as long as the source commit order is maintained. The driving query in the anonymous block is as follows and chose an appropriate filter to handle your issue for currec in (  select local_transaction_id, message_count  from dba_apply_error    where  -- local_transaction_id = '70.18.2231436' --# Use this filter to handle one LCR at a time     -- ERROR_MESSAGE LIKE '%DBOR_USERS%' --# Use this filter to handle LCR which have errors in the same table     -- error_creation_time >= to_timestamp('18-MAR-2013 08:00:00','dd-MON-RRRR HH24:MI:SS')  --#  Use the following two to filter on a date range.     --and error_creati...

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...

set lines 1000 pages 200 break on inst_id skip 1 col sid format 9999 col serial# format 999999 col username format a10 col Command format a15 col machine format a15 col os_user format a15 col program format a35 col logontime format a17 col ospid format a10 col event format a55 col sql_text format a75 SELECT  a.inst_id,          a.sid,          a.serial#,           b.spid ospid,         TO_CHAR(logon_time,'dd-Mon-rr hh24:mi') LogonTime,          lower(a.username) username,          lower(a.machine) machine,          lower(status) status,          rpad(lower(osuser),15,' ') OS_User,              lower(a.program) program,         decode(command,2,'INSERT',3,'SELECT',6,'UPDATE',7,'DELETE',47,'PL/SQL   ...

At times when you try to delete a service from CRS GI stack, you may the below error PRKP-1024 : The service [service_name] is still running. This is despite the fact that the service is definitely OFFLINE. While its not clear (to me at least) as  to why CRS has got itself into a twist, but a quick check using "crs_stat -t" may show that the service is indeed offline but a related entry ending with ".cs" may be online. On our rig no amount of restarting CRS or rebooting the cluster helped us, goes to show the amount of muddle that went in before, therefore the following can be done in order to get rid of it 1. Use the crs_stat -p | grep .srv to obtain the full name of the service.   e.g NAME=ora.[cluster_name].[service_name].cs 2. Use the crs_stop command to put offline the service    e.g crs_stop ora.[cluster_name].[service_name].cs 3. Repeat steps 1 and 2 to stop all other related resources e.g. srv You c...

There comes a time in your life (more often then not and when you least expect) you will find that your sql session is hanging or some client application is hung or the database itself is hanging. While utilities AWR and ADDM are helpful, these don't tell the internal status of the instance, in particular if you are stuck with a hung instance. In such cases running AWR/ADDM is out of the equation. Oracle provides a additional tool as part of "oradebug" called "hanganalyze". This tool can be used to either diagnose a known hung session or the database in general. This blog contains steps to generate hang analyze trace file Run the following query to obtain the SPID of the hung session Obtain session SPID select a.inst_id,         a.sid,         a.serial#,          b.spid ospid,         to_char(logon_time,'dd-Mon-rr hh24:mi') LogonTime,         lower(...

If you are asked to provide an extract of SAN allocation, then the following query can be used to provide the breakdown ASM Disk Info Query $ sqlplus / as sysdba set pages 200 lines 250 --colsep , col DiskPath format a50 col diskname format a20 col diskgroup_name format a20 break on Diskgroup_name skip 2 compute sum label 'Group_Size-GB' of total_gb on Diskgroup_name compute sum label 'Group_Size-GB' of Used_GB on Diskgroup_name compute sum label 'Group_Size-GB' of Free_GB on Diskgroup_name compute Avg label 'Free_percent%' of free_percent on Diskgroup_name select a.name Diskgroup_name,        b.name diskname,        b.path DiskPath,        round((b.total_mb/1024),2) Total_GB,        round(((b.total_mb-b.free_mb)/1024),2) Used_GB,        round((b.free_mb/1024),2) Free_GB,        100 - round((((b.total_mb - b.free_mb)*100)/b.total_mb),2) free_pe...

It is not possible to directly restore a manual or automatic OCR backup if the OCR is located in an ASM disk group. This is caused by the fact that the command 'ocrconfig -restore' requires ASM to be up and running in order to restore an OCR backup to an ASM disk group. However, for ASM to be available, the CRS stack must have been successfully started. For the restore to succeed, the OCR also must not be in use (r/w), i.e. no CRS daemon must be running while the OCR is being restored The following steps must be used in order to recover from a situation where the disks belonging to the ASM diskgroup +MGMT have been lost. The instructions assumes that new/fixed disks are now available on the effected servers and the following have already been carried out on both nodes of the cluster. - Disks for OCR and Voting are visible to the oracle cluster - The disk ownership has been changed from root:disk to oracle:dba or its equivalent accounts - /etc/udev/rules/50-udev.rules fi...