Apply patch by adpatch or opatch

•  adpatch to apply EBS patches

- If you don’t see the “AutoPatch is complete” message at the end of the Autopatch log file, Autopatch did not complete successfully.

- The autopatch log is in below directory with default name adpatch.log:
$APPL_TOP/admin/<SID>/log

File adpatch.lgi is the file that has information (about patch applied), for example, about files
that were not applied because of some kind of problem that occurs or because patches had been applied already. See below UPDATES 2.

Ignore warning messages. See Doc ID 401424.1  (for R12.1.3)

- Apply the patch on the CM node first. The patching process on other nodes will skip some steps that already ran in CM node.

- To apply GDF patches, maintenance mode is not necessary.
$ cd XXXXXXX
$ adpatch options=hotpatch

- For patch analysis, run
$ cd XXXXXXX
$ adpatch apply=n options=hotpatch

- Merge multiple patches: syntax
$ admrgpch -s /path/to/sourceFolder -d /patch/to/mergedPatch -manifest manifest_list.txt
After that, folder mergedPatch can be zipped up and copied to other servers. File manifest_list.txt shall list all .zip files with full patch.

- If patch failed with below similar error, you can answer No or answer Yes to finish the process. Then come back to fix the individual error.

The following Oracle Forms objects did not generate successfully:
ar      forms/US        ARXCWMAI.fmx
An error occurred while generating Oracle Forms files.
Continue as if it were successful [No] : No 
Freeing includes hash table
Freeing fixes hash table
 Freeing basedons hash table
Freeing entities hash table

If you answer NO, you have to re-run adpatch after you have fixed (i.e. compiled successfully) what failed. Re-run adpatch :
$ adpatch
... ...
Your previous AutoPatch session did not run to completion.
Do you wish to continue with your previous AutoPatch session [Yes] ? Yes
... ...

It will NOT ask you any passwords and will re-try what failed in previous session:

Assigned: file ARXCWMAI.fmx on worker  1 for product ar  username AR.
Completed: file ARXCWMAI.fmx on worker  1 for product ar  username AR.
... ...
Running AutoConfig ...
Running AutoConfig on : All products ...
... ...
Saving Patch History information to Database...
 Updating the current-view snapshot...
 ... ... ...

- To check if an EBS patch is installed or not, run a single SELECT statement:

SQL> select * from apps.ad_bugs where bug_number = 1313962;

- BTW, as sysdba use sql to check if a database patch is installed or not
SQL> set serveroutput on;
SQL> exec dbms_qopatch.get_sqlpatch_status;
or
SQL> select xmltransform(dbms_qopatch.is_patch_installed('31219939'), dbms_qopatch.get_opatch_xslt) "Patch installed?" from dual;
SQL> select * from sys.registry$history;

UPDATES 1:
Below query show status on each node if patch is not included in a merged patch. It is tested in R12.1.

SQL> SELECT aap.patch_name, aat.name, apr.end_date
FROM ad_applied_patches aap,
ad_patch_drivers apd,
ad_patch_runs apr,
ad_appl_tops aat
WHERE aap.applied_patch_id = apd.applied_patch_id
and apd.patch_driver_id = apr.patch_driver_id
and aat.appl_top_id = apr.appl_top_id
and aap.patch_name = '9239090';

UPDATES 2:
xxxxx.lgi file reports files that were not applied. For example, adpatch only compares the package versions on file system and does not check the version inside the database. If for some reason a newer one was copied to the file system,  the patch file will not get applied.

$  grep apgdfalb $APPL_TOP/admin/${TWO_TASK}/log/16981628.lgi
Will not apply apgdfalb.pls: Patch file is older.
  Patch  : /path/to/16981628/ap/patch/115/sql/apgdfalb.pls, v120.1.12010000.68
  On-Site: $AP_TOP/patch/115/sql/apgdfalb.pls, v120.1.12010000.72
Checkfile            sql          ap      apgdfalb.pls

But, the package version in the database is much lower. So I had to manually run file apgdfalb.pls in Sql*Plus to get the newer one.

SQL> select text from dba_source where name='AP_ACCTG_DATA_FIX_PKG' and line=2;
/* $Header: apgdfalb.pls 120.1.12010000.43 2011/08/24 05:55:58 kpasikan ship $ */

