Script to Monitor Concurrent Jobs and Hanging Sessions

The examples below are based on the following demo schema's:
• user SCOTT created with script: $ORACLE_HOME/rdbms/admin/scott.sql
• user HR created with script: $ORACLE_HOME/demo/schema/human_resources/hr_main.sql
The Export Data Pump and Import Data Pump examples that are mentioned below are based on the directory my_dir. This directory object needs to refer to an existing directory on the server where the Oracle RDBMS is installed. Example:
-- for Windows platforms:

CONNECT system/manager
CREATE OR REPLACE DIRECTORY my_dir AS 'D:\export';
GRANT read,write ON DIRECTORY my_dir TO public;

-- for Unix platforms:

CONNECT system/manager
CREATE OR REPLACE DIRECTORY my_dir AS '/home/users/export';
GRANT read,write ON DIRECTORY my_dir TO public;
1. QUERY in Parameter file.
Using the QUERY parameter in a parameter file is the preferred method. Put double quotes around the text of the WHERE clause.
Example to export the following data with the Export Data Pump client:
• from table scott.emp all employees whose job is analyst or whose salary is 3000 or more; and
• from from table hr.departments all deparments of the employees whose job is analyst or whose salary is 3000 or more.
File: expdp_q.par
-----------------
DIRECTORY =&nb p;my_dir
DUMPFILE = exp_query.dmp
LOGFILE = exp_query.log
SCHEMAS &nbs ;= hr, scott
INCLUDE = TABLE:"IN ('EMP', 'DEPARTMENTS')"
QUERY &n sp; = scott.emp:"WHERE job = 'ANALYST' OR sal >= 3000"
place following 3 lines on one single line:
QUERY = hr.departments:"WHERE department_id IN (SELECT DISTINCT
department_id FROM hr.employees e, hr.jobs j WHERE e.job_id=j.job_id
AND UPPER(j.job_title) = 'ANALYST' OR e.salary >= 3000)"

-- Run Export DataPump job:

%expdp system/manager parfile=expdp_q.par
Note that in this example the TABLES parameter cannot be used, because all table names that are specified at the TABLES parameter should reside in the same schema.
2. QUERY on Command line.
The QUERY parameter can also be used on the command line. Again, put double quotes around the text of the WHERE clause.
Example to export the following data with the Export Data Pump client:
• table scott.dept; and
• from table scott.emp all employees whose name starts with an 'A'
-- Example Windows platforms:
-- Note that the double quote character needs to be 'escaped'
-- Place following statement on one single line:

D:\> expdp scott/tiger DIRECTORY=my_dir DUMPFILE=expdp_q.dmp
LOGFILE=expdp_q.log TABLES=emp,dept QUERY=emp:\"WHERE ename LIKE 'A%'\"

-- Example Unix platforms:
-- Note that all special characters need to be 'escaped'

% expdp scott/tiger DIRECTORY=my_dir \
DUMPFILE=expdp_q.dmp LOGFILE=expdp_q.log TABLES=emp,dept \
QUERY=emp:\"WHERE ename LIKE \'A\%\'\"
Note that with the original export client two jobs were required:
-- Example Windows platforms:
-- Place following statement on one single line:

D:\> exp scott/tiger FILE=exp_q1.dmp LOG=exp_q1.log TABLES=emp
QUERY=\"WHERE ename LIKE 'A%'\"

D:\> exp scott/tiger FILE=exp_q2.dmp LOG=exp_q2.log TABLES=dept

-- Example Unix platforms:

> exp scott/tiger FILE=exp_q1.dmp LOG=exp_q1.log TABLES=emp \
QUERY=\"WHERE ename LIKE \'A\%\'\"

> exp scott/tiger FILE=exp_q2.dmp LOG=exp_q2.log TABLES=dept
3. QUERY in Oracle Enterprise Manager Database Console.
The QUERY can also be specified in the Oracle Enterprise Manager Database Console. E.g.:
• Login to the Oracle Enterprise Manager 10g Database Console, e.g.: http://my_node_name:5500/em
• Click on link 'Maintenance'
• Under 'Utilities', click on link 'Export to Files'
• Answer questions on the following pages.
• At 'step 2 of 5' (the page with the Options), click on link 'Show Advanced Options'
• At the end of the page, under the QUERY option, click on button 'Add'
• At the next page, choose the table name (SCOTT.EMP)
• And specify the SELECT statement predicate clause to be applied to tables being exported, e.g.: WHERE ename LIKE 'A%'
• Continue with the remaining options, and submit the job.
4. Import Data Pump parameter QUERY.
Similar to previous examples with Export Data Pump, the QUERY parameter can also be used during the import. An example of how to use the QUERY parameter with Import Data Pump:
-- In source database:
-- Export the schema SCOTT:

%expdp scott/tiger DIRECTORY=my_dir DUMPFILE=expdp_s.dmp \
LOGFILE=expdp_s.log SCHEMAS=scott

-- In target database:
-- Import all employees of department 10:

%impdp scott/tiger DIRECTORY=my_dir DUMPFILE=expdp_s.dmp \
LOGFILE=impdp_s.log TABLES=emp TABLE_EXISTS_ACTION=append \
QUERY=\"WHERE deptno = 10\" CONTENT=data_only
Note that this feature was not available with the original import client (imp). Also note that the parameter TABLE_EXISTS_ACTION=append is used to allow the import into an existing table and that CONTENT=data_only is used to skip importing statistics, indexes, etc.

