ADPatch Notes::

::PATCHING::

Patching is a program or set of instructions to fix a particular problem/ add a particular functionality in an existing program/product/software.

Patching Utilities:

1.    Auto Patch

2.   AD Merge Patch

3.   Patch Wizard

Patching Types:

1.   Bug Fix:

Fixing an existing issue

2.   New Feature:

Adds a new functionality

3.   Interoperability:

To make the current version of Oracle Application compatible with the Oracle database or Technology stack.

4.   Diagnostic:

To Determine the Source of an issue does not fix an issue

5.   Translation:

Contains files that have been translated from one language to another language

6.   Upgrade:

Fixes the problem with an upgrade or improves the performance of an upgrade from the previous release.

7.   Documentation:

 Updated oracle applications online help

Patch Format:

1. Individual Patch/One-off/Standalone:

This Patch is created to fix one particular bug

2. Mini-Pack:

This Patch is a cumulative patch to fix a particular product, that fixes most or all bugs related to that product.

3. Family Pack:

It is a group of Mini-Packs for related products that are bundled together including some individual packs.

4. Family Consolidated Upgrade Patch:

Need to Applied before starting upgrade. This Patch includes Performance improvements or bug fixes for process that run the upgrade.

5. Maintenance Pack:

It is a consolidation of all mini-packs where it updates a system to a new point of release of oracle applications to a higher level.

Patch Structure:

Patch Structure is formed by a top-level directory with several files nested under it. Top-Level directory is the Patch Number and various files in a Patch are:

Ø  Readme Files:

Readme.txt and Readme.html files describes:

• Software Requirements like patches to be applied before applying this  patch or what Application Release we should have.

• Disk Space Requirements

• Manual Steps to Run.

• Problems Fixed in this Patch

• Post Patching Steps.

Ø   Patch Driver Files:

Patch Driver files consist of various files:

• Copy Driver Files (Which Copies the product files)

• Database Driver Files (Which changes the database objects)

• Generate Driver Files (Which generates forms, graphics, messages and new objects)

• Nowadays most Patches contains one unified driver (where all driver files are placed in a single unit)

Ø  Replacement Files:

These files are listed in the copy driver which replaces the outdated forms, reports, html files, sql scripts, object modules with newer versions of files.

Ø  Metadata Files:

This contains a list of files included in this patch and the relationship between this patch and other patches. The information in this patch helps you to determine the pre-requisites patches and analyze the impact of patches in this system.

Auto Patch Set up Tasks::

•        Login as applmgr, as the owner of application files system.

•       Set up the environment.

•        Verify the database and oracle_home are correctly set.

•        Ensure sufficient Disk space, Temporary Disk space.

•        Copy the Patch files in the Patch Directory.

•        Read the Read me.txt files.

•        Back up file system and database.

•        Enable Maintenance Mode.

•        Count the invalid Objects.

•        Run auto patch (using command “adpatch”).

Auto Patch Prompts:

•       Auto Patch confirms whether our APPL_TOP is correct or not by prompting

Is this the correct APPL_TOP [YES]?

Our Default directory of APPL_TOP in file system is f:\oracle\app7appl

It Prompts us because Auto Patch log, output and restart files are located in this directory.

•       Auto patch asks for the name of the log file, by default it is adpatch.log. If the log file of the same name already exists then it appends this session at the end of log file indicating the “Start of the Auto Patch Session”.

Auto Patch records your session in a text file you specify.

 Enter your Auto Patch log file name or press [return] to accept the default log file name shown in brackets [adpatch.log]

•       Auto Patch Notifies you  by an email if an failure occurs in patching by asking,

Do you wish to activate this feature [Yes]?

•       It prompts to enter the batch size which sets the number of rows these scripts process, i.e. some sql scripts perform row set processing.

Please enter the batchsize [1000]:

•   Auto Patch asks for what type of files you currently have:-

Do you have files used for installing and upgrading the database in the APPL_TOP?

Do you have Java and HTML files for Self-Service Applications?

Do you have oracle application form files installed in this APPL_TOP?

Do you have Concurrent Program files installed in this APPL_TOP?

•      Auto Patch asks us to confirm the database and database home directory:

Is this the correct Database [Yes]?

