When upgrading to 12.1.1 and then 12.1.3, if you don’t apply patch 5233248 during the application of your 11i patches to prepare your database objects for R12, after the upgrade and all the R12.1.3 patches have been applied, there are three invalid objects that can only be resolved by applying this SLA patch to the 11i instance, in advance of the upgrade to R12.1.3. This patch is recommended as a patch needed if you are using “Upgrade by Request”.  There are other methods to implement “Upgrade by Request”, but this is the method we recommend. If you don’t apply this patch and set the start and end dates with the SLA pre-upgrade program shown below, then only the default number of periods will be upgraded. The number of periods that are upgraded by default are six, unless you set the periods in the SLA pre-upgrade program. We highly recommend setting the starting period to the first period in your instance and the ending period to the last period in your instance.

First, enable the SLA pre-upgrade program:

If you plan to upgrade all your data during the upgrade, and I highly recommend upgrading all your data during the upgrade downtime window, you will need to set the start and end dates for all the GL periods in your instance. Set these dates using the “SLA pre-upgrade” concurrent program as follows:

If you decide to use less than all the GL periods, the amount of time this saves has been about one to two hours during our customers production upgrades. The post upgrade concurrent request can upgrade any remaining data in a few hours, if you choose not to upgrade all your GL periods during the original production downtime window.  But a downtime window for this post upgrade concurrent program should requested because of the amount of archive logging it creates. If you have a downtime window to process the remaining GL periods, archive logging can be disabled.  If you do not have a downtime window during the post SLA processing, the archive log mount point can fill up and hang your database. For this reason we strongly recommend disabling archive logging during any post processing of SLA data.

This XLA patch needs to be run with 11i patches in order to avoid these three invalid objects after the 12.1.1 / 12.1.3 upgrade:

When applying the patch from the apps tier machine and it dies in the middle of the patch, in this case the R12.1.1 patch, 6678700, it said:

Reading completed jobs from restart file (if any).

AutoPatch error:

unable to find job when restarting

Wrong number of fields in restart file when restarting at line number =

This may indicate that the “END OF COMPLETED JOBS’ line is missing.

Notice the patch lists the number of jobs from the beginning of the patch, phase A0, not the current job, in this case is 48177 jobs at phase A144, as shown in the figure at the bottom of this post.

Other OTN threads suggest restarting the patch from the beginning. This requires a restore of the database, and while this solution works, it’s not the fastest solution.

Please edit the $APPL_TOP/admin/SID/restart/adpmain.rf9 file and remove the last incomplete line. See the figure below, the last line is incomplete compared to the line above it. Make a backup of the restart directory and edit the file and remove the last line.

Remove the line that says: Done job: prod= gmd un=GMD file=GMDSTNXT.sql ……

Then, restart the patch and use adctrl to set the jobs to restart.

Thanks, Mike

From: How To Run The Workflow Directory Services Concurrent Program From The SQLplus Prompt [ID 1213304.1]

1. Sync responsibility role data into the Workflow table:

begin
fnd_user_resp_groups_api.sync_roles_all_resp_secgrps(TRUE);
end;

2. Synchronize WF LOCAL tables:

exec WF_LOCAL_SYNCH.BulkSynchronization(‘ALL’)

The Synchoronize WF LOCAL table step above give the following error:

ERROR at line 1:

ORA-12801: error signaled in parallel query server P000

ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

ORA-06512: at “APPS.WF_LOCAL_SYNCH”, line 2726

ORA-06512: at line 1

sqlplus apps/apps $FND_TOP/patch/115/sql/affurgol.sql

The run step three from MOS Note [1213304.1]

3. Workflow Directory Services User/Role Validation:

exec wf_maintenance.ValidateUserRoles(p_BatchSize => null, p_check_dangling => TRUE, p_check_missing_ura => TRUE, p_UpdateWho => FALSE);

 Run adstpall.sh to stop the apps tier and adstrtal.sh to start the apps tier.

Issues with the R1211 Upgrade

After applying the latest R12.1.1 preinstall=y patches, I found these new issues:

To fix the GMIG3MIB.sql issue:

