Oracle 11.1.0.7 to 11gR2 Upgrade Steps

1. Download and install the new 11gR2 Oracle in the target machine.

2. After installation copy the ORACLE_HOME/rdbms/admin/utlu112i.sql to some /tmp location as the 

script needs to be executed in the 11.1.0.7 box as pre-requisite step.

Pre-requisite Upgrade Steps in the source(11.1.0.7) box

1. Run the utlu112i.sql script which was copied to /tmp location in the earlier steps.

2. If the script was not run before the upgrade then the upgrade will fail while running the catupgrd.sql.

    Common error which we will face when the above script was not run before the upgrade will be

SQL> SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
2 FROM registry$database
3 WHERE tz_version != (SELECT version from v$timezone_file);
SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
*
ERROR at line 1:
ORA-01722: invalid number

3.Login as / as sysdba and run the below commands

      sql> spool upgrade_pre.log

      sql>@utlu112i,sql

      sql>spool off

4. The above spool file will validate whether the upgrade requirements are met or not like it will display the tablespace info,update parameters,deprecated parameters...etc.

5. Run the /rdbms/admin/utlrp.sql to validate invalidate objects.

6. For 11.1.0.7 no need to apply any DST related patches and we can take after the db upgrade.

7. Run the DBMS_STATS.GATHER_DICTIONARY_STATS.

  

     EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

8. No files should be in backup mode.

    sql>select * from v$recover_file;

    sql>select * from v$backup where status != 'NOT ACTIVE';

9. The users SYS and SYSTEM have 'SYSTEM' as their default tablespace.

10. Shutdown the db and take the cold backup or backup using rman.

11. Currently taken the cold backup like copied all the redo logs,datafiles,temp files,control files...to the target db.

Upgrade Steps in Target

1. Set the ENV in the target box(11gR2).

2. Currently copied the init.ora and modified according to the target db.

3. login as / as sysdba

    1. Startup nomount

    2. Recreate the Control file (If the target box is new)

    3. alter database open resetlogs upgrade;

    4. spool upgrade.info

     5.@/rdbms/admin/catupgrd.sql

     6. spool off

12. Will discuss the DST settings in the next blog.

R12 Architecture

HRMS Legislative Data Installation using Data Installer

The installation of legislative data is a two step process.
The first step is to run the DataInstall program and select the localizations for each HRMS product that you require or that is already installed.
The command line for the Data Install program is as follows:
java oracle.apps.per.DataInstall apps (password) thin (server):(db port):(SID)
where: (password) refers to the APPS schema password
(server) is the database server name or ip address
(db port) is the sql net or database port number default is 1521
(SID) is the database system identifier like PROD
for example:
java oracle.apps.per.DataInstall apps apps thin dbsvr1:1521:proddb
In version of apps prior to 11.5.10 the command uses jre instead of java.
for example:
jre oracle.apps.per.DataInstall apps apps thin dbsvr1:1521:proddb
DataInstall version 115.36

DataInstall Main Menu

1. Select legislative data to install/upgrade
2. Select college data to install/upgrade
3. Select JIT/Geocode or OTL to install/upgrade
4. Exit to confirmation menu

Enter your choice : 1


