List running Weblogic servers and Node Managers

#!/bin/bash
# ---------------------------------------------------------------------------
# listWebLogicServers.sh - List running WebLogic Servers and Node Managers
# Peter Lorenzen
# Version 0.9
# ---------------------------------------------------------------------------

DOMAIN_DIRECTORY="domains"

echo "################################################################################"
echo "# WebLogic Servers"
echo "# <owner>  <domain>  <server>  <pid>  <ports>"
echo "################################################################################"
ps -ef | grep "[D]weblogic.Name="|while read tmp
do
  owner=`echo $tmp | awk '{ print $1 }'`
  webLogicServer=`echo $tmp | grep -oP "(?<=Dweblogic.Name=)[^ ]+"`
  pid=`echo $tmp | awk '{ print $2 }'`
  port=`echo $tmp | netstat -tlpn 2>/dev/null | grep $pid | awk '{ print $4 }' | tr '\n' ',' | tr ' ' ',' | grep -o ":....," | sort -u | tr -d '\n' | tr -d ':' | sed 's/,$//'`
  if [ -z "$port" ]; then
    port="null"
  fi
  domain=`echo $tmp | grep -oP "(?<=BootIdentityFile=)[^ ]+"`
  domain=`echo $domain | grep -oP "(?<=$DOMAIN_DIRECTORY/)[^ ]+" | cut -d/ -f1`
  if [ -n "$1" ]; then
    echo "$owner $domain $webLogicServer $pid $port kill -9 $pid"
  else 
    echo "$owner $domain $webLogicServer $pid $port"
  fi
done|sort|column -t

echo
echo "################################################################################"
echo "# Node Managers"
echo "# <owner>  <mw_home>  <pid>  <port>"
echo "################################################################################"
ps -ef | grep "[w]eblogic.NodeManager"|while read tmp
do
  owner=`echo $tmp | awk '{ print $1 }'`
  pid=`echo $tmp | awk '{ print $2 }'`
  port=`echo $tmp | netstat -tlpn 2>/dev/null | grep $pid | awk '{ print $4 }' | tr '\n' ',' | tr ' ' ',' | grep -o ":....," | sort -u | tr -d '\n' | tr -d ':' | sed 's/,$//'`
  if [ -z "$port" ]; then
    port="null"
  fi
  mw_home=`echo $tmp | grep -oP "(?<=bea.home=)[^ ]+"`
  if [ -n "$1" ]; then
    echo "$owner $mw_home $pid $port kill -9 $pid"
  else 
    echo "$owner $mw_home $pid $port"
  fi
done|sort|column -t

OUTPUT:

################################################################################

# WebLogic Servers

# <owner>  <domain>  <server>  <pid>  <ports>

################################################################################

oracle  AdminServer  3383       7013

oracle  GCDomain     EMGC_OMS1  15149      7301

root    AdminServer  30664      7007

root    AdminServer  58600      7004

root    AdminServer  59677      5060,7001

root    AdminServer  59902      7012

################################################################################

# Node Managers

# <owner>  <mw_home>  <pid>  <port>

################################################################################

oracle  14819  7403

How to find Sessions generating high redo/archives in Oracle

Sometimes, database generates lot more archives than the normal. In this article I will show you how to find them. Below are the queries to drill down to the root cause and fix the issue.

SELECT * FROM ( SELECT * FROM ( SELECT TO_CHAR(FIRST_TIME, 'DD/MM') AS "DAY" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0)), '999') "00:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0)), '999') "01:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0)), '999') "02:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0)), '999') "03:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0)), '999') "04:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0)), '999') "05:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0)), '999') "06:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0)), '999') "07:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0)), '999') "08:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0)), '999') "09:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0)), '999') "10:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0)), '999') "11:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0)), '999') "12:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0)), '999') "13:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0)), '999') "14:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0)), '999') "15:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0)), '999') "16:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0)), '999') "17:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0)), '999') "18:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0)), '999') "19:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0)), '999') "20:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0)), '999') "21:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0)), '999') "22:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0)), '999') "23:00" FROM V$LOG_HISTORY WHERE extract(year FROM FIRST_TIME) = extract(year FROM sysdate) GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM') ) ORDER BY TO_DATE(extract(year FROM sysdate) || DAY, 'YYYY DD/MM') DESC ) WHERE ROWNUM < 8;

