How to relocate the redo log files to a different location on disk?

How to relocate the redo log files to a different location on disk?

Solution

A.

1. Shut down the database.

2. Copy the online redo log files to the new location.

Operating system files, such as online redo log members, must be copied using the appropriate operating system commands.

The following example uses operating system commands (UNIX) to move the online redo log members to a new location:

$ mv /diska/logs/log1a.rdo   /diskc/logs/log1c.rdo

$ mv /diska/logs/log2a.rdo   /diskc/logs/log2c.rdo

3. Startup the database in mount mode, but do not open it.

SQL> CONNECT / as SYSDBA 

SQL> STARTUP MOUNT 

4. Rename the online redo log members.

SQL> ALTER DATABASE RENAME FILE '/diska/logs/log1a.rdo', '/diska/logs/log2a.rdo' 

TO '/diskc/logs/log1c.rdo', '/diskc/logs/log2c.rdo'; 

5. Open the database for normal operation.

SQL> ALTER DATABASE OPEN; 

B. If the database cannot be shut down, it will not possible to rename the files directly : first add new groups and then drop the non-needed ones.

1. Add as many new groups as the ones to be renamed with the following commands :

SQL> ALTER DATABASE ADD LOGFILE group 4 ('/log01A.dbf', '/log01B.dbf ') SIZE 512M; 

SQL> ALTER DATABASE ADD LOGFILE group 5 ('/log02A.dbf', '/log02B.dbf ') SIZE 512M;

 SQL> ALTER DATABASE ADD LOGFILE group 6 ('/log03A.dbf', '/log03B.dbf ') SIZE 512M;

2. Then drop the online redo log groups that are not needed anymore: you must have the ALTER DATABASE system privilege. 

Before dropping an online redo log group, consider the following restrictions and precautions: 

2.1 An instance requires at least two groups of online redo log files, regardless of the number of members in the groups. (A group is one or more members.) 

2.2 You can drop an online redo log group only if it is INACTIVE. If you need to drop the current group, first force a log switch to occur. 

Make sure an online redo log group is archived (if archiving is enabled) before dropping it.

To see whether this has happened, use the V$LOG view :

SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG; 

GROUP#    ARC STATUS 

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

1         YES ACTIVE 

2         NO  CURRENT 

3         YES INACTIVE

4         YES UNUSED

5         YES UNUSED

6         YES UNUSED

As group 3 is in INACTIVE mode, you can drop it :

SQL> ALTER DATABASE DROP LOGFILE GROUP 3; 

3. After dropping an online redo log group, make sure that the drop completed successfully, and then use the appropriate operating system command to delete the dropped online redo log files. 

Example of How To Resize the Online Redo Logfiles

Example of How To Resize the Online Redo Logfiles

EXAMPLE OF HOW TO RESIZE THE ONLINE REDO LOGS:
==============================================
Often times the online redo logs are sized too small causing database
performance problems.
The following is an example of how to resize the online log groups:
NOTE:  Examples are given for 9i and higher.   In prior releases, you needed
to use Server Manager and connect as the internal user.
1. First see the size of the current logs:            
             
   > sqlplus /nolog
   SQL> connect / as sysdba

   SQL> select group#, bytes, status from v$log;
   GROUP#     BYTES      STATUS                  
   ---------- ---------- ----------------                    
            1    1048576 INACTIVE                      
            2    1048576 CURRENT                        
            3    1048576 INACTIVE                          
                             
    Logs are 1MB from above, let's size them to 10MB.                              
                                 

2. Retrieve all the log member names for the groups:                                          
                                                   
   SQL> select group#, member from v$logfile;                                  
                                                     
            GROUP# MEMBER                                                        
   --------------- ----------------------------------------                      
                 1 /usr/oracle/dbs/log1PROD.dbf                                  
                 2 /usr/oracle/dbs/log2PROD.dbf                                  
                 3 /usr/oracle/dbs/log3PROD.dbf                                  
                                                             
                                                               
3. In older versions of the database you needed to shutdown and issue the following
   commands in restricted mode.   You can still do this, but the database can be online
   to perform these changes.

   Let's create 3 new log groups and name them groups 4, 5, and 6, each 10MB in
   size:                          
                           
   SQL> alter database add logfile group 4                            
           '/usr/oracle/dbs/log4PROD.dbf' size 10M;                                
                               
   SQL> alter database add logfile group 5                                
           '/usr/oracle/dbs/log5PROD.dbf' size 10M;    
       
   SQL> alter database add logfile group 6        
           '/usr/oracle/dbs/log6PROD.dbf' size 10M;  
       

4. Now run a query to view the v$log status:                                  
                                                                       
   SQL> select group#, status from v$log;                                      

      GROUP# STATUS
   --------- ----------------
           1 INACTIVE
           2 CURRENT
           3 INACTIVE          
           4 UNUSED
           5 UNUSED
           6 UNUSED        
     
   From the above we can see log group 2 is current, and this is one of the
   smaller groups we must drop. Therefore let's switch out of this group into
   one of the newly created log groups.                
                 

5. Switch until we are into log group 4, so we can drop log groups 1, 2, and 3:                                
   
   SQL> alter system switch logfile;        
   ** repeat as necessary until group 4 is CURRENT **
                                                           
                                                           
6. Run the query again to verify the current log group is group 4:                                                          
                                                               
   SQL> select group#, status from v$log;                                      
                                                                 
      GROUP# STATUS
   --------- ----------------
           1 INACTIVE
           2 INACTIVE
           3 INACTIVE          
           4 CURRENT
           5 UNUSED
           6 UNUSED                        

 Note: redo log Group 1 or 2 or 3 can be active after "alter system switch log file" which means could not be dropped, in this case,
you need to do "alter system checkpoint" to make redo log groups 1,2 and 3 inactive.                        
                                                 
7. Now drop redo log groups 1, 2, and 3:    

   SQL> alter database drop logfile group 1;                                  
   SQL> alter database drop logfile group 2;                                  
   SQL> alter database drop logfile group 3;                                  
                                 
   Verify the groups were dropped, and the new groups' sizes are correct.

   SVRMGR> select group#, bytes, status from v$log;

      GROUP#     BYTES STATUS
   --------- --------- ----------------
           4  10485760 CURRENT
           5  10485760 UNUSED
           6  10485760 UNUSED          


 
8.  At this point, you consider taking a backup of the database.

9.  You can now go out to the operating system and delete the files associated
    with redo log groups 1, 2, and 3 in step 2 above as they are no longer
    needed:
   
    % rm /usr/oracle/dbs/log1PROD.dbf
    % rm /usr/oracle/dbs/log2PROD.dbf
    % rm /usr/oracle/dbs/log3PROD.dbf                                                
                                                 
   Monitor the alert.log for the times of redo log switches. Due to increased
   redo log size, the groups should not switch as frequently under the same
   load conditions.


References:
===========

Chapter 6 of the Oracle8i Administrator's Guide, Release 8.1.5,
Part No. A67772-01 for further information on redo log maintenance.

Chapter 7 of the Oracle 9i Administrator's guide
Part No. A96521-01 for further information on redo log maintenance.


Chapter 6 of the Oracle 10g Administrator's guide
Part No. B10739-01 for further information on redo log maintenance.

Chapter 10 of the Oracle 11g Administrator's guide
Part No. B28310-04 for further information on redo log maintenance.

How To Maintain and/or Add Redo Logs

How To Maintain and/or Add Redo Logs

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.2.0.3 - Release: 9.2 to 11.2

Information in this document applies to any platform.

Goal

The purpose of this document is to demonstrate:

A. How to maintain and/or add redo logs.

B. How to determine the optimal size for redo logs



Solution

 A.  How to maintain and/or add redo logs.

1. Review information on existing redo logs.

