Upgrade database from 9i to 10g – Windows

Followings steps when performed, will upgrade the database from 9i to 10g. 

Please note that you need to install ORACLE_HOME for 10g before going ahead.

Step 1) Run the script utlu102i.sql from 10g OH in 9i database

D:\oracle\ora92\bin>sqlplus “/as sysdba”

SQL*Plus: Release 9.2.0.6.0 – Production on Tue Oct 31 17:26:25 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 – Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 – Production

SQL> spool preupgrade.log
SQL> @D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlu102i.sql
Oracle Database 10.2 Upgrade Information Utility    10-31-2006 17:26:55
.
**********************************************************************
Database:
**********************************************************************
–> name:       orcl
–> version:    9.2.0.6.0
–> compatible: 9.2.0
–> blocksize:  8192
.
**********************************************************************
Logfiles: [make adjustments in the current environment]
**********************************************************************
–> The existing log files are adequate. No changes are required.
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
–> SYSTEM tablespace is adequate for the upgrade.
…. minimum required size: 246 MB
…. AUTOEXTEND additional space required: 76 MB
WARNING: –> TEMP tablespace is not large enough for the upgrade.
…. currently allocated size: 10 MB
…. minimum required size: 58 MB
…. increase current size by: 48 MB
…. tablespace is NOT AUTOEXTEND ENABLED.
.
**********************************************************************
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
WARNING: –> “shared_pool_size” needs to be increased to at least 173667533
WARNING: –> “java_pool_size” needs to be increased to at least 67108864
WARNING: –> “streams_pool_size” is not currently defined and needs a value of
at least 50331648
WARNING: –> “large_pool_size” needs to be increased to at least 8388608
WARNING: –> “session_max_open_files” needs to be increased to at least 20
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
— No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]

**********************************************************************
— No obsolete parameters found. No changes are required
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
–> Oracle Catalog Views         [upgrade]  VALID
–> Oracle Packages and Types    [upgrade]  VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: –> Database contains stale optimizer statistics.
…. Refer to the 10g Upgrade Guide for instructions to update
…. statistics prior to upgrading the database.
…. Component Schemas with stale statistics:
….   SYS
.
**********************************************************************
SYSAUX Tablespace:
[Create tablespace in the Oracle Database 10.2 environment]
**********************************************************************
–> New “SYSAUX” tablespace
…. minimum required size for database upgrade: 500 MB
.

PL/SQL procedure successfully completed.

Once the above step is executed, follow the below steps to complete the upgrade.

Step 2) Make the recomended changes and bounce the database. Run the script again
        and check if any recomendations.

Step 3) Make the following changes
       
        Copy the init.ora file from old 9i ORACLE_HOME/dbs to new 10g ORACLE_HOME/dbs
        Copy the password file from old 9i ORACLE_HOME/dbs to new 10g ORACLE_HOME/dbs
        Change the compatible parameter to 9.2.0
       
Step 4) Delete the oracleservice in windows
        ORADIM -DELETE -SID <db_name>
       
Step 5) Create a new oracleservice
        ORADIM -NEW -SID <db_name> -INTPWD PASSWORD -STARTMODE MANUAL -PFILE ORACLE_HOME\DATABASE\INITSID.ORA

Oracle Blocks – Details

we can get the information about which table is present in which datafile.
For that you can seach for file_id in dba_extents using the segment name as your table name. using this file ID we can get the tablespace_name and file_name from dba_data_filesExample:

select file_id, block_id, segment_name from dba_extents
where segment_name = ‘IGI_MPP_AP_INVOICES_I1’

FILE_ID BLOCK_ID
———- ———-
SEGMENT_NAME
——————————————————————————–
29 47593
IGI_MPP_AP_INVOICES_I1
select tablespace_name, file_id, file_name from dba_data_files
where file_id = 29;
TABLESPACE_NAME FILE_ID
—————————— ———-
FILE_NAME
——————————————————————————–
APPS_TS_TX_IDX 29
/dy/oracle/product/test12/db/apps_st/data/a_txn_ind05.dbf