If there is a drastic change in redo/archive generation run below queries.. Get the segment that experienced the most changes during a specific period. This is helpful in tracking history data.

Note:- Change the date ranges according to the time frame.

SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24:MI') snap_time,        dhsso.object_name,        sum(db_block_changes_delta)  FROM dba_hist_seg_stat dhss,         dba_hist_seg_stat_obj dhsso,         dba_hist_snapshot dhs  WHERE dhs.snap_id = dhss.snap_id    AND dhs.instance_number = dhss.instance_number    AND dhss.obj# = dhsso.obj#    AND dhss.dataobj# = dhsso.dataobj#    AND begin_interval_time BETWEEN to_date('2016_12_12 01','YYYY_MM_DD HH24')                                           AND to_date('2016_12_12 10','YYYY_MM_DD HH24')  GROUP BY to_char(begin_interval_time,'YYYY_MM_DD HH24:MI'),           dhsso.object_name order by 3 desc;

In this case we had our Audit job creating most of the archives.

To find sessions generating lots of redo, you can use either of the following methods. Both methods examine the amount of undo generated. When a transaction generates undo, it will automatically generate redo as well.

The methods are:

1) Query V$SESS_IO. This view contains the column BLOCK_CHANGES which indicates how much blocks have been changed by the session. High values indicate a session generating lots of redo.

The query you can use is:

SELECT s.sid, s.serial#, s.username, s.program,           i.block_changes           FROM v$session s, v$sess_io i           WHERE s.sid = i.sid           ORDER BY 5 desc, 1, 2, 3, 4;

Run the query multiple times and examine the delta between each occurrence of BLOCK_CHANGES. Large deltas indicate high redo generation by the session.

2) Query V$TRANSACTION. This view contains information about the amount of undo blocks and undo records accessed by the transaction (as found in the USED_UBLK and USED_UREC columns).

The query you can use is:

SELECT s.sid, s.serial#, s.username, s.program,           i.block_changes           FROM v$session s, v$sess_io i           WHERE s.sid = i.sid           ORDER BY 5 desc, 1, 2, 3, 4;

Run the query multiple times and examine the delta between each occurrence of USED_UBLK and USED_UREC. Large deltas indicate high redo generation by the session.

You use the first query when you need to check for programs generating lots of redo when these programs activate more than one transaction. The latter query can be used to find out which particular transactions are generating redo.

Incremental Updated Backup or Rolling Forward Backup

#!/bin/bash
# Declare your environment variables
export ORACLE_SID=xxxxx
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin
log_date=$(date +%Y%m%d)
rman target=/ << EOF
spool log to /u03/fast_recovery_area/xxxxx/RMAN/backup_logs_$ORACLE_SID_${log_date}.log
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u03/fast_recovery_area/xxxxx/RMAN/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC';
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
# List of Database Incarnations
list incarnation;

# Incrementally updated backup with a retention policy of 7 days
# The backup is set to check for logical corruption   (BACKUP CHECK LOGICAL)
run {
RECOVER COPY OF DATABASE with TAG 'xxxxx_LVL0_MERGE_INCR' UNTIL TIME 'SYSDATE-8';
ALLOCATE CHANNEL RMAN_BACK_CH01 TYPE DISK FORMAT '/u03/fast_recovery_area/xxxxx/RMAN/bkp_%d_set%s_%T_%U';
BACKUP CHECK LOGICAL INCREMENTAL LEVEL 1 FOR RECOVER OF COPY with TAG 'xxxxx_LVL0_MERGE_INCR' DATABASE plus archivelogs;
}