•       Auto Patch needs password for your oracle System Schema in order to determine your installation Configuration.

Enter the Password for your ‘SYSTEM’ Oracle Schema: Manager

•       Auto Patch validates the usernames and passwords for application products

Enter the Oracle Password of Application Object Library [APPS]: APPS

•       Auto Patch asks for the directory that holds the Patch files, enter the directory where your oracle applications patch has been unloaded, or default from where we started Auto Patch.

The Default directory is [do1/appl/110/patch123]:

•       Auto Patch asks for the name of your patch driver file

Please enter the name of your Auto Patch driver File [patch.drv]:

•       Auto Patch tells you what patches you are about to apply, what products are going to be changed and asks if you want to continue:

• If the patch is not required to run serially you are prompted to enter the number of workers you want to use:

• ‘Compatible Parallel NO’ disables the parallel feature and runs jobs sequentially         ‘Compatible Parallel Yes’ is  the default for all Patches and Auto Patch runs exec and sql commands in parallel mode and asks for the number of workers to use :Default[6]

•       Auto Patch writes the informational message files(contains messages on actions auto patch did not perform) do1/appl/110/admin/V1103/log/adpatch.lgi

•       Auto Patch writes the log message files in                 do1/appl/110/admin/V1103/log/adpatch.log

   APPL_TOP         DATABASE NAME

•  After the Patch has been applied successfully, take the invalid objects count and if new invalid objects found, compile them so that no new invalids found.

Auto Patch Modes: 

•      Normal Mode (adpatch)

This mode is normal way of applying patches.

• Test Mode (adpatch apply=no)

It doesn’t apply the patch; it shows exactly what action is performed if it has been done.

Performs version verification and documents which files to be copied.

Determines which exec and sql commands to run

•      Pre-Install Mode(adpatch preinstall=y)

This mode is generally used during the upgrade process to update the AD utilities

It performs file version checking, file copy actions, and relink fnd and ad executables, save patch history information to file system

•       Non-Interactive Mode (adpatch defaultsfile=APPL_TOP/ADMIN/<SID>/def.txt)

This mode is used to apply patches without user intervention. Before we run Auto Patch in Non-Interactive Mode we need to create a Default file and verify whether default file is created.

Auto Patch Command Line Arguments:

•       apply= to run auto patch in test mode.

•       preinstall= to run auto patch in pre-install mode

•       patchtop= tell auto patch the top level directory for the current patch

•       driver= tell auto patch the name of the driver file

Auto Patch Options:

•       noautoconfig = tells auto patch to run autoconfig automatically.

•       nocompiledb = prevents ADPATCH from compiling the database objects.

•       nocompilejsp = prevents ADPATCH from compiling the JSP files.
• 
  
•       nocopyportion = tells auto patch whether to run commands normally found in copy driver.
• 
  
•       nodatabaseportion = tells auto patch whether to run commands normally found in database     driver.

•       nogenerateportion = tells auto patch whether to run commands normally found in generate driver.

•       hotpatch = tells auto patch to apply patch regardless of whether the oracle applications is in maintenance mode.

•       integrity = tells auto patch to perform patch integrity version checking, which checks the versions of files.

•       nomaintainmrc = tells auto patch whether to maintain the MRC (multiple Reporting currencies) schema after running actions that update the database.

•       noparallel = tells auto patch whether to run actions that update the database and actions that generates files in parallel.

•       noprereq = tells auto patch whether to perform pre-requisite patch checking.

•       novalidate= prevents ADPATCH from validating all schema connections.

AD Merge Patch: (admrgpch) :

       It merges multiple patches into a single patch reducing the patch application time. If all the source patches have split driver files the merged patch is a split driver file, if anyone of the source patch is a unified driver the merged patch is a unified driver

Syntax: - admrgpch –s <source directory> -d <destination directory>

Applied Patch Information :

       After applying patches use Oracle Applications Manager (OAM) to view the applied patch information. Patch History Files are Javaupdates <timestamp>.txt & Adpsv <timestamp>.txt                                AD_BUGS & AD_APPLIED_PATCHES tables are used to check whether the patches are applied. FND_INSTALL_PROCESS & AD_UTIL_PARAM tables are used to monitor the patches

Read More

Day-to-Day Activities::

Day to day activities:

