Issue with Oracle DB Upgrade 9.2.0.5 to 10.2.0.4 (Apps DB)

This blog is about error "insufficient Privileges" while connecting as sys as sysdba

SQL> conn / as sysdba
ERROR:
ORA-01031: insufficient privileges

When logging into the new ORACLE_HOME (to be upgraded)

I received this error. (ORA-01031)

OS: HP-UX 11.11 64 bit
OS_GROUP other than DBA My groups is USERS

Reason: in rdbms/lib config.c is hard-coded the group as "dba"


Solution:
1. Navigate to new ORACLE_HOME/rdbms/lib
2. Look for config.c
3. Backup config.o as config.o_bk
4. Backup config.c as config.c_bk
5. Open config.c
Change the lines as

Previous entry
/* SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative access. */
/* Refer to the Installation and User's Guide for further information. */

#define SS_DBA_GRP "dba"
#define SS_OPER_GRP "dba"

char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP};


Changed Section
Previous entry
/* SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative access. */
/* Refer to the Installation and User's Guide for further information. */

#define SS_DBA_GRP "users"
#define SS_OPER_GRP "users"

char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP};

6. run
make -f ins_rdbms.mk config.0 ioracle

once finished,

try to login as sysdba

it should work.

Cheers!!!!

Oracle Applications Upgrade to 12.1.2

Pre-requisites:

Applications should be of 12.1.1

For upgrading Oracle Applications from12.0.x to 12.1.1 please refer my blogpost

http://balajiabhi.blogspot.com/2009/07/upgrade-oracle-e-business-suite-1205-to.html

Web OH should be above 10.1.3.4

Step:1

Download AD_B_2 (8502056)

Download R121RUP2 (7303033)

Download Online Help patch (7303032)

Step:2

Apply 8502056:

Copy adgrants.sql from admin directory of the patch directory and copy to Database Oracle HOME/appsutil/admin

Source the Database environment file:

Run adgrants.sql as

Sqlplus /nolog

@adgrants.sql APPS

Source the Application Environment file

Apply the unified driver of 8502056 on the Application tier using adpatch

Be Sure that you have WEB_OH as 10.1.3.4 and above.

You can see that in WEB_OH (ORACLE_APPS_BASE/apps/tech_st/10.1.3/config/ias.properties) version.

Step 3:

Apply 7303033 using adpatch with default options.

• Be sure that you have enough space in APPS_TS_TX_DATA and APPS_TS_TX_IDX before starting the patch application.

After application of patch 7303033, it is advised to apply the online Help patch no. 7303032 using adpatch with default options

After application of these patches,

Please run autoconfig on the apps Tier and then make the appsutil.zip file from $AD_TOP/bin/perl

admkappsutil.pl

Copy the file to the $RDBMS_ORACLE_HOME and unzip the file.

Run autoconfig on the Database Tier.

It is mandatory to do a preclone on both the tiers which will enable to create a new clone directory structures during cloning.

Startup the instance.

Verify the upgrade using apps account in the backend

SQL> select release_name from FND_PRODUCT_GROUPS;

RELEASE_NAME

--------------------------------------------------

12.1.2

SQL>

Front end:

From the Forms:

Issues:

One object becomes invalid after application of the main patch.

OBJECT_NAME OWNER OBJECT_TYPE

------------------------------ ------------------- ----------------------------------

PA_PWP_SUMM_PKG APPS PACKAGE BODY

When you try to compile the object manually as apps, we got the following error:

SQL> alter package PA_PWP_SUMM_PKG compile body;

Warning: Package Body altered with compilation errors.

SQL> show err

Errors for PACKAGE BODY PA_PWP_SUMM_PKG:

LINE/COL ERROR

-------- -----------------------------------------------------------------

342/1 PL/SQL: SQL Statement ignored

375/10 PL/SQL: ORA-00942: table or view does not exist

I raised SR against this error. Let me update any fix for this. Until now, the upgrade is uneventful.

Cheers!!!!

Upgrade Oracle E Business Suite 12.0.X to 12.1.1

This blog is about to describe the upgrading from 12.0.x to 12.1.1

