11gR2 upgrade with 12.1.2

Steps for 11gR2 Upgrade
Pre-Upgrade Task
Remove DB from archive log mode
Complete Backup of the Database & Application Binaries
Apps Patches:
9062910 -- 11g Release 2 interoperability patch for Release 12.1
7651166 -- 12.1 TXK Delta 2 patch
Adconfig Patches
8966480 -- FWDPORT 8942827 - 11.2DB:APPS11I:NEED CHANGES TO TEMPLATE FILES TO SUPPORT 11GR2
9366993 -- ADBLDXML AND AUTOCONFIG COMPLETES WITH NJNI11:JAVA.LANG.UNSATISFIEDLINKERROR

Set the path of oraInventory in /var/opt/oracle/oraInst.loc file.
Make entry for the oracle home that needs to be upgrade in /var/opt/oracle/oratab file.
Take a backup of the existing RDBMS oraInventory folder.

Software Installation Task
Set the path for ORACLE_SID and ORACLE_BASE env variable
Create a 11.2.0 directory under the $ORACLE_BASE.
Execute the runInstaller to start the 11.2.0 Software only installation.
Click the Ignore all checkbox on Perform Prerequisite Checks when the checks fail for OS Patch - 124861-15 as this is an optional patch.
Run root.sh through root user when prompted.
Install the 11g Examples CD

"After the installation, make sure that: "
The ORACLE_SID environment variable must be set accordingly.
The ORACLE_BASE environment variable must be set accordingly.
The ORACLE_HOME environment variable points to the new 11.2.0 Oracle home.
The PATH environment variable includes $ORACLE_HOME/bin and the directory where the new perl executable is located (usually $ORACLE_HOME/perl/bin).
The LD_LIBRARY_PATH environment variable includes $ORACLE_HOME/lib.
The PERL5LIB environment variable points to the directories where the new perl libraries are located (usually $ORACLE_HOME/perl/lib/[perl version] and $ORACLE_HOME/perl/lib/site_perl/[perl version])

Example:
ORACLE_SID=test11g; export ORACLE_SID
ORACLE_BASE=/test11g/oracle; export ORACLE_BASE
ORACLE_HOME=/test11g/oracle/11.2.0; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/ctx/lib:/usr/dt/lib:/usr/openwin/lib; export LD_LIBRARY_PATH
PERL5LIB=$ORACLE_HOME/perl/lib/5.10.0:$ORACLE_HOME/perl/lib/site_perl/5.10.0; export PERL5LIB
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN

Create nls/data/9idata directory
From 11.2.0 Oracle Home run the following:
perl $ORACLE_HOME/nls/data/old/cr9idata.pl
Check the directory created --- $ORACLE_HOME/nls/data/9idata
set the env variable ORA_NLS10=$ORACLE_HOME/nls/data/9idata

Additional RDBMS Patch
Apply RDBMS Patch - 9218789
Note: Do not run any of the post install instructions as those will be done after the upgrade
Stop the Application Processes and database listener.
From old ORACLE_HOME --Connect DB as sysdba and Drop SYS.ENABLED$INDEXES
SQL> drop table sys.enabled$indexes;

Prepare for DB Upgrade
Copy utlu112i.sql from 11.2.0 ORACLE_HOME/rdbms/admin to a temporary folder.
"From Old ORACLE_HOME, connect DB as SYSDBA"
SQL> SPOOL upgrade_info.log
SQL> @utlu112i.sql
SQL> SPOOL off
Check for the output of the script.
Add space to SYSAUX Tablespace.
SQL> alter database datafile '//dbdata/data1/sysaux01.dbf' resize 600M;

add the following paraneter to init.ora file:
java_pool_size=128M
comment out the following parameters from init.ora file:
#plsql_native_library_dir = //oracle/11.1.0/plsql/nativelib
#plsql_native_library_subdir_count = 149

