Database Blog

Cloning Database and Oracle Homes Complete using RMAN

Steps for CLONING USING RMAN RECOVERY WITH ANY STORAGE MANAGER backups CLONING USING RMAN INCOMPLETE RECOVERY (DISK BACKUPS) With Shell script automation.HOW TO CLONE ORACLE HOME 1. Set the Storage…

Read More

LATCH Free Sessions Monitoring and Troubleshooting

LATCH COUNT ON DATABASE:set pagesize 5000set lines 180set long 5000col username for a15col osuser for a15col program for a20col “LOGON_TIME” for a23col “LAST_CALL_HRS” for 99999.999col status for a8col machine for…

Read More

DB File Scattered read Monitoring and Troubleshooting

Note: This event signifies that the user process is reading buffers into the SGA buffer cache and is waiting for a physical I/O call to return. A db file scattered…

Read More

Troubleshooting BUFFER BUSY WAITS

Note: This wait indicates that there are some buffers in the buffer cache that multiple processes are attempting to access concurrently. Query V$WAITSTAT for the wait statistics for each class…

Read More

Oracle Wait Event Analysis

Wait event column seq# format 99999 column EVENT format a30 column p2 format 999999 column STATE format a10 column WAIT_T format 9999 select SID,SEQ#,EVENT,P1,P2,WAIT_TIME WAIT_T,SECONDS_IN_WAIT,STATE from v$session_wait where sid =…

Read More

Locked objects analysis

Checking  Timing details, Client PID of associated oracle SID set head off set verify off set echo off set pages 1500 set linesize 100 set lines 120 prompt prompt Details…

Read More

To check for Various Locking/Blocking Issues

Different Lock Modes with explanation 0, ‘None’, 1, ‘Null’, 2, ‘Row-S (SS)’, 3, ‘Row-X (SX)’, 4, ‘Share’, 5, ‘S/Row-X (SSX)’,   6, ‘Exclusive’ EXCLUSIVE (X)                                       Lock allows queries but nothing…

Read More

To Check the Timing Details of SID and event waiting

select a.sid, a.serial#, a.status, a.program, b.event,to_char(a.logon_time, ‘dd-mon-yy hh24:mi’) LOGON_TIME, to_char(Sysdate, ‘dd-mon-yy–hh24:mi’) CURRENT_TIME, (a.last_call_et/3600) “Hrs connected” from v$session a, v$session_wait b where a.sid=&sid and a.sid=b.sid; Identifying Sever Machine and Client Machine…

Read More

To check for Long Running Sessions

SESSIONS ACTIVE FOR MORE THAN AN HOUR: set pagesize 5000 set lines 150 col username for a15 col osuser for a15 col program for a20 col logon_time for a20 col…

Read More

Session information on known SID basis

set echo off set linesize 132 set verify off set feedback off set serveroutput on; declare   SID             number        := 0 ;   SERIAL          number        := 0 ;   username       …

Read More