SELECT a.group#, b.member, a.status, a.bytes
FROM v$log a, v$logfile b
WHERE a.group#=b.group#
2. Add new groups

ALTER DATABASE ADD LOGFILE group 4 ('/log01A.dbf', '/log01B.dbf ') SIZE 512M;
ALTER DATABASE ADD LOGFILE group 5 ('/log02A.dbf', '/log02B.dbf ') SIZE 512M;
ALTER DATABASE ADD LOGFILE group 6 ('/log03A.dbf', '/log03B.dbf ') SIZE 512M;

3. Check the status on all redo logs again.

SELECT a.group#, b.member, a.status, a.bytes
FROM v$log a, v$logfile b
WHERE a.group#=b.group#
4.  Drop the online redo log groups that are not needed.  You must have the ALTER DATABASE system privilege.

Note: Before dropping an online redo log group, consider the following restrictions and precautions:
a.  An instance requires at least two groups of online redo log files, regardless of the number of members in the groups. (A group is one or more members.)

b. You can drop an online redo log group only if it is INACTIVE. If you need to drop the current group, first force a log switch to occur.

By using this command :

ALTER SYSTEM SWITCH LOGFILE;

c. Make sure an online redo log group is archived (if archiving is enabled) before dropping it.  This can be determined by:

GROUP# ARC STATUS
---------   ---     ----------------
1             YES   ACTIVE
2             NO    CURRENT
3             YES   INACTIVE
4             YES   UNUSED
5             YES   UNUSED
6             YES   UNUSED

d.  Check that the group is inactive and archived before dropping it .

SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;


ALTER DATABASE DROP LOGFILE GROUP 3;

e.  After dropping an online redo log group, make sure that the drop completed successfully, and then use the appropriate operating system command to delete the dropped online redo log files.  For more information refer to Note 395062.1


B.  How to determine the optimal size for redo logs

You can use the V$INSTANCE_RECOVERY view column OPTIMAL_LOGFILE_SIZE to determine the size of your online redo logs. This field shows the redo log file size in megabytes that is considered optimal based on the current setting of FAST_START_MTTR_TARGET. If this field consistently shows a value greater than the size of your smallest online log, then you should configure all your online logs
to be at least this size.

Note, however, that the redo log file size affects the MTTR. In some cases, you may be able to
refine your choice of the optimal FAST_START_MTTR_TARGET value by re-running the MTTR Advisor with your suggested optimal log file size.

You can also refer to this Note 1038851.6 - How to Estimate Size of Redo Logs

Please note that there is no column OPTIMAL_LOGFILE_SIZE in
V$INSTANCE_RECOVERY view in 9i. It only applies to 10g.

Analyze all pending requests

----------------------------------------------------------------------------------------
--
--      analyzepending.sql
--      Analyze all pending requests--
----------------------------------------------------------------------------------------


set serveroutput on size 100000
set feedback off
set verify off
set heading off
set timing off


DECLARE

FUNCTION chkwaiting(rid number) return varchar2 AS

parent_id       number(15);

BEGIN

        SELECT nvl(parent_request_id, -1)
               INTO   parent_id
               FROM   fnd_conc_req_summary_v
               WHERE  request_id = rid;
               IF parent_id = -1 THEN
                        return('Waiting, but unable to find a parent request for this request');
               ELSE
                        return('Waiting on parent request: ' || to_char(parent_id));
               END IF;

END chkwaiting;


PROCEDURE manager_check  (req_id        in  number,
                          mgr_defined   out boolean,
                          mgr_active    out boolean,
                          mgr_workshift out boolean,
                          mgr_running   out boolean) is

    cursor mgr_cursor (rid number) is
      select running_processes, max_processes,
             decode(control_code,
                    'T','N',       -- Abort
                    'X','N',       -- Aborted
                    'D','N',       -- Deactivate
                    'E','N',       -- Deactivated
                        'Y') active
        from fnd_concurrent_worker_requests
        where request_id = rid
          and not((queue_application_id = 0)
                  and (concurrent_queue_id in (1,4)));


  begin
    mgr_defined := FALSE;
    mgr_active := FALSE;
    mgr_workshift := FALSE;
    mgr_running := FALSE;

    for mgr_rec in mgr_cursor(req_id) loop
      mgr_defined := TRUE;
      if (mgr_rec.active = 'Y') then
        mgr_active := TRUE;
        if (mgr_rec.max_processes > 0) then
          mgr_workshift := TRUE;
        end if;
        if (mgr_rec.running_processes > 0) then
          mgr_running := TRUE;
        end if;
      end if;
    end loop;

END manager_check;

FUNCTION chknormal(rid number) return varchar2 AS

mgr_defined     boolean;
mgr_active      boolean;
mgr_workshift   boolean;
mgr_running     boolean;

BEGIN

        manager_check(rid, mgr_defined, mgr_active, mgr_workshift, mgr_running);

        IF mgr_defined = FALSE OR mgr_active = FALSE OR mgr_workshift = FALSE OR mgr_running = FALSE THEN
                return('No managers are running that can run this request');
        END IF;

        return('Pending Normal');

END chknormal;

FUNCTION analyzereq(rid number) return varchar2 AS

reqinfo         fnd_concurrent_requests%ROWTYPE;
qcf             fnd_concurrent_programs.queue_control_flag%TYPE;
v_enabled_flag  fnd_concurrent_programs.enabled_flag%TYPE;
conc_app_id     fnd_concurrent_requests.program_application_id%TYPE;
conc_id         fnd_concurrent_requests.concurrent_program_id%TYPE;
conc_cd_id      fnd_concurrent_requests.cd_id%TYPE;
traid           fnd_concurrent_requests.program_application_id%TYPE;
trcpid          fnd_concurrent_requests.concurrent_program_id%TYPE;
ireqid          fnd_concurrent_requests.request_id%TYPE;
pcode           fnd_concurrent_requests.phase_code%TYPE;
scode           fnd_concurrent_requests.status_code%TYPE;
run_alone_flag  varchar2(1);
r               varchar2(100);

CURSOR  c_inc IS
        SELECT to_run_application_id, to_run_concurrent_program_id
        FROM fnd_concurrent_program_serial
        WHERE running_application_id = conc_app_id
        AND running_concurrent_program_id = conc_id;

CURSOR  c_ireqs IS
        SELECT request_id, phase_code, status_code
        FROM   fnd_concurrent_requests
        WHERE  phase_code = 'R'
        AND    program_application_id = traid
        AND    concurrent_program_id = trcpid
        AND    cd_id = conc_cd_id;

