Start of AutoUpgrade session

************* Start of AutoUpgrade session *************

AutoUpgrade version: 11.5.0
AutoUpgrade started at: Thu Mar 14 2008 10:33:40


APPL_TOP is set to /u01/11i/uat/uatappl

NLS_LANG value from the environment is : American_America.WE8ISO8859P1
NLS_LANG value for this AD utility run is : AMERICAN_AMERICA.WE8ISO8859P1

It is critical that your Oracle Applications, RDBMS and related tools are
compatible and certified combinations. If you are uncertain whether a
combination is certified please contact Oracle Support Services.

Are you certain you are running a certified release combination [No] ? Yes

You can be notified by email if a failure occurs.
Do you wish to activate this feature [No] ? No

Please enter the batchsize [1000] : 1000


Please enter the name of the Oracle Applications System that this
APPL_TOP belongs to.

The Applications System name must be unique across all Oracle
Applications Systems at your site, must be from 1 to 30 characters
long, may only contain alphanumeric and underscore characters,
and must start with a letter.

Sample Applications System names are: "prod", "test", "demo" and
"Development_2".

Applications System Name [uat] : uat_sys1



NOTE: If you do not have or choose not to have certain types of files installed
in this APPL_TOP, you may not be able to perform certain tasks.

Example 1: If you don't have files used for installing or upgrading
the database installed in this area, you cannot install or upgrade
the database from this APPL_TOP.

Example 2: If you don't have forms files installed in this area, you cannot
generate them or run them from this APPL_TOP.

Example 3: If you don't have concurrent program files installed in this area,
you cannot relink concurrent programs or generate reports from this APPL_TOP.


Do you currently have or want to install files used for installing or upgrading
the database in this APPL_TOP [YES] ? YES


Do you currently have or want to install Java and HTML files for HTML-based
functionality in this APPL_TOP [YES] ? YES


Do you currently have or want to install Oracle Applications forms files
in this APPL_TOP [YES] ? YES


Do you currently have or want to install concurrent program files
in this APPL_TOP [YES] ? YES



Please enter the name Oracle Applications will use to identify this APPL_TOP.

The APPL_TOP name you select must be unique within an Oracle Applications
System, must be from 1 to 30 characters long, may only contain
alphanumeric and underscore characters, and must start with a letter.

Sample APPL_TOP Names are: "prod_all", "demo3_forms2", and "forms1".

APPL_TOP Name [zeus] : uat_sys1_appltop



You are about to install or upgrade Oracle Applications product tables
in your ORACLE database 'uat'
using ORACLE executables in '/u01/11i/uat/uatora/8.0.6'.

Is this the correct database [Yes] ? Yes

AutoUpgrade needs the password for your 'SYSTEM' ORACLE schema
in order to determine your installation configuration.

Enter the password for your 'SYSTEM' ORACLE schema: *****


Connecting to SYSTEM......Connected successfully.

There exists one FND_PRODUCT_INSTALLATIONS table.
AutoUpgrade will upgrade the existing product group.

The ORACLE username specified below for Application Object Library
uniquely identifies your existing product group: APPLSYS

Enter the ORACLE password of Application Object Library [APPS] : *****

AutoUpgrade is verifying your username/password.
Connecting to APPLSYS......Connected successfully.

The status of various features in this run of AutoUpgrade is:

<-Feature version in->
Feature Active? APPLTOP Data model Flags
------------------------------ ------- -------- ----------- -----------
CHECKFILE No 1 -1 Y N N Y N N
PREREQ No 6 -1 Y N N Y N N
CONCURRENT_SESSIONS No 2 -1 Y Y N Y Y N
PATCH_TIMING No 2 -1 Y N N Y N N
PATCH_HIST_IN_DB No 6 -1 Y N N Y N N
SCHEMA_SWAP No 1 -1 Y N N Y Y N



Connecting to SYSTEM......Connected successfully.

Connecting to APPLSYS......Connected successfully.

Identifier for the current session is 1

Reading product information from file...

Reading language and territory information from file...

Reading language information from applUS.txt ...

Reading database to see what industry is currently installed.


Oracle Applications is currently installed for Commercial or for-profit use.

Do you wish to:
1) Continue to use Oracle Applications for Commercial or for-profit use.
2) Convert Oracle Applications to government, education or
not-for-profit use

Enter your choice [1] : 1



Reading FND_LANGUAGES to see what is currently installed.
Currently, the following languages are installed:

Code Language Status
---- --------------------------------------- ---------
US American English Base
ESA Latin American Spanish Install


Reading language information from applESA.txt ...

Your base language will be AMERICAN.

Your other languages to install are: LATIN AMERICAN SPANISH

Setting up module information.
Reading database for information about the modules.
Saving module information.
Reading database for information about the products.
Connecting to SYSTEM......Connected successfully.

Connecting to APPLSYS......Connected successfully.

Connecting to SYSTEM......Connected successfully.

Reading database for information about how products depend on each other.
Connecting to APPLSYS......Connected successfully.

Reading topfile.txt ...

Saving product information.
Connecting to SYSTEM......Connected successfully.

Connecting to APPLSYS......Connected successfully.

Connecting to APPS......Connected successfully.

Connecting to SYSTEM......Connected successfully.

Saving task information.

AutoUpgrade Main Menu
--------------------------------------------------

1. Choose database parameters

2. Choose overall tasks and their parameters

3. Run the selected tasks

4. Exit AutoUpgrade

* Please use License Manager to license additional
* products or modules after the upgrade is complete.


Enter your choice : 1

AutoUpgrade - Choose database parameters

- O - - S - --- M --- --- I --- --- D ---
Product Action ORACLE Sizing Main Index Default
# Name | User ID Factor Tablespace Tablespace Tablespace
-- ---------------------- - ------- ------ ---------- ---------- ----------
1 Application Object Lib U APPLSYS 100 AOLD AOLX AOLD
2 Application Utilities U APPLSYS 0 AOLD AOLX AOLD
3 Applications DBA U APPLSYS 0 AOLD AOLX AOLD
4 Alert U APPLSYS 100 AOLD AOLX AOLD
5 Global Accounting Engi I AX 100 AXD AXX AXD
6 Common Modules-AK I AK 100 AKD AKX AKD
7 Subledger Accounting S XLA 100 XLAD XLAX XLAD
8 General Ledger U GL 100 GLD GLX GLD

There are 209 Oracle Applications. Enter U/D to scroll up/down.


- To change a database parameter for a product;
INCLUDE the LETTER ABOVE the COLUMN you want to change
U / D / T / B - Press up/down/top/bottom to see other products
[Return] - To return to the AutoUpgrade Main Menu

Enter your choice (for example, 1M) :

AutoUpgrade error:

APPLSYSD
APPLSYSX

The above tablespaces do not exist in your database.
Please either create the missing tablespaces or change your
tablespace selection to exclude these tablespaces.

Review the messages above, then press [Return] to continue.

AutoUpgrade - Choose database parameters

- O - - S - --- M --- --- I --- --- D ---
Product Action ORACLE Sizing Main Index Default
# Name | User ID Factor Tablespace Tablespace Tablespace
-- ---------------------- - ------- ------ ---------- ---------- ----------
39 Applications Shared Te S APPLSYS 100 APPLSYSD APPLSYSX AOLD
40 Enterprise Asset Manag EAM 100 EAMD EAMX EAMD
41 Transportation Executi FTE 100 FTED FTEX FTED
42 Public Sector Financia IGI 100 IGID IGIX IGID
43 Internet Procurement E ITG 100 ITGD ITGX ITGD
44 Inventory Optimization MSR 100 MSRD MSRX MSRD
45 Product Development IPD 100 IPDD IPDX IPDD
46 Product Development In ENI 100 ENID ENIX ENID

There are 209 Oracle Applications. Enter U/D to scroll up/down.


- To change a database parameter for a product;
INCLUDE the LETTER ABOVE the COLUMN you want to change
U / D / T / B - Press up/down/top/bottom to see other products
[Return] - To return to the AutoUpgrade Main Menu

Enter your choice (for example, 1M) : 39M

Enter the ORACLE tablespace name where you wish to store
Applications Shared Technology tables in ORACLE username APPLSYS [APPLSYSD] : AOLD

Connecting to APPLSYS......Connected successfully.

AutoUpgrade - Choose database parameters

- O - - S - --- M --- --- I --- --- D ---
Product Action ORACLE Sizing Main Index Default
# Name | User ID Factor Tablespace Tablespace Tablespace
-- ---------------------- - ------- ------ ---------- ---------- ----------
33 Report Manager FRM 100 FRMD FRMX FRMD
34 Activity Based Managem ABM 100 ABMD ABMX ABMD
35 Balanced Scorecard BSC 100 BSCD BSCX BSCD
36 SEM Exchange EAA 100 EAAD EAAX EAAD
37 Value Based Management EVM 100 EVMD EVMX EVMD
38 Strategic Enterprise M FEM 100 FEMD FEMX FEMD
39 Applications Shared Te S APPLSYS 100 AOLD APPLSYSX AOLD
40 Enterprise Asset Manag EAM 100 EAMD EAMX EAMD

There are 209 Oracle Applications. Enter U/D to scroll up/down.


- To change a database parameter for a product;
INCLUDE the LETTER ABOVE the COLUMN you want to change
U / D / T / B - Press up/down/top/bottom to see other products
[Return] - To return to the AutoUpgrade Main Menu

Enter your choice (for example, 1M) : 39I

Enter the ORACLE tablespace name where you wish to store
Applications Shared Technology indexes in ORACLE username APPLSYS [APPLSYSX] : AOLX


Connecting to SYSTEM......Connected successfully.

Connecting to APPLSYS......Connected successfully.

AutoUpgrade - Choose database parameters

