A New Begining

Probably this will be my last post in this year 2008...
The countdown begins.
The excitement rises.
A joyous New Year dawns.
Time to share the joy and excitement with loved ones and special friends...

May The Year 2009, Bring for You Happiness,Success and filled with Peace,Hope & Togetherness of your Family & Friends....
Wishing You a...*HAPPY NEW YEAR*

Oracle Applications R12 Migration from Solaris to Linux Platform

Abstract

This Document is intended to describe the steps we followed to migrate the existing Oracle Applications Release 12 both Application & Database Tiers from Solaris Sparc Platform to Redhat Linux Platform. This process provides us a way to quickly and easily move an existing Oracle Applications Release 12 from Solaris Sparc to Redhat Linux Platform, allowing us to utilize different hardware.

 

Document History

Author : Srinivas Ramineni

Title: Assistant Manger-ERP

Date Created : 05-Dec-2008

Company : Aurobindo Pharma ltd.,Hyderabad.

Migration of Oracle Applications Release 12 from Solaris to Linux Platform

This Document provides detailed steps on how we migrated our existing Oracle Applications Release 12 both Application & Database Tiers from Solaris Sparc Platform to Redhat Linux Platform.

This Process consists of Eight discrete steps. Each Step Covered in a Separate Section in this Document.

· Section-1: Prerequisites

· Section-2: Prepare the Source System

· Section-3: Prepare a target Release 12 database instance

· Section-4: Export the source Release 12 database instance

· Section-5: Import the Release 12 database instance

· Section-6: Update the imported Release 12 database instance

· Section-7: Prepare the Target Application Tier

· Section-8: Prepare The target Instance

Section-1: Prerequisites:

1. Apply the Applications consolidated export/import utility patch

Apply patch 6924477 to the source administration server node.

2. Apply latest Applications database preparation scripts patch

Apply patch 6342289 to every application tier server node in the source system.

3. Apply Platform Migration Patches
6903505 : QRMP:NOT ABLE TO APPLY R12 PLATFORM SPECIFIC PATCH DURING PLATFORM MIGRATION

6156498 : MIGRATION: CHANGES NEEDED TO ADCLONECTX.PL SCRIPT

4. Apply Additional Patches (Conditional)
6767273 : INSTALLING R12.AD.A.DELTA.4 FAILS FOR OFF-CYCLE PRODUCTS  (AD)

5. Run AutoConfig on the Source System

cd $ADMIN_SCRIPTS_HOME

adautocfg.sh apps/apps

6. Run adpreclone on the Source System

Log on to the source system as the APPLMGR user, and run the following commands on each node that contains an APPL_TOP:
cd <INST_TOP>/admin/scripts
perl adpreclone.pl appsTier

7. Maintain Snapshot Information
Run adadmin à Maintain Application Files à Maintain snapshot information àupdate current view snapshot.

8. Identify Technology Stack Updates

To find the applied patches on the Oracle Applications technology stack run this script

Note:Ensure "APPLRGF" variable is set in environment. If not, set it to the same value as "APPLTMP".

Cd $FND_TOP/patch/115/bin

Run the utility as follows:

perl $FND_TOP/patch/115/bin/TXKScript.pl 

-script=$FND_TOP/patch/115/bin/txkInventory.pl 

-txktop=$APPLTMP  - contextfile=$CONTEXT_FILE 

-appspass=apps 

-outfile=$APPLTMP/Report_Inventory.txt

-reporttype=text

Section-2: Prepare the Source System:

1. Create a working directory

$ mkdir /xxx/xxxx/expimp

2. Generate and upload the manifest of customer-specific files

1.      Generate customer-specific manifest file

Note: Log in to your Source System Applications Node as the APPLMGR user and Source the APPL_TOP environment file

cd <AD_TOP>/bin

perl adgenpsf.pl

above script generate the file adgenpsf.txt under <APPL_TOP>/admin/<TWO_TASK>/out.

2. Upload Manifest File for generation to target Linux/Unix platforms

Go to http://updates.oracle.com/PlatformMigration

3. Copy the Source System to the Target System

Copy the Application Tier file system from the Source Applications System to the Target Node by executing the following steps in the order listed.  

·        <APPL_TOP>

·        <COMMON_TOP>/clone

·        <COMMON_TOP>/java

·        <COMMON_TOP>/_pages

·        <COMMON_TOP>/webapps

·        <COMMON_TOP>/util

Ex: scp -r /UATtlsvol01/oracle/UAT/apps/apps_st/ tlsdev:/appldev/oracle/DEV/apps/

Note: Change Owner ship

4. Generate target database instance creation script aucrdb.sql

$ sqlplus system/<system password> \

@$AU_TOP/patch/115/sql/auclondb.sql 10

It creates aucrdb.sql in the current directory.

5. Record Advanced Queue settings

Copy the auque1.sql script from the $AU_TOP/patch/115/sql directory on the source administration server node to the working directory in the source database server node

Run as Database Owner

$ sqlplus /nolog

SQL> connect / as sysdba;

SQL> @auque1.sql

It generates auque2.sql.

6. Create parameter file for tables with long columns

Copy the aulong.sql script from the $AU_TOP/patch/115/sql directory on the source administration server node to the working directory in the source database server node

Run as Database Owner

$ sqlplus /nolog

SQL> connect system/<system password>;

SQL> @aulong.sql

It generates aulongexp.dat.

7. Remove rebuild index parameter in spatial indexes

SQL> select * from dba_indexes where index_type='DOMAIN' and

upper(parameters) like '%REBUILD%';

To remove the rebuild index parameter, use SQL*Plus to connect to the source database as the owner of the index and run the following command:

SQL> alter index <index name> rebuild parameters <parameters>

where <parameters> is the original parameter set without the rebuild_index parameter.

Ex: ALTER INDEX MST.MST_MD_ADM_BNDS_N1 rebuild parameters ('sdo_indx_dims=2 sdo_rtr_pctfree=10 TABLESPACE=APPS_TS_TX_IDX');

 

