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.
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.
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.
-- 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
-- apstca01.sql failed.
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.
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
*
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
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
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.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;
-- 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
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
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
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);
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
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.
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
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.
No comments:
Post a Comment