- O - - S - --- M --- --- I --- --- D ---
Product Action ORACLE Sizing Main Index Default
# Name | User ID Factor Tablespace Tablespace Tablespace
-- ---------------------- - ------- ------ ---------- ---------- ----------
33 Report Manager FRM 100 FRMD FRMX FRMD
34 Activity Based Managem ABM 100 ABMD ABMX ABMD
35 Balanced Scorecard BSC 100 BSCD BSCX BSCD
36 SEM Exchange EAA 100 EAAD EAAX EAAD
37 Value Based Management EVM 100 EVMD EVMX EVMD
38 Strategic Enterprise M FEM 100 FEMD FEMX FEMD
39 Applications Shared Te S APPLSYS 100 AOLD AOLX AOLD
40 Enterprise Asset Manag EAM 100 EAMD EAMX EAMD

There are 209 Oracle Applications. Enter U/D to scroll up/down.


- To change a database parameter for a product;
INCLUDE the LETTER ABOVE the COLUMN you want to change
U / D / T / B - Press up/down/top/bottom to see other products
[Return] - To return to the AutoUpgrade Main Menu

Enter your choice (for example, 1M) :

Connecting to SYSTEM......Connected successfully.

Saving your choices...
Done.

Backing up restart files, if any......Done.


AutoUpgrade Main Menu
--------------------------------------------------

1. Choose database parameters

2. Choose overall tasks and their parameters

3. Run the selected tasks

4. Exit AutoUpgrade

* Please use License Manager to license additional
* products or modules after the upgrade is complete.


Enter your choice : 2

AutoUpgrade - Choose overall tasks and their parameters

# Task Do it? Parameters
-- ------------------------------------------ ------ --------------------
1 Verify files necessary for upgrade YES
2 Install or upgrade database objects YES


There are 2 tasks. Enter U/D to scroll up/down.

- To change YES to NO or NO to YES
(You cannot change a task marked with a *)
P - To change the parameters of a task
U / D - To page up/down to see other tasks
[Return] - To return to the AutoUpgrade Main Menu

Enter your choice (for example 2 or 2P) :

Saving your choices...
Done.

Backing up restart files, if any......Done.


AutoUpgrade Main Menu
--------------------------------------------------

1. Choose database parameters

2. Choose overall tasks and their parameters

3. Run the selected tasks

4. Exit AutoUpgrade


Enter your choice : 3

Connecting to APPLSYS......Connected successfully.

Connecting to SYSTEM......Connected successfully.

Connecting to APPS......Connected successfully.


AD utilities can support a maximum of 999 workers. Your
current database configuration supports a maximum of 992 workers.
Oracle recommends that you use between 24 and 48 workers.


Enter the number of workers [24] : 24

Oracle 9i to 10gR2 Manual UPGRADE Steps

Steps for Upgrading the Database to 10g Release 2

Preparing to Upgrade

Fresh Install oracle software only 10gR2 on the same 11i instance.

Oracle 9i(9.2.0.6) to Oracle 10g(10.2.0.1)

Oracle 9i home ==> /oracle/app/oracle/testdb/9.2.0

Oracle 10g home ==> /u01/app/oracle/product/10.2.0/

mkdir -p /u01/app/oracle
chown -R oracle:oinstall /u01

export ORACLE_BASE=/oracle/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
export PATH=$PATH:$ORACLE_HOME/bin


kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default=262144
net.core.rmem_max=262144
net.core.wmem_default=262144
net.core.wmem_max=262144



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

Step 1

Copy utlu102i.sql , utltzuv2.sql 10g oracle home to /tmp folder. Then run both scripts.
This scripts will show the preupgrade steps.

ORACLE_HOME ==> 10g Home

cp $ORACLE_HOME/rdbms/admin/utlu102i.sql /tmp
cp $ORACLE_HOME/rdbms/admin/utltzuv2.sql /tmp
===================================

Step 2

Then login 9i oracle home and login sql prompt. Then run that above scripts.

sqlplus '/as sysdba'

SQL> spool Database_Info.log
SQL> @utlu102i.sql
SQL> spool off

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

Check the log file and solve that issues.

spool Database_Info.log

Oracle Database 10.2 Upgrade Information Utility 04-23-2008 11:07:05
.
**********************************************************************
Database:
**********************************************************************
--> name: TEST
--> version: 9.2.0.6.0
--> compatible: 9.2.0
.
**********************************************************************
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: 8082 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 58 MB
--> APPS_TS_QUEUES tablespace is adequate for the upgrade.
.... minimum required size: 577 MB
--> APPS_TS_TX_DATA tablespace is adequate for the upgrade.
.... minimum required size: 10842 MB
--> ODM tablespace is adequate for the upgrade.
.... minimum required size: 14 MB
--> OLAP tablespace is adequate for the upgrade.
.... minimum required size: 30 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 109 MB
.
**********************************************************************
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
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
.
**********************************************************************
Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No deprecated parameters found. No changes are required.
.
**********************************************************************
Obsolete Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
--> "optimizer_max_permutations"
--> "row_locking"
--> "undo_suppress_errors"
--> "max_enabled_roles"
--> "enqueue_resources"
--> "sql_trace"
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> JServer JAVA Virtual Machine [upgrade] VALID
...The 'JServer JAVA Virtual Machine' JAccelerator (NCOMP)
...is required to be installed from the 10g Companion CD.
--> Oracle XDK for Java [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [install]
--> Real Application Clusters [upgrade] INVALID
--> Oracle Data Mining [upgrade] VALID
--> OLAP Analytic Workspace [upgrade] UPGRADED
--> OLAP Catalog [upgrade] VALID
--> Oracle OLAP API [upgrade] UPGRADED
--> Oracle interMedia [upgrade] VALID
...The 'Oracle interMedia Image Accelerator' is
...required to be installed from the 10g Companion CD.
--> Spatial [upgrade] VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Passwords exist in some database links.
.... Passwords will be encrypted during the upgrade.
.... Downgrade of database links with passwords is not supported.
WARNING: --> Deprecated CONNECT role granted to some user/roles.
.... CONNECT role after upgrade has only CREATE SESSION privilege.
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
.... ODM
.
**********************************************************************
SYSAUX Tablespace:
[Create tablespace in the Oracle Database 10.2 environment]
**********************************************************************
WARNING: SYSAUX tablespace is present.
.... Minimum required size for database upgrade:500 MB
.... Online
.... Permanent
.... Readwrite
.... ExtentManagementLocal
.... SegmentSpaceManagementAuto
.

=======================================
Step 3

Check the above output file and resolve the warning and failed messages
=======================================

Increase the SYSTEM tablespace

select sum(bytes/1024/1024) from dba_free_space where tablespace_name='SYSTEM';

select FILE_NAME, sum(bytes/1024/1024) from dba_data_files where TABLESPACE_NAME='SYSTEM' GROUP BY FILE_NAME;

ALTER TABLESPACE SYSTEM ADD DATAFILE '/oracle/app/oracle/testdata/sys8.dbf' SIZE 4096M;

ALTER TABLESPACE SYSTEM ADD DATAFILE '/oracle/app/oracle/testdata/sys9.dbf' SIZE 4096M;

ALTER TABLESPACE SYSTEM ADD DATAFILE '/oracle/app/oracle/testdata/sys10.dbf' SIZE 4096M;


TEMP

alter database tempfile '/oracle/app/oracle/testdata/tmp1.dbf' resize 4096M;

APPS_TS_QUEUES

select sum(bytes/1024/1024) from dba_free_space where tablespace_name='APPS_TS_QUEUES';

select FILE_NAME, sum(bytes/1024/1024) from dba_data_files where TABLESPACE_NAME='APPS_TS_QUEUES' GROUP BY FILE_NAME;

ALTER TABLESPACE APPS_TS_QUEUES ADD DATAFILE '/oracle/app/oracle/testdata/queues3.dbf' SIZE 1024M;


APPS_TS_TX_DATA

select sum(bytes/1024/1024) from dba_free_space where tablespace_name='APPS_TS_TX_DATA';

select FILE_NAME, sum(bytes/1024/1024) from dba_data_files where TABLESPACE_NAME='APPS_TS_TX_DATA' GROUP BY FILE_NAME;

ALTER TABLESPACE APPS_TS_TX_DATA ADD DATAFILE '/oracle/app/oracle/testdata/tx_data12.dbf' SIZE 4096M;

ALTER TABLESPACE APPS_TS_TX_DATA ADD DATAFILE '/oracle/app/oracle/testdata/tx_data13.dbf' SIZE 4096M;

ALTER TABLESPACE APPS_TS_TX_DATA ADD DATAFILE '/oracle/app/oracle/testdata/tx_data14.dbf' SIZE 1024M;

ODM

select sum(bytes/1024/1024) from dba_free_space where tablespace_name='ODM';

select FILE_NAME, sum(bytes/1024/1024) from dba_data_files where TABLESPACE_NAME='ODM' GROUP BY FILE_NAME;

alter database datafile '/oracle/app/oracle/testdata/odm.dbf' resize 250m;

OLAP

select sum(bytes/1024/1024) from dba_free_space where tablespace_name='OLAP';

select FILE_NAME, sum(bytes/1024/1024) from dba_data_files where TABLESPACE_NAME='OLAP' GROUP BY FILE_NAME;

alter database datafile '/oracle/app/oracle/testdata/olap.dbf' resize 250m;

CREATE TABLESPACE sysaux DATAFILE '/oracle/app/oracle/testdata/sysaux01.dbf'
SIZE 500M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;


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

Step 4

Check for the TIMESTAMP WITH TIMEZONE Datatype.

SQL> @utltzuv2.sql

DROP TABLE sys.sys_tzuv2_temptab
*
ERROR at line 1:
ORA-00942: table or view does not exist



Table created.

Query sys.sys_tzuv2_temptab Table to see if any TIMEZONE data is affected by
version 2 transition rules

PL/SQL procedure successfully completed.


Commit complete.

=============================================
Step 5

To gather statistics run this script, connect to the database AS SYSDBA using SQL*Plus.


SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS');
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('ODM');
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('OLAPSYS');
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('MDSYS');


.... SYS
.... ODM
.... OLAPSYS
.... MDSYS
==================================================

Step 6

REVOKE CONNECT RIGHTS TO ABOVE 12 USERS

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');


GRANTEE
=======
CFD
DMS
HCC
DGRAY
EUL_US
SSOSDK
WEBSYS
PROJMFG
SERVICES
WIRELESS
EDWEUL_US

GRANTEE
------------------------------
MOBILEADMIN

12 rows selected.
============================================

SELECT 'REVOKE CONNECT FROM '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');


Take the spool on above scripts and run sql prompt.

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

Remove or comment the following initparameters 10 oracle home.

Copy init.ora file 9i to 10g oracle home, Then change the parameter file.

-->"optimizer_max_permutations"
--> "row_locking"
--> "undo_suppress_errors"
--> "max_enabled_roles"
--> "enqueue_resources"
--> "sql_trace"

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

Check the spool log file to Add and Increase the below parameter size

shared_pool_size=181217280
streams_pool_size=50331648
large_pool_size=8388608


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

Check the free tablespace size

select tablespace_name,sum(bytes/1024/1024) from dba_free_space where tablespace_name in ('SYSTEM','APPS_TS_QUEUES','APPS_TS_TX_DATA','ODM','SYSAUX') GROUP BY TABLESPACE_NAME;

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

set below env

export ORACLE_SID=TEST
export ORACLE_BASE=/u01/app/
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
export PATH=$PATH:$ORACLE_HOME/bin

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

After completing pre upgrade steps, you have to login 10g oracle home.

[oracle@sys43 admin]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Apr 23 14:48:39 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup upgrade
ORACLE instance started.


Total System Global Area 473956352 bytes
Fixed Size 1220024 bytes
Variable Size 297796168 bytes
Database Buffers 163577856 bytes
Redo Buffers 11362304 bytes
Database mounted.
Database opened.

SQL> spool upgrade.log
SQL> @catupgrd.sql



Oracle Database 10.2 Upgrade Status Utility 04-23-2008 16:23:48
.
Component Status Version HH:MM:SS
Oracle Database Server VALID 10.2.0.1.0 00:48:24
JServer JAVA Virtual Machine VALID 10.2.0.1.0 00:06:07
Oracle XDK VALID 10.2.0.1.0 00:06:03
Oracle Database Java Packages VALID 10.2.0.1.0 00:00:25
Oracle Text VALID 10.2.0.1.0 00:01:08
Oracle XML Database VALID 10.2.0.1.0 00:01:30
Oracle Real Application Clusters INVALID 10.2.0.1.0 00:00:02
Oracle Data Mining VALID 10.2.0.1.0 00:00:19
OLAP Analytic Workspace VALID 10.2.0.1.0 00:00:16
OLAP Catalog VALID 10.2.0.1.0 00:01:13
Oracle OLAP API VALID 10.2.0.1.0 00:00:38
Oracle interMedia VALID 10.2.0.1.0 00:05:22
Spatial INVALID 10.2.0.1.0 00:04:21
.
Total Upgrade Time: 01:31:00
========================================
spool invalid_pre.lst
select substr(owner,1,12) owner,
substr(object_name,1,30) object,
substr(object_type,1,30) type, status from
dba_objects where status <> 'VALID';
spool off

========================================
SQL>shutdown immediate

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

The 9idata directory has no files
so go to following folder and run that perl scripts.
/u01/11i/uat/oracle/uatdb/10.2.0/nls/data/old/cr9idata.pl


bash-2.05b$ perl cr9idata.pl Creating directory /u01/11i/uat/oracle/uatdb/10.2.0/nls/data/9idata ... Copying files to /u01/11i/uat/oracle/uatdb/10.2.0/nls/data/9idata... Copy finished. Please reset environment variable ORA_NLS10 to /u01/11i/uat/oracle/uatdb/10.2.0/nls/data/9idata! bash-2.05b$
============================
Then normal startup

SQL>startup
============================

Compile invalid objects

SQL> @utlrp.sql

========================================
SQL> shut immediate

cp env to oracle_home_10g and edit new values

Copy tnsnames.ora, listeners.ora and sqlnet.ora 9i to new 10g orale home.
cp tns_admin(9i) to oracle 10g

Start listener
================================================

SQL>startup


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

SymptomsIn 10gR2, setting the environment variable ORA_NLS10 causes the following error:ERROROra-12705: cannot access nls data files or invalid environment specified ora-127
This is script cr9idata.pl located following path.

/u01/11i/uat/oracle/uatdb/10.2.0/nls/data/old

bash-2.05b$ perl cr9idata.pl

Creating directory /u01/11i/uat/oracle/uatdb/10.2.0/nls/data/9idata ...Copying files to /u01/11i/uat/oracle/uatdb/10.2.0/nls/data/9idata...Copy finished.

Please reset environment variable ORA_NLS10 to /u01/11i/uat/oracle/uatdb/10.2.0/nls/data/9idata!
===========================================================
Implement and run Autoconfig on the new Database home

1. Copy AutoConfig to the RDBMS ORACLE_HOME

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

Steps:
* On the Application Tier (as the APPLMGR user):

a) Log in to the APPL_TOP environment and source the APPSORA.env file

