Main “steps” documents id:
362205.1: 10g Release 2 Export/Import Process for Oracle Applications Release 11i
557738.1: Export/import notes on Applications 11i Database 11g
1265268.1: Interoperability Notes Oracle EBS 11i with Oracle Database 11gR2 (11.2.0.1)
165195.1: Using AutoConfig to Manage System Configurations with Oracle Applications 11i
Oracle Database Upgrade Guide 11g Release 2 (11.2) part no. E23633-07
Other doc_id: 881505.1, 1367654.1
Troubleshooting:
Oracle Text: Re-installation of Applications 11i (11.5.10) Oracle Text Indexes [ID 312640.1]
Contents
Upgrade 11i EBS database from 10.2.0.4 to 11.2.0.1. 1
Before the Database Installation and Upgrade. 3
I. Interoperability “Pre”: 3
Database Upgrade: 4
II. Pre-Export the database: 4
III. Pre-Import the database: 6
IV- Export the database: 9
V- Import the database: 10
IV- Post-Import and database configuration: 11
Before the Database Installation and Upgrade
I. Interoperability “Pre”:
From doc_id: 1265268.1 -> Section1: Upgrading an E-Business Suite 11i Database to Oracle Database 11g Release 2 (11.2.0)
1) Verify S/W and system’s versions:
a) Ebs 11.5.10.2
b) ATG Rollup Patch 6
c) Developer 6i Patch set 18
d) AutoConfig Latest version with 9835302
2) Nothing to do
3) Nothing to do
4) Deregister the current database server
To deregister the current database server node because If you plan to change the database port, host, SID, or database name parameter, run the following command as the owner of the Oracle RDBMS file system and current database instance:
$ perl $ORACLE_HOME/appsutil/bin/adgentns.pl
appspass=[APPS db user password]
contextfile=$CONTEXT_FILE -removeserver
5) Update application tier context file with new database listener port number
If you want to continue use the APPS tier, modify XML file with new database info. OR just make new cloned instance to connect to new DB tier.
s_dbhost
New database hostname
s_dbdomain
New database domain name
s_db_serv_sid
New database SID
s_dbport
New database listener port
s_apps_jdbc_connect_descriptor
NULL
6) Upgrade the database instance
362205.1: 10g Release 2 Export/Import Process for Oracle Applications Release 11i
557738.1: Export/import notes on Applications 11i Database 11g
1265268.1: Interoperability Notes Oracle EBS 11i with Oracle Database 11gR2 (11.2.0.1)
165195.1: Using AutoConfig to Manage System Configurations with Oracle Applications 11i
Oracle Database Upgrade Guide 11g Release 2 (11.2) part no. E23633-07
According to previous documents the upgrade will be using import/export method.
Database Upgrade:
II. Pre-Export the database:
From doc_id: 362205.1 -> Section1: Prepare the source system
From doc_id: 557738.1 -> Section1: Prepare the source system
7) Apply the Applications consolidated export/import utility patch
Apply both patches for exporting 10 (4872830) and 11g (1235353).
8) Apply latest Applications database preparation scripts patch
Apply Patch 7225862 to every application tier server node in the source system.
9) Create a working directory on source database node
Create a working directory named expimp in the source system that will contain all generated files and scripts required to complete this section. As an example,
$ mkdir /u01/expimp
10) Generate target database instance creation script adcrdb.sql (10g), aucrdb.sql (11g)
11g $sqlplus system/manager @$AU_TOP/patch/115/sql/auclondb.sql 11
10g $sqlplus system/manager @$AU_TOP/patch/115/sql/adclondb.sql
Then edit this script to reflect the new environment.
“Split it into 2 files, first one for CREATE DATABASE statement include in it the UNDO TABLESPACE section. The other file is to the rest of tablespaces adcrdb_db.sql and adcrdb_tbs.sql”.
Also adpostcrdb.sql which you use to convert tablespaces to locally managed will be created.
11) Record Advanced Queue settings
Copy the auque1.sql script from the $AU_TOP/patch/115/sql directory on the source administration server node to the working directory in the source database server node
$ sqlplus /nolog
SQL> connect / as sysdba;
SQL> @auque1.sql
It generates auque2.sql in the current directory.
12) Remove rebuild index parameter in spatial indexes
SQL> select * from dba_indexes where index_type=’DOMAIN’ and
upper (parameters) like ‘%REBUILD%’;
To remove the rebuild index parameter, use SQL*Plus to connect to the source database as the owner of the index and run the following command:
SQL> alter index [index name] rebuild parameters (‘parameters’)
Where [parameters] is the original parameter set without the rebuild_index parameter.
13) Synchronize Text indexes
$ sqlplus ‘/ as sysdba’
SQL> select pnd_index_owner,pnd_index_name,count(*)
from ctxsys.ctx_pending
group by pnd_index_owner,pnd_index_name;
To synchronize the indexes, run the following command:
SQL> exec ctx_ddl.sync_index(‘[index owner].[index name]‘);
14) Drop SYS.ENABLED$INDEXES (conditional)
SQL> drop table sys.enabled$indexes;
15) Shut down Applications server processes and database listener
III. Pre-Import the database:
16) Prepare to create the 11.2.0 Oracle home
The 11.2.0 Oracle home must be installed on the database server node in a different directory than the current Oracle home
a) Finish OS prerequisites for new Oracle home “new host or exist host”.
b) Oracle OS owner profile option and environment variables.
17) Install the base 11.2.0 software: install software only
18) Install Oracle Database 11g Products from the 11g Examples CD
19) Create nls/data/9idata directory
Run the $ORACLE_HOME/nls/data/old/cr9idata.pl script to create the $ORACLE_HOME/nls/data/9idata directory.
20) Apply additional 11.2.0.1 RDBMS patches “9 patches using opatch”
Append Opatch tool to PATH,
$export PATH=$PATH:$ORACLE_HOME/OPatch
and run the following patches:
8570322
8685327
8761974
8771297
12672969-> instead of 8772028
8796511
8897784
8964142
8405205
21) Create working directory on target database server.
Create a working directory named expimp in the target system that will contain all generated files and scripts required to complete this section. As an example,
$ mkdir /u01/expimp
22) Copy scripts needed
Copy the following from Source environment “database or application servers” to the working directory on target database:
I. Copy initialization parameter file and CBO parameter file (initSID.ora and ifilecbo.ora “if exists”) from source database server, modify wanted parameter
II. Copy modified database creation scripts (adcrdb_db.sql and adcrdb_tbs.sql and adpostcrdb.sql) from source database server
III. Copy database preparation scripts (addb1020.sql, adsy1020.sql, adjv1020.sql, and admsc1020.sql) OR (audb1110.sql/audb1120.sql, ausy1110.sql/ausy1120.sql, aujv1110.sql/aujv1120.sql, and aumsc1110.sql/aumsc1120.sql) from $APPL_TOP/admin directory of the source administration server node.
IV. Copy auque2.sql generated file on step 18 from source database server working directory.
V. Copy $APPL_TOP/admin/adstats.sql from the administration server node to the working directory in the target database server node
VI. Copy $APPL_TOP/admin/adgrants.sql sql from the administration server node to the working directory in the target database server node.
VII. Copy $AD_TOP/patch/115/sql/adctxprv.sql from the administration server node to the working directory in the target database server node.
Create appsutil.zip file
perl
/bin/admkappsutil.pl
This will create appsutil.zip in $APPL_TOP/admin/out .
Then copy it to new ORACLE_HOME then
unzip -o appsutil.zip
23) Create the target database instance
Use modified adcrdb.sql script, generated from step 17
$ sqlplus /nolog
SQL> connect / as sysdba;
SQL> spool adcrdb.log;
SQL> startup nomount;
SQL> @adcrdb_db.sql
SQL> @adcrdb_tbs.sql
SQL> @ adpostcrdb.sql
SQL> exit;
24) Set up the SYS schema
the audb1110.sql, audb1120.sqlor addb1020.sql script sets up the SYS schema for use with the Applications. On the target database server node, use SQL*Plus to connect to the target database instance as SYSDBA and run as sys.
$ sqlplus “/ as sysdba” @/u01/expimp/audb1120.sql
25) Set up the SYSTEM schema
the ausy1110.sql, ausy1120.sql or adsy1020.sql script sets up the SYSTEM schema for use with the Applications. On the target database server node, use SQL*Plus to connect to the target database instance as SYSTEM and run as system
$ sqlplus system/manager @/u01/expimp/ausy1120.sql
26) Install Java Virtual Machine
The aujv1110.sql, aujv1120.sql or adjv1020.sql script installs the Java Virtual Machine (JVM) in the database. On the target database server node, use SQL*Plus to connect to the target database instance as SYSTEM and run as system.
$ sqlplus system/manager @/u01/expimp/aujv1120.sql
27) Install other required components
the aumsc1110.sql, aumsc1120.sql or admsc1020.sql script installs the following required components in the database: ORD, Spatial, XDB, OLAP, Data Mining, interMedia, and ConText. On the target database server node, use SQL*Plus to connect to the target database instance as SYSTEM and as system.
$ sqlplus system/manager @/u01/expimp/aumsc1120.sql FALSE SYSAUX TEMP
28) Install custom RDBMS components (conditional)
if you have other custom RDBMS components loaded in the source database such as Label Security; install them in the target database. To determine the RDBMS components that are loaded in the source and target databases, use SQL*Plus to connect to the databases as SYSDBA and run the following command:
SQL> select * from dba_registry;
29) Disable automatic gathering of statistics
Use SQL*Plus to connect to the database as SYSDBA and use the following commands to restart the database in restricted mode and run adstats.sql:
$ sqlplus “/ as sysdba”
SQL> shutdown normal;
SQL> startup restrict;
SQL> @adstats.sql
SQL> exit;
30) Back up the target database instance
the target database instance is now prepared for an import of the Applications data. You should perform a backup before starting the import.
IV- Export the database:
31) Create the export parameter file
Copy $AU_TOP/patch/115/import/auexpdp.dat template file and edit it with proper values as following:
directory=DUMP_DIR
dumpfile=full_exp%U.dmp
filesize=1G
full=y
logfile=full_expdpapps.log
32) Create export directory
$ sqlplus system/manager
SQL> create directory DUMP_DIR as ‘/u01/expimp’;
33) Shut down Applications server processes
34) Grant privilege to source system schema
$ sqlplus / as sysdba
SQL> grant EXEMPT ACCESS POLICY to system;
35) Export the Applications database instance
If the source database is 10.2.0 or 11.1.0, use the following command:
$ expdp system/[system password] \
parfile=[export parameter file name]
If the source database is 11.2.0, use the following command:
$ expdp “‘/ as sysdba’” parfile=[export parameter file name]
36) Revoke privilege from source system schema
SQL> revoke EXEMPT ACCESS POLICY from system;
V- Import the database:
37) Create the import parameter file
Copy the export parameter file you created in Step 30 from the source database server node to the working directory in the target database server node, renaming it if necessary. Updating the new file with the following changes converts it to an import parameter file:
Remove the exclude parameters.
Remove the filesize parameter.
Change the name of the log file.
Uncomment the transform parameter.
It will be like this:
directory=DUMP_DIR
dumpfile=full_exp%U.dmp
full=y
transform=oid:n
logfile=impdpapps.log
38) Create export directory
$ sqlplus system/manager
SQL> create directory DUMP_DIR as ‘/u01/expimp’;
39) Copy the export dump files from source to target
40) Set Oracle Text parameter
if the target database is 11.2.0, use SQL*Plus to connect to the target database as SYSDBA and run the following command to grant datastore privileges:
$ sqlplus “/ as sysdba”
SQL> exec ctxsys.ctx_adm.set_parameter (‘file_access_role’, ‘public’);
41) Import the Applications database instance
Start the import session on the target database server node using the customized import parameter file.
If the target database is 11.1.0, use the following command:
$ impdp system/manager parfile=[import parameter file name]
If the target database is 11.2.0, use the following command:
$ impdp “‘/ as sysdba’” parfile=[import parameter file name]
42) Revoke privilege from target system schema
Revoke the exempt access policy privilege from system by using SQL*Plus to connect to the database as SYSDBA and run the following command:
SQL> revoke EXEMPT ACCESS POLICY from system;
IV- Post-Import and database configuration:
From doc: Export/import notes on Applications 11i Database 11g [ID 557738.1]
And doc: Interoperability Notes Oracle EBS 11i with Oracle Database 11gR2 (11.2.0) [ID 881505.1]
And doc Using AutoConfig to Manage System Configurations with Oracle Applications 11i [ID 165195.1]
43) Reset Advanced Queues
Run file created in step:11.
$ sqlplus /nolog
SQL> connect / as sysdba;
SQL> @/u01/expimp/auque2.sql
44) Perform patch post-install instructions
run all the patch post install instructions except for those of 13001379 and 13366268. There is no need to run the post install instructions of 13001379 and 13366268 as they are run as part of the database upgrade.
45) Run adgrants.sql
$ sqlplus “/ as sysdba” @adgrants.sql APPS
46) Grant create procedure privilege on CTXSYS
$ sqlplus apps/apps @adctxprv.sql SYSTEM CTXSYS
47) Set CTXSYS parameter
$ sqlplus “/ as sysdba”
SQL> exec ctxsys.ctx_adm.set_parameter(‘file_access_role’, ‘public’);
48) Deregister the current database server
$ sqlplus apps/apps
SQL> exec fnd_conc_clone.setup_clean;
49) Some errors will be shown in the autoconfig
To avoid any error in running autoconfig in coming steps, review and verify the number and status of CTXSYS objects, synm. and grants.
To avoid any problem in INDEXES owned by CTXSYS, please run the following selected scripts from document: @jtfiappr.sql apps
Oracle Text: Re-installation of Applications 11i (11.5.10) Oracle Text Indexes [ID 312640.1]
enable applsys.wf_java_defered queue
some of grants may be lost so run the following list:
Compile the invalid objects.
$ sqlplus “/ as sysdba” @$ORACLE_HOME/rdbms/admin/utlrp.sql
50) Generate your Database Context File
Ensure that shell reflect the oracle_home and bin.
From extracted appsutil.zip
cd
/appsutil/bin
perl adbldxml.pl tier=db appsuser=apps
answer the question to generate XML file.
51) Generate and Apply AutoConfig Configuration files in DB tier.
Execute the following commands:
cd
/appsutil/bin
adconfig.sh contextfile=XML file created in step 50
52) Re-create custom database links
if the Oracle Net listener in the 11.2.0 Oracle home is defined differently than the one used by the old Oracle home, you must re-create any custom self-referential database links that exist in the Applications database instance. To check for the existence of database links, use SQL*Plus on the database server node to connect to the Applications database instance as APPS and run the following query:
$ sqlplus apps/[apps password]
SQL> select db_link from dba_db_links;
The EDW_APPS_TO_WH and APPS_TO_APPS database links, if they exist, should have been updated with the new port number by AutoConfig in the previous step.
If you have custom self-referential database links in the database instance, use the following commands to drop and re-create them:
$ sqlplus apps/[apps password]
SQL> drop database link [custom database link];
SQL> create database link [custom database link] connect to
[user] identified by [password] using
‘(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=[hostname])
(PORT=[port number]))(CONNECT_DATA=(SID=[ORACLE_SID])))’;
where [custom database link], [user], [password], [hostname], [port number], and [ORACLE_SID] reflect
the new Oracle Net listener for the database instance.
Drop database link “PRODUCTION.HQ.ASQ.COM”;
create database link “PRODUCTION.HQ.ASQ.COM”
using ‘(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=demo.local)(PORT=1521))))(CONNECT_DATA=(SERVICE_NAME=UPGRD))’;
Drop public database link “READAPPSPROD.REGRESS.RDBMS.DEV.US.ORACLE.COM”;
create public database link “READAPPSPROD.REGRESS.RDBMS.DEV.US.ORACLE.COM” using ’192.168.1.245:1521/UPGRD’;
Drop database link “EDW_APPS_TO_WH.HQ.ASQ.COM”;
create database link “EDW_APPS_TO_WH.HQ.ASQ.COM” using ’192.168.1.245:1521/UPGRD’;
Drop database link “EDW_APPS_TO_WH.ABDULSAMADALQURASHI.LOCAL”;
create database link “EDW_APPS_TO_WH.ABDULSAMADALQURASHI.LOCAL” using ’192.168.1.245:1521/UPGRD’;
Drop database link “APPS_TO_APPS.HQ.ASQ.COM”;
create database link “APPS_TO_APPS.HQ.ASQ.COM” using ’192.168.1.245:1521/UPGRD’;
Drop database link “APPS_TO_APPS.ABDULSAMADALQURASHI.LOCAL”;
create database link “APPS_TO_APPS.ABDULSAMADALQURASHI.LOCAL” using ’192.168.1.245:1521/UPGRD’;
Drop database link “APPS_TO_APPS.LOCAL”;
create database link “APPS_TO_APPS.LOCAL” using ’192.168.1.245:1521/UPGRD’;
Drop database link “EDW_APPS_TO_WH.LOCAL”;
create database link “EDW_APPS_TO_WH.LOCAL” using ’192.168.1.245:1521/UPGRD’;
53) Generate and Apply AutoConfig Configuration files in APPS tier.
54) Apply Oracle Human Resources (HRMS) patch 7721754
55) Use adadmin to:
Compile invalid object
Re-create grants and synonyms using adadmin
Compile flexfield