Restart the Database
Connect database as SYSDBA and execute the following queries:
SQL> SELECT * FROM v$recover_file;
SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
SQL> SELECT * FROM dba_2pc_pending;
SQL> PURGE DBA_RECYCLEBIN;
Re-execute utlu112i.sql

DB Upgrade Task
Run netca from old 11.1.0 Oracle Home and delete the listener.
Shutdown the database and comment out the following parameter in 11.1.0 Oracle Home's init.ora # local_listener='_LOCAL'

Make sure the following envs' are set properly:
$ export ORACLE_SID=
$ export ORACLE_HOME=
$ export PATH=$ORACLE_HOME/bin:$PATH
$ export ORACLE_BASE=
$ export TNS_ADMIN=$ORACLE_HOME/network/admin
Run netca from new 11.2.0 Oracle Home and create the new listener.
Run database upgrade assistant as follows:
"$ dbua -initParam ""_disable_fast_validate=TRUE"""

Modify initialization parameters
Use the init.ora parameter setting for the 11.2.0 as per NoteID:396009.1
"Ensure the parameter ""_disable_fast_validate=TRUE"" is set.."

Restart the Database.
Start the Database listener.
Connect with SYS and run only catmgdidcode.sql and utlrp.sql as part of the post install instructions in patch 9218789. The other scripts were already run as part of the dbua upgrade.
SQL> conn / as sysdba
SQL> @?/md/admin/catmgdidcode.sql
SQL> @?/rdbms/admin/utlrp.sql

Post DB Upgrade Tasks
Copy $APPL_TOP/admin/adgrants.sql from admin node to temporary folder.
Run as follows:
"$ sqlplus ""/ as sysdba"" @adgrants.sql apps"

Copy $AD_TOP/patch/115/sql/adctxprv.sql from admin node to temporary folder.
Run as follows:
$ sqlplus apps/[APPS password] @adctxprv.sql \
[SYSTEM password] CTXSYS
"$ sqlplus ""/ as sysdba"""
"SQL> exec ctxsys.ctx_adm.set_parameter('file_access_role', 'public');"

Login to Admin Node and execute the following script connected with APPS schema.
$ sqlplus apps/[APPS password] @$FND_TOP/patch/115/sql/wfaqupfix.sql APPLSYS APPS

Implement and run AutoConfig
Make sure all the above pre-req Patches are applied mentioned in lines 8-13
Create appsutil.zip
Login to Application Tier and Source the env
perl $AD_TOP/bin/admkappsutil.pl
the scripts will create appsutil.zip in $INST_TOP/admin/out
Copy AutoConfig to the RDBMS ORACLE_HOME
Copy or FTP the appsutil.zip file to the
cd
unzip -o appsutil.zip

Install JRE on the Database tier
Download Latest Update of JRE 6.0
http://java.sun.com/products/archive/
Ftp the file to $ORACLE_HOME/appsutil
$ cd $ORACLE_HOME/appsutil
$ sh jre1.6.0._latest_update#.sh
$ mv jre1.6.0._latest_update# jre

Generate the Database Context File
$ cd $ORACLE_HOME/appsutil/bin
$ perl adbldxml.pl
"Note: After creating the XML context file, do the following changes:"
1. Backup the existing context_file.
2. rename the existing context_file (_.xml) to _.xml [E.g SID_hostname.xml -> sid_vhost.xml]
3. Open the _.xml and change all the occurance of to vi sid_vhost.xml
4. ensure variable s_jretop points to a proper JRE directory i.e. $ORACLE_HOME/jdk/jre.
5. ensure variable s_db_tnsadmin points to $ORACLE_HOME/network/admin/$CONTEXT_NAME

Run AutoConfig on the Database tier
$ cd $ORACLE_HOME/appsutil/bin
$ ./adconfig.sh contextfile=