b) Create appsutil.zip file. This will create appsutil.zip in $APPL_TOP/admin/out


perl $AD_TOP/bin/admkappsutil.pl

bash-2.05b$ perl admkappsutil.pl

Starting the generation of appsutil.zip
Log file located at /progs2/11i/uat/applmgr/uatappl/admin/log/MakeAppsUtil_09051242.log
output located at /progs2/11i/uat/applmgr/uatappl/admin/out/appsutil.zip
MakeAppsUtil completed successfully.


c) Copy or FTP the appsutil.zip file to the

* On the Database Tier (as the APPLMGR or ORACLE user):

d) cd RDBMS ORACLE_HOME

e) Source 10g CONTEXT_NAME.env file

f) unzip -o appsutil.zip


2. Generate your Database Context File. Execute the following commands to create your Database Context File:

Steps:

a) cd RDBMS ORACLE_HOME

b) CONTEXT_NAME.env

c) cd <10.1.0>/appsutil/bin

d) perl adbldxml.pl tier=db appsuser=APPSuser appspasswd=APPSpwd

perl adbldxml.pl tier=db appsuser=apps appspasswd=xxxxx


Steps:
a) cd /appsutil/bin

b) adconfig.sh contextfile=CONTEXT.XML appspass=APPSpwd




References

Complete Checklist for Manual Upgrades to 10gR2
Doc ID: Note:316889.1
Oracle Applications Release 11i with Oracle 10g Release 2 (10.2.0)
Doc ID: Note:362203.1

Note 135090.1 - Managing Rollback/Undo Segments in AUM (Automatic Undo Management)
Note 159657.1 - Complete Upgrade Checklist for Manual Upgrades from 8.X / 9.0.1 to Oracle9iR2 (9.2.0)
Note 170282.1 - PLSQL_V2_COMPATIBLITY=TRUE causes STANDARD and DBMS_STANDARD to Error at Compile
Note 263809.1 - Complete checklist for manual upgrades to 10gR1 (10.1.0.x)
Note 293658.1 - 10.1 or 10.2 Patchset Install Getting ORA-29558 JAccelerator (NCOMP) And ORA-06512
Note 316900.1 - ALERT: Oracle 10g Release 2 (10.2) Support Status and Alerts
Note 356082.1 - ORA-7445 [qmeLoadMetadata()+452] During 10.1 to 10.2 Upgrade
Note 406472.1 - Mandatory Patch 5752399 for 10.2.0.3 on Solaris 64-bit and Filesystems Managed By Veritas or Solstice Disk Suite software
Note 407031.1 - ORA-01403 no data found while running utlu102i.sql/utlu102x.sql on 8174 database
Note 412271.1 - ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While Upgrading Or Patching Databases To 10.2.0.3
Note 465951.1 - ORA-600 [kcbvmap_1] or Ora-600 [Kcliarq_2] On Startup Upgrade Moving From a 32-Bit To 64-Bit Release
Note 466181.1 - 10g Upgrade Companion
Note 471479.1 - IOT Corruptions After Upgrade from COMPATIBLE <= 9.2 to COMPATIBLE >= 10.1
Note 557242.1 - Upgrade Gives Ora-29558 Error Despite of JAccelerator Has Been Installed
Oracle Database Upgrade Guide 10g Release 2 (10.2) Part Number B14238-01
http://download.oracle.com/docs/cd/B19306_01/server.102/b14238/toc.htm

ORACLE DATABASE PLATFORM LEVEL PATCHSET


ORACLE RDMBS VERSIONS AND YEAR OF RELEASE




Oracle Database Patchset Overview


Oracle Database Migration From 8.0.6 to 9i

1. --------------------------------------------------------

Upgrade path for Oracle 8 (8.0.x): If your old release version is 8.0.5 or less (i.e 8.0.4 or 8.0.3), then direct upgrade is
NOT supported. You must first upgrade this version to 8.0.6. After the upgrade to 8.0.6 or your version IS 8.0.6 , you can
directly upgrade your database to Oracle9i Rel2.

What version is running? What option is installed?

Select * from v$version;
Select * from v$option;

2. ---------------------------------------------------------

PERFORM a Full cold backup!!!!!!!

3. ---------------------------------------------------------

Avoid running out of space during the migration:

- Prepare the system rollback segment:

Alter rollback segment system storage (maxextents 121 next 1M);

- Ensure plenty of free space in the SYSTEM tablespace. A minimum of 150 Mb additional free space:

Select max(bytes) from dba_free_space where tablespace_name='SYSTEM';

- Ensure plenty of free space in the ROLLBACK tablespace. Ensure that you have at least 1 rollback segment of 70 Mb if the
number of objects in the database exceeds 5000:

Select count(*) from dba_objects;

4. ------------------------------------------------------------

Verify the certification of oracle 9i on the OS version you are using. Verify all necessary OS patches are installed. Example
for Solaris:

$ showrev -p

You can also check the installation in Note 169706.1

5. -------------------------------------------------------------

