How to Create a Database User

SQL>  select GRANTEE,GRANTED_ROLE,ADMIN_OPTION,DEFAULT_ROLE from dba_role_privs where GRANTEE ='JOHNSS1';
GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
JOHNSS1                        RPG_READ_ACCESS                NO  YES
SQL> select DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username = 'JOHNSS1';
DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
------------------------------ ------------------------------
TOOLS                          TEMP
SQL> CREATE USER SELVAJ IDENTIFIED BY ****
  2  DEFAULT TABLESPACE TOOLS
  3  TEMPORARY TABLESPACE temp
  4  ;
User created.
SQL> grant RPG_READ_ACCESS to SELVAJ;
Grant succeeded.
SQL> select GRANTEE,GRANTED_ROLE,ADMIN_OPTION,DEFAULT_ROLE from dba_role_privs where GRANTEE ='SELVAJ';
GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
SELVAJ                         RPG_READ_ACCESS                NO  YES
SQL> conn SELVAJ/*****

How to enable a trace for a concurrent program

Login as Sysadmin -> System Administrator -> Concurrent : Program -> Define
Press F11 -> Enter the COncurrent Program Name (Eg. Active Users) -> Press Ctrl + F11
Enable the check box “Enable Trace” above “Copy To” button.
Click on Save.
Close the window.
Select Requests -> Run
Submit the concurrent request.
After completion or during execution of the request, you should see a trace file generated on the database server under udump directory.
SQL to identify the trace file
select oracle_process_id from fnd_concurrent_requests where request_id=[request_id];
DB Node
cd $ORACLE_HOME/admin/[SID]_[hostname]/udump
ls -latr *[oracle_process_id]*.*
P.S: Dont forget to disable the trace

how to drop a tablespace

DROP TABLESPACE <tablespace name> INCLUDING CONTENTS AND DATAFILES;

If you don’t specify “AND DATAFILES”, Oracle will not drop the physical datafile after the DROP TABLESPACE command, but you can always delete the files from operating system shell (If the data files are locked, restart the server).
But always perform a backup of the database, as the “DROP TABLESPACE” command is irreversible. It’s also a good practice to check and identify how many datafiles a tablespace actually has before performing the drop action in order to avoid mistake.

SQL> select name from v$database;
NAME
---------
PNDW
SQL>
SQL>
SQL> select STATUS from v$datafile where NAME like '%xxtne%';
STATUS
-------
OFFLINE
OFFLINE
OFFLINE
OFFLINE
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces where tablespace_name in ('XXTNED','XXTNEX');
TABLESPACE_NAME                STATUS
------------------------------ ---------
XXTNED                         OFFLINE
XXTNEX                         OFFLINE

SQL> select file_name from dba_data_files where tablespace_name in ('XXTNED','XXTNEX');
FILE_NAME
--------------------------------------------------------------------------------
/aa_gen_dvd/oradata02/pndw/xxtned01.dbf
/aa_gen_dvd/oradata02/pndw/xxtnex01.dbf
/aa_gen_dvd/oradata02/pndw/xxtned02.dbf
/aa_gen_dvd/oradata02/pndw/xxtnex02.dbf
pndw$bdf /aa_gen_dvd/oradata02/
Filesystem          kbytes    used   avail %used Mounted on
/dev/vgaa_gen_dvd/lvol4
                   131072000 123009269 7581899   94% /aa_gen_dvd/oradata02

SQL> select name from v$database;
NAME
---------
PNDW
SQL> DROP TABLESPACE XXTNED INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
SQL>
SQL>
SQL> DROP TABLESPACE XXTNEX INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
SQL>
SQL> select file_name from dba_data_files where tablespace_name in ('XXTNED','XXTNEX');
no rows selected
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces where tablespace_name in ('XXTNED','XXTNEX');
no rows selected
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
pndw$bdf /aa_gen_dvd/oradata02/
Filesystem          kbytes    used   avail %used Mounted on
/dev/vgaa_gen_dvd/lvol4
                   131072000 102529205 26781960   79% /aa_gen_dvd/oradata02

find the details of concurrent requests from request id

set linesize 900
column RESPONSIBILITY_NAME format a30
column USER_CONCURRENT_PROGRAM_NAME format a30
column MACHINE  format a10
column REQUESTOR format a15

select cmp.USER_CONCURRENT_PROGRAM_NAME ,cm.request_id,
(select user_name from apps.fnd_user where USER_ID =cm.requested_by) Requestor, PARENT_REQUEST_ID,t.USED_UREC,t.USED_UBLK,cm.phase_code,
cm.status_code,s.sid,s.serial#,s.saddr,s.username,s.status, s.OSUSER,s.PROCESS,
s.MODULE,s.ACTION,fr.RESPONSIBILITY_NAME
from v$transaction t,v$session s,apps.FND_CONCURRENT_REQUESTS cm,
apps.FND_RESPONSIBILITY_VL fr,
apps.FND_CONCURRENT_PROGRAMS_VL cmp
where t.addr(+)=s.taddr and s.process(+)=cm.os_process_id
AND fr.RESPONSIBILITY_ID= cm.RESPONSIBILITY_ID
AND cmp.CONCURRENT_PROGRAM_ID=cm.CONCURRENT_PROGRAM_ID
and cm.request_id=&reqid
/

R12

In this post I am going to cover R12 architecture. Discernible change from its predecessors is INST_TOP.

R12 file system has come up with new model - Code, Data, Configurations are segregated nicely to have easy maintenance, to avert NFS mount issues on shared appltier configuration systems. Autoconfig will not write anything in appltop, commontop area in R12. All instance specific configurations, log files are written in INST_TOP area. Instance Home provides the ability to share Applications and technology stack code among multiple instances. I have highlighted the INST_TOP in the box. In the forthcoming posts I will clearly explain the in depth of INST_TOP(As a DBA you will spend much time in INST_TOP area when you comes to application tier node management ( e.g checking logs, starting/stopping services, ssl certificates management, CONTEXT_FILE modification etc.)

Techstack Components:

DB_TIER:
10.2.0.2 RDBMS ORACLE_HOME

APPL-TIER

10.1.2 C ORACLE_HOME / FORMS ORACLE_HOME (8.0.6 ORACLE HOME equivalence)
10.1.3 Java ORACLE_HOME/OC4J ORACLE_HOME (iAS ORACLE_HOME equivalence)

Concurrent Manager

set verify off
set lines 256
set trims ON
set pages 60
col concurrent_queue_id format 99999 heading "QUEUE Id"
col concurrent_queue_name format a20 trunc heading "QUEUE Code"
col user_concurrent_queue_name format a30 trunc heading "Concurrent Queue Name"
col max_processes format 999 heading "Max"
col running_processes format 999 heading "Act"
col running format 999 heading "Run"
col target_node format a15 heading "Node"
col status format a12 trunc heading "Status"
col run format 9999 heading 'Run'
col pend format 9999 heading 'Pending'
col cmgr_program FOR a65;
SELECT 'Instance : '
    ||NAME instance_name
FROM   v$database;

Prompt ===========================
Prompt concurrent manager status
Prompt ===========================
SELECT q.concurrent_queue_id,
      q.concurrent_queue_name,
      q.user_concurrent_queue_name,
      q.target_node,
      q.max_processes,
      q.running_processes,
      running.run                               running,
      pending.pend,
      Decode(q.control_code, 'D', 'Deactivating',
                             'E', 'Deactivated',
                             'N', 'Node unavai',
                             'A', 'Activating',
                             'X', 'Terminated',
                             'T', 'Terminating',
                             'V', 'Verifying',
                             'O', 'Suspending',
                             'P', 'Suspended',
                             'Q', 'Resuming',
                             'R', 'Restarting') status
FROM   (SELECT concurrent_queue_name,
              COUNT(phase_code) run
       FROM   fnd_concurrent_worker_requests
       WHERE  phase_code = 'R'
              AND hold_flag != 'Y'
              AND requested_start_date <= SYSDATE
       GROUP  BY concurrent_queue_name) running,
      (SELECT concurrent_queue_name,
              COUNT(phase_code) pend
       FROM   fnd_concurrent_worker_requests
       WHERE  phase_code = 'P'
              AND hold_flag != 'Y'
              AND requested_start_date <= SYSDATE
       GROUP  BY concurrent_queue_name) pending,
      apps.fnd_concurrent_queues_vl q
WHERE  q.concurrent_queue_name = running.concurrent_queue_name(+)
      AND q.concurrent_queue_name = pending.concurrent_queue_name(+)
      AND q.enabled_flag = 'Y'
ORDER BY Decode(q.application_id, 0, Decode(q.concurrent_queue_id, 1, 1,4, 2)),
         Sign(q.max_processes) DESC,
         q.concurrent_queue_name,
         q.application_id;

find and remove files older then 30 days

find /eb_rao0_p/applmgr/pnoltpcomn/admin/log/pnoltp_auxpap02/f60webmx_dump*  -mtime +30 -exec rm  {} \;


:find /eb_rao0_p/applmgr/pnoltpcomn/admin/log/pnoltp_auxpap02/*.log  -mtime +30 -exec rm  {} \;


 

Usefull daily activities commands

alter system kill session '2405,2664';
find / -name "*.system.log"
$Header bomstrc.odf 115.19.115102.3 2005/12/14 21:19:47 rfarook ship

$ find ./ -type l -exec ls -ltr {} \; | grep fndcpesr

If  file  A has control characters and then want to remove follow these steps.
dos2ux  A > B
mv B A
A  is now clean of control Characters

rm `ls -lrt | grep "Dec 26" | awk '{print $9}'`  -- /eb_rda0_p/applcsf
find . -name "*.req" -mtime +80 -exec rm '{}' \; -- keep 80 days and remove all b4 that
select running_processes from fnd_concurrent_queues where concurrent_queue_name = 'WFMLRSVC';---check workflow mailer

ddw7$find . -mtime +10 -exec rm -rf {} \;
ddw7$find . -mtime +10 -exec ls -lrt {} \;
find /tmp -name "Acro*" -mtime +15 -exec rm –f  {} \;

ls -l *.log|sort +4 -nr | more

vmstat 1 10

@usrsql_sid.sql

select l1.sid,v1.serial#, ' IS BLOCKING ',l2.sid from gv$lock l1,gv$lock l2,v$session v1 where v1.sid = l1.sid and  l1.block =1 and l2.request > 0 and
l1.id1=l2.id1 and l1.id2=l2.id2;

SELECT s.sid, p.spid, 'kill -9 '||p.spid FROM v$session s, v$process p WHERE s.paddr=p.addr(+) AND s.sid=3749

alter tablespace IEXX add datafile '/eb_rdo0_p/oradata/pnoltpdata/iexx104.dbf' size 1000m;
tkprof pnoltp_ora_27707_CSISCHED_CR302445386.trc pnoltp_ora_27707_CSISCHED_CR302445386.txt sys=no  sort='(prsela,exeela,fchela)' explain=apps/p0rSche

dba SELECT 'alter system kill session ''' || sid || ',' || serial# || ''';'
> FROM v$session
> WHERE status LIKE 'INACTIVE' AND MODULE LIKE 'JDBC Thin Client'
> AND username NOT LIKE 'APPLSYSPUB' AND TRUNC(logon_time) <
> TRUNC(SYSDATE)ORDER BY logon_time; 
>
>
>
>
> /******************** Blocking Session *******************************/
SELECT * FROM dba_locks WHERE blocking_others='Blocking'
>

