Best way for Debugging ADOP issues

Adop in EBS R12.2 is a messy thing, we will be welcome every time with new type of error, to analyze the error is also a big job, for that purpose oracle has very good tools and techniques.

Hope this will be helpful if you ever encountered in some unknown issue with adop.

To debug Online Patching issues which utilize the adop (AD Online Patching) tool we need to collect adop log files located in the <INSTALL BASE>/fs_ne/EBSapps/log/adop directory 
e.g. /u01/PROD/fs_ne/EBSapps/log/adop

Each cycle of adop creates a subdirectory corresponding to the patch session ID, e.g.

/u01/PROD/fs_ne/EBSapps/log/adop/1
/u01/PROD/fs_ne/EBSapps/log/adop/2
etc..etc

When running adop the on-screen terminal output will mention which adop session ID  is in use.
e.g. /u01/PROD/fs_ne/EBSapps/log/adop/9/apply_20121011_024437

Online Patching Log Analyzer Utility (this new feature is delivered by AD and TXK C Delta 4 patches)

This utility analyzes adop log directories for errors and warnings, and displays messages to help the user quickly identify any problems that may have occurred during an adop run. It thereby offers an alternative to reviewing log files manually.

The Log Analyzer utility can be run without options:

    To scan all log directories of the latest adop session for errors:
    $ adopscanlog

The utility can also be run with various options. Examples include:

    To scan log directories relating to the latest run of adop in the latest session:
    $ adopscanlog -latest=yes
    To scan log directories relating to the latest run of the specified phase, in the latest session:
    $ adopscanlog -latest=yes -phase=<phase_name>
    To scan all log directories of a given session (represented by a session_id) for errors:
    $ adopscanlog -session_id=<number>
    To see a complete list of supported parameters:
    $ adopscanlog -help

STEP 1:  Obtain all of the log files within the session ID directory. It is best to obtain a zip of the entire directory.

e.g. obtain a zip of /u01/PROD/fs_ne/EBSapps/log/adop/9

The session ID directory will contain:

a) A trace file for each phase;

e.g.

adop_20130316_085026.log
adop_20130316_091340.log
adop_20130316_210950.log  

b) Logs grouped in phase directories

e.g.

prepare_20130316_085026
apply_20130316_091340
cutover_20130316_210950

You can see the timestamps match between each log directory and the respective trace file.
prepare_20130316_085026 directory matches adop_20130316_085026.log 
apply_20130316_091340 directory matches adop_20130316_091340.log 
etc etc

The same goes for fs_clone activities. i.e. you will see something like this
fs_clone_20130319_233614 --> this is a directory
adop_20130319_233614.log 

In almost all cases, when debugging an adop failure the patch log directory will contain the information you need to determine root cause
e.g.
/u01/PROD/fs_ne/EBSapps/log/adop/2/cutover_20130316_210950/

In some cases the onscreen error will indicate which subroutine has failed. You will likely find a subdirectory containing log files which matches the failing routine. This should be where you focus your attention
e.g.
/u01/PROD/fs_ne/EBSapps/log/adop/2/cutover_20130316_210950/VIS_ufc/TXK_CTRL_forceshutdown_Sat_Mar_16_21_14_02_2013

STEP 2: Run the following SQL statements

This will show you the status for each adop phase along with its corresponding session id. This is effectively a history of online patching in an environment. 

a) select ADOP_SESSION_ID,PREPARE_STATUS,APPLY_STATUS,FINALIZE_STATUS,CUTOVER_STATUS,CLEANUP_STATUS,ABORT_STATUS,STATUS,ABANDON_FLAG,NODE_NAME from AD_ADOP_SESSIONS order by ADOP_SESSION_ID;

Note: 
       Y denotes that the phase is done
       N denotes that the phase has not been completed
       X denotes that the phase is not applicable
       R denotes that the phase is running (in progress)
       F denotes that the phase has failed
       P (is applicable only to APPLY phase) denotes at least one patch is already applied for the session id
       C denotes that the status of this ADOP session has completed

Note: Numerical statuses are only relevant for the cutover phase...

These status values are updated when a step has completed, and are as follows:

