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#,
                  thread#,
                  COMPLETION_TIME,
                  blocks,
                  block_size
           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.

SQL>

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:
LogLevel=STATEMENT
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
LogLevel=STATEMENT
LogDir=/app/apps/apps_st/appl/xdo/12.0.0/temp
[applmgr@hostname lib]$




3. Restart the Middle tier
cd $INST_TOP/admin/scripts
./adstopall.sh apps/apps
./adstrtall.sh 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.

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 have active user 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)

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
------------------------------ -
Invoice_Processing_Manager

1 row selected.

UPDATE fnd_concurrent_queues SET target_node = null where  concurrent_queue_id=;

1 row updated.

commit;

Commit complete.

Running concurrent requests in Oracle Apps EBS (11i,R12)

Set Pages 1000
Set head on
Column Manager   Format A12
Column Request   Format 999999999
Column Program   Format A30
Column User_Name Format A15
Column Started   Format A15
Column OSprocess Format A9
Column SPID      Format A9
Column LocalProcess Format A9
prompt Managers that are currently running a request;
prompt ;
Select /*+ RULE */ substr(Concurrent_Queue_Name,1,12) Manager,
       Request_Id Request, User_name,
       Fpro.OS_PROCESS_ID OSprocess,
      fcr.oracle_process_id LocalProcess,
       substr(Concurrent_Program_Name,1,35) Program, Status_code,
       To_Char(Actual_Start_Date, 'DD-MON-YY HH24:MI') Started
  from apps.Fnd_Concurrent_Queues Fcq, apps.Fnd_Concurrent_Requests Fcr,
      apps.Fnd_Concurrent_Programs Fcp, apps.Fnd_User Fu, apps.Fnd_Concurrent_Processes Fpro
 where
       Phase_Code = 'R' And
       Status_Code <> 'W' And
       Fcr.Controlling_Manager = Concurrent_Process_Id       And
      (Fcq.Concurrent_Queue_Id = Fpro.Concurrent_Queue_Id    And
       Fcq.Application_Id      = Fpro.Queue_Application_Id ) And
      (Fcr.Concurrent_Program_Id = Fcp.Concurrent_Program_Id And
       Fcr.Program_Application_Id = Fcp.Application_Id )     And
       Fcr.Requested_By = User_Id
       order by Started
/

Concurrent request details

set line 300
set pages 200
select r.request_id, s.inst_id,s.sid, s.serial#, s.status,
 substr(r.oracle_process_id, 1, 13) "REQUEST OS",
 r.status_code,r.phase_code, substr(fp.OS_PROCESS_ID, 1, 13) "CM OS ID"
from apps.fnd_concurrent_requests r,
 apps.fnd_concurrent_processes fp,
 gv$session s,
 gv$process p
where r.controlling_manager = fp.concurrent_process_id and
 r.oracle_process_id = p.spid and
 s.inst_id = p.inst_id and
 p.addr = s.paddr and
 r.request_id in (&reqid) order by r.request_id;

Oracle Database Tablespace status

SET lines 202 pages 99 feedback off
COLUMN tablespace_name        format a25             heading 'Tablespace|(TBS)|Name'
COLUMN autoextensible         format a6              heading 'Can|Auto|Extend'
COLUMN files_in_tablespace    format 999             heading 'Files|In|TBS'
COLUMN total_tablespace_space format 99,999,999,999 heading 'Total|Current|TBS|Space'
COLUMN total_used_space       format 99,999,999,999 heading 'Total|Current|Used|Space'
COLUMN total_tablespace_free_space format 99,999,999,999 heading 'Total|Current|Free|Space'
COLUMN total_used_pct              format 999.99      heading 'Total|Current|Used|PCT'
COLUMN total_free_pct              format 999.99      heading 'Total|Current|Free|PCT'
COLUMN max_size_of_tablespace      format 99,999,999,999 heading 'TBS|Max|Size'
COLUMN total_auto_used_pct         format 999.99      heading 'Total|Max|Used|PCT'
COLUMN total_auto_free_pct         format 999.99      heading 'Total|Max|Free|PCT'

TTITLE left _date center Tablespace Space Utilization Status Report skip 2

WITH tbs_auto AS
     (SELECT DISTINCT tablespace_name, autoextensible
                 FROM dba_data_files
                WHERE autoextensible = 'YES'),
     files AS
     (SELECT   tablespace_name, COUNT (*) tbs_files,
               SUM (BYTES) total_tbs_bytes
          FROM dba_data_files
      GROUP BY tablespace_name),
     fragments AS
     (SELECT   tablespace_name, COUNT (*) tbs_fragments,
               SUM (BYTES) total_tbs_free_bytes,
               MAX (BYTES) max_free_chunk_bytes
          FROM dba_free_space
      GROUP BY tablespace_name),
     AUTOEXTEND AS
     (SELECT   tablespace_name, SUM (size_to_grow) total_growth_tbs
          FROM (SELECT   tablespace_name, SUM (maxbytes) size_to_grow
                    FROM dba_data_files
                   WHERE autoextensible = 'YES'
                GROUP BY tablespace_name
                UNION
                SELECT   tablespace_name, SUM (BYTES) size_to_grow
                    FROM dba_data_files
                   WHERE autoextensible = 'NO'
                GROUP BY tablespace_name)
      GROUP BY tablespace_name)
