Upgrade OA 11.5.8 to 11.5.10.2

Apps Upgrade from 11.5.8 to 11.5.10.2,

Shared Application Tier file system conversion,

Database upgrade from

9.2.0.7 – 64 Bit to 10.2.03 – 64 bit

ATG_PF

Version 1.2

1 General

1.1 Problem Statement

There is no certified document which outlines the procedure for upgrading Foxtrot instance from 11.5.8 to 11.5.10.2, database from 9.2.0.7 – 64 bit to 10.2.0.3 – 64 bit and apply security fixes from April 2005 till October 2007.

1.2 Objective

Design and develop the procedure, which could assist the Project Team in

· Upgrading the Oracle Application from 11.5.8 to 11.5.10.2

· Upgrade Database from 9.2.0.7 – 64 bit to 10.2.0.3 – 64 bit

· Apply ATG_PF RUP5

· Apply security fixes from April 2005 till October 2007.

1.3 Project Team

Foxtrot – Evolution project team

1.4 Key Milestones

2 Upgrade Apps from 11.5.8 to 11.5.10.2

2.1 Apply TUMS Patch

· Apply patch 4238286 using adpatch on the admin node.

2.2 Run TUMS Script

Reference:

· Ensure that the database parameter UTL_FILE_DIR is set to a writable directory <DIRECTORY> where the TUMS for 11.5.10 Maintenance Pack report will be created.

· Apply the following driver file using adpatch: u4238286.drv (This is the unified driver)

· Generate the TUMS report by executing the following command:

· $ cd $AD_TOP/patch/115/sql

· $ sqlplus <APPS username>/<APPS password> @adtums.sql <DIRECTORY>

· A report file called tumsmp.html will be created in the directory. This directory path must have been listed in the UTL_FILE_DIR parameter of your database.

2.3 Apply AD.I Minipack

· Apply patch 5161676.

2.3.1 Post AD.I Tasks

· Grant privileges and create PL/SQL profiler objects

Note:

Run the adgrants.sql script as a user that can connect as SYSDBA to grant privileges to selected SYS objects and create PL/SQL profiler objects.

Reference:

· Create $ORACLE_HOME/appsutil/admin on the database server.

· Copy adgrants.sql from $APPL_TOP/admin to $ORACLE_HOME/appsutil/admin.

· Set the environment to point to ORACLE_HOME on the database server.

· Run following commands

$ sqlplus /nolog

SQL> connect / as sysdba

SQL> @$ORACLE_HOME/appsutil/admin/adgrants.sql <APPLSYS schema name>

· Run adconfig to instantiate the templates used by AD Utilities

· Perform following adadmin Tasks

· Regenerate JAR files.

· Update current view snapshot.

2.4 Update Technology Stack Templates

2.4.1 Run the Validation Script

Reference:

· From the location where this patch (5478710) unzipped, change directory to fnd/patch/115/bin .

· Run the Validation script as follows:

./txkprepatchcheck.pl -script=ValidateRollup

-outfile=$APPLTMP/txkValidateRollup.html

-appspass=<apps database password>

Review the report generated by the validation script. Perform the required actions described in the report

2.4.2 Apply patch 5478710

Apply patch 5478710 using adpatch

2.4.3 Perform post patch steps

Reference:

Update the RDBMS ORACLE_HOME file system with the AutoConfig files by performing the following steps:

· On the Application Tier (as the APPLMGR user):

Source the environment file.

Create the appsutil.zip file by executing:

$ADPERLPRG $AD_TOP/bin/admkappsutil.pl

This will create appsutil.zip in $APPL_TOP/admin/out .

· On the Database Tier (as the ORACLE user):

Copy or FTP the appsutil.zip file to the <RDBMS ORACLE_HOME>

cd <RDBMS ORACLE_HOME>

unzip -o appsutil.zip

Run AutoConfig by executing:

<RDBMS_ORACLE_HOME>/appsutil/scripts/<CONTEXT_NAME>/adautocfg.sh

2.5 Apply patch 3835781

Reference:

Unzip the patch into iAS Oracle Home/patch directory.

· Ensure you can connect as sysdba

(set following parameters to allow remote connections

REMOTE_LOGIN_PASSWORDFILE = exclusive

O7_DICTIONARY_ACCESSIBILITY = TRUE)

· Change directory to iAS Oracle Home/patch

· Run following command:

./patch.csh -m sysobjects -o <ORACLE_HOME> -c

<conn_str> -p <sys_password>

· Stop Oracle HTTP Server

· Run following command:

./patch.csh -m midtier -o <ORACLE_HOME>

2.6 Install and run Technology Stack Validation Utility

· Apply patch 3996414

· Perform following post patch steps

Reference:

· Check that the ADPERLPRG variable is set

· Run the technology validation utility on application tiers

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

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

-txktop=$APPLTMP

-appspass=<apps_password>

-outfile=$APPLTMP/txkVal11510MP.html

· Run the technology validation utility on database tier

$ADPERLPRG $ORACLE_HOME/appsutil/bin/TXKScript.pl

-script=$ORACLE_HOME/appsutil/bin/txkVal11510MP.pl

-txktop=$ORACLE_HOME/appsutil

-appspass=<apps_password>

-outfile=$ORACLE_HOME/appsutil/temp/txkVal11510MP_DB.html

· Ensure that the report has ALLPASS for both the tiers

2.7 Configure database for new products and tablespaces

2.7.1 Perform pre patch steps

Reference:

· Change directories to the ad/patch/115/sql subdirectory of this patch(3180164).

· Run adgnofa.sql in the following manner:

$ sqlplus <APPS schema name>/<APPS password> @adgnofa.sql ALL

· The adruncts.sql and adcrtbsp.sql scripts will be created in the current directory. Open adcrtbsp.sql in a text editor and look for any “create tablespace” commands. If there are any, examine the locations of the new data files, and make changes to them if necessary. Remember that the data files will be created on the database server node, which may be a different machine than the administration server node.

· Run adruncts.sql in the following manner:

$ sqlplus system/<SYSTEM password> @adruncts.sql

· adruncts.sql will display the disk space needed for adcrtbsp.sql to complete successfully. Verify that you have sufficient available disk space on the database server node, and answer the prompt with “yes”.

· adruncts.sql will run adcrtbsp.sql, and the results will be spooled to adcrtbsp.log in the current directory. Review the log file to make sure no errors were reported.

2.7.2 Apply patch 3180164

· Apply patch 3180164 using adpatch. Ensure that this patch is applied on admin node first.

2.7.3 Perform post patch tasks

· Recreate application environment file using adadmin.

· Log off from Unix and login again before proceeding further.

2.8 Install Planner Libraries (4297568)

Perform following tasks only on concurrent processing tier

Reference:

· Log in to each concurrent processing server node as the owner of the

Applications file system.

· If the $SHT_TOP/lib/ilog/5.1 directory already exists, back up any files or

directories that it contains.

· Unzip the ilog51lib.zip file, located in the sht/lib directory of this

patch, into the $SHT_TOP/lib directory. This will create the ilog/5.1

subdirectory if it doesn’t already exist.

· Change directories to $SHT_TOP/lib/ilog/5.1/<your platform>.

· Copy the library files from the current directory to the

$SHT_TOP/lib/ilog/5.1 directory.

· Use AD Administration to relink the following specific executables:

- MSO: MSONEW

- WIP: WICDOL WICMEX WICMLX

- WPS: WPCWFS

- MSR: MSRNEW (if using Release 11.5.4 or later)

- MST: MSTNEW (if using Release 11.5.10 or later)

- ENG: ENCACN (if using Release 11.5.6 or later

2.9 Prepare Depot Repair For Upgrade

· Apply patch 3695149

Note:

The patch generates an output file csdpremr.lst that indicates problematic data, and a description of the resolution. After completing necessary corrective action you should rerun the sqlplus script csdpremr.sql under the $CSD_TOP/patch/115/sql directory and continue to correct data until no further errors are reported. The report will indicate when all errors are resolved.

2.10 Upgrade Oracle ADI

These are client side instructions.

Note:

Extract the p3731741.exe file into a temporary directory.

If the patch is NOT being applied to an installation on Citrix WinFrame or

Microsoft Windows Terminal Server DO NOT perform steps 2 and 7.

2) At a command prompt, enter the following command to place the system in install mode:

change user /install

3) Run the locally installed version of Oracle Installer from the Start

Menu.

4) From the Software Asset Manager, click the From button. Use the dialog

box to navigate to the directory where the patch files were extracted.

5) Select the product (.prd) file displayed, click Open.

6) If both the ADI Request Center and the ADI Toolbar (Excel7/97/2000/XP/2003) components are installed in your Oracle home, then select the “Applications Desktop Integrator Products 7.1.35.10.01 component in the Available Products window and click Install.

If the ADI Request Center component is installed in your Oracle home, but

the ADI Toolbar (Excel7/97/2000/XP/2003) component is not, then open the

“Applications Desktop Integrator Products 7.1.35.10.01 component in the

Available Products window, select the “ADI Request Center 7.1.35.10.01″

component, and click “Install”.

7) At a command prompt, enter the following command to place the system in execution mode: change user /execute

2.11 Change password control policy

If any password related profile options are set then you will have to set them back to null.

2.12 Add space to tablespaces

· Add space to following tablespaces:

OKSX, CSMX, IGFD, ENGX,ENGD,GMFX,GMFD,ECXD, EGOD & EGOX. The space added was 2 GB each.

2.13 Unzip the Maintenance pack files

Unzip the American English and NLS files of Maintenance Pack

2.14 Apply the Maintenance Pack patch

· Apply patch 3480000 on all application tiers.

· Apply NLS patches (Simplified & Traditional Chinese)

· These NLS patches were merged.

2.15 Perform adadmin tasks

· Recreate grants and synonyms for APPS schema(s)

· Compile APPS schema(s)

· Compile flexfield data in AOL tables

· Maintain Multiple Reporting Currencies schema(s)

· Generate message files

· Compile Java Server Pages

2.16 Perform post Upgrade tasks

· Apply latest consolidated online help patch (3275245)

· Drop MRC Schema

3 Apply ATG_PF RUP5

3.1 Apply pre-req patches

3.1.1 Apply patch 5989593

Reference:

Create $ORACLE_HOME/appsutil/admin on the database server.

Copy adgrants.sql (UNIX) from the patchtop(5989593)/admin directory to

$ORACLE_HOME/appsutil/admin.

Set the environment to point to ORACLE_HOME on the database server.

Use SQL*Plus to run the script:

$ sqlplus /nolog

SQL> connect / as sysdba

SQL> @$ORACLE_HOME/appsutil/admin/adgrants.sql <APPLSYS schema name>

3.1.2 Apply patch 4291053

· Apply patch 4291053

· Compile APPS Schema using adadmin

· Compile Flexfield data in AOL tables

3.1.3 Apply patch 3761838 – FRM.G

3.1.4 Apply patch 4206794 – FRM.H

· Apply patch 4206794 and perform following tasks

· Generate message files

· Compile APPS Schema

· Compile Flexfied data in AOL Tables