Run sqlplus apps/apps  …/apps/apps_st/appl/gmi/12.0.0/patch/115/sql/GMIG3MIB.sql

Enter value for 2: GMI

Enter value for 1: 1000

Enter value for 3: 1

Enter value for 4: 1

I skipped the okemd2fx.sql, with option 8, and skipped the wmsrules.sql

First, go to edelivery.oracle.com and select the E-Business Suite and Linux, Solaris or whatever the appropriate OS is. Then, select the set of disks to install; For instance, I’ve selected Oracle Solaris 64-bit. You also have the choice of whether you want the NLS packs for additional languages:

Notice the next figure shows the first line is the Rapid Install RDBMS Disk2. This is the last disk of the ORACLE_HOME software disks. You will need to download this and rapidwiz will ask for these disks, even though you have selected the rapidwiz option to “Upgrade Oracle Applications”. In my case, I already have a 11.2.0.3 ORACLE_HOME, so I don’t need the 11.1.0.7 ORACLE_HOME that gets installed with these disks. However, there is no option other than install all the software. If you remove this ORACLE_HOME before rapidwiz finishes it’s post install checks, the install will fail and the oraInventory will not be updated. Therefore. wait for rapidwiz to completely finish and then delete the 11.1.0.7 ORACLE_HOME.

In the figure below. you don’t need to download the Rapid Install Databases disks. These are used if you want install the VISION database with data pre-populated.

You will need the Tools disks, the APPL_TOP disks and the startCD disks:

After you unzip these zip files,  you should have the following directories:

oraApps

oraAS

oraDB

startCD

If you want to download the software for Linux, notice the part numbers are different in the figure below:

If you are using Linux, there are different disks to install for Linux:

Hi Mike,

When a column is marked unused, it is considered for all effective purposes as dropped —  (so much so , you could follow up that command with adding a new column to the table with the same column name) — as I mentioned, this action (of setting column unused, is irreversible– there is no command to mark it “used”)….the only action left to do, is a space management operation to reclaim the space …so from this perspective, knowing which columns existed before, and were marked unused is irrelevant …all one needs to know is that table x has some columns that were marked unused (essentially dropped – minus space reclaim) …and that these tables are the ones  for which space can be reclaimed via the alter table …drop unused columns  command.

If you need to see which columns where marked ununsed , you can try one of the following approaches …

1 — View the 12.1.3 data model comparison report from MOS Doc. 1290886.1

2 — Try something like this in a test env  which I found poking around in the web — which created a view on dictionary objects which supposedly does the column lookup even for unused cols — but I have not tried it :   http://stackoverflow.com/questions/2949226/how-can-you-tell-which-columns-are-unused-in-all-tab-cols

thanks,
-lester

I have comments turned off on this blog, but please email me at mike@trutek.com, if you have questions or comments.

Sometimes stopping and restarting a patch that seems to be hanging with adctrl will cause the patch to finish. However, twice now this process has scared the living bejebers out of me. In the example below, job GMIG3MIB.sql was running for a very long time, so I stopped the patch with adctrl and restarted the patch, using adpatch.

After restarting the patch, the patch always scares me, because the first thing it says is that it’s starting over, then after reading the completed jobs from the restart file, it determines the correct number of jobs remaining:

Then, I got this message that say’s, it’s “Unable to find job when reading existing jobs”, Actually the job GMIG3MIB.sql has already finished, so I tried setting the status with adctrl to quit and failed, and the option 8, skip and restart on the next job.

So now, since the status was Skip & restart, I thought the jobs would be skipped, but I still kept getting the “Unable to find job when reading existing jobs” error from above.

Boy did I think I was in serious ____, but with a little research on MOS, I found: Inadvertently Closing a Session of Adpatch Causes ‘Unable to find job ‘ Error [ID 257505.1]

This MOS note says, to resolve this issue:

1. Determine the last job that was supposed to run. (zxexemptjur.sql)

2. Grep from all the adworker log files to confirm that the last job

has been completed successfully by one of the adworkers. (GMIG3MIB.sql)

3.  Update fnd_install_processes with control code and status both =

‘W’ for the worker that this driver is assigned.