BEGIN

        SELECT *
        INTO reqinfo
        FROM fnd_concurrent_requests
        WHERE request_id = rid;

        -- could be a queue control request
        SELECT queue_control_flag
        INTO   qcf
        FROM   fnd_concurrent_programs
        WHERE  concurrent_program_id = reqinfo.concurrent_program_id
        AND    application_id = reqinfo.program_application_id;
             
        IF qcf = 'Y' THEN
                return('Queue control request. Will be run by the ICM on its next sleep cycle');
        END IF;


        -- could be scheduled
        IF reqinfo.requested_start_date > sysdate THEN
                return('Scheduled to run on ' || to_char(reqinfo.requested_start_date, 'DD-MON-RR HH24:MI:SS'));
        END IF;

        -- could be on hold
        IF reqinfo.hold_flag = 'Y' THEN
                return('On hold');
        END IF;

        -- could be disabled
        select enabled_flag into v_enabled_flag
              from fnd_concurrent_programs
              where concurrent_program_id = reqinfo.concurrent_program_id
              and application_id = reqinfo.program_application_id;
        IF v_enabled_flag = 'N' THEN
                return('Concurrent program is disabled');
        END IF;

        -- advanced schedule
        IF reqinfo.status_code = 'P' THEN
                return('Scheduled to be run by the Advanced Scheduler');
        END IF;

        -- check queue_method_code
        IF reqinfo.queue_method_code NOT IN ('I','B') THEN
                return('Bad queue_method_code of: ' || reqinfo.queue_method_code);
        END IF;

        -- waiting status
        IF reqinfo.status_code IN ('A', 'Z') THEN
                return chkwaiting(reqinfo.request_id);
        END IF;

        -- check for runalones
        SELECT runalone_flag
            into run_alone_flag
            from fnd_conflicts_domain d
            where d.cd_id = reqinfo.cd_id;
   
        IF (run_alone_flag = 'Y') THEN
                return('Waiting on a run-alone request');
        END IF;

        -- Normal status
        IF reqinfo.status_code = 'I' THEN
                return chknormal(reqinfo.request_id);
        END IF;

        -- unconstrained requests
        IF reqinfo.queue_method_code = 'I' THEN
                -- bad status
                IF reqinfo.status_code = 'Q' THEN
                        return('Unconstrained Standby request. Will not be run');
                END IF;
               
                return('Odd status of: ' || reqinfo.status_code);

        END IF;

        -- constrained requests
        IF reqinfo.queue_method_code = 'B' THEN

                -- standby, check reasons for waiting
                IF reqinfo.status_code = 'Q' THEN
               
                        -- incompatible programs
                        SELECT program_application_id, concurrent_program_id, cd_id
                        INTO   conc_app_id, conc_id, conc_cd_id
                        FROM   fnd_concurrent_requests
                        WHERE  request_id = reqinfo.request_id;

                        FOR progs in c_inc LOOP

                                traid :=  progs.to_run_application_id;
                                trcpid := progs.to_run_concurrent_program_id;
       
                                OPEN c_ireqs;
                                LOOP

                                        FETCH c_ireqs INTO ireqid, pcode, scode;
                                        EXIT WHEN c_ireqs%NOTFOUND;
                                        return('Waiting on incompatible request ' || ireqid || '  phase=' || pcode || ' status=' || scode);
               
                                END LOOP;
                                CLOSE c_ireqs;

       
                         END LOOP;

                 

                        -- single threaded
                        IF reqinfo.single_thread_flag = 'Y' THEN
                                return('Single-threaded request. Waiting on other requests for this user.');
                        END IF;

                        -- request limit
                        IF reqinfo.request_limit = 'Y' THEN
                                return('Concurrent: Active Request Limit is set. Waiting on other requests for this user.');
                        END IF;

                 
                END IF;

                -- well, could be released, but waiting on a manager
                r := chknormal(reqinfo.request_id);
                IF substr(r, 1) = 'N' THEN
                        return r;
                END IF;

                -- could be just waiting on the CRM
                return('Pending Standby, probably waiting on the CRM');

        END IF;

        -- give up
        return('No idea');

END analyzereq;

PROCEDURE analyzeall AS

cnt  number := 1;

CURSOR c_reqs IS
       SELECT request_id FROM fnd_concurrent_requests
       WHERE phase_code = 'P'
       ORDER BY request_id;

BEGIN
        FOR rid in c_reqs LOOP
                DBMS_OUTPUT.PUT_LINE(cnt || ') ' || rid.request_id || ': ' || analyzereq(rid.request_id));
                cnt := cnt + 1;
        END LOOP;

END analyzeall;





BEGIN
        dbms_output.enable(2000000);

        DBMS_OUTPUT.PUT_LINE('Analyzing all Pending requests');
        DBMS_OUTPUT.PUT_LINE('-----------------------------------------');
        analyzeall;

END;
/

Concurrent Requests status

Select fcrt.USER_CONCURRENT_PROGRAM_NAME
, frt.responsibility_name
, fcr.request_id
, (NVL(fcr.actual_completion_date, sysdate) - fcr.actual_start_date)*24*60 "CURRENT RUNNING TIME(in Mins)"
, (fcr.actual_start_date - fcr.REQUEST_DATE)*24*60 "WAIT TIME(in Mins)"
, (fcr.actual_completion_date - fcr.actual_start_date)*24*60 "RUN TIME(in Mins)"
, fcr.actual_start_date
, fcr.actual_completion_date
, fcr.phase_code
, fcr.status_code
, fcr.completion_text
, fcr.REQUEST_DATE
, fu.USER_NAME
, fcr.PRIORITY
, fcr.REQUESTED_START_DATE
, fcr.NUMBER_OF_COPIES
, fcr.NUMBER_OF_ARGUMENTS
, fcr.PRINT_STYLE
, fcr.REQ_INFORMATION
, fcr.ARGUMENT_TEXT
, fcr.OUTFILE_NAME
, fcr.OFILE_SIZE
, fcr.OUTPUT_FILE_TYPE
, fcr.OUTFILE_NODE_NAME
from fnd_concurrent_requests fcr,
fnd_responsibility_tl frt,
fnd_concurrent_programs_tl fcrt,
fnd_user fu
where fcr.CONCURRENT_PROGRAM_ID = fcrt.CONCURRENT_PROGRAM_ID
and fcrt.LANGUAGE = 'US'
--and fcr.concurrent_program_id IN (7748378,7748233,7747832,77478321)
--And fcr.request_id IN (7748378,7748233,7747832,77478321)
--And fcrt.USER_CONCURRENT_PROGRAM_NAME like ('Belron Batch Pick Slip Report') ,('%Pick%')
--And fcrt.USER_CONCURRENT_PROGRAM_NAME like '%Period close value summary%'
--And fcrt.USER_CONCURRENT_PROGRAM_NAME like 'BELRON - Optimiza Bridge File - Data Files'
--And fcrt.USER_CONCURRENT_PROGRAM_NAME like '%Gather%'
and fcr.responsibility_application_id = frt.application_id
and fcr.REQUESTED_BY = fu.user_id
and frt.language = 'US'
and fcr.responsibility_id = frt.responsibility_id
and fcr.phase_code = 'R'
and fcr.status_code = 'R'
--and fcr.actual_start_date like Sysdate
-- and (upper(frt.responsibility_name) like UPPER('%Laddaw%')
-- OR
-- upper(frt.responsibility_name) like UPPER('%Bardon%'))
--order by request_id DESC
order by (NVL(fcr.actual_completion_date, sysdate) - fcr.actual_start_date)*24*60 desc






Status Code Phase Code
============= ================
CODE MEANING CODE MEANING

A Waiting C Completed
B Resuming I Inactive
C Normal P Pending
D Cancelled R Running
E Error
G Warning
H On Hold
I Normal
M No Manager
P Scheduled
Q Standby
R Normal
S Suspended
T Terminating
U Disabled
W Paused
X Terminated
Z Waiting

Concurrent Requests monitoring

1.How to Determine Which Manager Ran a Specific Concurrent Request?

col USER_CONCURRENT_QUEUE_NAME for a100
select b.USER_CONCURRENT_QUEUE_NAME from fnd_concurrent_processes a,
fnd_concurrent_queues_vl b, fnd_concurrent_requests c
where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID
and a.CONCURRENT_PROCESS_ID = c.controlling_manager
and c.request_id = '&conc_reqid';

2.Concurrent manager status for a given sid?

col MODULE for a20
col OSUSER for a10
col USERNAME for a10
set num 10
col MACHINE for a20
set lines 200
col SCHEMANAME for a10
select s.sid,s.serial#,p.spid os_pid,s.status, s.osuser,s.username, s.MACHINE,s.MODULE, s.SCHEMANAME,
s.action from gv$session s, gv$process p WHERE s.paddr = p.addr and s.sid = '&oracle_sid';


3. Find out request id from Oracle_Process Id:

select REQUEST_ID,ORACLE_PROCESS_ID,OS_PROCESS_Id from apps.fnd_concurrent_requests where ORACLE_PROCESS_ID='&a';

4.To find sid,serial# for a given concurrent request id?

set lines 200
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID
AND a.phase_code = 'R';