N denotes that the phase has not been completed
0 denotes that cutover/force_shutdown has started
1 denotes the "force_shutdown" step has successfully executed
3 denotes the "db_cutover" step has successfully executed
4 denotes the "fs_cutover" step has successfully executed
6 denotes the "force_startup" step has successfully executed 
Y denotes that the phase is done

cutover statuses

cutover_status='Y' 'COMPLETED'
cutover_status not in ('N','Y','X') and status='F' 'FAILED'
cutover_status='0' 'CUTOVER STARTED'
cutover_status='1' 'SERVICES SHUTDOWN COMPLETED'
cutover_status='3' 'DB CUTOVER COMPLETED'
cutover_status='D' 'FLIP SNAPSHOTS COMPLETED'
cutover_status='4' 'FS CUTOVER COMPLETED'
cutover_status='5' 'ADMIN STARTUP COMPLETED'
cutover_status='6' 'SERVICES STARTUP COMPLETED'
cutover_status='N' 'NOT STARTED'
cutover_status='X' 'NOT APPLICABLE'

STEP 3: Check the current status of the adop cycle

Source the run filesystem environment file and run command
adop -status

usage
adop -status  generates a summary report
adop -status <sessionID> generates a summary report for that session ID
adop -status -detail generates a detailed report

 STEP 4: Check AD and TXK C Patch levels

 SELECT codelevel FROM AD_TRACKABLE_ENTITIES WHERE abbreviation in ('txk','ad');

STEP 5: Check middle tier technology patch levels

/u01/VIS/fs1/EBSapps/appl/ad/12.0.0/sql> perl $FND_TOP/patch/115/bin/TXKScript.pl -script=/u01/VIS/fs1/EBSapps/appl/fnd/12.0.0/patch/115/bin/txkInventory.pl -txktop=$APPLTMP -contextfile=$CONTEXT_FILE -appspass=apps -outfile=/tmp/Inventory_Report.html

---

Appendix

A.1 General problems with Finalize phase and Abort command

For phase=finalize issues run the following command and attach the adzdshowlog.out file generated:

sqlplus <apps_schema_name>/<apps_Schema_password> @$AD_TOP/sql/ADZDSHOWLOG.sql

Note: the contents of the table will be truncated every time cleanup/prepare phase is run.
Older log information is stored in adzdshowlog.out

or

select * from ad_zd_logs order by log_sequence desc;

A.2 'Duplicate keys found' during Finalize

ERROR at line 1:
ORA-20001: Error: while calling ad_zd.finalize .ORA-01452: cannot CREATE UNIQUE
INDEX; duplicate keys found
ORA-06512: at line 8

Run the following:

sqlplus <apps_schema_name>/<apps_Schema_password> @$AD_TOP/sql/ADZDSHOWLOG.sql

The output will highlight the unique index which is failing. You can then use the following sql to identify the duplicate key values

select <list of columns for which unique index creation failed>, count(*)
from <schema_name>.<table_name> group by <list of columns again> having count(*)>1
e.g.
select REPRESENTATION_CODE, TRX_NUMBER#2, ORG_ID, count(*) from OKL.OKL_TRX_CONTRACTS_ALL group by REPRESENTATION_CODE, TRX_NUMBER#2, ORG_ID having count(*)>1

A.3 Problems with Online Enablement

For failed attempts at Online Enablement i.e. failures of patch 13543062, gather the following information:
1. Patch log
2. Worker logs
3. Output of 
select * from ad_zd_logs order by log_sequence desc;

4. output from:
@$AD_TOP/sql/ADZDSHOWDDLS.sql

If enablement appears to be having performance issues or seems to have hanged you can run the following script to determine if enablement is progressing or has hanged:
select count(1) from ad_zd_logs;
Run the scripts every 5 or  10 minutes. If the count is increasing then enablement is progressing.If enablement is progressing but very slowly ensure the DB initialization parameters are set as per the DB 11.2.0.3 and 12.2 requirements.

A.4 To show which patches were applied in each ADOP_SESSION_ID (patching cycle)

select * from ad_adop_session_patches order by end_date desc;

or

set pagesize 200;
set linesize 160;
column adop_session_id format 999999999999;
column bug_number format a15;
column status format a15;
column applied_file_system_base format a23;
column patch_file_system_base format a23;
column adpatch_options format a15;
column node_name format a15;
column end_date format a15;
column clone_status format a15;