Read more!


How to copy database users from one database to another new database and keep the
login password and granted roles, privileges ?

1. Oracle10g and above: Use Data Pump.
In Oracle10g you can use the Export DataPump and Import DataPump utilities. Example:
-- Step 1: In source database, run a schema level Export Data Pump
-- job and connect with a user who has the EXP-FULL_DATABASE role:

% expdp system/manager DIRECTORY=my_dir DUMPFILE=exp_scott.dmp \
LOGFILE=exp_scott.log SCHEMAS=scott

-- Step 2: In target database, run a schema level Import Data Pump
-- job which will also create the user in the target database:

% impdp system/manager DIRECTORY=my_dir DUMPFILE=exp_scott.dmp \
LOGFILE=imp_scott.log SCHEMAS=scott
Or you can create a logfile (SQLFILE) with all relevant statements. E.g.:
impdp system/manager directory=my_dir dumpfile=exp_scott.dmp logfile=imp_scott.log schemas=scott sqlfile=imp_user.sql

or:
2. In Oracle9i and above: query the Data Dictionary in the source database to obtain the required information to pre-create the user in the target database. Example:
2.1. Obtain the CREATE USER statement in the source database. E.g.:
SET long 200000000
SELECT dbms_metadata.get_ddl('USER','SCOTT') FROM dual;
2.2. Run other queries in the source database to determine which privileges, grants, roles, and tablespace quotas are granted to the users. E.g.:
SET lines 120 pages 100
SELECT * FROM dba_sys_privs WHERE grantee='SCOTT';
SELECT * FROM dba_role_privs WHERE grantee='SCOTT';
SELECT * FROM dba_tab_privs WHERE grantee='SCOTT';
SELECT * FROM dba_ts_quotas WHERE username='SCOTT';
2.3. Create a script file that contains the CREATE USER, CREATE ROLE statements, the GRANT statements, and the ALTER USER statements for tablespace quotas.
2.4. Pre-create the tablespaces for this user with SQL*Plus in the target database. Note that the original CREATE TABLESPACE statement can be obtained in the source database with DBMS_METADATA.GET_DDL. E.g.:
SET long 200000000
SELECT dbms_metadata.get_ddl('TABLESPACE','USERS') FROM dual;
2.5. Run the script of step 2.3. to create the user in the target database.
2.6. Run a user level export. E.g.:
exp system/manager file=exp_scott.dmp log=exp_scott.log owner=scott
2.7. Import this export dumpfile into the target database. E.g.:
imp system/manager file=exp_scott.dmp log=imp_scott.log fromuser=scott touser=scott

or:
3. If all users need to be copied into the new target database: use a full database export from the source database and a full database into the target database. Example:
3.1. Run a full database export. E.g.:
exp system/manager file=exp_f.dmp log=exp_f.log full=y
or in Oracle10g:
expdp system/manager directory=my_dir dumpfile=expdp_f.dmp logfile=expdp_f.log full=y
3.2. Pre-create the tablespaces with SQL*Plus in that target database if they have a different directory structure on the target server. If the directory structure on the target is the same as on the source, ensure that this directory structure is in place (import does not create directories when creating tablespaces). Note that the original CREATE TABLESPACE statement can be obtained with dbms_metadata.get_ddl (see the example in step 2.4. above).
3.3. Import the data into the target database with:
imp system/manager file=exp_f.dmp log=imp_f.log full=y
or in Oracle10g:
impdp system/manager directory=my_dir dumpfile=expdp_f.dmp logfile=impdp_f.log full=y


The rlwrap (readline wrapper) utility provides a command history and editing of keyboard input
for any other command. This is a really handy addition to SQL*Plus and RMAN .

Download the latest rlwrap software from the following URL.
http://utopia.knoware.nl/~hlub/uck/rlwrap/
Unzip and install the software using the following commands.
gunzip rlwrap*.gz
tar -xvf rlwrap*.tar
cd rlwrap*
./configure
make
make check
make install

Run the following commands, or better still append then to the ".bash_profile" of the

oracle software owner.

alias sqlplus='rlwrap ${ORACLE_HOME}/bin/sqlplus'
alias rman='rlwrap ${ORACLE_HOME}/bin/rman'
alias expdp='rlwrap ${ORACLE_HOME}/bin/expdp'
You can now start SQL*Plus or RMAN using "sqlplus" and "rman" respectively, and you will have

a basic command history and the current line will be editable using the arrow and delete keys.

Read more!






StatspackAnalyzer is a true expert system that seeks to codify expert DBA knowledge and advice against any
STATSPACK or AWR report. The well-structured decision rules of expert Oracle tuning specialists were collected,
quantified and then generalized and validated against real-world STATSPACK and AWR reports.
While no automated tool can fully replicate the decision processes of a human DBA tuning expert, this tool
makes observations about exceptional conditions within the STATSPACK or AWR report. This tool was never
intended to replace the human intuition of an Oracle performance expert, and all observations from
statspackanalyzer should be validated with a human expert.