This includes Challanges and issues and fixes during the process of upgrade.

This document refers to Redhat Enterprise Linux 4 update 5., Database 10.2.0.2, Forms server version 10.1.2.0.1 and 10g AS Web server 10.1.3

Take a backup of the production before doing (Strong Recommendation)

Metalink Document to refer: 752619.1 (But many challanges ahead if you follow the document)

The as-is server is being 12.0.5 and database is of 10.2.0.2, we upgraded the Database to 10.2.0.4 using Patchset 6810189

Phase I: Process of Database Upgrade:

1. Unzip the p6810189_10204_Linux-x86.zip to a temporary directory which is obtained from metalink.

Source the Database Enviroment File from RDBMS_ORACLE_HOME.

Being the database is of version 10gR2 (10.2.0.2) no need to verify the timezone using v$timezone.

2. Shut down all the application services, DB, listener, isqlplus and emctl dbconsole.

Please do remember, Take a backup of the database and inventory.

3. Go to unzipped directory and run ./runInstaller

if you are using local inventory then use

./runInstaller -invPtrLoc $ORACLE_HOME/oraInst.loc

If you want to attach your home to the central inventory then do:

$ORACLE_HOME/oui/bin/runInstaller -silent -attachHome -invPtrLoc /etc/oraInst.loc ORACLE_HOME=$ORACLE_HOME ORACLE_HOME_NAME=(SID)_DB_HOME_Version

You will get your oracle Home attached with the central Inventory. You can verify using runInstaller -> Installed Products

Select the home and location from the OUI and then proceed to next.

Once completed, startup the database using

$sqlplus as sys user

SQL> startup upgrade;

SQL> spool 'upgrade10204.log'

SQL> @$ORACLE_HOME/rdbms/catupgrd.sql

At the end the script will return the results of upgrade validity. Verify the validity of the database after upgrade.

SQL> shut immediate;

SQL> Startup;

SQL> $ORACLE_HOME/rdbms/utlrp.sql

This will compile all the invalid objects that are added and not compiled during upgrade.

After compilation:

Shutdown the instance and apply the additional RDBMS Patches using Opatch for E Business Suite upgrade.

Please be sure to have OPatch in the PATH

To Include OPatch in the path use

$export PATH=$PATH:$ORACLE_HOME/OPatch

Using Opatch Apply the following Patches

4247037
6600051
6870937
6991626

Don't forget to run adstats.sql from $APPL_TOP/admin again the upgrade process of APPL_TOP. 12.1.1. patchset will run it during its run.

Startup the database. (Time taken for the process to complete ~1h 15mins)

Phase II: (Upgrade 10gAS 10.1.3 to 10.1.3.4)

Important:

1.Take a backup of inventory before proceeding.

2. Source the environment file from $INST_TOP/ora/10.1.3

3. Verify echo $ORACLE_HOME as $ORACLE_APPS_BASE/apps/tech_st/10.1.3

4. Ensure that all the OC4J services are up and running.

1. Unzip and Apply Patch 7272722 obtained from metalink using ./runInstaller

If you are using local inventory please use

./runInstaller -invPtrLoc $ORACLE_HOME/oraInst.loc

During the process, the OPMN services will be down and started up by the OUI.

When prompted please give the OC4J admin password as either 'secret' or 'oafm' (As I upgraded two instance, one (12.0.6) was successful with 'secret' and another (12.0.5) by 'oafm'.)

This will complete the 10gAS (10.1.3.0.1) to be upgraded with 10.1.3.4

Time taken for this procedure ~ 1 hour.

Shutdown the services, Run Autoconfig on the apps Tier.

As per the metalink document, dont upgrade Opatch version to 1.0.0.0.62, it failed two times for me during the other patches. I advise you to keep the current Opatch as opatch tool.

Be sure, the path of the Opatch is present in the PATH variable.

Apply the following patches:

(if you are using local inventory, then use

Patch_Directory$opatch apply -invPtrLoc $ORACLE_HOME/oraInst.loc)

1. 6702510

(The issue is that.. being a general platform Patch, Opatch will fail at the verification of platform)