SELECT a.tablespace_name,
       CASE tbs_auto.autoextensible
          WHEN 'YES'
             THEN 'YES'
          ELSE 'NO'
       END AS autoextensible,
       files.tbs_files files_in_tablespace,
       files.total_tbs_bytes/1024/1024 total_tablespace_space,
       (files.total_tbs_bytes - fragments.total_tbs_free_bytes
       )/1024/1024 total_used_space,
       fragments.total_tbs_free_bytes/1024/1024 total_tablespace_free_space,
       (  (  (files.total_tbs_bytes - fragments.total_tbs_free_bytes)
           / files.total_tbs_bytes
          )
        * 100
       ) total_used_pct,
       ((fragments.total_tbs_free_bytes / files.total_tbs_bytes) * 100
       ) total_free_pct,
       AUTOEXTEND.total_growth_tbs/1024/1024 max_size_of_tablespace,
       (  (  (  AUTOEXTEND.total_growth_tbs
              - (AUTOEXTEND.total_growth_tbs - fragments.total_tbs_free_bytes
                )
             )
           / AUTOEXTEND.total_growth_tbs
          )
        * 100
       ) total_auto_used_pct,
       (  (  (AUTOEXTEND.total_growth_tbs - fragments.total_tbs_free_bytes)
           / AUTOEXTEND.total_growth_tbs
          )
        * 100
       ) total_auto_free_pct
  FROM dba_tablespaces a, files, fragments, AUTOEXTEND, tbs_auto
 WHERE a.tablespace_name = files.tablespace_name
   AND a.tablespace_name = fragments.tablespace_name
   AND a.tablespace_name = AUTOEXTEND.tablespace_name
   AND a.tablespace_name = tbs_auto.tablespace_name(+);

Concurrent Manager monitoring scripts

set line 200
set pages 100
col "Concurrent Manager's Name" format a60
spool comcurrent_manger_status.lst
SELECT b.USER_CONCURRENT_QUEUE_NAME AS "Concurrent Manager's Name",
       a.max_processes AS "TARGET Processes",
       a.running_processes AS "ACTUAL Processes",
       'Mismatch' Status
FROM apps.fnd_concurrent_queues a,apps.FND_CONCURRENT_QUEUES_VL b
 WHERE a.CONCURRENT_QUEUE_NAME = b.CONCURRENT_QUEUE_NAME
 and a.max_processes <> a.running_processes;

SELECT b.Pending_Standby,
       b.Pending_Normal,
       b.name AS "Concurrent Manager's Name",
       a.max_processes AS "TARGET Processes",
       a.running_processes AS "ACTUAL Processes",
       b.Running_Normal
  FROM apps.fnd_concurrent_queues a,
       (  SELECT c.USER_CONCURRENT_QUEUE_NAME Name,
                 c.CONCURRENT_QUEUE_NAME,
                 SUM (
                    DECODE (d.PHASE_CODE,
                            'P', DECODE (d.STATUS_CODE, 'Q', 1, 0),
                            0))
                    Pending_Standby,
                 SUM (
                    DECODE (d.PHASE_CODE,
                            'P', DECODE (d.STATUS_CODE, 'I', 1, 0),
                            0))
                    Pending_Normal,
                 SUM (
                    DECODE (d.PHASE_CODE,
                            'R', DECODE (d.STATUS_CODE, 'R', 1, 0),
                            0))
                    Running_Normal
            FROM apps.FND_CONCURRENT_QUEUES_VL c, apps.FND_CONCURRENT_WORKER_REQUESTS d
           WHERE     c.concurrent_queue_id = d.concurrent_queue_id
                 AND d.Requested_Start_Date <= SYSDATE
        GROUP BY c.USER_CONCURRENT_QUEUE_NAME, c.CONCURRENT_QUEUE_NAME) b
 WHERE a.CONCURRENT_QUEUE_NAME = b.CONCURRENT_QUEUE_NAME;

How to create restore point in Oracle Database 11g

To create restore point you need to do the below steps.

1. Archive log should be enabled.
2. db_flashback_retention_target need to be set
3. db_recovery_file_dest_size need to be set
4. SHUTDOWN IMMEDIATE;
5. STARTUP MOUNT;
6. FLASHBACK DATABASE TO RESTORE POINT BEFORE_CHANGES;

SQL> show parameter recovery

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest                string

db_recovery_file_dest_size           big integer 300G

recovery_parallelism                 integer     0

SQL> ALTER SYSTEM SET db_recovery_file_dest='+ORCL';

System altered.

SQL>  ALTER SYSTEM SET db_recovery_file_dest='+ORCL' sid='*';

System altered.

SQL> CREATE RESTORE POINT BEFORE_CHANGES GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL>

To restore database need to follow the below steps.

1. SHUTDOWN IMMEDIATE;
2. STARTUP MOUNT;
3. FLASHBACK DATABASE TO RESTORE POINT BEFORE_CHANGES;

Query to get user details with responsibility list in Oracle Apps EBS

select fu.user_name, frt.RESPONSIBILITY_NAME, furg.end_date

from

fnd_user_resp_groups furg,

FND_RESPONSIBILITY fr,

fnd_responsibility_tl frt,

fnd_user fu

where fu.user_id = furg.user_id

and furg.responsibility_id = fr.RESPONSIBILITY_ID

and frt.responsibility_id = fr.RESPONSIBILITY_ID

order by 1;

Above query is to list the user with responsibility in Oracle Apps