Script to Monitor Concurrent Jobs and Hanging Sessions

Here is a monitoring system to monitor all concurrent jobs, concurrent managers and hung sessions every
hour proactively and take appropriate action immediately. It gives the following reports
1. List of Concurrent Jobs that completed with error in last one hour.
2. List of Concurrent Jobs running for more then 1 hour.
3. List of concurrent Jobs completed with Warning in last one hour
4. List of Jobs that are Pending Normal for more than 10 Minutes.
5. List of Hung sessions or Orphan sessions.
6. List of Concurrent managers with Pending Normal jobs.
7. Critical Jobs completed in last one hour with completion time.

SELECT A FROM
(
select 'CONCURRENT PROGRAMS COMPLETED WITH ERROR STATUS BETWEEN '||to_char(sysdate - (1/24),
'dd-mon-yyyy hh24:mi:ss') || ' AND '|| to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') A, 'A' B,1 SRT from dual
UNION
select RPAD('-',125,'-') A, 'A' B,1.1 SRT from dual
UNION
SELECT to_char( rpad('REQUEST_ID',10) ||' '||rpad('ACTUAL START DATE',20)|| ' ' ||
rpad('CONCURRENT PROGRAM NAME',65)||' '||rpad('REQUESTOR',10)||' '||'P REQ ID'), 'A' B,1.2 FROM DUAL
UNION
select to_char( rpad(to_char(Request_ID),10) ||' '|| RPAD(NVL(to_char(actual_start_date,
'dd-mon-yyyy hh24:mi:ss'),' '),20) || ' ' || rpad(substr(Program,1,65),65)||' '||rpad(substr(requestor,1,10),
10)||' '||to_char(Parent_Request_ID) ) A, 'A' B, 1.4 SRT from fnd_conc_req_summary_v conc
where actual_completion_date > sysdate - (1/24) and phase_code = 'C' and status_code = 'E'
UNION
select RPAD('-',125,'-') A, 'A' B,1.6 SRT from dual
UNION
SELECT ' ', 'A', 1.8 FROM DUAL
UNION
SELECT ' ', 'A', 1.86 FROM DUAL
-----------------------------------------------------------
UNION
select 'CONCURRENT PROGRAMS COMPLETED WITH WARNING STATUS BETWEEN '||to_char(sysdate - (1/24),
'dd-mon-yyyy hh24:mi:ss') || ' AND '|| to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') A, 'D' B,1 SRT from dual
UNION
select RPAD('-',125,'-') A, 'D' B, 1.1 SRT from dual
UNION
SELECT to_char( rpad('REQUEST_ID',10) ||' '||rpad('ACTUAL START DATE',20)|| ' ' ||
rpad('CONCURRENT PROGRAM NAME',65)||' '||rpad('REQUESTOR',10)||' '||'P REQ ID'), 'D' B, 1.2 FROM DUAL
UNION
select to_char( rpad(to_char(Request_ID),10) ||' '|| RPAD(NVL(to_char(actual_start_date,
'dd-mon-yyyy hh24:mi:ss'),' '),20) || ' ' || rpad(substr(Program,1,65),65)||' '||rpad(substr(requestor,1,10),10)
||' '||to_char(Parent_Request_ID) ) A, 'D' B, 1.4 SRT from fnd_conc_req_summary_v conc where
actual_completion_date > sysdate - (1/24) and phase_code = 'C' and status_code = 'G'
and concurrent_program_id not in (47654,31881,47737)
UNION
select RPAD('-',125,'-') A, 'D' B, 1.8 SRT from dual
-----------------------------------------------------------
UNION
SELECT ' ', 'D', 1.86 FROM DUAL
UNION
SELECT ' ', 'D', 1.88 FROM DUAL
UNION
select 'CONCURRENT PROGRAMS THAT ARE PENDING NORMAL FOR THE PAST 10 MINUTES ' A, 'E' B,1 SRT
from dual
UNION
select RPAD('-',125,'-') A, 'E' B, 1.1 SRT from dual
UNION
SELECT to_char( rpad('REQUEST_ID',10) ||' '||rpad('ACTUAL START DATE',20)|| ' ' ||
rpad('CONCURRENT PROGRAM NAME',65)||' '||rpad('REQUESTOR',10)||' '||'P REQ ID'), 'E' B, 1.2 FROM DUAL
UNION
select to_char( rpad(to_char(Request_ID),10) ||' '|| RPAD(NVL(to_char(actual_start_date,
'dd-mon-yyyy hh24:mi:ss'),' '),20) || ' ' || rpad(substr(Program,1,65),65)||' '||rpad(substr(requestor,1,10),10)
||' '||to_char(Parent_Request_ID) ) A, 'E' B, 2 SRT FROM FND_CONC_REQ_SUMMARY_V CONC
WHERE SYSDATE - REQUEST_DATE > 0.00694444444444444 AND REQUESTED_START_DATE < SYSDATE
AND PHASE_CODE = 'P' AND STATUS_CODE = 'Q'
UNION
select RPAD('-',125,'-') A, 'E' B, 3 SRT from dual
UNION
SELECT chr(10)||chr(10) A, 'E' B, 4.4 SRT FROM DUAL
UNION
select 'CONCURRENT PROGRAMS THAT STARTED BEFORE '||to_char(sysdate - (1/24),'dd-mon-yyyy hh24:mi:ss')
||' AND ARE STILL RUNNING ' A, 'B' B,4.6 SRT FROM DUAL
UNION
SELECT RPAD('-',125,'-') A, 'B' B, 4.8 SRT FROM DUAL
UNION
SELECT to_char( rpad('REQUEST_ID',10) ||' '||rpad('ACTUAL START DATE',20)|| ' ' ||
rpad('CONCURRENT PROGRAM NAME',65)||' '||rpad('REQUESTOR',10)||' '||'P REQ ID'), 'B' B, 4.84 SRT FROM DUAL
UNION
SELECT to_char( rpad(to_char(Request_ID),10) ||' '|| RPAD(NVL(to_char(actual_start_date,
'dd-mon-yyyy hh24:mi:ss'),'-'),20) || ' ' || rpad(substr(Program,1,65),65)||' '||rpad(substr(requestor,1,10),
10)||' '||to_char(Parent_Request_ID) ) A, 'B' B, 4.86 SRT FROM FND_CONC_REQ_SUMMARY_V CONC
WHERE SYSDATE - ACTUAL_START_DATE > 0.0416666666666667 AND PHASE_CODE = 'R' AND STATUS_CODE = 'R'
-----------------------------------------------------------------------
UNION
SELECT RPAD('-',125,'-') A, 'C' B, 1.1 SRT FROM DUAL
UNION
SELECT ' ', 'C', 1.2 FROM DUAL
UNION
SELECT ' ', 'C', 5.8 FROM DUAL
UNION
select ' FOLLOWING ARE THE DETAILS OF HUNG OR ORPHAN SESSIONS AS OF '||to_char(sysdate ,
'dd-mon-yyyy hh24:mi:ss') A, 'C' B,1.5 SRT from dual
UNION
select RPAD('-',125,'-') A, 'C' B, 1.6 SRT from dual
UNION
SELECT to_char(rpad(to_char('SID'),5) ||' '||rpad('PROCESS',12)|| ' ' ||rpad('MODULE',10)||' '||rpad('ACTION',
25)||' '||rpad('USERNAME',15)||' '||rpad('PROGRAM',20)||' '||rpad('EVENT',25)) A, 'C' B, 5.2 FROM DUAL
UNION
select to_char(rpad(nvl(to_char(a.sid), ' '),7,' ')||' '||rpad(nvl(a.process, ' '),19,' ')||' '||rpad(nvl(a.module,
' '),10)||' '||rpad(nvl(a.action, ' '),20)||' '||rpad(nvl(a.username, ' '),15)||' '||rpad(nvl(a.program, ' '),20)||' '||
rpad(c.event,25)) A,'C' B, 5.4 SRT from gv$session a, gv$process b, gv$session_Wait c where c.event not
like 'SQL%' and c.event not in ('pmon timer','rdbms ipc message','pipe get','queue messages','smon timer',
'wakeup time manager','PL/SQL lock timer','jobq slave wait','ges remote message','async disk IO','gcs remote
message','PX Deq: reap credit','PX Deq: Execute Reply') and a.paddr=b.addr and a.sid=c.sid and a.inst_id=
c.inst_id and a.inst_id=b.inst_id and a.last_call_et >1800
UNION
select RPAD('-',125,'-') A, 'C' B, 5.6 SRT from dual
UNION
-----------------------------------------------------------------------
SELECT ' ', 'F', 1.01 FROM DUAL
UNION
select 'PENDING NORMAL MANAGERS IN LAST ONE HOUR '|| ' '|| to_char(sysdate - (1/24),
'dd-mon-yyyy hh24:mi:ss') || ' AND '|| to_char(sysdate,'dd-mon-yyyy hh24:mi:ss' ) A, 'F' B,1 SRT from dual
UNION
select RPAD('-',125,'-') A, 'F' B, 1.1 SRT from dual
UNION
SELECT to_char( rpad('CONCURRENT MANAGER NAME',35) || rpad('ACTUAL',25)|| ' ' ||rpad('TARGET',
20)||' '||rpad('RUNNING',25)||' '||'PENDING'), 'F' B, 1.2 FROM DUAL
UNION
select to_char (
decode (
fcq.USER_CONCURRENT_QUEUE_NAME,
'XXXXXX: High Workload',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,53),
'XXXXXX: Standard Manager',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,50),
'XXXXXX: MRP Manager',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,50),
'XXXXXX: Payroll Manager',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,51),
'XXXXXX: Fast Jobs',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,56),
'XXXXXX: Workflow',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,56),
'XXXXXX: Critical Jobs',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,56),
'Inventory Manager',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,56),
'Conflict Resolution Manager',rpad(fcq.USER_CONCURRENT_QUEUE_NAME,51),
null)
||' '||rpad(TO_CHAR(NVL(FCQ.RUNNING_PROCESSES,0)),30)||' '||
rpad(to_char(nvl(FCQ.MAX_PROCESSES,0)),30) ||' '||rpad(to_char(NVL(running,0)),30) || ' '||
to_char(NVL(PENDING,0))) A, 'F' B, 1.3 SRT
from
apps.fnd_concurrent_queues_vl FCQ,
(SELECT nvl(count(*),0) Running, fcwr.concurrent_queue_id
FROM fnd_concurrent_worker_requests fcwr
WHERE fcwr.concurrent_queue_id IN (1755,1756,1757,1758,1759,1760,1754,10,4)
AND (fcwr.phase_code = 'R')
AND fcwr.hold_flag != 'Y'
AND SYSDATE - fcwr.requested_start_date >= 0.00694444444444444
group by fcwr.concurrent_queue_id ) RUNNING ,
( SELECT nvl(count(*),0) Pending, fcwp.concurrent_queue_id
FROM fnd_concurrent_worker_requests fcwp
WHERE fcwp.concurrent_queue_id IN (1755,1756,1757,1758,1759,1760,1754,10,4)
AND (fcwp.phase_code = 'P')
AND fcwp.hold_flag != 'Y'
AND sysdate-fcwp.requested_start_date >= 0.00694444444444444
group by fcwp.concurrent_queue_id ) PENDING
WHERE FCQ.concurrent_queue_id=RUNNING.concurrent_queue_id(+)
AND FCQ.concurrent_queue_id=PENDING.concurrent_queue_id(+)
AND fcQ.concurrent_queue_id IN (1755,1756,1757,1758,1759,1760,1754,10,4)
UNION
select RPAD('-',125,'-') A, 'F' B, 1.4 SRT from dual
UNION
SELECT chr(10)||chr(10) A, 'F' B, 1.5 SRT FROM DUAL
UNION
-----------------------------------------------------------------------
SELECT ' ', 'G', 1.01 FROM DUAL
UNION
select 'CRITICAL PROGRAMS STATUS IN LAST ONE HOUR '|| ' '|| to_char(sysdate - (1/24),
'dd-mon-yyyy hh24:mi:ss') || ' AND '|| to_char(sysdate,'dd-mon-yyyy hh24:mi:ss' ) A , 'G' B,1 SRT from dual
UNION
select RPAD('-',125,'-') A, 'G' B, 1.1 SRT from dual
UNION
SELECT to_char( rpad('CONCURRENT PROGRAM NAME',55) || rpad('AVG TIME',20)|| ' ' ||rpad
('CURR MAX TIME',16)||' '|| 'REQUEST_ID'), 'G' B, 1.1 FROM DUAL
UNION
SELECT to_char (
decode (PROGRAM_NAME,
'AutoCreate Configuration Items',RPAD(PROGRAM_NAME,70),
'Memory-based Snapshot',RPAD(PROGRAM_NAME,71),
'Order Import',RPAD(PROGRAM_NAME,80),
'Workflow Background Process',RPAD(PROGRAM_NAME,69),
PROGRAM_NAME) ||' '||
rpad(TO_CHAR(STATIC.AVG_TIME),25) || ' '||
rpad(TO_CHAR(DYNAMIC.CURR_MAX_TIME),25) || ' '||
to_char(NVL(REQUEST_ID,NULL))) A, 'G' B, 1.2 SRT
FROM
(SELECT
CONCURRENT_PROGRAM_ID,
USER_CONCURRENT_PROGRAM_NAME,
REQUEST_ID,
ROUND((ACTUAL_COMPLETION_DATE-ACTUAL_START_DATE)*24*60,0) CURR_MAX_TIME
FROM APPS.FND_CONC_REQ_SUMMARY_V fcr
WHERE CONCURRENT_PROGRAM_ID IN (36888,
48681,39442,33137,47730,47731,47712,47729,31881)
and phase_code='C'
AND STATUS_CODE='C'
AND ACTUAL_COMPLETION_DATE>=(sysdate - (1/24))
AND REQUEST_ID IN (
SELECT MAX(REQUEST_ID) FROM APPS.FND_CONC_REQ_SUMMARY_V fcr WHERE CONCURRENT_PROGRAM_ID
IN (36888,48681,39442,33137,47730,47731,47712,47729,31881)
and phase_code='C'
AND STATUS_CODE='C'
AND ACTUAL_COMPLETION_DATE>=(sysdate - (1/24))
GROUP BY CONCURRENT_PROGRAM_ID) ) DYNAMIC ,
(select distinct CONCURRENT_PROGRAM_ID "CONCURRENT_PROGRAM_ID",
USER_CONCURRENT_PROGRAM_NAME "PROGRAM_NAME",
DECODE ( CONCURRENT_PROGRAM_ID,36888,39442,33137,31881,10,NULL) AVG_TIME
FROM APPS.FND_CONCURRENT_PROGRAMS_TL fcr
WHERE CONCURRENT_PROGRAM_ID IN
(36888,39442,33137,31881)
AND LANGUAGE='US' ) STATIC
WHERE DYNAMIC.CONCURRENT_PROGRAM_ID(+)=STATIC.CONCURRENT_PROGRAM_ID
UNION
select RPAD('-',125,'-') A, 'G' B, 1.4 SRT from dual
UNION
SELECT chr(10)||chr(10) A, 'G' B, 1.5 SRT FROM DUAL
-----------------------------------------------------------------------
) TEMP
ORDER BY B, SRT, A
Labels: 11i, Common APPS