use $export OPATCH_PLATFORM_ID=0,

then rerun the opatch apply.

2. Apply 6771776 (If you are getting error during opatch as Platform as 196 then use OPATCH_PLATFORM_ID = 46)

3. 7484199

4. 7411481

5. 7139320

6. 7139339

Post Upgrade steps

Source Application environment file

Regenerate appsborg.zip and appsborg2.zip using adadmin

Navigation: adadmin -> Choose Option 1-> Regenerate Product JAR files -> Force Option: No

Run autoconfig in application tier system.

Verify the Upgrade using:

either:

use ./runInstaller and then select installed products, it will show you the components from WEB_OH that are all upgraded to 10.1.3.4

or:

use opatch lsinventory -detail

Look for Oracle Application Server Patchset as 10.1.3.4 as follows:

Installed Patch List:

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

1) Patch 7139339 applied on Fri Jul 17 20:03:46 IST 2009 [ Base Bug(s): 7139339 ]

2) Patch 7139320 applied on Fri Jul 17 20:02:23 IST 2009 [ Base Bug(s): 7139320 ]

3) Patch 7411481 applied on Fri Jul 17 20:00:42 IST 2009 [ Base Bug(s): 7411481 ]

4) Patch 6771776 applied on Fri Jul 17 19:58:55 IST 2009 [ Base Bug(s): 6771776 ]

5) Patch 6702510 applied on Fri Jul 17 19:51:41 IST 2009 [ Base Bug(s): 6702510 ]

6) Patch 5586892 applied on Sat Dec 30 08:18:37 IST 2006

[ Base Bug(s): 5586892 5126270 4689959 5238255 ]

Verification from Opatch lsinventory -detail

oracle@xyz.com 7139339]$ opatch lsinventory -detail -
Oracle Interim Patch Installer version 1.0.0.0.56

Copyright (c) 2006 Oracle Corporation. All Rights Reserved..
We recommend you refer to the OPatch documentation underOPatch/docs for usage reference. We also recommend usingthe latest OPatch version. For the latest OPatch versionand other support related issues, please refer to document293369.1 which is viewable from metalink.oracle.com
Oracle Home : /oracle/ACE/apps/tech_st/10.1.3Oracle Home Inventory : /oracle/ACE/apps/tech_st/10.1.3/inventoryCentral Inventory : /oracle/ACE/inst/apps/ACE_samarth/admin/oraInventory from : /oracle/ACE/apps/tech_st/10.1.3/oraInst.locOUI location : /oracle/ACE/apps/tech_st/10.1.3/ouiOUI shared library : /oracle/ACE/apps/tech_st/10.1.3/oui/lib/linux/liboraInstaller.soJava location : /oracle/ACE/apps/tech_st/10.1.3/jre/1.4.2/bin/javaLog file location : /oracle/ACE/apps/tech_st/10.1.3/.patch_storage//*.log
Creating log file "/oracle/ACE/apps/tech_st/10.1.3/.patch_storage/LsInventory__07-17-2009_20-17-29.log"
Result:
PRODUCT NAME VERSION
============ =======
Agent Required Support Files Patch 10.1.0.5.0

Agent Required Support Files 10.1.0.2.0

Apache Module for Oracle Distributed Authoring and Versioning 10.1.2.1.0

Assistant Common Files Patch 10.1.0.5.0

Assistant Common Files 10.1.0.2.0

Bali Share 1.1.18.0.0

DataDirect Connect JDBC Drivers 10.1.2.0.1

DBJAVA Required Support Files Patch 10.1.0.5.0

DBJAVA Required Support Files 10.1.0.2.0

Documentation Required Support Files 10.1.0.3.0

Enterprise Manager Minimal Integration Patch 10.1.0.5.0

Enterprise Manager Minimal Integration 10.1.0.2.0

Enterprise Manager plugin Common Files 10.1.0.2.0

Enterprise Manager plugin Common Files 10.1.0.5.0

Extended Windowing Toolkit 3.3.18.0.0

HTTP Server Files 1.3.31.0.0

Oracle Application Server PatchSet 10.1.3.4.0

