DBA to DA

Monday, January 16, 2017

Oracle Database - What all to test in a quick health check for Oracle DB

scripts Anonymous January 16, 2017

One of the repetitive things I have seen and been asked for as DBA is to do a quick health check for the database.

Well, it has can comprise of a lot of things, but I have over time thought over few things which could help me to do a quick validation of the database.

Here is my list (and followed by the SQL statements as well)

Invalid Objects Currently in database
Blocking Session in Database
Details of Blocking SIDs
Sessions > 20MB PGA
Current Wait Events in the Database
Detailed Sess Waits
Wait Events in Last 7 Minutes - Database
Wait Events in Last 15 Minutes - Database
Wait Events in Last 60 Minutes - Databas
Current IO Functions Statistics
Load Average For Server
CPU and Memory
Tablespace Utilizaiton
Temp Space Utilization Stats
Current Locked Objects in Database

## SQL - HealthCheck###
set echo off
set heading off
set lines 500 pages 500

prompt Health Check Report for Database
select chr(9)||chr(9)||chr(9)|| name from V$database;
prompt Instance Name
select INSTANCE_NAME, to_char(STARTUP_TIME,'dd-MON-yyyy hh24:mi') STARTUP_TIME from V$instance;

set heading on
col OWNER for a15
col OBJECT_NAME for a30
break on owner on object_name
prompt ############################################################################
prompt Invalid Objects Currently in database
prompt ############################################################################

select owner, object_name, object_type from dba_objects where status <> 'VALID'
order by 1,2,3;

clear breaks

prompt ############################################################################
prompt Blocking Session in Database
prompt ############################################################################

select sid, serial#, username, status, event, BLOCKING_INSTANCE, BLOCKING_SESSION, BLOCKING_SESSION_STATUS, FINAL_BLOCKING_INSTANCE, FINAL_BLOCKING_SESSION_STATUS, FINAL_BLOCKING_SESSION
from v$session
where blocking_session is not null;

prompt ############################################################################
prompt Details of Blocking SIDs
prompt ############################################################################

select sid, serial#, username, status, event from V$session
where sid in (select distinct BLOCKING_SESSION from v$session
where blocking_session is not null);

prompt ############################################################################
prompt Sessions > 20MB PGA
prompt ############################################################################

column PGA_ALLOC_MEM format 99,990
column PGA_USED_MEM format 99,990
column inst_id format 99
column username format a15
column program format a40
column logon_time format a20

select s.inst_id, s.sid, s.username, s.logon_time, s.program, PGA_USED_MEM/1024/1024 PGA_USED_MEM, PGA_ALLOC_MEM/1024/1024 PGA_ALLOC_MEM
from gv$session s
, gv$process p
Where s.paddr = p.addr
and s.inst_id = p.inst_id
and PGA_USED_MEM/1024/1024 > 20 -- pga_used memory over 20mb
order by PGA_USED_MEM;

prompt ############################################################################
prompt Current Wait Events in the Database
prompt ############################################################################

col wait_class for a40
col event for a60
select wait_class, event, count(*) from v$session
where username is not null
group by wait_class,event
order by 3,1,2;

prompt ############################################################################
prompt Detailed Sess Waits - (sesswaits.sql)
prompt ############################################################################

col seconds_in_wait heading "Wait|(Sec.)" format 9,999,999
select event,
       sid,BLOCKING_SESSION,sql_id,
       p1,
--       p1text,
       p2,
--       p2text,
SECONDS_IN_WAIT
from v$session
where event not in ('SQL*Net message from client',
                'SQL*Net message to client',
                'pipe get',
                'pmon timer',
                'rdbms ipc message',
                'Streams AQ: waiting for messages in the queue',
                'Streams AQ: qmn coordinator idle wait',
                'Streams AQ: waiting for time management or cleanup tasks',
                'PL/SQL lock timer',
                'Streams AQ: qmn slave idle wait',
                'jobq slave wait',
                'queue messages',
                'io done',
                'i/o slave wait',
                'sbtwrite2',
                'async disk IO',
                'smon timer')
order by event, p1,p2 ;

prompt ############################################################################
prompt Wait Events in Last 7 Minutes - Database
prompt ############################################################################