Section-3: Prepare a target Release 12 database instance:

1. Create target Oracle 10g Oracle home

If you choose to use Rapid Install, you must use Rapid Install Release 12.0.0. As the owner of the Oracle RDBMS file system, start the Rapid Install wizard by typing:

$ rapidwiz -techstack

Choose the "10gR2 RDBMS" option in the techstack components window and provide the details for the new Oracle home.

Ex: /oradev/oracle/DEV

2. Create the target initialization parameter file and CBO parameter file

Copy the backup copy of InitDEV.ora file to the $ORACLE_HOME/dbs directory

Note: 1. Ensure that the undo_tablespace parameter in the initialization parameter file of the target database instance matches with the default undo tablespace set in the aucrdb.sql script.

2. Ignore the initialization parameters that pertain to the native compilation of PL/SQL code. You will be instructed to add them later, if necessary.

3. Create a working directory

$ mkdir /xxx/expimp

4. Create the target database instance

Copy the aucrdb.sql script from the source administration server node to the working directory in the target database server node.

Update the script on the target database server node with any necessary changes to the directory structures for the log file(s), data file(s), or tablespaces, reflecting the layout of the target database server node.

$ sqlplus /nolog

SQL> connect / as sysdba;

SQL> spool aucrdb.log;

SQL> startup nomount;

SQL> @aucrdb.sql

SQL> exit;

Note: When the target database instance has been created, restart the database instance.

5. Copy database preparation scripts to target Oracle home

Copy the following files from the $APPL_TOP/admin directory of the source administration server node to the working directory in the target database server node

1. addb1020.sql

2. adsy1020.sql

3. adjv1020.sql

4. admsc1020.sql

6. Set up the SYS schema

The addb1020.sqlscript sets up the SYS schema for use with the Applications.

$ sqlplus "/ as sysdba" @addb1020.sql

7. Set up the SYSTEM schema

The adsy1020.sql script sets up the SYSTEM schema for use with the Applications.

$ sqlplus system/<system password> @adsy1020.sql

8. Install Java Virtual Machine

The adjv1020.sql script installs the Java Virtual Machine (JVM) in the database

$ sqlplus system/<system password> @adjv1020.sql

Note: This script can be run only once in a given database instance, because the scripts that it calls are not rerunnable.

9. Install other required components

The admsc1020.sql script installs the following required components in the database: ORD, Spatial, XDB, OLAP, Data Mining, interMedia, and ConText.

$ sqlplus system/<system password> \

@admsc1020.sql FALSE SYSAUX TEMP

10. Disable automatic gathering of statistics

Copy $APPL_TOP/admin/adstats.sql from the administration server node to the target database server node.

$ sqlplus "/ as sysdba"

SQL> shutdown normal;

SQL> startup restrict;

SQL> @adstats.sql

SQL> exit;

 

Section-4: Export the source Release 12 database instance

1. Create the export parameter file

Copy $AU_TOP/patch/115/import/auexpdp.dat from the source administration server node to the working directory in the source database server node

Create a directory in the system schema that corresponds to the directory specified in the template.

$ sqlplus system/<system password>

SQL> create directory dmpdir as '/u01/expimp';

Note: Do not change the other parameters.

2. Shut down Applications server processes

Shut down all Applications server processes except the database and the Net8 listener for the database.

3. Grant privilege to source system schema

$ sqlplus ‘/as sysdba’

SQL> grant EXEMPT ACCESS POLICY to system;

4. Export the Applications database instance

$ expdp system/<system password> parfile=auexpdp.dat

Note: the export runs for several hours.

5. Export tables with long columns

$ exp parfile=aulongexp.dat

6. Export tables with XML type columns

Copy $AU_TOP/patch/115/import/auxmlexp.dat from the source administration server to the working directory in the source database server node

$ exp parfile=auxmlexp.dat

7. Revoke privilege from source system schema

SQL> revoke EXEMPT ACCESS POLICY from system;

 

Section-5: Import the Release 12 database instance

1. Create the import parameter files

Copy auimpdp.dat, aufullimp.dat, and auimpusr.dat from the $AU_TOP/patch/115/import directory in the source administration server node to the working directory in the target database server node.

Note: Make sure that the directory, dumpfile, and logfile parameters in auimpdp.dat and auimpusr.dat are set properly.

Create a directory in the system schema with the name set to the directory

$ sqlplus system/<system password>

SQL> create directory dmpdir as '/XXX/expimp';

2. Copy the export dump files

Copy the export dump files from the source database server node to the working directory in the target database server node.

3.Import the users into the target database

Start an import session on the target database server node using the customized import parameter file.

$ impdp system/<system password> parfile=auimpusr.dat

4. Grant Unlimited on Tablespace “APPS_TS_TX_DATA”

alter user AMS quota unlimited on APPS_TS_TX_DATA;

alter user AMS quota unlimited on APPS_TS_SEED;

alter user APPLSYS quota unlimited on APPS_TS_TX_DATA;

alter user AR quota unlimited on APPS_TS_TX_DATA;

alter user BEN quota unlimited on APPS_TS_TX_DATA;

alter user BOM quota unlimited on APPS_TS_TX_DATA;

alter user CN quota unlimited on APPS_TS_TX_DATA;

alter user CSI quota unlimited on APPS_TS_TX_DATA;

alter user FA quota unlimited on APPS_TS_TX_DATA;

alter user GMA quota unlimited on APPS_TS_TX_DATA;

alter user GMD quota unlimited on APPS_TS_TX_DATA;

alter user HR quota unlimited on APPS_TS_TX_DATA;

alter user HXC quota unlimited on APPS_TS_TX_DATA;

alter user IES quota unlimited on APPS_TS_TX_DATA;

alter user IGI quota unlimited on APPS_TS_TX_DATA;

alter user IGS quota unlimited on APPS_TS_TX_DATA;

alter user IGW quota unlimited on APPS_TS_TX_DATA;