Gather statistics for SYS schema
Copy $APPL_TOP/admin/adstats.sql from admin node to temporary folder.
Note: adstats.sql has to be run in restricted mode.
Make sure that you have at least 1.5 GB of free default temporary tablespace.
"$ 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 (conditional)
Re-create grants and synonyms
"Run ""Recreate grants and synonyms for APPS schema"" through ADADMIN."

Restart Applications server processes
Synchronize Workflow views
"Log on to Oracle E-Business Suite with the ""System Administrator"" responsibility"
Run the request with the following parameteres:
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

Refernce Metalink Notes:
ID 1058763.1 Interoperability Notes EBS R12 with Database 11gR2.
ID 387859.1 Using AutoConfig to Manage System Configurations in Oracle E-Business Suite Release 12.
ID 396009.1 Database Initialization Parameters for Oracle Applications Release 12.
ID 418664.1 Overview of Using Java with Oracle E-Business Suite Release 12
ID 455492.1 Using Latest Update of Java 6.0 with Oracle E-Business Suite Release 12

------------------------------------------
Happy Troubleshooting !!!

12.1.2 CU patchset

We have recently upgraded our 12.1.1 env to 12.1.2, it has only one AD patch prereq nothing else. It went smoothly with no errors.

We are planning to move our 11gR1 database to 11gR2 soon once minimum patch requirement for OS level met. Will keep you posted.

Happy Troubleshooting !!!

Ten Great Tips For 2010

Ten Great Tips For 2010

1. Stay out of Trouble.

2. Aim for Greater Heights.

3. Stay Focused on your Job.

4. Exercise to Maintain Good Health.

5. Practice Team Work.

6. Rely on your Trusted Partner to Watch your Back. Take your Time Trusting Others.

7. Save for Rainy Days.

8. Rest and Relax.

9. Always Take Time to Smile.

AND

10. Realize That Nothing is Impossible.

Wishing You and Your Family A Very Happy, Prosperous and Healthy New Year 2010!

Export issue in 11g (11.1.0.7) with R12 (12.1.1)

I got one request to export entire R12 (12.1.1) database. I thought it going to be easiest task for me, but it turn to be most challenging for me. This is how proceed:
1. Create Directory in database:
create directory SUNEXP as '/sundeep/exp';
2. Grant read, write on SUNEXP to DBA role, as I want to start expdp as system.
grant read,write on directory EXPDIR to "DBA";
3. Ran expdp full export:
expdp system/password DIRECTORY=SUNEXP FULL=Y COMPRESSION=ALL DUMPFILE=exp_sundeep.dmp LOGFILE=exp_sundeep.log

I thought I am done and it will gona take its own sweet time. But it failed at very first step:
----------------------------------------------------------------------------------
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS []
ORA-31642: the following SQL statement fails:
BEGIN "SYS"."DBMS_AW_EXP".SCHEMA_CALLOUT(:1,0,1,'11.01.00.00.00'); END;
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 923
ORA-33272: Analytic workspace ZPB.ZPBANNOT cannot be opened.
ORA-00600: internal error code, arguments: [xschkmk:!dict], [], [], [], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.KUPW$WORKER", line 7834
---------------------------------------------------------------------------------------
Now the issue is from where to start troubleshooting it. Then I thought of running this as normal exp command:
exp system/password FULL=Y FILE=exp_sundeep.dmp LOG=exp.log statistics=none feedback=1000000 buffer=2000000 compress=Y FILESIZE=4294967296 resumable=N

It started gracefully and end was not that graceful and I got the same error:
----------------------------------------------------------------------------------------
. exporting post-schema procedural objects and actions
EXP-00008: ORACLE error 33272 encountered
ORA-33272: Analytic workspace ZPB.ZPBANNOT cannot be opened.
ORA-00600: internal error code, arguments: [xschkmk:!dict], [], [], [], [], [], [], [], [], [], [], []ORA-06512: at "SYS.DBMS_AW", line 67
ORA-06512: at "SYS.DBMS_AW", line 131
ORA-06512: at "SYS.DBMS_AW", line 933
ORA-06512: at "SYS.DBMS_AW", line 1295
ORA-06512: at "SYS.DBMS_AW_EXP", line 575
ORA-06512: at "SYS.DBMS_AW_EXP", line 697
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_AW_EXP.schema_info_exp
. exporting user history table
. exporting default and system auditing options
. exporting statistics
Export terminated successfully with warnings.
---------------------------------------------------------------------------------------
Yup now there seems to be some issue. I focused on "ORA-33272: Analytic workspace ZPB.ZPBANNOT cannot be opened" which is my key words to start working on this issue.