Upgrade will leave all objects (packages,views,...) invalid, except for tables. All other objects must be recompiled manually. List all objects that are not VALID before the upgrade. This list of fatal objects.

Select substr(owner,1,12) owner, substr(object_name,1,30) object, Substr(object_type,1,30) type,status from dba_objects where status <>'VALID';

To create a script to compile all invalid objects, before upgrading, run the the script called utlrp.sql in the
$ORACLE_HOME/rdbms/admin directory.

This script recompiles all invalid PL/SQL in the database including views.

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus sys/ as sysdba
SQL> @utlrp.sql

Run the script and than rerun the query to get invalid objects.

spool invalid_pre.lst
Select substr(owner,1,12) owner,
Substr(object_name,1,30) object,
Substr(object_type,1,30) type, status from
dba_objects where status <>'VALID';
spool off

This last query will return a list of all objects that cannot be recompiled
before the upgrade in the file 'invalid_pre.lst'

There should be not dictionary objects invalid.


6. ---------------------------------------------------------------


Verify the kernel parameters according to the installation guide of the
new version.

Example for Solaris:
$ cat /etc/system


7. -------------------------------------------------------------------


Ensure ORACLE_SID is set to instance you want to upgrade.

Echo $ORACLE_SID
Echo $ORACLE_HOME

8. ------------------------------------------------------------------


For all information regarding the national characterset,

please refer to Note 276914.1

Before proceeding please check the column names involved with Note 278725.1

As of Oracle 9i the National Characterset (NLS_NCHAR_CHARACTERSET)
will be limited to UTF8 and AL16UTF16.

Note 276914.1 The National Character Set in Oracle 9i and 10g

The change itself is done in step 31 by running the upgrade script.

If you are NOT using N-type colums *for user data* then simply go to step 9.
No further action required.

( so if: select distinct OWNER, TABLE_NAME from DBA_TAB_COLUMNS where
DATA_TYPE in ('NCHAR','NVARCHAR2', 'NCLOB') and OWNER not in
('SYS','SYSTEM'); returns no rows, go to point 9.)

select distinct OWNER, TABLE_NAME from DBA_TAB_COLUMNS where
DATA_TYPE in ('CHAR','VARCHAR2', 'CLOB') and OWNER not in
('SYS','SYSTEM');


If you have N-type colums *for user data* then check:

SQL> select * from nls_database_parameters where parameter
='NLS_NCHAR_CHARACTERSET';


9. -----------------------------------------------------------------------


If you are upgrading from the 8.0.6 release check no users or roles called either MIGRATE or OUTLN.

Select * from dba_users where username in ('MIGRATE','OUTLN');
Select * from dba_roles where role in ('MIGRATE','OUTLN');


10. ------------------------------------------------------------------------


Check for corruption in the dictionary, use the following commands in sqlplus
connected as sys:

Set verify off
Set space 0
Set heading off
Set feedback off
Set pages 1000
Spool analyze.sql
Select 'Analyze '||object_type||' '||object_name
||' validate structure;'
from dba_objects
where owner='SYS'
and object_type in ('INDEX','TABLE','CLUSTER');
spool off
This creates a script called analyze.sql.
Run the script.

This script (analyze.sql) should not return any errors.

11. ----------------------------------------------------------------------------

Ensure that all Snapshot refreshes are successfully completed.
And replication is stopped.

$ Sqlplus SYS/

Select distinct(trunc(last_refresh)) from dba_snapshot_refresh_times;


12. ----------------------------------------------------------------------------

Stop the listener for the database

$ lsnrctl

Lsnrctl> stop

13. ----------------------------------------------------------------------------

Ensure no files need media recovery:

$ sqlplus SYS/

Select * from v$recover_file;

This should return no rows

14. ----------------------------------------------------------------------------

Ensure no files are in backup mode:

Select * from v$backup where status!='NOT ACTIVE';

This should return no rows.

15. ----------------------------------------------------------------------------

Resolve any outstanding unresolved distributed transaction:

Select * from dba_2pc_pending;

If this returns rows you should do the following:

Select local_tran_id from dba_2pc_pending;
Execute dbms_transaction.purge_lost_db_entry('');
Commit;


16. ----------------------------------------------------------------------------

Disable all batch and cron jobs.




17. ----------------------------------------------------------------------------

Ensure the users sys and system have 'system' as their default tablespace.

Select username, default_tablespace from dba_users where username
in ('SYS','SYSTEM');

To modify use:
Alter user sys default tablespace SYSTEM;
Alter user system default tablespace SYSTEM;

18. ----------------------------------------------------------------------------

Optionally ensure the aud$ is in the system tablespace when auditing is enabled.

Select tablespace_name from dba_tables where table_name='AUD$';


19. ----------------------------------------------------------------------------

Note down where all control files are located.

Select * from v$controlfile;

20. ----------------------------------------------------------------------------

Note down all sysdba users.

Select * from v$pwfile_users;

If a passwordfile is used copy it to the new location. On Unix the default
is $ORACLE_HOME/dbs/orapw.

On Windows NT this is %ORACLE_HOME%\database\orapw

21. ----------------------------------------------------------------------------

Shutdown the database

$ sqlplus SYS/
SQL> Shutdown immediate

22. ----------------------------------------------------------------------------

Change the init.ora file:

- Make a backup of the init.ora file.
- Verify that the parameter DB_DOMAIN is set properly.
- Ensure that the USER_DUMP_DEST, BACKGROUND_DUMP_DEST and the CORE_DUMP_DEST
are set to an explicit directory
- Set the parameter _SYSTEM_TRIG_ENABLED explicitly to FALSE during the upgrade
- Set the parameter OPTIMIZER_MODE to CHOOSE during the upgrade
- Either leave COMPATIBLE unset in your initialization parameter file or
set COMPATIBLE to 8.1.x. Setting this parameter a lower or a higher value
than 8.1.X results in an error during the upgrade.
- Ensure that the shared_pool_size and the large_pool_size are at least 150Mb


23. ----------------------------------------------------------------------------

Check for adequate freespace on archive log destination file systems.



24. ----------------------------------------------------------------------------

Ensure the NLS_LANG variable is set correctly:
$ echo $NLS_LANG


bash-2.05b$ echo $NLS_LANG
AMERICAN_AMERICA.WE8ISO8859P1

25. ----------------------------------------------------------------------------

If needed copy the listener.ora and the tnsnames.ora to the new location
(when no TNS_ADMIN env. Parameter is used)

cp $ORACLE_HOME/network/admin /network/admin

26. ----------------------------------------------------------------------------
If your Operating system is Windows NT, delete your services
With the ORADIM of your old oracle version.


For Oracle 8.0 this is:
C:\ORADIM80 -DELETE -SID

For Oracle8i or higher this is:
C:\ORADIM -DELETE -SID
And create the new Oracle9i service use ORADIM of the Oracle9i ORACLE_HOME:
C:\ORADIM -NEW -SID -INTPWD -MAXUSERS n
-STARTMODE MANUAL -PFILE %ORACLE_HOME%\DATABASE\init.ora

27. ----------------------------------------------------------------------------

If needed copy the init.ora file to the new oracle_home or
Create a link to the init.ora.

cp $OLD_ORACLE_HOME/dbs/init.ora $NEW_ORACLE_HOME/dbs/init.ora

OR

ln -s /init/ora/file/path/init.ora $ORACLE_HOME/dbs/init.ora

Also check 'ifile' parameters in the init.ora, to be set to the correct file.
if an IFILE is used, verify the above mentioned parameter for the init.ora
and copy this to the correct location. Change the IFILE entry in the init.ora
file when this file changes from location.


28. ----------------------------------------------------------------------------

Update the oratab entry, to set the new ORACLE_HOME and disable automatic
startup:

::N


29. ----------------------------------------------------------------------------

Update the environment variables like ORACLE_HOME and PATH

$ . oraenv

30. ----------------------------------------------------------------------------

Make sure the following enviroment variables point to the new
Release directories:

- ORACLE_HOME
- PATH
- ORA_NLS33
- ORACLE_BASE
- LD_LIBRARY_PATH
- ORACLE_PATH

For HP-UX systems verify the SHLIB_PATH parameter points to the new release
directories.

$ env | grep ORACLE_HOME
$ env | grep PATH
$ env | grep ORA_NLS33
$ env | grep ORACLE_BASE
$ env | grep LD_LIBRARY_PATH
$ env | grep ORACLE_PATH

HP-UX:
$ env | grep SHLIB_PATH


31. ----------------------------------------------------------------------------

Run the upgrade script:
$ cd $ORACLE_HOME/rdbms/admin
Sqlplus /nolog

SQL> connect sys/passwd_for_sys as sysdba


Use Startup MIGRATE when you are upgrading to Oracle 9.2:

SQL> startup migrate

Spool the output so you can take a look at possible errors after the upgrade:

SQL> spool upgrade.log

Run the appropriate script for your version.

From To: Only Script to Run
==== === ==================
8.0.6 9.0.1 u0800060.sql
8.0.6 9.2 u0800060.sql
8.1.5 9.0.1 u0801050.sql
8.1.5 9.2 Not Supported
8.1.6 9.0.1 u0801060.sql
8.1.6 9.2 Not Supported
8.1.7 9.0.1 u0801070.sql
8.1.7 9.2 u0801070.sql
9.0.1 9.2 u0900010.sql

Each of these scripts is a direct upgrade path from the version you are
on to Oracle9i. You do not need to run catalog.sql and catproc.sql as these
two scripts are called from within the upgrade script.

The remainder of this step is only valid for upgrades towards Oracle 9.2:

Display the contents of the component registry to determine which components


SQL> Select comp_name, version, status from dba_registry;

Run the script cmpdbmig.sql to upgrade the components which can be upgrade
with the SYSDBA privilege:

SQL> @cmpdbmig.sql


The components upgraded by this script are:
Jserver JAVAVM, oracle XDK for Java, Oracle 9i RAC, Oracle Data Mining,
OLAP analytical Workspace, Oracle 9i Java Packages, Messaging Gateway,
Oracle Workspace Manager, OLAP Catalog, Oracle Label Security.