Oracle ASkernel Common 10.1.3.0.0

Oracle Business Rules 10.1.3.0.0

.

.
.
OPatch succeeded.

Now upgrade the Opatch using 6880880 (Unzip the patch directly into the 10.1.3 Oracle Home)

Do not Start the application services and try to login to the applications using the port. As it throws login page cannot be displayed as the variable in context are pointing to portal/AppsLogin.jsp.

This will be resolved once you complete applying the patch 7303030.

Phase 3: Upgrading the Java JDK

Download the latest JDK from http://java.sun.com/javase/downloads/index.jsp

download jdk-6u14-linux-i586.bin

1. Be sure to shutdown all the middle tier services.

(a) Perform JDK Upgrade in IAS_ORACLE_HOME:

Navigate to $IAS_ORACLE_HOME/appsutil/

$mv jdk/ jdk_old/

$mkdir jdk/

copy the jdk-6u14-linux-i586.bin to $IAS_ORACLE_HOME/appsutil/jdk

Install the bin file using

$./jdk-6u14-linux-i586.bin

This will install latest jdk in appsutil/jdk

Copy the *.ttf files from $FND_TOP/resource to $IAS_ORACLE_HOME/appsutil/jdk/jre/lib/fonts.

Important: You need to do the steps as a post upgrade step if you complete with 7303030

b) Upgrade jre in $RDBMS_ORACLE_HOME

copy the jdk-6u14-linux-i586.bin to $RDBMS_ORACLE_HOME/appsutil

Source the database environment file

1. Navigate to $ORACLE_HOME/appsutil/

2. mv jre/ jre_old/

3. mkdir jre/

Install the jdk_version_linus-i586.bin to appsutil/jre

Verification of JDK installation

$ADJVAPRG -version

[oracle@prod appl]$ $ADJVAPRG -version

"1.6.0_14"

Java(TM) SE Runtime Environment (build 1.6.0_14-b08)

Java HotSpot(TM) Client VM (build 14.0-b16, mixed mode, sharing)

echo $CLASSPATH

[oracle@prod appl]$ echo $CLASSPATH

/oracle/PRODN/apps/tech_st/10.1.3/appsutil/jdk/lib/dt.jar:

/oracle/PRODN/apps/tech_st/10.1.3/appsutil/jdk/lib/tools.jar:

/oracle/PRODN/apps/tech_st/10.1.3/appsutil/jdk/jre/lib/rt.jar:

/oracle/PRODN/apps/apps_st/comn/java/lib/appsborg.zip:

/oracle/PRODN/apps/tech_st/10.1.2/forms/java:

/oracle/PRODN/apps/tech_st/10.1.2/forms/java/frmall.jar:/oracle/PRODN/apps/tech_st/10.1.2/jlib/ewt3.jar:/

oracle/PRODN/apps/tech_st/10.1.2/j2ee/OC4J_BI_Forms/applications/formsapp/formsweb/WEB-INF/lib/frmsrv.jar:/oracle/PRODN/apps/apps_st/comn/java/classes

$AFJVAPRG -version

[oracle@prod appl]$ $AFJVAPRG -version

java version "1.6.0_14"

Java(TM) SE Runtime Environment (build 1.6.0_14-b08)

Java HotSpot(TM) Client VM (build 14.0-b16, mixed mode, sharing)

echo $AF_CLASSPATH

Each should reflect the paths belonging to latest JDK like this

[IAS_ORACLE_HOME]/appsutil/jdk/lib/dt.jar, [IAS_ORACLE_HOME]/appsutil/jdk/lib/tools.jar, [IAS_ORACLE_HOME]/appsutil/jdk/jre/lib/rt.jar, and [IAS_ORACLE_HOME]/appsutil/jdk/jre/lib/charsets.jar

Method:1

Create an jsp file under $OA_HTML like jdktest.jsp

The JDK version is:<%= System.getProperty("java.version") %> :<%= System.getProperty("java.version") % >

compile the jdktest.jsp using ojspcompile, syntax is:

perl -x $FND_TOP/patch/115/bin/ojspCompile.pl --compile -s jdktest.jsp'