alter user INV quota unlimited on APPS_TS_TX_DATA;

alter user JTF quota unlimited on APPS_TS_TX_DATA;

alter user JTF quota unlimited on APPS_TS_ARCHIVE;

alter user MSC quota unlimited on APPS_TS_TX_DATA;

alter user OE quota unlimited on APPS_TS_TX_DATA;

alter user OKE quota unlimited on APPS_TS_TX_DATA;

alter user OSM quota unlimited on APPS_TS_TX_DATA;

alter user PA quota unlimited on APPS_TS_TX_DATA;

alter user PO quota unlimited on APPS_TS_TX_DATA;

alter user RG quota unlimited on APPS_TS_TX_DATA;

alter user RLM quota unlimited on APPS_TS_TX_DATA;

alter user WMS quota unlimited on APPS_TS_TX_DATA;

alter user WSH quota unlimited on APPS_TS_TX_DATA;

alter user AZ quota unlimited on APPS_TS_TX_DATA;

alter user BIS quota unlimited on APPS_TS_TX_DATA;

alter user CZ quota unlimited on APPS_TS_TX_DATA;

alter user BIC quota unlimited on APPS_TS_TX_DATA;

alter user BIS quota unlimited on APPS_TS_TX_DATA;

alter user CZ quota unlimited on APPS_TS_TX_DATA;

alter user FV quota unlimited on APPS_TS_TX_DATA;

alter user WSH quota unlimited on APPS_TS_TX_DATA;

alter user ALR quota unlimited on APPS_TS_TX_DATA;

alter user APPS quota unlimited on APPS_TS_TX_DATA;

5. Import tables with long columns into the target database

Modify the aufullimp.dat file with the following:

Set userid to "sys/<sys password> as sysdba".

Set file to the dump file containing the long tables (longexp by default).

Set the log file appropriately.

Leave the ignore parameter commented out.

Import the tables using the following command:

$ imp parfile=aufullimp.dat

Note: You will get failures for the triggers as the dependent tables have not yet been imported

6. Import the Applications database instance

$ impdp system/<system password> parfile=auimpdp.dat

Typically, import runs for several hours.

7. Import triggers into the target database

Modify the aufullimp.dat file with the following:

Set userid to "sys/<sys password> as sysdba".

Set file to the dump file containing the long tables (longexp by default).

Change the log file name.

Uncomment the ignore parameter.

Add a line with the parameter "rows=n".

Start an import session on the target database server node using the customized import parameter file. Use the following command:

$ imp parfile=aufullimp.dat.

 

Section-6: Update the imported Release 12 database instance

1. Reset Advanced Queues

Copy the auque2.sql script from the working directory in the source database server node to the working directory in the target database server node

$ sqlplus /nolog

SQL> connect / as sysdba;

SQL> @auque2.sql

2. Run adgrants.sql

Copy $APPL_TOP/admin/adgrants.sql from the administration server node to the working directory in the database server node.

$ sqlplus "/ as sysdba" @adgrants.sql <APPLSYS schema name>

3. 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

 

Section-7: Prepare the Target Application Tier

1. Install JDK and InfoZIP Utilities on the Target System
If not already installed, the following utilities must be installed on the Target System

1.Install JDK 1.5 or higher into the JDK_HOME.

2. InfoZip UNZIP version 5.52

3.InfoZip ZIP version Zip 2.30

Ex: which java

Download & install jdk-1_5_0_08-linux-i586-rpm.bin

2. Copy the Source System Context File to the Target System

3. Clone the Applications Context File on the Target System
Log on to the Target System as the APPLMGR user and run the following commands:

1. Create a pairsfile (text file) with the following values:

s_systemname=DEV

s_dbhost=tlsdev

s_dbSid=DEV

s_jdktop=/usr/java/jdk1.5.0_08

s_jretop=/usr/java/jdk1.5.0_08/jre

2. Generate the Target System Context File:

cd <COMMON_TOP>/clone/bin

perl adclonectx.pl migrate java=<JDK_HOME>  pairsfile=<pairsfile> contextfile=<Source Applications Context File>

ex: perl adclonectx.pl migrate java=/usr/java/jdk1.5.0_08 pairsfile=/home/appldev/pairsfile.txt contextfile=/home/appldev/UAT_tlsuat.xml

Note: The Clone Context tool will ask for all the new mount points on the Target migration node.

4. Install the Middle Tier Technology Stack

Run the Ra Wizard with the -techstack option to install the Oracle Applications Technology Stack.

Use the Target System context file created in the previous step:

cd <StageR12>/startCD/Disk1/rapidwiz

./rapidwiz -techstack

5. Run AutoConfig setup phase on the Target System

Execute the INSTE8_SETUP phase of AutoConfig with the new context file.

This will create the environment files required for the AutoPatch session:

cd <AD_TOP>/bin

./adconfig.sh run=INSTE8_SETUP contextfile=<Target System Context File>

Ex: ./adconfig.sh run=INSTE8_SETUP contextfile=/appldev/oracle/DEV/inst/apps/DEV_tlsdev/appl/admin/DEV_tlsdev.xml

6. Download and apply the customer-specific update Patch with AutoPatch

adpatch options=hotpatch,phtofile

Note: In order to successfully execute autopatch on the new platform, you must use the adpatch executable included on this patch. For Linux: 64

7. Regenerate File System Objects

Source the APPL_TOP environment file and perform the following tasks to regenerate the platform dependent files on the Target System:

1.  If migrating an Application tier that starts Forms, run the following script:

cd <AD_TOP>/bin

./adgensgn.sh <Apps User>/<Apps Password>

2.   Run adadmin to generate messages, forms, reports and product jar files.

8. Run AutoConfig to complete the Target System configuration

cd <AD_TOP>/bin

./adconfig.sh contextfile=<Target System Context File>

Note: On this last step, the database will be updated to reflect the new Target System profile.

 

Section-8: Final preparation