Display the components which were upgraded:

SQL> Select comp_name, version, status from dba_registry;

End the spool of the upgrade:

SQL> Spool Off



32. ----------------------------------------------------------------------------

Restart the database:

SQL> Shutdown Immediate (DO NOT USE SHUTDOWN ABORT!!!!!!!!!)

SQL> Startup restrict


Executing this clean shutdown flushes all caches, clears buffers and performs
other database housekeeping tasks. Which is needed if you want to upgrade
specific components.

33. ----------------------------------------------------------------------------

Run script to recompile invalid pl/sql modules:
SQL> @utlrp


If there are still objects which are not valid after running the script run
the following:
spool invalid_post.lst
Select substr(owner,1,12) owner,
Substr(object_name,1,30) object,
Substr(object_type,1,30) type, status from
dba_objects where status <>'VALID';
spool off

Now compare the invalid objects in the file 'invalid_post.lst' with the invalid
objects in the file 'invalid_pre.lst' you create in step 5.

There should be no dictionary objects invalid.

34. ----------------------------------------------------------------------------

Edit init.ora file:

35. ----------------------------------------------------------------------------

Shutdown the database and startup the database.
$ sqlplus /nolog
SQL> Connect sys/passwd_for_sys as sysdba
SQL> Shutdown
SQL> Startup restrict


36. ----------------------------------------------------------------------------

For all information regarding the national characterset,
please refer to Note 276914.1

A) IF you are NOT using N-type colums for *user* data:

select distinct OWNER, TABLE_NAME from DBA_TAB_COLUMNS where
DATA_TYPE in ('NCHAR','NVARCHAR2', 'NCLOB') and OWNER not in
('SYS','SYSTEM');
did not return rows in point 8 of this note.

then simply:
$ sqlplus /nolog
SQL> connect sys/passwd_for_sys as sysdba
SQL> shutdown immediate
and goto step 37.

B) IF your version 8 NLS_NCHAR_CHARACTERSET was UTF8:

you can look up your previous NLS_NCHAR_CHARACTERSET using this select:

select * from nls_database_parameters where parameter ='NLS_SAVED_NCHAR_CS';

then simply:
$ sqlplus /nolog
SQL> connect sys/passwd_for_sys as sysdba
SQL> shutdown immediate
go to step 37.


then the N-type colums *data* need to be converted to AL16UTF16:

To upgrade user tables with N-type colums to AL16UTF16 run the
script utlnchar.sql:


$ sqlplus /nolog
SQL> connect sys/passwd_for_sys as sysdba
SQL> @utlnchar.sql
SQL> shutdown immediate


37. ----------------------------------------------------------------------------

Now edit the init.ora:

- put back the old value for the JOB_QUEUE_PROCESSES parameter
- put back the old value for the AQ_TM_PROCESSES parameter
- If you change the value for NLS_LENGTH_SEMANTICS prior to the upgrade put
the value back to CHAR.
- If you changed the CLUSTER_DATABASE parameter prior the upgrade set it back to
TRUE

38. ----------------------------------------------------------------------------

Startup the database:

SQL> startup
Create a server parameter file with a initialization parameter file

SQL> Create spfile from pfile;

This will create a spfile as a copy of the init.ora file located in the

$ORACLE_HOME/dbs directory.

39. ----------------------------------------------------------------------------

Modify the listener.ora file:

For the upgraded intstance(s) modify the ORACLE_HOME parameter
to point to the new ORACLE_HOME.

40. ----------------------------------------------------------------------------

Start the listener
$ lsnrctl

LSNRCTL> start


41. ----------------------------------------------------------------------------

Enable cron and batch jobs

42. ----------------------------------------------------------------------------

Change oratab entry to use automatic startup
SID:ORACLE_HOME:Y


43. ----------------------------------------------------------------------------

To use the new features in 9i change the compatible parameter to the new release.
When everything is well tested, update the compatible parameter in the init.ora and restart to the new release number.
COMPATIBLE=9.0.X where x is the release number


Doc ID: Note: 159657.1

ENABLING THE ORACLE APPLICATIONS AUDIT FUNCTION

In Oracle Applications auditing can be done at two levels

Oracle Applications Users Level.
Oracle Applications Database/Schema Level.


In addition to the above you could also enable the standard Oracle RDBMS audit feature using the SYS.AUD$ table. In the current post however we will talk about only the auting capabilities of Oracle Applications.

DATABASE AUDITING
CONFIGURING

There are basically two steps required to enable database-level auditing

1. set the audit_trail parameter in init.ora
2. execute audit statements

Step 1 – Set “audit_trail” parameter

In the init.ora file for the instance, set the audit_trail parameter to true. This will enable auditing to the SYS.AUD$ table. Optionally, audit information can be written to the operating system by setting audit_trail parameter to OS. The database must be restarted for this parameter to take effect.

Step 2 – Execute audit statements

Execute the following SQL statements as the SYSTEM user

audit session ---> Session auditing – connects to the database
audit user ---> Create, alter, and drop user
audit database link ---> Create or drop database links
audit public database link ---> Create or drop public database links
audit system audit ---> Audit and noaudit statements

To review the existing system-level audits
SQL> select * from DBA_STMT_AUDIT_OPTS;
SQL> select * from DBA_PRIV_AUDIT_OPTS;


PURGING

The SYS.AUD$ table needs to be purged on a periodic basis, at least every 90 days. The database connection entries can be significant. All rows should be backed up prior to being purged.


APPS AUDITING – END USER ACCESS

Oracle Applications User Auditing

Sign On Audit

In Oracle Applications user level auditing can be done by enabling the Sign Audit profile option.

1. User level
2. Responsibility level
3. Form level


All user signons, responsibility selections, and form accesses will be logged to

APPLSYS.FND_LOGINS
APPLSYS.FND_LOGIN_RESPONSIBILITIES
APPLSYS.FND_LOGIN_RESP_FORMS


APPS AUDITING – END USER ACCESS

Unsuccessful Logins
Unsuccessful password attempts are automatically recorded in the

APPLSYS.FND_UNSUCCESSFUL_LOGINS and ICX.ICX_FAILURES

Concurrent Requests

All concurrent requests are recorded in the FND_CONCURRENT_REQUESTS table. There is no configuration required.

Based upon the level of Sign Auditing selected the System capture all or some of the following information.

• The users who sign on to the system
• The Sign in and Sign Out times
• Responsiblities they choose
• The forms they use
• Duration of using the form or responsibility
• Terminals used for sign on.


We can get this information from the system by running one of the standard Oracle Applications reports which are

• Signon Audit Users
• Signon Audit Responsibilities
• Signon Audit Forms
• Signon Audit Concurrent Requests
• Signon Audit Unsuccessful Logins


Using SQL

The end-user access data is stored in the following tables

APPLSYS.FND_LOGINS
APPLSYS.FND_LOGIN_RESPONSIBILITIES
APPLSYS.FND_LOGIN_RESP_FORMS
FND_CONCURRENT_REQUESTS
APPLSYS.FND_UNSUCCE

PURGING

The end-user access data can be purged using the Purge Signon Audit Data concurrent program. The only parameter is a date which all audit data older this date is purged. This program should be scheduled to be run weekly or monthly and saving at least 30-90 days of data.

The following tables are purged by this program

FND_LOGIN_RESP_FORMS
FND_LOGIN_RESPONSIBILITIES
FND_LOGINS
FND_UNSUCCESSFUL_LOGINS

APPS AUDITING – DATABASE ROW CHANGES

Minimal

FND_AUDIT_GROUPS
FND_AUDIT_SCHEMAS
FND_AUDIT_TABLES
FND_AUDIT_COLUMNS

Standard

ALR_ALERTS

Standard

FND_ORACLE_USERID

Above Average

FND_USER
FND_USER_RESP_GROUPS

High

FND_FORM_FUNCTIONS
FND_MENUS
FND_MENU_ENTRIES
FND_REQUEST_GROUPS
FND_REQUEST_GROUP_UNITS
FND_ENABLED_PLSQL
FND_RESP_FUNCTIONS
FND_CONCURRENT_PROGRAMS
FND_EXECUTABLES
FND_DATA_GROUPS
FND_DATA_GROUP_UNITS

CONFIGURING

Step 1 – Set AuditTrail Profile Option

The System Profile Option AuditTrail:Activate must be set to Yes. Be sure to log out of the applications to activate the profile option in your session.

Step 2 – Select the Audit Installations

As System Administrator, select Security -> AuditTrail -> Install.

 Check all the schemas for which auditing should be enabled. For example, if you want to audit FND_USERS, you would check APPLSYS since the FND_USERS table is in the APPLSYS schema.

 Save your selections.

Step 3 – Create a New Audit Group

 As System Administrator, select Security -> AuditTrail -> Groups.

 Create a new audit group by setting the Application Name to the application that owns the table (e.g., Application Object Library for APPLSYS), the Audit Group to a new name (e.g., My Audits), and Group State should be set to Enable Requested.

 Add the tables to be audited. Columns will be defined in the next step.

 Save the new audit group.

Step 4 – Define Table Columns to be Audited
For each table defined in the above step, define the columns to be audited using these steps –

 As System Administrator, select Security -> AuditTrail -> Tables.

 Query the table name.

 The primary key columns will always be saved. Add the columns that need to be audited. Do not ever add the following columns as user information is automatically added –

Creation Date Created By Last Update Login Last Update Date Last Updated By

 Save the columns.

Step 5 – Run AuditTrail Update Program

Run the AuditTrail Update Tables program to activate the auditing. This program will create a shadow table for each audited table and create triggers on each audited column in the original table. The shadow table will have the same name as the audited table appended with “_A”. Two views will be created for each column with the names “_AC#” and “_AV#” where # is a sequential number.

Troubleshooting

See the Oracle Applications System Administration Manual Chapter 3 for more information on accessing the audit trail information. Metalink Note 105624.1 contains information on troubleshooting AuditTrail issues.