Dont check it now, as your middle tier services are down.

Check with Database Tier:

$cd $ORACLE_HOME/appsutil/jre/bin

./java -version

[oracle@prod jre]$ cd bin

[oracle@prod bin]$

./java -version

java version "1.6.0_14"

Java(TM) SE Runtime Environment (build 1.6.0_14-b08)

Java HotSpot(TM) Client VM (build 14.0-b16, mixed mode, sharing)

Run Autoconfig on both tiers

Phase 4: Forms server Upgrade

In this Phase upgradation of Forms server from 10.1.2.0.1 to 10.1.2.3 is to be done.

Backup the inventory file.

Source the application environment file

1. Unzip 5983622 and run ./runInstall

2. Supply oc4jadmin as 'secret' when prompted.

(Note here configuration assistant will fail at oc4j configuration Assistant, Simply skip and click next. This is normal behavior for E Business Upgrade)

Once completed:

Apply the Following Patches using OPatch tool from TOOLS_ORACLE_HOME

p3559326_10105_LINUX.zip

p6880880_102000_LINUX.zip (Upgrade Opatch - Skip this patch)

p4407272_10123_LINUX.zip

p7016961_101210_LINUX.zip

p4526825_10123_LINUX.zip

p7016961_10123_LINUX.zip

p5394728_10105_LINUX.zip

p5917053_10123_LINUX.zip

p7121788_10123_LINUX.zip

p5983622_10123_LINUX.zip

p7140392_10123_GENERIC.zip

p6371228_10123_LINUX.zip

p7329300_10123_LINUX.zip

p6857221_10123_LINUX.zip

p8300196_10123_GENERIC.zip

p8374931_10123_GENERIC.zip

Note: if you face the error below during any of the Opatch or ./runInstaller: rwconverter error: like this

/ACE/apps/tech_st/10.1.2/lib//librw.a -lnsl/oracle/ACE/apps/tech_st/10.1.2//lib/

