Search This Blog


Wednesday, July 30, 2014

Daily archive log growth in Oracle Database

SQL> set line 300
SQL> set pages 300
SQL> select to_char(COMPLETION_TIME,'DD/MON/YYYY') Day,
  2       sum(blocks*block_size)/1048576/1024 "Size(GB)",
     count(sequence#) "Total Archives"
     from (select distinct sequence#,
           from v$archived_log
           where completion_time>=sysdate-14)
     group by to_char(COMPLETION_TIME,'DD/MON/YYYY')
     order by 1;  3    4    5    6    7    8    9   10   11   12

DAY                    Size(GB) Total Archives
-------------------- ---------- --------------
17/JUL/2014          42.7481585             72
18/JUL/2014          130.772517            187
19/JUL/2014          208.798485            276
20/JUL/2014          179.345992            238
21/JUL/2014          135.881896            171
22/JUL/2014           101.37771            140
23/JUL/2014          103.462677            141
24/JUL/2014          108.522655            161
25/JUL/2014          96.6876049            134
26/JUL/2014          95.9838228            125
27/JUL/2014          96.2189264            135
28/JUL/2014          106.510056            160
29/JUL/2014          92.0687962            122

13 rows selected.


How to generate the XML Debug Logs (Output Post Processor) for 'Payment Register'.

How to generate the XML Debug Logs (Output Post Processor) for 'Payment Register'.

1. Create the temp directory in $XDO_TOP
On the applications tier move to $XDO_TOP
 - create a temp directory - mkdir temp
 - set the permissions - chmod 777 temp
 - Use pwd and copy this directory location - it will be used in step 2

[applmgr@hostname ~]$ cd $XDO_TOP
[applmgr@hostname 12.0.0]$ mkdir temp

2. Create the Debug Config file
On the Applications Tier - change to $OA_JRE_TOP/bin - cd $OA_JRE_TOP/bin
- Execute this command - ./java -verbose | grep jre | grep 'java.lang.Runtime '
   -In the results - the complete path is present showing where the rt.jar resides
   - change to this directory - this is where the xdedebug.cfg should be created
- Use vi to create a new file called xdodebug.cfg

Enter these two lines:
LogDir=complete_path_to_$XDO_TOP/temp directory you created

[applmgr@hostname 12.0.0]$ cd $OA_JRE_TOP/bin
[applmgr@hostname bin]$ ./java -verbose | grep jre | grep 'java.lang.Runtime '
[Loaded java.lang.Runtime from /app/apps/tech_st/10.1.3/appsutil/jdk/jre/lib/rt.jar]
[applmgr@hostname bin]$ cd /app/apps/tech_st/10.1.3/appsutil/jdk/jre/lib/
[applmgr@hostname lib]$ vi xdodebug.cfg
[applmgr@hostname lib]$ vi xdodebug.cfg
[applmgr@hostname lib]$ cat xdodebug.cfg
[applmgr@hostname lib]$

3. Restart the Middle tier
cd $INST_TOP/admin/scripts
./ apps/apps
./ apps/apps

4. Engage PO Output for Communication - using standard style sheet

Purchasing - Reports/Run
 - Pass in the parameters to print the purchase order
 - Leave the Debug parameter as the default
 - Submit the request

5. Retrieve the logs

Move to $XDO_TOP/temp
 - Here there are files crearted, please collect these and put into a zip file.

Tuesday, July 29, 2014

Process to generate EBS PL/SQL profiler for a concurrent program.

Process to generate EBS PL/SQL profiler for a concurrent program.

Setup the profile option
• Responsibility: System Administrator
• Navigate: Profiles > System
• Select check box for User (User level) and provide the user name
• Query Profile Option Concurrent: Allow Debugging
• Click Find button

Set profile to Yes on the User level. (Otherwise Debug Options is not active under Submit Request Screen)

Run Concurrent Program with pl/sql profiler option setup
• Logon to the Responsibility that runs the Concurrent Program
• Select the concurrent program on Name field and provide necessary Parameters in the Submit Request screen
• (In this doc., Assume we choose ADS Archive Demand Plan as an example concurrent program.)
• In the Submit Request Screen, click on Debug Options button.
• Select the Checkbox for PL/SQL Profiler
• And then click OK button.

How to generate pl/sql profiler output for the concurrent program under EBS? (Doc ID 808005.1)

Saturday, July 26, 2014

Cconcurrent Manager cleaning scripts

SELECT  concurrent_queue_name manager,
concurrent_process_id pid,
process_status_code pscode
FROM    fnd_concurrent_queues fcq, fnd_concurrent_processes fcp
WHERE   fcq.concurrent_queue_id = fcp.concurrent_queue_id
AND     fcq.application_id = fcp.queue_application_id
AND     process_status_code not in ('K', 'S')
AND     concurrent_queue_id=;

If it returen something

UPDATE fnd_concurrent_queues
SET control_code = NULL
WHERE control_code not in ('E', 'R', 'X')
AND control_code IS NOT NULL
AND     concurrent_queue_id=

select * from fnd_concurrent_processes where concurrent_queue_id=;

no rows selected

UPDATE fnd_concurrent_queues
SET running_processes = 0, max_processes = 0 where concurrent_queue_id=;

1 row updated.

SELECT concurrent_queue_name manager,
control_code ccode
FROM fnd_concurrent_queues
WHERE control_code not in ('E', 'R', 'X')
AND control_code IS NOT NULL AND concurrent_queue_id=;

no rows selected

SELECT concurrent_queue_name manager,control_code ccode FROM fnd_concurrent_queues where concurrent_queue_id= ;

MANAGER                        C
------------------------------ -

1 row selected.

UPDATE fnd_concurrent_queues SET target_node = null where  concurrent_queue_id=;

1 row updated.


Commit complete.