Apply another Patch while Current patch failed in the Middle.

*******************************
How to apply another Patch while one Patch is failed and other one is pre-req for the failed Patch.
Here are the details please follow carefully. Make a note that this method is not officially supported...
*******************************

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. sqlplus applsys/apps
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. sqlplus applsys/apps
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/kirang/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/kirang
b. mv restart restart_back
c. mkdir restart
5. Drop the FND_INSTALL_PROCESSES table and the AD_DEFERRED_JOBS table.
a. sqlplus applsys/apps
b. drop table FND_INSTALL_PROCESSES;
c. drop table AD_DEFERRED_JOBS;
6. Apply the new patch.
7. Restore the .rf9 files located in $APPL_TOP/admin/kirang/restart_back
directory.
a. cd $APPL_TOP/admin/kirang
b. mv restart restart_
c. mv restart_back restart
8. Restore the FND_INSTALL_PROCESSES table which is owned by the APPLSYS
schema.
a. sqlplus applsys/apps
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. sqlplus applsys/apps
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.

ISSUE: During OATM Migration Invalid Indexes holding further progress of Migration.

REPRODUCE the ERROR:

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

Running the OATM Migration utility:

1. Migration Sizing Reports

2. Create New Tablespaces

3. Generate Migration Commands

4. Execute Migration Commands

5. Run Migration Status Reports

6. Run Post Migration Steps

7. Run Customization Steps

8. Run Migration in Batch Mode

[Q]uit [N]ext

Choosing the option : 3 "Generate Migration Commands"

Choose the Option : 1 below : "Invalid Indexes Report."

1. Invalid Indexes Report. Please correct/drop these before

generating migration commands

2. Generate migration commands for all schemas

3. Generate migration commands for a list of schemas

[Q]uit [B]ack [N]ext

Please enter your option - 1

PAGE: 1

Invalid Indexes Report. Please correct/drop these before generating migration co

Report Date : April 6, 2012

Index Owne Index Name Index Type Status

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

CS CS_FORUM_MESSAGES_TL_N4 CONTEXT FAILED

CS CS_FORUM_MESSAGES_TL_N4 CONTEXT POPULATE

CS CS_INCIDENTS_ALL_TL_N1 CONTEXT FAILED

CS CS_INCIDENTS_ALL_TL_N1 CONTEXT POPULATE

CS CS_KB_ELEMENTS_TL_N2 CONTEXT FAILED

CS CS_KB_ELEMENTS_TL_N2 CONTEXT POPULATE

CS CS_KB_SETS_TL_N3 CONTEXT FAILED

CS CS_KB_SETS_TL_N3 CONTEXT POPULATE

Report created /kirang/applmgr/11510/admin/kirang/log/fndinvld.txt

Need to follow Note : 312640.1 >> The step 2. Create the CS Text index preferences and indexes (7, 8,9, 10 & 11)

Create the CS Text index preferences and indexes (7, 8, 9, 10 & 11):

- Log into Unix as the Applications user, and change to $CS_TOP/patch/115/sql

- Log into SQL*Plus as the apps user

- Run the script cskbctxp.sql and pass the apps CS and CTXSYS usernames (e.g.

@cskbctxp apps cs ctxsys).

This creates the Text index preferences for the CS Text indexs.

- Run the script cskbsetx.sql and pass the cs schema name(i.e @cskbsetx CS).

This creates the CS_KB_SETS_TL_N3 Text index.

- Run the script cssrcrix.sql and pass the apps and cs schema names (i.e @cssrcrix apps CS).

This creates the SUMMARY_CTX_INDEX Text index.

- Run the script cskbelex.sql and pass the cs schema name(i.e @cskbelex CS).

This creates the CS_KB_ELEMENTS_TL_N2 Text index.

- Run the script cskbcatx.sql and pass the cs schema name(i.e @cskbcatx CS).

This creates the CS_KB_SOLN_CAT_TL_N1 Text index.

- Run the script cskbforx.sql and pass the cs schema name(i.e @cskbforx CS).

This creates the CS_FORUM_MESSAGES_TL_N4 Text index.

- NOTE the script csksynib.pls creates the package CS_KB_CONC_PROG_PKG. This is required

for the Concurrent Program submission that is used to execute the index creations for

the cskbsetx.sql, cskbforx.sql, cskbelex.sql, and cskbcatx.sql scripts. This script will

reside in $CS_TOP/admin/sql, or $CS_TOP/patch/115/sql (use the newest version).

- Log into Unix as the Applications user, and change to $CS_TOP/patch/115/sql or

$CS_TOP/admin/sql.