Read more!






rated 5.0 by 2 people [?]




Posted by Famy Rasheed 0 comments



Prevent Concurrent Requests Executing on New Cloned Environment

Email this | Share on Facebook | Subscribe to this feed


To prevent concurrent requests executing on a newly cloned environment 2 steps will be required.

1) Prevent Concurrent Managers from starting as part of the Clone

2) Change data concerning concurrent requests so they do not execute when the concurrent managers start.

To prevent Applications Services (including Concurrent Managers) from starting automatically during Rapid Clone an enhancement request has been raised to see if this can be considered an option during the clone.
Until this feature is available there is the following workaround.

1. Edit the $COMMON_TOP/clone/bin/adcfgclone.pl

2. Go to the end of the file

3. Change the following lines:-

print "\n Starting application Services for $s_dbSid:\n";
print "Running:\n";
print(" $s_com/admin/scripts/$s_contextname/adstrtal.$ext $s_apps_user/\n");
system("$s_com/admin/scripts/$s_contextname/adstrtal.$ext $s_apps_user/$PWD");

TO:

print "\nNOT Starting application Services for $s_dbSid:\n";
#print "Running:\n";
#print(" $s_com/admin/scripts/$s_contextname/adstrtal.$ext $s_apps_user/\n");
#system("$s_com/admin/scripts/$s_contextname/adstrtal.$ext $s_apps_user/$PWD");