SQL> @/path/to/16981628/ap/patch/115/sql/apgdfalb.pls
Package created.

• opatch to apply patches to ORACLE_HOME

- Go to the directory first
$ cd 7120514
- Apply the patch
$ opatch apply
(or,  $ opatch apply -invPtrLoc $ORACLE_HOME/oraInst.loc )

Notes:
(1) Use below line to get opatch version:
$ ./opatch -version

(2) While applying the patch, if OPatch detects a different platform ID in the applying instance, then do the below work-around to make Opatch work fine. For example, if the patch is available for Linux86 but the instance OS at Linux AMD x86-64, set an env variable:

$ export OPATCH_PLATFORM_ID=64

(3) Steps to Rollback the patch
1. Stop all services
2. Use the following command:
$ cd 7120514
$ opatch rollback -id 7120514

UPDATES:
1. If a patch is for a lower version of product or is using a wrong ORACLE_HOME, opatch may not apply it.  For example, when I wrongly downloaded zip file of patch 12965674 for 10.1.3.4 and then applied it to a 10.1.3.5 ORACLE_HOME, it just gave message:

SKIPPING_COMPONENT=oracle.j2ee,10.1.3.4.0
None of the patch actions is applicable to the Oracle Home.

OPatch will not apply this patch.
OPatch succeeded.

2. When the folder of the unzipped files had permission issue (owned by a different user), opatch stopped in the middle with below message. I answered N to apply it and then rolled it back successfully.

$ opatch apply
. . .  . . .  . . .
Replying 'Y' will terminate the patch installation immediately. It WILL NOT restore any updates that have been performed to this point. It WILL NOT update the inventory.
Replying 'N' will update the inventory showing the patch has been applied.
NOTE: After replying either 'Y' or 'N' it is critical to review:
      My Oracle Support Note 312767.1 How to rollback a failed Interim patch installation.
Do you want to STOP?
Please respond Y|N >
N
Running make for target ias_install.

Inventory is good and does not have any dangling patches.

Updating inventory...

Verifying patch...
  Verifying that patch ID is in Oracle Home inventory.
  Verifying archive files.

Comparing "/path/to/11780669/files/lib/librw.a/rwadr.o" and "$ORACLE_HOME/.patch_storage/verify/lib/librw.a/rwadr.o"
OPATCH_JAVA_ERROR: Unable to verify if patch has been applied.

Exception in thread "main" java.io.FileNotFoundException: /path/to/11780669/files/lib/librw.a/rwadr.o (Permission denied)
        at java.io.FileInputStream.open(Native Method)
        at java.io.FileInputStream.<init>(Unknown Source)
        at java.io.FileInputStream.<init>(Unknown Source)
        at opatch.VerifyPatch.verify(VerifyPatch.java:663)
        at opatch.VerifyPatch.main(VerifyPatch.java:933)Verification of the patch failed.

ERROR: OPatch failed as verification of the patch failed.

$ opatch rollback -id 11780669
. . .  . . .  . . . 
Is this system ready for updating?
Please respond Y|N >
Y
Removing patch 11780669...

Restoring archive files...
Running make for target  ias_install.
Updating inventory...
Backing up comps.xml ...
Inventory is good and does not have any dangling patches.
OPatch succeeded.

Verify if a server patch is installed or not

When you install a server patch on your database server or Discoverer server, you need to check if the patch or pre-requisite patch has been installed or not. Here is how you can do that:

$ cd $ORACLE_HOME/OPatch
$ ./opatch lsinventory -invPtrLoc $ORACLE_HOME/oraInst.loc | grep 6472361

If this command returns anything, the patch 6472361 may be installed on your server.

Below line also gives the list of installed patches. But, it does not give the patch number for the Top-level upgrade (such as 5337014 for upgrading 10.2.0.1.0 to 10.2.0.3.0. Without it, it is not easy to identify the binary file in download).

$ ./opatch lsinventory -oh $ORACLE_HOME

Installed Top-level Products (2):

Oracle Database 10g 10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 2 10.2.0.3.0
There are 2 products installed in this Oracle Home.

Interim patches (3) :