PURGING

The audit trail information should be purged on a periodic basis. There is no standard purge program and the AuditTrail must be manually disabled to permit purging. Use the following procedure to purge audit date –

1. As System Administrator, select Security -> Audit Trail -> Groups

a. Select the “Security Audit” group and set the group state to “Disable – Purge Table”

2. Run the “Audit Trail Update Tables” Report

3. Purge the data from the shadow table

4. Select Security -> Audit Trail -> Groups

a. Select the “Security Audit” group and set the group state to “Enable”

5. Run the “Audit Trail Update Tables” Report

PURGING

The audit trail information should be purged on a periodic basis. There is no standard purge program and the AuditTrail must be manually disabled to permit purging. Use the following procedure to purge audit date

1. As System Administrator, select Security -> Audit Trail -> Groups
a. Select the “Security Audit” group and set the group state to “Disable – Purge Table”
2. Run the “Audit Trail Update Tables” Report
3. Purge the data from the shadow table
4. Select Security -> Audit Trail -> Groups
a. Select the “Security Audit” group and set the group state to “Enable”
5. Run the “Audit Trail Update Tables” Report


Additionally you could also setup notifications for unsuccessful logins by setting the Sign-On: Notification system profile option to YES.

We can also use the monitor users window to have a user level auditing. However you can view the information only of those users for whom sign on auditing has been enabled.

Navigator -> System Administrator -> Security: User -> Monitor -> Query F11 and Ctrl+F11

Oracle Applications Database/Schema Level

Oracle Applications also allows to track down changes made to the data in oracle applications. For this purpose Audit Trails are setup and used. The trails track the audited table by creating a shadow table for each of the audited table. Internally this mechanism uses database triggers to populate the shadow tables. The Following steps are involved for setting up a audit trail in oracle applications.

Define Audit Groups

The first step involves defining your audit group. An audit group is a set of tables and columns that you wish to audit. You can choose either to enable auditing of all or some of the columns of a table except LONG, RAW and LONG RAW. You could have multiple audit groups and a same object can also be a part of different audit groups.



Define Audit Installations

In this stage you choose the registered ORACLE IDs that you wish to audit. Before you can define your audit installation you must have denied your audit groups.

Define Audit Table and Columns

After defining the audit groups and audit installations you can choose the tables and columns for which you wish to enable auditing. You can use the audit tables to do this.

Run the Audit Trail Update Tables Report
You must run a concurrent request Audit Trail Update Tables Report so that your audit trail definitions and the required shadow tables are created. This report is also responsible for building database triggers for your audit installations.

Disabling Auditing

You can disable auditing at anytime by changing the audit group state through the Audit Groups window to either Disable - Interrupt Audit, Disable - Prepare for Archive or Disable - Purge Table. You must run the Audit Trail Update Tables Report after making the selection.


FND_USER

FND_USER_RESPONSIBILITY

FND_USER_RESP_GROUPS

FND_SECURITY_GROUPS

FND_APPLICATION

FND_APPLICATION_TL

FND_RESPONSIBILITY

FND_RESPONSIBILITY_TL

FND_PRODUCT_INSTALLATIONS

WF_LOCAL_USER_ROLES

ICX_SESSIONS


REFERENCES

 Oracle8i Administrator’s Guide – Chapter 24 Auditing Database Use
 Oracle Applications System Administrator’s Guide – Chapter 3 User and Data Auditing
 Metalink Note 105624.1 – Troubleshooting (Audit Trail)
 Metalink Note 60828.1 – Overview of Oracle Applications AuditTrails
 Metalink Note 69660.1 – Understanding Data Auditing in Oracle Application Tables

Create UNDO tablespace in Oracle

In Oracle 8i and below, Rollback Segments provide read consistency and the ability to rollback transactions. In Oracle 9i, Undo segments can be used to provide this functionality. The advantage of using Automatic Undo Management is that it relieves the DBA of manually creating, sizing and monitoring the rollback segments in the database.

Drop Existing Rollback segments and create new UNDO tablespace

SQL> select segment_name,tablespace_name,status from dba_rollback_segs;

SEGMENT_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SYSTEM SYSTEM
ROLL301 RBS3



SQL> select file_name,tablespace_name, bytes from dba_data_files where tablespace_name = 'RBS3';


SQL> select 'alter rollback segment ' SEGMENT_NAME' offline;' from dba_rollback_segs;


SEGMENT_NAME
============

alter rollback segment ROLL301 offline;
alter rollback segment ROLL302 offline;
alter rollback segment ROLL303 offline;

SQL> alter rollback segment ROLL301 offline;

Rollback segment altered.

SQL> alter rollback segment ROLL302 offline;

Rollback segment altered.


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

SQL> drop rollback segment ;

SQL> select 'drop rollback segment ' SEGMENT_NAME';' FROM from dba_rollback_segs;


SQL> select 'drop rollback segment ' SEGMENT_NAME';' FROM dba_rollback_segs;

'DROPROLLBACKSEGMENT'SEGMENT_NAME';'
------------------------------------------------------

drop rollback segment ROLL301;
drop rollback segment ROLL302;

alter tablespace RBS3 offline;


SQL> alter tablespace RBS3 offline;
SQL> alter tablespace RBS2 offline;

drop tablespace RBS2;

Tablespace altered.

Enabling Automatic Undo Management

Since the default undo management mode is MANUAL, the instance must be told to use AUTO mode at instance startup. To do this the following initialization parameters can be set:

UNDO_MANAGEMENT = AUTO # Default is MANUAL
UNDO_TABLESPACE = undotbs_01 # The name of the undo tablespace.
UNDO_RETENTION = 900 # The time undo is retained.
# Default is 900 seconds.
UNDO_SUPPRESS_ERRORS = TRUE # Suppress errors when MANUAL undo admin

# SQL statements are issued.


Create Undo Tablespace

SQL> create undo tablespace APPS_UNDOTS1 datafile '/db2/oradata/dev/data/undodbs01.dbf' size 3000M reuse extent management local;

Add Datafile

SQL> ALTER TABLESPACE APPS_UNDOTS1 ADD DATAFILE '/db2/oradata/dev/data/undodbs02.dbf' size 3000M;

Add a datafile

ALTER TABLESPACE undotbs_01
ADD DATAFILE '/db2/oradata/dev/data/undodbs02.dbf'
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;

Resize an undo datafile

SQL> ALTER DATABASE DATAFILE '/db2/oradata/dev/data/undodbs04.dbf' RESIZE 40000M;

SQL> create undo tablespace APPS_UNDOTS2 datafile '/db2/oradata/dev/data/undodbs04.dbf' size 3000M reuse extent management local;

SQL> ALTER TABLESPACE APPS_UNDOTS2 ADD DATAFILE '/db2/oradata/dev/data/undodbs05.dbf' size 2000M;


Dynamic Parameters.

SQL> ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS_02;
SQL> ALTER SYSTEM SET UNDO_RETENTION=1800;
SQL> ALTER SYSTEM SET UNDO_SUPPRESS_ERRORS=FALSE;

Static Parameters.


SQL> ALTER SYSTEM SET UNDO_MANAGEMENT=AUTO SCOPE=SPFILE;

Drop an undo tablespace.
DROP TABLESPACE undotbs_01;

Monitoring

Undo information can be queried using the following views:

V$UNDOSTAT
V$ROLLSTAT
V$TRANSACTION

Troubleshooting ORA-30036 - Unable To Extend Undo Tablespace : Note:460481.1

How To Use Temporary Tablespaces

Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables.

Each database should have one temporary tablespace that is created when the database is created. You can create, drop and manage tablespaces with create temporary tablespace, drop temporary tablespace and alter temporary tablespace commands.

Allocate temporary tablespace to each user in the daabase, so we can avoid from sort space in the System tablespace.

SQL> CREATE USER scott DEFAULT TABLESPACE data TEMPORARY TABLESPACE temp;
SQL> ALTER USER scott TEMPORARY TABLESPACE temp;


You can remove a TEMPFILE from a database.

SQL> ALTER DATABASE TEMPFILE '/db2/oradata/dev/data/temp02.dbf' DROP INCLUDING DATAFILES;

If you remove all tempfiles from a temporary tablespace, you may encounter error:
ORA-25153: Temporary Tablespace is Empty. So add a TEMPFILE to a temporary tablespace:

SQL>ALTER TABLESPACE temp ADD TEMPFILE '/db2/oradata/dev/data/temp002.dbf' SIZE 200M;

SQL> ALTER TABLESPACE temp OFFLINE

SQL> DROP TABLESPACE temp;

HOW TO create Temporary Tablespaces?

SQL> CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/db2/oradata/dev/data/temp01.dbf' SIZE 3000M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

Add Temp Datafiles

SQL> ALTER TABLESPACE temp
ADD TEMPFILE '/db2/oradata/dev/data/temp02.dbf' SIZE 2000M REUSE;

How to Set Default Temporary Tablespaces

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

- The Default Temporary Tablespace must be of type TEMPORARY
- The DEFAULT TEMPORARY TABLESPACE cannot be taken off-line
- The DEFAULT TEMPORARY TABLESPACE cannot be dropped until you create another one.

SQL> SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

Monitoring Temporary Tablespaces and Sorting

Ttempfiles are not listed in V$DATAFILE and DBA_DATA_FILES
Use V$TEMPFILE and DBA_TEMP_FILES.
One can monitor temporary segments from V$SORT_SEGMENT and V$SORT_USAGE
DBA_FREE_SPACE does not record free space for temporary tablespaces. Use V$TEMP_SPACE_HEADER instead:

SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

TABLESPACE_NAME BYTES_USED BYTES_FREE
------------------------------ ---------- ----------
TEMP 328204288 1819279360
TEMP 332398592 1815085056
TEMP 317718528 1829765120

Concurrent Processing / APPS Reporting Scripts

Concurrent Manager Scripts

Non Destructive Script to Clean Concurrent Manager Tables

CMCLEAN.sql
This script re-sets the flags for requests to completed to allow the Managers to come up
(For all Releases of Apps.)


