RMAN creates . files in 12c

Oracle 12c RMAN backup would create . (hidden files) if you are using NAS storage for the backups. These . files would be converted to regular files once rman stops writing to the backup piece.  This is because DNFS is enabled by default in 12c and this is cauing this behavior. Solution is to disable dnfs and you will start seeing normal files even when rman is writing to the backup pieces.

Issue with AFPASSWD in 12.1.2

Dont use AFPASSWD to reset oracle user password or apps password. There is a bug (Doc ID 1492939.1) that causes adpatch to fail. To undo this I had to this in one of our environments. There is a patch that fixes this issue but this is a faster and simple workaround.

FNDCPASS apps/xxxxxxxx 0 Y system/xxxxxxxx  ALLORACLE xxxxxxxx

Our experience with Apex Listener 2.2 implementation

I try to list out the issues and learnings from our Apex Listener implementation here:

1. Which J2EE server to use to deploy apex listener
A. In my opinion, 10.1.3.X of Oracle Application server or Weblogic 12.1.2 is recommend.  GlassFish server was not as straightforward and I was concerned about the support may not be as good as Oracle AS and Weblogic when it comes to raising SRs and troubleshooting. I could be wrong but that was my understanding after going through some readings.

2. What is the equivalent of dads.conf
A. defaults.xml has most of the parameters that dads.conf  had in a modplsql implementation. dads.conf is more intuitive compared to defaults.xml.

3. Where are images stored ?
A. I had some confusion over this since apex listener 1.1 had actually stored the images in the war file and there were some documents out there which explained on how to add custom images later without generating the war file. But this is no longer true in Apex Listener 2.2. You store only the location where the images are stored in the war file and you can add any images to that location as long as that location is not changed.

4. During installation weblogic some screens just hang
A. Using vnc server solved this problems. If you used xterm then some screens where you are expected to enter passwords are greyed out or the installation hangs.

5. Noticed that the admin server comes up pretty quickly in 12.1.2 as compared to the 11g Fusion Middleware without using any of the workarounds that were suggested for 11g FMW slow startups of admin servers (you can google on this issue and you will find what those workarounds were)

6. We had a requirement , wherein, the application was using a different context-root (in apex listener or java terminology). This implementation was straightforward in modplsql implementation. If developers had an application that wanted to use http://www.example.com/pls/apex and http://www.example.com/pls/apex2 but both of them pointing to the same database, we had defined an additional dad in dads.conf file for apex2. But in apex listener, we had to deploy apex.war as apex2.war so that apex2.war would be deployed with the context-root apex2.war. There were methods on suggestions on how one could change the context-root of apex.war  (using the --context-root option when deploying the war file) but that was only  if you had one application that wanted to use a different context-root. But in our case we had multiple applications and both were actually going to the same database but were using different context-roots.  I think in all cases, multiple war files are not required (you could have an apache redirect in most cases) but in our case it was required as the application had verified what was the source ip (so one set of applications just allowed you to pass through and the other applications were verifying the source ip). The point I am trying to make is y ou may have to deploy multiple war files to change context-root in some cases and this is a pain point in my opinion when you do upgrades of apex listener (in modplsql implementation, you did not have to bother about this once you had the entries in the dads.conf file).

7. In the load tests that were done, apex listener was better than modplsql implementation in terms of handling the load. At the least ,it is as good as modplsql implementation. I did not have to modify the defaults.xml and load test was run for 100 concurrent users successfully.

8. Configuring for multiple databases using the map-url and workspace-id features of apex.war exists but I have not been to get it to work successfully. I will have to revisit this but again this is one of the features that is not straightforward in an apex listener implementation (compared to modplsql implementation - just another entry in dads.conf file would have been enough)

9. You can integrate Apex applications with  Oracle Ebusiness Applications  and apex listener is the only supported way of doing it. This is one more reason why you would want to move to apex listener. But you can still integrate modplsql based apex applications with oracle ebusiness ( it works but not supported and recommended)

10. The support at forums.oracle.com for Apex Listener is very good and you usually get good and valid responses from here if there are any implementation questions.

FRM-91111,FRM-10039 when trying to involke frmbld.sh

We were trying to open forms in an ebusiness 12.1.2 environment and we would get this error

frmbld.sh

FRM-91111: Internal Error: window system startup failure.
FRM-10039: Unable to start up the Form Builder.

Solution was to unset NLS_LANG (export NLS_LANG=  ) and t.hen we were able to open forms builder
Eventhough some notes suggested that NLS_LANG can be set but it should not UTF8 (ours was American_America.WE8ISO8859P15)

 

Interesting issue with mount points, single instance (non-rac)

We had a 2 node cluster installation (11.2.0.2) (ASM + GI standard installation). This supported our 2 node rac (2 rac databases on this boxes).
To this combination we added a 3rd database which was non-rac. The database files were on NAS and just like any of our single database  (non-rac) databases, we had the following mount options

 rw,bg,hard,rsize=32768,wsize=32768,vers=3,nointr,timeo=600,tcp  0 0

When we tried to mount the asm diskgroup that supported this database, the asm alert log reported an issue with mount options and the diskgroup would not mount. The exact error was

WARNING:NFS file system /db/of3uat mounted with incorrect options(rw,vers=3,rsize=32768,wsize=32768,hard,proto=tcp,timeo=600,retrans=2,sec=sys,addr=vfiler9-3248)
WARNING:Expected NFS mount options: rsize>=32768,wsize>=32768,hard,noac/actimeo=0


Searched on metalink and found note 1367321.1. Per this note (though no exact hit) it was ok to get the above warning.

But metalink note 359515.1 had the solution (athough not directly mentioned in the note).
Per this noted, if you are on non-rac, the mount options you need to use for datafiles  were

rw,bg,hard,rsize=32768,wsize=32768,vers=3,nointr,timeo=600,tcp
and for rac it was
rw,bg,hard,nointr,rsize=32768, wsize=32768,tcp,actimeo=0, vers=3,timeo=600


So eventhough we are trying to bring up a non-rac database, since we are using the GI + ASM infrastructure that was originally installed for rac databases, we had to mount the file system that supported the non-rac database also using the same options that are used for rac (rw,bg,hard,nointr,rsize=32768, wsize=32768,tcp,actimeo=0, vers=3,timeo=600)
When this change was made, we were able to mount the diskgroup without any issues.

 

Command to copy concurent queue definition between ebzi environments.

FNDLOAD apps/appspass 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpque.lct feeder_cm.ldt QUEUE APPLICATION_SHORT_NAME=MY_APP CONCURRENT_QUEUE_NAME="MY QUEUE"
 