run {
CROSSCHECK BACKUP;
CROSSCHECK COPY;
CROSSCHECK ARCHIVELOG ALL;
REPORT OBSOLETE;
DELETE NOPROMPT OBSOLETE;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT EXPIRED COPY;
}
# Validate Database Backup
restore validate database;

EXIT;
exit;
EOF

rman catalog rman/xxxxx@RMAN target=sys/xxxxxxxx@xxxxx_MH << EOF
resync catalog;
exit
EOF

TEMP TABLESPACE scripts

To check instance-wise total allocated, total used TEMP for both rac and non-rac

set lines 152

col FreeSpaceGB format 999.999

col UsedSpaceGB format 999.999

col TotalSpaceGB format 999.999

col host_name format a30

col tablespace_name format a30

select tablespace_name,

(free_blocks*8)/1024/1024 FreeSpaceGB,

(used_blocks*8)/1024/1024 UsedSpaceGB,

(total_blocks*8)/1024/1024 TotalSpaceGB,

i.instance_name,i.host_name

from gv$sort_segment ss,gv$instance i where ss.tablespace_name in (select tablespace_name from dba_tablespaces where contents='TEMPORARY') and

i.inst_id=ss.inst_id;

Total Used and Total Free Blocks

select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks  from gv$sort_segment;

Another Query to check TEMP USAGE

col name for a20

SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management

"ExtManag",

TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", TO_CHAR(NVL(t.bytes,

0)/1024/1024,'99999,999.999') ||'/'||TO_CHAR(NVL(a.bytes/1024/1024, 0),'99999,999.999') "Used (M)",

TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"

FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_temp_files group by

tablespace_name) a,

(select tablespace_name, sum(bytes_cached) bytes from

v$temp_extent_pool group by tablespace_name) t

WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+)

AND d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY';

Temporary Tablespace groups

SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

select tablespace_name,contents from dba_tablespaces where tablespace_name like '%TEMP%';

select * from dba_tablespace_groups;

Block wise Check

select TABLESPACE_NAME, TOTAL_BLOCKS, USED_BLOCKS, MAX_USED_BLOCKS, MAX_SORT_BLOCKS, FREE_BLOCKS from V$SORT_SEGMENT;

select sum(free_blocks) from gv$sort_segment where tablespace_name = 'TEMP';

To Check Percentage Usage of Temp Tablespace

select (s.tot_used_blocks/f.total_blocks)*100 as "percent used"

from (select sum(used_blocks) tot_used_blocks

from v$sort_segment where tablespace_name='TEMP') s,

(select sum(blocks) total_blocks

from dba_temp_files where tablespace_name='TEMP') f;

To check Used Extents ,Free Extents available in Temp Tablespace

SELECT tablespace_name, extent_size, total_extents, used_extents,free_extents, max_used_size FROM v$sort_segment;

To list all tempfiles of Temp Tablespace

col file_name for a45

select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible from dba_temp_files  order by file_name;

SELECT d.tablespace_name tablespace , d.file_name filename, d.file_id fl_id, d.bytes/1024/1024

size_m

, NVL(t.bytes_cached/1024/1024, 0) used_m, TRUNC((t.bytes_cached / d.bytes) * 100) pct_used

FROM

sys.dba_temp_files d, v$temp_extent_pool t, v$tempfile v

WHERE (t.file_id (+)= d.file_id)

