OEM 13cR2 Machine Setup

OEM 13cR2 Machine Setup

OEM Vineet Sachdeva   January 31, 2018

Environment

• Kernel -  3.10.0-514.el7.x86_64
• Release -
  NAME="Red Hat Enterprise Linux Server"
  VERSION="7.3 (Maipo)"

 Software Pre-reqs

Required Packages (minimum required Versions)
  

1. make-3.82-21
2. binutils-2.23
3. gcc-4.8.2-16
4. libaio-0.3.109-12
5. glibc-common-2.17-55
6. libstdc++-4.8.2-16
7. sysstat-10.1.5-4
8. glibc-devel 2.17-55 (i686) (This is a 32-bit package)
9. glibc-devel 2.17-55 (x86_64) (This is a 64-bit package)
10. libXtst-1.2.2-2 (x86_64)
11. glibc-2.17

Sysctl Configuration


vi /etc/sysctl.d/99-oem-sysctl.conf

fs.aio-max-nr = 1048576

fs.file-max = 6815744

kernel.shmall = 2097152
kernel.shmmax = 4294967295
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586
net.ipv4.ip_local_port_range = 9000 65500

 Apply Changes

# /sbin/sysctl --system

Set Limits for OEM Owner

vi /etc/security/limits.conf (Append)

oracle    soft    nproc   2047

oracle    hard    nproc   16384

oracle    soft    nofile  1024

oracle    hard    nofile  65536

oracle    soft    memlock 1048576

oracle    hard    memlock 1048576
 

***Make sure File permissions for /usr/bin/make and /usr/bin/ld.bfd is 0755 

User Addition 

groupadd -g 1001 dba
useradd -u 1001 -g dba -s /bin/bash -d /home/oracle oracle


Edit Bash Profile for Faster Access

export OMS_HOME=/opt/oracle/em13cr2/middleware
export AGENT_INST=/opt/oracle/agent/agent13cr2/agent_inst
export AGENT_HOME=/opt/oracle/agent/agent13cr2/agent_13.2.0.0.0/



Create Directories and set permissions for install
[root@omshost ~]# mkdir -p "/opt/oracle/em13cr2/middleware"
[root@omshost ~]# mkdir -p "/opt/oracle/agent/agent13cr2"
[root@omshost ~]# mkdir -p "/opt/oracle/em13cr2/gc_inst"
[root@omshost ~]# mkdir -p "/opt/oracle/swlib"
[root@omshost ~]# mkdir -p "/opt/oracle/em13cr2/bip/config"
[root@omshost ~]# mkdir -p "/opt/oracle/em13cr2/bip/cluster"
[root@omshost ~]# chown -R oracle:dba /opt/ ; chmod 777 /opt


Create Inventory Directory
[oracle@ omshost  ~]# mkdir -p /opt/oracle/oraInventory

[root@omshost  ~]# vi /etc/oraInst.loc
    inventory_loc=/opt/oracle/oraInventory
        inst_group=dba   
[root@omshost  ~]# chown oracle:dba /etc/oraInst.loc


Download and Stage Installation Files required to Download

[root@omshost linux]# du -sch em13200p1_linux64*
2.0G    em13200p1_linux64-2.zip
709M    em13200p1_linux64-3.zip
2.0G    em13200p1_linux64-4.zip
105M    em13200p1_linux64-5.zip
2.1G    em13200p1_linux64-6.zip
738M    em13200p1_linux64-7.zip
530M    em13200p1_linux64.bin
8.0G    total
 

OEM 13cR2 Series

OEM 13cR2 Series

OEM Vineet Sachdeva   January 31, 2018

I am going to publish a series of blogs for OEM 13cR2

This includes -

OEM Installation

1. OEM Machine Setup
2. OEM Silent Installation

OEM Patching

1. OMS Patching
2. Agent Patching

Plugins

1. Catalog Updation - Manual
2. Plugin Deployment on OMS - CLI
3. Plugin Deployment on OMS - GUI

Oracle Database - Log Switches Per hour

Oracle Database - Log Switches Per hour

scripts Anonymous   July 28, 2017

This script is used to find out log switches per hour in Oracle Database

ROWNUM < 31  tells to display data for last 30 days


SET PAGESIZE 90
SET LINESIZE 150
set heading on
column "00:00" format 9999
column "01:00" format 9999
column "02:00" format 9999
column "03:00" format 9999
column "04:00" format 9999
column "05:00" format 9999
column "06:00" format 9999
column "07:00" format 9999
column "08:00" format 9999
column "09:00" format 9999
column "10:00" format 9999
column "11:00" format 9999
column "12:00" format 9999
column "13:00" format 9999
column "14:00" format 9999
column "15:00" format 9999
column "16:00" format 9999
column "17:00" format 9999
column "18:00" format 9999
column "19:00" format 9999
column "20:00" format 9999
column "21:00" format 9999
column "22:00" format 9999
column "23:00" format 9999
SELECT * FROM (
SELECT * FROM (
SELECT TO_CHAR(FIRST_TIME, 'DD/MM') AS "DAY"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0), '99')) "00:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0), '99')) "01:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0), '99')) "02:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0), '99')) "03:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0), '99')) "04:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0), '99')) "05:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0), '99')) "06:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0), '99')) "07:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0), '99')) "08:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0), '99')) "09:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0), '99')) "10:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0), '99')) "11:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0), '99')) "12:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0), '99')) "13:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0), '99')) "14:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0), '99')) "15:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0), '99')) "16:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0), '99')) "17:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0), '99')) "18:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0), '99')) "19:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0), '99')) "20:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0), '99')) "21:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0), '99')) "22:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0), '99')) "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 <31;