5.To find concurrent program name,phase code,status code for a given request id?

SELECT request_id, user_concurrent_program_name, DECODE(phase_code,'C','Completed',phase_code) phase_code, DECODE(status_code,'D', 'Cancelled' ,
'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X', 'Terminated', 'C', 'Normal', status_code) status_code, to_char(actual_start_date,'dd-mon-yy:hh24:mi:ss') Start_Date, to_char(actual_completion_date,'dd-mon-yy:hh24:mi:ss'), completion_text FROM apps.fnd_conc_req_summary_v WHERE request_id = '&req_id' ORDER BY 6 DESC;


6.To find the sql query for a given concurrent request sid?

select sid,sql_text from gv$session ses, gv$sqlarea sql where
ses.sql_hash_value = sql.hash_value(+) and ses.sql_address = sql.address(+) and ses.sid='&oracle_sid'
/

7. To find child requests

set lines 200
col USER_CONCURRENT_PROGRAM_NAME for a40
col PHASE_CODE for a10
col STATUS_CODE for a10
col COMPLETION_TEXT for a20

SELECT sum.request_id,req.PARENT_REQUEST_ID,sum.user_concurrent_program_name, DECODE(sum.phase_code,'C','Completed',sum.phase_code) phase_code, DECODE(sum.status_code,'D', 'Cancelled' ,
'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X', 'Terminated', 'C', 'Normal', sum.status_code) status_code, sum.actual_start_date, sum.actual_completion_date, sum.completion_text FROM apps.fnd_conc_req_summary_v sum, apps.fnd_concurrent_requests req where req.request_id=sum.request_id and req.PARENT_REQUEST_ID = '&parent_concurrent_request_id';


8. Cancelling Concurrent request :

update fnd_concurrent_requests
set status_code='D', phase_code='C'
where request_id=&req_id;

9. Kill sessions program wise

select 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' immediate;' from v$session where MODULE like '';


10 .Concurrent Request running by SID

SELECT a.request_id,
d.sid as Oracle_SID,
d.serial#,
d.osuser,
d.process,
c.SPID as OS_Process_ID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND d.sid = &SID;

11. Find out request id from Oracle_Process Id:

select REQUEST_ID,ORACLE_PROCESS_ID,OS_PROCESS_Id from fnd_concurrent_requests where ORACLE_PROCESS_ID='&a';


12. Oracle Concurrent Request Error Script (requests which were error ed out)

SELECT a.request_id "Req Id"
,a.phase_code,a.status_code
, actual_start_date
, actual_completion_date
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name "program"
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND a.status_code = 'E'
AND a.phase_code = 'C'
AND actual_start_date > sysdate - 2
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND ctl.LANGUAGE = 'US'
ORDER BY 5 DESC;


13. Request submitted by User

SELECT
user_concurrent_program_name,
request_date,
request_id,
phase_code,
status_code
FROM
fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcp,
fnd_responsibility_tl fr,
fnd_user fu
WHERE
fcr.CONCURRENT_PROGRAM_ID = fcp.concurrent_program_id
and fcr.responsibility_id = fr.responsibility_id
and fcr.requested_by = fu.user_id
and user_name = '&user'
AND actual_start_date > sysdate - 1
ORDER BY REQUEST_DATE Asc;



14.Concurrent Program enable with trace

col User_Program_Name for a40
col Last_Updated_By for a30
col DESCRIPTION for a30
SELECT A.CONCURRENT_PROGRAM_NAME "Program_Name",
SUBSTR(A.USER_CONCURRENT_PROGRAM_NAME,1,40) "User_Program_Name",
SUBSTR(B.USER_NAME,1,15) "Last_Updated_By",
SUBSTR(B.DESCRIPTION,1,25) DESCRIPTION
FROM APPS.FND_CONCURRENT_PROGRAMS_VL A, APPLSYS.FND_USER B
WHERE A.ENABLE_TRACE='Y'
AND A.LAST_UPDATED_BY=B.USER_ID

Concurrent Processing - Concurrent Manager Generic Platform Questions and Answers

Concurrent Processing - Concurrent Manager Generic Platform Questions and Answers

FND Concurrent Manager issues.
Questions and Answers

When would one be required to bounce (stop and restart) the Concurrent Manager?

When one modifies the Printer Driver,the Manager which runs the request
which is attached to that Printer Driver should be re-started, however,if it is
not known,simply restart the Internal manager because the printer driver
can be used by multiple managers and multiple requests.
If only a concurrent program definition is modified, running a verify on the
Internal Manager will pick up the changes without the need for bouncing the
manager.


Does the Internal manager schedule requests to be run or does it put requests into queues to be run by other managers?

This is a very common misconception. The ICM really does not have any
such scheduling responsibilities. It has NOTHING to do with scheduling
requests, or deciding which manager will run a particular request.
Its function is only to run 'queue control' requests, which are
requests to startup or shutdown other managers. It is responsible for
startup and shutdown of the whole concurrent processing facility, and
it also monitors the other managers periodically, and restarts them if
they should go down. It can also take over the Conflict Resolution
manager's job, and resolve incompatibilities.


If the ICM itself should go down, requests will continue to run
normally, except for 'queue control' requests. One can restart it with
'startmgr', and do not need to kill the other managers first.


How to check to see if a concurrent manager is running?

One way to see if a manager is running is to use the 'Administer
Concurrent Managers' form. Navigate to Concurrent->Managers->Administer.
One will see two columns labeled 'Actual' and 'Target'. The Target column
lists the number of processes that should be running for each manager
for this particular workshift. The Actual column lists the number of
processes that are actually running. If the Actual column is zero, there
are no processes running for this manager. If the Target column is zero,
then either a workshift has not been assigned to this manager, or the current
workshift does not specify any target processes. If the target column
is not zero, then the manager processes have either failed to start up,
or gone down. One should check the manager's logfile and the ICM
logfile. One can also search for OS processes using the 'ps' command.
It is possible for the form to be inaccurate, i.e. it may show actual
processes even though they are not really running. When in doubt, check for
processes at the OS level.


Where do concurrent request or manager logfiles and output files go?

The concurrent manager first looks for the environment variable
$APPLCSF. If this is set, it creates a path using two other
environment variables: $APPLLOG and $APPLOUT
It places log files in $APPLCSF/$APPLLOG, output files go in
$APPLCSF/$APPLOUT

So for example, if one has this environment set:
$APPLCSF = /u01/appl/common
$APPLLOG = log
$APPLOUT = out

The concurrent manager will place log files in /u01/appl/common/log,
and output files in /u01/appl/common/out.
Note that $APPLCSF must be a full, absolute path, and the other two
are directory names.

If $APPLCSF is not set, it places the files under the product top of
the application associated with the request. For example, a PO report
would go under $PO_TOP/$APPLLOG and $PO_TOP/$APPLOUT
Logfiles go to: /u01/appl/po/9.0/log
Output files to: /u01/appl/po/9.0/out
All these directories must exist and have the correct permissions.

Note that all concurrent requests produce a log file, but not necessarily
an output file.
Concurrent manager logfiles follow the same convention, and will be
found in the $APPLLOG directory


What are the logfile and output file naming conventions?

Request logfiles: l.req


Output files: If $APPCPNAM is not set:.
If $APPCPNAM = REQID: o.out
If $APPCPNAM = USER: .out


Where: = The request id of the concurrent request
And: = The id of the user that submitted the request


Manager logfiles:


ICM logfile: Default is std.mgr, can be changed with the mgrname
startup parameter
Concurrent manager log: w.mgr
Transaction manager log: t.mgr
Conflict Resolution manager log: c.mgr


Where: is the concurrent process id of the manager


Can one delete a concurrent manager?

