External tables to monitor os space in UNIX

To create the external table in oracle database follow the below steps.
(Note: Here we are creating external table for monitoring the OS space information considering OS is UNIX flavored)

SQL> create directory Directory_Name as '/usr/tmp'

SQL> CREATE TABLE server_space
(
  FILESYSTEM VARCHAR2(100),
  TOTAL_GB VARCHAR2(10),
  USED_GB VARCHAR2(10),
  FREE_GB VARCHAR2(10),
  USED_SPACE VARCHAR2(10),
  MOUNTED_ON VARCHAR2(100)
)
ORGANIZATION EXTERNAL
 (  TYPE ORACLE_LOADER
    DEFAULT DIRECTORY Directory_Name
    ACCESS PARAMETERS
    (
       RECORDS DELIMITED BY NEWLINE
       NOBADFILE
       NODISCARDFILE
       NOLOGFILE
       SKIP 0
       FIELDS TERMINATED BY WHITESPACE
       MISSING FIELD VALUES ARE NULL
       REJECT ROWS WITH ALL NULL FIELDS
       (
         FILESYSTEM CHAR,
         TOTAL_GB CHAR,
         USED_GB CHAR,
         FREE_GB CHAR,
         USED_SPACE CHAR,
         MOUNTED_ON CHAR
       )
    )
    LOCATION (ALERT_LOG_DIR:'server.txt')
 )REJECT LIMIT UNLIMITED


Schedule the below line using crontab
df -h |grep -v Filesystem|awk '{print $1" "$2" "$3" "$4" "$5" "$6}' >/usr/tmp/server.txt

Now you can do the server side space monitoring without login to server.

SQL>select * from server_space;

Oracle's RMAN Validation for Identifying corruption

Follow the below steps to identify corruption as well as corrupted objects in database.

Step 1: Identify the corrupt blocks:

RMAN> connect
RMAN> backup validate check logical database;

or

1-- RMAN> configure device type disk parallelism 4;  
    RMAN> backup validate check logical database;    
--------------------------------------------------------------------------------

The corrupted blocks are listed in the view v$database_block_corruption:

2-- SQL> select * from v$database_block_corruption;
--------------------------------------------------------------------------------

Step 2 : Identify the corrupt segments:

1-- drop table corrupted_objects;
------------------------------------------------------------------------------

2--
create table corrupted_objects
as
select owner, segment_name, partition_name, segment_type, 1 file#, 1 block#
from dba_extents
where 1=2;
------------------------------------------------------------------------------

