Search This Blog

Total Pageviews

Saturday 21 May 2011

Oracle 11g process

ora_pmon_ : The Process Monitor is responsible for monitoring processes. In this case pmon will perform recovery for user processes that fail, will rollback uncommitted transactions, and clean up the buffer cache and free the resources being consumed.

ora_smon_ : The System Monitor process is responsible for instance recovery –cleaning up temporary segments that are not required and coalescing contiguous free extents in dictionary managed tablespaces.

ora_dbw0_ : The Database Writer process(s) handle the writing of dirty/modified buffers in the database buffer cache to datafiles.Depending on the activity of the database, there may be multiple database writers to keep up with the changes occurring.


ora_lgwr_ : The Log Writer process is responsible for writing data from the redo log buffers to the redo log files on disk.

ora_ckpt_ : The Checkpoint Process is responsible for signaling the synchronization of all database files with checkpoint information ensuring data consistency and faster database recovery in case of a crash.

ora_vktm_ : The Virtual Keeper of Time process is responsible for keeping track of the wall-clock time and used as a reference-time counter.

ora_diag_ : The Diagnosability Process handles hang detection and deadlock resolution.

ora_dbrm_ : The Database Resource Manager process takes care of setting up resource manager related tasks and resource plans.

ora_psp0_ : The Process Spawner will spawn Oracle processes as needed.


ora_vktm_ : The Virtual Keeper of Time process is responsible for keeping track of the wall-clock time and used as a reference-time counter.

ora_dbrm_ : The Database Resource Manager process takes care of setting up resource manager related tasks and resource plans.


ora_mman_ : The mman process is used for internal database tasks.


ora_reco_ : The Recoverer process is responsible for recovering all in-doubt transactions in a distributed database setup – connecting to remote databases and resolving pending transactions.

ora_mmon_ : The mmon process will perform manageability-related background tasks such as issuing alerts, taking snapshots, and capturing statistics values for SQL objects.

ora_mmnl_ : The mmnl process will perform other but light-weight manageability-related tasks such as capturing session history and metrics computation.

ora_d000_ : The Dispatcher processes will listen to and receive requests from connected sessions – placing them in the request queue on
the next available server for further processing. Dispatcher processes will also pick up outgoing responses from a results
queue and transmit those results back to the clients.

ora_s000_ : The Shared Server process will pick up requests from a call request queue, make the calls to the database to resolve the request, and then return the results to a result queue.

ora_smco_ : The Space Management Coordinator process handles the coordination and execution of space management tasks – spawning wnnn processes to carry out the task.

ora_fbda_ : The Flashback Data Archiver process archives historical data for tracked tables into the flashback data archives – storing a pre-image of the rows into the flashback archive and keeping the metadata for those rows.

ora_cjq0_ : The Job Queue process is used for batch processing – running user jobs within a scheduler service. Able to run a large number of concurrent jobs, the coordinator process (cjq0) will spawn job queue slaves (jnnn) to actually run the jobs.

===
SET LINESIZE 500
col sid format 999999
col username format a20
col osuser format a15
col name for a20
col ERROR for 999999999999999
col TRACEFILE for a80
SELECT b.spid,a.sid, a.serial#,a.username, a.osuser,BACKGROUND,name ,bg.DESCRIPTION,ERROR,a.sql_id,TRACEFILE
FROM v$session a, v$process b,V$BGPROCESS BG
WHERE a.paddr= b.addr
and BACKGROUND=1
and bg.paddr= b.addr
ORDER BY b.spid;



Oracle trace file compress

# Gzips Oracle Archive logs that are older than 5 days
#

cd /opt/oracle/admin/cccdb/arch
/usr/bin/find . -name '*.arc' -mtime +5 -exec /usr/bin/gzip -9 {} \;

Oracle Shell script for wait capture

#!/bin/bash

ORACLE_HOME=/opt/oracle/product/10.2; export ORACLE_HOME
ORACLE_SID=cccdb; export ORACLE_SID

# export PATH=$ORACLE_HOME/bin:.:$PATH

sqlplus -s /nolog <connect / as sysdba

set serveroutput on
set pagesize 100 lines 130
set feed on heading off
spool orapinx.alert

-- execute immediate dbms_output.put_line('see oracle dump');

declare

x number(7);

begin

select sysdate from dual;
select count(*) into x from v\$session_wait where event like '\%wait on X\%';

-- dbms_output.put_line('see oracle dump');

if x>0 then

execute immediate 'ALTER SESSION SET max_dump_file_size = UNLIMITED' ;
execute immediate 'ALTER SESSION SET EVENTS ''10998 trace name context forever,level 1''';
execute immediate 'ALTER SYSTEM SET EVENTS ''immediate trace name systemstate level 10''';
execute immediate 'ALTER SYSTEM SET EVENTS ''immediate trace name systemstate level 10''';
execute immediate 'ALTER SYSTEM SET EVENTS ''immediate trace name systemstate level 10''';

dbms_output.put_line('see oracle dump');
else
null;
end if;
end;

spool off
exit
EOF

# /opt/oracle/admin/cccdb/scripts

touch /opt/oracle/admin/cccdb/orapinx1.tmp

if [ `cat orapinx.alert|wc -l` -gt 0 ]

then
# echo "test"
cat /opt/oracle/admin/cccdb/scripts/orapinx.alert > /opt/oracle/admin/cccdb/scripts/orapinx.tmp

# to remove blank space
# cat tablespace.tmp | awk '$0!~/^$/ {print $0}' > xyz.tmp
# ^L= ctrl+L <<<<<<<<<--------


cat /opt/oracle/admin/cccdb/scripts/orapinx.tmp | sed 's/^L//g' > /opt/oracle/admin/cccdb/scripts/orapinx1.tmp

mv /opt/oracle/admin/cccdb/orapinx1.tmp /opt/oracle/admin/cccdb/orapinx.tmp



# mailx -s " Ora-error wait PIN X for APTUS (Novagenesis) Database" -r oracle@joshua anuj.singh@XXXX.co.uk < orapinx.tmp

fi

Oracle DBA

anuj blog Archive