4. Run "perl adcfgclone.pl appsTier" as normal.

The services will not start Automatically when the clone completes allowing data to be changed safely.
There is a need to change 2 sets of concurrent requests to prevent execution
a) Terminate 'Running' Requests
b) Set Pending jobs to 'On Hold'

a) Set Terminating or Running to Completed/Terminated
UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code = 'X'
WHERE status_code ='T'
OR phase_code = 'R'
/
b) Place Pending/(Normal/Standby) to On Hold
UPDATE fnd_concurrent_requests
SET hold_flag = 'Y'
WHERE phase_code = 'P'
AND status_code in ('Q','I')
/
Once these changes have been committed then the Application services including concurrent manager can be restarted and the concurrent jobs will not be executed.
Labels: 11i

Read more!






rated 5.0 by 2 people [?]




Posted by Famy Rasheed 1 comments



Queries Related to Concurrent Requests in 11i Applications

Email this | Share on Facebook | Subscribe to this feed


As part of day to day work, we need to use lot of queries to check the information about concurrent requests. Here are few queries which can be frequently used for day to day works and troubleshooting concurrent request / manager issues.
Note: These queries needs to be run from APPS schema.
Scheduled concurrent requests

Lot of times we need to find out the concurrent programs scheduled. Users can schedule the concurrent requests in three ways (To run once at a specified time / To run periodically / To run on specific days of the month or week).