--------------------------------------------------------------------------------
After you enter option 1 you will see a similiar list of Localizations to select from:
# Localization Product(s) Leg. Data? Action
1 Global Human Resources Installed 
2 Australia Human Resources  
3 Australia Payroll   
4 Belgium Human Resources  
5 Canada Human Resources  
6 Canada Payroll   
7 China Human Resources  
8 China Payroll   
9 Denmark Human Resources  
10 Denmark Payroll   
11 Finland Human Resources  
12 Finland Payroll   
13 France Human Resources  
14 France Payroll   
15 Germany Human Resources  
16 Hong Kong Human Resources  
17 Hong Kong Payroll   
18 Hungary Human Resources  
19 Hungary Payroll   
20 India Human Resources  
21 India Payroll   
22 Ireland Human Resources  
23 Ireland Payroll   
24 Italy Human Resources  
25 Japan Human Resources  
26 Japan Payroll   
27 Korea, Republic of  Human Resources  
28 Korea, Republic of  Payroll  
29 Kuwait Human Resources  
30 Kuwait Payroll  
31 Mexico Human Resources  
32 Mexico Payroll  
33 Netherlands Human Resources  
34 Netherlands Payroll   
35 New Zealand Human Resources  
36 New Zealand Payroll   
37 Norway Human Resources  
38 Norway Payroll   
39 Poland Human Resources  
40 Poland Payroll   
41 Saudi Arabia Human Resources  
42 Saudi Arabia Payroll   
43 Singapore Human Resources  
44 Singapore Payroll   
45 South Africa Human Resources  
46 South Africa Payroll   
47 Spain Human Resources  
48 Spain Payroll   
49 United Kingdom Human Resources Installed 
50 United Kingdom Payroll  Installed 
51 United States Human Resources Installed 
52 United States Payroll  Installed 
53 United States US Federal Human Resources  
- Change Action
where is [I : Install, C : Clear]
[Return] - To return to main menu.
Enter your choice (for example 2I) : 1I

--------------------------------------------------------------------------------
In some cases, when selecting to install United States Payroll, JIT/GeoCode may also be marked as set to install.
It is highly recommended that JIT/Geocode be selected for installation even though it may already be marked for installation.
In cases where JIT/Geocode is not automatically selected for install, if you are installing Legislation please ensure that JIT/GeoCode is also selected.
Be sure to select Global and all other Legislation and Products you have Installed when running a later hrglobal update.
In this example be sure to enter: 1I, 49I, 50I, 51I, 52I
After selecting to install a legislations/products the list will add Install to the action column as follows:
# Localisation Product(s) Leg. Data? Action
1 Global Human Resources Installed Install
2 Australia Human Resources  
3 Australia Payroll   
4 Belgium Human Resources  
5 Canada Human Resources  
6 Canada Payroll   
7 China Human Resources  
8 China Payroll   
9 Denmark Human Resources  
10 Denmark Payroll   
11 Finland Human Resources  
12 Finland Payroll   
13 France Human Resources  
14 France Payroll   
15 Germany Human Resources  
16 Hong Kong Human Resources  
17 Hong Kong Payroll   
18 Hungary Human Resources  
19 Hungary Payroll   
20 India Human Resources  
21 India Payroll   
22 Ireland Human Resources  
23 Ireland Payroll   
24 Italy Human Resources  
25 Japan Human Resources  
26 Japan Payroll   
27 Korea, Republic of  Human Resources  
28 Korea, Republic of  Payroll  
29 Kuwait Human Resources  
30 Kuwait Payroll  
31 Mexico Human Resources  
32 Mexico Payroll  
33 Netherlands Human Resources  
34 Netherlands Payroll   
35 New Zealand Human Resources  
36 New Zealand Payroll   
37 Norway Human Resources  
38 Norway Payroll   
39 Poland Human Resources  
40 Poland Payroll   
41 Saudi Arabia Human Resources  
42 Saudi Arabia Payroll   
43 Singapore Human Resources  
44 Singapore Payroll   
45 South Africa Human Resources  
46 South Africa Payroll   
47 Spain Human Resources  
48 Spain Payroll   
49 United Kingdom Human Resources Installed Install
50 United Kingdom Payroll  Installed Install
51 United States Human Resources Installed Install
52 United States Payroll  Installed Install
53 United States US Federal Human Resources  
- Change Action
where is [I : Install, C : Clear]
[Return] - To return to main menu.
Enter your choice (for example 2I) :

--------------------------------------------------------------------------------
To return to the main menu just press the enter/return key
If you are using College data use Menu 2 to chose To install it

DataInstall Main Menu

1. Select legislative data to install/upgrade
2. Select college data to install/upgrade
3. Select JIT/Geocode or OTL to install/upgrade
4. Exit to confirmation menu