- Log into SQL*Plus as the apps user, and execute csksynib.pls if necessary.

in order to create the interface (e.g. @csksynib.pls cs apps).

Details :

+++++++++++

SQL> @cskbctxp apps cs ctxsys

PL/SQL procedure successfully completed.

Commit complete.

SQL> @cskbsetx CS

DOC> | FILENAME |

DOC> | cskbsetx.sql |

DOC> | PURPOSE

DOC> | Drop index and submit a recreate index request. |

DOC> | Pre-req:

DOC> | CS_KB_CONC_PROG_PKG pacakge.

DOC> | ARGUMENTS

DOC> | 1. CS applicatin short name.

DOC> | NOTES

DOC> | Usage: @cskbsetx.sql CS

DOC> | |

DOC> | HISTORY |

DOC> | 18-DEC-2003 klou Created |

DOC> | 06-JAN-2004 klou Change install phase to last. |

DOC> | 26-MAR-2004 klou Default number of parallel workers. |

DOC> | 16-APR-2004 klou Fix bug 3572002. |

DOC> +=========================================================================*/

PL/SQL procedure successfully completed.

Commit complete.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @cssrcrix apps CS

PL/SQL procedure successfully completed.

Commit complete.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @cskbelex CS

DOC> | FILENAME |

DOC> | cskbelex.sql |

DOC> | PURPOSE

DOC> | Drop index and submit a recreate index request. |

DOC> | Pre-req:

DOC> | CS_KB_CONC_PROG_PKG pacakge.

DOC> | ARGUMENTS

DOC> | 1. CS applicatin short name.

DOC> | NOTES

DOC> | Usage: @cskbelex.sql CS

DOC> | |

DOC> | HISTORY |

DOC> | 18-DEC-2003 klou Created |

DOC> | 06-JAN-2004 klou Change install phase to last. |

DOC> | 26-MAR-2004 klou Default number of parallel workers. |

DOC> | 16-APR-2004 klou Fix bug 3572002. |

DOC> +=========================================================================*/

PL/SQL procedure successfully completed.

Commit complete.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @cskbcatx CS

DOC> | FILENAME |

DOC> | cskbcatx.sql |

DOC> | PURPOSE

DOC> | Drop index and submit a recreate index request. |

DOC> | Pre-req:

DOC> | CS_KB_CONC_PROG_PKG pacakge.

DOC> | ARGUMENTS

DOC> | 1. CS applicatin short name.

DOC> | NOTES

DOC> | Usage: @cskbcatx.sql CS

DOC> | |

DOC> | HISTORY |

DOC> | 18-DEC-2003 klou Created |

DOC> | 06-JAN-2004 klou Change install phase to last. |

DOC> +=========================================================================*/

PL/SQL procedure successfully completed.

Commit complete.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @cskbforx CS

DOC> | FILENAME |

DOC> | cskbforx.sql |

DOC> | PURPOSE

DOC> | Drop index and submit a recreate index request. |

DOC> | Pre-req:

DOC> | CS_KB_CONC_PROG_PKG pacakge.

DOC> | ARGUMENTS

DOC> | 1. CS applicatin short name.

DOC> | NOTES

DOC> | Usage: @cskbforx.sql CS

DOC> | |

DOC> | HISTORY |

DOC> | 18-DEC-2003 klou Created |

DOC> | 06-JAN-2004 klou Change install phase to last. |

DOC> +=========================================================================*/

PL/SQL procedure successfully completed.

Commit complete.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

$ cd $CS_TOP/patch/115/sql

$ ls -ltr csksynib.pls

-rwxr-xr-x 1 apkirang aakirang 45504 Aug 11 2006 csksynib.pls

SQL> @csksynib.pls CS apps

Package body created.

Commit complete.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Again reproducing the issue:

PAGE: 1

Invalid Indexes Report. Please correct/drop these before generating migration co

Report Date : April 6, 2012

Index Owne Index Name Index Type Status

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

CS CS_INCIDENTS_ALL_TL_N1 CONTEXT FAILED

CS CS_INCIDENTS_ALL_TL_N1 CONTEXT POPULATE

Report created /kirang/applmgr/11510/admin/kirang/log/fndinvld.txt

Still getting below invalids report:

Final Solution: As an apps user dropped the invalid and recreated as below:

a) drop index cs.CS_INCIDENTS_ALL_TL_N1;

b) SQL> @$CS_TOP/patch/115/sql/cssrcrix.sql apps CS

PL/SQL procedure successfully completed.

Commit complete.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Now the Issue is resolved. No new Invalids in the report. And safely proceed with OATM Migration...

Happy Reading

Thanks

Kiran Ginni