Q)What is your day to day activity as an Apps DBA?

Ans:
As an Apps DBA we monitor the system for different alerts (Entreprise Manager or third party tools used for configuring the Alerts) Tablespace Issues, CPU consuption
Database blocking sessions..etc
Regular maintanance activies like cloning,patching,custom code migrations (provided by developers)
Working with user isses.

Q)How often Do you patch?

Ans: Usually for non-production the patching request comes aroung weekly 4-6 and the same patches will be applied to Production in the outage or maintanance window.
Production has weekly maintance window (Eg Sat 6PM to 9PM) where all the changes (patches) will applied on production.

Q)How often Do you clone?

Ans: Cloning happens biweekly or monthly depending on the organization requierement.

Q)What change control/management or CCB?

Ans: Every organization has change control process, Change control process is no change goes into production witout proir testing on non-production instance.
Eg: If a user encouters an issue in production instance and the fix for the issue is known, Still the fix should not be applied directly on production, as it is not tested.
The same fix need to be first applied on a non-prod instance where the similar issue is happening and test the issue and instance stability.
Once user is happy with the results the change or fix will be implemented to production with the approval from Change control Board, CCB is a managemant team who reviews all the changes being deployed to production,
Depending on the need and criticality and testing results they approve the change movement to production instance.

Q)How much time does it take to upgrade, clone ?
Ans: Clone usually takes around 48hrs to copy and configure and upgrade depends on the database size and module involved.
upgrade from 11.5.9 to 11.5.10.2 will take around 3-4 days and 11i to R12 upgrade will take around 4-5 days.

Q)What is the meaning QA,CRP,SIT,DEV,UAT,PRE-PROD,PROD Instance?QA - Tesing Instance
CRP - Conference Room Pilot
SIT - System Integration Testing
DEV - Developement
UAT- User Acceptence Testing
STAGE - Pre-production Instance
Prod - Production/actuall instance where the business is running

Q)What do we have in FND_NODES?Ans:
FND_NODES table contains information about node_names and services enabled on a node.
In multinode instance if you want to know which node is running what services, You can query the fnd_nodes and get that information.

Q)when do we run FND_CONC_CLONE.SETUP_CLEAN ?
Ans:
FND_NODES table contains node information, If you have cloned test instance from production still the node information of production will be present after clone in the test instance.
we use FND_CONC_CLONE.SETUP_CLEAN to cleanup FND_NODES table in the target to clear source node information as part of cloning.
Below syntax to execute:SQL> sho user
USER is "APPS"
SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN;
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.

This will delete all the entries in the fnd_nodes table, to populate it with target system node information, Run autoconfig on DB node and Applications node.

Q)How verify the sysadmin password from command line?Ans:
This utility can be used to verify the GUEST/ORACLE password
SQL>select fnd_web_sec.validate_login('SYSADMIN','<sysadmin_password>')from dual;If it returns Y then sysadmin password is correct
If it returns N then sysadmin password that we are using
Eg:
SQL> select fnd_web_sec.validate_login('SYSADMIN','SYSADMIN123') from dual;FND_WEB_SEC.VALIDATE_LOGIN('SYSADMIN','SYSADMIN123')
--------------------------------------------------------------------------------
N
SQL> select fnd_web_sec.validate_login('SYSADMIN','SYSADMIN') from dual;FND_WEB_SEC.VALIDATE_LOGIN('SYSADMIN','SYSADMIN')
--------------------------------------------------------------------------------
Y

Q)List out few Apps related tables ?
Ans:CONCURRENT REQUEST/PROGRAM/MANAGERS
-------------------
FND_CONCURRENT_QUEUES
FND_CONCURRENT_PROGRAMS
FND_CONCURRENT_REQUESTS
FND_CONCURRENT_PROCESSES
FND_CONCURRENT_QUEUE_SIZE FND/AOL Tables
--------------FND_APPL_TOPS
FND_LOGINS
FND_USER
FND_DM_NODES
FND_TNS_ALIASES
FND_NODES
FND_RESPONSIBILITY
FND_DATABASES
FND_UNSUCCESSFUL_LOGINS
FND_LANGUAGES
FND_APPLICATION
FND_PROFILE_OPTION_VALUES AD/Patches
-------------
AD_APPLIED_PATCHES
AD_PATCH_DRIVERS
AD_BUGS
AD_INSTALL_PROCESSES
AD_SESSIONS
AD_APPL_TOPS

