DB UPGRADE 9206 TO 10203

System Overview
Version 11.5.10.2
Oracle Applications Vision Instance
OS: Redhat Linux 5 update 5
Type: Single Node instance
Applications OS User: appl11i
Database 9i OS User : db11i (same user for 9i and 10g)
9i Database Home = /d011/db11i/db/oaproddb/9.2.0
10g Database Home = /d011/db11i/db/oaproddb/10.2.0

Instance SID: DB11i
Host: demo
url:demo.conacent.com:8000
listener port:1521


Task – To Upgrade database from 9.2.0.6 to 10.2.0.3 for Oracle
Applications 11.5.10.2.


Pre Upgrade Steps

   a> Apply the patches - 5478710,4676589, 4653225
   b> Create a new home for Oracle 1 
   c> Install Oracle Database 10g.
   d> Install the companion CD.
   e> Install 10.2.0.3 patch set
   f> Create nls/data/9idata directo  
   g> Install 10g Listener


Upgrade Steps

   a> Use manual steps or dbua to upgrade the db from 9.2.0.6 to
      10.2.0.3. Manual upgrade is preferred in the industry

Post Upgrade Steps

   a> On the application tier generate appsutil.zip file
   b> Copy the appsutil.zip file generated on application tier to the         
      database tier inside 10g Oracle Home
   c> create a context file on database tier
   d> run autoconfig

Pre Upgrade Steps


a> Patches Required – 5478710, 4676589, 4653225
5478710 - TXK (FND & ADX) AUTOCONFIG ROLLUP PATCH O (December 2006)  
4676589 - ATG Roll up 4
4653225 - 11.5.10 INTEROP PATCH FOR 10GR2



Note: After apply patch 5478710(before run autoconfig) change  LD_ASSUME_KERNEL TO XD_ASSUME_KERNEL BOTH db and appl
      than runs autocfg.

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

1   Before  install 10g software to change
     vi /etc/redhat-release
    change:Red Hat Enterprise Linux ES release 4 (Nahant Update 2)

2.change inventory location

     #inventory_loc=/etc/oraInventory
     inventory_loc=/d011/db11i/db/oaproddb/10.2.0/inventory

3.change oratab location:
     cat oratab
    
       DB11i:/d011/db11i/db/oaproddb/10.2.0:N

4.Install 10 g software only

5. before  companios cd installion
  set variable in bash_profile


export KSH_VERSION='@(#)PD KSH v5.2.14 99/07/13.2'
#. /d011/db11i/db/oaproddb/10.2.0/DB11i_demo.env
export PATH


ORACLE_BASE=/d011/db11i/db/oaproddb
export ORACLE_BASE

ORACLE_HOME=/d011/db11i/db/oaproddb/10.2.0/
export ORACLE_HOME

6.Install Companions cd.

Note:  In a Product to install
      Oracle Database 10g Producst 10.2.0
       

7. Install 10g patchset3

   p5337014_10203_LINUX.zip

#####################################################################################################
1.Copy dbs,appsutil,admin,.env,network/admin in 9i to 10g.

2. Replace all the file with 9.2.0 to 10.2.0.

3. set .bash_profile to new oracle_home

4.  Runs create  perl cr9idata.pl

5.   Set ORA_NLS10

   vi .env($oracle_home)10g
  if test "db920" = "db101" -o "db920" != "db102"; then

6.Apply opatch in 10g (before upgrade)
   p5648872_10203_LINUX.zip

7.Make the below changes in initTEST.ora (10g)

#optimizer_max_permutations
#row_locking
#undo_suppress_errors
#max_enabled_roles
#enqueue_resources
#sql_trace
streams_pool_size=50331648
large_pool_size = 8388608
session_max_open_files=20
optimizer_features_enable=10.2.0.3
compatible=10.2.0.

8.shutdown 9i db and 9ilistener

9.set vi .bash_profile in new home(10g)

10.oratab set old home(9.2.0)

11. upgrade database

   a.Manual

    cd $ORACLE_HOME/bin

    sqlplus “/as sysdba”

     startup upgrade

