=======================================
For single Tablespace ---> change TB name in the script
=======================================
col "Tablespace" for a22
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999
select df.tablespace_name "Tablespace",
df.totalspace "Total MB", totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name and df.tablespace_name='PCMS_DATA' order by "Pct. Free";
col FILE_NAME for a50
set lines 200 pages 200
select FILE_NAME,BYTES/1024/1024,maxbytes/1024/1024, AUTOEXTENSIBLE from dba_data_files where TABLESPACE_NAME='PCMS_DATA'; it will give information like tablespace in file system or ASM disk
alter tablespace USERS add datafile '/data03/oradata/nupath/users05.dbf' size 1G autoextend on next 512M maxsize unlimited; -- Datafile in file system
alter tablespace LLMINDX add datafile '+DATA' size 1G autoextend on maxsize 32000M; -- Datafile in ASM
ALTER DATABASE DATAFILE '/u02/oradata/PROD8/devl_PROD8_02.dbf' AUTOEXTEND ON NEXT 250m MAXSIZE UNLIMITED; if autoextend off
alter database datafile '/data03/oradata/nas/system09.dbf' resize 15G;
**** tablespace free,used sizes,%space used***
column "Tablespace" format a13
column "Used MB" format 99,999,999
column "Free MB" format 99,999,999
column "Total MB" format 99,999,999
select
fs.tablespace_name "Tablespace",
(df.totalspace - fs.freespace) "Used MB",
fs.freespace "Free MB",
df.totalspace "Total MB",
round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
(select
tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from
dba_data_files
group by
tablespace_name
) df,
(select
tablespace_name,
round(sum(bytes) / 1048576) FreeSpace
from
dba_free_space
group by
tablespace_name
) fs
where
df.tablespace_name = fs.tablespace_name;
datafile shrink
===============
https://blog.dbi-services.com/resize-your-oracle-datafiles-down-to-the-minimum-without-ora-03297/ --- datafile with auto option
********************************************************************
set linesize 1000 pagesize 0 feedback off trimspool on
with
hwm as (
-- get highest block id from each datafiles ( from x$ktfbue as we don't need all joins from dba_extents )
select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks
from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn
),
hwmts as (
-- join ts# with tablespace_name
select name tablespace_name,relative_fno,hwm_blocks
from hwm join v$tablespace using(ts#)
),
hwmdf as (
-- join with datafiles, put 5M minimum for datafiles with no extents
select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024) hwm_bytes,bytes,autoextensible,maxbytes
from hwmts right join dba_data_files using(tablespace_name,relative_fno)
)
select
case when autoextensible='YES' and maxbytes>=bytes
then -- we generate resize statements only if autoextensible can grow back to current size
'/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
||'M from '||to_char(ceil(bytes/1024/1024),999999)||'M */ '
||'alter database datafile '''||file_name||''' resize '||ceil(hwm_bytes/1024/1024)||'M;'
else -- generate only a comment when autoextensible is off
'/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
||'M from '||to_char(ceil(bytes/1024/1024),999999)
||'M after setting autoextensible maxsize higher than current size for file '
|| file_name||' */'
end SQL
from hwmdf
where
bytes-hwm_bytes>1024*1024 -- resize only if at least 1MB can be reclaimed
order by bytes-hwm_bytes desc
/
********************************************************************** ---- Datafile with auto off
**********************************************************************
set linesize 1000 pagesize 0 feedback off trimspool on
with
hwm as (
-- get highest block id from each datafiles ( from x$ktfbue as we don't need all joins from dba_extents )
select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks
from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn
),
hwmts as (
-- join ts# with tablespace_name
select name tablespace_name,relative_fno,hwm_blocks
from hwm join v$tablespace using(ts#)
),
hwmdf as (
-- join with datafiles, put 5M minimum for datafiles with no extents
select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024) hwm_bytes,bytes,autoextensible,maxbytes
from hwmts right join dba_data_files using(tablespace_name,relative_fno) where tablespace_name not like '%UNDO%'
)
select
case when autoextensible='NO' and maxbytes>=bytes
then -- we generate resize statements only if autoextensible can grow back to current size
'/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
||'M from '||to_char(ceil(bytes/1024/1024),999999)||'M */ '
||'alter database datafile '''||file_name||''' resize '||ceil(hwm_bytes/1024/1024)||'M;'
else -- generate only a comment when autoextensible is off
'/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
||'M from '||to_char(ceil(bytes/1024/1024),999999)||'M */ '
||'alter database datafile '''||file_name||''' resize '||ceil(hwm_bytes/1024/1024)||'M;'
end SQL
from hwmdf
where
bytes-hwm_bytes>1024*1024 -- resize only if at least 1MB can be reclaimed
order by bytes-hwm_bytes desc
/
Sample mail:
===========
we have received the case due to the below tablespace usage trap for the 'CMC' database.
ORACLE_FREE_TS:CMC:CN_D01
ORACLE_FREE_TS:CMC:CN_I01
we have extended the tablespace size by adding the datafiles in the /data03 file system. we have tried to reclaim the space in /data4 and /data05 file systems but there is no scope to reclaim the space on it.
Note>> we told earlier there is no disk slot available for any additional disk and and we are in migration process to the new systems (refer to INC000002717511)
we have already disabled auto extend for some data files located at the filesystem /data05 but still trap exits in the server.
[root@s609479fr4sl001 ~]# df -h /data01 /data02 /data03 /data04 /data05
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_root-lv_data01
100G 75G 21G 79% /data01
/dev/mapper/vg_data02-data02
580G 495G 56G 90% /data02
/dev/mapper/vg_data02-data03
247G 84G 151G 36% /data03
/dev/mapper/vg_data04-lv_data04
826G 693G 91G 89% /data04
/dev/mapper/vg_data05-lv_data05
1.1T 996G 49G 96% /data05
we don't have sufficient space in the above file systems for further space addition, please take a look and let us know if any information needed.
########################
To check long locks
#######################
set lines 200 pages 200
select username,sid,status,sql_hash_value,status ,blocking_session,SECONDS_IN_WAIT from v$session where blocking_session is not null;
col USERNAME for a15
col OSUSER for a15
col PROGRAM for a25
col MACHINE for a35
select username,osuser,sid,serial#,sql_hash_value,program,machine,status,sql_id from v$session where sid=416;
select sql_text from v$sqlarea where hash_value=1783856273;
sample mail:
-----------
we have received the case due to the below long locks traps in the MAPROD database.
we could see the below long locks in the database, please check and let us know if you want us to kill the session.
USERNAME SID STATUS SQL_HASH_VALUE STATUS BLOCKING_SESSION SECONDS_IN_WAIT
--------------- ---------- -------- -------------- -------- ---------------- ---------------
WMS14PRDMP 1912 ACTIVE 4076346499 ACTIVE 2047 4426
Waiting session:
=============
USERNAME OSUSER SID SERIAL# SQL_HASH_VALUE PROGRAM MACHINE STATUS SQL_ID
--------------- --------------- ---------- ---------- -------------- ------------------------- ----------------------------------- -------- -------------
WMS14PRDMP wmsadmin 1912 3695 4076346499 JDBC Thin Client s619117rgvl14.uklond6.savvis.net ACTIVE 3ywf5ngtgh743
SYS@MAPROD AS SYSDBA > select sql_fulltext from v$sqlarea where HASH_VALUE=4076346499;
SQL_FULLTEXT
--------------------------------------------------------------------------------
update ASN_DETAIL set RECEIVED_QTY=NVL(RECEIVED_QTY,:"SYS_B_0") + :1 , SHIPPED_
QTY=SHIPPED_QTY+ :2 , RECEIVED_LPN_COUNT=NVL(RECEIVED_LPN_COUNT,:"SYS_B_1")+:3
, UNITS_ASSIGNED_TO_LPN=NVL(UNITS_ASSIGNED_TO_LPN,:"SYS_B_2")+ :4 , TC_PURCHAS
E_ORDERS_ID=:5 , TC_PO_LINE_ID=:6 , NBR_OF_PACK_FOR_CATCH_WT=:7 , STD_PACK_QT
Y=:8 , ASN_DETAIL_STATUS=:9 , LAST_UPDATED_DTTM=GETDATE() , LAST_UPDATED_SOURC
E=:10 , LAST_UPDATED_SOURCE_TYPE=:11 , ACTUAL_WEIGHT_RECEIVED=:12 , ACTUAL_WE
IGHT=:13 , CHECKSUM=:14 where ASN_DETAIL_ID=:15 and ASN_ID=:16
Blocking session:
===============
USERNAME OSUSER SID SERIAL# SQL_HASH_VALUE PROGRAM MACHINE STATUS SQL_ID
--------------- --------------- ---------- ---------- -------------- ------------------------- ----------------------------------- -------- -------------
WMS14PRDMP wmsadmin 2047 681 0 JDBC Thin Client s619117rgvl14.uklond6.savvis.net INACTIVE
we need your confirmation to kill the sessions to clear the locks.
#######################
Process and sessions
#######################
select resource_name, current_utilization, max_utilization,LIMIT_VALUE from v$resource_limit where resource_name in ('processes','sessions');
select count(1) Currnet_proc_count from v$process;
select count(1) Currnet_session_count from v$session;
select username, status, machine,count(*) from v$session group by username, status, machine, program order by 4 desc;
sample mail:
----------
CAUSE:
This incident has been raised as the number of process has crosshed the defined monitoring threshold value.
Database : instlprd@NE1ITCPRHDB39
INVESTIGATION:
Checked and below is the max value set for the database.
NAME TYPE VALUE
------------- ----------- -------
processes integer 500
Current Utilization :
SYS@instlprd AS SYSDBA > select count(1) from v$process;
COUNT(1)
----------
410
Current userwise INACTIVE sessions count :
USERNAME STATUS COUNT(1)
------------------------------ -------- ----------
DBA_RPT INACTIVE 1
DBSNMP INACTIVE 2
ACCOUNT_DB INACTIVE 157
OFFRCAT_DB INACTIVE 87
ACCOUNT_DB_APP INACTIVE 1
RESOURCE_MGMT_DB INACTIVE 90
From above we can see around 330 sessions are INACTIVE.
ACTION TAKEN:
None.
NEXT ACTION:
We need your permission to kill the inactive sessions to make avail more processes to database.
Or we can increase the Processes parameter value to a higher value, which needs a database bounce.
####################################
High archive generation
####################################
== Archivelog generation on a daily basis ===
set pages 1000
select trunc(COMPLETION_TIME,'DD') Day, thread#, round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives_Generated from v$archived_log
group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;
== Archive log generation on an hourly basis ===
alter session set nls_date_format='dd-MON-yy hh24:mi:ss';
set pages 1000
select trunc(COMPLETION_TIME,'HH') Hour,thread# , round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives from v$archived_log
group by trunc(COMPLETION_TIME,'HH'),thread# order by 1;
####################
Archive destination is full:
####################
we have moved some archives to other location
RMAN> catalog start with '/db1/vol03/oradata/archbkp/'; ======><archicves move location )
#########manully run the archuive backup script#############
run {
crosscheck archivelog all;
ALLOCATE CHANNEL ch00 TYPE DISK;
ALLOCATE CHANNEL ch01 TYPE DISK;
BACKUP AS COMPRESSED BACKUPSET
filesperset 20
FORMAT '/db1/vol05/orabackup/RMAN/dispordprd1/dispordprd1_fl_al_%s_%p_%t' ==>>backup pices location
archivelog all delete input;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
}
Sample mail:
==========
This case has been created due to high archive generation trap in the "SCPPDEV" database.
This is just information message only, its generated almost 186 archive/transaction logs on 24th Jan 2018 in the DB as below
Archive generation daily basis:
DAY THREAD# MB ARCHIVES_GENERATED
----------- ---------- ------- ------------------
22-JAN-18 1 46313 262
23-JAN-18 1 74362 418
24-JAN-18 1 32817 186
Archive generation hourly basis:
HOUR THREAD# MB ARCHIVES
------------------- ---------- -------- ----------
24-JAN-18 07:00:00 1 13197 73
24-JAN-18 08:00:00 1 19286 108
24-JAN-18 14:00:00 1 141 1
Currently archive generation back to normal state and related trap was cleared in the server as well.
I have set this incident to 'Resolved' status, but would like your confirmation before closing it.
####################
Open Cursors
####################
Total Open Cursors By Session:or bu username
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current';
Total Open Cursors By username & HostMachine:
select sum(a.value) total_cur, avg(a.value) avg_cur,
max(a.value) max_cur, s.username, s.machine
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current'
group by s.username, s.machine order by 1 desc;
MAX allowed Cursors and total open Cursors:
select max(a.value) as highest_open_cur, p.value as max_open_cur
from v$sesstat a, v$statname b, v$parameter p
where a.statistic# = b.statistic# and b.name = 'opened cursors current'
and p.name= 'open_cursors'
group by p.value;
Identify the session by its Oracle username to retrieve the sid value. Connect to the Oracle instance as the SYS or SYSTEM or a user with DBA privileges using SQL*Plus. Execute the following SQL statement to obtain the session's sid value:
Code:
SQL> SELECT sid FROM v$session WHERE username = 'TOM';
SID
----------
135
The sid value is used in the next step to identify the session to investigate.
List the session's SQL statement addresses, which have more than one active reference. Using the sid value from the previous statement, execute a query against the v$open_cursor view.
Code:
SQL> SELECT COUNT(*), address FROM v$open_cursor WHERE sid = 135 GROUP BY address HAVING COUNT(address) > 1 ORDER BY COUNT(*);
COUNT(*) ADDRESS
---------- --------
2 35E6083C
The result lists each cursor, which has been opened by the session more than once in descending order.
Obtain the actual SQL statement for each address returned in step 2. This can be accomplished by querying the v$sql view using the address value.
Code:
SQL> SELECT sql_fulltext FROM v$sql WHERE address = '35F97908';
Sample mail:
===========
Problem Description:
we have received the case due to OPEN_CURSORS parameter reached to the 100% in the 'pimprod' database.
Trap>>
ORACLE_OPEN_CURSORS:pimprod = 76
Steps taken to resolve (Technical Summary):
we have verified the database, we could see below sessions causing high cursors utilization.
HIGHEST_OPEN_CUR MAX_OPEN_CUR
---------------- -------------------------
300 300
TOTAL_CUR AVG_CUR MAX_CUR USERNAME MACHINE
--------- ---------- ---------- ------------- --------------------------------
301 150.5 300 EOS_MDW NE1ITCPRHEWS03.ne1.savvis.net
COUNT(*) ADDRESS
-------- ----------------
299 00000000B3476AC8
SYS@pimprod AS SYSDBA > SELECT sql_fulltext FROM v$sql WHERE address = '00000000B3476AC8';
SQL_FULLTEXT
--------------------------------------------------------------------------------
select EVENT_NAME,CREATE_DT,AUXDATA,REFERENCE,COMMENTS from EVENT_INSTANCE where
STATUS_CD = 7 and CREATE_DT < :1 and CONSUME_DT is null and ROWNUM <= 1000 ord
er by CREATE_DT
>>There are two ways to overcome the issue.
1)Kill aforementioned SID which is holding maximum cursors.
2)Increase the open_cursors value to 500 from 300 via a CRQ
Next action:
-----------
Please let us know how do you want us to proceed further.
For single Tablespace ---> change TB name in the script
=======================================
col "Tablespace" for a22
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999
select df.tablespace_name "Tablespace",
df.totalspace "Total MB", totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name and df.tablespace_name='PCMS_DATA' order by "Pct. Free";
col FILE_NAME for a50
set lines 200 pages 200
select FILE_NAME,BYTES/1024/1024,maxbytes/1024/1024, AUTOEXTENSIBLE from dba_data_files where TABLESPACE_NAME='PCMS_DATA'; it will give information like tablespace in file system or ASM disk
alter tablespace USERS add datafile '/data03/oradata/nupath/users05.dbf' size 1G autoextend on next 512M maxsize unlimited; -- Datafile in file system
alter tablespace LLMINDX add datafile '+DATA' size 1G autoextend on maxsize 32000M; -- Datafile in ASM
ALTER DATABASE DATAFILE '/u02/oradata/PROD8/devl_PROD8_02.dbf' AUTOEXTEND ON NEXT 250m MAXSIZE UNLIMITED; if autoextend off
alter database datafile '/data03/oradata/nas/system09.dbf' resize 15G;
**** tablespace free,used sizes,%space used***
column "Tablespace" format a13
column "Used MB" format 99,999,999
column "Free MB" format 99,999,999
column "Total MB" format 99,999,999
select
fs.tablespace_name "Tablespace",
(df.totalspace - fs.freespace) "Used MB",
fs.freespace "Free MB",
df.totalspace "Total MB",
round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
(select
tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from
dba_data_files
group by
tablespace_name
) df,
(select
tablespace_name,
round(sum(bytes) / 1048576) FreeSpace
from
dba_free_space
group by
tablespace_name
) fs
where
df.tablespace_name = fs.tablespace_name;
datafile shrink
===============
https://blog.dbi-services.com/resize-your-oracle-datafiles-down-to-the-minimum-without-ora-03297/ --- datafile with auto option
********************************************************************
set linesize 1000 pagesize 0 feedback off trimspool on
with
hwm as (
-- get highest block id from each datafiles ( from x$ktfbue as we don't need all joins from dba_extents )
select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks
from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn
),
hwmts as (
-- join ts# with tablespace_name
select name tablespace_name,relative_fno,hwm_blocks
from hwm join v$tablespace using(ts#)
),
hwmdf as (
-- join with datafiles, put 5M minimum for datafiles with no extents
select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024) hwm_bytes,bytes,autoextensible,maxbytes
from hwmts right join dba_data_files using(tablespace_name,relative_fno)
)
select
case when autoextensible='YES' and maxbytes>=bytes
then -- we generate resize statements only if autoextensible can grow back to current size
'/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
||'M from '||to_char(ceil(bytes/1024/1024),999999)||'M */ '
||'alter database datafile '''||file_name||''' resize '||ceil(hwm_bytes/1024/1024)||'M;'
else -- generate only a comment when autoextensible is off
'/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
||'M from '||to_char(ceil(bytes/1024/1024),999999)
||'M after setting autoextensible maxsize higher than current size for file '
|| file_name||' */'
end SQL
from hwmdf
where
bytes-hwm_bytes>1024*1024 -- resize only if at least 1MB can be reclaimed
order by bytes-hwm_bytes desc
/
********************************************************************** ---- Datafile with auto off
**********************************************************************
set linesize 1000 pagesize 0 feedback off trimspool on
with
hwm as (
-- get highest block id from each datafiles ( from x$ktfbue as we don't need all joins from dba_extents )
select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks
from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn
),
hwmts as (
-- join ts# with tablespace_name
select name tablespace_name,relative_fno,hwm_blocks
from hwm join v$tablespace using(ts#)
),
hwmdf as (
-- join with datafiles, put 5M minimum for datafiles with no extents
select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024) hwm_bytes,bytes,autoextensible,maxbytes
from hwmts right join dba_data_files using(tablespace_name,relative_fno) where tablespace_name not like '%UNDO%'
)
select
case when autoextensible='NO' and maxbytes>=bytes
then -- we generate resize statements only if autoextensible can grow back to current size
'/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
||'M from '||to_char(ceil(bytes/1024/1024),999999)||'M */ '
||'alter database datafile '''||file_name||''' resize '||ceil(hwm_bytes/1024/1024)||'M;'
else -- generate only a comment when autoextensible is off
'/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
||'M from '||to_char(ceil(bytes/1024/1024),999999)||'M */ '
||'alter database datafile '''||file_name||''' resize '||ceil(hwm_bytes/1024/1024)||'M;'
end SQL
from hwmdf
where
bytes-hwm_bytes>1024*1024 -- resize only if at least 1MB can be reclaimed
order by bytes-hwm_bytes desc
/
Sample mail:
===========
we have received the case due to the below tablespace usage trap for the 'CMC' database.
ORACLE_FREE_TS:CMC:CN_D01
ORACLE_FREE_TS:CMC:CN_I01
we have extended the tablespace size by adding the datafiles in the /data03 file system. we have tried to reclaim the space in /data4 and /data05 file systems but there is no scope to reclaim the space on it.
Note>> we told earlier there is no disk slot available for any additional disk and and we are in migration process to the new systems (refer to INC000002717511)
we have already disabled auto extend for some data files located at the filesystem /data05 but still trap exits in the server.
[root@s609479fr4sl001 ~]# df -h /data01 /data02 /data03 /data04 /data05
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_root-lv_data01
100G 75G 21G 79% /data01
/dev/mapper/vg_data02-data02
580G 495G 56G 90% /data02
/dev/mapper/vg_data02-data03
247G 84G 151G 36% /data03
/dev/mapper/vg_data04-lv_data04
826G 693G 91G 89% /data04
/dev/mapper/vg_data05-lv_data05
1.1T 996G 49G 96% /data05
we don't have sufficient space in the above file systems for further space addition, please take a look and let us know if any information needed.
########################
To check long locks
#######################
set lines 200 pages 200
select username,sid,status,sql_hash_value,status ,blocking_session,SECONDS_IN_WAIT from v$session where blocking_session is not null;
col USERNAME for a15
col OSUSER for a15
col PROGRAM for a25
col MACHINE for a35
select username,osuser,sid,serial#,sql_hash_value,program,machine,status,sql_id from v$session where sid=416;
select sql_text from v$sqlarea where hash_value=1783856273;
sample mail:
-----------
we have received the case due to the below long locks traps in the MAPROD database.
we could see the below long locks in the database, please check and let us know if you want us to kill the session.
USERNAME SID STATUS SQL_HASH_VALUE STATUS BLOCKING_SESSION SECONDS_IN_WAIT
--------------- ---------- -------- -------------- -------- ---------------- ---------------
WMS14PRDMP 1912 ACTIVE 4076346499 ACTIVE 2047 4426
Waiting session:
=============
USERNAME OSUSER SID SERIAL# SQL_HASH_VALUE PROGRAM MACHINE STATUS SQL_ID
--------------- --------------- ---------- ---------- -------------- ------------------------- ----------------------------------- -------- -------------
WMS14PRDMP wmsadmin 1912 3695 4076346499 JDBC Thin Client s619117rgvl14.uklond6.savvis.net ACTIVE 3ywf5ngtgh743
SYS@MAPROD AS SYSDBA > select sql_fulltext from v$sqlarea where HASH_VALUE=4076346499;
SQL_FULLTEXT
--------------------------------------------------------------------------------
update ASN_DETAIL set RECEIVED_QTY=NVL(RECEIVED_QTY,:"SYS_B_0") + :1 , SHIPPED_
QTY=SHIPPED_QTY+ :2 , RECEIVED_LPN_COUNT=NVL(RECEIVED_LPN_COUNT,:"SYS_B_1")+:3
, UNITS_ASSIGNED_TO_LPN=NVL(UNITS_ASSIGNED_TO_LPN,:"SYS_B_2")+ :4 , TC_PURCHAS
E_ORDERS_ID=:5 , TC_PO_LINE_ID=:6 , NBR_OF_PACK_FOR_CATCH_WT=:7 , STD_PACK_QT
Y=:8 , ASN_DETAIL_STATUS=:9 , LAST_UPDATED_DTTM=GETDATE() , LAST_UPDATED_SOURC
E=:10 , LAST_UPDATED_SOURCE_TYPE=:11 , ACTUAL_WEIGHT_RECEIVED=:12 , ACTUAL_WE
IGHT=:13 , CHECKSUM=:14 where ASN_DETAIL_ID=:15 and ASN_ID=:16
Blocking session:
===============
USERNAME OSUSER SID SERIAL# SQL_HASH_VALUE PROGRAM MACHINE STATUS SQL_ID
--------------- --------------- ---------- ---------- -------------- ------------------------- ----------------------------------- -------- -------------
WMS14PRDMP wmsadmin 2047 681 0 JDBC Thin Client s619117rgvl14.uklond6.savvis.net INACTIVE
we need your confirmation to kill the sessions to clear the locks.
#######################
Process and sessions
#######################
select resource_name, current_utilization, max_utilization,LIMIT_VALUE from v$resource_limit where resource_name in ('processes','sessions');
select count(1) Currnet_proc_count from v$process;
select count(1) Currnet_session_count from v$session;
select username, status, machine,count(*) from v$session group by username, status, machine, program order by 4 desc;
sample mail:
----------
CAUSE:
This incident has been raised as the number of process has crosshed the defined monitoring threshold value.
Database : instlprd@NE1ITCPRHDB39
INVESTIGATION:
Checked and below is the max value set for the database.
NAME TYPE VALUE
------------- ----------- -------
processes integer 500
Current Utilization :
SYS@instlprd AS SYSDBA > select count(1) from v$process;
COUNT(1)
----------
410
Current userwise INACTIVE sessions count :
USERNAME STATUS COUNT(1)
------------------------------ -------- ----------
DBA_RPT INACTIVE 1
DBSNMP INACTIVE 2
ACCOUNT_DB INACTIVE 157
OFFRCAT_DB INACTIVE 87
ACCOUNT_DB_APP INACTIVE 1
RESOURCE_MGMT_DB INACTIVE 90
From above we can see around 330 sessions are INACTIVE.
ACTION TAKEN:
None.
NEXT ACTION:
We need your permission to kill the inactive sessions to make avail more processes to database.
Or we can increase the Processes parameter value to a higher value, which needs a database bounce.
####################################
High archive generation
####################################
== Archivelog generation on a daily basis ===
set pages 1000
select trunc(COMPLETION_TIME,'DD') Day, thread#, round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives_Generated from v$archived_log
group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;
== Archive log generation on an hourly basis ===
alter session set nls_date_format='dd-MON-yy hh24:mi:ss';
set pages 1000
select trunc(COMPLETION_TIME,'HH') Hour,thread# , round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives from v$archived_log
group by trunc(COMPLETION_TIME,'HH'),thread# order by 1;
####################
Archive destination is full:
####################
we have moved some archives to other location
RMAN> catalog start with '/db1/vol03/oradata/archbkp/'; ======><archicves move location )
#########manully run the archuive backup script#############
run {
crosscheck archivelog all;
ALLOCATE CHANNEL ch00 TYPE DISK;
ALLOCATE CHANNEL ch01 TYPE DISK;
BACKUP AS COMPRESSED BACKUPSET
filesperset 20
FORMAT '/db1/vol05/orabackup/RMAN/dispordprd1/dispordprd1_fl_al_%s_%p_%t' ==>>backup pices location
archivelog all delete input;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
}
Sample mail:
==========
This case has been created due to high archive generation trap in the "SCPPDEV" database.
This is just information message only, its generated almost 186 archive/transaction logs on 24th Jan 2018 in the DB as below
Archive generation daily basis:
DAY THREAD# MB ARCHIVES_GENERATED
----------- ---------- ------- ------------------
22-JAN-18 1 46313 262
23-JAN-18 1 74362 418
24-JAN-18 1 32817 186
Archive generation hourly basis:
HOUR THREAD# MB ARCHIVES
------------------- ---------- -------- ----------
24-JAN-18 07:00:00 1 13197 73
24-JAN-18 08:00:00 1 19286 108
24-JAN-18 14:00:00 1 141 1
Currently archive generation back to normal state and related trap was cleared in the server as well.
I have set this incident to 'Resolved' status, but would like your confirmation before closing it.
####################
Open Cursors
####################
Total Open Cursors By Session:or bu username
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current';
Total Open Cursors By username & HostMachine:
select sum(a.value) total_cur, avg(a.value) avg_cur,
max(a.value) max_cur, s.username, s.machine
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current'
group by s.username, s.machine order by 1 desc;
MAX allowed Cursors and total open Cursors:
select max(a.value) as highest_open_cur, p.value as max_open_cur
from v$sesstat a, v$statname b, v$parameter p
where a.statistic# = b.statistic# and b.name = 'opened cursors current'
and p.name= 'open_cursors'
group by p.value;
Identify the session by its Oracle username to retrieve the sid value. Connect to the Oracle instance as the SYS or SYSTEM or a user with DBA privileges using SQL*Plus. Execute the following SQL statement to obtain the session's sid value:
Code:
SQL> SELECT sid FROM v$session WHERE username = 'TOM';
SID
----------
135
The sid value is used in the next step to identify the session to investigate.
List the session's SQL statement addresses, which have more than one active reference. Using the sid value from the previous statement, execute a query against the v$open_cursor view.
Code:
SQL> SELECT COUNT(*), address FROM v$open_cursor WHERE sid = 135 GROUP BY address HAVING COUNT(address) > 1 ORDER BY COUNT(*);
COUNT(*) ADDRESS
---------- --------
2 35E6083C
The result lists each cursor, which has been opened by the session more than once in descending order.
Obtain the actual SQL statement for each address returned in step 2. This can be accomplished by querying the v$sql view using the address value.
Code:
SQL> SELECT sql_fulltext FROM v$sql WHERE address = '35F97908';
Sample mail:
===========
Problem Description:
we have received the case due to OPEN_CURSORS parameter reached to the 100% in the 'pimprod' database.
Trap>>
ORACLE_OPEN_CURSORS:pimprod = 76
Steps taken to resolve (Technical Summary):
we have verified the database, we could see below sessions causing high cursors utilization.
HIGHEST_OPEN_CUR MAX_OPEN_CUR
---------------- -------------------------
300 300
TOTAL_CUR AVG_CUR MAX_CUR USERNAME MACHINE
--------- ---------- ---------- ------------- --------------------------------
301 150.5 300 EOS_MDW NE1ITCPRHEWS03.ne1.savvis.net
COUNT(*) ADDRESS
-------- ----------------
299 00000000B3476AC8
SYS@pimprod AS SYSDBA > SELECT sql_fulltext FROM v$sql WHERE address = '00000000B3476AC8';
SQL_FULLTEXT
--------------------------------------------------------------------------------
select EVENT_NAME,CREATE_DT,AUXDATA,REFERENCE,COMMENTS from EVENT_INSTANCE where
STATUS_CD = 7 and CREATE_DT < :1 and CONSUME_DT is null and ROWNUM <= 1000 ord
er by CREATE_DT
>>There are two ways to overcome the issue.
1)Kill aforementioned SID which is holding maximum cursors.
2)Increase the open_cursors value to 500 from 300 via a CRQ
Next action:
-----------
Please let us know how do you want us to proceed further.
I guess I am the only one who comes here to share my very own experience guess what? I am using my laptop for almost the post 2 years.
ReplyDeleteTotalSpaces Crack
Ample Guitar VST Crack
SmartDraw Crack
ApowerManager Crack