Q) How To find the latest application version
select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE updated,ROW_SOURCE_COMMENTS "how it is done",BASE_RELEASE_FLAG "Base version" FROM AD_RELEASES where END_DATE_ACTIVE IS NULL

Q) How to find out if any patch except localisation patch is applied or not, if applied, that what all drivers it contain and time of it's application

select A.APPLIED_PATCH_ID, A.PATCH_NAME, A.PATCH_TYPE, B.PATCH_DRVIER_ID, B.DRIVER_FILE_NAME, B.ORIG_PATCH_NAME, B.CREATION_DATE, B.PLATFORM, B.SOURCE_CODE, B.CREATIONG_DATE, B.FILE_SIZE, B.MERGED_DRIVER_FLAG, B.MERGE_DATE from AD_APPLIED_PATCHES A, AD_PATCH_DRIVERS B where A.APPLIED_PATCH_ID = B.APPLIED_PATCH_ID and A.PATCH_NAME = ''

Q) How to know that if the patch is applied successfully, applied on both node or not, start time of patch application and end time of patch application, patch top location , session id ... patch run id */

select D.PATCH_NAME, B.APPLICATIONS_SYSTEM_NAME, B.INSTANCE_NAME, B.NAME, C.DRIVER_FILE_NAME, A.PATCH_DRIVER_ID, A.PATCH_RUN_ID, A.SESSION_ID, A.PATCH_TOP, A.START_DATE, A.END_DATE, A.SUCCESS_FLAG, A.FAILURE_COMMENTS from AD_PATCH_RUNS A, AD_APPL_TOPS B, AD_PATCH_DRVIERS C, AD_APPLIED_PATCHES D where A.APPL_TOP_ID = B.APPL_TOP_ID AND A.PATCH_DRIVER_ID = C.PATCH_DRIVER_ID and C.APPLIED_PATCH_ID = D.APPLIED_PATCH_ID and A.PATCH_DRIVER_ID in (select PATCH_DRIVER_ID from AD_PATCH_DRIVERS where APPLIED_PATCH_ID in (select APPLIED_PATCH_ID from AD_APPLIED_PATCHES where PATCH_NAME = '')) ORDER BY 3;

Q) Howto find the base application version

select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE when updated, ROW_SOURCE_COMMENTS "how it is done" from AD_RELEASES where BASE_RELEASE_FLAG = 'Y'

Q) How To find all available application version

select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE when updated, END_DATE_ACTIVE "when lasted", CASE WHEN BASE_RELEASE_FLAG = 'Y' Then 'BASE VERSION' ELSE 'Upgrade' END "BASE/UPGRADE", ROW_SOURCE_COMMENTS "how it is done" from AD_RELEASES

Q) How To get file version of any application file which is changed through patch application

select A.FILE_ID, A.APP_SHORT_NAME, A.SUBDIR, A.FILENAME, max(B.VERSION) from AD_FILES A, AD_FILE_VERSIONS B where A.FILE_ID = B.FILE_ID and B.FILE_ID = 86291 group by A.FILE_ID, A.APP_SHORT_NAME, A.SUBDIR, A.FILENAME

Q) How To get information related to how many time driver file is applied for bugs

select * from AD_PATCH_RUN_BUGS where BUG_ID in (select BUG_ID from AD_BUGS where BUG_NUMBER = ''

Q) How To find latest patchset level for module installed

select APP_SHORT_NAME, max(PATCH_LEVEL) from AD_PATCH_DRIVER_MINIPKS GROUP BY APP_SHORT_NAME
Q) How To find what is being done by the patch
select A.BUG_NUMBER "Patch Number", B. PATCh_RUN_BUG_ID "Run Id",D.APP_SHORT_NAME appl_top, D.SUBDIR, D.FILENAME, max(F.VERSION) latest, E.ACTION_CODE action from AD_BUGS A, AD_PATCH_RUN_BUGS B, AD_PATCH_RUN_BUG_ACTIONS C, AD_FILES D, AD_PATCH_COMMON_ACTIONS E, AD_FILE_VERSIONS F where A.BUG_ID = B.BUG_ID and B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID and C.FILE_ID = D.FILE_ID and E.COMMON_ACTION_ID = C.COMMON_ACTION_ID and D.FILE_ID = F.FILE_ID and A.BUG_NUMBER = '' and B.PATCH_RUN_BUG_ID = ' < > ' and C.EXECUTED_FLAG = 'Y' GROUP BY A.BUG_NUMBER, B.PATCH_RUN_BUG_ID, D. APP_SHORT_NAME, D>SUBDIR, D.FILENAME, E.ACTION_CODE