12. Create sysaux tablespace

  CREATE TABLESPACE sysaux DATAFILE '/d01/oracle/singlenode/visdata/sysaux01.dbfSIZE 1000M REUSE
  EXTENT MANAGEMENT LOCAL
   SEGMENT SPACE MANAGEMENT AUTO
   ONLINE;

13.    Run catupgrade.sql
   cd $ORACLE_HOME/rdbms/admin
   @catupgrade.sql

  b. For dbua
     cd $ORACLE_HOME/bin
    ./dbua
############################################################################################################

Post Upgrade Steps:



Some more fixes


i) Fix Korean lexers
a. Connect to sqlplus as sysdba
b. execute $ORACLE_HOME/ctx/sample/script/drkorean.sql

ii) Run adgrants.sql
a. Connect to sqlplus as sysdba
b. Execute @$APPL_TOP/admin/adgrants.sql APPLSYS

iii) Grant create procedure privilege on CTXSYS
a. Connect to sqlplus with apps
b. Execute @$AD_TOP/patch/115/sql/adctxprv manager CTXSYS
manager is the SYSTEM password

Added few steps

i) Gather Statistics for SYS schema
a. sqlplus “/as sysdba”
b. shutdown immediate
c. startup restrict
d. @/oracle/tst10appl/admin/adstats.sql
e. shutdown
f. startup

ii) Recreate grants and synonym for apps
a. Log in to server with applmgr user
b. Execute adadmin
c. Choose -> Maintain Applications Database Entities menu
d. Choose -> Re-create grants and synonyms for APPS schema

 iii) On the application tier run the below command to generate
appsutil.zip file

 perl /a01/appl11i/appl/oaprodappl/ad/11.5.0/bin/admkappsutil.pl

iv)  Next step is to create a context file on database tier. Run the
below commands with appropriate values. This will generate context file
on database tier

$ORACLE_HOME/appsutil/bin
perl adbldxml.pl tier=db appsuser=apps appspasswd=apps

Note:copy jre folder in $ORACLE_HOME/appsutil to 10 oracle_home/appsutil/ than cretae context file

v) Once the context file is created on db tier, run autoconfig.

vi)start Application and Db

----------------------------------------------------------------------------------------------------------------
Login Page

-----------


   checking URL = http://demo.conacent.com:8000/OA_HTML/AppsLogin

   RW-50016: Error: - {0} was not created:
      File = {1}

apply opatch 5890966 for login page issue.

11i TO R12 UPGRADE

Database : 10.2.0.3
Application: 11.5.10.2



Step 1.  Apply the below patches in your existing 11.5.10.2 environment

Shut down the entire E-Business Suite environment and perform a full backup
4712852 - Minipack 11i.AD.I.4
5753359 - ALPPNR, NO RESPONSE ACTION PROCESSOR CONC PGM ENDS IN ERROR
5467526 - OATM MIGRATION UTILITY: ORA-22853 IN ERROR LOG
5120936 - TUMS for R12: TO DELIVER TUMS UTILITY FOR UPGRADES FROM 11I TO R12
5726010 - AD Preparation Scripts for R12


Step 2.   Run rapidwiz to create R12 code tree.

Step 3.    Move the new environment file into .bash_profile(R12)

Step 4.    Now onwards, you are connected to R12 application

-- Apply patch AD.A 4502962

Step 5.   Download the NLS Release 12 patch (4440000) for each active language in the system.

-- Run the American English upgrade patch driver
-- Takes 40 hours

Step 6.  Disable maintenance mode

Step 7.  Run admkappsutil.pl in application.

Step 8.   copy appsutil.zip to database(10g)

Step 9.   run adautocfg in db.

Step 10.  Run rapidwiz to configure and start processes.

HOW TO SHRINK A TABLE IN ORACLE

--------------- How to create table to another table reference

create table test121 as select * from fnd_user;


select count(1) from test121;

select * from test121;
------------------Insert data to anoter table reference:


insert into test121  select * from fnd_user;

------------------Check the table size

select segment_name,bytes/1024/1024 as mb from user_segments where segment_name='TEST121';


delete from test121;