/librw.so: undefined reference to `rolgxinoci_cid'

collect2: ld returned 1 exit status

make: *** [rwbuilder] Error 1

DO the following:

Restore the file

librw.so, and librw.a

from old backup to $TOOLS_ORACLE_HOME/lib and run

$TOOLS_ORACLE_HOME/reports/lib/make -f ins_reports.mk install
The result will be
/oracle/ACE/apps/tech_st/10.1.2/lib,-rpath,/oracle/ACE/apps/tech_st/10.1.2/jdk/jre/lib/i386:/lib:/usr/lib -lm `cat /oracle/ACE/apps/tech_st/10.1.2/lib/sysliblist` -ldl -lpthread -lm -L/oracle/ACE/apps/tech_st/10.1.2/lib -L/oracle/ACE/apps/tech_st/10.1.2/lib/stubs/ -lsnls10 -lpthread -ljvm -lhpi -Wl,-rpath,/usr/X11R6/lib -L/usr/X11R6/lib -lXm -lXt -lX11 -lm -lXp -lXext /oracle/ACE/apps/tech_st/10.1.2/lib//librw.a -lnslmv rwbuilder /oracle/ACE/apps/tech_st/10.1.2/bin/rwbuilderchmod 700 /oracle/ACE/apps/tech_st/10.1.2/bin/rwbuilder

then proceed with the patch, it should complete normally.

Create formsapp.ear

$FND_TOP/bin/txkrun.pl -script=CfgOC4JApp

Enter Application name for re-deployment ? forms
Enter Oc4j Instance password for re-deployment ? welcome (or current password)
? No ">Run Autoconfig ? No

If you are prompted for oc4jadmin password

and is incorrect as 'secret' or 'oafm' then

Do the following:
$INST_TOP/ora/10.1.3/j2ee/forms/config/system-jazn-data.xml

$cp system-jazn-data.xml system-jazn-data-ori.xml

$vi system-jazn-data.xml
locate oc4j-admin section
[user]
[name]oc4jadmin[/name]
[display-name]OC4J Administrator[/display-name]
[guid]88836370D11611DC9F30F9C1CD6F1A73[/guid]
[description]OC4J Administrator[/description]
[credentials]{903}F+iG1A46edXG9RdfY0pD2O4Ge/qyEjsg[/credentials]
[/user]

replace
{903}F+iG1A46edXG9RdfY0pD2O4Ge/qyEjsg
with

!Welcome [Please note '!' is important]

then issue oc4jadmin password as 'Welcome'. it will pass the step.

Run Autoconfig on Apps Tier

Apply Interoperability Patch 7120543

Apply OPatch 6880880 as in the previous section to upgrade Opatch tool to 1.0.0.0.62

Rebuild Forms and Reports executables
cd $ORACLE_HOME/forms/lib32
Note: if this directory does not exist: cd $ORACLE_HOME/forms/lib
$ make -f ins_forms.mk install
cd $ORACLE_HOME/reports/lib32
Note: if this directory does not exist: cd $ORACLE_HOME/reports/lib
$ make -f ins_reports.mk install

Regenerate appsborg.zip and appsborg2.zip using adadmin

Phase V upgrading to 12.1.1

Apply AD.B.1 Patch (7461070)

Copy adgrants.sql from patch/admin directory to $RDBMS_ORACLE_HOME/appsutil/admin directory

If it is not present create a directory /admin inside $RDBMS_ORACLE_HOME/appsutil

run adgrants.sql as sys user with syntax

$adgrants.sql apps

once finished, Apply the patch AD.B.1

Recreate grants and synonyms using adadmin

Apply the Main patch 7303030 (Contains 8 parts)

During the patch run, please expect

XDOLoader.class will fail on user xdo

Follow the procedure from http://balajiabhi.blogspot.com/2009/07/rebuilding-xdo-objects-failed-worker-on.html

(Completed within 15 hours)

Apply online Help patch 7303031 using adpatch. (It takes 28 minutes)

Apply the product specific patches as per the metalink document stated above.

Start the application services using adstrtal.sh from $ADMIN_SCRIPT_HOME

Verify the forms version using 'About Applications' from Forms session.

It should show the version as follows

To get other versions, enable about this page link in LOGINPAGE of the server
To do this please do the following:

set the profile option FND: Diagnostics = Yes

Then bounce the middle tier services, you will see a link 'About This Page' at the bottom of the web page

The output of the About this page will give all the particulars about the version upgrade.

Cheers!!! and Best of Luck!!!

FRM-92150: WEB client version too new

The error i faced after upgradation of 12.0.6 to 12.1.1 during forms startup.

FRM-92150: WEB Client version too new

The fix is as below:
Go to TOOLS_ORACLE_HOME (10.1.2)
cd forms/lib (in case of Linux) forms/lib32 (in case of other OS)
run
make -f ins_forms.mk sharedlib
it will create shared library functionalites again to run the forms.
The error will not be there..

Rebuilding XDO objects (Failed worker on 12.1.1 upgradation)

To build XDO objects if you face table or view not found during patching and if worker fails with status Failed on XDOLoader.class on product xdo on user xdo

Then try this:
First verify the application has been licensed with xdo.
1. select object_name,object_type,owner from dba_objects where object_name like 'XDO%';
you should have returned with 153 rows.
2. Select application_id,application_short_name from apps.fnd_application where application_short_name = 'XDO';

3.Recreate objects in XDO using
goto:
$XDO_TOP/patch/115/odf

use the command:
adodfcmp odffile=XDOTMGR.odf userid=applsys/apps changedb=yes priv_schema=system/manager mode=tables touser=apps/apps logfile=xdotables.log

adodfcmp odffile=XDOTMGR.odf userid=applsys/apps changedb=yes priv_schema=system/manager mode=indexes touser=apps/apps logfile=xdoindexes.log

adodfcmp odffile=XDOTMGR.odf userid=applsys/apps changedb=yes priv_schema=system/manager mode=sequences touser=apps/apps logfile=xdosequenc.log

adodfcmp odffile=XDOTMGR.odf userid=applsys/apps changedb=yes priv_schema=system/manager mode=views touser=apps/apps logfile=xdoviews.log

Rerun the patch or restart the failed worker.