file_id and block number can also be obtained from dba_segments.

SQL> select SEGMENT_NAME, HEADER_FILE , HEADER_BLOCK from dba_segments
2 where SEGMENT_NAME = ‘IGI_MPP_AP_INVOICES_I1’;

SEGMENT_NAME
——————————————————————————–
HEADER_FILE HEADER_BLOCK
———– ————
IGI_MPP_AP_INVOICES_I1
29 47595
you can also get the dump of a perticular oracle block and see the content of that block.
This is basically useful to check if the data block is corrupt.
Use the following command for the same

SQL> alter system dump datafile 29 block 47595;

System altered.

Check in UDUMP area, where we can see the trace file content as given below.

Sample dump will look like the following

==================================================

Dump file /dy/oracle/product/test12/db/tech_st/10.2.0/admin/test12_ap101fam/

udump/test12_ora_24459.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /dy/oracle/product/test12/db/tech_st/10.2.0
System name: SunOS
Node name: ap101fam
Release: 5.9
Version: Generic_117171-14
Machine: sun4u
Instance name: test12
Redo thread mounted by this instance: 1
Oracle process number: 39
Unix process pid: 24459, image: oracle@ap101fam (TNS V1-V3)

*** 2007-05-26 23:31:46.263
*** SERVICE NAME:(SYS$USERS) 2007-05-26 23:31:46.247
*** SESSION ID:(356.597) 2007-05-26 23:31:46.247
Start dump data blocks tsn: 11 file#: 29 minblk 47595 maxblk 47595
buffer tsn: 11 rdba: 0x0740b9eb (29/47595)
scn: 0x0000.00127f0b seq: 0x02 flg: 0x04 tail: 0x7f0b2302
frmt: 0x02 chkval: 0x93b9 type: 0x23=PAGETABLE SEGMENT HEADER
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00000001064EC000 to 0x00000001064EE000
1064EC000 23020000 0740B9EB 00127F0B 00000204 [#….@……….]
1064EC010 93B90000 00000000 00000000 00000000 […………….]
1064EC020 00000000 00000001 00000010 0A9C0000 […………….]
1064EC030 00000000 00000004 00000010 0740B9ED [………….@..]
1064EC040 00000000 00000000 00000000 00000001 […………….]
1064EC050 00000000 00000000 00000000 00000000 […………….]
1064EC060 00000004 00000010 0740B9ED 00000000 [………@……]
1064EC070 00000000 00000000 00000001 0740B9E9 [………….@..]
1064EC080 0740B9E9 00000000 00000000 00000000 [.@…………..]
1064EC090 00000000 00000000 00000000 00000000 […………….]



Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 1 obj#: 102145 flag: 0x20000000
Inc # 0
Extent Map
—————————————————————–
0x0740b9e9 length: 16

Auxillary Map
——————————————————–
Extent 0 : L1 dba: 0x0740b9e9 Data dba: 0x0740b9ec
——————————————————–

Second Level Bitmap block DBAs
——————————————————–
DBA 1: 0x0740b9ea

End dump data blocks tsn: 11 file#: 29 minblk 47595 maxblk 47595

==================================================

If you see there is a obj# giving in the above dump from a block we can actually see
if this obj# is matches with out object using sys table sys.obj$.

SQL> select name from sys.obj$
2 where obj# = 102145;

NAME
——————————
IGI_MPP_AP_INVOICES_I1

So just using the dump from a datablock we can very well check which database object this data belongs to.

Concurrent Manager: Output Post-processor issue

The concurrent program the output of which is XML report is failing in warning and the log shows the below message:
Beginning post-processing of request 5292785 on node AP6105RT at 02-AUG-2006 04:47:48. Post-processing of request 5292785 failed at 02-AUG-2006 04:49:48 with the error message:

The Output Post-processor is running but has not picked up this request. No further attempts will be made to post-process this request, and the request will be marked with Warning status.
Setting the profile option Concurrent: OPP Response Timeout to a higher value may be necessary. Example if the responce time is set to 60 make it atleast 180. Bounce the concurrent manager once this is done. This will resolve the issue.

If the issue still persist, check the OPP log file. You follow below navigation

System Administrator -> Concurrent : Manager -> Administrator

On this screen click on Output Post Processor and then click on Processes button -> Manager Log button

A new web page will open showing the manager log. Come to the bottom, you might see followng error

[10/7/08 10:15:56 PM] [1900472:RT5709298] Completed post-processing actions
for request 5709298.
[10/7/08 10:20:07 PM] [OPPServiceThread1] Post-processing request 5709301.
[10/7/08 10:20:07 PM] [1900472:RT5709301] Executing post-processing actions
for request 5709301.
[10/7/08 10:20:07 PM] [1900472:RT5709301] Starting XML Publisher
post-processing action.
[10/7/08 10:20:07 PM] [1900472:RT5709301]
@ Template code: FAXSRPIM
Template app:  OFA
Language:      EN
Territory:     US
Output type:   PDF
[10/7/08 10:20:07 PM] [UNEXPECTED] [1900472:RT5709301]
java.io.FileNotFoundException:
/appslog/fin_top/utl/fintest/out/xdosCfJu5EHsa100708_1020071837.fo (No such
file or directory)

at java.io.FileOutputStream.open(Native Method)
at java.io.FileOutputStream.<init>(FileOutputStream.java:179)
at java.io.FileOutputStream.<init>(FileOutputStream.java:131)
@ at oracle.apps.xdo.common.tmp.TmpFile.createTmpFileJDK118(TmpFile.java:146)
@ at oracle.apps.xdo.common.tmp.TmpFile.createTmpFile(TmpFile.java:113)
at oracle.apps.xdo.template.fo.util.FOUtility.generateFO(FOUtility.java:987)
at oracle.apps.xdo.template.fo.util.FOUtility.generateFO(FOUtility.java:212)
at oracle.apps.xdo.template.FOProcessor.createFO(FOProcessor.java:1657)
at oracle.apps.xdo.template.FOProcessor.generate(FOProcessor.java:967)

So the path /appslog/fin_top/utl/fintest/out/ is not correct. As per the note ID 428855.1 changed the path to valid location

Navigate the following as the responsibility “XML Publisher Administrator”
Select Administration on the Personal Home Page
Under the Configuration tab select Properties and then General.
From there the Temporary directory will be displayed.

Changed the path to some thing that is existing physically.

After that bounce the output post processor. This should resolve the issue.

MWA Service Management – Brief

MWA Service management – Brief

MWA is mobile web application. Using this we can have our applicaiton on mobile. MWA service is used in E-Business suite is for running the applicaiton on mobile. There are very few parameters that needs to be configured for this and we can start MWA service to be used on our mobile.

If we see context file we have a parameter named s_mwastatus which has to be enabled in order to use this service.

(oa_service_status oa_var=”s_mwastatus”)enabled(/oa_service_status)

Other then this, there will be a MWA port on which the service can be started. For ports we have two configuration, one is the telnetl port where we can start the service. If we have multiple telnet ports then we can have a dispatcher port which can load balance the telnet ports.

You can find the telnet port entry in context file as given below.

(mwaTelnetPortNo oa_var=”s_mwaTelnetPortNo” oa_type=”DUP_PORT” base=”10200 step=”6 range=”6 increment=”2 separator=”,” showall=”true” label=”MCSA Telnet Server Port”)10290,10292,10294(/mwaTelnetPortNo)

In E-Business suit we have the configuration file for MWA located at below path

$INST_TOP/admin/install/mwa.cfg

The important configuration setting in thie configuration file is as give below.

DBC file path
mwa.DbcFolder=(Path to $FND_SECURE)

telnet port
mwa.TelnetPortNumber=10290,10292,10294

Dispatcher port
mwa.Dispatcher=ap6003rems.us.oracle.com:10845

Log directory path
mwa.logdir= (Log dir path)

Log file name
mwa.SystemLog=system.log
The above parameters are self explainatory. Also some explaination is provided in config file.

You can use any port which is greated then 1024 and that is not used by any other service and you can start your service on that port. But make sure you make the same port entry in context file and mwa.cfg, else when autoconfig runs it will update mwa.cfg with the port number form context file and your service may not run.

You can start the services by going to $INST_TOP/admin/scripts and running following command

For Telnet port: mwactl.sh start (port number) &

For Dispatcher port: mwactl.sh start (port number) &

MWA URL will be
telnet http://(server name):(telnet port)
telnet http://(server name):(dispatcher port)

Example: telnet://ap6003rems.us.oracle.com:10845

You can use either telnet port or dispatcher port to log into the application. if dispatcher port is used, it will do load balancing.

Error while opening log files from CM form

Some times when we make a concurrent request and try to view the log file after complition of request (may be completed normal or some error) from the concurrent manager form we get following type of error

An error occurred while attempting to establish an Applications File Server connection with the node FNDFS_(Your env_name)_(Your_Server_name). There may be a network configuration problem, or the TNS listener on node FNDFS_(Your env_name)_(Your_Server_name) may not be running. Please contact your system administrator.

In this case you need to set one profile which will fix this issue. You need to set the value for RRA: Service Prefix to FNDFS_(Your_Env_name)_.

This can be done using navigation Profiles –> system.

concurrent manager is not responding

If the concurrent manager is not responding and there are lot of pending request from long time then those request can be killed from the backend using the following query.

UPDATE fnd_concurrent_requests
SET phase_code = ‘C’, status_code = ‘D’
WHERE status_code =’Q’ OR phase_code = ‘P’
AND REQUEST_ID = ;

You can kill all the long running requests.
Also one more option that can be done is login as apps/apps. Also we can make the internal manager to work like a CRM by setting the profile option
Internal Manager: work like CRM to yes.

Concurrent: Use ICM is the profile name

If u r getting Target node/queue unavailable in status column then in the manager –> define form check the Conflict resolution manager node. It should be set to correct node value. Set it to correct value and bounce the CM. By default this value is never set. It will take the node from FND_NODE table.

ORA-20100:

Problem Statement
Error Messages:
CSTPACIN.COST_INV_TXN: (70): ORA-20100: File o0086078.tmp creation for FND_FILE failed. You will find more information on the cause of the error in request logSolution
In this case we need to check following 3 things

1) Check the UTL file parameter in DB side. Compare it with the APPLPTMP parameter on application side. Both this should match.

