Find Blocking Session details

Find Blocking Session details

How to find Oracle Database Blocking Session Details

In first step, find SID from v$session.

SQL> select process,sid, blocking_session from v$session where blocking_session 

 is not null;

 PROCESS SID BLOCKING_SESSION
 ———— ———- —————-
 1234 365 366
 1234 366 365

In second step find the serial number for the Blocking Session to kill using SID

 SQL> select SERIAL# from v$session where SID=365;
 SERIAL#
 ———-
 130

In third step, kill the blocking session using SID and serial number

SQL> alter system kill session ’365,130′;
 System altered.

Handle Blocking sessions for oracle database






For blocking session most important is to find out which session is getting blocked and which is holding the session.
most of the time seeing one session is blocking many other session so to handle this type of situations use this method to clear the blocks.

steps:
1) select instance_name||' - '||status||' - '||startup_time||' - '||host_name||' - '||sysdate from v$instance;

to confirm the db name are you in correct database.

2) select sid from v$lock where block=1;

this query gives you the sid's which are getting effected.

3) find the sql text for this sessions.

select sql_text from v$sqltext where hash_value=( select prev_hash_value from v$session where sid='&sid');

4) check for the other detials like their sid, serail#, osuser, machine and their status ( Active / Inactive) by passing the sid from previous query of step 2.
check for all sid's from all of the results you may not get any sql text with one sid which will be active in status that is the one main culprit blocking session which is holding lock for other sessions to execute.


select sid||' - '||serial#||' - '||osuser||' - '||username||' - '||machine||' - '||status||' - '||logon_time
from v$session where sid=&123;

Identify the holder session which is active for more confirmation you can also check the holders and waiters.

check holders & waiters:
========================
set pagesize 100
select decode(request,0,'Holder: ','Waiter: ')||sid sess, id1, id2, lmode, request, type from v$lock
where (id1, id2, type) IN (SELECT id1, id2, type from v$lock where request>0) ORDER BY id1, request;

This query results with more details the top one is the holder and others are waiters.
which is notthing but the active session which you can see with the pervous query.

5) kill the holder session

ALTER SYSTEM KILL SESSION '&sid, &serial';

or run this.

SELECT 'alter system kill session ''' || s.sid || ',' || s.SERIAL# || ''';' a,
'ps -ef |grep LOCAL=NO|grep ' || p.SPID SPID,
'kill -9 ' || p.SPID
FROM gv$session s, gv$process p
WHERE ( (p.addr(+) = s.paddr) AND (p.inst_id(+) = s.inst_id))
AND s.sid = &sid;

you will get the result like
alter system kill session '123, 32422';
ps -ef |grep LOCAL=NO|grep 234223
kill -9 234223

use any one of the result to clear the lock.

Most of the time a DBA  can not directly kill the locks, in this situation consult with the application team to clear the lock and use this easy steps to clear the lock.

Tracking Oracle database growth:

Oracle script to check the database growth

SET LINESIZE 200

SET PAGESIZE 200

COL "Database Size" FORMAT a13

COL "Used Space" FORMAT a11

COL "Used in %" FORMAT a11

COL "Free in %" FORMAT a11

COL "Database Name" FORMAT a13

COL "Free Space" FORMAT a12

COL "Growth DAY" FORMAT a11

COL "Growth WEEK" FORMAT a12

COL "Growth DAY in %" FORMAT a16

COL "Growth WEEK in %" FORMAT a16

SELECT

(select min(creation_time) from v$datafile) "Create Time",

(select name from v$database) "Database Name",

ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) || ' MB' "Database Size",

ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 ),2) || ' MB' "Used Space",

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) || '% MB' "Used in %",

ROUND((FREE.P / 1024 / 1024 ),2) || ' MB' "Free Space",

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - ((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) || '% MB' "Free in %",

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile),2) || ' MB' "Growth DAY",

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3) || '% MB' "Growth DAY in %",

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)*7,2) || ' MB' "Growth WEEK",

ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*7,3) || '% MB' "Growth WEEK in %"

FROM    (SELECT BYTES FROM V$DATAFILE

UNION ALL

SELECT BYTES FROM V$TEMPFILE

UNION ALL

SELECT BYTES FROM V$LOG) USED,

(SELECT SUM(BYTES) AS P FROM DBA_FREE_SPACE) FREE

GROUP BY FREE.P;