Patch 6596361 : applied on Fri Nov 23 10:24:05 MST 2007
Created on 20 Nov 2007, 02:42:09 hrs PST8PDT,M3.2.0,M11.1.0
Bugs fixed:
6596361

Patch 5556081 : applied on Mon Sep 17 14:21:59 MST 2007
Created on 9 Nov 2006, 22:20:50 hrs PST8PDT
Bugs fixed:
5556081

Patch 5557962 : applied on Mon Sep 17 14:21:39 MST 2007
Created on 9 Nov 2006, 23:23:06 hrs PST8PDT
Bugs fixed:
4269423, 5557962, 5528974

Rac system comprising of multiple nodes
Local node = dssdb1p
Remote node = dssdb2p

Script to find the trace file by Request ID

This script will identify the trace file on the database server from a Request ID if it generates a trace file. It also gives the session information if the session is still running. Usually if the trace option is turned on, a trace file is created by the Request.

SQL>  SELECT 'Request id: '|| request_id ,
 'Trace id: '|| oracle_Process_id,
 'Trace Flag: '|| req.enable_trace,
 'Trace Name: '|| dest.value || '/' || lower(dbnm.value) || '_ora_' || oracle_process_id || '.trc',
 'Prog. Name: '|| prog.user_concurrent_program_name,
 'File Name: '|| execname.execution_file_name || ' ' || execname.subroutine_name ,
 'Status : '|| decode(phase_code,'R','Running') || '-' || decode(status_code,'R','Normal'),
 'SID Serial: ' || ses.sid || ',' || ses.serial#,
 'Module : ' || ses.module
 from apps.fnd_concurrent_requests req, v$session ses,
 v$process proc, v$parameter dest, v$parameter dbnm,
 apps.fnd_concurrent_programs_vl prog, apps.fnd_executables execname
 where req.request_id = 31414905
 and req.oracle_process_id=proc.spid(+)
 and proc.addr = ses.paddr(+)
 and dest.name='user_dump_dest'
 and dbnm.name='db_name'
 and req.concurrent_program_id = prog.concurrent_program_id
 and req.program_application_id = prog.application_id
 and prog.application_id = execname.application_id
 and prog.executable_id=execname.executable_id ;
 

Similar information can be got from two simple queries:

SQL> select value from v$parameter where name = 'user_dump_dest';
VALUE
----------------------------------------------------------------------
/path/to/udump


SQL> select oracle_process_id, enable_trace from fnd_concurrent_requests where request_id = 5589516;
ORACLE_PROCESS_ID  ENABLE_TRACE
--------------------------  ------------------
11662                            N

Two RMAN errors during restore

1. "restore database from tag=TAG20080113T210205" got errors:

ORA-19870: error reading backup piece bk_a2j634ef_1_1_643928527
ORA-19507: failed to retrieve sequential file, handle="bk_a2j634ef_1_1_643928527", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Backup file not found in NetBackup catalog

Even "RMAN> list backup of database completed after 'sysdate - 16';" show the backuppiece (and the TAG) is in status "AVAILABLE ".

In this case, most likely the backup tape has been recycled. You may not have chance to restore the database from this backup set.

2. If you see below errors:

......
channel ch02: reading from backup piece pejdb4dd_1_1
ORA-19870: error reading backup piece pajdb2gd_1_1
ORA-19507: failed to retrieve sequential file, handle="pajdb2gd_1_1", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Failed to open backup file for restore.

ORA-19870: error reading backup piece ppjddfcb_1_1
ORA-19507: failed to retrieve sequential file, handle="ppjddfcb_1_1", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Failed to process backup file

Most likely RMAN can not locate the next tape. You need to make sure all tapes for the backup set are in the tape drive.

Delete stats to speed up RMAN backup

There is a way that may speed up the RMAN backing up in 10G.


1. Run the following as sysdba on the target database:
SQL>exec dbms_stats.DELETE_TABLE_STATS('SYS','X$KCCRSR');

2. Implement the following sql statement as the first entry in your RMAN backup script:
run {sql "alter session set optimizer_mode=RULE";
...
}

Note: the 1st statement will cleanup old stats. You may want to wait until rman finishes if it is running. You cannot do anything to improve performance of the currently running rman session.