One can disable the manager by checking the 'Enabled' checkbox, or
simply Terminate the manager and it will not run again unless one
reactivates it.
If it is really necessary, one can query the manager in the
'Define Manager' form, and delete the row. (It is recommended that one
DOES NOT do this.)
What is the function of the 'Conflict Resolution Manager'?

Concurrent managers read requests to start concurrent programs running. The
Conflict Resolution Manager checks concurrent program definitions for
incompatibility rules.

If a program is identified as Run Alone, then the Conflict Resolution Manager
prevents the concurrent managers from starting other programs in the same
conflict domain.

When a program lists other programs as being incompatible with it, the
Conflict Resolution Manager prevents the program from starting until any
incompatible programs in the same domain have completed running.

What is the 'Internal Scheduler/Prereleaser' manager?

The short name for this manager is FNDSCH. It is also known as the
Advanced Scheduler/Prereleaser Manager. This manager is intended
to implement Advanced Schedules. Its job is to determine when a
scheduled request is ready to run. Advanced Schedules were not fully
implemented in Release 11.0, they are implemented in Release 11.5,
but are not widely used by the various Apps products. General Ledger
uses FNDSCH for financial schedules based on different calendars and
period types. It is then possible to schedule AutoAllocation sets,
Recurring Journals, MassAllocations, Budget Formulas, and MassBudgets
to run according to the General Ledger schedules that have been
defined.

If financial schedules in GL are not being used then it is not a
problem to deactivate this manager.


What is the 'Internal Monitor' manager/service?

This manager/service is used to implement Distributed Concurrent Processing.
It monitors whether the ICM is still running, and if the ICM crashes,
it will restart it on another node.
One does not need to run this manager/service unless one is using Distributed
Concurrent Processing.


See the Installation manual and Sysadmin Guide for more info on DCP.


How does one check/set the PMON method?

To check the PMON method:
1) cd $FND_TOP/sql
2) sqlplus apps/ @afimchk.sql
This will tell whether the internal manager is running, what the PMON
method is, and where the log file is.


To set the PMON method:
1) First shut the concurrent managers down
2) cd $FND_TOP/sql
3) sqlplus apps/ @afimpmon.sql LOCK (or RDBMS)



How does one enable/disable the Conflict Resolution Manager?

Use the system profile option 'Concurrent: Use ICM'.
Setting this to 'No' (which is the default) allows the CRM to be started.
Setting it to 'Yes' causes the CRM to be shutdown and the Internal
Manager (ICM) will take over the conflict resolution duties. If the CRM will
not start (it is started automatically by the ICM), check this profile option.


Note that using the ICM to resolve conflicts is not recommended.
The CRM's sole purpose is to resolve conflicts, while the ICM has
other functions to perform as well. Only set this option to 'YES'
if you have a good reason to do so.


How does one clean out the Concurrent Manager tables?

Cleaning out the tables is a useful method of making sure that there
are no invalid statuses that can prevent the managers from starting.
Previously, this has been done by truncating fnd_concurrent_processes
and/or fnd_concurrent_requests. Truncation of the tables is a little
drastic and not supported, and can cause problems later when trying to purge requests,
not to mention losing all of the request information.


Run the script, cmclean.sql, article Note 134007.1 CMCLEAN.SQL - Non
Destructive Script to Clean Concurrent Manager Tables
It will make sure the relevant status codes are valid without
deleting any information.


How does one tell concurrent manager processes apart at the OS level?

Use:
pf -ef | grep FNDLIBR



This will produce output like:
vd11 13703 13660 0 May 11 ? 0:01 FNDLIBR FND Concurrent_Processor
MANAGE OLOGIN="APPS/94A491A1000000000000000000
n1070161 24936 24927 0 Apr 29 ? 0:05 FNDLIBR FND Concurrent_Processor
MANAGE OLOGIN="APPS_APPDEMO/94C4B1C10000000000
n1070161 24938 24927 0 Apr 29 ? 0:06 FNDLIBR FND Concurrent_Processor
MANAGE OLOGIN="APPS_APPDEMO/94C4B1C10000000000
n1070161 24927 24922 0 Apr 29 ? 2:03 FNDLIBR FND CPMGR FNDCPMBR sysmgr
="" sleep=60 pmon=20 diag=N logfile=/u16/app

The last process, #24927, shows 'FNDLIBR FND CPMGR', this one is the
Internal Manager (ICM). Notice that it gives some of the parameters it
was started with, the other processes showing 'Concurrent_Processor'
are Standard manager processes. Notice that the ICM process is the
parent process of the Standard managers. (processes 24936 and 24938)

Other managers will have the name of the executable, like ARLIBR or
INVLIBR:
$ ps -ef | grep ARLIBR
vd11 13683 13660 0 May 11 ? 0:20 ARLIBR APPS/82A2A4940000000000000
000000000000000000000000000000000000000 AR ART



The Conflict Resolution manager will look like:
$ ps -ef | grep FNDCRM
n1070161 24941 24927 0 Apr 29 ? 1:17 FNDCRM APPS_APPDEMO/84BFBEB900000
0000000000000000000000000000000000000000000000


Why is one seeing pinging entries like this in the ICM logfile?

PING (0.0.0.0): 56 data bytes
64 bytes from nnn.nn.nn.n: icmp_seq=0 ttl=255 time=0.705 ms
64 bytes from nnn.nn.nn.n: icmp_seq=1 ttl=255 time=1.120 ms
Process monitor session ended : 29-FEB-2000 10:38:43
64 bytes from nnn.nn.nn.n: icmp_seq=2 ttl=255 time=0.985 ms
64 bytes from nnn.nn.nn.n: icmp_seq=3 ttl=255 time=1.006 ms



Pinging other machines is used in Distributed Concurrent Processing.
This means you have DCP turned on, using the environment variable
APPLDCP. Set APPLDCP to OFF and restart the managers.


The Restart button was used to start the Standard manager, but it still did not start?

Telling a manager to restart just sets the status to Restart. The ICM
will start it the next process monitor session or the next time the
ICM starts. Use Activate to start a manager immediately.
When a manager is deactivated manually, the ICM will not restart
it, one will need to set it to Restart, or activate it manually.


How many rows are in FND_CONCURRENT_REQUESTS and FND_CONCURRENT_PROCESSES tables?

Depending on the specification of the system it has been seen that when
tables reach above 3000-4000 rows, the performance begins to diminish, however,
there could be 30000-40000 rows in the tale before the performance begins
to degrade.

One may want to run the Purge Concurrent Request and/or Manager Data on
a regular basis, dependent on the amount of requests being run.

The Purge Concurrent Requests job can be used to purge:
Requests, Mgr logs, and All requests depending on what is chosen.

Use the following options: Enter = All, Mode = AGE, Mode Value = 15

The std.mgr log continuously grows where it may good to
archive it regularly.


Any processes pending in Internal or Conflict Resolution Manager?

Best course of action before starting the Concurrent Managers is to cancel
any "Deactivate" or "Verify" jobs pending in the Internal Manager and place
any other pending jobs on hold.


How does one turn on transaction manager diagnostics?

Set the profile option 'Concurrent:Debug Flags' to 'TCTM1' at the site
level. This will cause transactions to make debug entries in the
FND_CONCURRENT_DEBUG_INFO table. Truncate this table before running a
transaction, then select the entries from the table.
Starting the managers with diag=Y will also produce more information
in the transaction manager logfile.


How do transaction managers work?

Briefly:
(See the server documentation for details on the DBMS_PIPE package)
1) A tranasction manager is started on the concurrent processing
server, and periodically reads the pipe for incoming transactions.
2) A client program (usually a form) calls the
FND_TRANSACTION.SYNCHRONOUS function.
3) This function writes a message into the pipe containing the program
to be run and its parameters.
4) FND_TRANSACTION.SYNCHRONOUS begins reading a return pipe for the
return status.
5) The manager sees the message in the pipe, retrieves the program id
and parameters.
6) The manager runs the program with the specified parameters. The
program will be of type 'Immediate', so there will not be a
separate concurrent request run.
7) The program completes, and the manager packs its return status into
the return pipe.
8) FND_TRANSACTION.SYNCHRONOUS reads the return value and passes it
back to its caller.