Q) How To find Merged patch Information from database in Oracle Applications

select bug_number from ad_bugs where bug_id in ( select bug_id from ad_comprising_patches where patch_driver_id =(select patch_driver_id from ad_patch_drivers where applied_patch_id =&n) );
Q) How toto know, what all has been done during application of PATCH Select J.PATCh_NAME, H.APPLICATIONS_SYSTEM_NAME Instance_Name, H.NAME, I.DRIVER_FILE_NAME, D.APP_SHORT_NAME appl_top,D.SUBDIR, D.FILENAME, max(F.VERSION) latest, E.ACTION_CODE action from AD_BUGS A, AD_PATCH_RUN_BUGS B,AD_PATCH_RUN_BUG_ACTIONS C, AD_FILES D, AD_PATCH_COMMON_ACTIONS E, AD_FILE_VERSIONS F, AD_PATCH_RUNS G,
AD_APPL_TOPS H, AD_PATCH_DRIVERS I, AD_APPLIED_PATCHES J where A.BUG_ID = B.BUG_ID and
B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID and C.FILE_ID = D.FILE_ID and E.COMMON_ACTION_ID = C.COMMON_ACTION_ID
and D.FILE_ID = F.FILE_ID and G.APPL_TOP_ID = H.APPL_TOP_ID and G.PATCH_DRIVER_ID = I.PATCH_DRIVER_ID and
I.APPLIED_PATCH_ID = J.APPLIED_PATCH_ID and B.PATCH_RUN_ID = G.PATCH_RUN_ID and C.EXECUTED_FLAG = 'Y' and
G.PATCH_DRIVER_ID in (select PATCH_DRIVER_ID from AD_PATCH_DRIVERS where APPLIED_PATCH_ID
in (select APPLIED_PATCH_ID from AD_APPLIED_PATCHES where PATCH_NAME = 'merged'))
GROUP BY J.PATCH_NAME, H.APPLICATIONS_SYSTEM_NAME, H.NAME, I.DRIVER_FILE_NAME, D.APP_SHORT_NAME, D.SUBDIR,
D.FILENAME, E.ACTION_CODE

Q) How to find out Patch level of mini Pack

Select product_version,patch_level from FND_PROUDCT_INSTALLATIONS where patch_level like '%&shortname%'; Replace short name by name of Oracle Apps Minipack for which you want to find out Patch level . ex.
AD - for Applications DBA
GL - for General Ledger
PO - Purchase Order

Q)List out Profile Options Useful for Oracle Apps DBA?

Here is the list of few profile options which Apps DBA use frequently.
**It is not necessary that you as Apps DBA must know all profile options**Applications Help Web Agent
Applications Servlet Agent
Applications Web Agent
Concurrent: Active Request Limit
Concurrent: Hold Requests
Concurrent: Multiple Time Zones
Concurrent: Report Access Level
Concurrent: Report Copies
Concurrent: Request priority
Database Instance
Enable Security Group
FND: Debug Log Filename
FND: Debug Log Level
Forms Runtime Parameters
Gateway User ID
ICX: Discoverer Launcher
ICX: Forms Launcher
ICX: Report Launcher
ICX: Limit Connect
ICX: Limit time
ICX: Session Timeout
MO Operating Unit
Node Trust Level
RRA: Delete Temporary Files
RRA: Enabled
RRA: Service Prefix
RRA: Maximum Transfer Size
Self Service Personal Home Page Mode
Sign-On: Audit Level
Signon Password Failure Limit
Signon Password Hard to Guess
Signon Password Length
Signon Password No Reuse
Site Name
Socket Listener Port
TCF: Host
TCF: Port
TWO TASK
Viewer: Text

Read More