SELECT * FROM v$locked_object WHERE object_id=2392
/******************* Holder(1)& Waiters(0) **************************/
>
SELECT DECODE(request,0,'Holder: ','Waiter: ')|| sid sess, id1, id2,lmode,request, TYPE FROM V$LOCK WHERE (id1, id2, TYPE) IN (SELECT id1, id2, TYPE FROM V$LOCK WHERE request>0) ORDER BY id1, request
>
>
> /******************* Latch Count *************************************/
SELECT event, COUNT(1) FROM v$session_wait WHERE event IN ('latch free','enqueue','library cache lock','library cache load lock','library cache pin') GROUP BY event HAVING COUNT(1) > 2 ORDER BY event
>
> /******************** Latching Session *******************************/
> SELECT event, w.sid, s.sql_hash_value, s.username, seq#, wait_time,
> seconds_in_wait,
> DECODE( p1text, NULL, NULL, p1text || ' = ' || p1 ) ||
> DECODE( p2text, NULL, NULL, ', ' || p2text || ' = ' || p2 ) ||
> DECODE( p3text, NULL, NULL, ', ' || p3text || ' = ' || p3 )
> parameters,
> DECODE( s.action, 'Concurrent Request', 'CM/' || s.MODULE, s.MODULE
> ) program_name
> FROM v$session s, v$session_wait w
> WHERE event NOT IN ( 'pmon timer','rdbms ipc message','smon timer','WMON
> goes to sleep', 'pipe get', 'pipe put',-- 'Null event',
> 'SQL*Net message from client','client message','slave
> wait','queue messages', 'wakeup time manager', 'i/o slave wait' )
> AND w.sid = s.sid -- and event in ('latch free','enqueue','library
> cache lock','library cache load lock','library cache pin')
> AND event = 'enqueue'
> ORDER BY event, p1, p2;
>
> /******************** Running Sessoin *******************************/
SELECT /*+ choose */ s.status "Status", si.sid, s.MODULE "Module",s.username "DB User", s.osuser "OS User", s.machine "Machine",s.TYPE "Type",s.client_info "Client Info", s.server "Server", s.terminal "Terminal", s.program "Program", p.program "O.S. Program", lockwait "Lock Wait", s.process "Process", p.pid, s.audsid,s.action FROM v$session s, v$process p, sys.V_$SESS_IO si WHERE s.paddr = p.addr(+) AND si.sid(+) = s.sid AND s.sid = 2801 AND (s.username IS NOT NULL) ORDER BY 1 ASC,6 DESC
 /******************** Running Query *******************************/
SELECT * FROM v$sql WHERE hash_value=:hv
>
 select * from v$session where sid=4037

>
> /******************** Killing UNIX Sessoin *******************************/
SELECT s.sid, p.spid, 'kill -9 '||p.spid FROM v$session s, v$process p WHERE s.paddr=p.addr(+) AND s.sid=2244
>
> /********* Killing Long Running FRONT END Sessoin (Executed in each 15
> min)*************/
> SELECT sid, MODULE, status, last_call_et "SECONDS RUNNING",'kill -9
> '||p.spid
> FROM v$session s, v$process p WHERE s.paddr = p.addr AND s.last_call_et >=
> 300
> AND s.status <> 'INACTIVE' AND MODULE IN
> ('CSCCCCRC','HTGWRK','XXHTCSCCCCRC')
> --'JDBC Thin Client','FNDSCSGN') -- include this also to kill all the front
> end user's session.
>
> /******************** Killing stray database sessions
> **********************/
> SELECT 'alter system kill session ''' || sid || ',' || serial# || ''';'
> FROM v$session
> WHERE status LIKE 'INACTIVE' AND MODULE LIKE 'JDBC Thin Client'
> AND username NOT LIKE 'APPLSYSPUB' AND TRUNC(logon_time) <
> TRUNC(SYSDATE)ORDER BY logon_time;
>
>
> /**************** Monitoring/Cleaning stray Oracle processes ************/
> -- OS processes created by Oracle and which does not has corresponding
> oracle sessions
> -- Kill process containing(LOCAL=NO) and have date older than the current
> date
SELECT 'ps -fp '||spid,'kill -9 '||spid FROM v$process WHERE NOT EXISTS
 (SELECT 1 FROM v$session WHERE paddr = addr);
>
> /**************** Library Cache Lock/Pins ******************/
> SELECT /*+ ordered */ w1.sid waiting_session, p.spid, h1.sid
> holding_session, p1.spid,w.kgllktype lock_or_pin, w.kgllkhdl address,
> DECODE(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
> 'Unknown') mode_held,
> DECODE(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
> 'Unknown') mode_requested
> FROM DBA_KGLLOCK w, DBA_KGLLOCK h, v$session w1, v$session h1, v$process
> p, v$process p1
> WHERE (((h.kgllkmod != 0) AND (h.kgllkmod != 1) AND ((h.kgllkreq = 0) OR
> (h.kgllkreq = 1)))
> AND (((w.kgllkmod = 0) OR (w.kgllkmod= 1)) AND ((w.kgllkreq != 0) AND
> (w.kgllkreq != 1))))
> AND w.kgllktype = h.kgllktype AND w.kgllkhdl = h.kgllkhdl
> AND w.kgllkuse = w1.saddr
> AND h.kgllkuse = h1.saddr AND p.addr = w1.paddr AND
> p1.addr = h1.paddr
>
> SELECT sid, MODULE, status, sql_hash_value FROM v$session WHERE sid=&sid;
>
SELECT DISTINCT sql_text FROM v$sql WHERE hash_value=&hv;
>
> /***************** Shared Pool free size ********************/
SELECT * FROM v$parameter WHERE name LIKE 'shared%'
>
> SELECT pool,name,bytes/1024/1024 "FREE MB" FROM v$sgastat WHERE pool LIKE
> '%shared pool%' AND name LIKE '%free%'
>
> /***************** User wise session check ********************/
 SELECT username, status, COUNT(1) FROM v$session GROUP BY username, status ORDER BY 1
>
> /***************** Gather Stats Monitoring *******************/
> SELECT sid, opname, message, ROUND(((totalwork-sofar)/totalwork),4)*100 "%
> PENDING", time_remaining,
> start_time, username, sql_hash_value, serial# FROM v$session_longops WHERE
> time_remaining >0
>
> /***************** Top most executing queries ****************/
SELECT COUNT(*), SUM(executions) "TotExecs", MODULE,SUBSTR(sql_text,1,100)"SQL" FROM v$sqlarea WHERE executions < 5 GROUP BY SUBSTR(sql_text,1,100),MODULE HAVING COUNT(*) > 30 ORDER BY 1 DESC;
>
>
>
> SELECT * vrom v$process WHERE spid = :spid;
>
> SELECT * FROM V$session WHERE paddr = :addr;
>
>
>
> SELECT 'kill -9 ' ||p.spid
> FROM v$session s, v$process p, sys.V_$SESS_IO si
> WHERE s.paddr = p.addr(+) AND si.sid(+) = s.sid AND
> --s.username IN ('ORASSO_PUBLIC')
> s.username IN ('PORTAL_PUBLIC', 'ORASSO_PUBLIC','PORTAL')
> --('PORTAL_PUBLIC', 'ORASSO_PUBLIC')
> --s.username IN ('PORTAL_PUBLIC')
> --s.username IN ('PORTAL')
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> TO FIND OUT AUDIT++++++++++++++++++++++++++++++++++++++++++++++++
> +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>
> SELECT
> DECODE(NVL(alt,'-/-'),'-/-',NULL,CHR(0)||CHR(47)||CHR(0),NULL,'noaudit
> alter on '||owner||'.'||object_name||';') alt_stmpt
> ,DECODE(NVL(aud,'-/-'),'-/-',NULL,CHR(0)||CHR(47)||CHR(0),NULL,'noaudit
> audit on '||owner||'.'||object_name||';') aud_stmt
> ,DECODE(NVL(com,'-/-'),'-/-',NULL,CHR(0)||CHR(47)||CHR(0),NULL,'noaudit
> comment on '||owner||'.'||object_name||';') com_stmt
> ,DECODE(NVL(del,'-/-'),'-/-',NULL,CHR(0)||CHR(47)||CHR(0),NULL,'noaudit
> delete on '||owner||'.'||object_name||';') del_stmt
> ,DECODE(NVL(gra,'-/-'),'-/-',NULL,CHR(0)||CHR(47)||CHR(0),NULL,'noaudit
> grant on '||owner||'.'||object_name||';') gra_stmt
> ,DECODE(NVL(ind,'-/-'),'-/-',NULL,CHR(0)||CHR(47)||CHR(0),NULL,'noaudit
> index on '||owner||'.'||object_name||';') ind_stmt
> ,DECODE(NVL(ins,'-/-'),'-/-',NULL,CHR(0)||CHR(47)||CHR(0),NULL,'noaudit
> insert on '||owner||'.'||object_name||';') ins_stmt
> ,DECODE(NVL(loc,'-/-'),'-/-',NULL,CHR(0)||CHR(47)||CHR(0),NULL,'noaudit
> lock on '||owner||'.'||object_name||';') loc_stmt
> ,DECODE(NVL(ren,'-/-'),'-/-',NULL,CHR(0)||CHR(47)||CHR(0),NULL,'noaudit
> rename on '||owner||'.'||object_name||';') ren_stmt
> ,DECODE(NVL(sel,'-/-'),'-/-',NULL,CHR(0)||CHR(47)||CHR(0),NULL,'noaudit
> select on '||owner||'.'||object_name||';') sel_stmt
> ,DECODE(NVL(upd,'-/-'),'-/-',NULL,CHR(0)||CHR(47)||CHR(0),NULL,'noaudit
> update on '||owner||'.'||object_name||';') upd_stmt
> ,DECODE(NVL(REF,'-/-'),'-/-',NULL,CHR(0)||CHR(47)||CHR(0),NULL,'noaudit
> reference on '||owner||'.'||object_name||';') ref_stmt
> ,DECODE(NVL(exe,'-/-'),'-/-',NULL,CHR(0)||CHR(47)||CHR(0),NULL,'noaudit
> execute on '||owner||'.'||object_name||';') exe_stmt
> ,DECODE(NVL(cre,'-/-'),'-/-',NULL,CHR(0)||CHR(47)||CHR(0),NULL,'noaudit
> create on '||owner||'.'||object_name||';') cre_stmt
> ,DECODE(NVL(rea,'-/-'),'-/-',NULL,CHR(0)||CHR(47)||CHR(0),NULL,'noaudit
> read on '||owner||'.'||object_name||';') rea_stmt
> ,DECODE(NVL(wri,'-/-'),'-/-',NULL,CHR(0)||CHR(47)||CHR(0),NULL,'noaudit
> write on '||owner||'.'||object_name||';') wri_stmt
> FROM DBA_OBJ_AUDIT_OPTS
> WHERE (ALT <> '-/-'
> OR AUD <> '-/-'
> OR COM <> '-/-'
> OR DEL <> '-/-'
> OR GRA <> '-/-'
> OR IND <> '-/-'
> OR INS <> '-/-'
> OR LOC <> '-/-'
> OR REN <> '-/-'
> OR SEL <> '-/-'
> OR UPD <> '-/-'
> OR REF <> '-/-'
> OR EXE <> '-/-'
> OR CRE <> '-/-'
> OR REA <> '-/-'
> OR WRI <> '-/-' )
> ORDER BY 3, owner, object_type, object_name
>
> USER growth month BY month -
> SELECT TO_CHAR(TRUNC(creation_date),'Mon-yy') "Month", COUNT(*) "No. Of
> users created"
> FROM fnd_user WHERE TRUNC(creation_date) >= '01-Jan-05' GROUP BY
> TO_CHAR(TRUNC(creation_date),'Mon-yy')
> ORDER BY TO_DATE(TO_CHAR(TRUNC(creation_date),'Mon-yy'),'Mon-yy');
>
> USER growth weekly-
> SELECT TO_CHAR(TRUNC(creation_date),'Mon-yy')
> "Month",TO_CHAR(TRUNC(creation_date),'W') "Week", COUNT(*) "No. Of users
> created"
> FROM fnd_user WHERE TRUNC(creation_date) >= '01-Jan-06'
> GROUP BY TO_CHAR(TRUNC(creation_date),'Mon-yy'),
> TO_CHAR(TRUNC(creation_date),'W')
> ORDER BY TO_DATE(TO_CHAR(TRUNC(creation_date),'Mon-yy'),'Mon-yy'),
> TO_CHAR(TRUNC(creation_date),'W');
>
>
> SPACE growth month BY month -
> SELECT b.name,TO_CHAR(a.creation_time,'Mon-yy')
> "Month",SUM(a.bytes/(1024*1024)) "Size of datafile added"
> FROM v$datafile a, v$tablespace b WHERE a.ts#=b.ts# AND
> TRUNC(a.creation_time) >= '01-Jan-05'
> GROUP BY b.name,TO_CHAR(a.creation_time,'Mon-yy') ORDER BY
> b.name,TO_DATE(TO_CHAR(a.creation_time,'Mon-yy'),'Mon-yy');
>
> DATABASE growth month wise -
> ----------------------------------------------------------------------------------------------------
>
> SELECT TO_CHAR(creation_time,'Month,YYYY') "Month",
> ROUND(SUM(bytes/1024/1024/1024),2) "Size in GB"
> FROM v$datafile
> WHERE TRUNC(creation_time) >= '01-jan-05'
> GROUP BY
> TO_CHAR(creation_time,'Month,YYYY'),TO_CHAR(creation_time,'YYYYMM')
> ORDER BY TO_CHAR(creation_time,'YYYYMM')
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++
> DATAFILE Addition Info. ex. you want to see the information of IEXD tablespace
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
SELECT FILE#, TO_CHAR(creation_time, 'DD/MON/YYYY HH24:MI'), ts#, status,bytes/(1024*1024) AS "Size MB", name FROM v$datafile WHERE TS# = (SELECT TS# FROM v$tablespace WHERE name ='IEXD') ORDER BY v$datafile.creation_time DESC ,FILE# DESC;
>
>

> /*****************************************************************/
>
>
> SELECT cr.REQUEST_ID,
> cr.PHASE_CODE,
> cr.STATUS_CODE,
> cr.NUMBER_OF_ARGUMENTS,
> cr.ARGUMENT_TEXT,
> cr.CONCURRENT_PROGRAM_ID,
> cp.CONCURRENT_PROGRAM_ID,
> cp.USER_CONCURRENT_PROGRAM_NAME,
> cr.REQUEST_DATE,
> cr.REQUESTED_START_DATE,
> cr.ACTUAL_START_DATE,
> cr.ACTUAL_COMPLETION_DATE
> FROM FND_CONCURRENT_PROGRAMS_VL cp,
> FND_CONCURRENT_REQUESTS cr
> WHERE cp.CONCURRENT_PROGRAM_ID = cr.CONCURRENT_PROGRAM_ID
> AND cp.USER_CONCURRENT_PROGRAM_NAME ='Purge Obsolete Workflow
> Runtime Data'
> ORDER BY requested_start_date DESC
>
> /******* Trace file name of concurrent running ********/
> SELECT 'Request id: '||request_id ,
> 'Trace id: '||oracle_Process_id,
> 'Trace Flag: '||req.enable_trace,
> 'Trace Name:
> '||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
> 'Prog. Name: '||prog.user_concurrent_program_name,
> 'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
> 'Status : '||decode(phase_code,'R','Running')
> ||'-'||decode(status_code,'R','Normal'),
> 'SID Serial: '||ses.sid||','|| ses.serial#,
> 'Module : '||ses.module
> from fnd_concurrent_requests req, v$session ses, v$process proc,
> v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,
> fnd_executables execname
> where req.request_id = :request_id
> and req.oracle_process_id=proc.spid(+)
> and proc.addr = ses.paddr(+)
> and dest.name='user_dump_dest'
> and dbnm.name='db_name'
> and req.concurrent_program_id = prog.concurrent_program_id
> and req.program_application_id = prog.application_id
> and prog.application_id = execname.application_id
> and prog.executable_id=execname.executable_id;
>
>
SELECT SUM(bytes/1024/1024) FROM dba_free_space WHERE tablespace_name='IEXD'
>
> SELECT cr.REQUEST_ID,
> cr.PHASE_CODE,
> cr.STATUS_CODE,
> cr.NUMBER_OF_ARGUMENTS,
> cr.ARGUMENT_TEXT,
> cr.CONCURRENT_PROGRAM_ID,
> cp.CONCURRENT_PROGRAM_ID,
> cp.USER_CONCURRENT_PROGRAM_NAME,
> cr.REQUEST_DATE,
> cr.REQUESTED_START_DATE,
> cr.ACTUAL_START_DATE,
> cr.ACTUAL_COMPLETION_DATE
> FROM FND_CONCURRENT_PROGRAMS_VL cp,
> FND_CONCURRENT_REQUESTS cr
> WHERE cp.CONCURRENT_PROGRAM_ID = cr.CONCURRENT_PROGRAM_ID
> AND cp.USER_CONCURRENT_PROGRAM_NAME ='HTPROMOUPLOADPRO'
> ORDER BY requested_start_date DESC
>
> select * from v$session where sid=190
>
> select * from v$sqltext where hash_value='1172720175' order by piece
>
>
>
> /------------------------------To check the tablespace free space---------/
>
> select fs.tablespace_name "Tablespace", fs.freespace "Free
> MB",df.totalspace "Total MB",
> round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
> from (select tablespace_name,round(sum(bytes) / 1048576) TotalSpace
> from dba_data_files group by tablespace_name ) df,
> (select tablespace_name,round(sum(bytes) / 1048576) FreeSpace
> from dba_free_space group by tablespace_name) fs where df.tablespace_name =
> fs.tablespace_name order by 2 desc
>
> /----------------------------------------------------------------------------------------------------------------/
>
> /---------------------------------------Long running queries time left
> out----------------------/
>
> SELECT
> sid,serial#,USERNAME,opname,start_time,ROUND(((totalwork-sofar)/totalwork),4)*100
>
> "Pct Remaining", MESSAGE,time_remaining FROM v$session_longops WHERE
> time_remaining >0
>
> /----------------------------------------------------------------------------------------------------------------/
>
>
>
> /------------------------------------------To check the tablespace free
> space-------------------------------------/
>
>
> select tablespace_name,max(bytes/1024/1024) from dba_free_space group by
> tablespace_name order by 2 desc
>
>
> /----------------------------------------------------------------------------------------------------------------/
>
>
>
> /oracle/ncrmprddata/csx198.dbf
>
>
> alter tablespace IEXD add datafile '/eb_rdo0_p/oradata/pnoltpdata/iexd103.dbf' size 4g
> 2040m;

select * from v$fast_start_transactions;

SQL> alter tablespace IEXD add datafile '/eb_rdo0_p/oradata/pnoltpdata/iexd103.dbf' size 4082m;
Tablespace altered.
===================tablespace usage=======================
select * from (select s.sid,s.status, s.sql_hash_value sesshash,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 sid,status,sql_hash_value,sqlhash,s.username,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 6 desc,3)
        where  rownum < 11;

============================
select to_char(actual_start_date,'DD-MM-YY HH24:MI'),request_id from fnd_concurrent_requests where oracle_process_id=10583 order by 1;
===========printer=========
SQL> update fnd_profile_option_values set profile_option_value='' where profile_option_id in (select profile_option_id from fnd_profile_options where profile_option_name like 'PRINTER') and profile_option_value like 'ACP_GKJ_ADMIN';
1 row updated.
SQL> select fo.profile_option_id,fv.profile_option_value from fnd_profile_option_values fv,fnd_profile_options fo where fo.profile_option_name like 'PRINTER' and fv.profile_option_id=fo.profile_option_id and fv.profile_option_value like 'ACP_GKJ_A%';
===========================

SQL> update fnd_concurrent_requests set PHASE_CODE='C', STATUS_CODE='T' where REQUEST_ID in (6297322,6297320);
 2 row updated

SQL> update fnd_concurrent_requests set PHASE_CODE='C', STATUS_CODE='T' where REQUEST_ID='6297143';
1 row updated.

SQL> select sysdate from dual@APS_DBLINK;
SYSDATE
---------------
18-NOV-09

SQL> select sysdate from dual@D_DNOLTP;
SYSDATE
---------------
18-NOV-09

$ strings -a WSHUTCOB.pls| grep Header | grep WSHUTCOB  --> to find the versions

ORA-28001: the password has expired (DBD ERROR: OCISessionBegin).

A. Verify if the password is expired.
 1.select username,ACCOUNT_STATUS,EXPIRY_DATE from dba_users where username='DBSNMP';

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DA
------------------------------ -------------------------------- ---------
DBSNMP                         EXPIRED                                             19-DEC-10

B. If the password is expired and you don’t have the password then take the encrypted password using the below query.

 2.SQL> select PASSWORD from dba_users where username='DBSNMP';


 PASSWORD
 ------------------------------
 46169788712239BD

C. Check the profile value for that user .
3. SQL> select PROFILE from dba_users where username='DBSNMP';

PROFILE
------------------------------
APP_QUERY


D. Change the profile value to default temporarily.

4. SQL> alter user DBSNMP PROFILE default;
User altered.

E. Verify if the change is reflected.

5. SQL> select PROFILE from dba_users where username='DBSNMP';
PROFILE
------------------------------
DEFAULT

F. Reset the password using the below command to its old password as in step 2.
6. SQL> alter user DBSNMP identified by values '46169788712239BD';
User altered.

G. Now check if the account is open.
7. SQL> select username,ACCOUNT_STATUS,EXPIRY_DATE from dba_users where username='DBSNMP';
USERNAME                       ACCOUNT_STATUS                   EXPIRY_DA
------------------------------ -------------------------------- ---------
DBSNMP                         OPEN
H. Reset the profile value back to its original as in step 3.

8. SQL> alter user DBSNMP PROFILE APP_QUERY;
User altered.

I. Verify if the change has been reflected and it is the same as it was earlier.
9. SQL> select PROFILE from dba_users where username='DBSNMP';
PROFILE
------------------------------
APP_QUERY
**************   DONE   **************

Killing the Inactive forms sessions running for more then 12 hrs

-- Session Killing script--be carefull

set pages 200
select 'alter system kill session '''||sid||','||serial#||''';'
from
(
select distinct b.sid,b.serial# ,b.status,b.program,b.username,b.action,b.module,
to_char( b.logon_time, 'dd-MON-yyyy hh24:mi:ss' ) logon_time,
trunc( sysdate-b.logon_time ) "Dy",
trunc( mod( (sysdate-b.logon_time)*24, 24 ) )  "Hr",
trunc( mod( (sysdate-b.logon_time)*24*60, 60 ) )  "Mi",
trunc( mod( (sysdate-b.logon_time)*24*60*60, 60 ) ) "Sec"
from V$access a,v$session b, v$process c
where a.sid=b.sid
and b.paddr=c.addr
and b.status='INACTIVE'
and (b.action like '%FRM%' or b.action like '%frm%' or b.program like '%TOAD%' or b.program like '%toad%' or b.program like
'SQL%' or b.program like '%sql%' or b.program like '%FRM%'
or b.program like '%frm%' or b.action like 'SQL%' or b.action like 'sql%' or b.action like 'TOAD%' or b.action like 'toad%')
and (trunc( mod( (sysdate-b.logon_time)*24,24)) >=12 or trunc( sysdate-b.logon_time )>=1));

Find inactive forms/toad sessions running for more than 12 hrs

-- Script to identify FORM / TOAD session running for more than 12 hrs ----
set heading on
set line 9999
set pages 9999
col status for a8
col prog for a10
col username for a8
col module for a15
select p.sid,p.serial#, p.status,p.username,p.action,p.module,p.logon_time
from
(select distinct b.sid,b.serial# ,b.status,b.program,b.username,b.action,b.module,
to_char( b.logon_time, 'dd-MON-yyyy hh24:mi:ss' ) logon_time,
trunc( sysdate-b.logon_time ) "Dy",
trunc( mod( (sysdate-b.logon_time)*24, 24 ) )  "Hr",
trunc( mod( (sysdate-b.logon_time)*24*60, 60 ) )  "Mi",
trunc( mod( (sysdate-b.logon_time)*24*60*60, 60 ) ) "Sec"
from V$access a,v$session b, v$process c
where a.sid=b.sid
and b.paddr=c.addr
and b.status='INACTIVE'
and (b.action like '%FRM%' or b.action like '%frm%' or b.program like '%TOAD%' or b.program like '%toad%' or b.program like
'SQL%' or b.program like '%sql%' or b.program like '%FRM%'
or b.program like '%frm%' or b.action like 'SQL%' or b.action like 'sql%' or b.action like 'TOAD%' or b.action like 'toad%')
and (trunc( mod( (sysdate-b.logon_time)*24,24)) >=12 or trunc( sysdate-b.logon_time )>=1)) p order by p.logon_time;

find tablespaces consuming more then 85% space

. ./test.env
sqlplus "/as sysdba"
set lines 999
set pagesize 999
select tablespace_name, ROUND(bytes_free,2) "Free Space (MB)" , ROUND(bytes_total-bytes_free,2) "Used Space (MB)",
ROUND(bytes_total,2) "Total Size (MB)", perused "% Used", 100-perused "% Free" from
(select a.tablespace_name, a.bytes_free,b.bytes_total, ROUND((100-(100*a.bytes_free/b.bytes_total)),2)  perused from
(Select tablespace_name,sum(bytes)/1024/1024 bytes_free From dba_free_space Group by tablespace_name) a,
(Select tablespace_name,sum(bytes)/1024/1024 bytes_total From dba_data_files Group by tablespace_name) b
where a.tablespace_name=b.tablespace_name) where perused >= 85 and tablespace_name not like '%UNDO%'  ORDER BY 6 desc;

Change APPS password

1. Take Backup of the below files on the db node and all the apps nodes.
wdbsvr.app --> cd $IAS_ORACLE_HOME/Apache/modplsql/cfg/
CGIcmd.dat -->cd $ORACLE_HOME/reports60/server/
2. Bring down all the Application services.
3. FNDCPASS apps/<old apps pwd> 0 Y system/<pwd> SYSTEM APPLSYS <new apps passwd> ( on db node)
4. Manually change the password  with the new password on the below location on db node and apps nodes
wdbsvr.app
CGIcmd.dat
5. Bring up all the services on all the nodes.

verify the db links as they might be using the apps password , so need to drop and recreate them with the new apps password.

Oracle APPS Basics

What is .dbc file , where its stored , whats use of .dbc file ?
dbc as name says is database connect descriptor file which stores database connection information used by application tier to connect to database. This file is in directory $FND_TOP/secure also called as FND_SECURE

What is an UTL_FILE? What are different procedures and functions associated with it?
The UTL_FILE package lets your PL/SQL programs read and write operating system (OS) text files. It provides a restricted version of standard OS stream file input/output (I/O).

What is different between TRUNCATE and DELETE?
The Delete command will log the data changes in the log file where as the truncate will simply remove the data without it. Hence Data removed by Delete command can be rolled back but not the data removed by TRUNCATE. Truncate is a DDL statement whereas DELETE is a DML statement.

What is main concurrent Manager types.
# ICM - Internal Concurrent Manager which manages concurrent Managers
# Standard Managers - Which Manage processesing of requests.
# CRM - Conflict Resolution Managers , resolve conflicts in case of incompatibility.

Whats is difference between two env files in <CONTEXT>.env and APPS<CONTEXT>.env under $APPL_TOP ?
APPS<CONTEXT>.env is main environment file which inturn calls other environment files like <CONTEXT>.env under $APPL_TOP, <CONTEXT>.env under 806 ORACLE_HOME and custom.env for any Customized environment files.

Where would I find .rf9 file, and what it dose ?
These files are used during restart of patch in case of patch failure because of some reason


What is US directory in $AD_TOP or under various product TOP's .
US directory is defauly language directory in Oracle Applications. If you have multiple languages Installed in your Applications then you will see other languages directories besides US, that directory will contain reports, fmx and other code in that respective directory like FR for France, AR for arabic, simplifies chinese or spanish.

Where is appsweb.cfg or appsweb_$CONTEXT.cfg stored and why its used ?
This file is defined by environment variable FORMS60_WEB_CONFIG_FILE This is usually in directory $OA_HTML/bin on forms tier.
This file is used by any forms client session. When a user try to access forms , f60webmx picks up this file and based on this configuration file creates a forms session to user/client

What is Multi Node System ?
Multi Node System in Oracle Applications 11i means you have Applications 11i Component on more than one system. Typical example is Database, Concurrent Manager on one machine and forms, Web Server on second machine is example of Two Node System.

What are things you do to reduce patch timing ?
You can take advantage of following -
# Merging patches via admrgpch
# Use various adpatch options like nocompiledb or nocompilejsp
# Use defaults file
# Staged APPL_TOP during upgrades
# Increase batch size (Might result into negative )

ADIDENT UTILITY is used for what ?
ADIDENT UTILITY in ORACLE application is used to find version of any file . AD Identification. for ex. "adident Header <filename>

What is iAS Patch ?
iAS Patch are patches released to fix bugs associated with IAS_ORACLE_HOME (Web Server Component) Usually these are shiiped as Shell scripts and you apply iAS patches by executing Shell script. Note that by default ORACLE_HOME is pointing to 8.0.6 ORACLE_HOME and if you are applying iAS patch export ORACLE_HOME to iAS . You can do same by executing environment file under $IAS_ORACLE_HOME

Where to check log files after autoconfig is executed ?
On database tier its under $ORACLE_HOME/appsutil/log/$CONTEXT_NAME/MM_DD_YYYY/adconfig.log
On Application tier its under $APPL_TOP/admin/$CONTEXT_NAME/log/MMDDYYYY/adconfig.log

How can I roll back an AutoConfig session?
All backup configuration files from each AutoConfig session are stored in:
On the application tier: /admin//out//
On the database tier: /appsutil/out///
Inside these directories, there will be several files which autoconfig has taken a backup off before making changes. Also there will be a script restore.sh.
You can run restore.sh (Unix) or restore.cmd (Windows) to roll back an AutoConfig session.
You can find adautocfg.sh script for Database tier in $ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME and for Application tier in $COMMN_TOP/admin/scripts/$CONTEXT_NAME

What is difference between .xml file and AutoConfig ?
Autoconfig is Utility to configure your Oracle Application environment. .xml file is repository of all configuration from which AutoConfig picks configuration and polulates related files.

What is .lgi files ?
lgi files are created with patching along with .log files . .lgi files are informative log files containing information related to patch. You can check .lgi files to see what activities patch has done. Usually informative logs.

How will you skip worker during patch ?
If in your adctrl there are six option shown then seventh is hidden option.(If there are seven options visible then 8th option is to Skip worker depending on ad version).

Which two tables created at start of application Patch and drops at end of Patch ?
FND_INSTALLED_PROCESS and AD_DEFFERED_JOBS are the tables that get updated while applying a patch mainly d or unified driver.

How to compile an Oracle Reports file ?
Utility adrepgen is used to compile Reports. Synatx is given below adrepgen userid=apps\<psswd> source = $PRODUCT_TOP\srw\filename.rdf dest=$PRODUCT_TOP\srw\filename.rdf stype=rdffile dtype=rdffile logfile=x.log overwrite=yes batch=yes dunit=character

What is difference between AD_BUGS and AD_APPLID_PATCHES ?
AD_BUGS holds information about the various Oracle Applications bugs whose fixes have been applied (ie. patched) in the Oracle Applications installation.
AD_APPLIED_PATCHES holds information about the "distinct" Oracle Applications patches that have been applied. If 2 patches happen to have the same name but are different in content (eg. "merged" patches), then they are considered distinct and this table will therefore hold 2 records

What is ADSPLICE UTILITY ?
ADSPLICE UTILITY in ORACLE application is utility to add a new product.

What is access_log in apache , what entries are recored in access_log ? Where is default location of this file ?
access_log in Oracle Application Server records all users accessing oracle applications 11i. This file location is defined in httpd.conf with default location at $IAS_ORACLE_HOME/Apache/Apache/logs. Entries in this file is defined by directive LogFormat in httpd.conf Typical entry in access_log is
198.0.0.1 - - [10/Sep/2006:18:37:17 +0100] "POST /OA_HTML/OA.jsp?.... HTTP/1.1" 200 28035
where 200 is HTTP status code and last digits 28035 is bytes dowloaded as this page(Size of page)

How can you licence a product after installation ?
You can use ad utility adlicmgr to licence product in Oracle application.

What is MRC ? What you do as application DBA for MRC ?
MRC also called as Multiple Reporting Currency in oracle application. Default you have currency in US Dollars but if your organization operating books are in other currency then you as application DBA need to enable MRC in applications. How to enable MRC coming soon...

Where is Jserv configuration files stored ?
Jserv configuration files are stored in $IAS_ORACLE_HOME/Apache/Jserv/etc

How to compile JSP in Oracle Applications ?
You can use ojspCompile.pl perl script shipped with Oracle Applications to compile JSP files. This script is under $JTF_TOP/admin/scripts. Sample compilation method is
perl ojspCompile.pl --compile --quiet

Whats is location of access_log file ?
access_log file by default is located in $IAS_ORACLE_HOME/ Apache/Apache/logs. Location of this file is defined in httpd.conf by patameter CustomLog or TransferLog

What is your Oracle application 11i Webserver Version and how to find it ?
From 11.5.8 to 11.5.10 Webserver version is iAS 1.0.2.2.2, In order to find version under $IAS_ORACLE_HOME/Apache/Apache/bin execute ./httpd -version
./httpd -version
Server version: Oracle HTTP Server Powered by Apache/1.3.19
Server built: Dec 6 2005 14:59:13 (iAS 1.0.2.2.2 rollup 5)

What is Location of Jserv configuration files ?
Jserv configuration files are located in $IAS_ORACLE_HOME /Apache/Jserv/etc

What is *.DBC file and whats is location of DBC file ?
DBC as name stands for is database connect descriptor file used to connect to database. This file by default located in $FND_TOP/secure directory also called as $FND_SECURE directory.

What are few profile options which you update after cloning ?
Rapid clone updates profile options specific to site level . If you have any profile option set at other levels like server, responsibility, user....level then reset them


What is TWO_TASK in Oracle Database ?
TWO_TASK mocks your tns alias which you are going to use to connect to database. Lets assume you have database client with tns alias defined as PROD to connect to Database PROD on machine teachmeoracle.com listening on port 1521. Then usual way to connect is sqlplus username/passwd@PROD ; now if you don't want to use @PROD then you set TWO_TASK=PROD and then can simply use sqlplus username/passwd then sql will check that it has to connect to tnsalias define by value PROD i.e. TWO_TASK

Where GWYUID defined and what is its used in Oracle Applications ?
GWYUID is defined in dbc i.e. Database Connect Descriptor file . It is used to connect to database by think clients.

Where will you find forms configuration details apart from xml file ?
Forms configuration at time of startup is in script adfrmctl.sh and appsweb_$CONTEXT_NAME.cfg (defined by environment variable FORMS60_WEB_CONFIG_FILE) for forms client connection used each time a user initiates forms connection.

What is forms server executable Name ?
f60srvm

What are different modes of forms in which you can start Forms Server and which one is default ?
You can start forms server in SOCKET or SERVLET by defualt Forms are configured to start in socket mode.

What are the Back ground processes in Oracle and what are they.
This is one of the most frequently asked question.There are basically 9 Processes but in a general system we need to mention the first five background processes.They do the house keeping activities for the Oracle and are common in any system.
The various background processes in oracle are
a) Data Base Writer(DBWR) :: Data Base Writer Writes Modified blocks from Database buffer cache to Data Files.This is required since the data is not written whenever a transaction is commited.
b)LogWriter(LGWR) :: LogWriter writes the redo log entries to disk. Redo Log data is generated in redo log buffer of SGA. As transaction commits and log buffer fills, LGWR writes log entries into a online redo log file.
c) System Monitor(SMON) :: The System Monitor performs instance recovery at instance startup.This is useful for recovery from system failure
d)Process Monitor(PMON) :: The Process Monitor peforms process recovery when user Process fails. Pmon Clears and Frees resources that process was using.
e) CheckPoint(CKPT) :: At Specified times, all modified database buffers in SGA are written to data files by DBWR at Checkpoints and Updating all data files and control files of database to indicate the most recent checkpoint
f)Archieves(ARCH) :: The Archiver copies online redo log files to archival storal when they are busy.
g) Recoveror(RECO) :: The Recoveror is used to resolve the distributed transaction in network
h) Dispatcher (Dnnn) :: The Dispatcher is useful in Multi Threaded Architecture
i) Lckn :: We can have upto 10 lock processes for inter instance locking in parallel sql.