Note that these events take place essentially simultaneously on the
client and server. This is a synchronous transaction because the
client waits for the server to return, or times out waiting for it.


Inactive / Nomanager, but request completes.

When one tries to submit a request like Active users or
Active responsibilities, request gets submitted.

When viewing the help requests, one finds that it is
inactive / nomanager.

Within 12 to 15 seconds, refresh-it and is completed.

Initially, one could find only inactive and we look at
the diagnostic- the concurrent manager assigned is not
picking up.

There is no specialization rules in any managers except
the include program this source.

Most often when this occurs where a request goes
"inactive/no manager" and is then processed a short time
later, the solution is to either increase the cache size
for your Standard manger, or increase the actual number of
Standard manager processes.

Cache Size is set on the CONCURRENT/MANAGER/DEFINE form. Basically,
this regulates how many requests a manager will pick up for each
sleep cycle.

How does one process more concurrent requests concurrently?

The Concurrent Manager parameters, (Query the concurrent manager by
Login as Sysadmin, navigate -> Concurrent -> Manager -> Define and Query for
the relevant concurrent manager), should be modified to handle more
concurrent requests concurrently, this can be done in two steps:

(i) Increase the Number of Target processes for the manager

(ii) Change the cache size of the concurrent manager as this determines
how many requests will be evaluated by a manager at a time and should match the target (process) value as set above.


What does Active and Enabled statuses mean for a Concurrent Manager?
·If a concurrent manager is Active, that means that it is able to process concurrent requests.
·If a concurrent manager is Enabled, that means that it can be started and used to process concurrent requests.

How To Change Font Type And Size Of Oracle Application Forms

 How To Change Font Type And Size Of Oracle Application Forms 

Applies to:

Oracle Applications Technology Stack - Version: 11.5.10.2 to 11.5.10.2 - Release: 11.5 to 11.5

Information in this document applies to any platform.

Goal

Is there a way to change the 6i Forms Font Type and Size in 11i Oracle Applications?

Solution

1. Oracle 11i Applications does not support the changing of the 6i Forms fonts.

2. We do support the standard windows functionality of Large and Small (standard) fonts in 11i Forms as part of our accessibility support. This is set through the windows control panel. Large fonts runs with a clientDPI of 120, Small fonts with a clientDPI of 96. If using Large fonts, everything in the Forms UI is bigger, so a window that just fitted with standard fonts may be too big to see everything all at once, since making the fonts bigger also makes the window bigger. So there is a trade off.

Before starting the Oracle 11i Applications, set the Font size on the Desktop/PC. The default/standard Font size on most Desktop/PC's is set to Small. To change the font size to Large on a Windows 2000 Desktop, open the Control Panel on the Desktop, then navigate to: Display > Settings > Advanced > General. Use the drop down to change the Font Size to 'Large Fonts'. (note: If the Large font is not installed on the PC, you will need the OS installation CD to install the Large fonts)

Apply the changes

You may want to play around with the Display settings to find the best setting for your monitor. (Control Panel > Display > Settings > Screen Area 1024x768, 1280x1024, ...)

3. As a side note:

Do not update the windowsDPI in the fnd_top/resource/appsweb.cfg file. We do not support altering the clientDPI through the Forms configuration files. With standard windows dots per logical inch (DPI), the form layout is designed to work with

the available fonts on a particular client platform. Once you start changing the DPI, the font point size chosen as the best fit may not work and font clipping can occur.

ORA-00450, ORA-00443, background process "CJQ0" did not start On : 11.g

ORA-00450, ORA-00443, background process "CJQ0" did not start On : 11.2.0.3 version, RDBMS

When attempting to shutdown the database the following error occurs.

ORA-450: background process 'CJQ0' did not start
ORA-443: background process "CJQ0" did not start


The issue can be reproduced at will by shutdown the instance.

Cause


This problem is as described in
unpublished Bug 6865966 PMON TRYING TO START CJQ0 DURING DATABASE SHUTDOWN-ORA-443 & ORA-450
where development explained that the errors are misleading but are unharmful.


Solution

This is caused by scheduler autostart feature trying to start up the  coordinator at the same time as database is shutting down. So you can safely ignore the errors.

The unpublished Bug 6865966 is still under investigation and you can apply the patch, once the fix is made available.

The Autostart feature will not be available in 12c and hence this issue wont be present in 12c version.


References

@ BUG:6865966 - PMON TRYING TO START CJQ0 DURING DATABASE SHUTDOWN-ORA-443 & ORA-450

ORA-00450, ORA-00443, background process "CJQ0" did not start On : 11.g

ORA-00450, ORA-00443, background process "CJQ0" did not start On : 11.2.0.3 version, RDBMS

When attempting to shutdown the database the following error occurs.

ORA-450: background process 'CJQ0' did not start
ORA-443: background process "CJQ0" did not start


The issue can be reproduced at will by shutdown the instance.

Cause


This problem is as described in
unpublished Bug 6865966 PMON TRYING TO START CJQ0 DURING DATABASE SHUTDOWN-ORA-443 & ORA-450
where development explained that the errors are misleading but are unharmful.


Solution

This is caused by scheduler autostart feature trying to start up the  coordinator at the same time as database is shutting down. So you can safely ignore the errors.

The unpublished Bug 6865966 is still under investigation and you can apply the patch, once the fix is made available.

The Autostart feature will not be available in 12c and hence this issue wont be present in 12c version.


References

@ BUG:6865966 - PMON TRYING TO START CJQ0 DURING DATABASE SHUTDOWN-ORA-443 & ORA-450

What is the database initialization parameter that is associated to an ORA-32004 error ?

What is the database initialization parameter that is associated to an ORA-32004 error ?


Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.2.0.3 - Release: 9.2 to 11.2
Information in this document applies to any platform.
***Checked for relevance on 24-Jan-2012***
Goal

How to know which parameter is obsolete and/or deprecated when the only message on screen when
attempting to start a database is "ORA-32004: obsolete and/or deprecated parameter(s) specified" ?

Solution

1.

Look at the DBA's alert log of the associated instance as newer versions of the RDBMS software will identify the parameters that are obsolete and/or deprecated within the alert log at the time that an instance startup is initiated.

2.

If the alert log is not available or does not identify the obsolete or deprecated parameter and the database is accessible then check to see that none of the parameters in the parameter initialization file match a parameter name stored in the V$OBSOLETE_PARAMETER view of that same database.
Especially review any parameter where V$OBSOLETE_PARAMETER.ISSPECIFIED='TRUE'.

If there is a match, then it is an obsolete parameter.

3.

If the alert log is not available or does not identify the obsolete or deprecated parameter then check to see that none of the parameters in the parameter initialization file match a parameter name listed within the migration guide's list of deprecated parameters or it's list of obsolete parameters.

Vi: Search and Replace

Vi: Search and Replace

Change to normal mode with .

Search (Wraped around at end of file):

  Search STRING forward :   / STRING.
  Search STRING backward:   ? STRING.

  Repeat search:   n
  Repeat search in opposite direction:  N  (SHIFT-n)

Replace: Same as with sed, Replace OLD with NEW:

 
 First occurrence on current line:      :s/OLD/NEW
  
 Globally (all) on current line:        :s/OLD/NEW/g 

 Between two lines #,#:                 :#,#s/OLD/NEW/g
  
 Every occurrence in file:              :%s/OLD/NEW/g 

R12: Contact Center: Package CSC_ACTION_ASSEMBLER_PVT_W Invalid

Solution