How To Re-create Concurrent Manager Views


This script details how to Re-create the Concurrent Manager Views
(For all Releases of Apps.)


Concurrent Manager Check Script
CCM.sql

This script will diagnose Concurrent Managers and Concurrent Requests
(For all Releases of Apps.)


Concurrent Manager Check Script
FNDCCMDiagnostic115.sh

This script will diagnose Concurrent Managers and Concurrent Requests
(For Apps. 11.5.3 or higher)


Internal Manager Log Script
ICMLOG.sql

This script will provide the location for Retrieving the Internal Manager Log file
(For For all Releases of Apps.)


Concurrent Requests Scripts
Concurrent Request Log Script
CMLOGS.txt

Script for Retrieving Log Files Associated to a Concurrent Request
(For For all Releases of Apps.)


Concurrent Request Check Script
REQCHECK.sql

Script for diagnosing problematic Concurrent Requests
(For all Releases of Apps.)


Analyze Pending Requests Script
ANALYZEPENDING.sql

Script for diagnosing Pending Concurrent Requests
(For all Releases of Apps.)


Analyze Request Script
ANALYZEREQ.sql

Script for Providing a Detailed Analysis of One Concurrent Request
(For Applications Release 11 and up.)


Who Can Run which Requests
WHOCANRUN.sql
Script that lists Responsibilities that can Run a Given Concurrent Program
(For all Releases of Apps.)


Concurrent Request Diagnostic Script
FNDCCMDiagnostic115.sh

This script will diagnose Concurrent Managers and Concurrent Requests
(For Applications Release 11.5.3 and up.)


Concurrent Request Diagnostic Script
bde_request.sql

Process and Session info for one Concurrent Request
(For Applications Release 11.5 and up.)


Printing Script
FNDPrinterValidation115.sh

This script checks a customer's Printer configuration/setup within Oracle Applications.
(For Applications Release 11.5.3 and up.)


Report Review Agent Script
FNDValidateFNDFS115.sh

This script checks basic setup of the concurrent processing tier report review agent.
(For Applications Release 11.5.3 and up.)


You can download the scripts from metalink.

Concurrent Processing (CP) / APPS Reporting Scripts Doc ID: Note:213021.1


Configuring SSL with Oracle HTTP Server

Set ths following environment varibles

export SCRIPT_TOP=$COMMON_TOP/admin/scripts/sar_sys45

export APACHE_TOP=/oracle/app/apps/sarora/iAS/Apache

export OPENSSL_TOP=/oracle/app/apps/sarora/iAS/Apache/open_ssl

export OPENSSL_CONF=$OPENSSL_TOP/bin

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

Edit in XML file ===> APPL_TOP/admin

set the %s_url_protocol variable to https
set the %s_local_url_protocol variable to https
set the %s_webentryurlprotocol variable to https
set the %s_frmConnectMode variable to https
set the %s_webssl_port variable to the Apache SSL port required
set the %s_active_webport variable to the same value as that for the %s_webssl_port variable
set the %s_webport variable to the same value as that for the %s_webssl_port variable

set the %s_web_ssl_directory variable to point to the full directory path of the directory that is to contain the .crt and

.key files that you are using for Apache eg <$COMMON_TOP>/admin/certs/apache

set %s_apps_portal_url variable to https

=============================
Using self signed certificate with Applications genereted by OpenSSL for WebSever


$APACHE_TOP/open_ssl/bin/openssl req -x509 -newkey rsa:1024 -keyout cakey.pem -out cacert.pem -days 10000 -config openssl.cnf

$APACHE_TOP/open_ssl/bin/openssl req -nodes -new -x509 -keyout apache.key -out apache_request.pem -days 3650 -config openssl.cnf

$APACHE_TOP/open_ssl/bin/openssl x509 -x509toreq -in apache_request.pem -signkey apache.key -out apache.csr

$APACHE_TOP/open_ssl/bin/openssl ca -config openssl.cnf -policy policy_anything -out apache.crt -infiles apache.csr


cp apache.crt $COMMON_TOP/admin/certs/apache/ssl.crt/server.crt
cp apache.key $COMMON_TOP/admin/certs/apache/ssl.key/server.key
cp cacert.pem $COMMON_TOP/admin/certs/apache/ssl.crt/ca.crt
cp $APACHE_TOP/Apache/conf/ssl.crt/ca-bundle.crt $COMMON_TOP/admin/certs/apache/ssl.crt


openssl s_client -connect sys45.doyen.in:443



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

FRM-92050 Failed to connect to the server

If you got above forms error, please run the below syntax


txkrun.pl -script=SetAdvCfg -appsuser=apps -appspass=apps -enable=FormsLsnrServlet


[applmgr@sys43 TEST_sys43]$ txkrun.pl -script=SetAdvCfg -appsuser=apps -appspass=apps -enable=FormsLsnrServlet



++++ Configuration has completed successfully!

For additional information, review the following output file:

/oracle/app/apps/testcomn/admin/out/TEST_sys43/txkSetAdvCfg.xml


Then after sucessfully completed txkrun.pl, run the autoconfig.


[applmgr@sys43 TEST_sys43]$ adautocfg.sh

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


After complete the autoconfig, bounce the apps server.
Now start the apps server and start this appache scripts from root user (adapcctl.sh start)


[root@sys43 ~]# sh /oracle/app/apps/testcomn/admin/scripts/TEST_sys43/adapcctl.sh start

adapcctl.sh version 115.54

Apache Web Server Listener is not running.
Starting Apache Web Server Listener (dedicated HTTP) ...
Apache Web Server Listener (PLSQL) is not running.
Starting Apache Web Server Listener (dedicated PLSQL) ...

adapcctl.sh: exiting with status 0
=============================

https://.
=============================

System: Connection reset by peer (errno: 104)
Comment SSLCertificateChainFile in httpd.conf.


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

Reference Documents ID

http://forums.oracle.com/forums/thread.jspa?messageID=2307995


Running Configuration Wizards from the Command Line in Oracle Applications 11i

Metalik id : 277574.1

Subject: Troubleshooting SSL with Oracle Applications 11i
Doc ID: Note:300969.1

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

How To Use AD Merge Patch

Create Source and destination directory
Copy all the patche to source directory
and run the below scripts
admrgpch -s /home/applmgr/patch10g/source -d /home/applmgr/patch10g/dest -merge_name ramesh

Executing the merge of the patch drivers -- Processing patch: /home/applmgr/patch10g/source/4888294 -- Done processing patch: /home/applmgr/patch10g/source/4888294
-- Processing patch: /home/applmgr/patch10g/source/4653225 -- Done processing patch: /home/applmgr/patch10g/source/4653225
-- Processing patch: /home/applmgr/patch10g/source/5985992 -- Done processing patch: /home/applmgr/patch10g/source/5985992
-- Processing patch: /home/applmgr/patch10g/source/6502082 -- Done processing patch: /home/applmgr/patch10g/source/6502082

Copying files...
5% complete. Copied 98 files of 1942...
10% complete. Copied 195 files of 1942...
15% complete. Copied 292 files of 1942...
20% complete. Copied 389 files of 1942...
25% complete. Copied 486 files of 1942...
30% complete. Copied 583 files of 1942...
35% complete. Copied 680 files of 1942...
40% complete. Copied 777 files of 1942...
45% complete. Copied 874 files of 1942...
50% complete. Copied 971 files of 1942...
55% complete. Copied 1069 files of 1942...
60% complete. Copied 1166 files of 1942...
65% complete. Copied 1263 files of 1942...
70% complete. Copied 1360 files of 1942...
75% complete. Copied 1457 files of 1942...
80% complete. Copied 1554 files of 1942...
85% complete. Copied 1651 files of 1942...
90% complete. Copied 1748 files of 1942...
95% complete. Copied 1845 files of 1942...
100% complete. Copied 1942 files of 1942...

Character-set converting files...
4 unified drivers merged.
Patch merge completed successfully
Please check the log file at ./admrgpch.log.

Developer 6i Patch Patch 18*

Download the below Patches for developer 6i Patch set.



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

6502082 - done - AD.I.6
5985992 - done - TXK AUTOCONFIG ROLLUP PATCH Q (JUL/AUG 2007)

3453499 - already applied

4653225 - done - 11.5.10 INTEROP PATCH FOR 10GR2

==============================================================
4948577 - done - Developer 6i Patch Patch 18*

4888294 - done - Apps Interop Patch

3830807 - done - APPLICATIONS INTEROPERABILITY PATCH

4586086 - done - UNABLE TO RELINK LIBIFJAPI60.SO IN RHAS3.0

Download Additional Developer 6i Patches
5713544 - done
4261542 - done
5216496 - done
5753922 - done
6195758 - done
5938515 - done
================================================================

Apply this patch 5985992

From the location where this patch was unzipped, change directory
to fnd/patch/115/bin .

on Unix:

./txkprepatchcheck.pl -script=ValidateRollup -outfile=$APPLTMP/txkValidateRollup.html -appspass=apps


[applmgr@sys43 bin]$ ./txkprepatchcheck.pl -script=ValidateRollup -outfile=$APPLTMP/txkValidateRollup.html -appspass=apps
*** ALL THE FOLLOWING FILES ARE REQUIRED FOR RESOLVING RUNTIME ERRORS
*** STDOUT = /oracle/app/apps/testcomn/rgf/TEST_sys43/TXK/txkValidateRollup_Fri_Apr_11_21_21_24_2008_stdout.log
Reportfile /oracle/app/apps/testcomn/temp/txkValidateRollup.html generated successfully.




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


cd
unzip -o appsutil.zip

Run AutoConfig by executing:
/appsutil/scripts//
adautocfg.sh

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

Forms & Reports 6i Patch 18 for Linux