2) The file path mentioned in these 2 parameters should contain the space and permissions.

3) Check also the parameter APPLTMP

Please note that, following parameters should be in synch.

· APPLTMP
· APPLPTMP
· FORMS60_OUTPUT
· REPORTS60_TMP
· TMPDIR
· TMP

Set the same path correctly for all these variables in context file (APPL_TOP/admin/.xml) and run autoconfig. This will resolve the issue.

ORA-12547: TNS:lost contact

Some time we come across the issue when trying to connect with database from the application side using apps as a user. This basically requires the listener to be up to make connection with database. We get error similar to
Cannot login to sqlplus using following userid/passwd.

sqlplus apps/apps@dbname.

Gives error
ERROR:
ORA-12547: TNS:lost contact
———————————————————
Error Messages:
ERROR:
ORA-12547: TNS:lost contact
———————————————————

Solution:
In this case first check if the database listener is up or now. If the listener is up then check the sqlnet.ora file present in ORACLE_HOME/network/admin also can be reached using the environment variable TNS_ADMIN. This file contains 2 parameters, which restricts login to the database from the other hosts.

tcp.validnode_checking = yes

tcp.invited_nodes=(hostname1, hostname2, … )

This will allow entry from hostname1, hostname2 servers only. So if these values are commented and listener is bounced then this will resolve the issue.