1. Implement and run AutoConfig

Go through Doc: 387859.1 Section:6

Notepad : 1.Shut down all processes, including the database and the listener, and restart them to load the new environment settings

2. Take the necessary Backups

2. Gather statistics for SYS schema

$ sqlplus "/ as sysdba"

SQL> shutdown normal;

SQL> startup restrict;

SQL> @adstats.sql

SQL> shutdown normal;

SQL> startup;

SQL> exit;

Note: Make sure that you have at least 1.5 GB of free default temporary tablespace.

3. Re-create custom database links

$ sqlplus apps/<apps password>

SQL> select db_link from dba_db_links;

The EDW_APPS_TO_WH and APPS_TO_APPS database links, if they exist, should have been updated with the new port number by AutoConfig in the previous step.

$ sqlplus apps/<apps password>

create database link APPS_TO_APPS.TRIDENT.COM connect to APPS identified by APPS using 'DEV';

create database link EDW_APPS_TO_WH.TRIDENT.COM connect to APPS identified by APPS using 'DEV';

4. Create ConText and AZ objects

The consolidated export/import utility patch that you applied to the source administration server node contains a perl script, dpost_imp.pl, that you can run to generate an AutoPatch driver file.

$ perl $AU_TOP/patch/115/bin/dpost_imp.pl <driver file> 10

Once the driver file has been generated, use AutoPatch to apply it on the target administration server node.

5. Import tables with XML type columns into the target database (Not Required)

Modify the aufullimp.dat file with the following:

Set userid to "az/<az password>"

Set file to the dump file containing the tables with XML types (xmlexp by default).

Change the log file name.

Comment out the ignore and rows parameters.

Start an import session on the target database server node using the customized import parameter file. Use the following command:

$ imp parfile=aufullimp.dat

6. Populate CTXSYS.DR$SQE table

$ sqlplus apps/<apps password>

SQL> exec icx_cat_sqe_pvt.sync_sqes_for_all_zones;

7. Compile invalid objects

$ sqlplus "/ as sysdba" @$ORACLE_HOME/rdbms/admin/utlrp.sql

8. Maintain Applications database objects

Run AD Administration on the target administration server node. From the Maintain Applications Database Objects menu, perform the following tasks:

1.Compile flexfield data in AOL tables

2. Recreate grants and synonyms for APPS schema

9. Start Applications server processes

Start all the server processes on the target Applications system. You can allow users to access the system at this time.

10. Create DQM indexes

Create DQM indexes by following these steps:

Log on to Oracle Applications with the "Trading Community Manager" responsibility

Click Control > Request > Run

Select "Single Request" option

Enter "DQM Staging Program" name

Enter the following parameters:

Number of Parallel Staging Workers: 4

Staging Command: CREATE_INDEXES

Continue Previous Execution: NO

Index Creation: SERIAL

Click "Submit"

11. Run Gather Applications statistics program

12. Update Workflow configuration settings as per the Metalink Doc: 438086.1

References

1. Export/Import Process for Oracle E-Business Suite Release 12 Using 10gR2 Note:454616.1

2. Platform Migration with Oracle Applications Release 12 Note:438086.1

Encrypting Network Traffic Using Advanced Security - R12

To configure the E-Business Suite Release 12 to encrypt network traffic sent over the TNS protocol we use the Advanced Networking Option (ANO) that is part of the Advanced Security Option (ASO) of the Oracle database and included with the Release 12 E-Business Suite Technology Suite.

TNS (Transparent Networking Substrate) is an Oracle protocol running on top of a number of supported network protocols - typically TCP/IP. ANO/ASO encryption prevents sending TNS traffic "in-the-clear" over a network connection.
CERTIFICATION: This configuration is certified for Oracle Applications Release 12 using Forms listener Servlet (the default mode) on the following platforms: Linux-x86 Solaris-32 AIX4-32 Tru64 HP-UX Windows-32 .

Advanced security encryption can be configured, based on a combination of client and server configuration parameters as REJECTED, ACCEPTED, REQUESTED or REQUIRED.

Oracle has certified EBS Release 12 with the server parameter set to REQUIRED - this ensures that all EBS TNS network traffic is being encrypted. Although ANO/ASO supports a number of different encryption algorithms, the supported algorithms are version dependent.

For Release 12 certification the the server's preference is set to AES256, AES192, 3DES168.
Steps to enable the encryption in each of the different ORACLE_HOME’s in an EBS deployment.

Step 1 - Shutdown Middle Tier Server Processes and Database Listener

1. On the database server node, shut down the database listener:
$ORACLE_HOME/appsutil/scripts/ DEV_tlsdev/addlnctl.sh stop DEV

2. On each middle tier server, shut down all processes or services:
$ADMIN_SCRIPTS_HOME/adstpall.sh apps user / apps password

Step 2 - DB Tier Changes

1. Logon to the DB Tier server as the file system owner.

2. Source the DB Tier environment file located in Oracle Home directory.

3. Take a backup of the $TNS_ADMIN/sqlnet_ifile.ora file.

4. Open the $TNS_ADMIN/sqlnet_ifile.ora file with the editor of your choice and add the following lines replacing [crypto seed] with a string consisting of 10 - 70 alphanumeric characters of your choosing. The characters that form the value fo this parameter will be used when generating cryptographic keys. The more random the characters entered into this field are, the stronger the keys are.

SQLNET.ENCRYPTION_TYPES_SERVER=(AES256, AES192, 3DES168) SQLNET.ENCRYPTION_SERVER=REQUIRED SQLNET.CRYPTO_SEED=[crytpo seed]

Note: Oracle Corporation recommends that you enter as many characters as possible, up to 70, for the crypto seed to make the resulting key more random and therefore stronger.

5. After the changes have been made, restart the listener:
$ORACLE_HOME/appsutil/scripts/ DEV_tlsdev/addlnctl.sh start DEV

Step 3 - Create $TNS_ADMIN/sqlnet.ora and sqlnet_ifile.ora files on each Middle Tier.

