Thursday, December 29, 2016

Current Applied Patch & Patch level status

Check Current Applied Patch & Patch level status of all modules in oracle



Check Current Applied Patch

set linesize 1000
set pages 9999
col patch_name format a10
col patch_type format a10
col maint_pack_level format a15
set colsep |

  SELECT   patch_name,           patch_type,           maint_pack_level,           creation_date    FROM   applsys.ad_applied_patches ORDER BY   creation_date DESC


Query used to view the patch level status of all modules



SELECT   a.application_name, DECODE (b.status, 'I', 'Installed', 'S', 'Shared', 'N/A') status,           patch_level  FROM   apps.fnd_application_vl a, apps.fnd_product_installations b WHERE   a.application_id = b.application_id;

Application Short Name of a module

Query to find Application Short Name of a module


The following query lists all the applications related information. This query can be used to find the APPLICATION_SHORT_NAME of a module (eg. Payables, Receivables, Order Management, etc.) that are often used for downloading FNDLOAD LDT files, adding responsibility to a user and many more.

You can uncomment the FAT.APPLICATION_NAME condition (very bottom line of the query) to learn about a particular module. In this case, I used "Payables".


SELECT fa.application_id           "Application ID",
       fat.application_name        "Application Name",
       fa.application_short_name   "Application Short Name",
       fa.basepath                 "Basepath"
  FROM fnd_application     fa,
       fnd_application_tl  fat
 WHERE fa.application_id = fat.application_id
   AND fat.language      = USERENV('LANG')
   -- AND fat.application_name = 'Payables'  -- <change it>
 ORDER BY fat.application_name;
 

Friday, December 2, 2016

ADOP CUTOVER (Reducing Time)

What can I do to reduce the time required for cutover?

It is important to distinguish between the time needed for the whole cutover phase, and the downtime period within the phase.
The actual downtime (during which users cannot log in) is significantly shorter than the whole phase.

To help reduce the overall time taken by cutover, you can do three things:

  • Run the finalize phase explicitly, to obviate the need for cutover to do so.
  • Shut down the concurrent managers before running cutover, to avoid having to wait for concurrent requests to complete. Alternatively, ensure no long-running concurrent jobs are submitted while a patching cycle is in progress.
  • Ensure you are using the maximum number of parallel workers your system will support.

ADOP DOWNTIME MODE

What is downtime mode and when can it be used?


To optimize the process of upgrading to E-Business Suite Release 12.2, the AD Delta 5 Release Update Pack introduced downtime mode, which is used as follows:


  • $ adop phase=apply patches=<patch_number> apply_mode=downtime

Downtime mode does not use an online patching cycle. 
The process of applying a patch in downtime mode completes more quickly than in online mode, but at the cost of increased system downtime. 
When applying Oracle E-Business Suite patches in this mode, adop will first confirm that the application tier services are down, and will then proceed to apply the patch to the run edition of the Oracle E-Business Suite database and file system.

Downtime mode is supported for:
  • All patching (including post-upgrade patching) that forms part of the Release 12.2 upgrade process and is completed before the system is scaled up, the application tier services are started, and users log in to the upgraded system.
  • Single-node development or test environments, where production support and high availability are not required.
Downtime mode allows the 12.2 upgrade process to be completed as quickly as possible.
 Once the upgrade is complete and users are online, all subsequent patching on a production system should use online mode, not downtime mode, unless the patch readme states otherwise. 
Several restrictions apply to the use of downtime mode:
  • You cannot validate successful patch application before cutover to the updated code takes place.
  • There is no capability to abort a failed patch and return to the existing run edition.
  • Release 12.2 patches are not normally tested in downtime mode.
  • Use of downtime mode in a multi-node application tier environment is not tested or supported.


Note: Downtime mode is only supported for production use where explicitly documented, or otherwise directed by Oracle.

Tuesday, November 22, 2016

BACKUPSET DETAILS

Backupset Details