· Recreate grants and synonyms for APPS Schema

· Gather Schema Statistics

3.1.5 Apply patch 3854951

3.1.6 Apply documentation patch 5395066 using adpatch

5.1.1 – 5.1.7 patches were merged.

3.1.7 Run the technology stack validation utility

Reference:

· Source the environment logged in as applmgr

Ensure that APPLRGF environment is set, if not then set it to the value of APPLTMP

From the location where this patch(ATG_PF>RUP5) was unzipped, change directory to fnd/patch/115/bin.

Run the tech stack validation utility as follows:

./txkprepatchcheck.pl -script=ValidateRollup
-outfile=$APPLTMP/txkValidateRollup.html
-appspass=<apps database password>

Check the report file generated by this utility for any actions required to perform

3.1.8 Apply ATG_PF.RUP5 patch using adpatch.

Apply patch using adpatch

NLS Patches were merged.

3.1.9 Post ATG_PF.RUP5 steps

Apply following patches using adpatch

3865683

6145463

apply addional co-requisite patches as follows

4619025

6496781 – new patch added on feb 23

6265836 – new patch added on feb 23

6620452 – new patch added on feb 23

run adconfig

Disable default users (conditionally recommended)

Needs to confirm with Vikas / Akhilesh

Copy font files for Oracle XML Publisher

Reference:

· Copy the following font files from $FND_TOP/resource to the /lib/fonts directory (under OA_JRE_TOP and JRE_TOP) on all Web and concurrent nodes. If $FND_TOP/resource does not contain the font files, apply patch 3639533: Albany (Display) Font 4.02 Release.

· The font files are:

ALBANYWT.ttf – “single-byte” characters only

ALBANWTJ.ttf – Unicode font with Japanese Han Ideographs

ALBANWTK.ttf – Unicode font with Korean Han Ideographs

ALBANWTS.ttf – Unicode font with Simplified Chinese Han Ideographs

ALBANWTT.ttf – Unicode font with Traditional Chinese Han Ideographs

3.1.10 Apply TXK Autoconfig Rollup Q (5985992)

· Perform pre patch steps

Reference:

· Source the Applications environment file as the owner of the application tier file system. From the location where this patch was unzipped, change directory to fnd/patch/115/bin

· Run the Validation script as follows:

./txkprepatchcheck.pl -script=ValidateRollup

-outfile=$APPLTMP/txkValidateRollup.html

-appspass=<apps database password>

Review the report generated by the validation script. Perform the required actions described in the report before you apply the TXK (FND & ADX) AUTOCONFIG ROLLUP PATCH P

· Apply patch 5989992

· Perform following Post patch steps

· Update the RDBMS ORACLE_HOME file system with the AutoConfig files by performing the following steps:

On the Application Tier (as the APPLMGR user):

Source the environment file.

Create the appsutil.zip file by executing following command:

$ADPERLPRG $AD_TOP/bin/admkappsutil.pl

This will create appsutil.zip in $APPL_TOP/admin/out .

On the Database Tier (as the ORACLE user):

Copy or FTP the appsutil.zip file to the <RDBMS ORACLE_HOME>

cd <RDBMS ORACLE_HOME>

unzip -o appsutil.zip

· Run AutoConfig by executing following command:

$<RDBMS_ORACLE_HOME>/appsutil/scripts/<CONTEXT_NAME>/

adautocfg.sh

· Remove the log file sec_audit.log from the following location(s) if it exists:

* [iAS_ORACLE_HOME]/Apache/Apache/logs/ .

* [iAS_CONFIG_HOME]/Apache/Apache/logs on installations where a

Shared Oracle Home is configured.

3.1.11 Apply ATG Documentation patch 5382500 using adpatch

4 Upgrade J2SE 1.4

· Apply all the driver files of patch 3239047 using adpatch

· Obtain and apply following product related patches

§ Universal Work Queue patch 3695147

§ Collections patch 3199598

· Need to confirm with Elvis.

§ Web Applications Desktop Integrator patch 3503919

· Execute J2SE 1.4 Upgrade script

cd $FND_TOP/patch/115/bin

txkJdkM.sh jdktop=[JDK14_TOP][contextfile=<file>] [log=<file>] [appspass=<password>]

· Perform following step to upgrade the Java version used by AD Utilities

cd $AD_TOP/bin

perl <AD_TOP>/bin/adjavamig.pl java=[JDK14_TOP] [contextfile=<file>][logfile=<log_file>][-help]

run adconfig

run adadmin to generate product JAR files

5 Implement Shared Application file system

5.1 Pre-Merge Tasks

5.1.1 Maintain Snapshot Information

Reference:

· Login to each application tier server as applmgr user and run adadmin task of maintain snapshot information

5.1.2 Backup all the application tier binaries

5.1.3 Shutdown all application services

5.1.4 Prepare for Merge

Decide on the primary node and run merge scripts

Reference:

· On the primary node, login as applmgr user

$ cd <COMMON_TOP>/admin/scripts/<CONTEXT_NAME>

$ perl adpreclone.pl appsTier merge

· Once this process is complete login to remaining nodes and run

$ cd <COMMON_TOP>/admin/scripts/<CONTEXT_NAME>

$ perl adpreclone.pl appltop merge

5.1.5 Copy the files

Copy the required files for merging

Reference:

· Login to each secondary node and recursively copy

directory <COMMON_TOP>/clone/appl

- to –

directory <COMMMON_TOP>/clone/appl on Primary Node

5.1.6 Configure Tasks

Once copy is complete run the configuration tasks

Reference:

· Login to the merged APPL_TOP node(Primary Node) as the APPLMGR user and execute the following commands:

$ cd <COMMON_TOP>/clone/bin

$ perl adcfgclone.pl appsTier

5.1.7 Finishing Tasks

Perform adadmin tasks to complete the merge activity

Reference:

· Login to the merged APPL_TOP node(Primary Node) as the APPLMGR user and run following adadmin tasks

- Generate JAR Files Force = Y

- Generate message files

- relink executables

- copy files to destination

5.2 Enable shared application file system à Handover

On the primary node enable the shared file system support.

Reference:

· Stop all application services

$ cd <FND_TOP>/patch/115/bin

$ perl -I <AU_TOP>/perl txkSOHM.pl

· This script ask response for following prompts:

Absolute path of the context file

Type of Instance

Absolute path of 8.0.6 shared Oracle Home

Absolute path of iAS shared Oracle Home

Absolute path of Configuration top

Oracle Appplications APPS Schema password

5.3 Mount shared application file system on secondary node

5.4 Enable shared application file system on secondary node

Reference:

$ cd <FND_TOP>/patch/115/bin

$ perl -I <AU_TOP>/perl txkSOHM.pl

· The script will ask response for following prompts:

Absolute path of the context file

Type of Instance

Absolute path of 8.0.6 shared Oracle Home

Absolute path of iAS shared Oracle Home

Absolute path of configuration top

Oracle Applications APPS Schema password

6 Move the instance to Solaris 10 box

7 Upgrade Database to 10g Rel 2

7.1 Perform Pre Upgrade Tasks

7.1.1 Run pre-upgrade information tool

· Run the attached script on existing 9.2.0.6 / 9.2.0.7 database.

· Ensure that you connect as ‘SYS’ user to be able to run this script and spool the output to a file.

· Carry out the steps mentioned in the output file.

7.1.2 Check for Deprecated CONNECT Role

Reference:

After upgrading to 10gR2, the CONNECT role will only have the CREATE SESSION privilege; the other privileges granted to the CONNECT role in earlier releases will be revoked during the upgrade. To identify which users and roles in your database are granted the CONNECT role, use the following query:

SELECT grantee FROM dba_role_privs

WHERE granted_role = ‘CONNECT’ and

grantee NOT IN (

‘SYS’, ‘OUTLN’, ‘SYSTEM’, ‘CTXSYS’, ‘DBSNMP’,

‘LOGSTDBY_ADMINISTRATOR’, ‘ORDSYS’,

‘ORDPLUGINS’, ‘OEM_MONITOR’, ‘WKSYS’, ‘WKPROXY’,

‘WK_TEST’, ‘WKUSER’, ‘MDSYS’, ‘LBACSYS’, ‘DMSYS’,

‘WMSYS’, ‘OLAPDBA’, ‘OLAPSVR’, ‘OLAP_USER’,

‘OLAPSYS’, ‘EXFSYS’, ‘SYSMAN’, ‘MDDATA’,

‘SI_INFORMTN_SCHEMA’, ‘XDB’, ‘ODM’);

If users or roles require privileges other than CREATE SESSION, then grant

the specific required privileges prior to upgrading. The upgrade scripts

adjust the privilegesfor the Oracle-supplied users.

In Oracle 9.2.x and 10.1.x CONNECT role includes the following privileges:

SELECT GRANTEE,PRIVILEGE FROM DBA_SYS_PRIVS

WHERE GRANTEE=’CONNECT’

GRANTEE PRIVILEGE

—————————— —————————

CONNECT CREATE VIEW

CONNECT CREATE TABLE

CONNECT ALTER SESSION

CONNECT CREATE CLUSTER

CONNECT CREATE SESSION

CONNECT CREATE SYNONYM

CONNECT CREATE SEQUENCE

CONNECT CREATE DATABASE LINK

In Oracle 10.2 the CONNECT role only includes CREATE SESSION privilege.

7.1.3 Create script for dblinks in case of downgrade of database

Reference:

During the upgrade to 10gR2, any passwords in database links will be encrypted.

To downgrade back to the original release, all of the database links with encrypted passwords must be dropped prior to the downgrade. Consequently, the database links will not exist in the downgraded database. If you anticipate a requirement to be able to downgrade back to your original release, then save the information about affected database links from the SYS.LINK$ table, so that you can recreate the database links after the downgrade.

Following script can be used to construct the dblink.

SELECT