The below query will return all the concurrent requests which are scheduled using any of the above methods:
SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:' || cr.description,
cp.user_concurrent_program_name
) NAME,
argument_text, cr.resubmit_interval,
NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')
) schedule_type,
DECODE (NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')
),
'PERIODICALLY', 'EVERY '
|| cr.resubmit_interval
|| ' '
|| cr.resubmit_interval_unit_code
|| ' FROM '
|| cr.resubmit_interval_type_code
|| ' OF PREV RUN',
'ONCE', 'AT :'
|| TO_CHAR (cr.requested_start_date, 'DD-MON-RR HH24:MI'),
'EVERY: ' || fcr.class_info
) schedule,
fu.user_name, requested_start_date
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu,
apps.fnd_conc_release_classes fcr
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = 'P'
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = 'US'
AND fcr.release_class_id(+) = cr.release_class_id
AND fcr.application_id(+) = cr.release_class_app_id;

Note: The "SCHEDULE" column in the above query returns a string of zeros and ones for the requests which are scheduled on specific days of the month or week.

Positions 1 through 31: Specific day of the month.
Position 32: Last day of the month
Positions 33 through 39: Sunday through Saturday
Checking the duplicated schedules of the same program with the same arguments

The below query can be used to check the duplicated schedule of the same program with the same arguments. This can be used to alert the users to cancel these duplicated schedules.

Note: This query will return even though the request was submitted using a different responsibility.
SELECT request_id, NAME, argument_text, user_name
FROM (SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:' || cr.description,
cp.user_concurrent_program_name
) NAME,
argument_text, fu.user_name
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = 'P'
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = 'US'
AND fu.user_name NOT LIKE 'PPG%') t1
WHERE EXISTS (
SELECT 1
FROM (SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:'
|| cr.description,
cp.user_concurrent_program_name
) NAME,
argument_text, fu.user_name
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id =
cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = 'P'
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = 'US'
AND fu.user_name NOT LIKE 'PPG%') t2
WHERE t1.NAME = t2.NAME
AND t1.argument_text = t2.argument_text
AND t1.user_name = t2.user_name
GROUP BY NAME, argument_text, user_name
HAVING COUNT (*) > 1)
ORDER BY user_name, NAME
Average pending time per request

This is a very useful query to check the performance of the concurrent managers.

Average pending time for a request is calculated like below:
("Highest of Requested_start_date or Date_submitted" - Actual_start_date ) / Total requests

A Request can be in Pending state for variety of reasons like conflict with other requests, improperly tuned managers (sleep seconds / cache size / number of managers etc)