By default, the Oracle Applications Middle Tier installations do not have either a sqlnet.ora or sqlnet_ifile.ora file so we will need create these. We keep the ANO/ASO directives in the sqlnet_ifile.ora file to isolate it from any future autoconfig updates that affect the sqlnet.ora file.

1. Logon to the Middle Tier server as the file system owner.
2. Source your middle tier environment file (APPSDEV_tlsdev.env) located in the APPL_TOP directory.
3. Navigate to the $TNS_ADMIN directory.
4. Use the editor of you choice to create the sqlnet.ora file with the following lines:

#######################################################################
sqlnet.ora file for middle tier sqlnet encryption with Advanced SSL Configuration
######################################################################
IFILE = /sqlnet_ifile.ora
5. Use the editor of you choice to create the sqlnet_ifile.ora file with the following lines:

######################################################################### sqlnet_ifile.ora for middle tier sqlnet encryption with Advanced SSL Configuration
#######################################################################
SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256, AES192, 3DES168)
SQLNET.ENCRYPTION_CLIENT=REQUIREDSQLNET.CRYPTO_SEED=

somelongandrandomstringfordeploymentUpTo70characters

Note: the SQLNET.CRYPTO_SEED does not need to be the same as used on the db tier.

Step 4 - Update the Context File

Use the Oracle Applications Manager (OAM) Context Editor to change the SSL related variables on each middle tier server as shown As Below:

Set s_custom_dbc_params value as ENCRYPTION_CLIENT=REQUIRED ENCRYPTION_TYPES_CLIENT=(3DES168)

Step 5 - Run Autoconfig 1. Run autoconfig on each middle tier server:
$ADMIN_SCRIPTS_HOME/adautocfg.sh appspass=apps password

Step 6 - Restart the Middle Tier Services1. On each middle tier server, restart all processes and services: $ADMIN_SCRIPTS_HOME/adstrtall.sh apps user / apps password

Ref: Metalink Doc no: 376700.1

How to DROP an Oracle Database?

You can do it at the OS level by deleting all the files of the database.
The files to be deleted can be found using:

1) select * from dba_data_files;
2) select * from v$logfile;
3) select * from v$controlfile;
4) archive log list
5) initSID.ora
6) In addition you can clean the UDUMP, BDUMP, scripts etc

Clean up the listener.ora and the tnsnames.ora.
Make sure that the oratab entry is also removed.
With dbca you can easily drop an oracle database.
However you may wish manually to delete a database. The following steps will help you to manually drop a database.

1)Set the Oracle SID of the database which you interested to drop.
On my system it is,

$export ORACLE_SID=dbase

2)Connect to SQL*Plus as SYSDBA privilege.

$sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue May 13 02:41:24 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

3)Mount the database.

SQL> startup force mount

ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 92274728 bytes
Database Buffers 67108864 bytes
Redo Buffers 6369280 bytes
Database mounted.

4)Enable restricted session.

SQL> alter system enable RESTRICTED session;
System altered.

5)Drop the database.

SQL> drop database;
Database dropped.

Upgrade Oracle Database 9.2.0.1 to Oracle Database 9.2.0.6 (patchset 3948480)

Yesterday I have Completed Platform migration on payroll database. While doing the migration, I have upgraded Oracle Database 9.2.0.1 to Oracle Database 9.2.0.6
1. Log in to the Oracle 9.2.0.1 Database server machine as the oracle user (administrator role in case of windows) on Unix.
2. Make sure that your environment contains the correct values for the ORACLE_HOME and ORACLE_SID variables.
3. Shut down any existing Oracle9i database instances with normal or immediate priority As the oracle user:
sqlplus /nolog
SQL> connect / as sysdba
SQL>shutdown immediate