Oracle Database - Mining Active Session History (ASH) for Plan/SQL Execution Wait Events

Oracle Database - Mining Active Session History (ASH) for Plan/SQL Execution Wait Events

scripts Anonymous   May 29, 2017

In this blog I am going to discuss about mining ASH for wait events for a specific sql session or specific sql or specific plan hash value execution wait events.

This can be really powerful in cases like - 

1. when sql_monitoring report is no longer available,

2. or you want to tune a particular plan and you want to know at what plan_line it is taking more time

3. you want to tune a plan and want to know at what line it is taking time with what kind of wait event.

There are many other use cases but the above are summary of what can be done. 

Now I will quickly jump onto the sql part.

First SQL is to find out the Time taken by SQL on each plan line per the options

set lines 500 pages 500
col object_name for a25

SELECT plan_hash_value,
id line,
operation,
options,
object_name,
sum(10)
FROM dba_hist_active_sess_history a,
dba_hist_sql_plan b
WHERE a.sql_plan_hash_value = b.plan_hash_value
AND a.sql_id = b.sql_id
AND a.sql_plan_line_id = b.id
AND a.sql_id = 'ah9xc371174p7'
AND a.session_id = 446
AND a.session_serial# = 26701
GROUP BY plan_hash_value, id, operation, options, object_name
ORDER by 1,2;

 

There is a sum 10 option because dba_hist_active_sess_history has a sample size of 10 seconds

 Now Lets see to get this output from V$active_session_history, remember the sample size of V$ASH is 1 second.

SELECT plan_hash_value,
id line,
operation,
options,
object_name,
sum(1)
FROM V$active_session_history a,
dba_hist_sql_plan b
WHERE a.sql_plan_hash_value = b.plan_hash_value
AND a.sql_id = b.sql_id
AND a.sql_plan_line_id = b.id
AND a.sql_id = 'ah9xc371174p7'
AND a.session_id = 6769
AND a.session_serial# = 38229
GROUP BY plan_hash_value, id, operation, options, object_name
ORDER by 1,2;

 

 I have truncated the object names for obvious reasons in both. 

The key thing to notice here is that you have the time for each plan_line and the type of operation. 

Now the next thing is to find out within the operation / plan line what was the actual wait event i.e was it really I/O or CPU or direct path read etc. 

 

select ss.sql_id,
ss.sql_plan_line_id,
case SESSION_STATE when 'WAITING' then event
else SESSION_STATE end TIME_CATEGORY,
(SUM(10)) seconds,
round(RATIO_TO_REPORT(count(*)) OVER () * 100 ,2) pct_wait
from dba_hist_active_sess_history ss
WHERE ss.sql_id = 'ah9xc371174p7'
AND ss.session_id = 446
AND ss.session_serial# = 26701
group by ss.sql_id,ss.sql_plan_line_id,session_state,event
order by seconds;

SQL_ID        SQL_PLAN_LINE_ID TIME_CATEGORY                SECONDS   PCT_WAIT

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

ah9xc371174p7                5 db file sequential read           20        .22

ah9xc371174p7                5 db file parallel read             30        .33

ah9xc371174p7                1 ON CPU                            50        .55

ah9xc371174p7                2 ON CPU                            60        .65

ah9xc371174p7                5 ON CPU                            80        .87

ah9xc371174p7                6 ON CPU                          1130      12.32

ah9xc371174p7                6 db file parallel read           2760       30.1

ah9xc371174p7                6 db file sequential read         5040      54.96

 (In case output above is not formatted properly  due to font / browser limitations - see below output) 

Now lets see this output from V$Active Session History. 

select ss.sql_id,
ss.sql_plan_line_id,
case SESSION_STATE when 'WAITING' then event
else SESSION_STATE end TIME_CATEGORY,
(SUM(1)) seconds,
round(RATIO_TO_REPORT(count(*)) OVER () * 100 ,2) pct_wait
from  V$active_session_history ss
WHERE ss.sql_id = 'ah9xc371174p7'
AND ss.session_id = 6769
AND ss.session_serial# = 38229
group by ss.sql_id,ss.sql_plan_line_id,session_state,event
order by seconds;  

 The key thing here is to find on what step the sql took more time and take an informed decision if we want to tune that step / access path or wait_event. 

You can apply filters in V$ASH / DB_HIST_ASH to find details for different plans as a summary without a specific session or set of sessions, totally up to you.