‘create ‘||DECODE(U.NAME,’PUBLIC’,'public ‘)||’database link ‘||CHR(10)

||DECODE(U.NAME,’PUBLIC’,Null, U.NAME||’.')|| L.NAME||chr(10)

||’connect to ‘ || L.USERID || ‘ identified by ”’

||L.PASSWORD||”’ using ”’ || L.host || ””

||chr(10)||’;’ TEXT

FROM sys.link$ L,

sys.user$ U

WHERE L.OWNER# = U.USER# ;;

7.1.4 List Invalid Objects

Prepare a list of invalid objects before starting the database upgrade.

7.1.5 Update the Optimizer Statistics

Update the statistics of following users.

SYS

OLAPSYS

MDSYS

7.1.6 Apply the Interoperability patch for 10g

· Apply patch 4653225

7.1.7 Change init parameters from 9i RDBMS Home

DB_CACHE_SIZE=800M

SHARED_POOL_SIZE=900M

SHARED_POOL_RESERVED_SIZE=60M

Large_pool_size=20M

PGA_AGGREGATE_TARGET=2GB

7.2 Perform database upgrade tasks

7.2.1 Shutdown the databse and application services

7.2.2 Install 10.2.0.3 – 64 bit software

7.2.3 Install database components from 10G companion CD

Install Oracle JVM and Intermedia products from companion CD

7.2.4 Apply 10.2.0.3 – 64 bit patch

7.2.5 Create 9inls directory

Login to the Database Server Node as the Owner of the Oracle RDBMS File System and

Run the $ORACLE_HOME/nls/data/old/cr9idata.pl script to create the $ORACLE_HOME/nls/data/9idata directory.

à perl cr9idata.pl

After creating the directory, make sure that the ORA_NLS10 environment variable is set to the full path of the 9idata directory whenever you enable the 10g Oracle home.

7.2.6 Apply 10g Fixes

· Apply latest Opatch (patch 4898608)

(Install the Latest OPatch version (10.2.0.3.3)

Reference:

· Ensure that the directory contains the opatch script appears in your $PATH

· Backup the old Opacth directory in your Oracle Home and then unzip the above patch in 10.2.0 Oracle Home. It will create OPatch directory.

· Apply patch for file systems managed by Veritas (patch 5752399)

(Mandatory Patch 5752399 for 10.2.0.3 on Solaris 64-bit and Filesystems Managed By Veritas or Solstice Disk Suite software (lib files)

Reference:

· Set your current directory to the directory where the patch is located

· #cd 5752399

· #opatch apply

· Apply patch 4905638

(SWITCHOVER TO LOGICAL GENERATES ORA-1403 ORA-1333 ERRORS (lib files))

Reference:

· Set your current directory to the directory where the patch is located

· #cd 4905638

· #opatch apply

· Apply patch 5892355

( ASSERT IN KOPEPIC (lib & msg files))

Reference:

· Set your current directory to the directory where the patch is located

· #cd 5892355

· #opatch apply

· Apply patch 5648872

(Note:418531.1 – Dump in opidsa() after Applying the 10.2.0.3 Patch Set (lib files))

Reference:

· Set your current directory to the directory where the patch is located

· #cd 5648872

· #opatch apply

· Apply patch 5907779

(Bug 5907779 – Self deadlock hang on “cursor: pin S wait on X” (typically from DBMS_STATS) (lib files))

Reference:

· Set your current directory to the directory where the patch is located

· #cd 5907779

· #opatch apply

· Apply patch 5728380

(Bug 5728380 – DML may spin under ktspffc searching for space in ASSM segment (lib files))

Reference:

· Set your current directory to the directory where the patch is located

· #cd 5728380

· #opatch apply

· Apply patch 4430244

(GEHC: ORA-00600: INTERNAL ERROR CODE, ARGUMENTS: [KCBNEW_3], [0], [8 (lib files) Segment advisor code (eg: DBMS_SPACE.OBJECT_GROWTH_TREND) can load blocks into the cache for DROPped bjects as CURRENT leading to subsequent operations seeing an incorrect (old) version of a block)

Reference:

· Set your current directory to the directory where the patch is located

· #cd 4430244

· #opatch apply

· Apply patch 5045992

(GEHC: RDBMS Server: Patch ORA-00600 [KXSPOAC EXL 1], [23], [23] (lib files)ABSTRACT – ORA-600 [KXSPOAC : EXL 1], [23], [23]
If a SQL statement fails with Ora-600 [kxspoac : EXL 1] when executed by a parallel execution slave and that SQL statement has numeric binds then you are probably seeing this bug)

Reference:

· Set your current directory to the directory where the patch is located

· #cd 5045992

· #opatch apply

· Apply patch 6068126

(GEHC: VARIOUS ERRORS IN ALERT.LOG ORA-7445 PEVM_INSTC2 MEMCPY (lib files))

Reference:

· Set your current directory to the directory where the patch is located

· #cd 6068126

· #opatch apply

· Apply patch 5201883

( UNEXPLAINED MMAN TRACE FILE (lib files))

Reference:

· Set your current directory to the directory where the patch is located

· #cd 5201883

· #opatch apply

· Apply patch 5257698

Apply Patch 5257698 (RDBMS Server: Patch TOO MANY ‘LX40030.NLB’ FILES OPEN AFTER UPGRADE TO 10GR2 .. 10.2.0.3)

Reference:

Set your current directory to the directory where the patch is located

#cd 5257698

· #opatch apply

· Apply patch 4686909

(CONVERT FUNCTION REPORTS ORA-1482 )

Set your current directory to the directory where the patch is located

#cd 4686909

#opatch apply

· Apply patch 5587976

APPSST: ORA-07445: EXCEPTION ENCOUNTERED: CORE DUMP [DRURNEW()+129] [SIGSEGV]

Reference:

Set your current directory to the directory where the patch is located

#cd 5587896

· #opatch apply

7.2.7 Upgrade the database

· Login to the system as oracle user and ensure that following environment variables are set to point to new 10g home

§ ORACLE_HOME

§ PATH

§ ORA_NLS10

§ LD_LIBRARY_PATH

Update the utlrp script to run in parallel. Check for line

@@utlprp.sql &

And update it to

@@utlprp.sql 24

Use DBUA to upgrade the database.

From ORACLE_HOME/bin start DBUA by typing ‘dbua’ from the command prompt. Follow the instructions to complete the upgrade. While running DBUA when it asks for parallel workers give the same number of workers as above i.e. 24

7.2.8 Check if OLAP AND Data Mining is installed.

Use following SQL to confirm as to whether OLAP and Data mining is installed

SQL> select comp_id from dba_registry where comp_id=’ODM’ or comp_id=’AMD’;

If the query does not return ‘ODM’ then you need to install Oracle Data Mining.

To install Data Mining use following procedure

Connect to database as SYSDBA user and run

SQL> @$ORACLE_HOME/rdbms/admin/dminst.sql SYSAUX TEMP

If the query does not return ‘ODM’ then you need to install Oracle OLAP. To install Oracle OLAP use following procedure

Connect to database as SYSDBA user and run

SQL> @$ORACLE_HOME/olap/admin/olap.sql SYSAUX TEMP

7.2.9 Fix Korean Lexers

Use SQL*Plus to connect to the database as SYSDBA, and run drkorean.sql using the following command:

$ sqlplus “/ as sysdba” @$ORACLE_HOME/ctx/sample/script/drkorean.sql

7.3 Post Database Upgrade Tasks

7.3.1 Run adgrants.sql

Use SQL*Plus to connect to the database as SYSDBA and run the script using the following command:

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

7.3.2 Grant Create procedure privileve to CTXSYS

Copy $AD_TOP/patch/115/sql/adctxprv.sql from the administration server node to the database server node. Use SQL*Plus to connect to the database as APPS and run the script using the following command:

$ sqlplus apps/<APPS password> @adctxprv.sql \

<SYSTEM password> CTXSYS

7.3.3 Run adconfig

7.3.4 Gather statistics for SYS schema

Copy $APPL_TOP/admin/adstats.sql from the administration server node to the database server node Use SQL*Plus to connect to the database as SYSDBA and use the following commands to restart the database in restricted mode, run adstats.sql, and restart the database in normal mode:

$ sqlplus “/ as sysdba”

SQL> shutdown normal;

SQL> startup restrict;

SQL> @adstats.sql

SQL> shutdown normal;

SQL> startup;

SQL> exit;

7.3.5 Recreate grants and synonyms

7.4 Apply Jan 2008 CPU for Oracle database 10g

Apply patch 6646853 to new 10g Oracle Home

Reference:

· Ensure that the Opatch version installed is 10.2.0.3.4 or higher.

· Ensure that Opatch is in path and apply this patch

· Ensure that the $PATH has the following executables: make, ar, ld, and nm

· Check whether the java and jar executables are present in 10.2.0 Oracle home. By default, the OPatch utility uses the executables from these locations. However, if they are located elsewhere, you can use the opatch apply command with the -jdk flag, and then specify the full path to the JDK to be used.

· Set your current directory to the directory where the patch is located

cd 6646853

opatch napply -skip_subset -skip_duplicate

· Load modified sql files into the database.

cd $ORACLE_HOME/cpu/CPUJan2008

sqlplus /nolog

SQL> CONNECT / AS SYSDBA

SQL> STARTUP

SQL> @catcpu.sql

SQL> QUIT

· Check the logfile for any errors

· If catcpu.sql reports any invalid objects run following commands

cd $ORACLE_HOME/rdbms/admin

sqlplus /nolog

SQL> CONNECT / AS SYSDBA

SQL> STARTUP

SQL> @utlrp.sql

· Check whether view compilation has already been performd for the database by executing following command:

SELECT * FROM registry$history where ID = ’6452863′;

· If the view compilation is already performed this query will return one row, else it will return no rows

· Run the view recompilation script. Note that this script is run with the database in upgrade mode, which restricts connections as SYSDBA.

cd $ORACLE_HOME/cpu/view_recompile

sqlplus /nolog

SQL> CONNECT / AS SYSDBA

SQL> STARTUP UPGRADE

SQL> @view_recompile_jan2008cpu.sql

SQL> SHUTDOWN;

SQL> STARTUP;

· Check the logfile for any errors

· If any invalid objects are reported then run utlrp.sql as below:

cd $ORACLE_HOME/rdbms/admin

sqlplus /nolog

SQL> CONNECT / AS SYSDBA

SQL> STARTUP

SQL> @utlrp.sql

Alert:

· On Solaris 10, OPatch may fail verification. If this happens, you must manually perform the verification. One of the reasons for failure is because Sun Microsystems changes an object file’s size when it archives the file into a library.

· In this case perform manual verification per below:

· Locate the current apply log file in $ORACLE_HOME/cfgtoollogs/opatch

· Locate the diagnostic records

· Compare the source and destination files. If all the source files are smaller by than 4 bytes compared to destination files then the patch has been applied successfully.

7.5 Gather schema statistics for sys, apps and applsys schemas

Reference:

· Execute the following statements to gather schema statistics. This is used to fasten the patching time.

· Login as 10g oracle user

SQL> connect / as sysdba

SQL>dbms_stats.gather_schema_stats(‘SYS’,cascade=>TRUE,degree=>24);

SQL>dbms_stats.gather_schema_stats(‘APPS’,cascade=>TRUE,degree=>24);

SQL>dbms_stats.gather_schema_stats(‘APPLSYS’,cascade=>TRUE,degree=>24);

7.6 Upgrade OUI to version 2.2 (patch 5035661)

Apply patch 5035661 to both iAS and RDBMS Oracle Home

Reference:

1. Applying the patch on the iAS $ORACLE_HOME:

· Unzip the patch into the <iAS ORACLE_HOME> directory:

unzip -od <iAS ORACLE_HOME> p5035661_11i_<PLATFORM>.zip

· Source the Apps environment file

· Change directory to the <iAS ORACLE_HOME>/appsoui/setup

· Execute the perl script OUIsetup.pl:

perl OUIsetup.pl

2. Applying the patch on the RDBMS $ORACLE_HOME:

· Unzip the patch into the <RDBMS ORACLE_HOME> directory:

· unzip -od <RDBMS ORACLE_HOME> p5035661_11i_<PLATFORM>.zip

· Source the DB environment file :

· Change directory to the <RDBMS ORACLE_HOME>/appsoui/setup

· Execute the perl script OUIsetup.pl:

perl OUIsetup.pl

7.7 Update timezone files to v4

Reference:

· Apply patch 5632264 for Oracle 10.2.0.3 (Solaris SPARC-64 bit) to update the timezone files to version 4 through opatch utility.

7.8 Apply OJVM Time Zone Update February-2007

Reference:

· Backup the following files and remove them from 10.2.0 ORACLE_HOME

$ORACLE_HOME/javavm/lib/zi/Africa/Timbuktu

$ORACLE_HOME/javavm/lib/zi/America/Buenos_Aires

$ORACLE_HOME/javavm/lib/zi/America/Catamarca

$ORACLE_HOME/javavm/lib/zi/America/Coral_Harbour

$ORACLE_HOME/javavm/lib/zi/America/Cordoba

$ORACLE_HOME/javavm/lib/zi/America/Indianapolis

$ORACLE_HOME/javavm/lib/zi/America/Jujuy

$ORACLE_HOME/javavm/lib/zi/America/Louisville

$OACLE_HOME/javavm/lib/zi/America/Mendoza

$ORACLE_HOME/javavm/lib/zi/Europe/Belfast

$ORACLE_HOME/javavm/lib/zi/Pacific/Yap

· Unzip the patch 5865568 and apply with opatch

· connect as SYS and run the $ORACLE_HOME/javavm/admin/fixTZa script.

$ sqlplus “/ as sysdba”

SQL> STARTUP

SQL> @?/javavm/admin/fixTZa

· If it terminates in any way other than printing the following message,

########################################################

Bug is in fact present, so this patch is needed

Proceed by restarting the database and running script

fixTZb

########################################################

inspect the output to determine whether the script was either incorrectly run or the bug is in fact not present. If the latter, take no further action. If the earlier, correct the indicated condition and rerun the script.

· Shutdown and restart the database using startup migrate.

· connect as SYS and run $ORACLE_HOME/javavm/admin/fixTZb script.

SQL> SHUTDOWN

SQL> STARTUP MIGRATE

SQL> @?/javavm/admin/fixTZb

If it terminates in any way other than printing the following message,

########################################################

# Bug is no longer present. Patch succeeded.

# ########################################################

inspect the output to determine what was done incorrectly and retry as indicated.

· Shutdown the database.

SQL> SHUTDOWN

· Restart the database without startup migrate.

8 Apply patch 5700129 to Oracle HTTP Server

· Make sure that all processes running from the Oracle Application Server ORACLE_HOME being patched, are shutdown before installing this patch.

· Ensure that ORACLE_HOME is set to the Oracle Application Server ORACLE_HOME.

· Set JDK_HOME to the JDK directory (JDK 1.3.1 or later).

· Ensure $ORACLE_HOME/bin and $JDK_HOME/bin are included in your PATH environment variable, and also ensure that the following executables are present in the $PATH: make,ar,ld,nm

· Set ORAINST to the location of the oraInst.loc file only if the Oracle Universal Installer inventory has been moved from the default location.

· For example, if you have moved the inventory to /tmp directory, then set ORAINST to /tmp/oraInst.loc.

· If ORAINST is not set, then the default location for that platform is used. For example, the default location on Solaris Operating System (SPARC 32-bit) is /var/opt/oracle/oraInst.loc.

· Set your current directory to the directory where the patch is located and then run the installcpu.sh script using the following command:

· sh installcpu.sh

· Inspect the following installation log file for any errors:

· $ORACLE_HOME/cpu/CPUJan2007/install<timestamp>.log

· Run the cpu_root.sh script as user root using the following command:

sh cpu_root.sh

· Run the 5700129/remove_demo.sh script to remove vulnerable Oracle HTTP Server demos. The demos cannot be restored after you run the script. If you expect to use the demos in the future, then manually backup the files in the $ORACLE_HOME/Apache/Apache/fcgi-bin directory before running the script.

· cd 5700129

· sh remove_demo.sh

9 Apply patches for Oracle Developer

9.1 Apply patch 5713544

Unzip the patch. Change the directory to where the patch was unzipped.

cd 5713544

If you don’t have genshlib in $ORACLE_HOME/bin directory then copy this file over there.

cp genshlib $ORACLE_HOME/bin

sh patch.sh

9.2 Apply patch 5216496

Copy the zip in $ORACLE_HOME and Unzip the zip file. This will unzip into 5216496 folder.

· If you don’t have genshlib in $ORACLE_HOME/bin directory then copy this file over there.

cp genshlib $ORACLE_HOME/bin

· Copy your original libraries in case you ever need it:

cd $ORACLE_HOME/lib

cp libiff.a libiff.a.ORG

cp libiff.so.0 libiff.so.0.ORG

cp libiffw.a libiffw.a.ORG

cp libiffw.so.0 libiffw.so.0.ORG

· Archive .o into iwf and iwfw library

cd $ORACLE_HOME/5216496/opt

ar -rv $ORACLE_HOME/lib/libiff.a ifzprs.o

cd $ORACLE_HOME/5216496/optweb

ar -rv $ORACLE_HOME/lib/libiffw.a ifzprs.o

· Generate All *.so’s

cd $ORACLE_HOME/lib

$ORACLE_HOME/bin/genshlib iffw 0

$ORACLE_HOME/bin/genshlib iff 0

· Generate forms executables

cd $ORACLE_HOME/forms60/lib

make -f cus_forms60w.mk install

9.3 Apply patch 5753922

Unzip Patch file into 6i Oracle Home

cd 5753922

sh patch.sh

9.4 Apply patch 6195758

Unzip the patch into 6i Oracle Home.

cd $ORACLE_HOME

unzip p6195758_600_GENERIC.zip

Unzip the java class files and regenerate your JAR files

Backup the Forms class files,

%cp -r $ORACLE_HOME/forms60/java/oracle/forms/handler/UICommon.class

$ORACLE_HOME/forms60/java/oracle/forms/handler/UICommon.class.PRE_BUG6195758

%cp -r $ORACLE_HOME/forms60/java/oracle/forms/handler/ComponentItem.class

$ORACLE_HOME/forms60/java/oracle/forms/handler/ComponentItem.class.PRE_BUG6195758

Inside folder 6195758 in step-2 has class files in oracle\forms\handler directory. Copy this file into ORACLE_HOME/forms60/java/oracle/forms/handler

cd $ORACLE_HOME/6195758/oracle/forms/handler

cp UICommon.class $ORACLE_HOME/forms60/java/oracle/forms/handler/UICommon.class

%cp ComponentItem.class $ORACLE_HOME/forms60/java/oracle/forms/handler/ComponentItem.class

9.5 Apply Patch 5938515

Unzip Patch file in 6i Oracle Home

cd 5938515

sh patch.sh

9.6 Apply Forms Interoperability Patch

Apply patch 4888294 using adpatch

9.7 Generate JAR Files

Generate JAR files using adadmin

9.8 Apply Security Fixes to Developer 6i

· Apply patch 5687261 to forms server

Unzip patch in 6i oracle home

cd 5687261

sh patch.sh 2>&1 | tee patch.log

Please inspect patch.log for any errors

· Apply patch 5686997

Unzip the patch in 6i Oracle Home

cd 5686997

sh patch.sh 2>&1 | tee patch.log

Please inspect patch.log for any errors.

· Apply patch 5078711

· Make sure that all processes running from the ORACLE_HOME being patched are shutdown before installing this patch.

· Ensure that ORACLE_HOME is set.

· Set JDK_HOME to the JDK directory (JDK 1.3.1 or later).

· Ensure $ORACLE_HOME/bin is included in your PATH environment variable, and also ensure that the following executables must be present in the $PATH: make, ar, ld, nm

· Set your current directory to the directory where the patch is located and then run the installcpu.sh script by entering the following UNIX command:

sh installcpu.sh

Inspect the following install log file for any errors:

10 Upgrade jinitiator to 1.3.1.29

· Extrace jinit*.exe from patch 6350285 and place it in $COMMON_TOP/util/jinitiator/ directory.

· Apply interoperability patch 6169479 using adpatch

· Run the jinit.sh script from fnd/patch/115/bin directory from where patch was unzipped.

jinit.sh 13129

This script will ask for following files

Location of APPSORA.env file
Location of Context File
Password for the APPS User in the database

11 Apply security fixes

Apply following patches for fixing various security bugs

July 2005 CPU

4132004

4355663

October 2005 CPU

4522020

4613714

4583865

4566995

January 2006 CPU

4865928

4759690

April 2006 CPU

4150288

5211695

4969592

4332440

5074725

5046719

July 2006 CPU

5183582

4896755

5083302

5127737

October 2006 CPU

5447522

5486407

5335967

5483388

5521476

January 2007 CPU

5658489

3748835

5661619

April 2007 CPU

5893391

5900224

5352601

5720979

5738134

5909233

5873313

5904576

5967405

5935683

July 2007 CPU

5973659

6045931

6117954

6045983

5973898

5973651

5973629

6185758

6082483

6110945

6111066

6111008

6188705

October 2007 CPU

4897479

4514856

6181762

5889600

January 2008 CPU

You can merge and apply these patches in one go

12 Apply Application Patches

Apply following application patches using adpatch for various bugs/issues/rollups

Slno

Module

Patch ID

Patch Category

1

INV

4536492

One-Off

2

Mobile Applications

4605116

One-Off

3

WMS

4550026

One-Off

4

INV

4739140

One-Off

5

Assets

6001110

One-Off

6

Tech Stack

4608023

Pre-Req

7

OM

4665900

Pre-Req

8

OM

4651420

Pre-Req

9

Shipping

4946700

Pre-Req

10

Shipping

4649626

Pre-Req

11

BoM

4216942

Pre-Req

12

OPM

5527236

Pre-Req

13

11i.HZ.N

3618299

Pre-Req

14

WMS

5855276

Rollup

15

Projects

4626807

Pre-Req

16

Projects

3485155

Family Pack

17

Projects

3337098

Pre-Req

18

Projects

4252634

Pre-Req

19

Project Mgmt

4440890

Pre-Req

20

Project Mgmt

5164577

Pre-Req

21

Project Mgmt

4884310

Pre-Req

22

Project Mgmt

4463552

Pre-Req

23

Tech Stack

4631022

Pre-Req

24

Tech Stack

4609681

Pre-Req

25

Project Mgmt

5087961

Pre-Req

26

Projects

5644830

Rollup

27

Web ADI

4957576

Post-Req

28

Purchasing

4364505

Post-Req

29

Project Mgmt

4682341

Post-Req

30

Project Costing

6272075

Post-Req

31

BOM

4917687

Rollup

32

Finance

3653484

Family Pack

33

Payables

5974819

Pre-Req

34

Project Costing

5394384

Pre-Req

35

Payables

4551936

Family Pack

36

Payables

6185758

One-Off

37

WMS

6442635

One-Off

38

WIP

4635071

One-Off

39

WIP

6173921

One-Off

40

Projects

6522896

One-Off

41

Projects

5532386

One-Off

42

Finance

6329356

One-Off

43

OM

5531894

One-Off

44

AR

4537149

One-Off

45

WMS

5849272

One-Off

46

Shipping

6220171

One-Off

47

Warehouse

5948166

One-Off

48

Oracle iReceivables

3618333

One-Off

49

Oracle iReceivables

4713959

One-Off

50

Purchasing

6003319

One-Off

51

Oracle iReceivables

3618333

Rollup

52

Oracle iReceivables

4713959

One-Off

53

Projects

5507295

One-Off

54

Projects

5843852

One-Off

55

Shipping

5688014

One-Off

56

Purchasing

4366835

One-Off

57

oracle iRec.

6398572

One-Off

58

Projects

6509104

One-Off

59

QA

4960189

One-Off

60

Project Billing

5532386

One-Off

61

Finance

4351912

One-Off

62

Finance

4713959

One-Off

63

Finance

6310878

One-Off

64

Finance

5404404

One-Off

65

Finance

6369513

One-Off

66

DUMMY BUG FOR FND PATCH

5733769

Pre_req for ASCP

67

DUMMY BUG FOR FND PATCH

5297822

Pre_req for ASCP

68

ASCP Planning Engine

6350793

One-Off

69

ATP RUPs

6374582

One-Off

70

Collections

6447137

One-Off

71

Help patch for ASCP

6080508

One-Off

72

Online Help

3275245

One-Off

73

OM

5697729

Pre-req for 6359269

74

OM

6058052

Pre-req for 6359269

75

OM

4406147

Pre-req for 6359269

76

OM(11.5.10) Cumulative Patch

6359269

One-Off

77

QA

6215459

One-Off

78

Mobile

6711886

One-Off

79

WIP

4517649

One-Off

80

Project

6768664

adpatch option=nocheckfile

81

Project

6050071

One-Off

82

Assests

5912795

One-Off

83

mobile

4941477

One-Off

We can merge and apply these patches in one go

13 Issues Faced.

13.1 Failure in patch 3180164

Solution : Backup $APPL_TOP/admin/topfile.txt.
2. Remove izu from that file.
3. Retry Patch 3180164, continue if it gives you that option.
4. After the patch is applied, run AutoConfig, and your topfile.txt should be recreated with izu
back in.

13.2 ADWorker Failed Duplication of Synonym and indexes

Solution:

14 Proposed Init.ora parameters for 10g

aq_tm_processes	1
background_dump_dest	‘/erppgfr1/oracle/10.2.0/admin/erppgfr1_tsgsd1006/bdump’
compatible	’10.2.0′
control_files	/erppgfr1/dbdata/data1/cntrl01.dbf’, ‘/erppgfr1/dbdata/data2/cntrl02.dbf’, ‘/erppgfr1/dbdata/redo1/cntrl03.dbf’
core_dump_dest	‘/erppgfr1/oracle/10.2.0/admin/erppgfr1_tsgsd1006/cdump’
cursor_sharing	‘EXACT’
db_block_checking	‘FALSE’
db_block_checksum	‘TRUE’
db_block_size	8192
db_cache_size	750M
db_file_multiblock_read_count	8
db_files	1022
db_name	‘erppgfr1′
dml_locks	10000
java_pool_size	67108864
job_queue_processes	2
large_pool_size	33554432
log_buffer	10485760
log_checkpoint_interval	100000
log_checkpoint_timeout	1200
log_checkpoints_to_alert	TRUE
max_dump_file_size	’20480′
nls_comp	‘binary’
nls_date_format	‘DD-MON-RR’
nls_language	‘american’
nls_length_semantics	‘BYTE’
nls_numeric_characters	‘.,’
nls_sort	‘binary’
nls_territory	‘america’
O7_DICTIONARY_ACCESSIBILITY	TRUE
olap_page_pool_size	4194304
open_cursors	600
optimizer_features_enable	’9.2.0′ = To Be Removed
parallel_max_servers	8
parallel_min_servers	0
pga_aggregate_target	2147483648
processes	1000
query_rewrite_enabled	‘true’
session_cached_cursors	200
session_max_open_files	20
sessions	2000
shared_pool_reserved_size	100M
shared_pool_size	750M
streams_pool_size	50331648
timed_statistics	TRUE
undo_management	‘AUTO’
undo_retention	1800
undo_tablespace	‘APPS_UNDOTS1′
user_dump_dest	‘/erppgfr1/oracle/10.2.0/admin/erppgfr1_tsgsd1006/udump’
utl_file_dir	/erppgfr1/erpapp/comn/temp’, ‘/erppgfr1/oracle/10.2.0/appsutil/outbound/ erppgfr1_tsgsd1006′,’/usr/tmp’, ‘/erppgfr1/erpapp/comn/temp’, ‘/custompro
workarea_size_policy	‘AUTO’
optimizer_secure_view_merging	FALSE
plsql_code_type	‘INTERPRETED’
plsql_optimize_level	2
_b_tree_bitmap_plans	FALSE
_fast_full_scan_enabled	FALSE
_kks_use_mutex_pin	TRUE
_like_with_bind_as_equality	TRUE
_sort_elimination_cost_ratio	5
_sqlexec_progression_cost	2147483647
_system_trig_enabled	TRUE
_trace_files_public	TRUE

September 18, 2008

sunil

Leave a comment

Categories: Upgrade

Create Custom_Top in R12

Here are the step by step instructions to create Custom Top & Test the Customized Application

Installation Steps

If Destination Environment Does not Include

q Make the directory structure for your custom application files

cd $APPL_TOP

mkdir xbol

mkdir xbol/11.5.0

mkdir xbol/11.5.0/admin

mkdir xbol/11.5.0/admin/sql

mkdir xbol/11.5.0/admin/odf

mkdir xbol/11.5.0/sql

mkdir xbol/11.5.0/bin

mkdir xbol/11.5.0/reports

mkdir xbol/11.5.0/reports/US

mkdir xbol/11.5.0/forms

mkdir xbol/11.5.0/forms/US

mkdir xbol/11.5.0/$APPLLIB

mkdir xbol/11.5.0/$APPLOUT

mkdir xbol /11.5.0/$APPLLOG

q Create the file customPRODR12_oslcoe01.env under $APPL_TOP.

Add entry XBOL_TOP=/u01/PRODR12/apps/apps_st/appl/xbol/12.0.0

export XBOL_TOP

q Create new tablespace for database objects

create tablespace XBOL datafile ‘/oracle/PRODR12/db/apps_st/data/xbol.dbf’ size 10M;

q Create schema

create user BOLINF identified by BOLINF default tablespace XBOL

temporary tablespace temp quota unlimited on XBOL quota unlimited on temp;

grant connect, resource to BOLINF;

q Register your Oracle Schema

Login to Applications with System Administrator responsibility

Navigate to Application–>Register

Application = Custom Application

Short Name = XBOL

Basepath = XBOL_TOP

Description = Custom Application

q Register Oracle User

Naviate to Security–>Oracle–>Register

Database User Name = BOLINF

Password = BOLINF

Privilege = Enabled

Install Group = 0

Description = Custom Application User

Verification Checklist

Note:Include a subset of test steps that will confirm that the customization has been installed properly.

Verify that the customization has been properly installed by following these steps:

q Testing the Custom Application

Copy a report from $FND_TOP/reports/US/ FNDSCURS.rdf to $XBOL_TOP/reports/US/ and rename it to CUSTFNDSCURS.rdf

Create an Executable:

Login to System Administartor Responsibility

Concurrent à Program à Executable

Enter the following details:

Define a Concurrent program:

Login to System Administartor Responsibility

Concurrent à Program à Define

Enter the following details:

Add the Custom Application to the Standard Data group:

Login to System Administartor Responsibility

Security à Oracle à DataGroup

Enter the following details:

Add the Custom Report to the System Administrator Reports Group:

Login to System Administartor Responsibility

Security à User à Responsibility à Request

Enter the following details

Run the Custom Report through the System administrator Responsibility

Complted Normal Status Indicates a Successful installation of a Custom Application.

August 26, 2008

sunil

3 Comments

Categories: R12 Installation

Clear fnd_concurrent_requests and concurrent_queues table

1. Shutdown Concurrent Managers (eg. run “adcmctl.sh stop apps/<apps password>”) on the application node(s) where the Concurrent Managers run.

Run the following query under the apps account to ensure any jobs scheduled to run in the source database are cancelled:

sql> truncate table applsys.fnd_concurrent_requests;

2 . Run the following query under the apps account to clear SOURCE node entries in the fnd_concurrent_queues table which would prevent the Concurrent Managers from starting correctly:

sql> UPDATE fnd_concurrent_queues

WHERE node_name IN(‘<Source Server1>’, ‘<Source Server2>’, ‘<etc>’);

Startup Concurrent Managers (eg. run “adcmctl.sh start apps/<apps password>”) on the application nodes where the Concurrent Managers run.

August 26, 2008

sunil

Leave a comment

Categories: Scripts

Rename ASM Instance in RAC

. Last week I build a two nodes cluster which will be called node 5 and 6 in the clusterware. When you normally configure ASM on a cluster through GUI, it builds number of ASM instances depending on number of nodes selected in the clusterware starting from 1 and 2. Unlike disk group it does not ask you to specify the name of the instance, which could be a GUI limitation. This is how I ended up having ASM instance called ‘+ASM1’ for node 5 and ‘+ASM2’ for node 6 but I wanted to reserve those names for node 1 and 2 when they are ready to be added. Hence I went round to rename the ASM instances to be called +ASM5 and +ASM6.

As nobody has come across this crazy situation before hence there are no help in Google and metalink. However I have now managed to work out the process to do this. To be honest it is not that bad as it sounds. If you ever come across this situation and would like to rename any ASM instance please read on …..

1. Logon to one of the ASM instance from a node and create a pfile from spfile.

2. Edit the pfile to change instance names.

I only changed the following parameters in the pfile:

+ASMn.asm_diskgroups=’DG1′,’DG2′,’RECOVERY’#Manual Mount
+ASMn.instance_number=n

Example: In the following example I am changing my instance names from +ASM1 to +ASM5 and +ASM2 to +ASM6 and they will be bound to node 5 and 6 as I said earlier.

+ASM5.asm_diskgroups=’DG1′,’DG2′,’RECOVERY’#Manual Mount
+ASM6.asm_diskgroups=’DG1′,’DG2′,’RECOVERY’#Manual Mount
+ASM5.instance_number=5
+ASM6.instance_number=6

3. Shutdown the RAC database either through normal database shutdown procedure or server control (srvctrl) utility.

4. Use server control (srvctl) utility to remove all database services and instances from OCR. (You may not have to do this though)

5. Restart the ASM instance with the edited pfile created in step-2

6. Create a new spfile from the pfile which will hold all the new instance names.

7. Go to the $ORACLE_ASM_HOME/dbs on each node and link the parameter file to the new spfile. (I am assuming you hold you ASM sp file on a shared storage to be used by all the nodes in the cluster).

8. Edit the /etc/oratab file to replace old ASM instance names with new names on all nodes.

9. Now add al new ASM instances to the OCR using server control (srvctl) utility.

10. Shutdown the ASM instance which started earlier to create spfile.

11. Now Start-up ASM instances on all nodes either through normal database start-up procedure or through server control (srvctl) utility.

12. Now you can run your CRS stat (‘crs_stat –t’ or crsstat) to verify that new ASM instances are up and running.

13. Add all services and instances to OCR (which were deleted in step 4) using server control (srvctl).

August 26, 2008

sunil

1 Comment

Categories: RAC

Find Size Of Your Database

SELECT ”TOTAL :”||SUM(ALLOCATED_MB), ”USED :”||SUM(USED_MB) USED, ”FREE :”||SUM(FREE_SPACE_MB) FROM (SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 allocated_mb, ((df.bytes / 1024 / 1024) – NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) used_mb, NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb FROM v$datafile df, dba_free_space dfs WHERE df.file# = dfs.file_id(+) GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes ORDER BY file_name); SELECT ”TEMPSIZE:”||USER_BYTES/1024/1024 FROM DBA_TEMP_FILES;

August 26, 2008

sunil

Leave a comment

Categories: Scripts

Find Who changed SYSADMIN password

select user_name
from fnd_user
where user_id = ( select last_updated_by from fnd_user where user_name=’SYSADMIN’ );

August 26, 2008

sunil

4 Comments

Categories: Scripts

STATSPACK

Create PERFSTAT Tablespace

The STATSPACK utility requires an isolated tablespace to obtain all of the objects and data. For uniformity, it is suggested that the tablespace be called PERFSTAT, the same name as the schema owner for the STATSPACK tables. It is important to closely watch the STATSPACK data to ensure that the stats$sql_summary table is not taking an inordinate amount of space.

SQL> CREATE TABLESPACE perfstat
     DATAFILE ‘/u01/oracle/db/AKI1_perfstat.dbf’ SIZE 1000M REUSE
     EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K
     SEGMENT SPACE MANAGEMENT AUTO
     PERMANENT
     ONLINE;

Run the Create Scripts

Now that the tablespace exists, we can begin the installation process of the STATSPACK software. Note that you must have performed the following before attempting to install STATSPACK.

Run catdbsyn.sql as SYS

Run dbmspool.sql as SYS

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus “/ as sysdba”
SQL> start spcreate.sql

Choose the PERFSTAT user’s password
———————————–
Not specifying a password will result in the installation FAILING

Enter value for perfstat_password: perfstat

Choose the Default tablespace for the PERFSTAT user
—————————————————
Below is the list of online tablespaces in this database which can
store user data. Specifying the SYSTEM tablespace for the user’s
default tablespace will result in the installation FAILING, as
using SYSTEM for performance data is not supported.

Choose the PERFSTAT users’s default tablespace. This is the tablespace
in which the STATSPACK tables and indexes will be created.

TABLESPACE_NAME CONTENTS STATSPACK DEFAULT TABLESPACE
—————————— ——— —————————-
PERFSTAT PERMANENT
SYSAUX PERMANENT *
USERS PERMANENT

Pressing <return> will result in STATSPACK’s recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace: PERFSTAT

Choose the Temporary tablespace for the PERFSTAT user
—————————————————–
Below is the list of online tablespaces in this database which can
store temporary data (e.g. for sort workareas). Specifying the SYSTEM
tablespace for the user’s temporary tablespace will result in the
installation FAILING, as using SYSTEM for workareas is not supported.

Choose the PERFSTAT user’s Temporary tablespace.

TABLESPACE_NAME CONTENTS DB DEFAULT TEMP TABLESPACE
—————————— ——— ————————–
TEMP TEMPORARY *

Pressing <return> will result in the database’s default Temporary
tablespace (identified by *) being used.

Enter value for temporary_tablespace: TEMP

…..
…..
Creating Package STATSPACK…

Package created.

No errors.
Creating Package Body STATSPACK…

Package body created.

No errors.

NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.

Check the Logfiles: spcpkg.lis, spctab.lis, spcusr.lis

Adjusting the STATSPACK Collection Level

STATSPACK has two types of collection options, level and threshold. The level parameter controls the type of data collected from Oracle, while the threshold parameter acts as a filter for the collection of SQL statements into the stats$sql_summary table.

SQL> SELECT * FROM stats$level_description ORDER BY snap_level;

Level 0	This level captures general statistics, including rollback segment, row cache, SGA, system events, background events, session events, system statistics, wait statistics, lock statistics, and Latch information.
Level 5	This level includes capturing high resource usage SQL Statements, along with all data captured by lower levels.
Level 6	This level includes capturing SQL plan and SQL plan usage information for high resource usage SQL Statements, along with all data captured by lower levels.
Level 7	This level captures segment level statistics, including logical and physical reads, row lock, itl and buffer busy waits, along with all data captured by lower levels.
Level 10	This level includes capturing Child Latch statistics, along with all data captured by lower levels.

You can change the default level of a snapshot with the statspack.snap function. The i_modify_parameter => ‘true’ changes the level permanent for all snapshots in the future.

SQL> exec statspack.snap(i_snap_level => 6, i_modify_parameter => ‘true’);

Create, View and Delete Snapshots

sqlplus perfstat/perfstat
SQL> exec statspack.snap;
SQL> select name,snap_id,to_char(snap_time,’DD.MM.YYYY:HH24:MI:SS’)
     “Date/Time” from stats$snapshot,v$database;

NAME         SNAP_ID Date/Time
——— ———- ——————-
AKI1               4 14.11.2004:10:56:01
AKI1               1 13.11.2004:08:48:47
AKI1               2 13.11.2004:09:00:01
AKI1               3 13.11.2004:09:01:48

SQL> @?/rdbms/admin/sppurge;
Enter the Lower and Upper Snapshot ID

Create the Report

sqlplus perfstat/perfstat
SQL> @?/rdbms/admin/spreport.sql

Statspack at a Glance

What if you have this long STATSPACK report and you want to figure out if everything is running smoothly? Here, we will review what we look for in the report, section by section. We will use an actual STATSPACK report from our own Oracle 10g system.

Statspack Report Header

STATSPACK report for

DB Name DB Id Instance Inst Num Release RAC Host
———— ———– ———— ——– ———– — —————-
AKI1 2006521736 AKI1 1 10.1.0.2.0 NO akira

              Snap Id     Snap Time      Sessions Curs/Sess Comment
            ——— —————— ——– ——— ——————-
Begin Snap:         5 14-Nov-04 11:18:00       15      14.3
End Snap:         6 14-Nov-04 11:33:00       15      10.2
   Elapsed:                15.00 (mins)

Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 24M Std Block Size: 4K
Shared Pool Size: 764M Log Buffer: 1,000K

Note that this section may appear slightly different depending on your version of Oracle. For example, the Curs/Sess column, which shows the number of open cursors per session, is new with Oracle9i (an 8i Statspack report would not show this data).

Here, the item we are most interested in is the elapsed time. We want that to be large enough to be meaningful, but small enough to be relevant (15 to 30 minutes is OK). If we use longer times, we begin to lose the needle in the haystack.

Statspack Load Profile

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   —————       —————
                  Redo size:            425,649.84         16,600,343.64
              Logical reads:              1,679.69             65,508.00
              Block changes:              2,546.17             99,300.45
             Physical reads:                 77.81              3,034.55
            Physical writes:                 78.35              3,055.64
                 User calls:                  0.24                  9.55
                     Parses:                  2.90                113.00
                Hard parses:                  0.16                  6.27
                      Sorts:                  0.76                 29.82
                     Logons:                  0.01                  0.36
                   Executes:                  4.55                177.64
               Transactions:                  0.03

% Blocks changed per Read: 151.59 Recursive Call %: 99.56
Rollback per transaction %: 0.00 Rows per Sort: 65.61

Here, we are interested in a variety of things, but if we are looking at a “health check”, three items are important:

The Hard parses (we want very few of them)

Executes (how many statements we are executing per second / transaction)

Transactions (how many transactions per second we process).

This gives an overall view of the load on the server. In this case, we are looking at a very good hard parse number and a fairly light system load (1 – 4 transactions per second is low).

Statspack Instance Efficiency Percentage

Next, we move onto the Instance Efficiency Percentages section, which includes perhaps the only ratios we look at in any detail:

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %: 100.00       Redo NoWait %:   99.99
            Buffer Hit   %:   95.39    In-memory Sort %: 100.00
            Library Hit   %:   99.42        Soft Parse %:   94.45
         Execute to Parse %:   36.39         Latch Hit %: 100.00
Parse CPU to Parse Elapsd %:   59.15     % Non-Parse CPU:   99.31

Shared Pool Statistics        Begin   End
                               —— ——
             Memory Usage %:   10.28   10.45
    % SQL with executions>1:   70.10   71.08
% Memory for SQL w/exec>1:   44.52   44.70

The three in bold are the most important: Library Hit, Soft Parse % and Execute to Parse. All of these have to do with how well the shared pool is being utilized. Time after time, we find this to be the area of greatest payback, where we can achieve some real gains in performance.

Here, in this report, we are quite pleased with the Library Hit and the Soft Parse % values. If the library Hit ratio was low, it could be indicative of a shared pool that is too small, or just as likely, that the system did not make correct use of bind variables in the application. It would be an indicator to look at issues such as those.

OLTP System

The Soft Parse % value is one of the most important (if not the only important) ratio in the database. For a typical OLTP system, it should be as near to 100% as possible. You quite simply do not hard parse after the database has been up for a while in your typical transactional / general-purpose database. The way you achieve that is with bind variables. In a regular system like this, we are doing many executions per second, and hard parsing is something to be avoided.

Data Warehouse

In a data warehouse, we would like to generally see the Soft Parse ratio lower. We don’t necessarily want to use bind variables in a data warehouse. This is because they typically use materialized views, histograms, and other things that are easily thwarted by bind variables. In a data warehouse, we may have many seconds between executions, so hard parsing is not evil; in fact, it is good in those environments.

The moral of this is …

… to look at these ratios and look at how the system operates. Then, using that knowledge, determine if the ratio is okay given the conditions. If we just said that the execute-to-parse ratio for your system should be 95% or better, that would be unachievable in many web-based systems. If you have a routine that will be executed many times to generate a page, you should definitely parse once per page and execute it over and over, closing the cursor if necessary before your connection is returned to the connection pool.

Statspack Top 5 Timed Events

Moving on, we get to the Top 5 Timed Events section (in Oracle9i Release 2 and later) or Top 5 Wait Events (in Oracle9i Release 1 and earlier).

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                      % Total
Event                                               Waits    Time (s) Call Time
——————————————– ———— ———– ———
CPU time                                                          122     91.65
db file sequential read                             1,571           2      1.61
db file scattered read                              1,174           2      1.59
log file sequential read                              342           2      1.39
control file parallel write                           450           2      1.39
          ————————————————————-
Wait Events DB/Inst: AKI1/AKI1 Snaps: 5-6

-> s – second
-> cs – centisecond - 100th of a second
-> ms – millisecond - 1000th of a second
-> us – microsecond – 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)

This section is among the most important and relevant sections in the Statspack report. Here is where you find out what events (typically wait events) are consuming the most time. In Oracle9i Release 2, this section is renamed and includes a new event: CPU time.

CPU time is not really a wait event (hence, the new name), but rather the sum of the CPU used by this session, or the amount of CPU time used during the snapshot window. In a heavily loaded system, if the CPU time event is the biggest event, that could point to some CPU-intensive processing (for example, forcing the use of an index when a full scan should have been used), which could be the cause of the bottleneck.

Db file sequential read – This wait event will be generated while waiting for writes to TEMP space generally (direct loads, Parallel DML (PDML) such as parallel updates. You may tune the PGA AGGREGATE TARGET parameter to reduce waits on sequential reads.

Db file scattered read - Next is the db file scattered read wait value. That generally happens during a full scan of a table. You can use the Statspack report to help identify the query in question and fix it.

SQL ordered by Gets

Here you will find the most CPU-Time consuming SQL statements

SQL ordered by Gets DB/Inst: AKI1/AKI1 Snaps: 5-6
-> Resources reported for PL/SQL code includes the resources used by all SQL
   statements called by the code.
-> End Buffer Gets Threshold:     10000 Total Buffer Gets:         720,588
-> Captured SQL accounts for    3.1% of Total Buffer Gets
-> SQL reported below exceeded 1.0% of Total Buffer Gets

                                                     CPU      Elapsd     Old
Buffer Gets    Executions Gets per Exec %Total Time (s) Time (s) Hash Value
————— ———— ————– —— ——– ——— ———-
         16,926            1       16,926.0    2.3     2.36      3.46 1279400914
Module: SQL*Plus
create table test as select * from all_objects

Tablespace IO Stats

Tablespace
——————————
                 Av      Av     Av                    Av        Buffer Av Buf
         Reads Reads/s Rd(ms) Blks/Rd       Writes Writes/s      Waits Wt(ms)
————– ——- —— ——- ———— ——– ———- ——
TAB      1,643       4    1.0    19.2       16,811       39          0    0.0
UNDO       166       0    0.5     1.0        5,948       14          0    0.0
SYSTEM     813       2    2.5     1.6          167        0          0    0.0
STATSPACK 146       0    0.3     1.1          277        1          0    0.0
SYSAUX      18       0    0.0     1.0           29        0          0    0.0
IDX         18       0    0.0     1.0           18        0          0    0.0
USER        18       0    0.0     1.0           18        0          0    0.0
          ————————————————————-

Rollback Segment Stats

->A high value for “Pct Waits” suggests more rollback segments may be required
->RBS stats may not be accurate between begin and end snaps when using Auto Undo
managment, as RBS may be dynamically created and dropped as needed

        Trans Table       Pct   Undo Bytes
RBS No      Gets        Waits     Written        Wraps Shrinks Extends
—— ————– ——- ————— ——– ——– ——–
     0            8.0    0.00               0        0        0        0
     1        3,923.0    0.00      14,812,586       15        0       14
     2        5,092.0    0.00      19,408,996       19        0       19
     3          295.0    0.00         586,760        1        0        0
     4        1,312.0    0.00       4,986,920        5        0        5
     5            9.0    0.00               0        0        0        0
     6            9.0    0.00               0        0        0        0
     7            9.0    0.00               0        0        0        0
     8            9.0    0.00               0        0        0        0
     9            9.0    0.00               0        0        0        0
    10            9.0    0.00               0        0        0        0
          ————————————————————-

Rollback Segment Storage

->Optimal Size should be larger than Avg Active

RBS No    Segment Size      Avg Active    Optimal Size    Maximum Size
—— ————— ————— ————— —————
     0         364,544               0                         364,544
     1      17,952,768       8,343,482                      17,952,768
     2      25,292,800      11,854,857                      25,292,800
     3       4,321,280         617,292                       6,418,432
     4       8,515,584       1,566,623                       8,515,584
     5         126,976               0                         126,976
     6         126,976               0                         126,976
     7         126,976               0                         126,976
     8         126,976               0                         126,976
     9         126,976               0                         126,976
    10         126,976               0                         126,976
          ————————————————————-

Generate Execution Plan for given SQL statement

If you have identified one or more problematic SQL statement, you may want to check the execution plan. Remember the “Old Hash Value” from the report above (1279400914), then execute the scrip to generate the execution plan.

sqlplus perfstat/perfstat
SQL> @?/rdbms/admin/sprepsql.sql
Enter the Hash Value, in this example: 1279400914

SQL Text
~~~~~~~~
create table test as select * from all_objects

Known Optimizer Plan(s) for this Old Hash Value
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Shows all known Optimizer Plans for this database instance, and the Snap Id’s
they were first found in the shared pool. A Plan Hash Value will appear
multiple times if the cost has changed
-> ordered by Snap Id

First        First          Plan
Snap Id     Snap Time     Hash Value        Cost
——— ————— ———— ———-
        6 14 Nov 04 11:26   1386862634        52

Plans in shared pool between Begin and End Snap Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Shows the Execution Plans found in the shared pool between the begin and end
snapshots specified. The values for Rows, Bytes and Cost shown below are those
which existed at the time the first-ever snapshot captured this plan – these
values often change over time, and so may not be indicative of current values
-> Rows indicates Cardinality, PHV is Plan Hash Value
-> ordered by Plan Hash Value

——————————————————————————–
| Operation                      | PHV/Object Name     | Rows | Bytes|   Cost |
——————————————————————————–
|CREATE TABLE STATEMENT          |—– 1386862634 —-|       |      |     52 |
|LOAD AS SELECT                  |                     |       |      |        |
| VIEW                           |                     |     1K| 216K|     44 |
| FILTER                        |                     |       |      |        |
|   HASH JOIN                    |                     |     1K| 151K|     38 |
|    TABLE ACCESS FULL           |USER$                |    29 | 464 |      2 |
|    TABLE ACCESS FULL           |OBJ$                 |     3K| 249K|     35 |
|   TABLE ACCESS BY INDEX ROWID |IND$                 |     1 |    7 |      2 |
|    INDEX UNIQUE SCAN           |I_IND1               |     1 |      |      1 |
|   NESTED LOOPS                 |                     |     5 | 115 |     16 |
|    INDEX RANGE SCAN            |I_OBJAUTH1           |     1 |   10 |      2 |
|    FIXED TABLE FULL            |X$KZSRO              |     5 |   65 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   FIXED TABLE FULL             |X$KZSPR              |     1 |   26 |     14 |
|   VIEW                         |                     |     1 |   13 |      2 |
|    FAST DUAL                   |                     |     1 |      |      2 |
——————————————————————————–

Resolving Your Wait Events

The following are 10 of the most common causes for wait events, along with explanations and potential solutions:

1. DB File Scattered Read

This generally indicates waits related to full table scans. As full table scans are pulled into memory, they rarely fall into contiguous buffers but instead are scattered throughout the buffer cache. A large number here indicates that your table may have missing or suppressed indexes. Although it may be more efficient in your situation to perform a full table scan than an index scan, check to ensure that full table scans are necessary when you see these waits. Try to cache small tables to avoid reading them in over and over again, since a full table scan is put at the cold end of the LRU (Least Recently Used) list.

2. DB File Sequential Read

This event generally indicates a single block read (an index read, for example). A large number of waits here could indicate poor joining orders of tables, or unselective indexing. It is normal for this number to be large for a high-transaction, well-tuned system, but it can indicate problems in some circumstances. You should correlate this wait statistic with other known issues within the Statspack report, such as inefficient SQL. Check to ensure that index scans are necessary, and check join orders for multiple table joins. The DB_CACHE_SIZE will also be a determining factor in how often these waits show up. Problematic hash-area joins should show up in the PGA memory, but they’re also memory hogs that could cause high wait numbers for sequential reads. They can also show up as direct path read/write waits.

3. Free Buffer

This indicates your system is waiting for a buffer in memory, because none is currently available. Waits in this category may indicate that you need to increase the DB_BUFFER_CACHE, if all your SQL is tuned. Free buffer waits could also indicate that unselective SQL is causing data to flood the buffer cache with index blocks, leaving none for this particular statement that is waiting for the system to process. This normally indicates that there is a substantial amount of DML (insert/update/delete) being done and that the Database Writer (DBWR) is not writing quickly enough; the buffer cache could be full of multiple versions of the same buffer, causing great inefficiency. To address this, you may want to consider accelerating incremental checkpointing, using more DBWR processes, or increasing the number of physical disks.

4. Buffer Busy

This is a wait for a buffer that is being used in an unshareable way or is being read into the buffer cache. Buffer busy waits should not be greater than 1 percent. Check the Buffer Wait Statistics section (or V$WAITSTAT) to find out if the wait is on a segment header. If this is the case, increase the freelist groups or increase the pctused to pctfree gap. If the wait is on an undo header, you can address this by adding rollback segments; if it’s on an undo block, you need to reduce the data density on the table driving this consistent read or increase the DB_CACHE_SIZE. If the wait is on a data block, you can move data to another block to avoid this hot block, increase the freelists on the table, or use Locally Managed Tablespaces (LMTs). If it’s on an index block, you should rebuild the index, partition the index, or use a reverse key index. To prevent buffer busy waits related to data blocks, you can also use a smaller block size: fewer records fall within a single block in this case, so it’s not as “hot.” When a DML (insert/update/ delete) occurs, Oracle Database writes information into the block, including all users who are “interested” in the state of the block (Interested Transaction List, ITL). To decrease waits in this area, you can increase the initrans, which will create the space in the block to allow multiple ITL slots. You can also increase the pctfree on the table where this block exists (this writes the ITL information up to the number specified by maxtrans, when there are not enough slots built with the initrans that is specified).

5. Latch Free

Latches are low-level queuing mechanisms (they’re accurately referred to as mutual exclusion mechanisms) used to protect shared memory structures in the system global area (SGA). Latches are like locks on memory that are very quickly obtained and released. Latches are used to prevent concurrent access to a shared memory structure. If the latch is not available, a latch free miss is recorded. Most latch problems are related to the failure to use bind variables (library cache latch), redo generation issues (redo allocation latch), buffer cache contention issues (cache buffers LRU chain), and hot blocks in the buffer cache (cache buffers chain). There are also latch waits related to bugs; check MetaLink for bug reports if you suspect this is the case. When latch miss ratios are greater than 0.5 percent, you should investigate the issue.

6. Enqueue

An enqueue is a lock that protects a shared resource. Locks protect shared resources, such as data in a record, to prevent two people from updating the same data at the same time. An enqueue includes a queuing mechanism, which is FIFO (first in, first out). Note that Oracle’s latching mechanism is not FIFO. Enqueue waits usually point to the ST enqueue, the HW enqueue, the TX4 enqueue, and the TM enqueue. The ST enqueue is used for space management and allocation for dictionary-managed tablespaces. Use LMTs, or try to preallocate extents or at least make the next extent larger for problematic dictionary-managed tablespaces. HW enqueues are used with the high-water mark of a segment; manually allocating the extents can circumvent this wait. TX4s are the most common enqueue waits. TX4 enqueue waits are usually the result of one of three issues. The first issue is duplicates in a unique index; you need to commit/rollback to free the enqueue. The second is multiple updates to the same bitmap index fragment. Since a single bitmap fragment may contain multiple rowids, you need to issue a commit or rollback to free the enqueue when multiple users are trying to update the same fragment. The third and most likely issue is when multiple users are updating the same block. If there are no free ITL slots, a block-level lock could occur. You can easily avoid this scenario by increasing the initrans and/or maxtrans to allow multiple ITL slots and/or by increasing the pctfree on the table. Finally, TM enqueues occur during DML to prevent DDL to the affected object. If you have foreign keys, be sure to index them to avoid this general locking issue.

7. Log Buffer Space

This wait occurs because you are writing the log buffer faster than LGWR can write it to the redo logs, or because log switches are too slow. To address this problem, increase the size of the log files, or increase the size of the log buffer, or get faster disks to write to. You might even consider using solid-state disks, for their high speed.

8. Log File Switch

All commit requests are waiting for “logfile switch (archiving needed)” or “logfile switch (Checkpoint. Incomplete).” Ensure that the archive disk is not full or slow. DBWR may be too slow because of I/O. You may need to add more or larger redo logs, and you may potentially need to add database writers if the DBWR is the problem.

9. Log File Sync

When a user commits or rolls back data, the LGWR flushes the session’s redo from the log buffer to the redo logs. The log file sync process must wait for this to successfully complete. To reduce wait events here, try to commit more records (try to commit a batch of 50 instead of one at a time, for example). Put redo logs on a faster disk, or alternate redo logs on different physical disks, to reduce the archiving effect on LGWR. Don’t use RAID 5, since it is very slow for applications that write a lot; potentially consider using file system direct I/O or raw devices, which are very fast at writing information.

10. Idle Event.

There are several idle wait events listed after the output; you can ignore them. Idle events are generally listed at the bottom of each section and include such things as SQL*Net message to/from client and other background-related timings. Idle events are listed in the stats$idle_event table.

Remove STATSPACK from the Database

After a STATSPACK session you want to remove the STATSPACK tables.

sqlplus “/ as sysdba”
SQL> @?/rdbms/admin/spdrop.sql
SQL> DROP TABLESPACE perfstat INCLUDING CONTENTS AND DATAFILES;

August 26, 2008

sunil

Leave a comment

Categories: StatsPack

Find Applied Patches

SELECT * FROM (SELECT bug_number AS Patch FROM ad_bugs UNION SELECT patch_name AS Patch FROM ad_applied_patches) WHERE Patch IN (‘Patch_Number’);

August 26, 2008

sunil

Leave a comment

Categories: Scripts

Find Command

1. Find / Replace globally ————-à>>> :%s/find/replace/g

2. Remove month wise —————–à > find . -mtime +5 -exec rm -f {} \;

3. Find month wise ——————-à find . -mtime +5 -exec rm -f {} \;

4. Find File ——————————> Find / -name “xyz” –print 2>/dev/null

5. Find the Concurrent Request Log and Output files which are not deleted

find $APPLCSF/$APPLLOG -mtime +5 -name “*.req”

find $APPLCSF/$APPLOUT -mtime +5 -name “*.out

6. Delete the Concurrent Request log and output files which are older than 7 Days

find $APPLCSF/$APPLLOG -mtime +5 -name “*.req” -exec rm “{}” “;”

find $APPLCSF/$APPLOUT -mtime +5 -name “*.out” -exec rm “{}” “;”

7. Delete other files in $APPLCSF/$APPLLOG directory

find $APPLCSF/$APPLLOG -mtime +7 -name “*.log” -exec rm “{}” “;”

find $APPLCSF/$APPLLOG -mtime +5 -name “*.mgr” -exec rm “{}” “;”

find $APPLCSF/$APPLLOG -mtime +7 -name “f60webmx*” -exec rm “{}” “;”

find $APPLCSF/$APPLLOG -mtime +7 -name “*.rti” -exec rm “{}” “;”

August 26, 2008

sunil

Leave a comment

Categories: Misc

Find OS(Linux) 32-bit or 64-bit

1. $ORACLE_HOME/bin file sqlplus

or

2. Getconf KERNEL_BITS

August 26, 2008

sunil

Leave a comment

Categories: Misc

TAR Commands

tar tvf myarchiv.tar; table of contents of a tar file

tar tzvf myarchiv.tar.gz; table of contents of a gzipped tar file

tar tvfj myarchiv.tar.bz2; table of contents of a bzipped tar file (this works only for newer versions of gnu tar)

tar xvf myarchiv.tar; extract all files from a tar file

tar xvf myarchiv.tar file1 file2; extract specific files from a tar file

tar xzvf myarchiv.tar.gz; extract all files from a gzipped tar file

tar xvfj file.tar.bz2; extract all files from a bzipped tar file

tar kxzvf files.tar.gz; query if an extracted file overwrites an existing file in the present d

August 26, 2008

sunil

Leave a comment

Categories: Misc

Find OS Version

OS (Linux) - uname -a or cat /etc/*-release

Solaris - isainfo -v

August 26, 2008

sunil

Leave a comment

Categories: Misc

Changing Apps Passwords

1. FNDCPASS apps/<old_psswd> 0 Y system/<psswd> SYSTEM APPLSYS new_psswd>

2. FNDCPASS apps/<passwd> 0 Y SYSTEM USER SYSADMIN <new passwd>

3. FNDCPASS apps/<password> 0 Y system/<password> ALLORACLE <new_password>

If APPS password of the new incarnation of the target database is different to what it was previously, then the following files need to be updated:

$ORACLE_HOME(806)/ reports60/server/CGIcmd.dat (on reports node)

$iAS_TOP/Apache/modplsql/cfg/wdbsvr.app (on web node)

4. ALTER USER SYSTEM IDENTIFIED BY <psswd>

5. ALTER USER SYS IDENTIFIED BY <psswd>

August 26, 2008

sunil

Leave a comment

Categories: Misc

Monitor Temporary Tablespace

select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

August 26, 2008

sunil

Leave a comment

Categories: Scripts

Monitor Tablespace Usage

select a.tablespace_name,
a.bytes/1024/1024 MB_Allocated,
(a.bytes – b.bytes)/1024/1024 MB_Used,
b.bytes/1024/1024 MB_Free,
round(((a.bytes – b.bytes)/a.bytes) * 100,2) percent_used
from
( select tablespace_name,
sum(bytes) BYTES
from dba_data_files
group by tablespace_name
)
a,
( select tablespace_name,
sum(bytes) BYTES
from dba_free_space
group by tablespace_name
)
b
where a.tablespace_name=b.tablespace_name
order by ((a.bytes-b.bytes)/a.bytes) desc ;

August 26, 2008

sunil

Leave a comment

Categories: Scripts

Find Locked Objects

select a.sid,a.serial#,c.object_name 
 from V$session a, 
 V$locked_object b, 
 user_objects c 
 where a.sid=b.session_id 
 and b.object_id=c.object_id;

August 26, 2008

sunil

Leave a comment

Categories: Scripts

Find sessions running jobs

set linesize 250

col sid for 9999 head ‘Session|ID’

col spid head ‘O/S|Process|ID’

col serial# for 9999999 head ‘Session|Serial#’

col log_user for a10

col job for 9999999 head ‘Job’

col broken for a1 head ‘B’

col failures for 99 head “fail”

col last_date for a18 head ‘Last|Date’

col this_date for a18 head ‘This|Date’

col next_date for a18 head ‘Next|Date’

col interval for 9999.000 head ‘Run|Interval’

col what for a60

select j.sid,

s.spid,

s.serial#,

j.log_user,

j.job,

j.broken,

j.failures,

j.last_date||’:'||j.last_sec last_date,

j.this_date||’:'||j.this_sec this_date,

j.next_date||’:'||j.next_sec next_date,

j.next_date – j.last_date interval,

j.what

from (select djr.SID,

dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,

dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,

dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT

from dba_jobs dj, dba_jobs_running djr

where dj.job = djr.job ) j,

(select p.spid, s.sid, s.serial#

from v$process p, v$session s

where p.addr = s.paddr ) s

where j.sid = s.sid;

August 26, 2008

sunil

2 Comments

Categories: Scripts

Find Running Jobs

set linesize 250

col sid for 9999 head ‘Session|ID’

col log_user for a10

col job for 9999999 head ‘Job’

col broken for a1 head ‘B’

col failures for 99 head “fail”

col last_date for a18 head ‘Last|Date’

col this_date for a18 head ‘This|Date’

col next_date for a18 head ‘Next|Date’

col interval for 9999.000 head ‘Run|Interval’

col what for a60

select j.sid,

j.log_user,

j.job,

j.broken,

j.failures,

j.last_date||’:'||j.last_sec last_date,

j.this_date||’:'||j.this_sec this_date,

j.next_date||’:'||j.next_sec next_date,

j.next_date – j.last_date interval,

j.what

from (select djr.SID,

dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,

dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,

dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT

from dba_jobs dj, dba_jobs_running djr

where dj.job = djr.job ) j;

August 26, 2008

sunil

Leave a comment

Categories: Scripts

Find scheduled Jobs

set linesize 250

col log_user for a10

col job for 9999999 head ‘Job’

col broken for a1 head ‘B’

col failures for 99 head “fail”

col last_date for a18 head ‘Last|Date’

col this_date for a18 head ‘This|Date’

col next_date for a18 head ‘Next|Date’

col interval for 9999.000 head ‘Run|Interval’

col what for a60

select j.log_user,

j.job,

j.broken,

j.failures,

j.last_date||’:'||j.last_sec last_date,

j.this_date||’:'||j.this_sec this_date,

j.next_date||’:'||j.next_sec next_date,

j.next_date – j.last_date interval,

j.what

from (select dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,

dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,

dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT

from dba_jobs dj) j;

August 26, 2008

sunil

Leave a comment

Categories: Scripts

Find Active Users & PIDs

SELECT

S.USERNAME SU,

S.OSUSER OSU,

to_char(S.LOGON_TIME,’MM-DD-YYYY HH24:MI:SS’) LOGTIME,

S.STATUS STAT,

S.SID SSID,

S.SERIAL# SSER,

LPAD(P.SPID,9) SPID,

SUBSTR(SA.SQL_TEXT,1,540) TXT

FROM V$PROCESS P,

V$SESSION S,

V$SQLAREA SA

WHERE P.ADDR=S.PADDR

AND S.USERNAME IS NOT NULL

AND S.SQL_ADDRESS=SA.ADDRESS (+)

AND S.SQL_HASH_VALUE=SA.HASH_VALUE (+)

ORDER BY 1,3,6;

August 26, 2008

sunil

Leave a comment

Categories: Scripts

2.11 Change password control policy

2.13 Unzip the Maintenance pack files

2.14 Apply the Maintenance Pack patch

5.2 Enable shared application file system à Handover

9.5 Apply Patch 5938515

Installation Steps

If Destination Environment Does not Include

Verification Checklist

Recent Posts

Pages

Follow “Oracle Apps Dba”