Set your ORACLE_HOME.
If you are going to apply this patch on top of an iAS install:
Set ORACLE_HOME to /6iserver
Prepend /6iserver/bin to the *beginning* of PATH
Prepend /6iserver/lib to the *beginning* of LD_LIBRARY_PATH
==========================================================
[applmgr@sys43 patch10g]$ export ORACLE_HOME=/oracle/app/apps/testora/iAS/6iserver
[applmgr@sys43 patch10g]$ export LD_LIBRARY_PATH=/oracle/app/apps/testora/iAS/6iserver/lib:$LD_LIBRARY_PATH
[applmgr@sys43 patch10g]$ export PATH=/oracle/app/apps/testora/iAS/6iserver/bin:$PATH


[applmgr@sys43 6iserver]$ pwd
/oracle/app/apps/testora/iAS/6iserver
=============================================
Copy the files in this patch to your $ORACLE_HOME
[applmgr@sys43 developer6i_patch18]$ echo $ORACLE_HOME
/oracle/app/apps/testora/iAS/6iserver
[applmgr@sys43 6iserver]$ unzip p4948577_600_LINUX.zip

ls -ltr
drwxr-xr-x 6 applmgr oinstall 4096 Jan 13 2006 developer6i_patch18

[applmgr@sys43 developer6i_patch18]$ pwd
/oracle/app/apps/testora/iAS/6iserver/developer6i_patch18
Run the install script contained in the patch:
cd $ORACLE_HOME/developer6i_patch18
./patch_install.sh 2>&1 | tee patch_install_p18.log (ksh)
./patch_install.sh |& tee patch_install_p18.log (csh)
================================================================
[applmgr@sys43 developer6i_patch18]$ ./patch_install.sh 2>&1 | tee patch_install_p18.log

Patch can be deinstalled by using /oracle/app/apps/testora/iAS/6iserver/developer6i_patch18/patch_deinstall.sh script
===============================================================
Check patch_install_p18.log for errors
Relink Procedure Builder, Forms, Graphics and Reports:
cd $ORACLE_HOME/procbuilder60/lib; make -f ins_procbuilder.mk install
cd $ORACLE_HOME/forms60/lib; make -f ins_forms60w.mk install
cd $ORACLE_HOME/graphics60/lib; make -f ins_graphics60w.mk install

============================================================
[applmgr@sys43 developer6i_patch18]$ cd $ORACLE_HOME/procbuilder60/lib
[applmgr@sys43 lib]$ make -f ins_procbuilder.mk install

[applmgr@sys43 lib]$ cd $ORACLE_HOME/forms60/lib
[applmgr@sys43 lib]$ make -f ins_forms60w.mk install

[applmgr@sys43 lib]$ cd $ORACLE_HOME/graphics60/lib
[applmgr@sys43 lib]$ make -f ins_graphics60w.mk install

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

Reports has both link-time and run-time dependency with libjava.so so you need to append either one of:
$ORACLE_HOME/network/jre11/lib/linux/native_threads
$ORACLE_HOME/network/jre11/lib/i686/native_threads
in $LD_LIBRARY_PATH before linking Reports.
Please check your files under $ORACLE_HOME/network/jre11/lib to see which one of the above is appropriate on your system. The same $LD_LIBRARY_PATH should be used at run-time.
cd $ORACLE_HOME/reports60/lib; make -f ins_reports60w.mk install


======================================================
[applmgr@sys43 lib]$ cd $ORACLE_HOME/network/jre11/lib/i686/
[applmgr@sys43 i686]$ pwd
/oracle/app/apps/testora/iAS/6iserver/network/jre11/lib/i686
[applmgr@sys43 i686]$ ls -ls native_threads
total 4940
2552 -rwxr-xr-x 1 applmgr oinstall 2606042 Jun 20 2001 libawt.so
1376 -rwxr-xr-x 1 applmgr oinstall 1404357 Jun 20 2001 libjava.so
312 -rwxr-xr-x 1 applmgr oinstall 313893 Jun 20 2001 libjpeg_sun.so
164 -rwxr-xr-x 1 applmgr oinstall 160561 Jun 20 2001 libmath.so
88 -rwxr-xr-x 1 applmgr oinstall 84707 Jun 20 2001 libmmedia.so
156 -rwxr-xr-x 1 applmgr oinstall 151672 Jun 20 2001 libnet.so
88 -rwxr-xr-x 1 applmgr oinstall 85802 Jun 20 2001 libsysresource.so
204 -rwxr-xr-x 1 applmgr oinstall 203482 Jun 20 2001 libzip.so


[applmgr@sys43 i686]$ export LD_LIBRARY_PATH=$ORACLE_HOME/network/jre11/lib:$LD_LIBRARY_PATH
[applmgr@sys43 i686]$ cd $ORACLE_HOME/reports60/lib
[applmgr@sys43 lib]$ make -f ins_reports60w.mk install
=======================================================


[applmgr@sys43 bin]$ pwd
/oracle/app/apps/testora/iAS/6iserver/developer6i_patch18/bin
-rwxr-xr-x 1 applmgr oinstall 2811 Dec 12 2005 genshlib

Copy this file genshlib to /oracle/app/apps/testora/iAS/6iserver/bin
[applmgr@sys43 bin]$ cp genshlib genshlib.bak_Apr12
[applmgr@sys43 bin]$ pwd
/oracle/app/apps/testora/iAS/6iserver/bin
[applmgr@sys43 bin]$ cp -b genshlib /oracle/app/apps/testora/iAS/6iserver/bin

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


unzip p3830807_8063_LINUX.zip

cd 3830807

chmod u+x patch.sh

export LD_LIBRARY_PATH=/oracle/app/apps/testora/8.0.6/network/jre11/lib:$LD_LIBRARY_PATH

./patch.sh

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

cd $ORACLE_HOME
mkdir patch
cd patch

unzip p4586086_600_LINUX.zip


cd $ORACLE_HOME/forms60/lib

[applmgr@sys43 lib]$ ls -lrt env_forms60.mk
-rw-r--r-- 1 applmgr oinstall 28454 Dec 22 2005 env_forms60.mk

mv env_forms60.mk env_forms60.mk.PRE_BUG4586086


cp $ORACLE_HOME/patch/bug4586086/env_forms60.mk $ORACLE_HOME/forms60/lib

cd $ORACLE_HOME/forms60/lib
make -f cus_forms60w.mk libso_install


adrelink.sh force=y "fnd f60webmx"


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

cd 5713544
sh patch.sh

E.g. to relink f60webmx for Oracle Applications
adrelink.sh force=y "fnd f60webmx"

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


1. Stop your web listeners and Forms Server.

2. Make a patch directory within your 6i ORACLE_HOME and unzip

cd $ORACLE_HOME
[applmgr@sys43 6iserver]$ cp p4261542_600_GENERIC.zip $ORACLE_HOME
unzip p4261542_600_GENERIC.zip


[Part 2] Unzip the java class files and regenerate your JAR files

3. Backup the Forms class files, i.e.$ORACLE_HOME/forms60/java/oracle/forms/handler/AlertDialog.class

[applmgr@sys43 4261542]$ cd $ORACLE_HOME/forms60/java/oracle/forms/handler/
[applmgr@sys43 handler]$ ls -ls AlertDialog.class
8 -rw-rw-r-- 1 applmgr oinstall 6673 Dec 21 2005 AlertDialog.class

[applmgr@sys43 handler]$ mv AlertDialog.class AlertDialog.class.bak

[applmgr@sys43 handler]$ cd $ORACLE_HOME/forms60/java/oracle/forms/engine/
[applmgr@sys43 engine]$ ls -ls Main.class
24 -rw-rw-r-- 1 applmgr oinstall 20545 Dec 21 2005 Main.class

[applmgr@sys43 engine]$ mv Main.class Main.class.bak




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

[applmgr@sys43 engine]$ cd $ORACLE_HOME/4261542/oracle/forms/engine
[applmgr@sys43 engine]$ cp Main.class $ORACLE_HOME/forms60/java/oracle/forms/engine/Main.class
[applmgr@sys43 engine]$ cd $ORACLE_HOME/4261542/oracle/forms/handler
[applmgr@sys43 handler]$ cp AlertDialog.class $ORACLE_HOME/forms60/java/oracle/forms/handler/AlertDialog.class


5. Generate JAR Files use adadmin

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

cd 5216496
sh patch.sh
=================================

[applmgr@sys43 6iserver]$ cp p6195758_60827_GENERIC.zip $ORACLE_HOME

[applmgr@sys43 6iserver]$ unzip p6195758_60827_GENERIC.zip

Backup the Forms class files,


[applmgr@sys43 handler]$ cd $ORACLE_HOME/forms60/java/oracle/forms/handler/
[applmgr@sys43 handler]$ mv UICommon.class UICommon.class.bak
[applmgr@sys43 handler]$ cd $ORACLE_HOME/forms60/java/oracle/forms/handler/
[applmgr@sys43 handler]$ mv ComponentItem.class ComponentItem.class.bak

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


[applmgr@sys43 handler]$ cd $ORACLE_HOME/6195758/oracle/forms/handler
[applmgr@sys43 handler]$ cp UICommon.class $ORACLE_HOME/forms60/java/oracle/forms/handler/UICommon.class
[applmgr@sys43 handler]$ cp ComponentItem.class $ORACLE_HOME/forms60/java/oracle/forms/handler/ComponentItem.class

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

cd 5938515
sh patch.sh

adrelink.sh force=y "fnd f60webmx"

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


cd 5753922
sh patch.sh

adrelink.sh force=y "fnd f60webmx"

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


How to find forms version in patch set


$ORACLE_HOME/bin/f60gen help=y
Forms 6.0 (Form Compiler) Version 6.0.8.27.0 (Production)

Metalik id :125767.1

6.0.8.27.0 ===> Patch 18




Pre-Upgrade Step:


select banner from v$version;

SELECT product_group_id GroupID, product_group_name GroupName,release_name Release,product_group_type GroupType, argument1 FROM fnd_product_groups;

select count(*) from ad_bugs where bug_number = '5985992';


patch 4653225

Check the developer 6i Patch set.

Metalink Note 125767.1


Developer 6i patchset 18 (4948577)