We can schedule this script to gather data regularly for historical analysis as we normally purge the concurrent requests regularly.
SELECT TO_CHAR (actual_start_date, 'DD-MON-YYYY') DAY,
concurrent_queue_name,
(SUM ( ( actual_start_date
- (CASE
WHEN requested_start_date > request_date
THEN requested_start_date
ELSE request_date
END
)
)
* 24
* 60
* 60
)
)
/ COUNT (*) "Wait_Time_per_Req_in_Secs"
FROM apps.fnd_concurrent_requests cr,
apps.fnd_concurrent_processes fcp,
apps.fnd_concurrent_queues fcq
WHERE cr.phase_code = 'C'
AND cr.actual_start_date IS NOT NULL
AND cr.requested_start_date IS NOT NULL
AND cr.controlling_manager = fcp.concurrent_process_id
AND fcp.queue_application_id = fcq.application_id
AND fcp.concurrent_queue_id = fcq.concurrent_queue_id
GROUP BY TO_CHAR (actual_start_date, 'DD-MON-YYYY'), concurrent_queue_name
ORDER BY 2
Note: Depending on the purging schedules some requests might miss if the corresponding data in fnd_concurrent_processes is purged.
Checking which manager is going to execute a program
The below query identifies the manager which will be executing a given program. This query is based on the specialization rules set for the managers.
SELECT user_concurrent_program_name, user_concurrent_queue_name
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_queue_content cqc,
apps.fnd_concurrent_queues_tl cq
WHERE cqc.type_application_id(+) = cp.application_id
AND cqc.type_id(+) = cp.concurrent_program_id
AND cqc.type_code(+) = 'P'
AND cqc.include_flag(+) = 'I'
AND cp.LANGUAGE = 'US'
AND cp.user_concurrent_program_name = '&USER_CONCURRENT_PROGRAM_NAME' AND NVL (cqc.concurrent_queue_id, 0) = cq.concurrent_queue_id
AND NVL (cqc.queue_application_id, 0) = cq.application_id
AND cq.LANGUAGE = 'US'
To see all the pending / Running requests per each manager wise
SELECT request_id, phase_code, status_code, user_name,
user_concurrent_queue_name
FROM apps.fnd_concurrent_worker_requests cwr,
apps.fnd_concurrent_queues_tl cq,
apps.fnd_user fu
WHERE (cwr.phase_code = 'P' OR cwr.phase_code = 'R')
AND cwr.hold_flag != 'Y'
AND cwr.requested_start_date <= SYSDATE
AND cwr.concurrent_queue_id = cq.concurrent_queue_id
AND cwr.queue_application_id = cq.application_id
AND cq.LANGUAGE = 'US'
AND cwr.requested_by = fu.user_id
ORDER BY 5
Note: The same information can be seen in Administer Concurrent Manager form for each manager.
Checking the incompatibilities between the programs

The below query can be used to find all incompatibilities in an application instance.
SELECT a2.application_name, a1.user_concurrent_program_name,
DECODE (running_type,
'P', 'Program',
'S', 'Request set',
'UNKNOWN'
) "Type",
b2.application_name "Incompatible App",
b1.user_concurrent_program_name "Incompatible_Prog",
DECODE (to_run_type,
'P', 'Program',
'S', 'Request set',
'UNKNOWN'
) incompatible_type
FROM apps.fnd_concurrent_program_serial cps,
apps.fnd_concurrent_programs_tl a1,
apps.fnd_concurrent_programs_tl b1,
apps.fnd_application_tl a2,
apps.fnd_application_tl b2
WHERE a1.application_id = cps.running_application_id
AND a1.concurrent_program_id = cps.running_concurrent_program_id
AND a2.application_id = cps.running_application_id
AND b1.application_id = cps.to_run_application_id
AND b1.concurrent_program_id = cps.to_run_concurrent_program_id
AND b2.application_id = cps.to_run_application_id
AND a1.language = 'US'
AND a2.language = 'US'
AND b1.language = 'US'
AND b2.language = 'US'
The table apps.fnd_concurrent_program_serial has the information about incompatibilities.
Labels: 11i, Common APPS

Read more!






rated 5.0 by 1 person [?]




Posted by Famy Rasheed 0 comments

RAPID CLONE indepth

We need cloning for various of reasons. suppose I have got production instance and want to test some application. Now here I want to test the application, need to apply some product specific patches which might affect whole application then I have to give downtime which will affect business. So if I had the same this on whic hI can play and test my application or code or say I can apply some patches and at the same time business also run then nothing like it. This can be achieved by cloning or duplicating the application.

Duplicating the application is not easy or its just not simply copy the application, it wont work in any case. Logically it sounds fine just to copy the filesystem and relink it and start the application but its not that easy.

Basically for cloning Oracle Application it can be categorised into three major categories.

-Preparing the Source System
-Copy the file System
-Preparing or say Configuring the Target System

You can achive first and third steps using a Rapid Clone utility which Oracle provides for cloning

There can be many ways and methods to clone Oracle Application which many organisations follows but the only supported method provided by Oracle is using Rapid clone.

Rapid Clone is the new cloning utility introduced in Release 11.5.8. Rapid Clone leverages the new installation and configuration technology utilized by Rapid Install.