To compile the CSC_ACTION_ASSEMBLER_PVT_W package and body manually,
run these statements in SQLplus:

SQL> alter session set "_disable_fast_validate"=TRUE;
SQL> alter package CSC_ACTION_ASSEMBLER_PVT compile;
SQL> alter package CSC_ACTION_ASSEMBLER_PVT compile body;

SQL> alter package CSC_ACTION_ASSEMBLER_PVT_W compile;
SQL> alter package CSC_ACTION_ASSEMBLER_PVT_W compile body;



The "_disable_fast_validate" parameter should be set to true during maintenance operations.
See this Note for further explanation:

Note.1058763.1 Ext/Pub Interoperability Notes EBS R12 with Database 11gR2

How To Resolve RLM Invalids R12.1.3 upgrade

          How To Resolve RLM Invalids

On 12.1.3 need to resolve the following invalid objects:
RLM_EXTINTERFACE_SV
RLM_RD_SV
RLM_MANAGE_DEMAND_SV

Fix

Please run the following for the 3 RLM invalid objects:
SQL>
RLM_MANAGE_DEMAND_SV PACKAGE
RLM_RD_SV PACKAG BODY
RLM_EXTINTERFACE_SV PACKAGE BODY

select object_name,object_type from from dba_objects
where object_name ='RLM_MANAGE_DEMAND_SV';

alter package RLM_MANAGE_DEMAND_SV compile;

select object_name,object_type from from dba_objects
where object_name ='RLM_RD_SV PACKAG';

alter package RLM_RD_SV;

select object_name,object_type from from dba_objects
where object_name ='RLM_EXTINTERFACE_SV PACKAGE';

alter package RLM_EXTINTERFACE_SV PACKAGE compile;

ASO object in my 12.1.3 environment

How can I successfully compile the invalid ASO object in my 12.1.3 environment? ASO_OPP_QTE_PUB ASO_QUOTE_HEADERS_PVT  ASO_QUOTE_PUB_W  ASO_SECURITY_INT  Please do the following: 1. Compile each of the objects using the package source files. First compile the package SPEC asoisecs.pls, then the package BODY: For example: @asoisecs.pls @asoisecb.pls @asovqw1s.pls @asovqw1b.pls @asopopqs.pls @asopopqb.pls @asovqhds.pls @asovqhdb.pls 2. Confirm these ASO packages have compiled successfully. select object_name, object_type, status from dba_objects where object_type like '%PACKAGE%' and object_name like '%ASO%'; 3. If any ASO invalids remain, please upload the appscheck as follows from Order Management Log into your Order Management responsibility View->Requests->Submit a request->Single Request->Diagnostics Appscheck ----> Application Parameters Quoting (QOT) Order Capture (ASO)

R12.1.1 Invalid Objects After Patching - FRM-18108: Failed to load the following objects

R12.1.1 Invalid Objects After Patching - FRM-18108: Failed to load the following objects

When attempting to prepare a new instance on R12.1.1  for Order Management, receive invalid objects that will not regenerate.


ERROR
-----------------------
FRM-18108: Failed to load the following objects.

Source Module:OEXOEREL.fmb
Source Object: RELATED_ITEMS_PROMPT
Source Module:OEXOEREL.fmb
Source Object: RELATED_ITEMS
Source Module:OEXOEREL.fmb
Source Object: RELATED_ITEMS_CONTROL
Source Module:OEXOEREL.fmb
Source Object: RELATED_ITEMS_FOLDER
Source Module:OEXOEREL.fmb
Source Object: RELATED_ITEMS_FIXED
Source Module:OEXOEREL.fmb

In file aderrchk.lst
See the following error:
JAVA CLASS 0
oracle/apps/fnd/common/ias/JservA
dminHelper
ORA-29552: verification warning:
java.lang.UnsupportedClassVersionError:
oracle/apps/fnd/common/ias/JservAdminHelper (Unsupported
major.minor version 49.0)
(repeated for more JAVA messages)
...
PACKAGE BODY 0
BPEL_UPDATEORDEREBS11I10TOCOMS
PLS-00907: cannot load library unit APPS.OE_ACKNOWLEDGMENT_PUB
(referenced by APPS.OE_OUTBOUND_INT)

ran adadmin for ONT applications; below objects where not  compiled:
 au resource OESCHORG.pll
 au resource OEXOEFRM.pll
 au resource OEXOELIB.pll
 au resource OEXOELIN.pll
 ont forms/US OESCHORG.fmx
 ont forms/US OEXOEORD.fmx
 ont forms/US OEXOETEL.fmx
 ont forms/US OEXOERLI.fmx
 ont forms/US OEXOEVER.fmx

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1.Refresh from production ( 0 invalid objects)
2. Apply about 50 patches
3. Error in OM files

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot access forms to complete setup and create sales orders.

Changes

Create a new R12.1.1 instance.
Cause

Many invalid OM objects after creating and patching a new instance.

The following bug was created for this issue:
OM Bug  9655621 - R12.1.1 - OE INVALID OBJECTS AFTER APPLING 51 PATCHES (NON OM)


Solution


To implement the solution, please execute the following steps:
1.
a) restart the database
b) run utlirp/utlrp as sys as instructed in the base bug.
    $ORACLE_HOME/rdbms/admin/utlirp.sql
    $ORACLE_HOME/rdbms/admin/utlrp.sql
c) if apps objects are still invalid after this then run the relevant
Apps script(s) to recompile.
d) once all objects in the database are valid run the following select
to ensure there are no timestamp mismatches remaining:

alter session set nls_date_format='dd-mon-yy hh24:mi:ss';

select do.name dname, po.name pname, d.p_timestamp, po.stime p_stime
from sys.obj$ do, sys.dependency$ d, sys.obj$ po
where d.P_OBJ#=po.obj#(+)
and d.D_OBJ#=do.obj#
and do.status=1 /*dependent is valid*/
and po.status=1 /*parent is valid*/
and po.stime!=d.p_timestamp /*parent timestamp not match*/
and do.type# not in (28,29,30) /*dependent type is not java*/
and po.type# not in (28,29,30) /*parent type is not java*/
order by 2,1;

If issues performing step 1, create a new service request with DataBase product,
component PL/SQL.

2. After following the above instruction, if all the Database packages are valid,
then try recompiling the pll and forms

How to Manually Kill the Correct Database Session For a Hanging Forms Runtime Process

How to Manually Kill the Correct Database Session For a Hanging Forms Runtime Process


Problem Description
-------------------

You are trying to kill an operating system process ID (PID) for a specific
Oracle job, but you are unsure of the exact process number to kill.  You asked
how to find the desired PID number.

Solution Description
--------------------

Run the following select statement to find the desired process ID (PID) and
username:

   select spid, sid, a.serial#, b.username
   from v$session a, v$process b
   where a.paddr = b.addr;

Following is an example of what the query returns:

  SPID    SID     SERIAL#     USERNAME
  ----    ---     -------     --------
  11526     1        1        absmith
  11547     3        1        absmith
  11551     6        1        absmith
 

Explanation
-----------

The SPID is the actual operating system process identifier.

The SID is Oracle's session identifier.

The SERIAL# is Oracle's session serial number used to identify a session's
objects.

After the operating system PID has been killed, type the following (it is
faster than waiting for PMON to clean up the terminated process):

     alter system kill session 'sid, serial#';

orms Process (FRMWEB) Consumes 100% of CPU in Oracle Applications R12

Forms Process (FRMWEB) Consumes 100% of CPU in Oracle Applications R12

On Oracle Applications R12 when checking the top processes on the OS level for the middle tier, you find that forms process (frmweb) almost consumes 100% of the CPU.
Cause

The root cause of the issue is that returning rows from LOVs in core forms causes the forms process to grow up into memory depending on the number of rows returned.

