Data Dictionary Curruption error in R12.2 adop

For Seeded table mismatch in any phase
==============================
Example

[apsid@host001 <RUN> ]$ adop phase=finalize

Enter the APPS password:
Enter the SYSTEM password:
Enter the WLSADMIN password:

 Please wait. Validating credentials...


RUN file system context file: /sid/inst/fs2/inst/apps/sid_host001/appl/admin/sid_host001.xml
PATCH file system context file: /sid/inst/fs1/inst/apps/sid_host001/appl/admin/sid_host001.xml
Execute SYSTEM command : df /sid/applmgr/fs1

************* Start of  session *************
 version: 12.2.0
 started at: Wed Oct 08 2014 11:33:11

APPL_TOP is set to /sid/applmgr/fs2/EBSapps/appl
  There is already a session which is incomplete. Details are:
        Session Id            :   47
        Prepare phase status  :   COMPLETED
        Apply phase status    :   ATLEAST ONE PATCH IS ALREADY APPLIED
        Cutover  phase status :   NOT COMPLETED
        Abort phase status    :   NOT COMPLETED
        Session status        :   FAILED
  Will continue with previous session
yes
  [START 2014/10/08 11:34:32] adzdoptl.pl run
    ADOP Session ID: 47
    Phase: finalize
    Log file: /sid/applmgr/fs_ne/EBSapps/log/adop/47/adop_20141008_113227.log
    [START 2014/10/08 11:34:42] finalize phase
      [UNEXPECTED]Dictionary Corrupted:
      [START 2014/10/08 11:34:56] Data Dictionary Curruption Details
        APPS            INV_MWB_CG_TRANSFER            V_20140912_2110 APPS            INV_MWB_CG_TRANSFER            V_20131106_0451 TS mismatch: 08-OCT-14 10:53:56 08-NOV-13 05:44:00
      [END   2014/10/08 11:34:57] Data Dictionary Curruption Details
      NOTE: Please contact Oracle Support and request them to open a bug against
            Oracle Application Install(166), Component Online Patching(OP).
      [UNEXPECTED]Finalize phase completed with errors/warnings. Please check logfiles
      Log file: /sid/applmgr/fs_ne/EBSapps/log/adop/47/adop_20141008_113227.log

adop exiting with status = 1 (Fail)


D_OWNER         D_NAME                         D_EDITION                 P_OWNER         P_NAME                         P_EDITION                 REASON
--------------- ------------------------------ ------------------------- --------------- ------------------------------ ------------------------- --------------------------------------------------
APPS            INV_MWB_CG_TRANSFER            V_20140912_2110           APPS            INV_MWB_CG_TRANSFER            V_20131106_0451           TS mismatch: 08-OCT-14 10:53:56 08-NOV-13 05:44:00

Elapsed: 00:00:12.00




Solution
======