Output-

The below script lists the details of database growth per month:

select to_char(creation_time, 'MM-RRRR') "Month", sum(bytes)/1024/1024/1024 "Growth in GB"
from sys.v_$datafile
where to_char(creation_time,'RRRR')='2014'
group by to_char(creation_time, 'MM-RRRR')
order by  to_char(creation_time, 'MM-RRRR');

Sample output from the script:

Month                                                                       Growth in GB
------------------------------------------------------------      -----------------
05-2014                                                                       101.588867
06-2014                                                                       525.609375
07-2014                                                                             57.5
09-2014                                                                               10
10-2014                                                                       31.0976563
11-2014                                                                               52

Below script is useful for tablespace level database growth:

select b.tsname tablespace_name , MAX(b.used_size_mb) cur_used_size_mb , round(AVG(inc_used_size_mb),2)avg_increas_mb  

from ( SELECT a.days,a.tsname , used_size_mb , used_size_mb - LAG (used_size_mb,1) OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb

from ( SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days  ,ts.tsname ,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb

from dba_hist_tbspc_space_usage  tsu , dba_hist_tablespace_stat  ts ,dba_hist_snapshot  sp, dba_tablespaces  dt   where tsu.tablespace_id= ts.ts# 

AND tsu.snap_id = sp.snap_id

AND ts.tsname = dt.tablespace_name AND sp.begin_interval_time > sysdate-7

GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'), ts.tsname 

ORDER BY ts.tsname, days ) a ) b GROUP BY b.tsname ORDER BY b.tsname;

Sample output from the script:

TABLESPACE_NAME                CUR_USED_SIZE_MB AVG_INCREAS_MB

---------------------------------         ---------------------------   -------------------------

DW_AGG                                       8150.31                                 46.94

DW_DAC_REP_DATA                    858.31                                   3.29

DW_DIM_DATA                             7078.81                                 6

DW_DIM_INDX                              3229.69                                3.89

DW_DIM_STG                                1202.38                                -9.61

DW_FACT_DATA                          304706.94                            894.51

DW_FACT_INDX                           32227.81                             183.04

DW_FACT_STG                             5483.81                                120.7

DW_INFA_DOMAIN_DATA              1                                      0

DW_INFA_REP_DATA                 3617.88                                2.43

DW_OTHER                                  14314.88                              83.68

DW_OTHER_INDX                        203.81                                  .71

PRD_BIPLATFORM                      7493                                     64.57

PRD_MDS                                        13.44                                    0

SYSAUX                                       7097.75                                 -3.94

SYSTEM                                       2408.31                                10.43

UNDOTBS1                                 62488.13                               7074.66

UNDOTBS2                                 13088.38                              -866.43

UNDOTBS3                                  81902                                   10326

USERS                                          2015.13                                  -2.68

Tracking Oracle database growth:

select to_char(CREATION_TIME,'RRRR') year,to_char(CREATION_TIME,'MM') month,round(sum(bytes)/1024/1024/1024) gb
from v$datafile group by to_char(CREATION_TIME,'RRRR'),to_char(CREATION_TIME,'MM') order by 1,2;

YEAR MO         GB
---- -- ----------
2000 04          9
2004 06          5
2004 11          5
2005 01          2
2005 02          4
2005 06          4
2005 09         20
2005 10          2
2005 11         12
2006 01          8
2007 01          1
2007 06          8
2007 08       1490
2008 01          8
2008 02          9
2008 03         44

Prerequisite check “CheckActiveFilesAndExecutables” failed (files are active) in Patching activity.

During apply patch with opatch command, on windows we will get the following error:

Prerequisite check “CheckActiveFilesAndExecutables” failed

On Windows System, When you try to apply the Patch after clean shutdown.

Sometime following error occured:

Following files are active :
d:\oracle\SID\11203\bin\oracommon11.dll
d:\oracle\SID\11203\bin\oraclient11.dll
d:\oracle\SID\11203\dbhome_1\bin\orageneric11.dll
d:\oracle\SID\\11203\bin\orapls11.dll

d:\oracle\SID\11203\bin\oran11.dll
d:\oracle\SID\11203\bin\oraxml11.dll
d:\oracle\SID\11203\bin\oci.dll
d:\oracle\SID\11203\bin\orannzsbb11.dll
d:\oracle\SID\11203\bin\oraasmclnt11.dll