Enter your choice : 2

# Localization College Data? Action
1 United Kingdom Installed 
2 United States Installed 
- Change Action
where is [I : Install, C : Clear]
[Return] - To return to main menu.
Enter your choice (for example 2I) :

--------------------------------------------------------------------------------
To view currently installed JIT/Geocode data and to install JIT/Geocode and OTL data use menu 3
DataInstall Main Menu
1. Select legislative data to install/upgrade
2. Select college data to install/upgrade
3. Select JIT/Geocode or OTL to install/upgrade
4. Exit to confirmation menu

Enter your choice : 3
Currently installed JIT/Geocode data

Patch  applied Date
JIT_Q4_2004.1  22-MAR-2005
GEOCODE_ANNUAL_2004  22-MAR-2005
GEOCODE_ANNUAL_2004.2 22-MAR-2005
JIT_Q2.1_2004  28-OCT-2004
JIT_Q3_2003  11-DEC-2003
JIT_Q2_2003  05-AUG-2003
GEOCODE_ANNUAL_2003  05-AUG-2003
JIT_Q1_2003  04-JUN-2003
JIT_Q4.1_2002  20-JAN-2003
GEOCODE_ANNUAL_2002.1 20-JAN-2003
GEOCODE_ANNUAL_2002  20-SEP-2002
JIT_Q2_2002  20-SEP-2002
JIT_Q1_2002  16-JUL-2002
JIT_Q4_2001  17-JAN-2002
GEOCODE_ANNUAL_2001  19-DEC-2001
JIT_Q3_2001  19-DEC-2001
JIT_Q1_2001  25-JUN-2001
GEOCODE_2000_Q3  13-SEP-2000
JIT_Q3_2000  13-SEP-2000
GEOCODE_1999_Q4  21-AUG-2000
JIT_Q1_2000  21-AUG-2000
JIT_Q4_1999  12-JAN-2000
GEOCODE_1999_Q3  12-JAN-2000
# Option Data? Action
1 JIT/Geocode  Installed 
2 Oracle Time and Labor (OTL)   
- Change Action
where is [I : Install, C : Clear]
[Return] - To return to main menu.
Enter your choice (for example 1I) :

--------------------------------------------------------------------------------
To exit DataInstall program use menu 4, and confirm your choices
DataInstall Main Menu
1. Select legislative data to install/upgrade
2. Select college data to install/upgrade
3. Select JIT/Geocode or OTL to install/upgrade
4. Exit to confirmation menu

Enter your choice : 4
DataInstall - Actions confirmation
Do you really wish to exit and save your changes?
[Y] - Yes, save then exit
[N] - No, don't save but exit
[Return] - To return to the DataInstall Main Menu
Enter your choice (for example Y) : y

DataInstall - Actions summary

The following actions will be performed:


Localisation Product(s) Leg. Data? Action
Global  Human Resources Installed Install
United Kingdom Human Resources Installed Install
United Kingdom Payroll  Installed Install
United States  Human Resources Installed Install
United States  Payroll  Installed Install

Localisation College Data? Action
United Kingdom Installed 
United States  Installed 
Option Data? Action
JIT/Geocode  Installed Install
Oracle Time and Labor (OTL)  

--------------------------------------------------------------------------------
The second stage of the legislative update is to run ADPATCH to apply the hrglobal.drv driver.
Make sure you run the driver that is in the $PER_TOP/patch/115/driver directory.
If you run one from a different directory it may run without error, but no legislative data will have been updated.
Be sure to check the generated log files after hrglobal driver has completed to ensure no issues were encountered.
Please note that older version of Data Install required one to use Force Install to update legislation that where already installed.
If you see Force Install in your the list of actions in DataInstall, please apply the latest hrglobal patch to upgarde your DataInstall program.
The latest hrlgobal patch can be found in Metalink Note:145837.1.

Adpatch in Non-Interactive Mode

From 11.5, we can run AutoPatch non-interactively.

