Upgrade Database 11.2 to 12.2
(Article From Toadworld.com)
Easily explained and steps to upgrade.
2) Upgrade Database
(Article From Toadworld.com)
Easily explained and steps to upgrade.
Introduction:
Oracle E-Business suite R12.2 is shipped with Oracle Database Version 11.2.0.3. There will be always a requirement for upgrading your database to latest available release for fixing bugs, using new features and to be on supported version of database. This article will outline all steps required for upgrading and using 12cR1 database with your Oracle E-Business suite R12.2
Environment details:
Host : erpnode3 ( Oracle Enterprise Linux 5.7)
Installation Type :
| Host Details | erpnode3 - Oracle Enterprise Linux 5.7 64 Bit |
| EBS Installation Type | Single Node (DB + Application) |
| EBS Version | R12.2.4 |
| Database Version | 11.2.0.3 |
STEPS for Upgrade:
1) Pre-Upgrade Steps:
1.1 - Install 12cR1 RDBMS Software
1.2 - Install R12cR1 Examples CD
1.3 - Create /nls/data/9idata directory
1.4 - Install all pre-requisite Database and Application patches
1.5 - Verify the JRE version in Oracle Home
1.6 - Verify Application patching cycle is complete
1.7 - Drop SYS.ENABLED$INDEXES
1.8 - Remove the MGDSYS schema
1.9 - Run pre upgrade tool
2) Upgrade Database
2.1 - Use DBUA for upgrading database
3) Post Upgrade Steps:
3.1 - Install patch post Installation steps for all RDBMS patches (opatches)
3.2 - Start Listener from 12cR1 Home
3.3 - Application Database accounts Expired & Locked
3.4 - Run adgrants.sql
3.5 - Grant create procedure privilege on CTXSYS
3.6 - Compile Invalid Objects
3.7 - Set CTXSYS parameter
3.8 - Validate Workflow ruleset
3.9 - Create context file and run autoconfig on dbTier
3.10 - Run script "adstats.sql" to gather SYS stats
3.11 - Create new MGDSYS schema
3.12 - Apply post upgrade WMS patches
3.13 - Recreate grants and synonyms
3.14 - Start Application services
3.15 - Run concurrent Request "Synchronize workflow views"
3.16 - Verify the upgraded version from OAM
4) Issues
4.1 - Unable to proceed with 12c runInstaller with error "[INS-10102] Installer initialization failed"
4.2 - DBUA not listing the database
4.3 - Invalid specification of system parameter "LOCAL_LISTENER"
4.4 - Application Database account locked after upgrade
1.1 - Install 12cR1 RDBMS Software
Install 12cR1 RDBMS Oracle Home in a separate directory from the existing Oracle Home. The current Oracle Home location is "/u01/ora_test/11.2.0" and 12cR1 Oracle Home will be Installed in "/u01/ora_test/12.1.0"
- Set the Proper Display Variable and execute "runInstaller"
- Here select option "Install Database software only"
- Select "Single Instance Database"
- If you have any other language Installed and configured than add required languages.
- Select Enterprise Edition
- Provide the valid Oracle Base and Oracle home location.
- Select the valid groups for all roles. In my case only one group is used.
- If there are any missing pre-requisites listed then fix it. Some of these pre-requisites can be fixed using fixup script and missing rpm package cannot be fixed using fixup script. Hence install any listed missing rpm packages.
- Execute fixup script from the specified location as "root" user.
- Check "summary" of setting configured for Installation. If there are any changes you can edit it from the same screen.
- Execute "root.sh" script from root user.
- Here the Installation of RDBMS software completed.
1.2 - Install R12cR1 Examples CD
- Installation of Examples CD is mandatory and one should not skip its Installation before starting the upgrade process. Examples CD will be Installed in existing newly Installed 12cR1 Oracle home.
- set the proper display variable and execute runInstaller
- Here select the existing 12cR1 Oracle home (/u01/ora_test/12.1.0)
- Check "summary" for configured settings.
- Installation of "Examples CD" completed successfully.
1.3 -Create /nls/data/9idata directory
Execute "cr9idata.pl" script from 12c Home ($ORACLE_HOME/nls/data/old/cr9idata.pl). Configure "ORA_NLS10" Environment variable with directory created in 12c Home
[oraebs@erpnode3 ~]$ perl $ORACLE_HOME/nls/data/old/cr9idata.pl
Creating directory /u01/ora_test/12.1.0/nls/data/9idata ...
Copying files to /u01/ora_test/12.1.0/nls/data/9idata...
Copy finished.
Please reset environment variable ORA_NLS10 to /u01/ora_test/12.1.0/nls/data/9idata!
[oraebs@erpnode3 ~]$
1.4 - Install all pre-requisite Database and Application patches
Application Patches:
Current EBS environment is running on latest release EBS R12.2.4 so there are no additional application patches are required to be Installed.
Database Patches:
The following RDBMS patches need to be Installed as a pre-requisites before upgrading the Database.
Patches Linux X86-84 Bit - Version 12.1.0.1.0
|
17695298 (see Footnote 2), 14237793, 16989137, 17184721, 17448638, 17600719, 17801303, 17892268, 17912217, 17973865, 17973883, 18288676, 18419770, 18604144, 18614015, 18665660, 18685209, 19466632, 19603897 (see Footnote 3), 19393542
|
Footnote 2 - This is the Database Bundle Patch for 12.1.0.1.0 and must be applied first. This includes the database patch for 18241194 which will be removed in one of the subsequent patches as it is no longer needed.
Footnote 3 - If a conflict is reported with 18241194, either roll back 18241194 first (opatch rollback, then apply 19603897), or allow opatch to roll it back when applying Patch 19603897.
Configure 12c Enviroment variables:
[oraebs@erpnode3 ~]$ cat 12c.env
export ORACLE_HOME=/u01/ora_test/12.1.0
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
export ORA_NLS10=/u01/ora_test/12.1.0/nls/data/9idata
[oraebs@erpnode3 ~]$
[oraebs@erpnode3 ~]$ export ORACLE_HOME=/u01/ora_test/12.1.0
[oraebs@erpnode3 ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oraebs@erpnode3 ~]$ export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
[oraebs@erpnode3 ~]$ vi 12c.env
[oraebs@erpnode3 ~]$ which opatch
/u01/ora_test/12.1.0/OPatch/opatch
[oraebs@erpnode3 ~]$ opatch lsinventory
Oracle Interim Patch Installer version 12.1.0.1.0
Copyright (c) 2012, Oracle Corporation. All rights reserved.
Oracle Home : /u01/ora_test/12.1.0
Central Inventory : /u01/ora_test/oraInventory
from : /u01/ora_test/12.1.0/oraInst.loc
OPatch version : 12.1.0.1.0
OUI version : 12.1.0.1.0
Log file location : /u01/ora_test/12.1.0/cfgtoollogs/opatch/opatch2014-11-17_15-53-52PM_1.log
Lsinventory Output file location : /u01/ora_test/12.1.0/cfgtoollogs/opatch/lsinv/lsinventory2014-11-17_15-53-52PM.txt
------------------------------------------------------------------------------
Installed Top-level Products (2):
Oracle Database 12c 12.1.0.1.0
Oracle Database 12c Examples 12.1.0.1.0
There are 2 products installed in this Oracle Home.
There are no Interim patches installed in this Oracle Home.
------------------------------------------------------------------------------OPatch succeeded.
[oraebs@erpnode3 ~]$
The above command lists the Installed products in an Existing Oracle home. All pre-requisites patches needs to be Installed in 12.1.0.1.0 Oracle home.
As per footnote2 patch "17695298" should be Installed first as this patch contains the consolidated bug fixes.
As per footnode3 If a conflict is detected with patch "18241194" than uninstall this patch than Install patch "19603897".
Refer Attached file for Installing all RDBMS opatches.
[applebs@erpnode3 appl_test]$ adop phase=cutover,cleanup
1.5 - Verify the JRE version in Oracle Home
To upgrade to 12cR1 minimum version of JRE required is version 6. Please make sure that Installed version of JRE in an existing Oracle Home. If the Installed version is lower than required then please upgrade it.
In current setup the Installed version is version 7 so no action is required.
[oraebs@erpnode3 bin]$ pwd
/u01/ora_test/11.2.0/appsutil/jre/bin
[oraebs@erpnode3 bin]$ ./java -version
java version "1.7.0_17"
Java(TM) SE Runtime Environment (build 1.7.0_17-b02)
Java HotSpot(TM) 64-Bit Server VM (build 23.7-b01, mixed mode)
[oraebs@erpnode3 bin]$
1.6 - Verify Application patching cycle is complete
Before starting the upgrade process verify the Application patching cycle is complete and there no pending actions.
[applebs@erpnode3 appl_test]$ adop phase=cutover,cleanup
1.7 - Drop SYS.ENABLED$INDEXES
If table SYS.ENABLED$INDEXEX exists then drop this table with sysdba user. In current setup this table doesn't exists.
[oraebs@erpnode3 11.2.0]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Nov 18 11:39:15 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> drop table sys.enabled$indexes;
drop table sys.enabled$indexes
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> desc sys.enabled$indexes;
ERROR:
ORA-04043: object sys.enabled$indexes does not exist
SQL>
1.8 - Remove the MGDSYS schema
If upgrading from database version prior to 12c than drop MGDSYS schema from the existing database. Execute script "catnomgdidcode.sql " from an existing Oracle home.
SQL> @?/md/admin/catnomgdidcode.sql
User dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
Synonym dropped.
SQL>
1.9 - Run pre upgrade tool
Pre Upgrade tool will lists all changes need to be performed before starting the upgrade process.
Copy pre upgrade scripts from 12cR1Oracle home to any other directory. I used "db_scripts" directory to keep all upgrade related scripts in one location.
[oraebs@erpnode3]$cp /u01/ora_test/12.1.0/rdbms/admin/preupgrd.sql .
[oraebs@erpnode3]$cp /u01/ora_test/12.1.0/rdbms/admin/utluppkg.sql .
Make sure that preupgrd.sql and utluppkg.sql files are copied in the same directory.
- Connect from 11.2.0.3 Home as sysdba and run pre-upgrade tool
SQL> @preupgrd.sql
Loading Pre-Upgrade Package...
Executing Pre-Upgrade Checks...
Pre-Upgrade Checks Complete.
************************************************************
Results of the checks are located at:
/u01/ora_test/11.2.0/cfgtoollogs/test/preupgrade/preupgrade.log
Pre-Upgrade Fixup Script (run in source database environment):
/u01/ora_test/11.2.0/cfgtoollogs/test/preupgrade/preupgrade_fixups.sql
Post-Upgrade Fixup Script (run shortly after upgrade):
/u01/ora_test/11.2.0/cfgtoollogs/test/preupgrade/postupgrade_fixups.sql
************************************************************
Fixup scripts must be reviewed prior to being executed.
************************************************************
************************************************************
====>> USER ACTION REQUIRED <<====
************************************************************
The following are *** ERROR LEVEL CONDITIONS *** that must be addressed
prior to attempting your upgrade.
Failure to do so will result in a failed upgrade.
1) Check Tag: INVALID_SYS_TABLEDATA
Check Summary: Check for invalid (not converted) table data
Fixup Summary:
"UPGRADE Oracle supplied table data prior to the database upgrade."
+++ Source Database Manual Action Required +++
You MUST resolve the above error prior to upgrade
************************************************************
SQL>
Oracle Database 12c Pre upgrade utility will generate preupgrade.log, preupgrade_fixups.sql and postupgrade_fixup.sql
We need to review preupgrade.log file and check for all recommendation. This will be helpful if you are performing the manual upgrade.
- perform full database backup.
2) Upgrade Database
2.1 - Use DBUA for upgrading database
- Configure the temporary environment file during upgrade process. After enabling autoconfig on 12c Home it will generate new environment file with all required EBS parameters
- Execute dbua from 12c Home
Make sure that oratab file contains a valid SID entry in "/etc/oratab" file. In my environment after fresh installation this entry was missing and dbua was not able to list the database. After manually adding entry in oratab file it was listed in dbua window.
Its gathering the information required for upgrading the database.
- Performing the pre-requisite checks
As mentioned in the tech notewe can safely ignore DBMS LDAP dependencies. If there are any Invalid objects in the database try to compile them.
- Select "ignore" and set action "ignore" then click on next.
- You can select Parallelism parameters based on available hardware resources. If you want to upgrade the Timezone Data then check "Upgrade Timezone Data" it will upgrade your Timezone Data to V18, the current version of Timezone is 17.
- Listener will be configured after upgrading the database. So do not select to configure listener.
- You can perform a backup before starting the upgrade process if there is no backup policy in place. If you backed up your database already then you can select "I have my own backup and restore strategy"
- This error was encountered as there are no listeners configured in 12.1.0.1 Oracle Home. You can safely ignore this error. We will manually configure the listener after completion of database upgrade process. To avoid this error you can create the listener from 12cR1 Oracle home before starting dbua.
- You can also avoid this error by removing the LOCAL_LISTENER parameter from the spfile created by dbua and re-run dbua.
- There was an error in upgrade alert log "SYSTEM.EBS_LOGON ORA-01031:Insufficient privileges"
By default SYSTEM.EBS_LOGON trigger is enabled. To avoid this error disable this trigger.
- Upgrade completed successfully. Click on "upgrade results" to check full details.
- The new spfile has been created in 12cR1 Oracle home.
- Time zone version has been upgraded from version 17 to version 18
- sec_case_sensitive_logon parameter was removed.
3) Post Upgrade Steps:
3.1 - Install patch post Installation steps for all RDBMS patches (opatches)
Perform post Installation steps for all Installed patches prior to upgrade. If DATAPATCH is required to run then it can be run but only once.
3.2 - Start Listener from 12cR1 Home:
Copy TNS_ADMIN directory from 11gR2 Home to 12cR1 home and modify all required files with 12c Oracle Home location.
[oraebs@erpnode3 admin]$ ls -lrt
total 12
-rw-r--r-- 1 oraebs dbaerp 205 May 11 2011 shrept.lst
drwxr-xr-x 2 oraebs dbaerp 4096 Oct 27 21:25 samples
drwxr-xr-x 2 oraebs dbaerp 4096 Oct 27 23:11 test_erpnode3
[oraebs@erpnode3 admin]$ cp -pr test_erpnode3/u01/ora_test/12.1.0/network/admin
[oraebs@erpnode3 admin]$ pwd
/u01/ora_test/11.2.0/network/admin
[oraebs@erpnode3 admin]$
- Configured listener.ora and tnsnames.ora
- Start listener from 12c Home
[oraebs@erpnode3 test_erpnode3]$ pwd
/u01/ora_test/12.1.0/network/admin/test_erpnode3
[oraebs@erpnode3 test_erpnode3]$ export TNS_ADMIN=/u01/ora_test/12.1.0/network/admin/test_erpnode3
[oraebs@erpnode3 test_erpnode3]$ lsnrctl start test
LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 20-NOV-2014 08:55:35
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Starting /u01/ora_test/12.1.0/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 12.1.0.1.0 - Production
System parameter file is /u01/ora_test/12.1.0/network/admin/test_erpnode3/listener.ora
Log messages written to /u01/ora_test/12.1.0/network/admin/test.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=erpnode3.oralabs.com)(PORT=1529)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=erpnode3.oralabs.com)(PORT=1529)))
STATUS of the LISTENER
------------------------
Alias test
Version TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date 20-NOV-2014 08:55:36
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/ora_test/12.1.0/network/admin/test_erpnode3/listener.ora
Listener Log File /u01/ora_test/12.1.0/network/admin/test.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=erpnode3.oralabs.com)(PORT=1529)))
Services Summary...
Service "test" has 1 instance(s).
Instance "test", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oraebs@erpnode3 test_erpnode3]$
3.3 - Application Database accounts Expired& Locked
After successful completion of upgrade we noticed that all database accounts was expired and locked Including APPS, APPLSYS and APPLSYSPUB except "SYS" and "SYSTEM".
This is the default behavior of 12c DBUA and its mentioned in MOS tech note " 1516557.1" in section known Issues. You should not encounter this Issue if you are manually upgrading your database using upgrade script.
As per Oracle documentation and procedure we should change the Application Database account using FNDCPASS utility. As the APPS account is locked and expired it was not allowing to change the password using FNDCPASS utility.
Solution:
- As there was no alternate available then changed the password for APPS using alter command to the same old password.
- Changed password again using FNCPASS utility for APPLSYS and APPLSYSPUB user.
- Changed password for all Application Oracle users
- Run autoconfig on application and database Tiers
Tried login with APPS user:
[oraebs@erpnode3 db_scripts]$ sqlplus
SQL*Plus: Release 12.1.0.1.0 Production on Thu Nov 20 00:23:07 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name: apps
Enter password:
ERROR:
ORA-28000: the account is locked
Enter user-name: /as sysdba
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> alteruser apps account unlock;
User altered.
SQL>
Check the account Status:
SQL> select username,account_status, lock_date , expiry_date from dba_users where username='APPS';
USERNAME ACCOUNT_ST LOCK_DATE EXPIRY_DA
---------- ---------- --------- ---------
APPS EXPIRED 18-NOV-14
[applebs@erpnode3 sql]$ pwd
/u01/appl_test/fs1/EBSapps/appl/ad/12.0.0/patch/115/sql
[applebs@erpnode3 sql]$ FNDCPASS apps/APPS 0 Y system/manager APPLSYS APPS
APP-FND-01564: ORACLE error 28001 in AFPCOA
Cause: AFPCOA failed due to ORA-28001: the password has expired
.
The SQL statement being executed at the time of the error was: and was executed from the file .
[applebs@erpnode3 sql]$
- Changed password manually for APPS user:
[applebs@erpnode3 sql]$ sqlplus
SQL*Plus: Release 10.1.0.5.0 - Production on Thu Nov 20 09:18:26 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: apps
Enter password:
ERROR:
ORA-28001: the password has expired
Changing password for apps
New password:
Retype new password:
Password changed
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>
- In Weblogic admin server APPS password is configured, hence changed password again with FNDCAPSS utility and updated the same password in weblogic admin server datasource.
[applebs@erpnode3 ~]$ FNDCPASS apps/APPS 0 Y system/manager SYSTEM APPLSYS APPS
Log filename : L462324.log
Report filename : O462324.out
[applebs@erpnode3 ~]$
In R12.2 the process for changing the APPS password is same as prior releases with some additional steps in Weblogic Admin Server.
Steps:
- Start Admin server (Do not start any other services/managed servers)
- Log in to Weblogic Server Admin Console
- Click Lock & Edit in Change Center
- In the Domain >expand Services and select Data Sources
- On "Summary of JDBC Data Sources" page > select EBSDataSource
- On the "Settings for EBSDataSource" > select the Connection Pool tab
- Enter the new password
- Click on Save
- Click on Activate Changes in Change Center
- stop admin server
- Run autoconfig on Database Tier and Application Tier
All other Oracle Application schemas accounts are expired and locked. We need to change password for APPLSYSPUB and other schema (like AR, GL) using FNDCPASS ALLORACLE.
- Change password for APPLYSPUB using alter command.
- Change password for all Oracle User using FNDCPASS utility.
[applebs@erpnode3 appl_test]$ FNDCPASS apps/apps 0 Y system/manager ALLORACLE oracle
Log filename : L466353.log
Report filename : O466353.out
[applebs@erpnode3 appl_test]$
- After changing passwords all Application database accounts will now have new password but still these accounts are locked. All these users should be unlocked using alter command.
- Use below SQL command to generate script for altering users which are locked.
- script attached "db_unlock_users.sql"
SQL>select 'alter user '||username||' account unlock;' from dba_users
where ACCOUNT_STATUS='LOCKED';
- As per the procedure we should run autoconfig after changing the password using FNDCPASS. But we will run autoconfig later while enabling autoconfig on 12c Home dbTier.
After successful completion of autconfig on dbTier than run autoconfig on Application Tier.
- Script to unlock Application database accounts:
3.4 - Run adgrants.sql
Run "adgrants.sql" script from $APPL_TOP/admin
[oraebs@erpnode3 db_scripts]$ ls
adgrants.sql preupgrd.sql utluppkg.sql
[oraebs@erpnode3 db_scripts]$ mv adgrants.sql adgrants.sql.12.2.0
[oraebs@erpnode3 db_scripts]$ cp /u01/appl_test/fs1/EBSapps/appl/admin/adgrants.sql .
[oraebs@erpnode3 db_scripts]$ ls -lrt
total 596
-rwxr-xr-x 1 oraebs dbaerp 99663 Nov 4 21:11 adgrants.sql.12.2.0
-rw-r--r-- 1 oraebs dbaerp 5231 Nov 14 23:48 preupgrd.sql
-rw-r--r-- 1 oraebs dbaerp 381893 Nov 14 23:53 utluppkg.sql
-rwxr-xr-x 1 oraebs dbaerp 99663 Nov 20 00:14 adgrants.sql
[oraebs@erpnode3 db_scripts]$
SQL>@adgrants.sql APPS
Connected.
---------------------------------------------------
--- adgrants.sql started at 2014-11-20 00:16:02 ---
Creating PL/SQL profiler objects.
PL/SQL procedure successfully completed.
End of PURGE DBA_RECYCLEBIN.
Commit complete.
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oraebs@erpnode3 db_scripts]$
3.5 - Grant create procedure privilege on CTXSYS
Run script "adctxprv.sql" from $AD_TOP/sql by connecting as SYSDBA user
[oraebs@erpnode3 db_scripts]$ cp /u01/appl_test/fs1/EBSapps/appl/ad/12.0.0/patch/115/sql/adctxprv.sql .
[oraebs@erpnode3 db_scripts]$ sqlplus
SQL*Plus: Release 12.1.0.1.0 Production on Thu Nov 20 09:24:00 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name: apps
Enter password:
Last Successful login time: Thu Nov 20 2014 09:21:59 +03:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>@adctxprv.sql manager CTXSYS
Connecting to SYSTEM
Connected.
PL/SQL procedure successfully completed.
Commit complete.
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oraebs@erpnode3 db_scripts]$
3.6 - Compile Invalid Objects
[oraebs@erpnode3 db_scripts]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Thu Nov 20 09:25:57 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql
3.7 - Set CTXSYS parameter
Execute following procedure with "SYSDBA" account
SQL> exec ctxsys.ctx_adm.set_parameter('file_access_role', 'public');
PL/SQL procedure successfully completed.
SQL>
3.8 - Validate Workflow ruleset
On admin server node, run script "$FND_TOP/patch/115/sql/wfaqupfix.sql" as below:
[applebs@erpnode3 sql]$ pwd
/u01/appl_test/fs1/EBSapps/appl/fnd/12.0.0/patch/115/sql
[applebs@erpnode3 sql]$ ls -lrt wfaqupfix.sql
-rwxr-xr-x 1 applebs dbaerp 4942 Nov 6 18:39 wfaqupfix.sql
[applebs@erpnode3 sql]$ sqlplus
SQL*Plus: Release 10.1.0.5.0 - Production on Thu Nov 20 09:32:35 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: apps
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>@wfaqupfix.sql APPLSYS APPS
PL/SQL procedure successfully completed.
Commit complete.
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[applebs@erpnode3 sql]$
3.9 - Create context file and run autoconfig on dbTier
In 12c Oracle Home "appsutil" directory doesn’t exists, We have to copy the appsutil directory from 11g Oracle Home to 12c Oracle Home.
§ Run script to generate latest appsutil.zip
§ Copy appsutil directory from 11g Home to 12c Home
§ copy and unzip appsutil.zip in 12c Oracle Home
§ Generate new contextfile using "adbldxml.pl" script
§ Execute adconfig.sh script
[applebs@erpnode3 bin]$ ls -l admkappsutil.pl
-rwxr-xr-x 1 applebs dbaerp 7232 Nov 24 2012 admkappsutil.pl
[applebs@erpnode3 bin]$ perl admkappsutil.pl
Starting the generation of appsutil.zip
Log file located at /u01/appl_test/fs1/inst/apps/test_erpnode3/admin/log/MakeAppsUtil_11200942.log
output located at /u01/appl_test/fs1/inst/apps/test_erpnode3/admin/out/appsutil.zip
MakeAppsUtil completed successfully.
[applebs@erpnode3 bin]$
Copy Existing appsutil directory from 11gR2 Home to 12cR1 Home.
Run script "$AD_TOP/bin/admkapsutil.pl" to generate new appsutil.zip file, It will generate file in $INST_TOP/out directory. Copy this file to 12c Oracle Home and unzip it.
[oraebs@erpnode3 12.1.0]$ cp -pr /u01/ora_test/11.2.0/appsutil .
[oraebs@erpnode3 12.1.0]$ cp /u01/appl_test/fs1/inst/apps/test_erpnode3/admin/out/appsutil.zip .
[oraebs@erpnode3 12.1.0]$ ls -lrt appsutil.zip
-rw-r--r-- 1 oraebs dbaerp 3538270 Nov 20 09:45 appsutil.zip
[oraebs@erpnode3 12.1.0]$
- Unzip file "appsutil.zip" file from 12.1 Home
[oraebs@erpnode3 12.1.0]$ unzip -o appsutil.zip
Archive: appsutil.zip
inflating: appsutil/template/adclobtmp.sql
inflating: appsutil/template/afinit_db112.ora
inflating: appsutil/template/adstrtdb.sql
inflating: appsutil/template/adregtools.drv.........................................
[oraebs@erpnode3 bin]$ perl adbldxml.pl
Starting context file generation for db tier..
Using JVM from /u01/ora_test/12.1.0/appsutil/jre/bin/java to execute java programs..
APPS Password:
The log file for this adbldxml session is located at:
/u01/ora_test/12.1.0/appsutil/log/adbldxml_11200955.log
Could not Connect to the Database with the above parameters, Please answer the Questions below
Enter Hostname of Database server: erpnode3
Enter Port of Database server: 1529
Enter SID of Database server: test
Enter Database Service Name: test
Enter the value for Display Variable: 0.0
The context file has been created at:
/u01/ora_test/12.1.0/appsutil/test_erpnode3.xml
[oraebs@erpnode3 bin]$
- Execute autoconfig on database Tier:
[oraebs@erpnode3 appsutil]$ pwd
/u01/ora_test/12.1.0/appsutil
[oraebs@erpnode3 appsutil]$ cd bin/
[oraebs@erpnode3 bin]$ sh adconfig.sh
Enter the full path to the Context file: /u01/ora_test/12.1.0/appsutil/test_erpnode3.xml
Enter the APPS user password:
The log file for this session is located at: /u01/ora_test/12.1.0/appsutil/log/test_erpnode3/11201009/adconfig.log
AutoConfig is configuring the Database environment...
AutoConfig will consider the custom templates if present.
Using ORACLE_HOME location : /u01/ora_test/12.1.0
Classpath : :/u01/ora_test/12.1.0/jdbc/lib/ojdbc6.jar:/u01/ora_test/12.1.0/appsutil/java/xmlparserv2.jar:/u01/ora_test/12.1.0/appsutil/java:/u01/ora_test/12.1.0/jlib/netcfg.jar:/u01/ora_test/12.1.0/jlib/ldapjclnt12.jar
Using Context file : /u01/ora_test/12.1.0/appsutil/test_erpnode3.xml
Context Value Management will now update the Context file
Updating Context file...COMPLETED
Attempting upload of Context file and templates to database...COMPLETED
Updating rdbms version in Context file to db121
Updating rdbms type in Context file to 64 bits
Configuring templates from ORACLE_HOME ...
AutoConfig completed successfully.
[oraebs@erpnode3 bin]$
- Run autoconfig on appsTier Run &Patch File system:
Execute autconfig on run filesystem by sourcing environmental variables for RUN file system and on patch file system after sourcing environmental variable on patch file system.
Ignore errors encountered while running autoconfig on patch file system.
[applebs@erpnode3 scripts]$ adautocfg.sh
Enter the APPS user password:
The log file for this session is located at: /u01/appl_test/fs1/inst/apps/test_erpnode3/admin/log/11201225/adconfig.log
AutoConfig is configuring the Applications environment...
AutoConfig will consider the custom templates if present.
Using CONFIG_HOME location : /u01/appl_test/fs1/inst/apps/test_erpnode3
Classpath : /u01/appl_test/fs1/FMW_Home/Oracle_EBS-app1/shared-libs/ebsappsborg/WEBINF/lib/ebsAppsborgManifest.jar:/u01/appl_test/fs1/EBSapps/comn/java/classes
Using Context file : /u01/appl_test/fs1/inst/apps/test_erpnode3/appl/admin/test_erpnode3.xml
Context Value Management will now update the Context file
Updating Context file...COMPLETED
Attempting upload of Context file and templates to database...COMPLETED
Configuring templates from all of the product tops...
Configuring AD_TOP........COMPLETED
Configuring FND_TOP.......COMPLETED
Configuring ICX_TOP.......COMPLETED
Configuring MSC_TOP.......COMPLETED
Configuring IEO_TOP.......COMPLETED
...................................
...................................
Configuring GMF_TOP.......COMPLETED
Configuring PON_TOP.......COMPLETED
Configuring FTE_TOP.......COMPLETED
Configuring ONT_TOP.......COMPLETED
Configuring AR_TOP........COMPLETED
Configuring AHL_TOP.......COMPLETED
Configuring IES_TOP.......COMPLETED
Configuring OZF_TOP.......COMPLETED
Configuring CSD_TOP.......COMPLETED
Configuring IGC_TOP.......COMPLETED
AutoConfig completed successfully.
[applebs@erpnode3 scripts]$
3.10 - Run script "adstats.sql" to gather SYS stats
Run script "$APPL_TOP/admin/adstats.sql" as SYSDBA user to gather statistic for sys user. Enable restricted mode before running the script and should be disabled once finished.
[oraebs@erpnode3 db_scripts]$ cd /u01/appl_test/fs1/EBSapps/appl/admin
[oraebs@erpnode3 admin]$ ls -l adstats.sql
-rwxr-xr-x 1 applebs dbaerp 2752 Nov 24 2012 adstats.sql
[oraebs@erpnode3 admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Thu Nov 20 13:53:08 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>alter system enable restricted session;
System altered.
SQL>@adstats.sql
Connected.
-------------------------------------------------
--- adstats.sql started at 2014-11-20 13:53:50 ---
Checking for the DB version and collecting statistics ...
PL/SQL procedure successfully completed.
------------------------------------------------
--- adstats.sql ended at 2014-11-20 14:30:35 ---
Commit complete.
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oraebs@erpnode3 admin]$
[oraebs@erpnode3 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Thu Nov 20 14:33:18 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>alter system disable restricted session;
System altered.
3.11 - Create new MGDSYS schema
MGDSYS schema was dropped before starting the upgrade. If you're planning to use MGDSYS schema then you should create it again by running script "@?/rdbms/admin/catmgd.sql"
SQL>@?/rdbms/admin/catmgd.sql
.. Creating MGDSYS schema
User created.
.. Granting permissions to MGDSYS
Grant succeeded.
Grant succeeded.
3.12 - Apply post upgrade WMS patches
If upgraded prior version to 12c we have to apply patch "19007053". As we are upgraded from version 11g then we should apply this patch.
[applebs@erpnode3 admin]$adop phase=apply patches=19007053 apply_mode=downtime
3.13 - Recreate grants and synonyms
Using adadmin from Admin server node recreate grants and synonyms for APPS schema:
AD Administration Main Menu
--------------------------------------------------
1. Generate Applications Files menu
2. Maintain Applications Files menu
3. Compile/Reload Applications Database Entities menu
4. Maintain Applications Database Entities menu
5. Exit AD Administration
Enter your choice [5] : 4
Maintain Applications Database Entities
---------------------------------------------------
1. Validate APPS schema
2. Re-create grants and synonyms for APPS schema
3. Maintain multi-lingual tables
4. Check DUAL table
5. Return to Main Menu
- Verify adadmin logfile for Errors.
3.14 - Start Application services
Start Application services using “adstrtal.sh" script and make sure all services started successfully without any errors.
- Login to WLS Admin server and check both Admin and managed servers are working normally.
- Login to Oracle Application and verify all functionalities are working normally.
3.15 - Run concurrent Request "Synchronize workflow views"
Run concurrent request by specifying the following parameters and make sure request completed successfully without any Issues.
3.16 - Verify the upgraded version from OAM
Login to OAM as sysadmin user:
Navigate to system administrator responsibility > sitemap > Monitor > database
4. Issues:
4.1 - Unable to proceed with 12c runInstaller with error "[INS-10102] Installer initialization failed"
Refer:
http://www.toadworld.com/platforms/oracle/b/weblog/archive/2014/11/14/ins-21003-installer-has-detected-that-an-invalid-inventory-pointer-location-file-was-specified-oracle-12c.aspx
4.2 - DBUA not listing the database
This is the fresh Installation of EBS R12.2. File "/etc/oratab" does not contain entry for SID. Hence DBUA was unable to detect SID from 11g Oracle home. Manually add entry in "/etc/oratab" file and rerun dbua.
4.3 - Invalid specification of system parameter "LOCAL_LISTENER"
"ORA-00119: Invalid specification for system parameter LOCAL_LISTENER"
"ORA-00132: syntax error or unresolved network name test_LOCAL"
This Issue occurred due to incorrect configuration of LOCAL_LISTENER parameter in dbua created spfile. This Issue can be avoided by setting the valid parameter of LOCAL_LISTENER in the spfile or by removing this parameter from spfile.
4.4 Application Database account locked after upgrade
After completion of upgrade noticed all database account are locked & Expired including APPS, APPLSYS and APPLSYSPUB except sys and system.
This is a known Issue if database is upgraded using 12c DBUA then it will expire all database accounts except SYS and SYSTEM.
Please refer section "3.3 - Application Database accounts Expired & Locked"
No comments:
Post a Comment