When an end user login to forms and start working with LOV within core forms sometimes and according to the search criteria that the user will provide to filter the results in LOV, it may fetch huge numbers of records in which causes the frmweb process to grow very large, and in extreme cases this can even lock up the current process or even the whole machine.

So when executing a LOV query, every row is fetched into memory on the middle tier, the frmweb process can get extremely large, and the larger it gets the more likely it is to start paging.
Eventually it starts consuming excessive CPU just paging the process in and out of memory, which is probably what you can see here in this case as the amount of memory consumed when the LOV records are fetched into memory obviously depends on the amount of data in each record.

This has been mentioned in the following bug:
Bug 6519700 - ESC: CSE: R12SIP: 6513826 FRMWEB RUNAWAY PROCESS CONSUMING 100% CPU-MIDDLE TIER

Solution

To implement the solution, please execute the following steps:

1. Stop all services on the middle tier.

2. Set following forms environment variables:

FORMS_RECORD_GROUP_MAX to 10000 or if that proves too restrictive, increase it to 20000 or 30000.
FORMS_CATCHTERM=0

In order to set the above forms variables so next time autoconfig run does not override those values, do the following steps :

1- For Forms Variable "FORMS_CATCHTERM" the context vairable name is: "s_forms_catchterm" and you can update the context file located in ($INST_TOP/appl/admin/) 

2- For other forms variable "FORMS_RECORD_GROUP_MAX" there is no variable defined in Autoconfig for that one and have to customize the autoconfig for the forms variables to set that environment as following:
a- Go to the autoconfig Template folder:
$cd $AD_TOP/admin/template
b- Create new directory named (custom)
$ mkdir custom
c- Make sure that new directory has same file permissions as ($AD_TOP/admin/template)
d- Copy the following autoconfig template to the new custom directory:
$cp $AD_TOP/admin/template/APPLSYS_ux.env $AD_TOP/admin/template/custom/APPLSYS_ux.env
e- Edit the file copied file under custom directory and add the following 2 lines at the end of section:

####################################
# Oracle Forms environment variables
####################################

FORMS_RECORD_GROUP_MAX=10000
export FORMS_RECORD_GROUP_MAX

f- Save and exit from the file.
g- Next time autoconfig run, it will read the custom directory and check for any customizations there.
3. Run Autoconfig on the middle tier and make sure it is completed successfully.

4. Startup all services.

5. Monitor the forms process to see its CPU usage, and you will see that form process usage is reduced and not causing any more CPU consumption up to 100% as before.

6. Migrate the solution as appropriate to other environments.

IMPORTANT NOTE: If you find that setting (FORMS_RECORD_GROUP_MAX=30000) still too restrictive for certain LOV's then Oracle product team needs to consider redesigning the LOV's in their form and at that time raise a new SR with the Oracle Support team so they can raise a new bug with development for that specific LOV to get it redesigned but this may happen ONLY in RARE cases.

How To Select Number of Workers Based on Number of CPUs When Running ADPATCH

How To Select Number of Workers Based on Number of CPUs When Running ADPATCH


Application Install - Version: 11.5.10.2 to 11.5.10.2 - Release: 11.5 to 11.5
Generic UNIX
Checked for relevance on 11-MAY-2010
Goal

How to select number of workers based on number of processors when running adpatch

Solution

Select 2x the number of processors (CPUs) on the instance to be patched.

Ex. Machine has 4 processors, select up to 8 workers when running adpatch.

Finding alert.log file in 11g

  Finding alert.log file in 11g [ID 438148.1]


Beginning with Release 11g of Oracle Database, the alert log is written as both an XML-formatted file and as a text file, as in earlier releases.Both these log files are stored inside the ADR home.The ADR root directory is known as ADR BASE.The Automatic Diagnostic Repository (ADR) is a directory structure that is stored outside of the database.This parameter is set by DIAGNOSTIC_DEST initialization parameter.

If this parameter is omitted or left null, the database sets DIAGNOSTIC_DEST upon startup as follows:

If environment variable ORACLE_BASE is set, DIAGNOSTIC_DEST is set to the directory designated by ORACLE_BASE.
If environment variable ORACLE_BASE is not set, DIAGNOSTIC_DEST is set to ORACLE_HOME/log.
for e.g

SQL> show parameter diagno

NAME                          TYPE          VALUE
--------------------------- ----------- ------------------------------
diagnostic_dest             string      /u01/oracle/product/ora11g/log
The location of an ADR home is given by the following path, which starts at the ADR base directory:

/diag/// 

For example,

 for a database with a SID and database name both equal to ora11g, the ADR home would be in the following location:

/diag/rdbms/ora11g/ora11g/

Within the ADR home directory are subdirectories where the database instance stores diagnostic data.

Subdirectory Name Contents
alert The XML-formatted alert log
trace Background and server process trace files and SQL trace files and text alert.log file
cdump Core files


XML formatted alert.log
-------------------------
The alert log is named log.xml and is stored in the alert subdirectory of ADR home.

To get the log.xml path

ADR_BASE/diag/product_type/product_id/instance_id/alert

from sqlplus

SQL> select value from v$diag_info where name ='Diag Alert';

ADRCI utility to view a text version of the alert log (with XML tags stripped)

Text formatted alert.log
-----------------------

The alert.log is named alertSID.log and is stored in the trace subdirectory of ADR home.


To view the text only alert.log file

/diag////trace 

from sqlplus

SQL> select value from v$diag_info where name ='Diag Trace';
or
SQL>show parameter background_dump_dest

Open file alert_SID.log with a text editor

You can also use the ADR Client Interface called 'adrci' to view the alert.log

% adrci
ADRCI> show alert
       show alert -tail 50

The alert log of a database is a chronological log of messages and errors, including the following:

Error while running adbldxml.pl

Error while running adbldxml.pl after upgrading database to 11.1.0.7 from 10.2.0.2

The following error was encountered while running adbldxml.pl on the database tier to create the context file. 


====================================================================
Could not Connect to the Database : ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory

Connecting to the VISN11 database instance...

Connection paramaters values: 
Database server hostname ==> NGLINUX05.NEWGEN.COM
Database listener port ==> 1542
Database SID ==> VISN11
Database schema name ==> apps

Could not Connect to the Database : ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory


AC-40000: Error: Exception - java.sql.SQLException: ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
====================================================================


The problem was that the database was not registered with the listener.




SQL> show parameter local_listener;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string




The value for LOCAL_LISTENER is missing, so automatic instance registration is not taking place
We can correct this by setting the LOCAL_LISTENER parameter:


SQL> alter system set local_listener='(ADDRESS =(PROTOCOL=TCP)(HOST=host.domain.com)(PORT=port_number)(SID=sid_name)';

System altered.

SQL> alter system register;

System altered.


SQL> exit

Now adbldxml runs successfully.

FNDLOAD Download / Upload Concurrent Programs Fails To Load Parameters

How to load parameter values when using FNDLOAD to move a concurrent program from one instance to another? There have been changes since Enhancement Request << BUG 6934421>>.


Solution

At the source instance run FNDLOAD DOWNLOAD using the control file afcpprog.lct and the parameter P_VSET_DOWNLOAD_CHILDREN="Y".

For example:
FNDLOAD apps/{pwd} 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct /export/home/applmgr/XXINTF_INV_ITEM_OUTBOUND_PKG.ldt PROGRAM APPLICATION_SHORT_NAME="XXINTF" CONCURRENT_PROGRAM_NAME="XXINTF_INV_ITEM_OUTBOUND_PKG" P_VSET_DOWNLOAD_CHILDREN="Y"

Then at the target instance run FNDLOAD UPLOAD using the data file you just generated.

For example:
FNDLOAD apps/{pwd} 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct /export/home/applmgr/XXINTF_INV_ITEM_OUTBOUND_PKG.ldt

If you are replacing an existing custom program, add the parameter - CUSTOM_MODE=FORCE.