Oracle Database Administration

Some times we'll get the following warning messages in the alert log of the database continuously. Alert log message: ============== WARN: ARC2: Terminating ARCH (pid 10111) hung on a disk operation Mon May 05 22:13:32 2014 krsv_proc_kill: Killing 266287972353 processes (Process by index) Mon May 05 22:20:32 2014 ARC2: Detected ARCH process failure ARC2: STARTING ARCH PROCESSES Mon May 05 22:20:32 2014 ARC1 started with pid=21, OS id=12104 ARC1: Archival started ARC2: STARTING ARCH PROCESSES COMPLETE Mon May 05 22:20:41 2014 Deleted Oracle managed file /data/oracle/flash_recovery_area/ORCL/archivelog/2014_05_05/o1_mf_1_29497_9phnw343_.arc           Do not panic when you see the above message.  As long as there are no other side-effects this error can be ignored.  It occurs when disk IO & CPU load are high in the server.  Normally we are getting this warning messages when ever backup runs since disk io will be very high during...

We'll have a situation to calculate IOPS (Input Output Per Second) of oracle database at times to know the performance bottleneck of an oracle database regarding IO or when planning capacity for new hardware implementation. Though we can find it from AWR report, Below is the sql statement to calculate the IOPS of an Oracle database IOPS for entire day: ============== ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS'; break on report compute sum of value on report select METRIC_NAME,avg(AVERAGE) value from dba_hist_sysmetric_summary where begin_time between to_date('16-FEB-14 00:00:00', 'dd-MON-yy hh24:mi:ss') and to_date('16-FEB-14 23:59:59', 'dd-MON-yy hh24:mi:ss') and end_time like '%16-FEB-14%' and  METRIC_NAME in ('Physical Read Total IO Requests Per Sec','Physical Write Total IO Requests Per Sec') group by METRIC_NAME; You can change the time interval in the above sql query based on your req...

Finding reason or cause for heavy or more archive log generation in a particular time period As i said in the previous post we are going to see SQL queries to find the cause or reason for more archive log generation in a problematic window... Sometimes customer would ask the sql query which generated more archive logs sometimes before or days before (not for current archive logs generation which is described in the previous post). In such scenarios, follow the steps below. Step 1: ====== First you must know the timing when more number of archive logs generated in the databases. for that you can use the below query. Below sql query gives how many number of archive logs generated for each and every hour... col day for a12 set lines 1000 set pages 999 col "00" for a3 col "01" for a3 col "02" for a3 col "03" for a3 col "04" for a3 col "05" for a3 col "06" for a3 col "07" for...

Many times customer would ask us  that why more number of archive logs are being generated & flash recovery area (FRA) is getting filled with archive logs?  What are the queries or sessions involved in generating more archive logs? below we can see the sql query to find which sessions and sql statements are generating more redo or more number of archive logs.... current sessions generating redo ================================= set lines 2000 set pages 1000 col sid for 99999 col name for a09 col username for a14 col PROGRAM for a21 col MODULE for a25 select s.sid,sn.SERIAL#,n.name, round(value/1024/1024,2) redo_mb, sn.username,sn.status,substr (sn.program,1,21) "program", sn.type, sn.module,sn.sql_id from v$sesstat s join v$statname n on n.statistic# = s.statistic# join v$session sn on sn.sid = s.sid where n.name like 'redo size' and s.value!=0 order by redo_mb desc; From the above query we can have the session and sql id which generates redo ...

Here we'll see how to find heavy or more cpu consumed sql query in oracle We are facing high CPU load on Linux servers daily at times. Whenever CPU load is high, we'll get the TOP output and if the load is due to oracle database, we'll track currently running sql queries which is using more CPU on database & update the customer when they ask RCA report of high CPU load on servers. The below query is to find the sql queries which is causing CPU load on server & using more CPU resources currently. sessions based on cpu usage : ----------------------------------------- set pages 1000 set lines 1000 col OSPID for a06 col SID for 99999 col SERIAL# for 999999 col SQL_ID for a14 col USERNAME for a15 col PROGRAM for a23 col MODULE for a18 col OSUSER for a10 col MACHINE for a25 select * from ( select p.spid "ospid", (se.SID),ss.serial#,ss.SQL_ID,ss.username,substr(ss.program,1,22) "program",ss.module,ss.osuser,ss.MACHINE,ss.status, ...