alter table test121 enable row movement;


 alter table test121 shrink space;


select segment_name,bytes/1024/1024 as mb from user_segments


------ take table backup

create table test_bak as select * from test1

How to Audit User Activity in Oracle Database

Step 1.

SQL> alter system set audit_trail=db scope=spfile;
System altered.

SQL> alter system set audit_sys_operations=true scope=spfile;

Step 2. stop Database

Step 3. start database.

Step 4 check parameter
SQL> show parameter audit_
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string          /d011/db11i/db/oaproddb/10.2.0/rdbms/audit
audit_sys_operations            boolean     TRUE
audit_syslog_level                string
audit_trail                            string           DB

Step 5. Log in as sysdba.

 SQL> audit all by apps by access;
     
OR
        Give the audit permission for delete and drop objects.

        SQL> audit DELETE ANY TABLE,DROP ANY INDEX,DROP ANY PROCEDURE,DROP ANY TABLE,DROP ANY VIEW,ALTER ANY PROCEDURE,ALTER ANY INDEX by apps
by access whenever successful;

Step 7. Check the operation (log in as sysdba);

 SQL> SELECT username, extended_timestamp, owner,obj_name ,action_name FROM dba_audit_trail WHERE  owner = 'APPS' and extended_timestamp=sysdate -1 ORDER BY timestamp;

 SQL> SELECT count(1) FROM dba_audit_trail WHERE  owner = 'APPS' and extended_timestamp=sysdate -1 ORDER BY timestamp;

 SQL> SELECT count(1) FROM dba_audit_trail WHERE  owner = 'APPS' and extended_timestamp=sysdate - 1

SELECT count(1) FROM dba_audit_trail WHERE  owner = 'APPS' ORDER BY timestamp;

Step 8. Check how many no of record in audit tables;
select count(1) from sys.aud$;

        

Enable Trace for long running Concurrent Request in Apps 11i/R12

Enable Trace for long running Concurrent Request in Apps 11i/R12

This post covers overview of How to troubleshoot long running concurrent request in Oracle
Apps 11i/R12

Step 1 : Check Concurrent Request ID of long running concurrent request from front end
Step 2 : Find SID, SERIAL# and SPID by running SQL (given below)
Step 3 : Enable event 10046 trace with level 12 using oradebug ( for 15-20 minute)
Step 4 : Disable trace (once you are happy with trace size)
Step 5 : Convert raw trace to TKPROF using various sort options like fchela, prsela, execpu
Step 6 : Check TKPROF out file to find root cause of slow concurrent request

Step 1 : Check Request ID from Find Concurrent request screen (In my case Request ID is 1145)

Step 2 : Run below command to find SPID, provide concurrent request ID (1145 in my case)
when prompted

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID;


REQUEST_ID   SID    SERIAL#             OSUSER                       PROCESS           SPID
—————----------------------------------------------------------------------------
1145              514     28                  applmgr                     17794           2987.
.
Step 3.1 : Check and confirm SPID on Database Node
$ ps-ef | grep 2987
proddb 2987 1 0 13:30:43 ?
0:03 oracledbr12 (LOCAL=NO)

Step 3.2 : Set OSPID (2987 in my case) for ORADEBUG
SQL> oradebug setospid 2987

Step 3.3 : Enable trace for 10046 event with level 12
SQL> oradebug event 10046 trace name context forever, level 12
LEVEL 12 – Both Binds and Waits
LEVEL 8 – Only WAITS
LEVEL 4 – Only BIND Variables

Step 3.4 : Locate Trace file as
SQL>oradebug tracefile_name
/oracle/apps/proddb/10.2.0/admin/prod_CSDoracle/udump/ prod _ora_2987.trc
Wait for 15-20 minutes


Step 4 : Disable trace
SQL> oradebug event 10046 trace name context off


Step 5: Create tkprof file like
$ tkprof prod _ora_2987.trc prod _ora_2987.txt explain=apps/<****> sort=(exeela,fchela)
sys=no


Step 6 : Check TKPROF file to find root cause of slow concurrent request


We can follow the Metalink Note: 296559.1 to know more about tracing.