select wait_class, event, count(*) from v$active_session_history
where sample_time > sysdate - 1/192
group by wait_class,event
order by 3,1,2;

prompt ############################################################################
prompt Wait Events in Last 15 Minutes - Database
prompt ############################################################################

select wait_class, event, count(*) from v$active_session_history
where sample_time > sysdate - 1/96
group by wait_class,event
order by 3,1,2;

prompt ############################################################################
prompt Wait Events in Last 60 Minutes - Database
prompt ############################################################################

select wait_class, event, count(*) from v$active_session_history
where sample_time > sysdate - 1/24
group by wait_class,event
order by 3,1,2;

prompt ############################################################################
prompt Current IO Functions Statistics
prompt ############################################################################

col function_name    format a25         heading "File Type"
col reads            format 99,999,999 heading "Reads"
col writes           format 99,999,999 heading "Writes"
col number_of_waits format 99,999,999 heading "Waits"
col wait_time_sec    format 999,999,999 heading "Wait Time|Sec"
col avg_wait_ms      format 999.99      heading "Avg|Wait ms"

set lines 80
set pages 10000

select
   function_name,
   small_read_reqs + large_read_reqs reads,
   small_write_reqs + large_write_reqs writes,
   wait_time/1000 wait_time_sec,
   case when number_of_waits > 0 then
          round(wait_time / number_of_waits, 2)
       end avg_wait_ms
from
   v$iostat_function
order by
    wait_time desc;

set heading off
prompt ############################################################################
prompt Load Average For Server
prompt ############################################################################
select 'Load Average - ' ||   value || ' NUM_CPUS - ' || (select   value   from v$osstat where stat_name = 'NUM_CPUS') || ' LA/pCPU - ' || value/(select   value   from v$osstat where stat_name = 'NUM_CPUS')
   from v$osstat
where stat_name = 'LOAD';

set pagesize 60
column "Tablespace" heading "Tablespace Name" format a30
column "Size" heading "Tablespace|Size (mb)" format 9999999.9
column "Used" heading "Used|Space (mb)" format 9999999.9
column "Left" heading "Available|Space (mb)" format 9999999.9
column "PCTFree" heading "% Free" format 999.99

ttitle left "Tablespace Space Allocations"
break on report
-- compute sum of "Size", "Left", "Used" on report
select /*+ RULE */
t.tablespace_name,
NVL(round(((sum(u.blocks)*p.value)/1024/1024),2),0) Used_mb,
t.Tot_MB,
NVL(round(sum(u.blocks)*p.value/1024/1024/t.Tot_MB*100,2),0) "USED %"
from v$sort_usage u,
v$parameter p,
(select tablespace_name,sum(bytes)/1024/1024 Tot_MB
from dba_temp_files
group by tablespace_name
) t
where p.name = 'db_block_size'
and u.tablespace (+) = t.tablespace_name
group by
t.tablespace_name,p.value,t.Tot_MB
order by 1,2;

prompt ############################################################################
PROMPT ======================= Total TEMP_TS consuming =======================
prompt ############################################################################
select tablespace, sum(blocks)*8192/1024/1024 consuming_TEMP_MB from
v$session, v$sort_usage where tablespace in (select tablespace_name from
dba_tablespaces where contents = 'TEMPORARY') and session_addr=saddr
group by tablespace;

prompt ############################################################################
PROMPT ======================= Sessions consuming TEMP_TS more than 10 MB =======================
prompt ############################################################################
select sid, tablespace,
sum(blocks)*8192/1024/1024 consuming_TEMP_MB from v$session,
v$sort_usage where tablespace in (select tablespace_name from
dba_tablespaces where contents = 'TEMPORARY') and session_addr=saddr
group by sid, tablespace having sum(blocks)*8192/1024/1024 > 10
order by sum(blocks)*8192/1024/1024 desc ;

prompt ############################################################################
PROMPT ======================= Current Locked Objects =======================
prompt ############################################################################

col owner for a25
col object_name for a35
col oracle_username for a25
col os_user_name for a25

SELECT B.Owner, B.Object_Name,b.object_type, A.Oracle_Username, A.OS_User_Name, A.SESSION_ID, A.LOCKED_MODE
FROM V$Locked_Object A, All_Objects B
WHERE A.Object_ID = B.Object_ID