4  Restart adpatch.

This worked and saved me from restoring the database files and restarting the 1211 MP, 6678700, with 125402 jobs and running the patch for another 16 hours. Whew.

update applsys.fnd_install_processes set status=’W’ where worker_id=1;

update applsys.fnd_install_processes set status=’W’ where worker_id=2;

update applsys.fnd_install_processes set control_code=’W’ where worker_id=1;

update applsys.fnd_install_processes set control_code=’W’ where worker_id=2;

To fix the GMIG3MIB.sql issue:

Run sqlplus apps/apps  …/apps/apps_st/appl/gmi/12.0.0/patch/115/sql/GMIG3MIB.sql

Enter value for 2: GMI

Enter value for 1: 1000

Enter value for 3: 1

Enter value for 4: 1

I skipped the okemd2fx.sql, with option 8, and skipped the wmsrules.sql.

I started the most recent pass of the R12.1.1 upgrade and then realized I had forgotten some patches. Some of the patches had not been download from MOS, because I tried to download too many at a time. I think there is a limit of about 100 patches per download, but I don’t know the exact number. Therefore, I only downloaded about 100 of the 120 patches I intended to download.

I started the upgrade using the u6678700.drv, in $AU_TOP/patch/115/driver and then I double checked my patch directory. I was surprised and worried when I realized I did not have all the patches. I was missing some very important patches, included in an earlier post, and sumarized below, including the R12.1 CUP1, 7303029 and others.

The list from earlier for the R12.1 preinstall=y patches:

7303029, 7648869, 8230656, 8351855, 8429275, 8495719, 8509517, 8517880, 8615142, 8712047, 8731432, 8752951, 8764069, 8781600, 8850587, 8865466, 8871012, 8942413, 8967918, 9062910, 9082021, 9202645, 9257954, 9290222, 9491856, 9504903, 9560244, 9586498, 9726737, 9799876, 9868229, 9903933, 9918101, 9947835, 10011700, 10029457, 10041471, 10096115, 10096191, 10144929, 10163624, 10163753, 10170555, 10198811, 10221534, 10235226, 10275581, 10358280, 10359715, 10393730, 10420245, 11071399, 11653739, 11767692, 11847569, 11928146,, 12344218, 12347791, 12372035, 12382774, 12387835, 12422840, 12553436, 12578648, 12648752, 12651338, 12682665, 12877002, 12877611, 12912302, 12930626, 12944782, 12990345, 12992125, 13016846, 13019385, 13040251, 13243594, 13248921, 13335019, 13356426, 13390676, 13397299, 13403786,  13451293, 13509912, 13544907, 13545598, 13557244, 13577549, 13598940,  13684949, 13695167, 13774403, 13793279, 13799611,  13820304, 13833174, 13837821, 13856969, 13864126, 13872862, 13917170, 13931180, 13978746, 14026963

I got a little excited when I realized I had jeopardized all my earlier work, but not double checking that the patches had been accurately uploaded to the server. However, I know how to apply a patch in the middle of the maintenance pack, by making a copying of the $APPL_TOP/admin/SID/restart directory and a copy of the FND_INSTALL_PROCESSES table (create table applsys.fnd_install_processes_backup as select * from applsys.fnd_install_processes).

After making a backup of the table and directory, I stopped the patch using adctrl and applied the missing R12.1.1 preinstall patches. After copying the restart directory and fnd_install_processes table from the backups, I restart the 6678700 R12.1.1 Maintenance Pack. To my surprise, it never asked me if I wanted to continue with the previous patch, and started the 6678700 driver from the beginning. I was mortified. I was sure the patch would fail, as this has happened to me before, with failure as the only option, and recovery of the database would be necessary.

However, to my surprise, the patch worked just fine. After further investigation, the patch had previously reached phase A25, when I stopped it to apply the missing patches. These phases contain only odf and xdf files. These object descriptor files and xml descriptor files prepare the instance and can be re-run. The odf and xdf files continue through phases A35. Therefore, any failures before the end of phase A35 can be restarted without using the copies of the restart directory and backup of the FND_INSTALLED_PROCESSES table.