select ADOP_SESSION_ID, BUG_NUMBER, STATUS, APPLIED_FILE_SYSTEM_BASE, PATCH_FILE_SYSTEM_BASE, ADPATCH_OPTIONS, NODE_NAME, END_DATE, CLONE_STATUS
from ad_adop_session_patches
order by end_date desc;

Note: STATUS
N - Not Applied In the current node but applied in other nodes
R - Patch Application is going on.
H - Patch failed in the middle. (Hard Failure)
F - Patch failed in the middle but user tried to skip some failures.
S - Patch Application succeeded after skipping the failed jobs.
Y - Patch Application succeeded.

C - Reserved for clone and config_clone. Indicates clone completed

A.5 Issues with FS_CLONE

a) select ADOP_SESSION_ID,BUG_NUMBER,CLONE_STATUS,STATUS,NODE_NAME from AD_ADOP_SESSION_PATCHES order by ADOP_SESSION_ID;

b) sqlplus <apps_schema_name>/<apps_Schema_password> @$AD_TOP/sql/ADZDSHOWLOG.sql

c) following files/dirs

• $COMMON_TOP/clone/FMW/logs/CLONE<timestamp>.error
• $COMMON_TOP/clone/FMW/logs/CLONE<timestamp>.log
• A zip of files $INST_TOP/admin/log/clone directory
• A zip of directory $APPLRGF/TXK/ohsCloneLog ( this captures cloning failures for OHS )

d) RUN and PATCH context files from all the nodes

e) if one of the scripts fails within fs_clone you can add some parameters and run it manually to obtain extra debug:

here are some examples

note the addition of -silent false and -debug true

/u01/applmgr/fs2/FMW_Home/oracle_common/bin/pasteConfig.sh -javaHome /u01/applmgr/fs2/EBSapps/comn/util/jdk64 -al /u01/applmgr/fs1/EBSapps/comn/clone/FMW/WLS/EBSdomain.jar -tdl /u01/applmgr/fs2/FMW_Home/user_projects/domains/EBS_domain_sa3 -tmw /u01/applmgr/fs2/FMW_Home -mpl /u01/applmgr/fs1/EBSapps/comn/clone/FMW/WLS/plan/moveplan.xml -ldl /u01/applmgr/fs1/inst/apps/sa3_spitfire2/admin/log/clone/wlsT2PApply -silent false -domainAdminPassword /u01/applmgr/fs1/EBSapps/comn/clone/FMW/tempinfo.txt -debug true

or

/oa12uat/R122/fs2/FMW_Home/oracle_common/bin/pasteConfig.sh -javaHome
/oa12uat/R122/fs2/EBSapps/comn/util/jdk32 -al
/oa12uat/R122/fs1/EBSapps/comn/clone/FMW/OHS/ohsarchive.jar -mpl
/oa12uat/R122/fs1/EBSapps/comn/clone/FMW/OHS/moveplan.xml -ldl
/oa12uat/R122/fs1/inst/apps/oa12uat_coreuatap01/admin/log/clone/ohsT2PApply
-silent false -tcn EBS_web_oa12uat -tih
/oa12uat/R122/fs2/FMW_Home/webtier/instances/EBS_web_VIS_OHS1 -tinEBS_weVIS_OHS1 -toh /oa12uat/R122/fs2/FMW_Home/webtier -debug true

review the log and out files as well as the console output

A.6 ADOP Remote Invocation Issues and running ADOP on Multi Web Node Systems

Upload the following log files:

$APPL_TOP/admin/<SID>_patch/log/remote_execution_result.xml
$APPL_TOP/admin/<SID>/log/remote_execution_result.xml

Adop log directory corresponding to the failed adop id

Other key things to consider in multi web node instances.

- SSH should be enabled to allow communication from the primary node to the secondary nodes. Use txkRunSSHSetup.pl to enable SSH.

- If SSH is not enabled pay very special attention to the specific requirements for running adop in this case (see the maintenance guide)

- When patching ensure you copy patches to the patch home directory of all application tiers e.g. fs_ne/EBSapps/patch

- When debugging issues make sure to you review the adop logs on all application tiers.

Reference : 12.2 E-Business Suite - Collecting Online Patching and fs_clone Log Files (Doc ID 1542162.1)