3--
declare
cursor C1 is select file#, block#, blocks
from v$database_block_corruption;
begin
for r1 in C1 loop
  for r2 in 1..r1.blocks loop
   insert into corrupted_objects
   select owner, segment_name, partition_name,segment_type, r1.file#, r1.block#+r2-1
     from dba_extents
    where file_id=r1.file#
      and (r1.block#+r2-1) between block_id and block_id + blocks - 1;
   if sql%notfound then
   /* segment header block might be corrupt causing dba_extents not returning rows. */
    insert into corrupted_objects
     select owner, segment_name, partition_name, segment_type, r1.file#, r1.block#
       from dba_segments
      where header_file=r1.file# and header_block = (r1.block#+r2-1);
   end if;
   commit;
  end loop;
 end loop;
end;
/
----------------------------------------------------------------------------------

4-- Select all the objects populated in the table corrupted_objects:

SQL> select distinct owner, segment_name, partition_name, segment_type from corrupted_objects;

Export/Import and Corrupt Block in oracle database

While Doing export backup of database/schema/objects;
If there is a corrupted object, an error will  be written in the export log showing the object that was not exported.
Therefore, no corrupt object blocks will be exported into the export file.

1. Export does not detect all corruptions:
  - does not detect disk corruptions above the high water mark;
  - does not detect corruptions in indexes, or in free or temporary extents;
  - does not detect all corruptions in the data dictionary (the export
     utility does not read all of the data dictionary, so there could still be undetected corruptions in the
     SYSTEM tablespace).

2. Only a Conventional path export is able to detect logical corrupt blocks.
  When using a Direct path export , the data is read from disk directly
  into the export session's program global area (PGA): the rows are
  transferred directly to the Export session's private buffer. This means
  that the SQL command-processing layer (evaluation buffer) is bypassed,
  because the data is already in the format that Export expects.
  Therefore, do not specify the export parameter DIRECT=Y but use DIRECT=N
  which is also the default value for the DIRECT parameter.

Therefore, use a conventional export and not the direct path.

Scenario for the Physical Block Corruption

SMON's unable to clean the temporary segments due to the fact that these blocks are currently fractured

A fractured block is a clear symptom about serious issues within the O.S./H.W. layers.
Oracle keeps track off the header of each block and constructs before writing down to disk by building a small
4 byte field/value in the tail of each block to guarantee it is correctly written

Pls see the following as an Example:
-----------------------
Corrupt block relative dba: 0x0380e573 (file 14, block 58739)
Fractured block found during buffer read
Data in bad block -
type: 6 format: 2 rdba: 0x0380e573
last change scn: 0x0288.8e5a2f78 seq: 0x1 flg: 0x04
consistency value in tail: 0x00780601 <- Should be least 4 significant bytes from scn, flg && seq
check value in block header: 0x8739, computed block checksum: 0x2f00
spare1: 0x0, spare2: 0x0, spare3: 0x0
***
Reread of rdba: 0x0380e573 (file 14, block 58739) found same corrupted data
-----------------------

This value was right on the block oracle asked o.s. to write but, unfortunatelly, the write did not complete as
a whole and only partial write was done.

There are checks that may be run against datafiles to ensure the validity of all tail values on all blocks of them.
DBV catches this kind of failures and may be used against your DB file(s) to check this.

Identically, there is a clear path to follow when this happens.
These blocks are badly written by o.s./h.w. and as such, Oracle operations over the block(s) affected are correct.
(otherwise, a different kind of error would have been printed out)

. What to do to recover from this scenario (fractured block(s)):
----------------------------------------------------------------
0.- Offline affected datafile(s) on production instance or shutdown + startup mount DB.
In case SYSTEM datafile is affected, you need necessarily to shutdown DB + startup mount (you can't offline System TS).
1.- Restore previous backup of affected datafile(s) into different location
2.- Check with DBV the file restored
- corruption exists -> Return to step 1 by selecting an 'older' backup
- corruption does not exist -> Continue with step 3.-
3.- Rename current datafiles at o.s. level to avoid overwriting in case something fails
4.- Recover affected datafile(s)
-> recover database ; -- this will recover exclusivelly the old datafiles found
5.- Online affected datafile(s) or open database after media recovery complete

. What if no backup exists or corruption exists in all backups:
---------------------------------------------------------------
In this case we can only rebuild the affected object.
We will not be able to get rid of these temporary segments.

I believe the only thing that may be done here is:
.- Determine where the problem resides (at OS/HW level)
.- in the mean time, move this DB (the correct part of it) to a new box
-> 733824.1 HowTo Recreate a database using TTS

Block Corruption in Oracle Database

Oracle classifies the Data File Block corruptions as Physical and Logical :

Physical Block Corruptions

This kind of block corruptions are normally reported by Oracle with error ORA-1578 and the detailed corruption description is printed in the alert log.

Corruption Examples are:

    Bad header - the beginning of the block (cache header) is corrupt with invalid values
    The block is Fractured/Incomplete - header and footer of the block do not match
    The block checksum is invalid
    The block is misplaced
    Zeroed out blocks / ORA-8103

Detailed Corruption Description:

Fractured Block:
A Fractured block means that the block is incomplete. Information from the block header does not match the block tail.

Bad Checksum:
Block Checksums are used to identify if the block was changed by something external to Oracle and after the block was last written by Oracle.
Checksum is calculated by DBWR or direct loader before writing the block to disk and stored in the block header. Every time that the block is read and if db_block_checksum is different than false, Oracle calculates a checksum and compares it to the one stored in the block header. Reference Note 30706.1

Block Misplaced:
This is when Oracle detected that the content of the block being read belongs to a different block and the checksum is valid


********************************************************************************

Logical Block Corruptions

This is when block contains a valid checksum and the structure below the beginning of the block is corrupt (Block content is corrupt). It may cause different ORA-600 errors.

The detailed corruption description for Logical Corruptions are not normally printed in the alert.log. DBVerify will report what is logically corrupted in the block.

Corruption Examples are:

    row locked by non-existent transaction - ORA-600 [4512],etc
     the amount of space used is not equal to block size
    avsp bad
    etc.

When db_block_checking is enabled, it may produce the internal errors ORA-600 [kddummy_blkchk] or ORA-600 [kdBlkCheckError].


If db_block_checking is enabled and the block is already logically corrupt on disk, the next block update will mark the block as Soft Corrupt and future reads of this block will produce the error ORA-1578. In that case DBVerify reports this corruption with error "DBV-200: Block, dba <rdba>, already marked corrupted".

How to find Special Characters from the table

To Find Special Characters in Particular column 

SELECT *
  FROM table_name
 WHERE RTRIM
          ((LTRIM
               (TRANSLATE
                   (REPLACE (&p_column, 'A', ''),
                       'ABCDEFGHIJKLMNOPQRSTUVWXYZ
abcdefghijklmnopqrstuvwxyz0123456789@#$%^&*()_+-=,!\`~{}./?:";''[]'
                    || CHR (160)
                    || CHR (32)
                    || CHR (0)
                    || CHR (253),
                    'A'
                   )
               )
           )
          ) IS NOT NULL;

Also by using below PL/SQL block you can find special characters through out the all columns in table.

SET SERVEROUTPUT ON SIZE 1000000

DECLARE

PROCEDURE GOOEY(V_TABLE VARCHAR2, V_COLUMN VARCHAR2) IS

TYPE T_ID IS TABLE OF NUMBER;

TYPE T_DUMP IS TABLE OF VARCHAR2(20000);

TYPE T_DATA IS TABLE OF VARCHAR2(20000);

L_ID T_ID;

L_DATA T_DATA;

L_DUMP T_DUMP;

CURSOR A IS

SELECT DISTINCT COLUMN_NAME

FROM DBA_TAB_COLUMNS

WHERE TABLE_NAME = V_TABLE

AND DATA_TYPE = 'VARCHAR2'

AND COLUMN_NAME NOT IN ('CUSTOMER_KEY','ADDRESS_KEY');

BEGIN

FOR X IN A LOOP

L_ID := NULL;

L_DATA := NULL;

L_DUMP := NULL;

EXECUTE IMMEDIATE 'SELECT ' || V_COLUMN || ', ' || X.COLUMN_NAME || ', ' ||

'dump(' || X.COLUMN_NAME || ')'

|| ' FROM ' || V_TABLE

|| ' WHERE RTRIM((LTRIM(REPLACE(TRANSLATE(' || X.COLUMN_NAME ||

',''ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789@#$%^&*()_+

-=,!\`~{}./?:";''''[ ]'',''A''), ''A'', '''')))) IS NOT NULL'

BULK COLLECT INTO L_ID, L_DATA, L_DUMP;

IF L_ID IS NOT NULL THEN

FOR K IN 1..L_ID.COUNT LOOP

DBMS_OUTPUT.PUT_LINE(V_TABLE || ' - ' || X.COLUMN_NAME || ' - ' ||

TO_CHAR(L_ID(K),'999999999999'));

DBMS_OUTPUT.PUT_LINE(L_DATA(K));

DBMS_OUTPUT.PUT_LINE(L_DUMP(K));

DBMS_OUTPUT.PUT_LINE('*********************');

END LOOP;

END IF;

END LOOP;

END GOOEY;

BEGIN

GOOEY('TABLENAME1','COLUMN_NAME');

GOOEY('TABLENAME2',' COLUMN_NAME ');

GOOEY('TABLENAME3',' COLUMN_NAME ');

END; 

Service Management in Concurrent Processing

An Oracle Applications system depends on a variety of services such as Forms Listeners, HTTP Servers, Concurrent Managers, and Workflow Mailers. Such services are composed of one or more processes that must be kept running for the proper functioning of the applications. Previously many of these processes had to be individually started and monitored by system administrators. Management of these processes was complicated by the fact that these services could be distributed across multiple host machines. Service Management helps to greatly simplify the management of these processes by providing a fault tolerant service framework and a central management console built into Oracle Applications Manager.

Service Management is an extension of concurrent processing, which provides a powerful framework for managing processes on multiple host machines. With Service Management, virtually any application tier service can be integrated into this framework. Services such as the Oracle Forms Listener, Oracle Reports Server, Apache Web listener, and Oracle Workflow Mailer can be run under Service Management.

With Service Management, the Internal Concurrent Manager (ICM) manages the various service processes across multiple hosts. On each host, Service Manager acts on behalf of the ICM, allowing the ICM to monitor and control service processes on that host. System administrators can then configure, monitor, and control services though a management console which communicates with the ICM.

Generic Service Management

 

Service Management provides a fault tolerant system. If a service process exits unexpectedly, the ICM will automatically attempt to restart the process. If a host fails, the ICM may start the affected service processes on a secondary host. The ICM itself is monitored and kept alive by Internal Monitor processes located on various hosts.

Service Management provides significant improvements in the manageability of Oracle Applications. System administrators can now use the central console in Oracle Applications Manager (OAM) to manage a variety of services that formerly had to be managed independently on separate hosts. The entire set of system services may be started or stopped with a single action. Service Management also provides a great benefit by automatically compensating for certain system failures.

Service processes are very much like concurrent manager and transaction manager processes. They must be kept running on a middle tier for the proper functioning of their respective products. The concurrent processing management feature has been built for concurrent managers and transaction managers, to provide fault tolerance, process distribution, and simplified configuration and control.


Benefits of Service Management
• The service processes will no longer need to be manually and individually started and monitored by Oracle Applications system administrators.
• Services can take advantage of the process distribution and fault tolerance capabilities that have been developed for concurrent processing.
• As with concurrent manager processes, system administrators can use work shifts to determine the number of processes that will be active for a service on a given node for a given time period.

To extend process management support to the various Applications services, the Internal Concurrent Manager must be able to start, monitor, and control processes on all Applications tiers. Every node of every tier will have an Oracle RPC-based Service Manager installed. The ICM will use the Service Manager to manage processes.

Service
A service is a process or collection of processes that perform actions at the request of client processes. A concurrent manager is a type of service where the client submits a request for actions to be processed while the client continues to do other work. While active, a service must have one or more listener processes that wait to process requests from clients. An example of a listener is a concurrent manager process which periodically polls a queue for requests to process.

Service Instance
Each service controlled by service management may have multiple service instances.
Each instance may consist of one or more processes.

Concurrent:GSM Enabled Profile Option
The Concurrent:GSM Enabled profile option should be set to Y to enable Service Management. It is set automatically to Y by AutoConfig. Disabling Service Management is not recommended as that may prevent necessary services from starting.

Service Management and Control Scripts
With Service Management, the Apache Server, Forms Server, Forms Metrics Server, Forms Metrics Client, and Reports services can be managed through Oracle Applications Manager. When these services are enabled for Service Management, they can still be controlled using the control scripts listed below; for example, using adapcctl.sh (UNIX) or adapcctl (Windows).

These control scripts are generated by AutoConfig for the Forms Listener, Reports Server, and other Application Tier services, and synchronize with Service Management. If you start or stop a service using one of these scripts, Service Management is notified of the change. If the Service Management infrastructure is not running, the control scripts can be used to control individual services. The service status is synchronized with Service Management when the Internal Concurrent Manager (ICM) is restarted. Running one of these control scripts from the command line starts or stops the respective service synchronously and the FNDSVCRG program and the ICM handles the data collection.
The control scripts that can be managed by Service Management are:
• adapcctl.sh (Apache)
• adfrmctl.sh (Forms)
• adfmsctl.sh (Metrics Server)
• adfmcctl.sh (Metrics Client)
• adrepctl.sh (Reports)

Network Failure Recovery
As part of its shutdown process, the ICM determines if it's being forced to shutdown due to losing its database connection. This is done by looking for specific error messages ORA-3113, ORA-3114, or ORA-1041. If one of these error messages is detected, the ICM spawns the reviver process, which attempts to make a database connection. If unsuccessful, it sleeps for a period before trying again. This continues until either a successful connection is made or it receives a signal to shut itself down. When a successful connection is made, the process kills the old ICM database session, and then starts a new ICM using the normal start manager script. Once the ICM is restarted, it starts up any other managers that had also shut down, and normal processing resumes.

Failover Sensitive Workshifts
Nodes can become overloaded when a middle-tier node fails and service instances on that node failover to their secondary nodes. The Load Distribution feature allows the System Administrator to control the allocation of resources during normal processing. The Failover Sensitivity feature allows Work Shifts to failover with fewer processes than on the original node. This lessens the impact on the existing resources allocated on the secondary node.
The number of failover processes is entered as part of the standard Work Shift settings in the Service Instance Definition. When failover occurs, the ICM uses the Failover Processes value in place of the normal running processes value as it iterates through service instances to perform queue sizing.

Advanced Concurrent Managers

A concurrent manager will run requests based on both its assigned:

• Work shift/s - when the manager can run and the number of manager processes the manager should run and
• Specialization Rules - The programs the custom manager can run.

Note: In general, all requests run through the Standard manager unless you create a custom manager with a work shift and assign a program via a specialization rule and then exclude the program from the standard manager.

Custom Concurrent Managers

A word of warning: - Creating a custom manager is relatively easy to process, getting rid of one is not. In fact you are not meant to delete a custom manager, only disable them, so plan carefully before you go creating custom managers.

Work shifts

A work shift defines when one or more manager/s will run. Out of the box – all Concurrent managers are assigned the “standard” work shift. This work shift runs between 00:00 am and 23:59 seven (7) days a week.

Before you go creating additional work shifts you really want to be very sure of why you require a custom work shift, what will run in that work shift and what will happen to any program assigned to the manager / work shift when the work shift defines the manager as inactive.

One of the most complex custom work shifts is one to only run selected concurrent programs overnight. There are three (3) basic steps to achieving this setup:

• In order for a manager to only run requests overnight (spans midnight) you must create two (2) work shifts and assign both those work shifts to the “Overnight” Manager:

                         Overnight_am to run between 18:00 and 23:59

                         Overnight_pm to run between 00:00 and 07:00

• You then create a custom concurrent manager usually with a descriptive name such as “Overnight_only” and assign it both the overnight_am and overnight_pm work shifts.
• The final step is to assign the programs you want to be run by the “overnight_only” manager using specialisation rules.

Note: When a request is submitted to run in the “Overnight_only” manager before or after the “Overnight_only” manager is running, the request will be marked as Pending Error, as there are no managers to run the request until the managers start.

Specialization rules

Specialization rules are used to assign specific concurrent manager programs to specific queues.

Specialization rules can be one of the more complex Oracle Applications features to set up and once set up, one of the more complex to find using the current E-Business Suite screens. This makes it hard for an administrator taking over a site to understand and troubleshoot. Remember, with specialization rules simplicity is the key.

To add to the complexity, there are a number of specialization rule combinations:

• By Program
• By Application user
• By Oracle User
• By Request Type
• By Complex Rule, which is a combination of Program, application user and / or Oracle user

In the case of a program rule, it’s a simple process of “including” a program to run in the custom manager and “excluding” the program from the standard manager.

Specialization Rules to concurrent manager (Custom/Standard) is set via the applications  

Concurrent > Manager > Define

Request types

Request types are one of the most powerful features of concurrent processing; they allow you to assign new requests of a program from one concurrent manager to another without the need to bounce the concurrent managers.

Note: Once a program has been submitted the queue cannot be changed.

A request type is a logical folder if you will, which is assigned to a specific queue by specialization rules the same way as a program is assigned e.g. The request type “Slow” is “included” to the concurrent manager “Slow” queue and “excluded” from the standard manager.

The request type for a concurrent program is set via the applications Concurrent > Program > Define screen

Incompatibility Rules

Programs may also not run when you expect based on one or more incompatibility rules.

An incompatibility rule defines that a selected program cannot run whilst a program that has been defined as incompatible is running.

An example of this is the GL Posting program “GLPPOS”, this program has been defined as incompatible with itself, as such a second or subsequent posting program being submitted must wait until the initial posting program has completed.

Much like specialization rules, incompatibility rules are easy to create but not so easy to keep track of.

Incompatibility rules are managed vie the application Concurrent > Programs > Define Screen. Select the incompatibilities button.

Role of the Conflict Resolution Manager (FNDCRM)

The conflict resolution manager assesses each requested concurrent program against programs that are running, referencing the applications incompatibility rules.

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.

Run Alone Programs

You can make a program incompatible with all other concurrent programs by defining the program to be run-alone via the run alone option in the concurrent program define screen. Care should be exercised here as once this program is running no other programs can run at the same time.

How many concurrent managers should I have?

It is a common misconception that the more managers you have the better throughput you will get. This situation is further compounded once the business find out you can add managers. They too generally believe there are spare resources you are not utilising, and therefore that you should add more managers.

When configuring managers it is best to start low, and work up.

A general rule of thumb is you should have no more managers than 2 (standard and custom) times the number of CPUs (optimal is 1.8 as this leaves head room for user connections).

Always resist the urge to add managers

·         Generally no more than 2 managers per CPU

·         Custom managers for FSGs ( Assume 1 FSG will consume one CPU )

·         Custom managers for selected slow jobs

·         Custom managers for selected fast jobs

Note: When you creating custom managers do not forget to reduce the number of standard managers.

Sleep and cache settings in Concurrent Managers:

When a queue wakes up (sleep time) it queries the FND_CONCURRENT_REQUESTS table for any pending requests. The value of the cache size will determine the number of rows that will be returned. If a request completes within the managers sleep time, additional requests held in cache can be run prior to the next manager wake up time.

Some Important Documents for Patching..

Following list contains some of the important documents that are related to  EBZs- patches

• EBS Quick Reference For MFG Rollup Patches (Doc ID 726229.1)
• Procurement Family Patch Release History (Doc ID 222339.1)
• Recommended patches for iProcurement (Doc ID 1399131.1)
• Recommended patches for iSupplier (Doc ID 1401834.1)
• Recommended patches for Purchasing Approvals (Doc ID 1122052.1)
• Recommended patches for Purchase Order and Requisition Processing (Doc ID 1358356.1)
• Procurement Suite Patching Strategy For One-Off Requests and RUPs (Doc ID 602754.1)
• EBS Quick Reference for MFG Rollup Patches (Doc ID 726229.1)
• What Are The Punchout Patches After Applying Latest CU And Procurement RUP 11 Or RUP 12 Patches (Doc ID 1305076.1)
• Oracle iProcurement Patches Guide - Master List (Doc ID 362023.1)
• INV/WMS/RCV Family Patch Release History / Patchsets / RUPs (Doc ID 726226.1)
• Critical E-Business Suite11i (11.5.10) Extended Support Information on Minimum Baseline Patch Requirements (Doc ID 1116887.1)
• How To Determine the Product Information Management (PIM) Patchset and Rollup Patch Version (Doc ID 414470.1)
• Oracle E-Business Suite Release 12 Release Update Pack (RUP) (Doc ID 423541.1)
• Logistics Suite Patching Strategy For One-Off Requests and RUPs (Doc ID 605248.1)
• EBS Quick Reference For MFG Rollup Patche (Doc ID 726229.1)
• Oracle E-Business Suite Release 12 Release Update Pack (RUP) (Doc ID 423541.1)
• Introduction to Oracle Recommended Patches (Doc ID 756388.1)
• How to Find E-Business Suite Recommended Patches (Doc ID 1400757.1)
• Patch Wizard Utility  (Doc ID 976188.1)
• Patch Wizard FAQ (Doc ID 976688.1)
• Patch Wizard Overview Videos (Doc ID 1210479.1)
• Patch Wizard Training (Doc ID 1085668.1)
• New Required Patches for Patch Wizard,...for Oracle E-Business Suite Releases 11i, 12.0, and 12.1 (Doc ID 1267768.1)
• Oracle E-Business Suite Releases 11i and 12 Critical Patch Update Knowledge Document (April 2011)        [Document 1272097.1]   
• Patch Wizard Utility        [Document 976188.1]   
• How to Apply a Patch and How to Retrieve Patch List for OSB 11g Using the OPatch Utility        [Document 1266548.1]   
• HOW TO INSTALL A PATCH ON CLUSTERWARE 11.2.0.1 INTO GRID INFRASTRUCTURE HOME        [Document 1054439.1]   
• Oracle Applications Patching FAQ for Release 12   [Document 459156.1]
• Release 11i Adpatch Basics  [Document 181665.1]
• Oracle Applications Patching FAQ for Release 11i  [Document 457566.1]
• How to Apply an 11i Patch When adpatch is Already Running  [Document 175485.1]
• 11.0.x : Patch Installation Frequently Asked Questions   [Document 60766.1]
• Patching Best Practices and Reducing Downtime��  [Document 225165.1]
• What Log Files are Generated with Adpatch, and Where are They Located?   [Document 314540.1]
• How To Apply a Patch in a Multi-Server Environment  [Document 136342.1]
• HRMS PATCH INSTALL FAQ  [Document 374915.1]

Overview – Patching in E-Business Suite

Overview

adpatch is one of AD utilities which can be used for Applying patches to the Oracle Applications and Oracle database (also you can add new languages and products as a patch).
--AD Utilities (abbreviation from Applications DBA) are programs which perform a variety of tasks, including generating files, updating your system, merging and applying patches, installing off-cycle products.  These AD Utilities are executable files in $AD_TOP/bin

Applying a patch updates your existing system in various ways, from adding a new feature or product to improving system performance.
patches are applied for a number of reasons, including:

■ Fixing an existing issue■ Adding a new feature or functionality■ Updating to a higher maintenance level■ Applying the latest product enhancements■ Providing interoperability to new technology stacks■ Determining the source of an issue■ Applying online help

Depending on the type of patch, it may update the file system, or the database, or both.

Unified Driver File

The unified driver, named u.drv, contains the commands necessary to change files and database objects, and to generate new objects. It contains copy,database, and generate portions and performs the copy, database, and generate actions in the stated order. You typically run the unified driver on all APPL_TOPs. AutoPatch
runs only the actions that are required for the current APPL_TOP.

C,D and G Driver File
C – copy driver
D – database driver
G – forms and reports generation driver

Apps patches may take a name like p111111_R12_Unix.zip where p menas patch next few digit are patch number Unix is platform or OS on which this patch can be applied.
When you unzip a patch you will see some files types like:

1. readme.txt - This file contain steps to spply patches, List of Prerequisite patch should be there on apps Instance (If not apply that patch )
2. cXXXXXXX.drv c stand for copy driver file , this copies patch content to respective patch location , driver is like bus driver which provides instruction on work adpatch need to perform.
3. dXXXXXXX.drv d stand for Database driver & contain content related to database like creating packages, tables, adding column….
4. gXXXXXXX.drv This contain files related to forms , reports, graphis or messages
5. uXXXXXXX.drv Sometime these three types of files are bundled together into single driver file called Unified driver file

Hence as a system admin or as a DBA you have to be aware of which type of driver file to apply and where ? (its common sense if this is d driver you just need to apply once on concurrent manager tier and c & g on all tiers as database is single & if you run it once its enough . If you applied it again by mistake its no harm & will not cause any issues .Similary c & g you need to apply from all tiers , because c will copy your content & g will generate message files).
If you are not sure where to apply what just apply it on all tiers & depending on your context file in APPL_TOP/admin/SID_hostname.xml it will apply where to install what by checking xml tag TIER_ADFORMS , TIER_ADWEB , TIER_ADADMIN open your xml file & see these parameters are there or not ( For xml file your system should be Autoconfig enabled ) all system after 11.5.8 by default are autoconfig enabled .

Common Patch Terminology:

Standalone/Oneoffpatches : This is used to define patch created to fix single/particular problem.
Mini Pack : This is group of oneoff patches for a particular product like INV, GL, AP and named like 11i.GL.E ( means this group of patches contain fix for 11i GL product (General Ledger till time E is released ) This is cummutative which means it will include 11i.GL.A, 11i.GL.B ….11iGL.D till 11i.GL.E earlier in 10.7 it used to called as patchset.
Family Pack : Group of mini packs in one family buldeled together is called as family pack. they are usually named as 11i_PF. Few example of falilies are SCM ( 11i.SCM_PF.G ), ATG ( 11i.ATG_PF.H ) _PF indicate Product Family Pack
Maintenance Pack : Group of family pack together is called as maintenance pack. So if you say your Verison is 11.5.10 then its maintenance pack 10 ( 3rd digit is maintenance pack )

Hence, few one off patch make mini pack , few mini pack related to same family bundeled together as family pack & all family pack fixed till that time are bundeled in Maintenance pack

NLS Patch

 When you have more than one language , like English & French or Arabic) then apart from normal patch you have to apply patch for specific language Installed in your system called as NLS patch

Benifits of Oracle Recommended patches:

Oracle Recommended Patches provide the following benefits:

• They fix a set of critical issues commonly encountered in targeted environments and configurations.
• They stabilize production environments because the patches address known critical issues.
• They help save time and cost by eliminating rediscovery of known issues.
• They are tested as a single combined unit, resulting in increased quality and eliminating the risk of combining patches that are only independently tested.
• They make it easier to identify patches applicable for a targeted environment/configuration.

 

How to troubleshoot a Patch Failure - adpatch

---

To Note that Workers are controlled by adctrl. Based on the error in the log , fix the problem ; Use adctrl ; select option restart failed worked & then give worker number you want to restart to confirm if problem is fixed.

Please check the following file types / errors causing job failures :

a) odf files : (Object Definition Files) -odf files are there to create tables ,indexes and view etc.
b) pls scripts : -If the failed pls scripts are for creating stored procedure or packages
c) sql Scripts: -Failure on the SQL Script
d) WFLOAD and FNDLOAD failure- Look for the error message in the log file of the worker that fails.

In order to resolve the error, you may consider the following action plan

1) Try to execute the failing sql / pls manually and see the results
2) Check the Alert Log  and If any of the workers failed due to insufficient tablespace, then add the space , restart the failed worker.
3) Check the database session waits - There could be enquires.
Check the locks and locked objects to ensure that the session applying the patch is now waiting on a lock. If so need to diagnose, kill the session holding the lock.
(Scripts for database Diagnosis provoded in the database Scripts Section)
4) -For WFLOAD and FNDLOAD, you need to look at the log file generated by the above command. The worker’s logfile should point you to the location of the log file.
5) If adpatch fails to generate reports or forms. it is ok to skip these failure as you can always regenerate them after patching.
6) If it fails for a Pre-req patch and a lot of downtime has already lapsed :

If Adpatch Fails due to pre-requisite Patch or for some other reason and a lot of time has already been spent since the patch started running then , instead of applying this patch from begining you need to do the following steps in order to save the time you spend while applying the patch.

1. Using the adctrl utility, shutdown the workers.

a. adctrl
b. Select option 3 “Tell worker to shutdown/quit”

2. Backup the FND_INSTALL_PROCESSES table which is owned by the APPLSYS schema

a. sqlplusapplsys/<password>
b. create table fnd_Install_processes_back
as select * from fnd_Install_processes;
c. The 2 tables should have the same number of records.
select count(*) from fnd_Install_processes_back;
select count(*) from fnd_Install_processes;

3. Backup the AD_DEFERRED_JOBS table.

a. sqlplusapplsys/<password>
b. create table AD_DEFERRED_JOBS_back
as select * from AD_DEFERRED_JOBS;
c. The 2 tables should have the same number of records.
select count(*) from AD_DEFERRED_JOBS_back;
select count(*) from AD_DEFERRED_JOBS;

4. Backup the .rf9 files located in $APPL_TOP/admin/<SID>/restart directory.

At this point, the adpatch session should have ended and the cursor should
be back at the Unix prompt.
a. cd $APPL_TOP/admin/<SID>
b. mv restart restart_back
c. mkdir restart

5. Drop the FND_INSTALL_PROCESSES table and the AD_DEFERRED_JOBS table.

a. sqlplusapplsys/<password>
b. drop table FND_INSTALL_PROCESSES;
c. drop table AD_DEFERRED_JOBS;

6. Apply the new patch ( say pre-req patch).

Once this Pre-req Patch is applied successfully.

7. Restore the .rf9 files located in $APPL_TOP/admin/<SID>/restart_back directory.

a. cd $APPL_TOP/admin/<SID>
b. mv restart restart_<patchnumber>
c. mv restart_back restart

8. Restore the FND_INSTALL_PROCESSES table which is owned by the APPLSYS schema.

a. sqlplusapplsys/<password>
b. create table fnd_Install_processes
as select * from fnd_Install_processes_back;
c. The 2 tables should have the same number of records.
select count(*) from fnd_Install_processes;
select count(*) from fnd_Install_processes_back;

9. Restore the AD_DEFERRED_JOBS table.

a. sqlplusapplsys/<password>
b. create table AD_DEFERRED_JOBS
as select * from AD_DEFERRED_JOBS_back;
c. The 2 tables should have the same number of records.
select count(*) from AD_DEFERRED_JOBS_back;
select count(*) from AD_DEFERRED_JOBS;

10. Re-create synonyms

a. sqlplus apps/apps
b. create synonym AD_DEFERRED_JOBS for APPLSYS.AD_DEFERRED_JOBS;
c. create synonym FND_INSTALL_PROCESSES FOR APPLSYS.FND_INSTALL_PROCESSES;

11. Start adpatch, it will resume where it stopped previously.

If you want to skip a worker , this option is not visible & do it using adctrl option 8 . It is not a good idea to skip the failed worker without consulting from oracle.

adpatch Options

---

Type "adpatch help=y" on unix prompt to display all the options. You can execute adpatch by logging in as the applications OS user and sourcing the appropriate environment file.

#su – applmgr
$. ./APPSORA.env
$adpatch

By default adpatch does not take any clause, but there are some clauses that you could use with adpatch

Running a patch in test mode

You can use the apply clause with adpatch to specify weather to run the patch in TEST mode or not, when you run the patch in  test mode it does not do any changes but runs generates a log fiule with all the actions it would have performed.

$adpatch apply=n|y
The default is apply=y

Pre-install Mode

You can run a patch in pre install mode, this would be done normally during an upgrade or consolidated update. When a patch is applied in a preinstall mode the all the AD utilities are updated before the upgrade or update.

$adpatch preinstall=y
The default is preinstall=n

Other Options with adpatch
You can use the options clause to specify some of the other options available with adpatch.

Autoconfig

You can use the options=noautoconfig top specify autopatch that you do not wish to run autoconfig as a part of the patch  application. This can be useful when applying a large number of patches when they are not merged. By default autoconfig is run  as a part of adpatch.

$adpatch options=noautoconfigCheckfile
The chekfile option of adpatch tells adpathc to check for already executed exec, SQL, and exectiercommands.You can use  options=nocheckfile skips this check, however this can cause performance overheds so should be used only when specified.

$adpatch options=nocheckfile

Compile Database

By defaultyautopatch compiles the invalid objects after the patch application, in case you wish not to do so you can specify  options=nocompiledb along with autopatch.
$adpatch options=nocompiledb

Compile JSP

By defaultyautopatch compiles the java server pages (jsp) after the patch application, in case you wish not to do so you can specify options=nocompilejsp along with autopatch.
$adpatch options=nocompilejsp

Copy Portion

If you wish adpatch not to execute the commands present in the copy driver portion of the patch you can use the  options=nocopyportion.
$adpatch options=nocopyportion

Database Portion

If you wish adpatch not to execute the commands present in the database driver portion of the patch you can use the options=nodatabaseportion.
$adpatch options=nodatabaseportion

Generate Portion

If you wish adpatch not to execute the commands present in the generate driver portion of the patch you can use the options=nogenerateportion
$adpatch options=nogenerateportion

Maintenance Mode

If you wish to apply a patch regardless of the system being in maintenance mode you can use options=hotpatch.
$adpatch options=hotpatch

Integrity Check

If you wish to check the integrity of the patch you can use the options=integrity. Since metalink patches are pre checked for  their integrity it is generally not required to do an explicit check and the default value is nointegrity.
$adpatch options=integrity

Maintain MRC

You can use the maintainmrc option to specify weather you wish adpatch to execute the Maintain MRC schema as a part of the patch application or not. By default maintain MRC is done for standard patches and is disbaled for tarnslation and documentation patches.
$adpatch options=nomaintainmrc

Pre requisite Patch Check

If you wish adpatch not to check for pre requisite patches before application of the main patch you can use options=noprereq.By default pre requsite checking is enabled.
$adpatch options=noprereq

Validate Schemas

If you wish adpatch to explicitly validate all the registedschems by making a connection you can use options=validate. By  default this validation is not performed.
$adpatch options=validate

Java Classes

If you wish adpatch not to copy new java classes from the patch you can use options=nojcopy.By default java classes are copied.
$adpatch options=nojcopy

Force Copy

By default adpatch copies the files without check the version of the existing files already present on the system.If you do  not wish the newer version of the file to be replaced by the older version contained in the patch use options=noforcecopy.
$adpatch options=noforcecopy

Relinking

If you wish adpatch not do perform relinking you can use options=nolink.
$adpatch options=nolink

Generate Forms

If you wish adpatch not to generate the forms files you can specify options=nogenform.
$adpatch options=nogenform

Generate Reports

If you wish adpatch not to generate the report files you can specify options=nogenrep.
$adpatch options=nogenrep

You could specify multiple options at the command line using the , delimiter.

$adpatch options=hotpatch,nojcopy

1. Check the patch log file : Log file for adpatch or apps patch will exist as specified while running adpatch The default location is $APPL_TOP/admin/SID/log with name adpatch.log.
2. See on which worker the Patch has failed.
3. Check the Worker log for the failed Worker at the same location ($APPL_TOP/admin/SID/log) by the name adwork<worker_number>.log.

---