set lines 220
set pages 1000
col backup_type for a4 heading "TYPE"
col controlfile_included heading "CF?"
col incremental_level heading "INCR LVL"
col pieces for 999 heading "PCS"
col elapsed_seconds heading "ELAPSED|SECONDS"
col device_type for a10 trunc heading "DEVICE|TYPE"
col compressed for a4 heading "ZIP?"
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col input_file_scan_only for a4 heading "SCAN|ONLY"
select
  d.bs_key, d.backup_type, d.controlfile_included, d.incremental_level, d.pieces,
  to_char(d.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
  to_char(d.completion_time, 'yyyy-mm-dd hh24:mi:ss') completion_time,
  d.elapsed_seconds, d.device_type, d.compressed, (d.output_bytes/1024/1024) output_mbytes, s.input_file_scan_only
from V$BACKUP_SET_DETAILS d
  join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
where session_recid = &SESSION_RECID
  and session_stamp = &SESSION_STAMP
order by d.start_time;

BACKUP JOB STATUS

 Backup jobs’ status and metadata


set lines 220
set pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST"
select
  j.session_recid, j.session_stamp,
  to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
  to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
  (j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
  decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
                                     3, 'Tuesday', 4, 'Wednesday',
                                     5, 'Thursday', 6, 'Friday',
                                     7, 'Saturday') dow,
  j.elapsed_seconds, j.time_taken_display,
  x.cf, x.df, x.i0, x.i1, x.l,
  ro.inst_id output_instance
from V$RMAN_BACKUP_JOB_DETAILS j
  left outer join (select
                     d.session_recid, d.session_stamp,
                     sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
                     sum(case when d.controlfile_included = 'NO'
                               and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
                     sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
                     sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
                     sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
                   from
                     V$BACKUP_SET_DETAILS d
                     join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
                   where s.input_file_scan_only = 'NO'
                   group by d.session_recid, d.session_stamp) x
    on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
  left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
                   from GV$RMAN_OUTPUT o
                   group by o.session_recid, o.session_stamp)
    ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
order by j.start_time;

CF:

Number of controlfile backups included in the backup set
DF:

Number of datafile full backups included in the backup set
I0:

Number of datafile incremental level-0 backups included in the backup set
I1:

Number of datafile incremental level-1 backups included in the backup set
L:

Number of archived log backups included in the backup set
OUT INST:

BACKUP REPORT SCRIPTS

Script will report on all backups – full, incremental and archivelog backups -


SET LINESIZE 125
COL start_time FORMAT A25
COL END_TIME FORMAT A25
SET PAGES 9999
col STATUS format a25
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
elapsed_seconds/3600                   hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;


script will report all on full and incremental backups, not archivelog backups -




col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
elapsed_seconds/3600                   hrs
from V$RMAN_BACKUP_JOB_DETAILS
where input_type='DB FULL'
order by session_key;


col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
elapsed_seconds/3600                   hrs
from V$RMAN_BACKUP_JOB_DETAILS
where input_type like '%DB%'
order by session_key;

ARCHIVE LOG GENERATION

Archive log generation per day 


SELECT SUM_ARCH.DAY,
         SUM_ARCH.GENERATED_MB,
         SUM_ARCH_DEL.DELETED_MB,
         SUM_ARCH.GENERATED_MB - SUM_ARCH_DEL.DELETED_MB "REMAINING_MB"
    FROM (  SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
                   SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
                      GENERATED_MB
              FROM V$ARCHIVED_LOG
             WHERE ARCHIVED = 'YES'
          GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH,
         (  SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
                   SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
                      DELETED_MB
              FROM V$ARCHIVED_LOG
             WHERE ARCHIVED = 'YES' AND DELETED = 'YES'
          GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH_DEL
   WHERE SUM_ARCH.DAY = SUM_ARCH_DEL.DAY(+)
ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');

Friday, November 11, 2016

How To Tell If Concurrent Managers For A Particular SID Are Running

 Doc ID 2089560.6

PROBLEM DESCRIPTION
-------------------

You are logged on to a server running multiple Oracle Applications instances, 
and you want to know if there are any concurrent managers processes running
for a particular instance running.

The following command:

     ps -ef | grep FNDLIBR

shows all FNDLIBR processes running on the server.  However, how can you tell 
which processes are associated with which instance?  How can you find all other 
concurrent manager processes and System PIDs?


SOLUTION DESCRIPTION
--------------------

You need to run $FND_TOP/sql/afcmstat.sql as the APPS user in the 
APPL_TOP/instance you are interested in.
 
The section labeled:  "Managers with their corresponding Oracle and System 
Process Id's" contains the information you are looking for.  If the internal 
manager is not running, you will not get a list under the heading.


These SQL scripts are useful in diagnosing concurrent manager related 
problems.  The names are listed below (located in $FND_TOP/sql): 
 
NAME              DESCRIPTION 
-----------------------------------------------------------------
 
afcmstat.sql    Lists active manager processes along with 
                defined max capacity to be in effect at the 
                present time.  Note: could be slow if table 
                FND_CONCURRENT_PROCESSES is large (since 
                history is maintained in it). 

afimchk.sql     Tells the status of ICM and PMON method in effect.
 
  
afrqrun.sql     Lists all the running, waiting (waiting to be 
                registered after sub requests completed) and 
                terminating requests. 
 
afrqwait.sql    Lists requests that are constrained and thus 
                waiting for the ICM to release them. 
 
afrqscm.sql     Prints log file name of managers that can run a 
                given request.  Helpful when a runnable request 
                stays pending to check for possible error 
                messages in manager log file. 
 
afcmcreq.sql    Prints the log file name of the manager that 
                processed the request (takes request id as 
                argument). 
  
afrqstat.sql    Summary of completed concurrent requests 
                grouped by completion status and execution type. 
 
afimlock.sql    Lists locks that the ICM is waiting to get. 
 
afcmrrq.sql     Lists managers that are currently running a 
                request (username, request id, program name, 
                start date, etc).

How To Change The Log And Output Location For Concurrent Managers?

A) The Logs and Output are located in $APPLCSF/$APPLLOG and  
$APPLCSF.

cd $APPLCSF/log
cd $APPLCSF/out

You can change the value of APPLCSF, APPLLOG and APPLOUT from the context file or
 you can do it from OAM. 

 The $APPLCSF/$APPLLOG and $APPLCSF/$APPLOUT directories should exist 
  and be accessible from the concurrent node.

B. Run autoconfig and bounce the concurrent managers for the changes to be implemented.