SQL> select aw_name, aw_version from all_aws where owner='ZPB';

AW_NAME AW_V
------------------------------ ----
ZPBANNOT 9.1
ZPBCODE 10.2
ZPBDATA 9.1

See here was the issue, ZPBANNOT was 9.1 version and my db was 11.1. I thought of export is alone and delete it so that I can proceed and once done I can import it back. Following are the command do the same:
set serveroutput on
exec dbms_aw.execute('aw attach ZPB.ZPBANNOT');
exec dbms_aw.execute('limit name to all');
exec dbms_aw.execute('allstat');
exec dbms_aw.execute('export all to eif file ''SUNEXP/ZPB.ZPBANNOT_old.eif'' ');

When I ran first command it failed:
set serverout on
SQL> exec dbms_aw.execute('aw attach ZPB.ZPBANNOT RW');
exec dbms_aw.execute('aw attach ZPB.ZPBANNOT RW')
*
ERROR at line 1:
ORA-33272: Analytic workspace ZPB.ZPBANNOT cannot be opened.
ORA-00600: internal error code, arguments: [xschkmk:!dict], [], [], [], [], [],[], [], [], [], [], []ORA-06512: at "SYS.DBMS_AW", line 93
ORA-06512: at "SYS.DBMS_AW", line 122
----------------------------------------------------------------------------------------
One of the note says ZPBDATA or ZPBANNOT are obsolete objects and we can delete them.
I did this to delete them

set serveroutput on
exec dbms_aw.execute('aw delete zpb.zpbdata')
exec dbms_aw.execute('aw delete zpb.zpbannot')

Then started the same expdp command which I used earlier and this time it worked. It looks really simple but it ate my 1.5 days to resolve this issue.

Happy Troubleshooting !!!

OPP on Virtual Host

In one of my earlier post I have mentioned how to use Virtual host for Concurrent manager. In this post I am specifically talking about issue you might encounter while using report output as xml and it doesn't show the actual output rather it shows html tags. So just understand the concept how OPP is being used in xml reports and then look for solution.

An Application user submits an XML Publisher based report.
The Standard Concurrent Manager process the request.
The XML data file is generated by the standard concurrent manager.
This can be done by various methods:
Oracle Reports - Report Definition File (RDF)
XML Publisher Data Template - XML data template linked to the Data Definition
Any other process that produces XML output
A Post processing action defines that the output needs to be generated by the Output Post Processor hence it is triggered by the standard manager.
The Output Post Processor generates the final report and informs the standard Concurrent Manager whether that was successful.
The Standard Concurrent Manager finalizes the concurrent request.

Now the error in report:
----------------------------------
One or more post-processing actions failed. Consult the OPP service log for details.

Now the error in OPP log:
----------------------------------
oracle.apps.fnd.cp.util.RemoteFileException: An Error occured during file transmission in the FND File Server. The Applications File Server could not open the file 12345333 for read.

Now the resolution:
----------------------------------
Was worried why OPP is not able to read the file, we have Shared APPL_TOP too. Then though it might be due to virtual host. Then came across one note: 824887.1, it was like sure shot for me and it worked. There is a profile option "Concurrent: Force Local Output File Mode" it was set to No I made it Yes and checked the failed request. Vola it worked as expected and my team mates were jumping. For more detail about this profile look at metalink note mentioned earlier.

Happy Troubleshooting !!!