Basically there are two cloning methodology using Autoconfig and without using autoconfig. First one that is using autoconfig is completely replaced by Rapidclone.

Non-autocofig cloning methodology was generally used before 11.5.5 that is non autoconfig enabled system.

Here I will discuss Rapid Clone method of cloning and also throw some light on cloning 11.0.3 oracle application as some users are still using that.

So first about the Rapid clone method.

In Category 1 that is first you need to make source for clone. You need to dress up Source system so that only configuration files and not the whole file system is copied.

Pre-requiste Steps

To start with cloning you need to first check about some pre-requisites some utilities should be of required version

Here are they,

Oracle Universal Installer should be of 2.2.0.19 version
Perl should be of 5.005 version minimum
JRE 1.1.8 and 1.3.1
JDK 1.3.1
Zip utility of version 2.3 minimum
Latest Autoconfig Templates patches are applied. TXK-O is the latest till date.
Latest Rapid Clone patches should be applied. Latest is ADX.F till date.

After check ing the pre-requisites now your system is ready to clone.

Preparing the Source system:

After checking the above mentioned pre-requisites now you have to prepare the source system which need to be cloned.

In a broad way what this step will do is this will create the staged clone directory which will be having the driver files and configuration file of the source.

So what exactly is going to happen in this step. Lets get in more details

Basically you have to run the adpreclone.pl script once for Application tier and one for the datbase (even if you are on single node ! )

Lets first check for the Database tier

On Source system, run following as ORACLE user

cd RDBMS Oracle_Home/appsutil/scripts/_perl ./adpreclone.pl dbTier

After running this it goes in two stages

dbTechstack and Database

What exactly it will do is
It will create stage cloned area at $ORACLE_HOME/appsutil/clone This clone directory has got following main directories
data,jre,bin,db,html,context

It will prepare datbase techstack at dbTechStack stage. Major activities it will take care of at this stage.

-Creates template files at $ORACLE_HOME/appsutil/template
-Creates driver files at $ORACLE_HOME/appsutil/driver/instconf.drv
-Converts inventory from binary to xml

It will prepare datbase at the database stage. Major activities includes

-Create datbase control file script
$Oracle_Home/appsutil/clone/context/data/stage/addbhomsrc.xml
$Oracle_Home/appsutil/clone/context/data/stage/adcrdb.zip
adcrdbclone.sql

-Generates database creation driver file
$Oracle_Home/appsutil/clone/context/data/driver/data.drv

-Copy JDBC Libraries
$Oracle_Home/appsutil/clone /clone/jlib/classes111.zip

So all this happens on database side

Now lets concentrate on Application tier side. Again it goes almost in the same way that is in two stages.

As a APPLMGR user on application tier this script is run
cd $COMMON_TOP/admin/scripts/_perl ./adpreclone.pl appsTier

It will also create the staged clone directory at $COMMON_TOP/clone
It goes in two stages. Lets see one by one

atTechStack that is Application tier techstack.

- Creates template files for
Oracle_iAS_Home/appsutil/template
Oracle_806_Home/appsutil/template

-Creates Techstack driver files for
Oracle_iAS_Home/appsutil/driver/instconf.drv
Oracle_806_Home/appsutil/driver/instconf.drv

appltop preparation

-It will create application top driver file
$COMMON_TOP/clone/appl/driver/appl.drv

-Copy JDBC libraries
$COMMON_TOP/clone/jlib/classes111.zip

So this all happens in dressing the source system

After then we need to copy the files.

Copy following files

APPL_TOP
OA_HTML ($COMMON_TOP/html)
JAVA_TOP
COMMON_TOP/util
COMMON_TOP/clone
8.0.6 ORACLE_HOME
iAS ORACLE_HOME
DB ORACLE_HOME
dbf files

After this stage now you need to configure the Target node. Basically you will run adcfgclone.pl same for databse tier and for application tier.
Let go in bit more detail:

First we will see for the database tier

On the target node you will run
ORACLE_DB_HOME/appsutil/clone/bin/perl adcfgclone.pl dbTier

Again there are two stages :

First will look at dbTechstack
It will use the driver files and templates which were created at source and has been copied to target.

Following scripts are run
-adchkutl.sh
-adclonectx.pl
-runInstallConfigDriver —- located in $Oracle_Home/appsutil/driver/instconf.drv
-Relinking $Oracle_Home/appsutil/install/adlnkoh.sh

Now for database
-Driver file
$Oracle_Home/appsutil/clone/context/data/driver/data.drv
-Create database adcrdb.zip
-Autoconfig is run
-Control file creation adcrdbclone.sql

Now on application tier
from COMMON_TOP/clone/bin/perl adcfgclone.pl appsTier

Stage at TechStack
-Creates context file for target adclonectx.pl
-Run driver files
$Oracle_806_Home/appsutil/driver/instconf.drv
$Oracle_iAS_Home/appsutil/driver/instconf.drv

Relinking of Oracle Home
$Oracle_806_Home/bin/adlnk806.sh
$Oracle_iAS_Home/bin/adlnkiAS.sh

at the end for ApplTop stage
It runs driver and then autoconfig

So this will totally configure your application which has been copied.

At the end you have to some finishing tasks

-Update Printer settings
-Update Workflow Configuration settings
-Update Profile options