4. Download the patchset & Extract the 9.2.0.6 patch set (3948480 into Stage directory.
5. Install the 9.2.0.6 patch set
cd /Stage/3948480/Disk1/
Run Setup.exe
Welcome
Next ->
Specify File Locations
Source Path: choose default
Destination Name: ORACLE_HOME (select from dropdown list, which databse you want to upgrade)
Destination Path: /oracle/products/payroll
Next ->
Summary
Install ->
End of Installation
Exit

Pre Installation Steps before Database Upgrade
1. If JServer is part of the installation, ensure that there is at least 10 MB of free space allocated to the SYSTEM tablespace.
2. For RAC installations, ensure that there is at least 50 MB of free space allocated to the XDB tablespace.
3. Set the value of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters as follows:
1. Start the database:
2. SQL> STARTUP
3. If necessary, enter the following command to determine whether the system uses an initialization parameter file (initsid.ora) or a server parameter file (spfiledbname.ora):
SQL> SHOW PARAMETER PFILE;
This command displays the name and location of the server parameter file or the initialization parameter file.
Note:The UF installation uses an spfile (Oracle9i Database will automatically use the ORACLE_HOME/dbs/spfilewebct.ora file if it exists), but we want to make sure that the pfile and the file match up to reduce confusion.
4. Determine the current values of these parameters:
SQL> SHOW PARAMETER SHARED_POOL_SIZE
SQL> SHOW PARAMETER JAVA_POOL_SIZE
5. If the system is using a server parameter file:
a. If necessary, set the value of the SHARED_POOL_SIZE initialization parameter to at least 250 MB:
b. SQL> ALTER SYSTEM SET SHARED_POOL_SIZE='250M' SCOPE=spfile;
c.
d. If necessary, set the value of the JAVA_POOL_SIZE initialization parameter to at least 250 MB:
e. SQL> ALTER SYSTEM SET JAVA_POOL_SIZE='250M' SCOPE=spfile;

6. If the system uses an initialization parameter file, if necessary, change the values of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters to at least 250 MB in the initialization parameter file (initsid.ora).
7. Shut down the database:
SQL> SHUTDOWN

Upgrading the Database

Perform the following steps on every database associated with the upgraded Oracle home:

1. Log in as the oracle user:
sqlplus /nolog
SQL> connect / as sysdba
2. Enter the following SQL*Plus commands:
SQL> startup migrate
SQL> spool patch.log
SQL> @?/rdbms/admin/catpatch.sql
SQL> spool off

3. Review the patch.log file for errors and inspect the list of components that is displayed at the end of catpatch.sql script.
4.list provides the version and status of each SERVER component in the database. If necessary, rerun the catpatch.sql script after correcting any problems.
5. Restart the database:
SQL> shutdown immediate
SQL> startup
5. Execute the following script to recompile all invalid PL/SQL packages now. (This step is optional but recommended.)
SQL> @?/rdbms/admin/utlrp.sql
6. Restart the listener & other processes

OERR Utility to Diagnosis the Problem

OERR Stands for Oracle Error.It is used to diagnosis the problem

oradev@aplerp4 # cd $ORACLE_HOME/bin
oradev@aplerp4 # oerr ora 00060
00060, 00000, "deadlock detected while waiting for resource"
// *Cause: Transactions deadlocked one another while waiting for resources.
// *Action: Look at the trace file to see the transactions and resources
// involved. Retry if necessary.

oradev@aplerp4 # oerr ora 01555
01555, 00000, "snapshot too old: rollback segment number %s with name \"%s\" too small"
// *Cause: rollback records needed by a reader for consistent read are
// overwritten by other writers
// *Action: If in Automatic Undo Management mode, increase undo_retention
// setting. Otherwise, use larger rollback segments

oradev@aplerp4 # oerr ora 1652
01652, 00000, "unable to extend temp segment by %s in tablespace %s"
// *Cause: Failed to allocate an extent for temp segment in tablespace.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
// files to the tablespace indicated.
Srinivas Ramineni
Oracle Applications DBA

JSP Pages Hanging in R12 After Removing Cached Class Files in _pages

In Release 12 after Removing the compiled class files from the JSP pages in the directory $COMMON_TOP/_pages and Bounce of Apache server,calling JSP pages results in hanging 'blank' screen.

In Release 11i when performing the same steps new class files are created automatically when the JSP page is called from a browser session and JSP was rendered fine.

In Release 12 it's observed that when calling the JSP no class file is created in the diretciry $COMMON_TOP/_pages

1. Change the value for the main_mode parameter to recompile (instead of justrun)

This can be achieved by doing the following actions
• Use the Context editor to change the value for "s_jsp_main_mode" in the .xml file used by autoconfig and change value from justrun to recompile
• Run Autoconfig to propagate the changes to the configuration files
• Verify that now the $INST_TOP/ora/10.1.3/j2ee/oacore/application-deployments/oacore/html/orion-web.xml has


main_mode
recompile


• Test the scenario failing before.
• See that now a new _.class is created when the JSP page is called.

After A Clone, Blank AppsLogin / AppsLocalLogin Page in R12?

while try to access the Applications we are facing the Blank Apps Login, After Completion of the clone Successfully.

The cause might be explained because if the system was not able to access the classes this may have as root cause jsp / class corruption or wrong $CLASSPATH.

To implement the solution, please execute the following steps:

A. Set the JSP compilation to Automatic using the following steps:
1. Use the vi editor to edit the $CONTEXT_NAME.xml context file and change the value for "s_jsp_main_mode" from "justrun"or “Compile” to "recompile"
Note: Backup the context file before editing it.

2. Run Autoconfig to propagate the changes to the configuration files.

3. Verify that now the
$INST_TOP/ora/10.1.3/j2ee/oacore/application-deployments/oacore/html/orion-web.xml has


main_mode
recompile


4. Restart the Middle Tier services and access the applications to load JSP's which will cause them to be recompiled on the run.
5. After successfully accessing the applications, use the vi editor to edit the $CONTEXT_NAME.xml and revert the value for "s_jsp_main_mode" to "justrun" in order not to hamper the performance of your system.
6. Run AutoConfig to propagate the changes.

An error occurred while retrieving Oracle Applications Help information

Hi
we have upgraded our financials to Rollup 5 in R12.0.4 Instance.
Recently our Functional Consultant had find out Oracle Applications Help information is not retrieving

Error is appearing like the below mentioned.

404 NOT FOUND

An error occurred while retrieving Oracle Applications Help information

Help target US/AP/@APXINWKB_INVOICES_SUM_FOLDER_WINDOW could not be found.

Solution:

Apply the Financials Rollup 5 Help(p6941569_R12_GENERIC.zip) patch.

while applying the Financials Rollup5 patch,we have ignored to apply that patch.

Srinivas Ramineni.
Oracle Apps DBA

WARNING: "Memory Notification: Library Cache Object Loaded Into SGA"

In one of our Database(10g R2)10.2.0.1, we have found that the WARNING message "Memory Notification: Library Cache Object Loaded Into SGA" is being written continuously in alert log file.

To prevent or avoid these messages being generated, we need to adjust the size of a hidden initialization parameter "_kgl_large_heap_warning_threshold" to a high value or zero.

Note: In 10.2.0.2, the threshold is increased to 50MB.
so this should be a reasonable and recommended value.

To find out a hidden parameter value in the database.

SQL> show parameter _kgl_large_heap_warning_threshold

To change the hidden parameter value.

If you are using spfile, login as sysdba

$sqlplus /nolog
SQL> connect /as sysdba
SQL> alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile ;

System altered.

Or if you are using init prameter file

Then, edit the initialization parameter file and add the below line.

_kgl_large_heap_warning_threshold=8388608

Shut down and re-start the database after changes

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP;

SQL> show parameter _kgl_large_heap_warning_threshold

Srinivas Ramineni
Oracle Apps DBA

Script to Find High Impact Concurrent Programs

Sql Script to find High Impact concurrent Programs

#Script to find High Impact Concurrent Programs in html format
set linesize 4000
set verify off
set feedback off
set pagesize 999
set markup html on entmap on spool on preformat off
spool Highimpactconcurrentprograms.html

PROMPT High Impact Concurrent Programs

select substr(cname.user_concurrent_program_name,1,80) "prog name",
exec_date,creq.occur,creq.max_time,creq.min_time,creq.avg_time
from (select
concurrent_program_id,trunc(actual_start_date) exec_date,
count(concurrent_program_id) occur,
round(min(actual_completion_date-actual_start_date)*24*60,2) min_time,
round(max(actual_completion_date-actual_start_date)*24*60,2) max_time,
round(avg(actual_completion_date-actual_start_date)*24*60,2) avg_time
from apps.fnd_concurrent_requests
where status_code='C'
and phase_code='C'
and trunc(actual_start_date)>trunc(sysdate-30)
group by concurrent_program_id ,trunc(actual_start_date)
having max(actual_completion_date-actual_start_date)*24*60 >30) creq
,apps.fnd_concurrent_programs_tl cname
where creq.concurrent_program_id=cname.concurrent_program_id
and cname.language='US'
order by max_time,occur;

set markup html off entmap off spool off preformat on
set linesize 2000 verify on feedback on
spool off

Adding a New Language(s) to the Oracle Applications

Most often when Customers want to add a New Language, the DBA team goes through the process of searching tons of their Emails, Tickets, CSR's for all patches's that need to be applied to keep in synch with US Language. Most often this list can make it messy and go uncontrolled.

This is the simple way to add a new Language(s) to the Oracle Applications provided by Oracle.

Verify Prerequisites

Release 11i

Verify AD Patchset Level : You must be at least on AD mini-pack I (11i.AD.I.2)

Apply Update : If you are not on 11i.AD.I.6 or later, apply update 5837664. This update delivers the latest tool for generating manifest file.

Prepare the Applications Instance for New Language(s): If you are planning to request Translation Synchronization patch for a language which has not yet been activated in your instance, you need to do the following:
· Activate new language(s) using the Oracle Applications Manager (OAM) License Manager.
· Run the AD Administration utility to maintain multilingual tables from the Maintain Applications Database Entities menu.

Maintain Snapshot Information : Recommended

Release 12:

Verify AD Patchset Level: If you are not on R12.AD.A.1 or later, apply update 5635729.
Prepare the Applications Instance for New Language(s): See Installing Translations in Oracle Applications NLS Release Notes, Release 12.0.4.

Requesting Translation Synchronization Patch

Generate and Upload the Manifest of Customer Specific Files :

1. Log on to your Applications' administration node as the APPLMGR user

2. Source the APPL_TOP environment file.

3. Use the manifest generation tool to generate customer specific manifest file by executing the following command:
perl $AD_TOP/bin/adgennls.pl

4. The ouput generated from the manifest tool is in the format :
$APPL_TOP/admin/$TWO_TASK/out/adgennls.txt

5. The manifest contains all of your active languages. You do not need to run the tool for each individual language.

Once the manifest is generated, you can go to http://updates.oracle.com/TransSync & Follow the instructions on the screen to upload the manifest file.

A Translation synchronization patch will be generated specifically based on your manifest for each of your active languages.The patch will synchronize the language file versions and American English file versions in your Applications instance.

If you select the option to get latest translations, the patch will also bring your translations up-to-date. If your current translations are already up-to-date and in sync with your American English file versions, you will be notified by e-mail that there is no need to apply Translation Synchronization patch.

Applying Translation Synchronization Patch

Download and Apply the Customer Specific NLS Patch

1. Download it from OracleMetaLink. Follow the instructions in the README file to apply the patch.

2. A Merged Translation patch may be generated if full translations are not available.

3. You can merge multiple language Translation Synchronization Patches and apply the single merged patch to your system.

Post Install Steps:

If you apply Translation Synchronization patch to add a new language, you need to run the AD Administration utility to generate message files from the Generate Applications Files menu.

Reference : Metalink Doc: 252422.1

--Srinivas Ramineni
Oracle Apps DBA

To Find ICM log file path

Smal Script to Find ICM log file path

SELECT 'ICM_LOG_NAME=' fcp.logfile_name
FROM fnd_concurrent_processes fcp, fnd_concurrent_queues fcq
WHERE fcp.concurrent_queue_id = fcq.concurrent_queue_id
AND fcp.queue_application_id = fcq.application_id
AND fcq.manager_type = '0'
AND fcp.process_status_code = 'A';


ICM logfile: Default is std.mgr, can be changed with the mgrname

Startup Parameter
Concurrent manager log: w.mgr
Transaction manager log: t.mgr
Conflict Resolution manager log: c.mgr

--
Srinivas Ramineni
Oracle Apps DBA

Concurrent Manager Profile Values

CONCURRENT MANAGER PROFILE OPTIONS
----------------------------------

1. Concurrent: Active Request Limit

Value: Numeric

This profile options will restrict the number of concurrent requests that may be run simultaneously by each user. If you do not specify a limit, then no limit is imposed.

2. Concurrent: Attach URL

Values: YES/NO

If you set this option to YES, then this causes a URL to be attached to request completion notifications. For example, when a user submits a request and uses the 'Defining Completion Options' region to specify people to be notified, a notification is sent each of the people designated.

3. Concurrent: Collect Request Statistics

Values: YES/NO

If you set this option to YES, this causes applications to collect statistics on your run-time concurrent processes.

4. Concurrent: Conflicts Domain

Values: LOV (list of values: domains)

This option identifies the domain within which all the incompatibilities between programs has to be resolved

5. Concurrent: Debug Flags

This option is used to debug the transactions managers.You should only use this option at the request of Oracle Support.

6. Concurrent: Directory for Copy

You can identify a directory on your operating system to store copies of your report output or log files. This directory is used when a copy operation is requested in character mode of the applications.

7. Concurrent: Enable Request Submission in View Mode

Values: YES/NO

Setting this option to YES will enable the 'Submit a New Request' button
when users invoke the form FNDRSRUN (Find Requests Form). Navigation path is either: (1) Menu: Help > View My Requests, or (2) navigate (with system administrator responsibility) to, Requests > View.

8. Concurrent: Hold Requests

Values: YES/NO

This option enables you to automatically place requests on hold after submission.

9. Concurrent: Multiple Time Zones

Values: YES/NO

When the client's session and the concurrent manager are running in different times zones, use this option to ensure that the request is scheduled immediately regardless of you client session's time zone.

10. Concurrent: PMON Method

This option is presented for documentation purposes only. Users cannot see or alter this profile option. This option is not visible or cannot be updated from the System Profile Option (FNDPOMPV) form. The PMON method refers to the process monitor. The Internal Concurrent Manager (ICM) monitors the individual
concurrent managers' processes to verify the managers are running. Normally, the PMON method must be set to LOCK. To change the profile option setting, you must execute the SQL script "afimpmon.sql" which resides in directory"$FND_TOP/sql/".

11. Concurrent: Report Access Level
Values: RESPONSIBILITY/USER

This option determines access privileges to report output files and log files generated by a concurrent program. This option can beset by a system administrator or by the user.

12. Concurrent: Report Copies

Values: Numeric

This option determines the number of default copies that print for each submitted concurrent request.

13. Concurrent: Request Priority

Values: Numeric

Concurrent requests in Oracle Applications are queued. Requests normally run according to a start time on a first submitted, first run basis. Priority overrides this request start time. A higher priority request starts before an earlier request. This option displays the default priority for you concurrentrequests. Only a system administrator can change your request priority.

14. Concurrent: Request Start Time

Values: Numeric (Date/Time)

With this profile option, you can set the date and time that your requests are available to start running

* If the start time is at or before the current date and time, requests are available to run immediately

* Start a request in the future, for example, at 6:00 PM on December 31, 2000, enter the following value 31-DEC-2000 18:00

* This profile option requires the date and time,
for example, 31-DEC-2000 18:00

* Changing values does not affect request already submitted

15. Concurrent: Save Output

Values: YES/NO

--

Srinivas Ramineni

Oracle Apps DBA

ConCurrent Manager FAQ

Concurrent Manager FAQs
Q: Which Concurrent Managers (CCMs) are supported by the Sysadmin team?
Q: Where do the concurrent request output and log files reside?
Q: Where are temporary files located?
Q: How to change PMON method to LOCK.
Q: How to start/shutdown the CCM on Unix as APPS
Q: How to terminate a concurrent request that cannot be cancelled.
Q: A Manager is not activating.
Q: Internal Concurrent Manager functionality.
Q: How to purge requests that are in Pending status.

1. Q: Which Concurrent Managers (CCMs) are supported by the Sysadmin team?
· Internal Concurrent Manager (ICM)
· Standard Manager
· Conflict Resolution Manager (CRM)

The setup of product-specific managers related issues should
be directed to the associated product group, i.e. the Inventory manager

2. Q: Where do the concurrent request output and log files reside?
A: $APPLCSF/out or $Product_Top/out for output files
$APPLCSF/log or $Product_Top/log for log files

Q: Where are temporary files located?
A: Temporary files reside in:
$APPLPTMP
$APPLTMP
$REPORTS25_TMP

3. Q: How to change PMON method to LOCK.

To check the process monitor (PMON) method status:
Connect as database user "APPS".
SELECT profile_option_value
FROM fnd_profile_option_values
WHERE level_id = 10001
AND level_value = 0
AND application_id = 0
AND profile_option_id =
(SELECT profile_option_id
FROM fnd_profile_options
WHERE profile_option_name = 'CONC_PMON_METHOD');

This should return one row with a value of 'LOCK'

If the value is 'RDBMS' or 'OS' run the following script:

$FND_TOP/sql/AFIMPMON.SQL - this will set the PMON method to LOCK instead of RDBMS.

Bounce the database. If running on NT, restart the NT Server completely.

Within 11.5 Applications, the only PMON method is LOCK. From 11i this profile Option set at O.S level

4. Q: How to start and shutdown the CCM on Unix as APPS
A: To Start:

STARTMGR sysmgr=APPS/passwd

To Shutdown:

concsub apps/ sysadmin 'System Administrator' SYSADMIN CONCURRENT FND ABORT
11.5 => 11.5.4
adcmctl.sh apps/passwd start/stop prod
11.5.5 =>
adcmctl.sh start/stop apps/passwd prod

5. Q: How to terminate a concurrent request that cannot be cancelled.
A: Identify the request number to terminate.
Shut down the concurrent managers and issue the following sql command as applsys:

update fnd_concurrent_requests
set status_code = 'E', phase_code = 'C'
where Request_id = ; (reqnum = request number)

6. Q: A Manager is not activating.
A: Check the count in sys.dual, system.dual, & apps.fnd_dual. There should only be one (1) row for each.
If the count is greater, change to one only. This can be done from sqlplus as follows:

As user apps:
select *
from SYS.DUAL;
Any extra rows should be deleted.
delete rownum
from SYS.DUAL;
(rownum = the row number to delete)

Any extra rows for apps.fnd_dual must be removed by performing the following SQL command:

delete from fnd_dual
where rownum < (select max(rownum) from fnd_dual);

Bounce the Concurrent Managers.


7 . Q: Internal Concurrent Manager.
A: If the ICM should go down, requests will continue to run normally, except for 'queue control' requests.

If the ICM should go down, you can restart it with 'startmgr'. There is no need to shut down the other managers first.


8. Q: How to purge requests that are in Pending status.
A: The Purge Concurrent Requests program will only purge requests that are in Completed status. Set the status of the Pending requests to Completed
before purging them. The Internal Manager Must be DOWN!

Using sqlplus as APPLSYS perform the following:

UPDATE fnd_concurrent_requests
SET phase_code = 'C'
WHERE phase_code = 'P'

--
Srinivas Ramineni
Oracle Apps DBA