SQL> select du.name d_owner, d.name d_name, d.defining_edition d_edition,
       pu.name p_owner, p.name p_name, p.defining_edition p_edition,
  2    3        case
  4           when p.status not in (1, 2, 4) then 'P Status: ' || to_char(p.status)
  5        else 'TS mismatch: ' ||
  6             to_char(dep.p_timestamp, 'DD-MON-YY HH24:MI:SS') || ' ' ||
  7             to_char(p.stime, 'DD-MON-YY HH24:MI:SS')
  8        end reason
  9  from sys."_ACTUAL_EDITION_OBJ" d,
 10       sys.user$ du,
 11       sys.dependency$ dep,
 12       sys."_ACTUAL_EDITION_OBJ" p,
 13       sys.user$ pu
 14  where d.obj# = dep.d_obj#
 15    and d.owner# = du.user#
 16    and p.obj# = dep.p_obj#
 17    and p.owner# = pu.user#
 18    and d.status = 1                                    -- Valid dependent
  and bitand(dep.property, 1) = 1                     -- Hard dependency
 19   20    and d.subname is null                               -- !Old type version
 21    and not(p.type# = 32 and d.type# = 1)               -- Index to indextype
 22    and not(p.type# = 29 and d.type# = 5)               -- Synonym to Java
 23    and not(p.type# in(5, 13) and d.type# in (2, 55))   -- TABL/XDBS to TYPE
 24    and (p.status not in (1, 2, 4) or p.stime <> dep.p_timestamp)
 25  /

D_OWNER                        D_NAME
------------------------------ ------------------------------
D_EDITION                      P_OWNER
------------------------------ ------------------------------
P_NAME                         P_EDITION
------------------------------ ------------------------------
REASON
--------------------------------------------------------------------
APPS                           INV_MWB_CG_TRANSFER
V_20140912_2110                APPS
INV_MWB_CG_TRANSFER            V_20131106_0451
TS mismatch: 08-OCT-14 10:53:56 08-NOV-13 05:44:00

SQL> @$AD_TOP/patch/115/sql/adzddtsfix.sql
exec dbms_utility.invalidate(794147,NULL,0);
exec sys.utl_recomp.recomp_parallel


SQL> exec dbms_utility.invalidate(794147,NULL,0);

PL/SQL procedure successfully completed.

SQL> exec sys.utl_recomp.recomp_parallel

PL/SQL procedure successfully completed.

SQL> select du.name d_owner, d.name d_name, d.defining_edition d_edition,
       pu.name p_owner, p.name p_name, p.defining_edition p_edition,
  2    3        case
  4           when p.status not in (1, 2, 4) then 'P Status: ' || to_char(p.status)
  5        else 'TS mismatch: ' ||
  6             to_char(dep.p_timestamp, 'DD-MON-YY HH24:MI:SS') || ' ' ||
  7             to_char(p.stime, 'DD-MON-YY HH24:MI:SS')
      end reason
  8    9  from sys."_ACTUAL_EDITION_OBJ" d,
 10       sys.user$ du,
 11       sys.dependency$ dep,
 12       sys."_ACTUAL_EDITION_OBJ" p,
 13       sys.user$ pu
 14  where d.obj# = dep.d_obj#
  and d.owner# = du.user#
 15   16    and p.obj# = dep.p_obj#
 17    and p.owner# = pu.user#
 18    and d.status = 1                                    -- Valid dependent
 19    and bitand(dep.property, 1) = 1                     -- Hard dependency
 20    and d.subname is null                               -- !Old type version
 21    and not(p.type# = 32 and d.type# = 1)               -- Index to indextype
 22    and not(p.type# = 29 and d.type# = 5)               -- Synonym to Java
 23    and not(p.type# in(5, 13) and d.type# in (2, 55))   -- TABL/XDBS to TYPE
 24    and (p.status not in (1, 2, 4) or p.stime <> dep.p_timestamp)
 25  /

no rows selected


For Custom Objects
==================
Inform to custom schema owner to check and drop those objects

opatch not working to apply April PSU patch:17572728 in weblogic 12.1.2.0.0 : setHomeDirs.sh: No such file or directory

Component :  Weblogic 12c
version: 12.1.2.0.0

Whenever we try to apply PSU on weblogic 12c (12.1.2), we have to apply the patch under MW_HOME (middleware home) ex: /sid/fmw/product/12  as ORACLE_HOME.

Issue:
======

$ /sid/fmw/product/12/OPatch/opatch lsinventory -jdk $JAVA_HOME
/sid/fmw/product/12/oracle_common/common/bin/setHomeDirs.sh: line 16: /sid/fmw/product/12/utils/config/10.3/setHomeDirs.sh: No such file or directory

Solution
========
If the directory  /sid/fmw/product/12/utils/config/10.3 is not created , first create the directory

and copy the setHomeDirs.sh as below

cp /sid/fmw/product/12//inventory/Templates/oracle_common/common/bin/setHomeDirs.sh /sid/fmw/product/12/utils/config/10.3


Issue 2:
========

./opatch lsinventory -detail -jdk $JAVA_HOME

Exception in thread "main" java.lang.NoClassDefFoundError: com/oracle/cie/gdr/external/InventoryException
        at oracle.opatch.OPatch.main(OPatch.java:548)
Caused by: java.lang.ClassNotFoundException: com.oracle.cie.gdr.external.InventoryException
        at java.net.URLClassLoader$1.run(URLClassLoader.java:366)
        at java.net.URLClassLoader$1.run(URLClassLoader.java:355)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.net.URLClassLoader.findClass(URLClassLoader.java:354)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:425)
        at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:308)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:358)
        ... 1 more
       
 Solution
 ========

 This issue occurs mostly if you try to apply higher version of OPatch in the lower version home.
 In my case , Have got this issue because i tried to update my OPatch with 13.1.0.0 , but this is only supported for 11.1.0.9
 You can get the version by reverting to older version and check the opatch lsinventory -detail -jdk $JAVA_HOME

 Ex: 

 bash-4.1$ ./opatch lsinventory -detail -jdk $JAVA_HOME
 Oracle Interim Patch Installer version 11.1.0.9.0
 Copyright (c) 2011, Oracle Corporation.  All rights reserved.


 Oracle Home       : /sid/fmw/product/12/oracle_common
 Central Inventory : /sid/fmw/oraInventory
    from           : /sid/fmw/product/12/oracle_common/oraInst.loc
 OPatch version    : 11.1.0.9.0
 OUI version       : 11.1.0.9.0
 OUI location      : /sid/fmw/product/12/oracle_common/oui
 Log file location : /sid/fmw/product/12/oracle_common/cfgtoollogs/opatch/opatch2014-06-12_00-15-11AM_1.log

 Patch history file: /sid/fmw/product/12/oracle_common/cfgtoollogs/opatch/opatch_history.txt


 OPatch detects the Middleware Home as "/sid/fmw/product/12"

 Lsinventory Output file location : /sid/fmw/product/12/oracle_common/cfgtoollogs/opatch/lsinv/lsinventory2014-06-12_00-15-11AM.txt

 --------------------------------------------------------------------------------
 Installed Top-level Products (1):

 Oracle AS Common Toplevel Component                                  11.1.1.2.0
There are 1 products installed in this Oracle Home.

Multiple Sets of Books is not compatible with MultiOrg

adadmin error
Error in addgbc():
Multiple Sets of Books is not compatible with MultiOrg


The issue is caused by the following setup:

Duplicate entries for BSDK Bestseller application in FND_PRODUCT_INSTALLATIONS table.

+++++++++++++++++++++++++++++++++++++++++++++++++++++

When the following queries are executed, two entries are found for the same application in FND_PRODUCT_INSTALLATIONS table with same application name and application id but different oracle id.

1 . SELECT APPLICATION_ID,COUNT(*)
FROM FND_PRODUCT_INSTALLATIONS
GROUP BY APPLICATION_ID
HAVING COUNT(*) > 1;
----------------------------------------------------------
Upon execution the result will be as follows:

APPLICATION_ID COUNT(*)
-------------- ----------
20023 2

----------------------------------------------------------

2. SELECT FPI.APPLICATION_ID, FPI.ORACLE_ID, FAL.APPLICATION_NAME
FROM FND_PRODUCT_INSTALLATIONS FPI, FND_APPLICATION_TL FAL
WHERE FPI.APPLICATION_ID= "<--this value will be obtained from the above query-->"
AND FPI.APPLICATION_ID = FAL.APPLICATION_ID;
----------------------------------------------------------
Upon execution the result will be as follows:-

APPLICATION_ID  ORACLE_ID APPLICATION_NAME
-------------- ---------- --------------------------------------------------
         20023        900 XXX Custom Application
         20023      20044 XXX Custom Application


Note: The data over here is customer specific, it may be different for case to case basis.
--------------------------------------------------------


Solution

SELECT LAST_UPDATE_DATE, APPLICATION_ID, STATUS, ORACLE_ID FROM
FND_PRODUCT_INSTALLATIONS WHERE APPLICATION_ID = <--this value is customer specific-->;

NOTE: There will be two duplicate entries in the FND_PRODUCT_INSTALLATIONS table.

SQL> /

LAST_UPDATE_DATE   APPLICATION_ID S  ORACLE_ID
------------------ -------------- - ----------
12-SEP-13                   20023 I        900
12-SEP-13                   20023 I      20044

3. In the FND_PRODUCT_INSTALLATIONS table, check for the STATUS = 'L' for each application which from the above query when executed.

4. Remove the application which has values for STATUS = 'L' and application_id same as the other one.

(STATUS =>, I means installed, L means custom, N means not installed, and S means installed as shared product in FND_PRODUCT_INSTALLATIONS table).

5. Take backup of FND_PRODUCT_INSTALLATIONS table (either with export or with create table)

6. SQL> delete from FND_PRODUCT_INSTALLATIONS where APPLICATION_ID= 20023 and ORACLE_ID=20044;

7. SQL> SELECT FPI.APPLICATION_ID, FPI.ORACLE_ID, FAL.APPLICATION_NAME FROM FND_PRODUCT_INSTALLATIONS FPI, FND_APPLICATION_TL FAL WHERE FPI.APPLICATION_ID=20023 AND FPI.APPLICATION_ID = FAL.APPLICATION_ID;

    APPLICATION_ID  ORACLE_ID APPLICATION_NAME
    -------------- ---------- --------------------------------------------------
             20023        900 XXX Custom Application
   
    SQL> SELECT APPLICATION_ID,COUNT(*)
    FROM FND_PRODUCT_INSTALLATIONS
    GROUP BY APPLICATION_ID
    HAVING COUNT(*) > 1;  2    3    4
   
    no rows selected

8. SQL> commit

Reference Note id: Error in addgbc(): Multiple Sets of Books is not compatible with MultiOrg (Doc ID 1163458.1)

ORACLE R12.2 ADOP LOGFILES and STATUS CHECK

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

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 sub directory 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

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

General Problems

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) zip of files in fs1/EBSapps/comn/clone/FMW/logs, fs2/EBSapps/comn/clone/FMW/logs. These log files are also useful for clone issues during the prepare phase. ( this captures cloning failures for WLS)
d) zip of directory $APPLRGF/TXK/ohsCloneLog ( this captures cloning failures for OHS )

you can check  for fsclone issues in  Prepare phase error link

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

ORACLE R12.2 ADOP ERRORS

adop errors at fsclone Stage.

Error :

1. Error occurred while executing <perl /sid/applmgr/fs2/EBSapps/appl/ad/12.0.0/patch/115/bin/txkADOPPreparePhaseSynchronize.pl
2. Error occurred while CONFIG_CLONE Patch File System from Run File System using command

Check the adop logfile for errors, and also verify the errors in fsclone logfiles
location : /sid/applmgr/fs_ne/EBSapps/log/adop/<adop session id>/prepare_20140110_003210/ohs1/TXK_SYNC_migrate_Fri_Jan_10_00_40_18_2014

Solutions for different issues.

Error 1:  (/sid/applmgr/fs_ne/EBSapps/log/adop/<adop session id>/adop_20140110_003210.log)

Running [filesystem]/fs1/EBSapps/comn/clone/FMW/pasteBinary.sh -javaHome [filesystem]/fs1/EBSapps/comn/clone/FMW/t2pjdk -al [filesystem]/fs1/EBSapps/comn/clone/FMW/FMW_Home.jar -tl [filesystem]/fs2/FMW_Home -invPtrLoc /etc/oraInst.loc -ldl [filesystem]/fs1/EBSapps/comn/clone/FMW/logs -silent true -executeSysPrereqs false
Script Executed in 7200015 milliseconds, returning status -1
Script timed out.

Solution

1. Open a new terminal.

2. Execute :
      export TIMEDPROCESS_TIMEOUT=-1
      export CONFIG_JVM_ARGS="-Xms1024m -Xmx2048m"

3. Execute :
      adop phase=fs_clone

Error 2:  (/sid/R122UPGR/fs1/EBSapps/comn/clone/FMW/logs)

FSCloneStageAppsTier_<datestamp>.log, the following error will be seen:

/d01/R122UPGR/fs1/FMW_Home/oracle_common/bin/copyBinary.sh -javaHome /d01/R122UPGR/fs1/EBSapps/comn/clone/FMW/t2pjdk -al /d01/R122UPGR/fs1/EBSapps/comn/clone/FMW/FMW_Home.jar -smw /d01/R122UPGR/fs1/FMW_Home -ldl /d01/R122UPGR/fs1/EBSapps/comn/clone/FMW/logs -invPtrLoc /etc/oraInst.loc -silent true
Script Executed in 28420 milliseconds, returning status 255
Script failed, exit code 255

In:  /d01/R122UPGR/fs1/EBSapps/comn/clone/FMW/logs the following error is seen:

SEVERE : Dec 4, 2013 6:17:41 PM - ERROR - CLONE-20275   Insufficient space to create /tmp/CLONINGCLIENT3005869153400434600

Solution

Create space in /tmp or assign more disk storage to /tmp.
OR
assign the diffrent mountpoint as temp directory

export T2P_JAVA_OPTIONS="-Djava.io.tmpdir=/<sid>/applmgr/temp"

Error 3 (/sid/applmgr/fs_ne/EBSapps/log/adop/<adop session id>/prepare_20140110_003210/ohs1/TXK_SYNC_migrate_Fri_Jan_10_00_40_18_2014/wlsTechStack_FSCloneStageAppsTier_01100041.log)

Edit session is enabled in domain. Current Editor = weblogic
Please activate all of your changes before proceed clone

Solution

Login to weblogic console, in home page click on Release configuration button and rerun the adop preprae phase

Error 4 (/sid/applmgr/fs_ne/EBSapps/log/adop/<adop session id>/adop_20140110_003210.log)

adop=prepare encountering [ERROR] Error: While executing on all other nodes

Solution

Since the all nodes are not currently implemented the issue is cause by this autoconfig variable setting.

<shared_file_system oa_var="s_shared_file_system">false </shared_file_system>

It seems your half way to enabling another app node hence this setting

Since there is no extra app node registered.
What happens is that adop then looks for another app node and cannot find the node and it fails .

Please change the setting to false, run autoconfig, and re-test adop prepare

Issue should be resolved