AND (d.file_id = v.file#);

Additional checks

select distinct(temporary_tablespace) from dba_users;

select username,default_tablespace,temporary_tablespace from dba_users order by temporary_tablespace;

SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

Changing the default temporary Tablespace

SQL> alter database default temporary tablespace TEMP;

Database altered.

To add tempfile to Temp Tablespace

alter tablespace  temp  add tempfile '&tempfilepath' size 1800M;

alter tablespace temp add tempfile '/m001/oradata/SID/temp02.dbf' size 1000m;

alter tablespace TEMP add tempfile '/SID/oradata/data02/temp04.dbf' size 1800M autoextend on maxsize 1800M;

To resize the  tempfile in Temp Tablespace

alter database tempfile '/u02/oradata/TESTDB/temp01.dbf' resize 250M

alter database tempfile '/SID/oradata/data02/temp12.dbf' autoextend on maxsize 1800M;

alter tablespace TEMP add tempfile '/SID/oradata/data02/temp05.dbf' size 1800m reuse;

To find Sort Segment Usage by Users

select username,sum(extents) "Extents",sum(blocks) "Block"

from v$sort_usage

group by username;

To find Sort Segment Usage by a particular User

SELECT s.username,s.sid,s.serial#,u.tablespace, u.contents, u.extents, u.blocks

FROM v$session s, v$sort_usage u

WHERE s.saddr=u.session_addr

order by u.blocks desc;

To find Total Free space in Temp Tablespace

select 'FreeSpace  ' || (free_blocks*8)/1024/1024 ||' GB'  from v$sort_segment where tablespace_name='TEMP';

select tablespace_name , (free_blocks*8)/1024/1024  FreeSpaceInGB,

(used_blocks*8)/1024/1024  UsedSpaceInGB,

(total_blocks*8)/1024/1024  TotalSpaceInGB

from v$sort_segment where tablespace_name like '%TEMP%'

To find  Total Space Allocated for Temp Tablespace

select 'TotalSpace ' || (sum(blocks)*8)/1024/1024 ||' GB'  from dba_temp_files where tablespace_name='TEMP';

Get 10 sessions with largest temp usage

cursor bigtemp_sids is

select * from (

select s.sid,

s.status,

s.sql_hash_value sesshash,

u.SQLHASH sorthash,

s.username,

u.tablespace,

sum(u.blocks*p.value/1024/1024) mbused ,

sum(u.extents) noexts,

nvl(s.module,s.program) proginfo,

floor(last_call_et/3600)||':'||

floor(mod(last_call_et,3600)/60)||':'||

mod(mod(last_call_et,3600),60) lastcallet

from v$sort_usage u,

v$session s,

v$parameter p

where u.session_addr = s.saddr

and p.name = 'db_block_size'

group by s.sid,s.status,s.sql_hash_value,u.sqlhash,s.username,u.tablespace,

nvl(s.module,s.program),

floor(last_call_et/3600)||':'||

floor(mod(last_call_et,3600)/60)||':'||

mod(mod(last_call_et,3600),60)

order by 7 desc,3)

where rownum < 11;

Displays the amount of IO for each tempfile

SELECT SUBSTR(t.name,1,50) AS file_name,

f.phyblkrd AS blocks_read,

f.phyblkwrt AS blocks_written,

f.phyblkrd + f.phyblkwrt AS total_io

FROM   v$tempstat f,v$tempfile t

WHERE  t.file# = f.file#

ORDER BY f.phyblkrd + f.phyblkwrt DESC;

select * from (SELECT u.tablespace, s.username, s.sid, s.serial#, s.logon_time, program, u.extents, ((u.blocks*8)/1024) as MB,

i.inst_id,i.host_name

FROM gv$session s, gv$sort_usage u ,gv$instance i

WHERE s.saddr=u.session_addr and u.inst_id=i.inst_id  order by MB DESC) a where rownum<10;

Check for ORA-1652

show parameter background

cd <background dump destination>

ls -ltr|tail

view <alert log file name>

shift + G ---> to get the tail end...

?ORA-1652 ---- to search of the error...

shift + N ---- to step for next reported error...

I used these queries to check some settings:

-- List all database files and their tablespaces:

select  file_name, tablespace_name, status

,bytes   /1000000  as MB

,maxbytes/1000000  as MB_max

from dba_data_files ;

-- What temporary tablespace is each user using?:

select username, temporary_tablespace, default_tablespace from dba_users ;

-- List all tablespaces and some settings:

select tablespace_name, status, contents, extent_management

from dba_tablespaces ;

TABLESPACE_NAME                CONTENTS  EXTENT_MAN STATUS

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

SYSTEM                         PERMANENT DICTIONARY ONLINE

TOOLS                          PERMANENT DICTIONARY ONLINE

TEMP                           TEMPORARY DICTIONARY OFFLINE

TMP                            TEMPORARY LOCAL      ONLINE

Now, the above query and the storage clause of the old 'create tablespace TEMP' command seem to tell us the tablespace only allows temporary objects, so it should be safe to assume that no one created any tables or other permanent objects in TEMP by mistake, as I think Oracle would prevent that. However, just to be absolutely certain, I decided to double-check. Checking for any tables in the tablespace is very easy:

-- Show number of tables in the TEMP tablespace - SHOULD be 0:

select count(*)  from dba_all_tables

where tablespace_name = 'TEMP' ;

Checking for any other objects (views, indexes, triggers, pl/sql, etc.) is trickier, but this query seems to work correctly - note that you'll probably need to connect internal in order to see the sys_objects view:

-- Shows all objects which exist in the TEMP tablespace - should get

-- NO rows for this:

column owner        format a20

column object_type  format a30

column object_name  format a40

select

o.owner  ,o.object_name

,o.object_type

from sys_objects s

,dba_objects o

,dba_data_files df

where df.file_id = s.header_file

and o.object_id = s.object_id

and df.tablespace_name = 'TEMP' ;

Identifying WHO is currently using TEMP Segments

10g onwards

SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, (b.blocks*d.block_size)/1048576 MB_used, c.sql_text

FROM v$session a, v$tempseg_usage b, v$sqlarea c,

     (select block_size from dba_tablespaces where tablespace_name='TEMP') d

    WHERE b.tablespace = 'TEMP'

    and a.saddr = b.session_addr

    AND c.address= a.sql_address

    AND c.hash_value = a.sql_hash_value

    AND (b.blocks*d.block_size)/1048576 > 1024

    ORDER BY b.tablespace, 6 desc;

Progress Of RMAN Job

Progress Of RMAN Job

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
       ROUND(SOFAR/TOTALWORK*100,2) "% COMPLETE"
       FROM V$SESSION_LONGOPS
       WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%'
       AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;

Flash Recover Area

FINDING ORACLE FLASH RECOVERY AREA USAGE

SELECT NAME,(SPACE_LIMIT / 1024 / 1024 / 1024) SPACE_LIMIT_GB,((SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE) / 1024 / 1024 / 1024) AS SPACE_AVAILABLE_GB,ROUND((SPACE_USED - SPACE_RECLAIMABLE) / SPACE_LIMIT * 100, 1) AS PERCENT_FULL FROM V$RECOVERY_FILE_DEST;

-- FRA Occupants

   SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

-- Location and size of the FRA

show parameter db_recovery_file_dest

-- Size, used, Reclaimable

SELECT

 ROUND((A.SPACE_LIMIT / 1024 / 1024 / 1024), 2) AS FLASH_IN_GB,

 ROUND((A.SPACE_USED / 1024 / 1024 / 1024), 2) AS FLASH_USED_IN_GB,

 ROUND((A.SPACE_RECLAIMABLE / 1024 / 1024 / 1024), 2) AS FLASH_RECLAIMABLE_GB,

 SUM(B.PERCENT_SPACE_USED)  AS PERCENT_OF_SPACE_USED

FROM

 V$RECOVERY_FILE_DEST A,

 V$FLASH_RECOVERY_AREA_USAGE B

GROUP BY

 SPACE_LIMIT,

 SPACE_USED ,

 SPACE_RECLAIMABLE ;

-- After that you can resize the FRA with:

-- ALTER SYSTEM SET db_recovery_file_dest_size=xxG scope=both;

-- Or change the FRA to a new location (new archives will be created to this new location):

-- ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u....';