Knowledge is Power – share IT



Notes on upgrading 11i E-Business Suite database from 10.2.0.4 to 11.2.0.3 on LinuxApply 11gR2 interoperability patches using adpatch on Admin Tier8815204 INTEROPERABILITY PATCH FOR R11I ON 11.2 RDBMS5644137PLM_PF.C: USE OF LITERAL CAUSING PERFORMANCE ISSUE WHEN INSERTING DATA INTO USER DEFINED ATTRIBUTES TABLES7456837ONE -OFF - 11I AOL/J CODE BASE AND 11G JDBC DRIVER COMPLIANCE9535311TXK AUTOCONFIG AND TEMPLATES ROLLUP PATCH Update9835302TCH11201: ADBLDXML AND AUTOCONFIG COMPLETES WITH JAVA.LANG.UNSATISFIEDLINKERRORReference Metalink Notes:881505.1165195.1Setup the 11.2.0.3 environment [oracle@kens-orasql-002-uat script]$ export ORACLE_HOME=/hds001/oracle/product/11.2.0.3/dbhome_1export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATHexport LD_LIBRARY_PATH=$ORACLE_HOME/libexport PERL5LIB=/hds001/oracle/product/11.2.0.3/dbhome_1/perl/lib/5.10.0:/hds001/oracle/product/11.2.0.3/dbhome_1/perl/lib/site_perl/5.10.0[oracle@kens-orasql-002-uat script]$ cd /hds001/oracle/product/11.2.0.3/dbhome_1/nls/data/old/[oracle@kens-orasql-002-uat old]$ which perl/hds001/oracle/product/11.2.0.3/dbhome_1/perl/bin/perl[oracle@kens-orasql-002-uat old]$ perl cr9idata.plCreating directory /hds001/oracle/product/11.2.0.3/dbhome_1/nls/data/9idata ...Copying files to /hds001/oracle/product/11.2.0.3/dbhome_1/nls/data/9idata...Copy finished.Please reset environment variable ORA_NLS10 to /hds001/oracle/product/11.2.0.3/dbhome_1/nls/data/9idata!Edit .bash_profile ORA_NLS10=/hds001/oracle/product/11.2.0.3/dbhome_1/nls/data/9idataexport ORA_NLS10Install opatch for 11.2.0.3[oracle@kens-orasql-002-uat stage]$ mv /hds001/oracle/product/11.2.0.3/dbhome_1/OPatch /hds001/oracle/product/11.2.0.3/dbhome_1/OPatch.old[oracle@kens-orasql-002-uat stage]$ cp p6880880_112000_Linux-x86-64.zip $ORACLE_HOME[oracle@kens-orasql-002-uat stage]$ cd $ORACLE_HOME/[oracle@kens-orasql-002-uat dbhome_1]$ unzip p6880880_112000_Linux-x86-64.zipArchive: /hds001/oracle/product/11.2.0.3/dbhome_1/p6880880_112000_Linux-x86-64.zip creating: OPatch/ creating: OPatch/oplan/ inflating: OPatch/oplan/README.html….….[oracle@kens-orasql-002-uat dbhome_1]$ cd OPatch[oracle@kens-orasql-002-uat OPatch]$ ./opatch versionOPatch Version: 11.2.0.3.0OPatch succeeded.export PATH=/hds001/oracle/product/11.2.0.3/dbhome_1/OPatch:$PATH[oracle@kens-orasql-002-uat OPatch]$ which opatch/hds001/oracle/product/11.2.0.3/dbhome_1/OPatch/opatchApply additional 11.2.0.3 RDBMS patchesApply the following patches using opatch 985853912942119129603021298518413001379130048941325893613366268Prepare the database for the 11.2.0.3 UpgradeAs SYS as SYSDBA SQL> purge dba_recyclebin;DBA Recyclebin purged.SQL> exec dbms_stats.gather_dictionary_stats;PL/SQL procedure successfully completed.SQL> Drop index system.repcat$_audit_column_idx1 ;Drop index system.repcat$_audit_column_idx1 *ERROR at line 1:ORA-01418: specified index does not existRemove all hidden database parameters and events from pfile (or spfile) and restart the database:_b_tree_bitmap_plans_fast_full_scan_enabled_gby_hash_aggregation_enabled_like_with_bind_as_equality_optimizer_push_pred_cost_based_sort_elimination_cost_ratio_sqlexec_progression_cost_trace_files_publicGet a list of pre-upgrade INVALID objects SQL> set pagesize 5000SQL> spool invalid_objects.lstSQL> select owner,count(*) from dba_objects where status='INVALID' group by cube(owner);SQL> select owner,object_type,object_name from dba_objects where status=’INVALID’order by owner,object_type, object_name;SQL> spool offDisable Archive logging and restart the databaseSet Up the 11.2.0.3 environment to launch the Database Upgrade Assistant(dbua)If you see a screen like this …. This can happen if the environment variable ORA_TZFILE is set and is pointing to the Oracle 10g Home – unset the variable and launch dbua once again.POST UPGRADEAfter the upgrade check DBA_REGISTRY SQL> set linesize 120SQL> col comp_name format a50SQL> select comp_name,status,version from dba_registry;COMP_NAME STATUS VERSION-------------------------------------------------- ----------- ------------------------------Oracle Text VALID 11.2.0.3.0Oracle Data Mining VALID 11.2.0.3.0OLAP Catalog VALID 11.2.0.3.0Spatial VALID 11.2.0.3.0Oracle Multimedia VALID 11.2.0.3.0Oracle XML Database VALID 11.2.0.3.0Oracle Expression Filter VALID 11.2.0.3.0Oracle Database Catalog Views VALID 11.2.0.3.0Oracle Database Packages and Types VALID 11.2.0.3.0Oracle Real Application Clusters INVALID 11.2.0.3.0JServer JAVA Virtual Machine VALID 11.2.0.3.0COMP_NAME STATUS VERSION-------------------------------------------------- ----------- ------------------------------Oracle XDK VALID 11.2.0.3.0Oracle Database Java Packages VALID 11.2.0.3.0OLAP Analytic Workspace VALID 11.2.0.3.0Oracle OLAP API VALID 11.2.0.3.015 rows selected.Upgrade the statistics tableSQL> exec dbms_stats.upgrade_stat_table('applfnd','fnd_stattab');PL/SQL procedure successfully completed.Create the updated 11.2.0.3 pfile[oracle@kens-orasql-002-uat ~]$ cd $ORACLE_HOME/dbs[oracle@kens-orasql-002-uat dbs]$ ls –lrt spfile*-rw-r----- 1 oracle oinstall 4608 Sep 18 12:29 spfileCLMTS10G.oraSQL> create pfile from spfile;File created.Update the following database parameters after the RDBMS 11gR2 upgrade_b_tree_bitmap_plansFALSE_fast_full_scan_enabledFALSE_like_with_bind_as_equalityTRUE_optimizer_autostats_jobfalse_sort_elimination_cost_ratio5_system_trig_enabledtrue_trace_files_publicTRUEcompatible11.2.0utl_file_dir'/usr/tmp', '/oracle/CLMTS10G/db/tech_st/11.2.0.2/appsutil/outbound/CLMTS10G_KENs-ORASQL-001'Note – check the utl_file_dir parameter as paths may change after the upgradeRemove the following database parameters:core_dump_destnls_languageplsql_optimize_leveltimed_statisticsSet up the 11.2.0.3 ListenerKill the 10g listener oracle 377 1 0 Aug16 ? 00:01:24 /hds001/oracle/product/10.2.0/dbhome_1/bin/tnslsnr CLMTS10G -inheritoracle 18909 18287 0 09:49 pts/3 00:00:00 grep tns [oracle@kens-orasql-002-uat script]$ kill -9 377Create the 11gR2 listener using netca – retain the same port as the 10g listenerCopy adgrants.sql, adctxprv.sql and adstats.sql from the Admin Tier across to the 11gR2 Oracle homeAPPL_TOP/admin/adgrants.sqlAPPL_TOP/admin/adstats.sqlAD_TOP/patch/115/sql/ adctxprv.sqlAs SYSDBA:SQL> @adgrants.sql <APPS USER>As APPS:SQL> @adctxprv.sql <SYSTEM password> CTXSYSUpdate the CTXSYS file_access_role parameter.SQL> exec ctxsys.ctx_adm.set_parameter( 'file_access_role', 'public' );PL/SQL procedure successfully completed.AutoConfig SetupDeregister the current database server (conditional) If you plan to change the database port, host, SID, or database name parameter on the database server, you must also update AutoConfig on the database tier and deregister the current database server node. Use SQL*Plus to connect to the database as APPS and run the following command:$ sqlplus apps/[APPS password]SQL> exec fnd_conc_clone.setup_clean;Copy appsutil directory from 10GR2 home to 11gR2 home [oracle@kens-orasql-002-uat dbhome_1]$ cp -fR appsutil /hds001/oracle/product/11.2.0.3/dbhome_1/[oracle@kens-orasql-002-uat dbhome_1]$Download jre-6u29-linux-x64.binrun./jre-6u29-linux-x64.binmv jre1.6.0_29 jre[oracle@kens-orasql-002-uat bin]$ perl adbldxml.pl tier=db appsuser=apps_applfnd jtop=/hds001/oracle/product/11.2.0.3/dbhome_1/appsutil/jreRun AutoConfig on Database Tier using adconfigWe were getting error in autoconfig while it was running afdbprf.sh and adcrobj.sh sscriptsSP2-1503: Unable to initialize Oracle call interfaceSP2-0152: ORACLE may not be functioning properlyadcrobj.sh exiting with status 1ERRORCODE = 1 ERRORCODE_ENDFixed by:cd /hds001/oracle/product/11.2.0.3/dbhome_1/appsutil/templatevi ad8idbux.envadded lines :if test "%s_database%" = "db112" ; thenORA_TZFILE=""elseORA_TZFILE="$ORACLE_HOME/oracore/zoneinfo/timezlrg.dat"fiexport ORA_TZFILE[oracle@kens-orasql-002-uat bin]$ ./adconfig.shEnter the full path to the Context file: /hds001/oracle/product/11.2.0.3/dbhome_1/appsutil/CLMTS10G_kens-orasql-002-uat.xmlEnter the APPS user password:The log file for this session is located at: /hds001/oracle/product/11.2.0.3/dbhome_1/appsutil/log/CLMTS10G_kens-orasql-002-uat/09191047/adconfig.logAutoConfig is configuring the Database environment...AutoConfig will consider the custom templates if present. Using ORACLE_HOME location : /hds001/oracle/product/11.2.0.3/dbhome_1 Classpath : /hds001/oracle/product/11.2.0.3/dbhome_1/appsutil/jre/lib/rt.jar:/hds001/oracle/product/11.2.0.3/dbhome_1/jdbc/lib/ojdbc6.jar:/hds001/oracle/product/11.2.0.3/dbhome_1/appsutil/java/xmlparserv2.zip:/hds001/oracle/product/11.2.0.3/dbhome_1/appsutil/java:/hds001/oracle/product/11.2.0.3/dbhome_1/jlib/netcfg.jar Using Context file : /hds001/oracle/product/11.2.0.3/dbhome_1/appsutil/CLMTS10G_kens-orasql-002-uat.xmlContext Value Management will now update the Context file Updating Context file...COMPLETED Attempting upload of Context file and templates to database...COMPLETEDUpdating rdbms version in Context file to db112Updating rdbms type in Context file to 64 bitsConfiguring templates from ORACLE_HOME ...AutoConfig completed successfully.Run adstats.sqlAs SYSDBA :SQL> shutdown immediate;SQL> startup restrictSQL> @adstatsSQL> shutdown immediate;SQL> startupNote: If host, listener port , database name etc changes then have to run autoconfig on the apps tiers as well On Admin tier via adadmin Recreate grants and synonyms Update bash profile of database user to point to new environment file ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download