Before we can run AutoPatch non-interactively, for this mode we must first create an AutoPatch defaults file in $APPL_TOP/admin/<SID>

Here are the simple steps to create an AutoPatch defaults file for our current environment:

1. Specify defaultsfile=<New Defaults File Name> on the AutoPatch command line. The defaults file must be located under $APPL_TOP/admin/<SID>.
For example:
adpatch defaultsfile=$APPL_TOP/admin/testdb1/my_def.txt

2. Run AutoPatch up to the point where it asks you for the directory where your Oracle Applications patch has been unloaded. Then type ‘abort’ at this prompt.

3. Verify that our defaults file exists.

Once we have an AutoPatch defaults file for our current environment, you can run AutoPatch non-interactively.

It is possible to apply just a single patch driver file non-interactively using AutoPatch. Here is an example:
•defaults file is $APPL_TOP/admin/PROD/def.txt
•Applying copy driver for patch xxxxx, which is located in directory $APPL_TOP/patch/xxxxx.
•Using three parallel workers
•AutoPatch log file name is xxxxx.log
4. The AutoPatch command line would be:
adpatch defaultsfile=$APPL_TOP/admin/PROD/defaults.txt \
logfile=xxxxx.log \
patchtop=$APPL_TOP/patch/xxxxx \
driver=uxxxxx.drv \
workers=8 \
interactive=no

or

adpatch defaultsfile=$APPL_TOP/admin/PROD/defaults.txt interactive=no

If we don’t give any of the mode as mentioned above and apply the patch simply using adpatch command then Interactive will work which is normal mode of patch application.

Customizing the Configuration Files

a.Determine the AutoConfig template file you want to customize

Execute the following command to find out the corresponding AutoConfig template file for the configuration file you want to customize: Platform  Tier  Command 
Unix Application
<AD_TOP>/bin/adtmplreport.sh contextfile=<CONTEXT> target=<configurationfile>
 Database <RDBMS ORACLE_HOME>/appsutil/bin/adtmplreport.sh contextfile=<CONTEXT> target=<configurationfile>
For example, if you want to customize

$COMMON_TOP/html/bin/appsweb_$CONTEXT_NAME.cfg, execute:

On UNIX
$AD_TOP/bin/adtmplreport.sh contextfile=$CONTEXT_FILE \
target=$COMMON_TOP/html/bin/appsweb_$CONTEXT_NAME.cfg
The adtmplreport utility returns the name and location of the AutoConfig template file. For the above UNIX example it would return:
$FND_TOP/admin/template/appsweb.cfg.
we cannot customize all AutoConfig template files. The AutoConfig template file cannot be customized if the "LOCK" keyword appears in the template file's entry in the product driver file. AutoConfig ignores custom template files that are marked with "LOCK". For example, the following entry in <AD_TOP>/admin/driver/adtmpl.drv would prevent customization of the file adconfig.txt:
ad admin/template adconfig.txt INSTE8 <s_at>/admin adconfig.txt 600 LOCK

b.Create the custom template directory

Create a directory named "custom" at the location where the AutoConfig template file resides.
For example, if you want to customize <FND_TOP>/admin/template/appsweb.cfg, execute the following command as the applmgr user:

On UNIX
mkdir $FND_TOP/admin/template/custom

The custom template directory may have already been created when you migrated your customizations and migrate existing customizations. 

c.Copy the AutoConfig template file

Copy the AutoConfig template file to the custom template file.
Execute the following command as the applmgr user:

On UNIX
cp -i <AutoConfig template file> <custom template file>
For example:
cp -i $FND_TOP/admin/template/appsweb.cfg \
      $FND_TOP/admin/template/custom/appsweb.cfg

d.Edit the custom template file

Edit the custom template file with the editor of your choice, such as vi on UNIX.
Note: It is not supported to edit AutoConfig template files. Be sure to edit the custom template file only. 

e.Verify your customizations

Execute the adchkcfg utility.