Friday, January 6, 2017

Oracle Database - How to view Current Transaction Status (ROLLBACK or ONGOING)

scripts Anonymous January 06, 2017

One of the key Questions in Oracle Database is how to find out the current Transaction Status in Oracle i.e it is in rollback / Ongoing ?

Well if we look at it oracle provides view V$transaction which gives us a flag column which helps us (PSSS.. Undocumented but figured out with some testing)

Here is the clue
V$transaction.FLAG = 7683 - ONGOING
V$transaction.FLAG = 7811 - ROLLBACK.

Below SQL is what can help you.
I have also taken liberty to add couple of columns and another view v$sqlcommand

col username for a15
col tr_status for a15
col COMMAND_NAME for a20

select ss.sid, ss.serial#, ss.username, st.used_ublk, st.used_urec, ss.status, decode(st.flag,7683,'ONGOING',7811,'ROLLBACK', st.flag) tr_status, sqt.command_name, ss.sql_id, ss.prev_sql_id
from v$session ss , v$transaction st, V$sqlcommand sqt
where ss.saddr = st.ses_addr
and sqt.command_type = ss.command
order by 3;

The additional columns are to give you some further picture, for example if the status of session is INACTIVE and transaction is ONGOING that means, the session did some dmls but did not commit yet but sitting idle.

Below is a test I did on my dummy database to check the values.
All I did was to insert multiple times into my dummy table.

I kept on doing inserts- to my dummy table and let me show you the output.
(The output might be a bit off bit due to font limitation, but the attached picture will give you the idea what I am talking about )

When you see the rollback happening, it is actually when I did the rollback and no rows selected comes when the rollback is complete.

SPECIAL CASE

A special case for this is that if you have killed a session, in this case you will not able to see any data using above query.

You can see these details use V$FAST_START_TRANSACTIONS

Thursday, October 27, 2016

Oracle OEM 13c OMS and Oracle DB Installation

Anonymous October 27, 2016

In this blog we are going to discuss OEM 13c Installation

Installing RDMS and EMPRD (EM database):

1) Start of installation:

2) Selected option to create and configure database :

3) Choose the option to server class:

4) Choose Single instance database:

5) Choose Advanced installation:

6) Language is auto selected to English:

7) To create Database for OEM, its mandatory to be Enterprise Edition:

8) Create owner which doesn’t have Admin Privileges: (Can’t proceed further if the owner had admin privileges)

Username: xxxxx

Password: xxxxx

9) Select locations for RDBMS installation:

10) Choose option for General purpose:

11) Name the database to EMPRD:

12) Minimum required memory allocation is 6GB:

13) Choose Character set :

14) Choose Datafiles location for EMPRD database:

15) Choose none as OEM is not yet installed:

16) EMPRD database is not mandatory to be in archivelog:

17) SYS, SYSTEM and DBSNMP passwords are kept default with “xxxxxx”

18) Check all the configurations given:

19) Progress of RDBMS installation:

20) Progress of EMPRD database:

21) Installation of RDBMS is complete:

22) Creation of EMPRD is complete:

23) Make sure EMPRD had minimum configuration to proceed with OEM installation:

ALTER SYSTEM SET processes=300 SCOPE=SPFILE;

ALTER SYSTEM SET session_cached_cursors=200 SCOPE=SPFILE;

ALTER SYSTEM SET sga_target=2G SCOPE=SPFILE;

ALTER SYSTEM SET shared_pool_size=600M SCOPE=SPFILE;

ALTER SYSTEM SET pga_aggregate_target=1G SCOPE=SPFILE;

ALTER SYSTEM SET job_queue_processes=20 SCOPE=SPFILE;

ALTER SYSTEM SET log_buffer=10485760 SCOPE=SPFILE;

ALTER SYSTEM SET open_cursors=300 SCOPE=SPFILE;

13c: New requirement:

alter system set optimizer_adaptive_features=FALSE scope=both;

Restart the instance.

Those parameters are min. values which mean you can increase those values via your needs.

Oracle recommended to use UNDO tablespace is at least 200M in size. Also, make sure you have 3 redo logs of at least 300M to prevent a warning during the installation.

Installing OMS:

1) Start of OEM installation: