Glimpses of E-Business Suite 12.2: WebLogic Server and Online Patching

Finally 12.2 is on the way. Read more from Steven Chan blog
There are lots of updates coming in this release, but from a technology stack perspective, EBS 12.2 will be notable for two things:

1. Replacing Oracle Containers for Java (OC4J) 10g with WebLogic Server 11g
2. Online Patching support via 11gR2 Edition-Based Redefinition

Cheers!!!

ORA-20011: Approximate NDV failed: ORA-00600: internal error code, arguments

Windows 2008 Enterprise
Oralce 11.2.0.2
After upgrade from 11.1.0.7 to 11.2.0.2 release, as part of R12 dbtier post-installation procedure, required to run adstats.sql and getting the following error

ERROR at line 1:
  ORA-20011: Approximate NDV failed: ORA-00600: internal error code, arguments:
  [1350], [1], [23], [], [], [], [], [], [], [], [], []
  ORA-06512: at "SYS.DBMS_STATS", line 24098
  ORA-06512: at "SYS.DBMS_STATS", line 24629
  ORA-06512: at line 3
  ORA-06512: at line 33

Finally found the cause of the error, environment variable ORA_NLS10 not set even though successfully completed the  $ORACLE_HOME/nls/data/old/cr9idata.pl executed to create $ORACLE_HOME/nls/data/9idata from upgrade notes

set ORA_NLS10=%ORACLE_HOME%\nls\data\9idata

Retest the issue by running adstats.sql again .

adstats.sql should run successfully now.


Cheers!!!

FNDCPASS Fails With "ORA-01017" Error When Changing Password with 11G Database

After upgrading to 12.0 and database from 10.2.0.2 to 11.1.0.6 getting 'ORA-01017' error when tried to change the application password using FNDCPASS utility.

The reason for this issue is that, database SEC_CASE_SENSITIVE_LOGON parameter defaults to TRUE in 11G.  When this occurs the password sensitivity conversion does not occur. Passwords that are input as lower case are automatically updated as upper case.  As a workaround set the parameter to FALSE in the init.ora, run autoconfig on application tiers and bounce the database.

Please refer Note 567116.1 Init.ora Parameter "SEC_CASE_SENSITIVE_LOGON"


Cheers!!!

Oracle 11g R2 Patchset 1 (11.2.0.2) installation on OEL 5.5

Please makes sure that the following or later versions of packages are successfully installed along with operating system. These are pre-requisite for oracle 11g installation.

binutils-2.17.50.0.6

compat-libstdc++-33-3.2.3

elfutils-libelf-0.125

elfutils-libelf-devel-0.125

elfutils-libelf-devel-static-0.125

gcc-4.1.2

gcc-c++-4.1.2

glibc-2.5-24

glibc-common-2.5

glibc-devel-2.5

glibc-headers-2.5

kernel-headers-2.6.18

ksh-20060214

libaio-0.3.106

libaio-devel-0.3.106

libgcc-4.1.2

libgomp-4.1.2

libstdc++-4.1.2

libstdc++-devel-4.1.2

make-3.81

numactl-devel-0.9.8.i386

sysstat-7.0.2

To use ODBC, you must also install the following additional 32-bit ODBC RPMs, depending on your operating system:

unixODBC-2.2.11 (32-bit) or later
unixODBC-devel-2.2.11 (32-bit) or later

Modify of add these entries in /etc/sysctl.conf using vi editor

fs.aio-max-nr = 1048576

fs.file-max = 6815744

kernel.shmall = 2097152

kernel.shmmax = 536870912

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

net.ipv4.ip_local_port_range = 9000 65500

net.core.rmem_default = 262144

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem_max = 1048586

If necessary, update the resource limits in the /etc/security/limits.conf configuration file for the installation owner. For example, add the following lines to the /etc/security/limits.conf file:

oracle              soft    nproc   2047

oracle              hard    nproc   16384

oracle              soft    nofile  1024

oracle              hard    nofile  65536

oracle              soft    stack   10240

Modify or add the following entry in /etc/pam.d/login using vi editor

session required pam_limits.so

Next create the group and user for oracle database

/usr/sbin/groupadd -g 500 oinstall

/usr/sbin/groupadd -g 501 dba

/usr/sbin/groupadd -g 502 oper

/usr/sbin/useradd -u 500 -m -g oinstall -G dba,oper oracle

id oracle

passwd oracle

Create the installation directory for oracle installation

mkdir -p /u01/oracle

chown -R oracle:dba /u01/oracle

chmod -R 775 /u01/oracle

Create staging directory for oracle installation

mkdir -p /u01/stage

chown -R oracle:dba /u01/stage

After unzipping the installation files change to the directory containing uninstaller. You must be the oracle user (not root) and you must verify your shell is set correctly. As the oracle user and start the Oracle installer.

$ cd /u01/stage/database

$./runInstaller.

You can ignore this email setting

You can skip software updates

From installation option, select create and configure a database.

From the server class, choose server class

For a standalone database choose the first option.

Choose Install type as Advance Install.

Database Edition should be Enterprise Edition.

As we created earlier, choose /u01/oracle as Base and Software location will be similar to the below picture

This database is for OBIEE 11g, so please choose Data Warehousing

Here you can edit your database name.

If this database is for demo purpose, so I have to keep with minimum configuration

You can choose File System. But if you are planning for ASM refer configuration details from installation document.

Better to choose the second option this time.

Accept the defaults

This is a typical result. Based on your OS configuration, please read the oracle Installation documents for more info.

The following information is good for future reference.

This will take some time to complete. Be ready for that…

Now the database creation is going on.

Finally database configuration success. If you want to change the password, you can use Password Management button.

From this screen, will prompt to do two things. As a root, you have executed these commands.

Now database 11gR2 (11.2.0.2) is ready for use.

1. Oracle Enterprise Linux 5.5 Installation
2. Repository Creation Utility
3. Oracle Business Intelligence 11g Installation

Cheers!!!

Oracle Enterprise Linux 5.5 Installation

The installation is almost entirely the same as the Red Hat Enterprise Linux 5 installation. First of all boot from the CD or DVD. At the boot screen, press the "Enter" key.

Press the "tab" key to move focus to the "Skip" key, and then press the "Enter" key to continue. On the "Welcome" screen, click the "Next" button..

Select the appropriate language, and then click the "Next" button.  From the next screen select the relevant keyboard setting, and then click the "Next" button

From the next screen click the "Yes" button on the disk partitioning warning dialog. Check the "Review and modify partitioning layout" option, and then allow the installer to automatically partition the disk by clicking on the "Next" button. Click the "Yes" button on the subsequent warning dialog. The installer will list the default partitioning scheme for your size disk. The partitions screen should now looks something like the following picture, then click the "Next" button

Accept the boot loader settings by clicking the "Next" button.

Configure the network interface with the appropriate settings. This can be done automatically using DHCP, or manually. If you are not using DHCP, enter the appropriate IP address and subnet mask, and then click the "OK" button.

If you are not using DHCP, enter the host name, gateway and DNS information, and then click the "Next" button.

Select the relevant region by clicking on the map. From the next select the relevant city by clicking on the map. Click on the "Next" button to proceed.

Enter a root password for the server, and then click the "Next" button to proceed.

Select the "Customize now" option and the appropriate installation type and click the "Next" button

The "Package Group Selection" screen allows you to select the required package groups, and individual packages within the details section. When you've made your selection, click the "Next" button

On the "About to install" screen, click the "Next" button and click the "Continue" button on the "Required Install Media" screen. During this installation phase, enter the appropriate CDs as requested.

Click the "Reboot" button to complete the installation.

On the "Welcome" screen, click the "Forward" button

Accept the license agreement and click the "Forward" button. From Firewall screen, choose the "Disabled" option and click the "Forward" button. Click the "Yes" button on the subsequent warning screen

On the SELinux screen, choose the "Disabled" option and click the "Forward" button prompt click the "Yes" button on the subsequent warning screen

Next screen will prompt Accept the default setting on the Kdump screen by clicking the "Forward" button. Adjust the Date and Time settings if necessary, and click the "Forward" Button. Create an additional system user if required, and click the "Next" button. If you chose not to define an additional system user, click the "Continue" button on the resulting warning dialog.

On the sound card screen, click the "Forward" button. On the "Additional CDs" screen, click the "Finish" button. Click the "OK" button on the reboot request dialog. Once the system has rebooted, you are presented with the login screen. Once logged in, you are ready to use the desktop.

1. Oracle Database 11g R2 Installation
2. Repository Creation Utility
3. Oracle Business Intelligence 11g Installation

Cheers!!!

R12 - Upgrade first patchset (11.2.0.2) for the 11gR2 Database

Upgrading an R12 Database to Oracle Database 11g Release 2 (11.2.0.2)

Recently Oracle had certified R12 database 11.2.0.2, the first patchset for the 11gR2 Database. I have successfully completed the installation. Have a look at it

Application Version : E-Business Suite Release 12.1.1
Database Version : 10.2.0.4
OS : Enterprise Linux AS Release 4.6
Current DB Home /u01/ajidb/10.2.0
New Home /u01/oracle/AJI/db/tech_st/11.2.0

I have followed these steps to achieve this.

Apply the following patches

1. 11g Release 2 interoperability patch for Release 12.1 (9062910)
2. 12.1 TXK Delta 3 patch (8919489)
3. 9868229
4. 10024524

AutoConfig Latest version with 9852070 and 9738085

Source the Application Tier environemnt file

. /u01/oracle/AJI/apps/apps_st/appl/APPSAJI_visr12.env

Enable maintenance mode

[appsR12@visr12 ~]$ adadmin defaultsfile=$APPL_TOP/admin/$TWO_TASK/adalldefaults.txt logfile=adadmin.log menu_option=ENABLE_MAINT_MODE workers=4

 export PATCH_TOP=~/upgradeR12db_11.2.0.2/

Apply 11g Release 2 interoperability patch for Release 12.1 (9062910)

adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/adalldefaults.txt logfile=u9062910.log patchtop=$PATCH_TOP/9062910 driver=u9062910.drv workers=4

apply Patch 12.1 TXK Delta 3 patch (8919489)
Step :1
adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/adalldefaults.txt logfile=u8919489.log patchtop=$PATCH_TOP/8919489 driver=u8919489.drv workers=4
Step: 2
 1.1. On the Application Tier (as the APPLMGR user):
        1.1.1. Source the environment file.
        1.1.2. Create the appsutil.zip file by executing:

               $ADPERLPRG $AD_TOP/bin/admkappsutil.pl
               This will create appsutil.zip under $INST_TOP/admin/out

   1.2. On the Database Tier (as the ORACLE user):
        1.2.1. Copy or FTP the appsutil.zip file to the [RDBMS ORACLE_HOME]
        1.2.2. cd [RDBMS ORACLE_HOME]
        1.2.3. unzip -o appsutil.zip
        1.2.4. Run AutoConfig by executing:
               [RDBMS_ORACLE_HOME]/appsutil/scripts/[CONTEXT_NAME]/adautocfg.sh

apply Patch 9868229
adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/adalldefaults.txt logfile=u9868229.log patchtop=$PATCH_TOP/9868229 driver=u9868229.drv workers=4

apply Patch 10024524

adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/adalldefaults.txt logfile=u10024524.log patchtop=$PATCH_TOP/10024524 driver=u10024524.drv workers=4

Apply Latest veriosn AutoConfig 9852070 and 9738085

adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/adalldefaults.txt logfile=u9738085.log patchtop=$PATCH_TOP/9738085 driver=u9738085.drv workers=4

adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/adalldefaults.txt logfile=u9852070.log patchtop=$PATCH_TOP/9852070 driver=u9852070.drv workers=4

1.1. On the Application Tier (as the APPLMGR user):
        1.1.1. Source the environment file.
        1.1.2. Create the appsutil.zip file by executing:

               $ADPERLPRG $AD_TOP/bin/admkappsutil.pl
               This will create appsutil.zip under $INST_TOP/admin/out

   1.2. On the Database Tier (as the ORACLE user):
        1.2.1. Copy or FTP the appsutil.zip file to the [RDBMS ORACLE_HOME]
        1.2.2. cd [RDBMS ORACLE_HOME]
        1.2.3. unzip -o appsutil.zip
        1.2.4. Run AutoConfig by executing:
               [RDBMS_ORACLE_HOME]/appsutil/scripts/[CONTEXT_NAME]/adautocfg.sh

Install the 11.2.0.2 software downloaded from http://updates.oracle.com/download/10098816.html

In the Installation Types window, select the option install the database software only.
The 11.2.0 Oracle home must be installed on the database server node in a different directory than the current Oracle home.
New Oracle Home /u01/oracle/AJI/db/tech_st/11.2.0

Also required to install Oracle Database 11g Products from the 11g Examples CD

Once installed  base 11.2.0 software, need to set the following in .bash_profile of oracle user
ORACLE_SID=AJI;export ORACLE_SID
ORACLE_BASE=/u01/oracle/AJI;export ORACLE_BASEORACLE_HOME=$ORACLE_BASE/db/tech_st/11.2.0;export ORACLE_HOME
LIBPATH=$ORACLE_HOME/lib:$ORACLE_HOME/odbc/lib;export LIBPATHLD_LIBRARY_PATH=$ORACLE_HOME/lib;export LD_LIBRARY_PATH
PATH=$ORACLE_HOME/bin:$PATHPERL5LIB=$ORACLE_HOME/perl/lib/5.10.0;export PERL5LIBPERL5LIB=$PERL5LIB:$ORACLE_HOME/perl/lib/site_perl/5.10.0;export PERL5LIB

Create nls/data/9idata directory

perl $ORACLE_HOME/nls/data/old/cr9idata.pl
This will create create the $ORACLE_HOME/nls/data/9idata directory.

ORA_NLS10=/u01/oracle/AJI/db/tech_st/11.2.0/nls/data/9idata;export ORA_NLS10

Apply any other additional 11.2.0.2 RDBMS patches

Also required to Apply the following patches:

4247037
10149223

Now you can do the Database Upgrade from 10.2.0.4.0 to 11.2.0.2.0:

Shut down Applications server processes and database listener

I have used Database Upgrade Assistant (DBUA) to do the upgradation from 10.2.0.4 to 11.2.0.2. This will take some times to complete based on your hardware.

Now required to upgrade Oracle E-Business Suite has only one statistics table, APPLSYS.FND_STATTAB. Please execute the following

Connect to SQL*Plus as sysadmin user and run the following line:
EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('APPLSYS','FND_STATTAB');

Next step is that modify initialization parameters for R12.
I have followed the Note: 396009.1, Database Initialization Parameter Settings for Oracle Applications Release 12

Perform patch post-install instructions

Run only the catmgdidcode.sql and utlrp.sql scripts for the post install instructions in patch 9218789.

After the Database Upgrade:

Start the new database listener
If the Oracle Net listener for the database instance in the new Oracle home has not been started, you must start it now. Since AutoConfig has not yet been implemented, start the listener manually.

Set the TNS_ADMIN environment variable to the directory where you created your listener.ora and tnsnames.ora file


Run adgrants.sql

Copy $APPL_TOP/admin/adgrants.sql from the administration server node to the database server node.
$ sqlplus "/as sysdba" @adgrants.sql APPS

Grant create procedure privilege on CTXSYS

Copy $AD_TOP/patch/115/sql/adctxprv.sql from the administration server node to the database server node.
$ sqlplus apps/[APPS password] @adctxprv.sql [SYSTEM password] CTXSYS

Set CTXSYS parameter
Use SQL*Plus to connect to the database as SYSDBA and run the following command:

$ sqlplus "/ as sysdba"
SQL> exec ctxsys.ctx_adm.set_parameter('file_access_role', 'public');

Validate Workflow ruleset
On the administration server node, use SQL*Plus to connect to the database as APPS and run the $FND_TOP/patch/115/sql/wfaqupfix.sql script using the following command:

$ sqlplus [APPS user]/[APPS password] @wfaqupfix.sql\
  [APPLSYS user] [APPS user]

Implement and run AutoConfig

Implement and run AutoConfig in the new Oracle home on the database server node. If the database listener of the new Oracle home is defined differently than the old Oracle home, you must also run AutoConfig on each application tier server node to update the system with the new listener.

Gather statistics for SYS schema
Copy $APPL_TOP/admin/adstats.sql from the administration server node to the database server node.
$ sqlplus "/ as sysdba"
SQL> alter system enable restricted session;
SQL> @adstats.sql
$ sqlplus "/ as sysdba"
SQL> alter system disable restricted session;
SQL> exit;

Re-create custom database links, if any.

Re-create grants and synonyms

Oracle Database 11g Release 2 (11.2) contains new functionality for grants and synonyms compared to previous database releases. As a result, you must re-create the grants and synonyms in the APPS schema. On the administration server node, as the owner of the Applications file system, run AD Administration and select the "Recreate grants and synonyms for APPS schema" task from the Maintain Applications Database Objects menu.


Restart Applications server processes

Restart all the Application tier server processes that you shut down previously. Remember that the Oracle Net listener for the database instance, as well as the database instance itself, need to be started in the 11.2 Oracle home. Users may return to the system.


Synchronize Workflow views
Log on to Oracle E-Business Suite with the "System Administrator" responsibility. Click Requests > Run > Single Request and the OK button. Enter the following parameters:

Request Name = Workflow Directory Services User/Role Validation
p_BatchSize = 10000
p_Check_Dangling = Yes
Add missing user/role assignments = Yes
Update WHO columns in WF tables = No
Click "OK" and "Submit".

Please refer Interoperability Notes EBS R12 with Database 11gR2 (1058763.1)

Cheers!!!

Patch 7684818 conflicts with other patches

As per the interoperability Note for Release 12.1 with DB 11gR1 (802875.1)
When attempting to apply the DB Patch 7684818 following error occurs as part of database upgrade to 11.0.x.

WARNING:OUI-67301:
Following patches have conflicts: [ 8833297 6972189 7111245 7295298 ]
Please contact Oracle Support Services to resolve patch conflicts.

To fix this error, please execute following steps :
1.Open a new Shell and source the DB Environment
2.cd $ORACLE_HOME/OPatch
3.opatch napply [Path where the DB Patches are stored]/7684818 -id 4247037,6263237,6815733, \ 6991626,7000281,7243270,7277741,7319922,7327166,7684818 -skip_duplicate

Apply the DB Patch again.


Cheers!!!

Missing Oracle Text Supplied Knowledge Base after 10gR2 database installation

After applying the 10.2.0.2.0, 10.2.0.3.0 or 10.2.0.4 Patchset and attempting to use theme functionality this will fail with errors like the following:

ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-11422: linguistic initialization failed
DRG-11446: supplied knowledge base file
/u1/app/oracle/product/10.2.0/ctx/data/enlx/droldUS.dat not installed
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 364
Or while running RepCA you receive a warning message that says:
"Oracle Text Knowlege Base is not installed."

Solution
1) You will need to install Oracle Text knowledge base from the 10.2.0.1.0 Companion CD, please refer to the Companion CD Installation Guide specific to your platform for specific details.
2) Afterwards you must re-apply 10.2.0.2.0, 10.2.0.3.0 or 10.2.0.4.0 Patchset. Start up the runInstaller once again and select the 10.2.0.2.0, 10.2.0.3.0 or 10.2.0.4.0 products.xml file once again in the "Specify File Locations" screen. The OUI will see the new components installed and will only install the 10.2.0.2.0, 10.2.0.3.0 or 10.2.0.4.0 patchsets associated to the new products.
3) Click on 'Install' on the Summary Screen (On 10.2.0.3.0 there should be roughly 13 "New Installations" items that are being installed)

4) After this then re-run catupgrd.sql script once again and this will correct the situation.

Same steps are applicable when Ultra Search product is required and was not installed with Oracle Database 10g release 2 (10.2.0.1.0) software.


Cheers!!!

Gather Schema Statistics fails with Ora-20001 errors after 11G database upgrade

Gather Schema Statistics" program reported following errors in request log files :

Error #1: ERROR: While GATHER_TABLE_STATS: 
object_name=GL.JE_BE_LINE_TYPE_MAP***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt*** 
Error #2: ERROR: While GATHER_TABLE_STATS: 
object_name=GL.JE_BE_LOGS***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt*** 
Error #3: ERROR: While GATHER_TABLE_STATS: 
object_name=GL.JE_BE_VAT_REP_RULES***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***

Error #4: ERROR: While GATHER_TABLE_STATS:  object_name=FII.FII_FIN_ITEM_HIERARCHIES***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***

There are two reasons for that error message:

1 ) There are duplicate rows on FND_HISTOGRAM_COLS table for JE_BE_LINE_TYPE_MAP table.
Because of this problem, FND_STATS tries to gather histogram information using wrong command and
it fails with ora-20001 errors.

Following SQL should have returned one row , not two.

SQL> select a.column_name, nvl(a.hsize,254) hsize
from FND_HISTOGRAM_COLS a
where table_name = 'JE_BE_LINE_TYPE_MAP'
order by column_name;

COLUMN_NAME HSIZE
------------------------------ ----------
SOURCE 254
SOURCE 254

2) Column does not exist on the table but still listed in FND_HISTOGRAMS_COL table.

Solution:
Find out all duplicates and/or obsolete rows in FND_HISTOGRAM_COLS and delete one of them.
Remember to take backup of the FND_HISTOGRAM_COLS table before deleting any data.

-- identify duplicate rows
select table_name, column_name, count(*)
from FND_HISTOGRAM_COLS
group by table_name, column_name
having count(*) > 1;


-- Use above results on the following SQL to delete duplicates
delete from FND_HISTOGRAM_COLS
where table_name = '&TABLE_NAME'
and  column_name = '&COLUMN_NAME'
and rownum=1;


-- Use following SQL to delete obsoleted rows
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  ='&TABLE_NAME'
   and hc.table_name= tc.table_name (+)
   and hc.column_name = tc.column_name (+)
   and tc.column_name is null
  );


Cheers!!!