FNDLOAD apps/appspass 0 Y UPLOAD $FND_TOP/patch/115/import/afcpque.lct feeder_cm.ldt QUEUE APPLICATION_SHORT_NAME=MY_APP CONCURRENT_QUEUE_NAME="MY_QUEUE"

the above fndload commands can be used to download and upload concurrent queues between environnments. At our work, we have a frequent requirement where users want to run a bunch of concurrent programs but do not want to block the standard manager or other users. They ask us to create special concurrent managers for this purpose and migrate it across the 10 environments we have. Before discovering the above, we used to do each of the steps manually in each environment. Te above commands make it easier to migrate. This does copy the specalization and workshift rules. However, the exclusions from standard manager and other managers (that should be made when  a concurrent program has to exclusively run from a particular manager) are not done. That step is still a manual step.

 

Migrating Oracle E-Business Suite R12 from Linux 32-bit to Linux 64-bit

I try to document some of the issues we faced while migrating from 32 bit to 64 bit for application servers

1. The first misconception was that if we move to 64 bit from 32 bit, we would not be able to patch the application since there are not 64 bit patches available for all patches. There were conflicting opinions from Oracle Support (depends on the guys who pick your SR) that this is true  (you cannot patch 64 bit environments with 32 bit patches and  you would have to install 64 bit executables). This is not true and 32 bit patches can be applied on 64 bit platforms (this is confirmed in Steven Chan's blog too). In fact, Oracle probably does not specifically release 64 bit patches for Ebusiness suite.

2. Our Unix sysadmin did not install linux32 and this caused issues with all the shutdown and startup scripts. All of them check for the existence of linux32 before executing the stop and start commands. Exceptions are concurrent manager and apps listener startup/shutdown scripts.

3. Our Unix admin installed linux32 but we still had issues during startup. This was because he probably did not install it properly or got a wrong version. After installing linux32, we did not see issues with startup scripts but the command would just hang and timeout finally. 

This was confirmed by running these commands 

inux32

usage: linux32 [--3gb] [--4gb] program args ...

Default is --3gb to limit the address space of the 32bit children to 3GB

/u01/app/applmgr

applmgr@ofapdev21:OF1TCH> linux32 --4gb

Memory fault

/u01/app/applmgr

applmgr@ofapdev21:OF1TCH> linux32 --3gb

Memory fault

/u01/app/applmgr

applmgr@ofapdev21:OF1TCH> uname -m

x86_64

On a working system, where linux32 works properly the output should be like this

$ uname -m

x86_64

$ linux32

-sh-3.2$ uname -m

i686

-sh-3.2$ 

Our unix admin reinstalled linux32 and we stopped getting these errors anymore.

4. The other issue (this is silly and may be not everyone will face it) was rpc.statd process was not started on these application servers and we have a shared appl top (NAS) and even our INST_TOP was on NAS  (not shared between apps servers though). This caused some of the startup scripts not to acquire locks on files and these were seen in $LOG_HOME/ora/10.1.3/opmn/*.err files. Related issue was the update snaphot (through adadmin) never completed because it was waiting for acquiring a lock. I would see message like this

Trying to obtain a lock...

Deleting Temp Global Snapshot files ...Start time:Sun Oct 28 2012 18:05:39

Deleting Temp Global Snapshot Bugfixes ...Start time:Sun Oct 28 2012 18:05:39

Updating Global Snapshot files ...Start time:Sun Oct 28 2012 18:05:39

Total No. of records to be processed = 758374

5. The other thing that I noticed is that the startup/shutdown take considerably longer time on 64 bit boxes compared to 32 bit boxes (both have same memory). Still need to investigate this.

6. In the document " Migrating Oracle E-Business Suite R12 from Linux 32-bit to Linux 64-bit [Doc Id 471566.1]" one of the steps is Update CTXORIG.xml.

Instead of this why cant I directly update the context file to have this value

LINUX_X86-64 

and run autoconfig.

What is the difference between changing this in CONTEXT FILE vs ctxorig.xml. In fact, ctxorig.xml is generated by autoconfig and it would be overwritten even if I changed the ctxorig.xml and do not change context file and then run autoconfig

That was the question I had and for that Oracle had this response.

 I found you might find issues such as the one described in Note After Running Afcfgclone.Pl The Library Files On Apps Tier Disappear (Doc ID 564721.1) if you fail to set the right platform when performing the migration in the CTXORIG.xml. Per SystemCheck.java the check for the right platform is made first in CTXORIG.xml file.

Ebusiness R12Upgrade Issues (from 11.5.10.2 to 12.1.2)

Here I will try to document the issues we faced  or some steps that were not documented (at the time of our upgrade) during the upgrade

Rapidwiz: 

-- Rapidwiz does not like _ in sid name. We had to rename our databases to remove the _ as part of the upgrade.
-- Don't specify the config file on the command line. Instead, once you get into 
RapidWiz you would choose to "Load the following saved configuration" file on 
the "Configuration Choice" screen. 
-- First node: I select the upgrade option and create the config file.txt and proceed with the installation. 
 The second node installation in that case would be an actual 'install' vs an 'upgrade'. 
-- Verify the oraInst.loc has the right entries in the middle tier



On secondary nodes, when I try to start the rapidwiz it gave an error
Rapid Install Wizard is validating your file system......
2 dvd labels found
Rapid Install found an already existing /app/of1_int/intcomn/temp/RapidInstall directory.
Please make sure that no other instance of Rapid Install is running
or that the previous Rapid Install session completed.  If the area
in /app/of1_int/intcomn/temp/RapidInstall is no longer being used, it must be completely
removed before starting up a new Rapid Install session.
So renamed /app/of1_int/intcomn/temp/RapidInstall to /app/of1_int/intcomn/temp/RapidInstall.primary and started the install.
This is because we are on shared appl top.

Pre Upgrade steps:
Run olap.sql on 11.5.10 env.


$ORACLE_HOME/olap/admin @olap.sql SYSAUX TEMP


Disable maintenance plan 

   execute dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','RESOURCE_PLAN',''); 
   execute dbms_scheduler.set_attribute('MONDAY_WINDOW','RESOURCE_PLAN',''); 
 execute dbms_scheduler.set_attribute('TUESDAY_WINDOW','RESOURCE_PLAN',''); 
 execute dbms_scheduler.set_attribute('WEDNESDAY_WINDOW','RESOURCE_PLAN',''); 
 execute dbms_scheduler.set_attribute('THURSDAY_WINDOW','RESOURCE_PLAN','') 
 execute dbms_scheduler.set_attribute('FRIDAY_WINDOW','RESOURCE_PLAN','') 
 execute dbms_scheduler.set_attribute('SATURDAY_WINDOW','RESOURCE_PLAN','') 
  execute dbms_scheduler.set_attribute('SUNDAY_WINDOW','RESOURCE_PLAN','') 
commit;

-- appdstln.sql  fails with ORA-00001: unique constraint (XLA.XLA_DISTRIBUTION_LINKS_U1) violated 
This is the workaround. Steps have to be run prior to upgrade and after upgrade.Putting all the pre-upgrade and post steps here 


  (i) Identify the Invoice with multiple reversals of the Prepayment 
Applications. 
. 
CREATE TABLE bkp_pri AS 
SELECT parent_reversal_id 
FROM ap_invoice_distributions_all 
WHERE 1 = 2; 
. 
ALTER SESSION enable parallel dml; 
. 
INSERT /*+ parallel(aid) */ 
INTO bkp_pri 
SELECT parent_reversal_id 
FROM ap_invoice_distributions_all aid 
WHERE line_type_lookup_code = 'PREPAY' 
AND parent_reversal_id IS NOT NULL 
GROUP BY parent_reversal_id HAVING COUNT(*) > 1; 
. 
COMMIT; 
. 
CREATE TABLE bkp_inv AS 
SELECT * 
FROM ap_invoices_all 
WHERE invoice_id IN 
(SELECT DISTINCT invoice_id 
FROM ap_invoice_distributions_all 
WHERE invoice_distribution_id IN 
(SELECT parent_reversal_id 
FROM bkp_pri) 
); 
. 
SELECT * 
FROM bkp_inv; 
. 
UPDATE ap_invoices_all 
SET gl_date = to_date('01-JAN-1950', 'DD-MON-YYYY') 
WHERE invoice_id IN 
(SELECT invoice_id 
FROM bkp_inv); 
COMMIT;


as sysdba $ORACLE_HOME/ctx/sample/script/drkorean.sql

grant execute on OLAPSYS.CWM2_OLAP_METADATA_REFRESH to apps; 
grant execute on OLAPSYS.CWM2_OLAP_CATALOG to apps; 
grant execute on OLAPSYS.CWM2_OLAP_DIMENSION to apps; 
grant execute on OLAPSYS.CWM2_OLAP_CUBE to apps;

-- Increase shared_pool_size in both asm instances to 512 m 

Upgrade Issues:

--  These jobs never complete. Our functional team analyzed and determined that they would not need these and the following were skipped.

cd $ICX_TOP/patch/115/sql 
cp icxr12pd.sql icxr12pd.sql.orig
cp icxr12mi.sql icxr12mi.sql.orig
Put exit 0; in the orig file. 
cd $PO_TOP/patch/115/sql 
cp  poxukfi.sql poxukfi.sql.orig
Put exit 0; in the orig file. 

-- This is for performance issue with apidstln.sql

cp apidstln.sql apidstln.sql.orig 
>Performance team suggested following. 
This script had some join order issue, which caused Merge Cartesian Join. 
Please place FND_CURRENCIES FC just after GL_LEDGERS GL in the FROM clause in 
your APIDSTLN.SQL 
... 
FND_CURRENCIES FC, 
AP_INVOICE_DISTRIBUTIONS_ALL AID, 
ZX_REC_NREC_DIST ZRD, 
GL_LEDGERS GL 
=> We need to change it in 3 places (i.e. in 3 union set queries.) 
.... 
AP_INVOICE_DISTRIBUTIONS_ALL AID, 
ZX_REC_NREC_DIST ZRD, 
GL_LEDGERS GL , 
FND_CURRENCIES FC 

-- In RAC instances some workers fail because of inproper setting of AD_APPS_JDBC_URL. Workaround was to run this before starting any R12 patches.

TEMP_APPS_JDBC_URL="jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=YES)(FAILOVER=YES)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=myhost)(PORT=1521)))(CONNECT_DATA=(SID=MYSID)))"
AD_APPS_JDBC_URL=`echo $TEMP_APPS_JDBC_URL | cut -d "@" -f2`
export AD_APPS_JDBC_URL
--

 To remove -ms128m from the JVM parameter, please open 
$APPL_TOP/admin/adovars.env with text editor and find the following line 
ADJREOPTS="-ms128m -mx256m" 
then change it to 
ADJREOPTS="-mx256m"
re-source the environment after this change. 

-- WHEN THERE ARE AROUND 5000 JOBS TO COMPLETE (apsin.odf running) start utlrp.sql (which will make adobjcmp.sql run faster later) couple of times; Similarly run adsstats.sql and when patch starts running adsstats.sql, fail the worker and skip the job. adsstats.sql will run in parallel with 12.1.2 upgrade (monitor this using sid and kill it if it interferes with 12.1.2. This job is re-runnable

-- If paxingen_pa_proj_sum_temp.xdf fails then drop 
DROP TABLE PAXINGEN_PA_PROJ_SUM_TEMP;  and restart worker.

-- Renamed table per metalink note During R12 Upgrade HRCRETRG.Sql is Failing [ID 779841.1]
Time is: Mon Dec 21 2009 14:57:53
FAILED: file hrcretrg.sql on worker 1.
ATTENTION: Please fix the above failed worker(s) so the manager can continue.

SQL> show user
USER is "APPS"
SQL> alter table psp.PSP_ENC_SUMMARY_LINES_ARCH rename to PSP_ENC_SUMMARY_LINES_ARCH_2;
Table altered.

alter table psp.PSP_ORGANIZATION_ACCOUNTS_OLD rename to PSP_ORGANIZATION_ACCOUNTS_OLD2

/

-- ==================================== 

FAILED: file CustMigrationTool.class on worker 5 for product fnd username APPLSYS.
Time is: Tue Dec 22 2009 22:10:05
FAILED: file CustMigrationTool.class on worker 10 for product fnd username APPLSYS.
Time is: Tue Dec 22 2009 22:10:05
FAILED: file CustMigrationTool.class on worker 19 for product fnd username APPLSYS.
Time is: Tue Dec 22 2009 22:12:11
FAILED: file CustMigrationTool.class on worker 15 for product fnd username APPLSYS.
Time is: Tue Dec 22 2009 22:16:41
FAILED: file CustMigrationTool.class on worker 19 for product fnd username APPLSYS.
Time is: Tue Dec 22 2009 22:18:47
FAILED: file CustMigrationTool.class on worker 15 for product fnd username APPLSYS.
Time is: Tue Dec 22 2009 22:20:01

Solution: At the end of phase A144, these failed. Solution was to restart the failed workers. The logs show that
the connection is reset. Worker 15 had to be restarted using option 6. Either use option 2 to restart worker and see if it is successful or try using option 6. 

-- cskbcat.ldt failed.


Solution: From rdbms home run as sysdba $ORACLE_HOME/ctx/sample/script/drkorean.sql and restart the worker.  [ID 415487.1]

I included the step to run drkorean.sql as a pre-upgrade step and save some time for subsequent upgrades.

--  wsmCoProdUpg.sql , wsmUpgCpyRtg.sql, wsmwltonl.sql failed.

declare
*
ERROR at line 1:
ORA-01086: savepoint 'START_PT' never established
ORA-06512: at line 204
ORA-06502: PL/SQL: numeric or value error: character string buffer too small 

Fails with the above error. Fix is to increase   l_full_path           VARCHAR2(5120); from   l_full_path           VARCHAR2(512) in  wsmCoProdUpg.sql and restart the worker. This is because our utl_fil_dir is too long and wont fit in 512 characters. Need to log tar for this to get permanent fix.

Restart worker after that and it completes

For subsequent upgrades, I modified utl_file_dir during the upgrade to include only one line and did not have to use the workaround that I used above.

-- IGSEN4000939.sql fails with this error

ERROR at line 1:
ORA-20102: Unhandled Exception in upgrade script IGSEN4000939.sql - ORA-06502:
PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 229 

 As per [ID 554606.1} this can be skipped. Skip and continue

-- EGOSILDU.sql fails. We dont use EGO.  Skipped this as per note  [ID 847687.1]
--  file IGSAD4287260A.sql ,     IGSAD4287251A.sql fails. Skip as per note 554606.1

 -- zpbremcwm.sql fails

sqlplus -s APPS/***** @/app/of1_tch/r12/apps/apps_st/appl/zpb/12.0.0/patch/115/sql/zpbremcwm.sql
     cwm2_olap_cube.drop_cube(G_SCHEMA, v_cube.name);
     *
ERROR at line 235:
ORA-06550: line 235, column 6:
PLS-00904: insufficient privilege to access object OLAPSYS.CWM2_OLAP_CUBE
ORA-06550: line 235, column 6:
PL/SQL: Statement ignored
ORA-06550: line 239, column 6:
PLS-00904: insufficient privilege to access object OLAPSYS.CWM2_OLAP_DIMENSION
ORA-06550: line 239, column 6:
PL/SQL: Statement ignored
ORA-06550: line 243, column 6:
PLS-00904: insufficient privilege to access object OLAPSYS.CWM2_OLAP_CATALOG
ORA-06550: line 243, column 6:
PL/SQL: Statement ignored
ORA-06550: line 247, column 4:
PLS-00904: insufficient privilege to access object
OLAPSYS.CWM2_OLAP_METADATA_REFRESH
ORA-06550: line 247, column 4:
PL/SQL: Statement ignored

 Follow note [ID 744973.1]

Grant the following privileges to the user APPS:

grant execute on OLAPSYS.CWM2_OLAP_METADATA_REFRESH to apps;
grant execute on OLAPSYS.CWM2_OLAP_CATALOG to apps;
grant execute on OLAPSYS.CWM2_OLAP_DIMENSION to apps;
grant execute on OLAPSYS.CWM2_OLAP_CUBE to apps;

I included the step to grant privileges as a pre-upgrade step and save some time for subsequent upgrades.

-- apstca01.sql failed. 

sqlplus -s APPS/***** @/app/of1_tch/r12/apps/apps_st/appl/ap/12.0.0/patch/115/sql/apstca01.sql &un_ap &batchsize 3 20
DECLARE
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
ORA-06512: at line 1146 

Time when worker failed: Wed Dec 23 2009 10:38:31
Raised SR 3-1247951981 : Need to do this for next upgrade

Please read the readme and apply the patch 8638085:R12.AP.B in preinstall mode and then restart the 12.1.1 upgrade patch. This patch should deliver the right sql version
120.29.12010000.5 

-- poxukfi.sql and icxr12pd.sql , icxr12mi.sql taking long time.

Skipped this

 Unless Appendix E steps are completed these will take a long time. Once it is confirmed no functionality is missed by skipping this, we can exit these scripts or appendix E steps should be completed by the functional teams. In our case, it was determined by functional teams that they dont need this.

-- zxaptrxmigupd.sql takes time to complete.

Stop the job and come out of the patch.

sqlplus apps/
SQL> EXEC FND_STATS.GATHER_TABLE_STATS('ZX','ZX_LINES_DET_FACTORS',percent=>99,degree=>NULL,cascade=>TRUE);
SQL> EXEC FND_STATS.GATHER_TABLE_STATS('ZX','ZX_REC_NREC_DIST',percent=>99,degree=>NULL,cascade=>TRUE);
SQL> EXEC FND_STATS.GATHER_TABLE_STATS('ZX','ZX_LINES',percent=>99,degree=>NULL,cascade=>TRUE);
SQL> EXEC FND_STATS.GATHER_TABLE_STATS('ZX','ZX_LINES_SUMMARY',percent=>99,degree=>NULL,cascade=>TRUE);
SQL> EXEC FND_STATS.GATHER_TABLE_STATS('ZX','ZX_PARTY_TAX_PROFILE',percent=>99,degree=>NULL,cascade=>TRUE);
SQL> EXEC FND_STATS.GATHER_TABLE_STATS('ZX','ZX_REC_NREC_DIST',percent=>99,degree=>NULL,cascade=>TRUE);
SQL> EXEC FND_STATS.GATHER_TABLE_STATS('AP','AP_INVOICES_ALL',percent=>99,degree=>NULL,cascade=>TRUE);
SQL> EXEC FND_STATS.GATHER_TABLE_STATS('AP','AP_INVOICE_LINES_ALL',percent=>99,degree=>NULL,cascade=>TRUE);
SQL> EXEC FND_STATS.GATHER_TABLE_STATS('PO','PO_LINE_LOCATIONS_ALL',percent=>99,degree=>NULL,cascade=>TRUE);
SQL> EXEC FND_STATS.GATHER_TABLE_STATS('XLA','XLA_UPGRADE_DATES',percent=>99,degree=>NULL,cascade=>TRUE);
SQL> EXEC FND_STATS.GATHER_TABLE_STATS('APPLSYS','FND_CURRENCIES',percent=>99,degree=>NULL,cascade=>TRUE);
SQL> EXEC FND_STATS.GATHER_TABLE_STATS('APPLSYS','FND_DOCUMENT_SEQUENCES',percent=>99,degree=>NULL,cascade=>TRUE);

After that restart patch.

-- apidstln.sql is taking long time and failed with unique constraint error.

ERROR at line 1:
ORA-00001: unique constraint (XLA.XLA_DISTRIBUTION_LINKS_U1) violated
ORA-06512: at line 649

Raised tar 3-1251257301. The action items given in tar were tried but the query failed with snapshot too old error.

But in the end when the worker failed, after all other workers completed then the query given by oracle did not give error. Refer tar for details.
Just restarted the workers and it completed without failing on XLA.XLA_DISTRIBUTION_LINKS_U1 index.

-- sqlplus -s APPS/***** @/app/of1_tch/r12/apps/apps_st/appl/cz/12.0.0/patch/115/sql/czhist.sql &un_cz

PL/SQL procedure successfully completed.

BEGIN FND_STATS.GATHER_COLUMN_STATS('CZ','CZ_DEVL_PROJECTS','DELETED_FLAG'); END;

*
ERROR at line 1:
ORA-20002: Version of statistics table APPLSYS.FND_STATTAB is too old.  Please
try upgrading it with dbms_stats.upgrade_stat_table
ORA-06512: at "APPS.FND_STATS", line 664
ORA-06512: at "APPS.FND_STATS", line 2497
ORA-06512: at line 1 

solution: as sys,   EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('APPLSYS','FND_STATTAB')

-- No issues were encountered for 12.1.2 upgrade.

-- modplsql is not enabled by default after the apps upgrade. Copied the modplsql directory from 10.1.3 home and made changes in $INST_TOP/ora/10.1.3/Apache to suit our requirements.  Updated fnd_enabled_plsql.enabled apps schema to Y.  Oracle does not recommend this approach but this is one of the workarounds if you have modplsql enabled apps that cannot be converted to xml reports or use OAF.

-- Some DFFs were missing after the upgrade even though the ldt files were loaded properly. The log files showed the concurrent request completed successfully. This happened only for one of our upgrades (in INT env) but not in other environments. For this environment, functional teams identified the issues and we loaded the ldts again manually. Not sure why this happened.

-- Do not give too many workers in the hope that the upgrade will complete faster. We have java io connection exception errors. The jobs that are affected with this issue dont fail but if you notice the worker log you will see the errors.

-- As per upgrade docs, Oracle recommends to make some init.ora changes for the duration of the upgrade. It is important to remember and revert this changes after the upgrade. We forgot to do this in one of our upgrades and users started noticing performance issues once the application was released.

11i Install maintain oracle applications - Important points

1. The forms server mediates between the forms client, which is a Java applet
   running on the desktop, and the Oracle database server on the back end, or database tier.



2. The default value for context_name is _
   
3. 
   Oracle Applications Framework Processing
   With Oracle Applications Framework, an HTML-based Applications module uses the
   following access path:
   1. The user clicks on the hyperlink of a function from a browser.
   2. The browser makes a URL request to the Web listener.
   3. AOL/J validates user access to the page.
   4. The page definition is loaded from the Metadata Dictionary on the database tier into the
   application tier (Metadata UI Definition).
   5. The BC4J objects that contain the applications logic and access the database are
   instantiated.
   6. The Java Controller programmatically manipulates the page definition as necessary,
   based on dynamic UI rules.
   7. UIX (HTML UI Generator) interprets the page definition, creates the corresponding
   HTML in accordance with UI standards, and sends the page to the browser.

• The files in the plsql subdirectory (.pll files) are used by Oracle Reports.
• The files in the resource subdirectory (.pll and .plx files) are used by Oracle Forms.



Schemas Used During Signon
When you sign on to Oracle Applications, you initially connect to the public schema,
APPLSYSPUB. Once your user name and password are verified, Oracle Applications connects
you to the APPS schema and allows you to choose a responsibility. You can then use the
Professional interface or the HTML interface to access data that resides in a product schema.


The key directory on the database tier is
/appsutil/scripts/. This directory contains
control scripts to start and stop the database and database listener processes and to run
AutoConfig.



Here is the main reason for maintenance mode and how it minimizes
downtime.
There is a new Workflow feature called Cache Invalidation, which is built on the Business
Events System. This feature generates Java cache invalidation messages for changes to seed
data and, as a result, hampers the performance of FNDLOAD. As some patch sessions update a
significant amount of seed data and also require running FNDLOAD in the background, having

this feature enabled negatively impacts the performance of AutoPatch sessions. Maintenance
mode shuts down the Workflow Business Events System.

Rename ASM Diskgroup (renamedg) notes

This is how we tested the rename dg command available in11gR2. We used kfed before renamedg was available.

1. Create target disk group
2. Dismount target disk group
3. Do a bcv establish /split
4. Use rename dg renamedg phase=one dgname=FSSBCV_DG newdgname=FSSTGT_DG asm_diskstring='/u01/app/oracle/asm_devices/FSSTGT_DG/*' config=/tmp/renamedg.conf verbose=true
Parsing parameters..
Parameters in effect:
         Old DG name       : FSSBCV_DG
         New DG name          : FSSTGT_DG
         Phases               :
                 Phase 1
         Discovery str        : /u01/app/oracle/asm_devices/FSSTGT_DG/*
         Clean              : TRUE
         Raw only           : TRUE
renamedg operation: phase=one dgname=FSSBCV_DG newdgname=FSSTGT_DG asm_diskstring=/u01/app/oracle/asm_devices/FSSTGT_DG/* config=/tmp/renamedg.conf verbose=true
Executing phase 1
Discovering the group
Performing discovery with string:/u01/app/oracle/asm_devices/FSSTGT_DG/*
Identified disk UFS:/u01/app/oracle/asm_devices/FSSTGT_DG/emcpowerd1 with disk number:0 and timestamp (32950513 1756534784)
Checking for hearbeat...
Re-discovering the group
Performing discovery with string:/u01/app/oracle/asm_devices/FSSTGT_DG/*
Identified disk UFS:/u01/app/oracle/asm_devices/FSSTGT_DG/emcpowerd1 with disk number:0 and timestamp (32950513 1756534784)
Checking if the diskgroup is mounted or used by CSS
Checking disk numbe
Generating configuration file..
Completed phase 1
Terminating kgfd context 0x2b59d07cd0a0)
5. Run this kfed read  /dev/emcpowerd1 > /tmp/before_rename_dg.txt
6.  renamedg phase=two dgname=FSSBCV_DG newdgname=FSSTGT_DG config=/tmp/renamedg.conf verbose=true
Parsing parameters..
Parameters in effect:
         Old DG name       : FSSBCV_DG
         New DG name          : FSSTGT_DG
         Phases               :
                 Phase 2
         Discovery str        : (null)
         Clean              : TRUE
         Raw only           : TRUE
renamedg operation: phase=two dgname=FSSBCV_DG newdgname=FSSTGT_DG config=/tmp/renamedg.conf verbose=true
Executing phase 2
Looking for /u01/app/oracle/asm_devices/FSSTGT_DG/emcpowerd1
Modifying the header
Completed phase 2
Terminating kgfd context 0x2ae0a28050a0
$
7. Mount the target diskgroups
8. Use nid to rename the target database.

Oracle Applications Database: Migration from Solaris to Linux

Here I list the steps we followed when migrating from Solaris Oracle Applications databases to Linux.

 The other important changes that were introduced as part of this (apart form platform migration) were
1. 11.2.02 version of Grid Infrastructure (in case of RAC) and Oracle Restart Installations in case on  non-rac  databases was installed on Linux. On Solaris, standalone 11.1.0.7.6  ASM installations existed.

2. Moving from multiple ASM instances per box (This is not supported configuration but this is how it was setup originally on Solaris. Each database on the same box had its own ASM instance. This is not supported but it worked. The issue we faced though was during upgrades - if you had applied PSU on ASM and upgraded one of the databases that it supported by this database also the same PSU, then the other database which was not upgraded could not be brought up (Example , ASM is at PSU 6 and Database D1 is at PSU 6 but Database D2 using the same ASM instance (another diskgroup) was a lower PSU level cannot be brought up). The advantage in this model was that if ASM instance crashed, only the supporting database had to be restarted. But for support reasons, we decided to move away from that model (plus with GI install, starting 11.2.0.2 this was not possible to install this way as well).

3. RDBMS version was maintained at 11.1.0.7. We did not want to upgrade the RDBMS to 11.2.0.2 at this time because of the fear of introducing more variables (and difficulty in testing - whether an issue was caused by Linux migration or Database upgrade).

4. Since the Linux boxes were beefier compared to Solaris boxes, we added more SGA (10-15% more)

As far as the actual process is concerned, we followed the standard Oracle note on migration of Oracle Applications  362205.1 (with minor modifications, listed below).

Tasks

1. Grid Installation and RDBMS installation on Linux boxes.
2. Create the export parameter file
    Sample file:
  directory=linux_mig
dumpfile=aexp%U.dmp
filesize=8388608000
parallel=16
full=y
exclude=SCHEMA:"='MDDATA'"
exclude=SCHEMA:"='OLAPSYS'"
exclude=SCHEMA:"='ORDSYS'"
exclude=SCHEMA:"='DMSYS'"
exclude=SCHEMA:"='OUTLN'"
exclude=SCHEMA:"='ORDPLUGINS'"
exclude=STATISTICS
job_name='linux_migration'
#transform=oid:n
QUERY=applsys.wf_item_attribute_values:"where item_type!='WFERROR'and name != 'EVENT_MESSAGE'"
logfile=expdp_for_of1uat.log

3. Apply patch 7120092 to the source administration server node

4. Record Advanced Queue settings

          sqlplus /nolog 

          connect / as sysdba; 
          @auque1.sql 
         This will generate auque2.sql5. Create the target database instance
         Modified the script aucrdb.sql to suit to our requirements (change file names, storage parameters)
       Recreate control files with max instances = 3 
       sqlplus '/ as sysdba' 
      startup nomount 
     @aucrdb_OF1PRD.sql 
6. Set up the SYS schema 
     sqlplus '/ as sysdba' 
      @audb1110.sql
7. Set up the SYSTEM schema
         sqlplus system/xxxxx 
          @ausy1110.sql
8. Install Java Virtual Machine
         sqlplus system/xx 
        @aujv1110.sql

9. Install other required components

          sqlplus system/xx 

        @aumsc1110.sql FALSE SYSAUX TEMP

10. Disable automatic gathering of statistics

        sqlplus "/ as sysdba" 

      alter system enable restricted session; 
      @adstats.sql 
      sqlplus "/ as sysdba" 
      alter system disable restricted session; 

11. We have SAN/NAS for ASM storage. Since we were migrating to Linux, the diskgroups were recreated on the new box once storage was available.

Once the down time begins the following steps were done.

1. set cluster_database = false and bring up the instance on node 1(we have a 2 node RAC)

2. Grant privilege to source system schema --  AS SYS, grant EXEMPT ACCESS POLICY to system;This is because we had VPD on our Apps

3.  Create directory for export datapump

      AS SYS, create directory linux_mig as '/linux_migration/OF1PRD'; 

              grant read,write on directory linux_mig to public;

4. Purge Unused columns

            select * from sys.dba_unused_col_tabs; 

           set head off pages 0 feedback off verify off echo off 

           spool dropUnusedCols.sql 

               select 'alter table ' || owner || '.' || table_name || ' drop unused columns;' 

               from sys.dba_unused_col_tabs; 

                spool off 

                Clean dropUsedCols.sql and run as sysdba. Ignore these errors 

                ERROR at line 1: 

               ORA-12996: cannot drop system-generated virtual column

5.   Purge recycle bins

        as sysdba 

            PURGE DBA_RECYCLEBIN; 
             select owner, count(*) from dba_recyclebin group by owner;

6. This was not documented in note 362205.1. But there was a bug with datapump export which truncated very big packages. I did not track this but basically large XLA packages were truncated upon export. Solution was to drop them and recreate them after the datapump import.

genDropXLA.sql 
        set pages 0 head off feedback off lines 170 echo off trim on verify off termout off timing on 
        spool dropXLA_AAD_Pkgs.sql 
        select distinct('drop package ' || owner || '.' || object_name || ';') 
        from dba_objects 
        where object_type='PACKAGE BODY' and object_name like 'XLA_00%AAD%PKG'; 
        spool off 
        set timing on 
      @dropXLA_AAD_Pkgs 

7. Export the applications database instance
expdp system/[system password] parfile=auexpdp.dat

 
Our database size was 2 TB and it took roughly 4 hrs to do the export.

Import the Applications database instance 
The import process was where we did things differently for index creation. In our first environment when we went through the standard process, the import took more than 2 days and was not complete because the index creation was in serial order. There were some notes on Metalink on how to speed up the index creation but they were not very useful in reducing the time. On googling, I found Randolf Geist has written a script for parallel object creation for import datapump (http://oracle-randolf.blogspot.com/2011/02/concurrent-index-creation.html) and I had used the script and modified it to our requirements to create indexes in parallel. This was the entire import process completed in less than 10 hrs as opposed to the original 2+ days and not running to completion. 
There was another issue that we ran (probably because of our patch level and patches and was never fixed by Oracle at that time). Import datapump would crash after running for about 2 hours and workaround was to restart it. We had given all the information to Oracle in the SR that was raised but no patch was given. So we just monitored the import closely and restarted it when it failed. This would happen only once during the entire import process and in our case it was roughly when it was 2 hrs after the start of the import.

1. Create the import parameter files

All files will be in LINUX_MIGRATION_OF1UAT. 

create or replace directory linux_mig as '/linux_migration/expdp/'; 

       grant read, write on directory linux_mig to public; 

impdp sample par 

=============== 

        directory=linux_mig 

        dumpfile=aexp%U.dmp 

        parallel=24 

        full=y 

                    transform=oid:n 

        job_name='lin_mig_imp' 

        logfile=impdp_OF1UAT.log 

2. Import the Applications database instance - Phase I

Run import and monitor.When the impdp starts creating indexes and go to next step. 

Note:The impdp fails after 1.5 hr or 2 hr. Workaround is to restart the impdp after restarting database and asm. This is phase 1 of import. 

3. Create indexes in parallel.

Like I mentioned the credit for this goes to Randolf Geist. I only applied it with slight modifications to suit our environment and my requirements. Here is the readme I prepared for my usage.

parallel index creation

Step 1: Create sql file that has the sql for all indexes

directory=linux_mig

dumpfile=aexp%U.dmp

sqlfile=all_indexes.sql

include=INDEX

That will create all the indexes. Open the file and remove extra portions that has create index for fnd_lobs_cts and other domain indexes. Call it all_index_mode.sql. I will update document on what needs to be removed exactly. But you can open the file all_indexes.sql and also figure out.

cleanup of file:

 %s!ALTER SESSION SET EDITION= "ORA$BASE";!!g

DOTN DO THIS STEP IN PARALLEL WITH THE MAIN IMPORT. DO IT BEFORE OR AFTER THE IMPORT.

Step 2:

SQL>

SQL> grant execute on dbms_aq to kmadduri;

Grant succeeded.

SQL> grant execute on dbms_aqadm to kmadduri;

Grant succeeded.

SQL> grant execute on dbms_scheduler to kmadduri;

Grant succeeded.

grant create table, create type, create procedure to kmadduri;

OS Path: /linux_migration/OF2INT -- This is the path of export dump files.

Enter the file name

of the file containing the CREATE INDEX DDL commands

Please double-check that this file contains the intended

DDL commands, in particular this should NOT be a genuine SQLFILE

generated via IMPDP that has NOT been post-processed by the

"transform_all_sql.sh" unix script (see documentation for more details)

Furthermore please note that at present the code does not

tokenize the file contents but simply executes the commands

delimited by semicolon. For the intended purpose of creating indexes

this should be sufficient except for rare cases of function-based

indexes containing semicolons in string expressions as part of the

virtual column definition, but other SQL like complex CTAS statements

might contain such additional semicolons - use with care in such cases.

Filename: all_index_mod.sql

Enter the number of concurrent worker threads for serial indexes

The default is NULL which means use CPU_COUNT * INSTANCES threads

If you do not want two sets of workers enter here the total number

of worker threads and 0 for the number of threads for the second worker set

You need then also to enter the SAME worker thread name for both

sets in the following prompts otherwise this will not work as expected

Serial Degree (default NULL): 64

Enter the number of concurrent worker threads for parallel indexes

The default is 1, enter a number or NULL for automatic degree

which is again CPU_COUNT * INSTANCES threads (could be high for parallel DDL)

If you do not want two sets of workers enter here 0

for the number of threads of the second worker set

You need then also to enter the SAME worker thread name for both

sets in the following prompts otherwise this will not work as expected

Parallel Degree (default 1): 0

Enter the name of worker thread 1

The default is SERIAL_INDEX

If you do not want two sets of workers enter here the same name

as for the second worker thread on the next prompt

otherwise this will not work as expected

Maximum length of identifier: 20 chars

Worker set 1 name (default SERIAL_INDEX):

Enter the name of worker thread 2

The default is PARALLEL_INDEX

If you do not want two sets of workers enter here the same name

as for the first worker thread on the previous prompt

otherwise this will not work as expected

Maximum length of identifier: 20 chars

Worker set 2 name (default PARALLEL_INDEX): SERIAL_INDEX

Starting concurrent index creation...

Check AQ$CREATE_INDEX_QUEUE view for number of tasks

Check CREATE_INDEX_CONCURRENT_LOG table for progress and errors

You are about to start concurrent index creation now...

OS Path             : /linux_migration/OF2INT

Filename            : all_index_mod.sql

Serial Degree       : 64

Parallel Degree     : 0

Worker Thread 1 Name: SERIAL_INDEX

Worker Thread 2 Name: SERIAL_INDEX

Hit CTRL+C to cancel, ENTER to continue...

SQL>

SQL> create or replace directory CREATE_INDEX_CONCURRENT_DIR as '&os_path';

old   1: create or replace directory CREATE_INDEX_CONCURRENT_DIR as '&os_path'

new   1: create or replace directory CREATE_INDEX_CONCURRENT_DIR as '/linux_migration/OF2INT'

Directory created.

Elapsed: 00:00:00.01

SQL>

SQL> begin

  2    pk_create_index_concurrent.create_index_concurrent(

  3      p_directory_name        => 'CREATE_INDEX_CONCURRENT_DIR'

  4    , p_file_name             => '&file_name'

  5    , p_parallel_degree_set_1 => &serial_degree

  6    , p_parallel_degree_set_2 => &parallel_degree

  7    , p_worker_set_id_1       => '&thread_1_name'

  8    , p_worker_set_id_2       => '&thread_2_name'

  9    );

 10  end;

 11  /

old   4:   , p_file_name             => '&file_name'

new   4:   , p_file_name             => 'all_index_mod.sql'

old   5:   , p_parallel_degree_set_1 => &serial_degree

new   5:   , p_parallel_degree_set_1 => 64

old   6:   , p_parallel_degree_set_2 => &parallel_degree

new   6:   , p_parallel_degree_set_2 => 0

old   7:   , p_worker_set_id_1       => '&thread_1_name'

new   7:   , p_worker_set_id_1       => 'SERIAL_INDEX'

old   8:   , p_worker_set_id_2       => '&thread_2_name'

new   8:   , p_worker_set_id_2       => 'SERIAL_INDEX'

4. While step 3 is in progress, some indexes would be created by datapump as well. That is fine. There may be some locking (I did not get any locks but you could and I just thought I would monitor and kill them instead of stopping the import and continuing with prallel index creation and start the impdp job. This actually saved some time  because both the impdp and the parallel index creation script were creating indexes but the majority of them were created by the parallel index script).  Once Step 3 completes and when datapump is creating fnd_lobs_ctx index , skip the job because fnd_lobs_ctx takes long time to complete and we can do it in parallel while other post tasks are running. There is a note on metalink which gives steps on how to speed up fnd_lobs_ctx. I also removed all the domain index creation scripts from step 3 and decided to run them from sql prompt. 

This whole process took around 7 hrs to complete (step 1 to step 4). 

5. Revoke privilege from target system schema (revoke EXEMPT ACCESS POLICY from system;)

6. Reset Advanced Queues
    sqlplus /nolog
 connect / as sysdba; 
 @auque2.sq

7. Copy listener.ora,tnsnames.ora from old host and change the host names

8. Run adgrants.sql

   sqlplus '/as sysdba' 

    @adgrants.sql apps 

9. Grant create procedure privilege on CTXSYS ( sqlplus apps/[APPS password]  @adctxprv.sql system_pwd CTXSYS)

10. Run AutoConfig

-- Change context files on apps tiers to reflect new db host. 
-- EXEC FND_CONC_CLONE.SETUP_CLEAN; 
-- Make sure ISTWatch is included in listener.ora. This was required for us. May not be required for all implementations.

11. Change dads.conf on each middle tier. This is also required for us because we still use modplsql even though we are on 12.1.2

12. Gather statistics for SYS schema 

sqlplus "/ as sysdba" 
  alter system enable restricted session; 
  @adstats.sql 
 sqlplus "/ as sysdba" 
 alter system disable restricted session; 

13. Re-create custom database links 

14. Create ConText and AZ objects

      Apply patch 7129002

   cd $AU_TOP/patch/115/bin/ 
  perl $AU_TOP/patch/115/bin/dpost_imp.pl step9.drv 11 
Once the driver file has been generated, use AutoPatch to apply it on the target administration server node. 
You may get warning that patch is applied , do you want to continue. Say Yes.

15. Populate CTXSYS.DR$SQE table 

    sqlplus apps/[apps password] 

exec icx_cat_sqe_pvt.sync_sqes_for_all_zones;

16. Compile invalid objects (@utlrp)

17.  Run AD Administration on the target administration server node. From the Maintain Applications Database Objects menu, perform the following tasks: 

   1. 
      Compile flexfield data in AOL tables 
   2. 
      Recreate grants and synonyms for APPS schema 
   

18.  create xla packages as apps user

 sqlplus apps/[apps password] 

  
 Apply  patch 10396457 
    SQL> @xla6128278.sql 

Enter value for outfilename: xla6128278.sql

This step is not documented in the metalink note above. I got this from an exadata oracle apps migration white paper. But this wil create the large xla packages which would fail to be created and hence compiled properly.

Other issues:

-- csr_rules_b does not get imported

SQL> PURGE RECYCLEBIN; 
Recyclebin purged. 
  set serveroutput on 
  BEGIN 
   DBMS_XMLSCHEMA.deleteSchema( schemaurl => 'http://xmlns.oracle.com/CRM/Scheduler/Rules', delete_option => DBMS_XMLSCHEMA.DELETE_CASCADE_FORCE    ); 
    COMMIT; 
     EXCEPTION 
    WHEN OTHERS THEN 
    dbms_output.put_line('Encountered ' || SQLERRM || ' when deleting Scheduler Rules XML Schema'); 
     END; 
-- reset the password of csr user to the same oen as apps (first check if apps passowrd works for csr) 
FNDCPASS apps/xxxxx 0 Y system/xxxx ORACLE CSR xxxxx 
 cd $CSR_TOP/patch/115/sql 
  @csrrsreg.sql apps xxxxx csr xxxxx

--- WWV_FLOW_GENERATE_DDL  IS INVALID

grant select on dba_db_links to FLOWS_030100; 
 grant select on DBA_DEPENDENCIES to FLOWS_030100; 
 grant select on DBA_TRIGGERS to FLOWS_030100; 
 grant select on DBA_TYPES to FLOWS_030100; 
 ANDge FLOWS_030100.WWV_FLOW_GENERATE_DDL compile body;

-- Drop and recreate fnd_lobs_ctx

   cd $FND_TOP/sql 

drop index applsys.fnd_lobs_ctx;         
update fnd_lobs 
set FILE_FORMAT = 'IGNORE' 
where NVL(PROGRAM_NAME,'NULL') != 'FND_HELP' ; 
 @aflobbld.sql applsys apps 
update fnd_lobs 
set file_format = fnd_gfm.set_file_format(file_content_type); 
select 
FILE_FORMAT, 
count(*) 
from 
fnd_lobs 

-- Gather stats complete with errors

    select hc.table_name, hc.column_name  
from FND_HISTOGRAM_COLS hc , dba_tab_columns tc 
where  hc.table_name ='FII_FIN_ITEM_HIERARCHIES' 
 and hc.table_name= tc.table_name (+) 
and hc.column_name = tc.column_name (+) 
and tc.column_name is null 
delete from FND_HISTOGRAM_COLS 
where (table_name, column_name) in 
  ( 
   select hc.table_name, hc.column_name 
   from FND_HISTOGRAM_COLS hc , dba_tab_columns tc 
   where hc.table_name  ='FII_FIN_ITEM_HIERARCHIES' 
   and hc.table_name= tc.table_name (+) 
   and hc.column_name = tc.column_name (+) 
   and tc.column_name is null 
  ); 
  exec dbms_stats.unlock_schema_stats('APPLSYS') 

-- Additional Privs

GRANT ALTER ON "CSR"."CSR_RULE_WINDOWS_B" TO "APPS" WITH GRANT OPTION; 
GRANT DELETE ON "CSR"."CSR_RULE_WINDOWS_B" TO "APPS" WITH GRANT OPTION; 
GRANT INDEX ON "CSR"."CSR_RULE_WINDOWS_B" TO "APPS" WITH GRANT OPTION; 
GRANT INSERT ON "CSR"."CSR_RULE_WINDOWS_B" TO "APPS" WITH GRANT OPTION; 
GRANT SELECT ON "CSR"."CSR_RULE_WINDOWS_B" TO "APPS" WITH GRANT OPTION; 
GRANT UPDATE ON "CSR"."CSR_RULE_WINDOWS_B" TO "APPS" WITH GRANT OPTION; 
GRANT REFERENCES ON "CSR"."CSR_RULE_WINDOWS_B" TO "APPS" WITH GRANT OPTION; 
GRANT ON COMMIT REFRESH ON "CSR"."CSR_RULE_WINDOWS_B" TO "APPS" WITH GRANT OPTION; 
GRANT QUERY REWRITE ON "CSR"."CSR_RULE_WINDOWS_B" TO "APPS" WITH GRANT OPTION; 
GRANT DEBUG ON "CSR"."CSR_RULE_WINDOWS_B" TO "APPS" WITH GRANT OPTION; 
GRANT FLASHBACK ON "CSR"."CSR_RULE_WINDOWS_B" TO "APPS" WITH GRANT OPTION;

-- ACL Setup 

Drop existing ACL and create ACL's similiar to the ones on Solaris 

-- compile all custom forms