INFO:Prerequisite check “CheckActiveFilesAndExecutables” failed.

OPatch failed with error code = 74/44
This is the common error occurred during the patch applied on window system.

One command is available for check which process is holdling lock on these files

Tasklist /m ora*

You can check with the tasklist command. It will provide you list on cmd.

Note:
1. Winmgmt (Windows Management Instrumentation ) is the service used by tasklist command.
If you already disable this service as mentioned in some readme file then you need to enable and start to run tasklist command
2. You always used cmd.ext run as administrator so that you will able to see other user open task also.
Suppose if you find the ora* files is used by some jave.exe then you can also kill that process.

Note: Before fire kill command check at internet what the effect of process if you kill it.
Sometime window process locked and it will cause server shutdown if you kill window process. So always check effect first.
IF some application java.exe is used you can kill them.

taskkill /pid processid_number

Source From  Sandeep Singh

https://smarttechways.com/2015/03/09/prerequisite-check-checkactivefilesandexecutables-failed-files-are-active-in-patching-activity/comment-page-1/#comment-531

Step To Generate AWR Report

Step 1: Go to $ORACLE_HOME/rdbms/admin

                

Step 2: Run command ls -lrt awr*

                

Step 3: Connect to sqlplus

                 

Step 4:  From the sql prompt run awrrpt (or) awrrpti (for specified instance)

             @awrrpt.sql (if u are in $ORACLE_HOME/rdbms/admin)

             or if You are at any location then

              SQL>   @$ORACLE_HOME/rdbms/admin/awrrpt.sql

Step 5: it will now ask for the report as either ‘HTML’ or ‘TEXT’. (choose one)

             SQL> @awrrpt.sql

            Current Instance

            ~~~~~~~~~~~~~~~~

             DB Id              DB Name      Inst Num    Instance

            -----------            ------------ -------- --------    ----

            2735184012      ABC                1               ABC

            Specify the Report Type

             ~~~~~~~~~~~~~~~~~~~~~~~

           Would you like an HTML report, or a plain text report?

           Enter 'html' for an HTML report, or 'text' for plain text

           Defaults to 'html'

           Enter value for report_type: HTML(I am selecting HTML)

Step 6: Select number of days you want to go back or just hit enter for listing all completed snapshots.

           For Example

          Specify the number of days of snapshots to choose from

          ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

          Entering the number of days (n) will result in the most recent

          (n) days of snapshots being listed.  Pressing <return> without

          specifying a number lists all completed snapshots.

           Enter value for num_days:

          (just Hit enter to get all snapshot details)

Step 7: Then specify Begin and End snapshot Ids.

 

            For E.g

            Specify the Begin and End Snapshot Ids

            ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

            Enter value for begin_snap: 20429

            Begin Snapshot Id specified: 20429

            Enter value for end_snap: 20431

Step 8: Here you specify the name of the report or select the default name assigned.

Step 9: The report gets generated.

Step 10: Exit SQLPLUS.

Step 11: Run command ls –ltr newname to show the new file created under the path you are in

ORA:04031 ( Out Of Memory)

Hi , this is the very frequent issue  that comes in database. Here I am sharing some of option to analyse the root cause.

* First of all we need to check trace file and alert log, may be trace file can give you root cause.

* This error also come due to swap memory issue, so we have to consider that on also.

* Issue is due to shared pool fragmentation, which can re-occur as the load increases and may        in a day or two days.

* The primary cause for ORA-04031 is also Hard Parse. So for this we have to genrate AWR        report during the problem Time and should check the load profile section for Hard-                    Parses/Section.

* This should be secound option, we can query v$memory_resize_ops view to get details of            memory consumption.

   (check component,initial_size, target_size,start_time,end_time...) memory resizing occur if          there is a memory pressure.

SQL> desc v$memory_resize_ops;


 Name                                              Null?    Type
 ----------------------------------------- -------- ----------------------------
 COMPONENT                                          VARCHAR2(64)
 OPER_TYPE                                             VARCHAR2(13)
 OPER_MODE                                           VARCHAR2(9)
 PARAMETER                                           VARCHAR2(80)
 INITIAL_SIZE                                          NUMBER
 TARGET_SIZE                                         NUMBER
 FINAL_SIZE                                             NUMBER
 STATUS                                                      VARCHAR2(9)
 START_TIME                                            DATE

 END_TIME                                                DATE