On UNIX
adchkcfg.sh contextfile=<CONTEXT>

 When this utility runs, it instantiates any custom template files in place of the corresponding AutoConfig template file. The adchkcfg utility generates a report with information about all files and profile options that will be changed during the next normal execution of AutoConfig. Verify that your customizations would be applied as expected in your next AutoConfig run.

f.Run AutoConfig

Run AutoConfig and it instantiates any custom template file in place of the corresponding AutoConfig template file.

Steps to monitor the Concurrent Request

1. Take the Request Id of the Concurrent Program which needs to be monitored.

2. Get the sid for the request id with the help of below query

SELECT ses.sid,ses.sql_hash_value
ses.serial#
FROM v$session ses,
v$process pro
WHERE ses.paddr = pro.addr
AND pro.spid IN (SELECT oracle_process_id
FROM fnd_concurrent_requests
WHERE request_id = &request_id);

v$ for non-rac and gv$ for rac

3. After getting the sid,sql_hash_value please use this value in the below query to get the disk reads,buffergets values.

Select Inst_Id,
       Hash_Value,
       Sql_Id,
       Module,
       Buffer_Gets,
       Disk_Reads,
       Executions,
       Rows_Processed
  From Gv$sql
 Where Hash_Value = value;

4. The buffer_gets and disk_reads values should keep changing for every execution of the above query. If the values are constant for more time then you need to notify the Application People.

Helpful Queries

Query to check the number of Users logged in

select count(distinct d.user_name)

from apps.fnd_logins a, gv$session b, gv$process c, apps.fnd_user d

where b.paddr = c.addr

and a.pid=c.pid

and a.spid = b.process

and d.user_id = a.user_id

and (d.user_name = 'USER_NAME' OR 1=1);

How to check the database growth in previous months

select to_char(creation_time, 'RRRR Month') "Month",sum(bytes)/1024/1024

"Growth in MB" from sys.v_$datafile

where creation_time > SYSDATE-365

group by creation_time

Query to find the long running session ids and their work done

SELECT SID, SERIAL#, OPNAME, TARGET, SOFAR, TOTALWORK, UNITS,

TO_CHAR(START_TIME,'DD/MON/YYYY HH24:MI:SS') START_TIME,

TO_CHAR(LAST_UPDATE_TIME,'DD/MON/YYYY HH24:MI:SS') LAST_UPDATE_TIME,

TIME_REMAINING, ELAPSED_SECONDS, MESSAGE, USERNAME

FROM V$SESSION_LONGOPS

WHERE TIME_REMAINING != 0;

Query for finding the Blocking Sessions

SELECT substr(DECODE(request,0,'Holder: ','Waiter: ')||sid,1,12) sess,

       id1, id2, lmode, request, type, inst_id

FROM GV$LOCK

WHERE (id1, id2, type) IN

   (SELECT id1, id2, type FROM GV$LOCK WHERE request>0)

     ORDER BY id1, request;

If the environment is RAC Enabled else use V$ instead of GV$

Query to get the session id from the request id

SELECT ses.sid,
ses.serial#
FROM v$session ses,
v$process pro
WHERE ses.paddr = pro.addr
AND pro.spid IN (SELECT oracle_process_id
FROM fnd_concurrent_requests
WHERE request_id = &request_id);

Query to get the SPID

SELECT spid

FROM v$process

WHERE addr IN

(SELECT paddr FROM v$session WHERE sid=<> AND serial#=<>);


Query to get the request_id from the sid

select f.request_id, v.spid,s.sid, s.username,s.serial#, s.osuser, s.status

from gv$process v, gv$session s, applsys.fnd_concurrent_requests f

where 1=1

--and s.username=&USER_NAME

and s.paddr=v.addr

and f.oracle_process_id=v.spid

and trunc(f.request_date)=trunc(sysdate)

and sid=give the sid

Query to get the ddl of the database objects

select dbms_metadata.get_ddl('OBJECT_TYPE', 'OBJECT_NAME') from dual;

for example

select dbms_metadata.get_ddl('DB_LINK', 'APPS_TO_APPS') from dual;