http://forums.oracle.com/forums/thread.jspa?messageID=9175141
Hi
Please following the following steps:
1. Check if the account (say hyp_docm) is there in Admin group of all the HFM boxes.
2. Ensure DOCM is not stopped in any HFM boxes.
3. Windows Fix
- Execute on HFM Servers
Note – this change is made because of performance / stability issues, in accordance to Microsoft article KB899148 (http://support.microsoft.com/kb/899148/en-us).
- Open registry ‘Start -> Run “regedit”’
- Navigate to ‘HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows NT\Rpc’
Note – you may have to create the Rpc key under Windows NT key
To create key
Navigate to ‘HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows NT’
Right-click, select ‘New’
Select ‘Key’
Set name to Rpc
- Right-click on the right-hand pane, navigate ‘New >DWORD Value’
Set name to ‘Server2003NegotiateDisable’
- Double-click ‘Server2003NegotiateDisable’
Value Data: 1
- Select ‘OK’
- Close Registry editor
- Reboot the server (Must)
4. Dcom permission
Set Local Policy Permissions
=> Open Local Security Settings applet, ‘Start -> Run “secpol.msc”’
=> Navigate to ‘Local Policies -> User Rights Assignments’
=> Double-click ‘Act as Part of Operating System’
=> Select ‘Add User or Group’
Enter object name ‘domain\hyp_docm’
Select ‘Check Names’
Select ‘OK’
=> Select ‘Apply’
=> Select ‘OK’
=> Double-click ‘Bypass Traverse Checking’
=> Select ‘Add User or Group’
Enter object name ‘domain\hyp_docm’
Select ‘Check Names’
Select ‘OK’
=> Select ‘Apply’
=> Select ‘OK’
=> Double-click ‘Log on as Batch Job’
=> Select ‘Add User or Group’
Enter object name ‘domain\hyp_docm'
Select ‘Check Names’
Select ‘OK’
=> Select ‘Apply’
=> Select ‘OK’
=> Double-click ‘Log on as Service’
=> Select ‘Add User or Group’
Enter object name ‘domain\hyp_docm'
Select ‘Check Names’
Select ‘OK’
=> Select ‘Apply’
=> Select ‘OK’
=> Close Local Security Settings applet
5. Rotate the config log and re-configure all HFM steps using EPM Confihurator. Don't forget to confin Workssoace web
Regards
Rupak
Mantra to Win | winmantras.com
http://forums.oracle.com/forums/thread.jspa?messageID=9175141
Wednesday, December 22, 2010
Monday, December 13, 2010
Enable Database Enterprise Manager Console on R12
Enable Database Enterprise Manager Console on R12
First login as applmgr user, shutdown all application tier process using adstpall.sh.
Login as oracle user to linux. Source the database environment.
Ensure REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE is set in the init.ora for the database using this command: SQL> show parameter REMOTE_LOGIN_PASSWORDFILE;
Create a password file using this command on linux: orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=change_on_install entries=5
Then execute this command: emca -config dbcontrol db -repos recreate -SID ITB -PORT 1521 -DBCONTROL_HTTP_PORT 5500
(remember to change SID and port if needed).
You will be asked some question:
SYS password: the default is change_on_install
DBSNMP password: dbsnmp
SYSMAN password: sysman
Email: enter your email address (optional)
SMTP server: enter your smtp server
SYSMAN schema will be dropped and recreate.
While dropping the scema, database will be quisced. Database sessions that blocking the quiescing process must be killed.
Use sqlplus to identify the sessions:
SQL> select bl.sid, serial#, user, osuser, type, program from v$blocking_quiesce bl, v$session se where bl.sid = se.sid;
Make note on SID and SERIAL#.
Kill the session using this sql command:
SQL> alter system kill session 'SID,SERIAL#' immediate;
(don't forget to change SID and SERIAL#).
On create schema process you may need to increase the size of SYSAUX tablespace.
After the process completed you can access the enterprise manager on internet browser using this URL:
http://hostname:5500/em
You may also have to run $ORACLE_HOME/root.sh as root to avoid ERROR: NMO not setuid-root. Just answer no if overwrite is asked
First login as applmgr user, shutdown all application tier process using adstpall.sh.
Login as oracle user to linux. Source the database environment.
Ensure REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE is set in the init.ora for the database using this command: SQL> show parameter REMOTE_LOGIN_PASSWORDFILE;
Create a password file using this command on linux: orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=change_on_install entries=5
Then execute this command: emca -config dbcontrol db -repos recreate -SID ITB -PORT 1521 -DBCONTROL_HTTP_PORT 5500
(remember to change SID and port if needed).
You will be asked some question:
SYS password: the default is change_on_install
DBSNMP password: dbsnmp
SYSMAN password: sysman
Email: enter your email address (optional)
SMTP server: enter your smtp server
SYSMAN schema will be dropped and recreate.
While dropping the scema, database will be quisced. Database sessions that blocking the quiescing process must be killed.
Use sqlplus to identify the sessions:
SQL> select bl.sid, serial#, user, osuser, type, program from v$blocking_quiesce bl, v$session se where bl.sid = se.sid;
Make note on SID and SERIAL#.
Kill the session using this sql command:
SQL> alter system kill session 'SID,SERIAL#' immediate;
(don't forget to change SID and SERIAL#).
On create schema process you may need to increase the size of SYSAUX tablespace.
After the process completed you can access the enterprise manager on internet browser using this URL:
http://hostname:5500/em
You may also have to run $ORACLE_HOME/root.sh as root to avoid ERROR: NMO not setuid-root. Just answer no if overwrite is asked
Friday, December 10, 2010
PROMPT cost manager and worker requests that have not completed
select wrk.request_id ,
decode(wrk.phase_code,
'C','Complete',
'I','Inactive',
'P','Pending',
'R','Running',
'Unknown')
phase_code,
decode(wrk.status_code,
'C','Normal',
'D','Cancelled',
'E','Error',
'F','Scheduled',
'I','Normal',
'M','No Manager',
'Q','Standby',
'R','Normal',
'S','Suspended',
'T','Terminating',
'U','Disabled',
'W','Paused',
'Z','Waiting'
,'Unknown') status_code,
prg.concurrent_program_name,
substr(argument1,1,10) arg1,
substr(argument2,1,10) arg2,
substr(argument3,1,10) arg3
from fnd_concurrent_programs prg,
fnd_concurrent_requests wrk
where prg.application_id = wrk.program_application_id
and prg.concurrent_program_id =
wrk.concurrent_program_id
and prg.concurrent_program_name IN ('CMCICU','CMCMCW','CMCTCM','CMCACW','CMCCTW','CMCOCW')
and phase_code <> 'C'
decode(wrk.phase_code,
'C','Complete',
'I','Inactive',
'P','Pending',
'R','Running',
'Unknown')
phase_code,
decode(wrk.status_code,
'C','Normal',
'D','Cancelled',
'E','Error',
'F','Scheduled',
'I','Normal',
'M','No Manager',
'Q','Standby',
'R','Normal',
'S','Suspended',
'T','Terminating',
'U','Disabled',
'W','Paused',
'Z','Waiting'
,'Unknown') status_code,
prg.concurrent_program_name,
substr(argument1,1,10) arg1,
substr(argument2,1,10) arg2,
substr(argument3,1,10) arg3
from fnd_concurrent_programs prg,
fnd_concurrent_requests wrk
where prg.application_id = wrk.program_application_id
and prg.concurrent_program_id =
wrk.concurrent_program_id
and prg.concurrent_program_name IN ('CMCICU','CMCMCW','CMCTCM','CMCACW','CMCCTW','CMCOCW')
and phase_code <> 'C'
Tuesday, November 30, 2010
monitor_jdbc_conn.sql
Script
Rem
Rem monitor_jdbc_conn.sql
Rem
Rem NAME
Rem monitor_jdbc_conn.sql
Rem
Rem DESCRIPTION
Rem This shows JDBC connection utilization on database by machine, process
Rem and module.
Rem
Rem NOTES
Rem Runs as apps or apps read only user
Rem
--
-- Set header information for all columns used
--
set lines 132
set pages 500
column module heading "Module Name" format a48;
column machine heading "Machine Name" format a25;
column process heading "Process ID" format a10;
column inst_id heading "Instance ID" format 99;
column username for a10
column sid for 9999
column sql_text for a50
prompt =======================================================
prompt JDBC Connections
select to_char(sysdate, 'dd-mon-yyyy hh24:mi') Time from dual
/
prompt =======================================================
prompt =======================================================
prompt No of Instances
select inst_id instance_id, count(*) from gv$session group by inst_id
/
prompt =======================================================
prompt
prompt JDBC Connection Usage Per JVM Process
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select machine, process, count(*) from gv$session
where program like '%JDBC%'
group by machine, process
order by 1 asc
/
prompt
prompt Connection Usage Per Module
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select count(*), module
from gv$session
where program like '%JDBC%'
group by module
order by 1 asc
/
prompt
prompt Connection Usage Per process and module
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select count(*), machine, process, module
from gv$session
where program like '%JDBC%'
group by machine, process, module
order by 1 asc
/
prompt
prompt Idle connections for more than 3 hours
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select count(*),machine, program
from gv$session
where program like '%JDBC%'
and last_call_et > 3600 *3
group by machine, program
/
prompt
prompt Active connections which are taking more than 10 min to run
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select *
from gv$session
where program like '%JDBC%'
and last_call_et > 600
and status = 'ACTIVE'
order by last_call_et asc
/
prompt
prompt Statements from JDBC connections taking more than 10 min to run
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select s.process, s.sid, t.sql_text
from gv$session s, gv$sql t
where s.sql_address =t.address
and s.sql_hash_value =t.hash_value
and s.program like '%JDBC%'
and s.last_call_et > 600
and s.status = 'ACTIVE'
/
prompt
prompt Active connections which are taking more than 20 min to run
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select *
from gv$session
where program like '%JDBC%'
and last_call_et > 1200
and status = 'ACTIVE'
order by last_call_et asc
/
prompt
prompt Statements from JDBC connections taking more than 20 min to run
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select s.process, s.sid, t.sql_text
from gv$session s, gv$sql t
where s.sql_address =t.address
and s.sql_hash_value =t.hash_value
and s.program like '%JDBC%'
and s.last_call_et > 1200
and s.status = 'ACTIVE'
/
prompt
prompt Active connections which are taking more than 30 min to run
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select *
from gv$session
where program like '%JDBC%'
and last_call_et > 1800
and status = 'ACTIVE'
order by last_call_et asc
/
prompt
prompt Statements from JDBC connections taking more than 30 min to run
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select s.process, s.sid, t.sql_text
from gv$session s, gv$sql t
where s.sql_address =t.address
and s.sql_hash_value =t.hash_value
and s.program like '%JDBC%'
and s.last_call_et > 1800
and s.status = 'ACTIVE'
/
prompt
prompt Inactive connections which last ran fnd_security_pkg.fnd_encrypted_pwd
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select s.sql_hash_value, t.sql_text, s.last_call_et
from gv$session s , gv$sqltext t
where s.username = 'APPLSYSPUB'
and s.sql_hash_value= t.hash_value
and t.sql_text like '%fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3%';
prompt =======================================================
Rem
Rem monitor_jdbc_conn.sql
Rem
Rem NAME
Rem monitor_jdbc_conn.sql
Rem
Rem DESCRIPTION
Rem This shows JDBC connection utilization on database by machine, process
Rem and module.
Rem
Rem NOTES
Rem Runs as apps or apps read only user
Rem
--
-- Set header information for all columns used
--
set lines 132
set pages 500
column module heading "Module Name" format a48;
column machine heading "Machine Name" format a25;
column process heading "Process ID" format a10;
column inst_id heading "Instance ID" format 99;
column username for a10
column sid for 9999
column sql_text for a50
prompt =======================================================
prompt JDBC Connections
select to_char(sysdate, 'dd-mon-yyyy hh24:mi') Time from dual
/
prompt =======================================================
prompt =======================================================
prompt No of Instances
select inst_id instance_id, count(*) from gv$session group by inst_id
/
prompt =======================================================
prompt
prompt JDBC Connection Usage Per JVM Process
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select machine, process, count(*) from gv$session
where program like '%JDBC%'
group by machine, process
order by 1 asc
/
prompt
prompt Connection Usage Per Module
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select count(*), module
from gv$session
where program like '%JDBC%'
group by module
order by 1 asc
/
prompt
prompt Connection Usage Per process and module
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select count(*), machine, process, module
from gv$session
where program like '%JDBC%'
group by machine, process, module
order by 1 asc
/
prompt
prompt Idle connections for more than 3 hours
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select count(*),machine, program
from gv$session
where program like '%JDBC%'
and last_call_et > 3600 *3
group by machine, program
/
prompt
prompt Active connections which are taking more than 10 min to run
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select *
from gv$session
where program like '%JDBC%'
and last_call_et > 600
and status = 'ACTIVE'
order by last_call_et asc
/
prompt
prompt Statements from JDBC connections taking more than 10 min to run
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select s.process, s.sid, t.sql_text
from gv$session s, gv$sql t
where s.sql_address =t.address
and s.sql_hash_value =t.hash_value
and s.program like '%JDBC%'
and s.last_call_et > 600
and s.status = 'ACTIVE'
/
prompt
prompt Active connections which are taking more than 20 min to run
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select *
from gv$session
where program like '%JDBC%'
and last_call_et > 1200
and status = 'ACTIVE'
order by last_call_et asc
/
prompt
prompt Statements from JDBC connections taking more than 20 min to run
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select s.process, s.sid, t.sql_text
from gv$session s, gv$sql t
where s.sql_address =t.address
and s.sql_hash_value =t.hash_value
and s.program like '%JDBC%'
and s.last_call_et > 1200
and s.status = 'ACTIVE'
/
prompt
prompt Active connections which are taking more than 30 min to run
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select *
from gv$session
where program like '%JDBC%'
and last_call_et > 1800
and status = 'ACTIVE'
order by last_call_et asc
/
prompt
prompt Statements from JDBC connections taking more than 30 min to run
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select s.process, s.sid, t.sql_text
from gv$session s, gv$sql t
where s.sql_address =t.address
and s.sql_hash_value =t.hash_value
and s.program like '%JDBC%'
and s.last_call_et > 1800
and s.status = 'ACTIVE'
/
prompt
prompt Inactive connections which last ran fnd_security_pkg.fnd_encrypted_pwd
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select s.sql_hash_value, t.sql_text, s.last_call_et
from gv$session s , gv$sqltext t
where s.username = 'APPLSYSPUB'
and s.sql_hash_value= t.hash_value
and t.sql_text like '%fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3%';
prompt =======================================================
Oracle Applications Technology Stack - Version: 11.5.10.2 to 12.0.6 - Release: 11.5 to 12.0
Java
----------------------------------------------------------------------------------------------------
note 418664.1 Overview of Using Java with Oracle E-Business Suite Release 12
note 455492.1 Using Latest Update of Java 6.0 with Oracle E-Business Suite Release 12
note 384249.1 Using Latest Update of JDK 5.0 with Oracle E-Business Suite Release 12
note 300482.1 Overview of Using Java with Oracle E-Business Suite Release 11i
note 401561.1 Using J2SE Version 6 with Oracle E-Business Suite 11i
note 304099.1 Using J2SE Version 5.0 with Oracle E-Business Suite 11i, Release 11.5.10
note 246105.1 Upgrading to J2SE 1.4.2 with Oracle Applications 11i
note 130091.1 Upgrading Oracle Applications 11i to use JDK 1.3
JRE/Jinitiator/EBS Client Software
----------------------------------------------------------------------------------------------------
note 389422.1 Recommended Browsers for Oracle E-Business Suite Release 12
note 393931.1 Deploying Sun JRE (Native Plug-in) for Windows Clients in Oracle E-Business Suite Release 12
note 285218.1 Recommended Browsers for Oracle Applications 11i
note 290807.1 Deploying Sun JRE (Native Plug-in) for Windows Clients in Oracle E-Business Suite 11i
note 124606.1 Upgrading JInitiator with Oracle E-Business Suite 11i
note 1155883.1 Oracle E-Business Suite Desktop Client Hardware and Software Requirements
Forms
----------------------------------------------------------------------------------------------------
note 437878.1 Upgrading OracleAS 10g Forms and Reports in Oracle E-Business Suite Release 12
note 750359.1 Upgrading to Previous Version of OracleAS 10g Forms and Reports in Oracle E-Business Suite Release 12
note 384241.1 Using Forms Socket Mode with Oracle E-Business Suite Release 12
note 438652.1 R12 Forms Runtime Diagnostics (FRD), Tracing And Logging For Forms In Oracle Applications
note 125767.1 Upgrading Developer 6i with Oracle Applications 11i
note 232313.1 Information on Previous Versions of Developer 6i Patchsets
note 201340.1 Using Forms Listener Servlet with Oracle Applications 11i
note 150168.1 Obtaining Forms Runtime Diagnostics (FRD) In Oracle Applications 11i
note 760250.1 Diagnosing Forms Mouse Focus Problems Using JRE in Release 11i
OracleAS
----------------------------------------------------------------------------------------------------
note 454811.1 Upgrading to the Latest OracleAS 10g 10.1.3.x Patch Set in Oracle E-Business Suite Release 12
note 743518.1 Starting up AS10g services in an EBusiness Suite Release 12 environment
JDBC
----------------------------------------------------------------------------------------------------
note 557194.1 monitor_jdbc_conn.sql - Script to monitor JDBC connections in Apps eBusiness Suite
JVM
----------------------------------------------------------------------------------------------------
note 362851.1 Guidelines to setup the JVM in Apps Ebusiness Suite 11i and R12
note 567551.1 Configuring various JVM tuning parameters for Oracle E-Business suite 11i and R12
note 370583.1 Basic troubleshooting of JVM consuming cpu or too many JDBC connections in Apps 11i
Apache/mod_jserv
----------------------------------------------------------------------------------------------------
note 230688.1 11i Basic Apache/mod_jserv Troubleshooting with Hello.class
Debug/Trace
----------------------------------------------------------------------------------------------------
note 422419.1 R12 - How To Enable and Collect Debug for HTTP, OC4J and OPMN
Note 443671.1 R12 - Collecting Configuration Information for HTTP, OC4J and OPMN
note 427848.1 How to Enable Execution Context ID (ECID) in the R12 access_log
note 455154.1 R12: How To Obtain A Thread Dump When OC4J is shutdown
note 603390.1 How To Create a User Event Trace in R11i / R12
OA Framework
----------------------------------------------------------------------------------------------------
note 391554.1 Oracle Application Framework Documentation Resources, Release 12
note 275880.1 Oracle Application Framework Release 11i Documentation Road Map
note 275875.1 Oracle Application Framework Troubleshooting Release 11i (11.5.10)
note 357597.1 How To Generate A SQL Trace In OA Framework For Oracle Applications
WebCache
----------------------------------------------------------------------------------------------------
note 380486.1 Installing and Configuring Web Cache 10g and Oracle E-Business Suite 12
note 306653.1 Installing and Configuring Web Cache 10.1.2 and Oracle E-Business Suite 11i
JDeveloper
----------------------------------------------------------------------------------------------------
note 330236.1 Configuring JDeveloper For Use With Oracle Applications 11i and R12
note 357218.1 Troubleshooting JDeveloper setup for Oracle Applications
note 787209.1 How to find the correct version of JDeveloper to use with eBusiness Suite 11i or Release 12
Performance
----------------------------------------------------------------------------------------------------
note 163208.1 bde_last_analyzed.sql - Verifies CBO Statistics
note 174605.1 bde_chk_cbo.sql - Reports Database Initialization Parameters related to an Apps 12 or 11i instance
note 396009.1 Database Initialization Parameters for Oracle Applications Release 12
note 216205.1 Database Initialization Parameters for Oracle Applications 11i
note 744143.1 Tuning performance on eBusiness suite
note 169935.1 Troubleshooting Oracle Applications Performance Issues
note 244040.1 Oracle E-Business Suite Recommended Performance Patches
Security
----------------------------------------------------------------------------------------------------
note 738923.1 Oracle E-Business Suite Releases 11i and 12 Critical Patch Update note (January 2009)
note 403537.1 Best Practices for Securing Oracle E-Business Suite Release 12
note 189367.1 Best Practices for Securing the E-Business Suite Release 11i
Ihelp
----------------------------------------------------------------------------------------------------
Note 740834.1 R12: Troubleshooting iHelp
Advanced Configurations
----------------------------------------------------------------------------------------------------
note 380483.1 Oracle E-Business Suite Release 12 Additional Configuration and Deployment Options
note 384248.1 Sharing The Application Tier File System in Oracle E-Business Suite Release 12
note 217368.1 Advanced Configurations and Topologies for Enterprise Deployments of E-Business Suite 11i
note 233428.1 Sharing the Application Tier File System in Oracle Applications 11i
note 233436.1 Installing Oracle Application Server 10g with Oracle E-Business Suite Release 11i
note 364439.1 Tips and Queries for Troubleshooting Advanced Topologies
DMZ
----------------------------------------------------------------------------------------------------
note 380490.1 Oracle E-Business Suite R12 Configuration in a DMZ
note 726953.1 Case History: Implementing a Reverse Proxy Alone in the DMZ Configuration - R12
note 287176.1 DMZ Configuration with Oracle E-Business Suite 11i
note 438744.1 Case History: Implementing a Reverse Proxy Alone in a DMZ Configuration - 11i
note 460564.1 Hints and Tips for Troubleshooting the URL Firewal
Loadbalancer
----------------------------------------------------------------------------------------------------
note 380489.1 Using Load-Balancers with Oracle E-Business Suite Release 12
note 727171.1 Implementing Load Balancing On Oracle E-Business Suite - Documentation For Specific Load Balancer Hardware
note 601694.1 How To Check Session Persistence On BigIP F5 And Cisco Ace Load Balancer Appliances
note 603325.1 Using Cisco ACE Series Application Control Engine with Oracle E-Business Suite Release 12
SSL
----------------------------------------------------------------------------------------------------
note 376700.1 Using SSL with Oracle E-Business Suite Release 12
note 123718.1 11i A Guide to Understanding and Implementing SSL for Oracle Applications
Portal
----------------------------------------------------------------------------------------------------
note 305918.1 Using Oracle Portal 10g with Oracle E-Business Suite Release 11i
SSO/OID
----------------------------------------------------------------------------------------------------
note 376811.1 Integrating Oracle E-Business Suite Release 12 with 10g AS Oracle Internet Directory and Oracle Single Sign-On
note 261914.1 Integrating Oracle E-Business Suite Release 11i with Oracle Internet Directory and Oracle Single Sign-On
note 444573.1 Basic checks for user integration when using Oracle E-Business Suite 11i with Oracle AS 10g
Orace Access Manager / AccessGate
----------------------------------------------------------------------------------------------------
note 975182.1 Integrating Oracle E-Business Suite with Oracle Access Manager using Oracle E-Business Suite AccessGate
note 1077460.1 Troubleshooting Oracle Access Manager and Oracle E-Business Suite AccessGate
Webcenter
----------------------------------------------------------------------------------------------------
note 557221.1 Oracle WebCenter 10g Application Creation and Deployment Guide for Oracle E-Business Suite Release 12
Secure Enterprise Search
----------------------------------------------------------------------------------------------------
note.566097.1 Oracle E-Business Suite Secure Enterprise Search Release Notes, Release 12.1.1
note.744820.1 Oracle E-Business Suite Secure Enterprise Search Best Practices, Release 12
note.462377.1 Installing Oracle E-Business Suite Secure Enterprise Search, Release 12
note.740499.1 Oracle E-Business Suite Secure Enterprise Search Release Notes, Release 12.0.6
note 726239.1 Oracle E-Business Suite Secure Enterprise Search Troubleshooting Guidelines, Release 12
SOA/BPEL/Webservices
----------------------------------------------------------------------------------------------------
note 556540.1 Installing Oracle E-Business Suite Integrated SOA Gateway, Release 12.1
note 565922.1 Oracle E-Business Suite Integrated SOA Gateway Release Notes, Release 12.1.1
note 815196.1 Oracle E-Business Suite Integrated SOA Gateway 12.1.1 Consolidated One-Off
note 726414.1 Oracle E-Business Suite Integrated SOA Gateway Troubleshooting Guide, Release 12
note 755067.1 Using Oracle BPEL 10g with E-Business Suite Release 12.1.1
note 755069.1 EBS R12 Packager and Adapters for External Application Servers
Note 782455.1 How Can I Expose E-Business Suite Integration Interfaces as Web Services in Release 11i and R12.0
MWA
----------------------------------------------------------------------------------------------------
note 782162.1 MWA Troubleshooting Tips for Release 12
note 269991.1 MWA Troubleshooting Tips for Release 11i
Certified/Supported
----------------------------------------------------------------------------------------------------
http://blogs.oracle.com/stevenchan/certifications.html
note 363827.1 Rebaselined Oracle Applications Technology Components for Releases 11.5.7, 11.5.8, 11.5.9, and 11.5.10
note 883202.1 Minimum Baseline Patch Requirements for Extended Support on Oracle E-Business Suite 11.5.10
note 1199724.1 E-Business Suite 11.5.10 Minimum Patch Level and Extended Support Information Center
WEBADI
----------------------------------------------------------------------------------------------------
note 417692.1 Installing, Configuring and Troubleshooting Web ADI (Web Applications Desktop Integrator)
note 452452.1 R12 Installing, Configuring and Troubleshooting Web ADI (Web Applications Desktop Integrator)
note 294739.1 How to enable tracing for WEBADI
note 726989.1 List of Patches in Web Applications Desktop Integrator (Web ADI) for Releases 11i and 12
Diagnostics
----------------------------------------------------------------------------------------------------
note 167000.1 E-Business Suite Diagnostics Installation Guide
note 421245.1 E-Business Suite Diagnostics References for R12
note 179661.1 E-Business Suite Diagnostics 11i Test Catalog
Installation
----------------------------------------------------------------------------------------------------
note 761564.1 Oracle Applications Installation and Upgrade Notes Release 12 (12.1.1) for Linux x86
note 402310.1 Oracle Applications Installation and Upgrade notes Release 12 (12.0) for Linux (32-bit)
note 406982.1 Cloning Oracle Applications Release 12 with Rapid Clone
note 559518.1 Cloning Oracle E-Business Suite Release 12 RAC-Enabled Systems with Rapid Clone
note 421409.1 Unbreakable Linux enviroment check before R12 install
note 458533.1 How to Enable Enterprise Manager on the Oracle E-Business Suite Release 12
note 603716.1 Using AS10g AS Control with eBusiness Suite Rel 12
note 452120.1 How to locate the log files and troubleshoot RapidWiz for R12
note 316806.1 Oracle Applications Installation Update notes, Release 11i (11.5.10.2)
note:230672.1 Cloning Oracle Applications Release 11i with Rapid Clone
note 207296.1 How to Find out Linux Version Information?
Database
----------------------------------------------------------------------------------------------------
note:454750.1 Oracle Apps Release 12 with Oracle Database 10.2.0 interoperability notes
note 735276.1 Interoperability notes E-Business Suite R12 with Oracle Database 11gR1
note 388577.1 Configuring Oracle Applications Release 12 with 10g R2 RAC
note 466649.1 Using Oracle 11g Release 1 Real Application Clusters and Automatic Storage Management with Oracle E-Business Suite Release 12
note 216550.1 Oracle Applications Release 11i with Oracle9i Release 2 (9.2.0)
note 362203.1 Oracle Applications Release 11i with Oracle 10g Release 2 (10.2.0)
note 452783.1 Oracle Applications Release 11i with Oracle 11g Release 1 (11.1.0)
note 362135.1 Configuring Oracle Applications Release 11i with 10g R2 RAC and ASM
note 554539.1 Using Database Partitioning with Oracle E-Business Suite
----------------------------------------------------------------------------------------------------
note 418664.1 Overview of Using Java with Oracle E-Business Suite Release 12
note 455492.1 Using Latest Update of Java 6.0 with Oracle E-Business Suite Release 12
note 384249.1 Using Latest Update of JDK 5.0 with Oracle E-Business Suite Release 12
note 300482.1 Overview of Using Java with Oracle E-Business Suite Release 11i
note 401561.1 Using J2SE Version 6 with Oracle E-Business Suite 11i
note 304099.1 Using J2SE Version 5.0 with Oracle E-Business Suite 11i, Release 11.5.10
note 246105.1 Upgrading to J2SE 1.4.2 with Oracle Applications 11i
note 130091.1 Upgrading Oracle Applications 11i to use JDK 1.3
JRE/Jinitiator/EBS Client Software
----------------------------------------------------------------------------------------------------
note 389422.1 Recommended Browsers for Oracle E-Business Suite Release 12
note 393931.1 Deploying Sun JRE (Native Plug-in) for Windows Clients in Oracle E-Business Suite Release 12
note 285218.1 Recommended Browsers for Oracle Applications 11i
note 290807.1 Deploying Sun JRE (Native Plug-in) for Windows Clients in Oracle E-Business Suite 11i
note 124606.1 Upgrading JInitiator with Oracle E-Business Suite 11i
note 1155883.1 Oracle E-Business Suite Desktop Client Hardware and Software Requirements
Forms
----------------------------------------------------------------------------------------------------
note 437878.1 Upgrading OracleAS 10g Forms and Reports in Oracle E-Business Suite Release 12
note 750359.1 Upgrading to Previous Version of OracleAS 10g Forms and Reports in Oracle E-Business Suite Release 12
note 384241.1 Using Forms Socket Mode with Oracle E-Business Suite Release 12
note 438652.1 R12 Forms Runtime Diagnostics (FRD), Tracing And Logging For Forms In Oracle Applications
note 125767.1 Upgrading Developer 6i with Oracle Applications 11i
note 232313.1 Information on Previous Versions of Developer 6i Patchsets
note 201340.1 Using Forms Listener Servlet with Oracle Applications 11i
note 150168.1 Obtaining Forms Runtime Diagnostics (FRD) In Oracle Applications 11i
note 760250.1 Diagnosing Forms Mouse Focus Problems Using JRE in Release 11i
OracleAS
----------------------------------------------------------------------------------------------------
note 454811.1 Upgrading to the Latest OracleAS 10g 10.1.3.x Patch Set in Oracle E-Business Suite Release 12
note 743518.1 Starting up AS10g services in an EBusiness Suite Release 12 environment
JDBC
----------------------------------------------------------------------------------------------------
note 557194.1 monitor_jdbc_conn.sql - Script to monitor JDBC connections in Apps eBusiness Suite
JVM
----------------------------------------------------------------------------------------------------
note 362851.1 Guidelines to setup the JVM in Apps Ebusiness Suite 11i and R12
note 567551.1 Configuring various JVM tuning parameters for Oracle E-Business suite 11i and R12
note 370583.1 Basic troubleshooting of JVM consuming cpu or too many JDBC connections in Apps 11i
Apache/mod_jserv
----------------------------------------------------------------------------------------------------
note 230688.1 11i Basic Apache/mod_jserv Troubleshooting with Hello.class
Debug/Trace
----------------------------------------------------------------------------------------------------
note 422419.1 R12 - How To Enable and Collect Debug for HTTP, OC4J and OPMN
Note 443671.1 R12 - Collecting Configuration Information for HTTP, OC4J and OPMN
note 427848.1 How to Enable Execution Context ID (ECID) in the R12 access_log
note 455154.1 R12: How To Obtain A Thread Dump When OC4J is shutdown
note 603390.1 How To Create a User Event Trace in R11i / R12
OA Framework
----------------------------------------------------------------------------------------------------
note 391554.1 Oracle Application Framework Documentation Resources, Release 12
note 275880.1 Oracle Application Framework Release 11i Documentation Road Map
note 275875.1 Oracle Application Framework Troubleshooting Release 11i (11.5.10)
note 357597.1 How To Generate A SQL Trace In OA Framework For Oracle Applications
WebCache
----------------------------------------------------------------------------------------------------
note 380486.1 Installing and Configuring Web Cache 10g and Oracle E-Business Suite 12
note 306653.1 Installing and Configuring Web Cache 10.1.2 and Oracle E-Business Suite 11i
JDeveloper
----------------------------------------------------------------------------------------------------
note 330236.1 Configuring JDeveloper For Use With Oracle Applications 11i and R12
note 357218.1 Troubleshooting JDeveloper setup for Oracle Applications
note 787209.1 How to find the correct version of JDeveloper to use with eBusiness Suite 11i or Release 12
Performance
----------------------------------------------------------------------------------------------------
note 163208.1 bde_last_analyzed.sql - Verifies CBO Statistics
note 174605.1 bde_chk_cbo.sql - Reports Database Initialization Parameters related to an Apps 12 or 11i instance
note 396009.1 Database Initialization Parameters for Oracle Applications Release 12
note 216205.1 Database Initialization Parameters for Oracle Applications 11i
note 744143.1 Tuning performance on eBusiness suite
note 169935.1 Troubleshooting Oracle Applications Performance Issues
note 244040.1 Oracle E-Business Suite Recommended Performance Patches
Security
----------------------------------------------------------------------------------------------------
note 738923.1 Oracle E-Business Suite Releases 11i and 12 Critical Patch Update note (January 2009)
note 403537.1 Best Practices for Securing Oracle E-Business Suite Release 12
note 189367.1 Best Practices for Securing the E-Business Suite Release 11i
Ihelp
----------------------------------------------------------------------------------------------------
Note 740834.1 R12: Troubleshooting iHelp
Advanced Configurations
----------------------------------------------------------------------------------------------------
note 380483.1 Oracle E-Business Suite Release 12 Additional Configuration and Deployment Options
note 384248.1 Sharing The Application Tier File System in Oracle E-Business Suite Release 12
note 217368.1 Advanced Configurations and Topologies for Enterprise Deployments of E-Business Suite 11i
note 233428.1 Sharing the Application Tier File System in Oracle Applications 11i
note 233436.1 Installing Oracle Application Server 10g with Oracle E-Business Suite Release 11i
note 364439.1 Tips and Queries for Troubleshooting Advanced Topologies
DMZ
----------------------------------------------------------------------------------------------------
note 380490.1 Oracle E-Business Suite R12 Configuration in a DMZ
note 726953.1 Case History: Implementing a Reverse Proxy Alone in the DMZ Configuration - R12
note 287176.1 DMZ Configuration with Oracle E-Business Suite 11i
note 438744.1 Case History: Implementing a Reverse Proxy Alone in a DMZ Configuration - 11i
note 460564.1 Hints and Tips for Troubleshooting the URL Firewal
Loadbalancer
----------------------------------------------------------------------------------------------------
note 380489.1 Using Load-Balancers with Oracle E-Business Suite Release 12
note 727171.1 Implementing Load Balancing On Oracle E-Business Suite - Documentation For Specific Load Balancer Hardware
note 601694.1 How To Check Session Persistence On BigIP F5 And Cisco Ace Load Balancer Appliances
note 603325.1 Using Cisco ACE Series Application Control Engine with Oracle E-Business Suite Release 12
SSL
----------------------------------------------------------------------------------------------------
note 376700.1 Using SSL with Oracle E-Business Suite Release 12
note 123718.1 11i A Guide to Understanding and Implementing SSL for Oracle Applications
Portal
----------------------------------------------------------------------------------------------------
note 305918.1 Using Oracle Portal 10g with Oracle E-Business Suite Release 11i
SSO/OID
----------------------------------------------------------------------------------------------------
note 376811.1 Integrating Oracle E-Business Suite Release 12 with 10g AS Oracle Internet Directory and Oracle Single Sign-On
note 261914.1 Integrating Oracle E-Business Suite Release 11i with Oracle Internet Directory and Oracle Single Sign-On
note 444573.1 Basic checks for user integration when using Oracle E-Business Suite 11i with Oracle AS 10g
Orace Access Manager / AccessGate
----------------------------------------------------------------------------------------------------
note 975182.1 Integrating Oracle E-Business Suite with Oracle Access Manager using Oracle E-Business Suite AccessGate
note 1077460.1 Troubleshooting Oracle Access Manager and Oracle E-Business Suite AccessGate
Webcenter
----------------------------------------------------------------------------------------------------
note 557221.1 Oracle WebCenter 10g Application Creation and Deployment Guide for Oracle E-Business Suite Release 12
Secure Enterprise Search
----------------------------------------------------------------------------------------------------
note.566097.1 Oracle E-Business Suite Secure Enterprise Search Release Notes, Release 12.1.1
note.744820.1 Oracle E-Business Suite Secure Enterprise Search Best Practices, Release 12
note.462377.1 Installing Oracle E-Business Suite Secure Enterprise Search, Release 12
note.740499.1 Oracle E-Business Suite Secure Enterprise Search Release Notes, Release 12.0.6
note 726239.1 Oracle E-Business Suite Secure Enterprise Search Troubleshooting Guidelines, Release 12
SOA/BPEL/Webservices
----------------------------------------------------------------------------------------------------
note 556540.1 Installing Oracle E-Business Suite Integrated SOA Gateway, Release 12.1
note 565922.1 Oracle E-Business Suite Integrated SOA Gateway Release Notes, Release 12.1.1
note 815196.1 Oracle E-Business Suite Integrated SOA Gateway 12.1.1 Consolidated One-Off
note 726414.1 Oracle E-Business Suite Integrated SOA Gateway Troubleshooting Guide, Release 12
note 755067.1 Using Oracle BPEL 10g with E-Business Suite Release 12.1.1
note 755069.1 EBS R12 Packager and Adapters for External Application Servers
Note 782455.1 How Can I Expose E-Business Suite Integration Interfaces as Web Services in Release 11i and R12.0
MWA
----------------------------------------------------------------------------------------------------
note 782162.1 MWA Troubleshooting Tips for Release 12
note 269991.1 MWA Troubleshooting Tips for Release 11i
Certified/Supported
----------------------------------------------------------------------------------------------------
http://blogs.oracle.com/stevenchan/certifications.html
note 363827.1 Rebaselined Oracle Applications Technology Components for Releases 11.5.7, 11.5.8, 11.5.9, and 11.5.10
note 883202.1 Minimum Baseline Patch Requirements for Extended Support on Oracle E-Business Suite 11.5.10
note 1199724.1 E-Business Suite 11.5.10 Minimum Patch Level and Extended Support Information Center
WEBADI
----------------------------------------------------------------------------------------------------
note 417692.1 Installing, Configuring and Troubleshooting Web ADI (Web Applications Desktop Integrator)
note 452452.1 R12 Installing, Configuring and Troubleshooting Web ADI (Web Applications Desktop Integrator)
note 294739.1 How to enable tracing for WEBADI
note 726989.1 List of Patches in Web Applications Desktop Integrator (Web ADI) for Releases 11i and 12
Diagnostics
----------------------------------------------------------------------------------------------------
note 167000.1 E-Business Suite Diagnostics Installation Guide
note 421245.1 E-Business Suite Diagnostics References for R12
note 179661.1 E-Business Suite Diagnostics 11i Test Catalog
Installation
----------------------------------------------------------------------------------------------------
note 761564.1 Oracle Applications Installation and Upgrade Notes Release 12 (12.1.1) for Linux x86
note 402310.1 Oracle Applications Installation and Upgrade notes Release 12 (12.0) for Linux (32-bit)
note 406982.1 Cloning Oracle Applications Release 12 with Rapid Clone
note 559518.1 Cloning Oracle E-Business Suite Release 12 RAC-Enabled Systems with Rapid Clone
note 421409.1 Unbreakable Linux enviroment check before R12 install
note 458533.1 How to Enable Enterprise Manager on the Oracle E-Business Suite Release 12
note 603716.1 Using AS10g AS Control with eBusiness Suite Rel 12
note 452120.1 How to locate the log files and troubleshoot RapidWiz for R12
note 316806.1 Oracle Applications Installation Update notes, Release 11i (11.5.10.2)
note:230672.1 Cloning Oracle Applications Release 11i with Rapid Clone
note 207296.1 How to Find out Linux Version Information?
Database
----------------------------------------------------------------------------------------------------
note:454750.1 Oracle Apps Release 12 with Oracle Database 10.2.0 interoperability notes
note 735276.1 Interoperability notes E-Business Suite R12 with Oracle Database 11gR1
note 388577.1 Configuring Oracle Applications Release 12 with 10g R2 RAC
note 466649.1 Using Oracle 11g Release 1 Real Application Clusters and Automatic Storage Management with Oracle E-Business Suite Release 12
note 216550.1 Oracle Applications Release 11i with Oracle9i Release 2 (9.2.0)
note 362203.1 Oracle Applications Release 11i with Oracle 10g Release 2 (10.2.0)
note 452783.1 Oracle Applications Release 11i with Oracle 11g Release 1 (11.1.0)
note 362135.1 Configuring Oracle Applications Release 11i with 10g R2 RAC and ASM
note 554539.1 Using Database Partitioning with Oracle E-Business Suite
Sunday, November 28, 2010
Export/import process for R12 using 11gR1 [ID 741818.1]
Export/import process for R12 using 11gR1 [ID 741818.1]
--------------------------------------------------------------------------------
Modified 19-SEP-2010 Type WHITE PAPER Status PUBLISHED
Export/Import Process for Oracle E-Business Suite Release 12
Database Instances Using Oracle Database 11g Release 1 or 11g Release 2
December 2009
--------------------------------------------------------------------------------
This document describes the process of re-creating an existing Applications Release 12.x database instance using the datapump utilities. The most current version of these notes is Note 741818.1 on My Oracle Support. There is a change log at the end of this document.
The datapump utilities allow you to move existing data in Oracle format to and from Oracle databases. For example, datapump export files can archive database data, or move data among different Oracle databases that run on the same or different operating systems. This document assumes that you are already familiar with datapump.
There are special considerations when exporting or importing an Applications Release 12 database instance. This process consists of five discrete steps. Each step is covered in a separate section in this document.
Section 1: Prepare the source system
Tasks that must be performed to prepare the source system for the database export
Section 2: Prepare a target Release 12 database instance
Tasks for creating an empty database instance in preparation for import
Section 3: Export the source Release 12 database instance
Tasks that must be performed to produce a valid export of an Applications Release 12 database instance
Section 4: Import the Release 12 database instance
Tasks for running the import utility
Section 5: Update the imported Release 12 database instance
Tasks that must be performed to restore the imported Applications Release 12 database instance to a fully functional state
The source (export from) ORACLE_HOME directories must be Oracle Database 10g Release 2 (10.2.0) or Oracle Database 11g (11.x). The target (import to) ORACLE_HOME directories must be Oracle Database 11g (11.x). You may not downgrade using export/import.
The export/import process may use either or both the datapump utilities (expdp/impdp) and the traditional export/import (exp/imp). For more information, read Oracle Database Utilities 11g Release 1 (11.1) or Oracle Database Utilities 11g Release 2 (11.2).
Attention: This document uses UNIX/Linux syntax when describing directory structures. However, it applies to Windows servers as well. Where there is a significant difference in tasks for Windows, specific instructions are given.
Some of the tasks in this document affect the APPL_TOP of one or more application server tiers. Those tasks require that the Applications file system environment be enabled by running the APPSORA.env file (for UNIX or Linux) or the envshell.cmd file (for Windows) prior to performing the tasks. Other tasks affect the Applications database instance. Those tasks require that the Oracle 11g environment be enabled by running the [ORACLE_SID].env/cmd file under the Oracle 11g Oracle home on the database server node prior to performing the tasks. In addition, you may have more than one Oracle home installed on the database server node, so it is important that you run the correct [ORACLE_SID].env/cmd file before performing tasks that affect the database instance. Read the instructions carefully to determine which environment should be enabled for each step.
Attention: This document assumes that the source and target application server tiers are the same. Creating new application server tiers for the target environment has to be done either before starting or after completing all the steps in this document. Then, update and run AutoConfig for the source database and application server tiers to enable the source environment.
Attention: If you are using Oracle Database Vault, refer to Note 822048.1 before performing any step in this document.
--------------------------------------------------------------------------------
Section 1: Prepare the source system
This section describes how to ensure that you have the required patches, create your export file, and capture important information that is required to import your database.
Apply prerequisite patches
Ensure that you have applied steps 1 and 2 in Section 1 of the Oracle E-Business Suite Release 12 with Oracle Database 11g Release 1 (11.1.0) Interoperability Notes if you are importing an E-Business Suite Release 12.0 into 11.1.0, Oracle E-Business Suite Release 12.1 with Oracle Database 11g Release 1 (11.1.0) Interoperability Notes if you are importing an E-Business Suite Release 12.1 into 11.1.0, or Oracle E-Business Suite Release 12 with Oracle Database 11g Release 2 (11.2.0) Interoperability Notes if you are importing an E-Business Suite Release 12.x into 11.2.
When performing the AutoConfig prerequisite instructions, ensure that steps 3.1 and 3.2.1 of the Using AutoConfig to Manage System Configurations in Oracle E-Business Suite Release 12 document are completed. The other steps in 3.2 are not necessary as they will be done at the target side.
Apply the Applications consolidated export/import utility patch
Apply patch 7120092 to the source administration server node. This patch provides several SQL scripts that facilitate exporting and importing an Applications database instance.
Create a working directory
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
Generate target database instance creation script aucrdb.sql
The target database instance must be created with the same tablespace structure as the source database instance. The export/import patch provides the auclondb.sql script which generates the aucrdb.sql script, which you use to create the target database instance with the appropriate tablespace and file structure. The script converts all tablespaces except for SYSTEM to locally managed tablespaces with auto segment space management, if they are not already so.
On the source administration server node, use SQL*Plus to connect to the database as SYSTEM and run the $AU_TOP/patch/115/sql/auclondb.sql script. It creates aucrdb.sql in the current directory.
$ sqlplus system/[system password] \
@$AU_TOP/patch/115/sql/auclondb.sql 11
Record Advanced Queue settings
Advanced Queue settings are not propagated in the target database instance during the export/import process. Therefore, you must record them beforehand and enable them in the target database instance afterwards. The export/import patch contains auque1.sql, which generates a script called auque2.sql. You can use auque2.sql to enable the settings in the target database instance.
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. Then, on the source database server node, as the owner of the source database server file system and database instance, use SQL*Plus to connect to the source database as sysdba and run the auque1.sql script. It generates auque2.sql.
$ sqlplus /nolog
SQL> connect / as sysdba;
SQL> @auque1.sql
Create parameter file for tables with long columns (conditional)
If the source database is Oracle Database 10g Release 2, tables with long columns may not propagate properly in datapump. Therefore, they have to be migrated separately using the traditional export/import utilities.
Copy the aulong.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. Then, on the source database server node, as the owner of the source database server file system and database instance, use SQL*Plus to connect to the source database as sysdba and run the aulong.sql script. It generates aulongexp.dat.
$ sqlplus /nolog
SQL> connect system/[system password];
SQL> @aulong.sql
Remove rebuild index parameter in spatial indexes
Ensure that you do not have the rebuild index parameter in the spatial indexes. To see if you have any rebuild index parameters, on the source database server node, as the owner of the source database server file system and database instance, use SQL*Plus to connect to the source database as sysdba and run the following command:
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.
Deregister the current database server (conditional)
If you plan to change the database port, host, SID, or database name parameter on the database server, deregister the current database server node by running 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
contextfile=$CONTEXT_FILE -removeserver
--------------------------------------------------------------------------------
Section 2: Prepare a target Release 12 database instance
This section describes how to create the empty target database and populate it with all of the required system objects prior to running import.
The Oracle home of the target database instance can be the same Oracle home that the source database instance uses, or it can be different (on another machine running a different operating system, for example), as long as it uses Oracle Database 11g Release 1 Enterprise Edition.
Create target Oracle 11g Oracle home (conditional)
If you want the target Oracle 11g Oracle home to be separate from the source Oracle home, you must create it now. Perform the steps in the "Database Installation" subsection of Section 1 of the Oracle E-Business Suite Release 12 with Oracle Database 11g Release 1 (11.1.0) Interoperability Notes, Oracle E-Business Suite Release 12.1 with Oracle Database 11g Release 1 (11.1.0) Interoperability Notes, or Oracle E-Business Suite Release 12 with Oracle Database 11g Release 2 (11.2.0) Interoperability Notes.
Modify sqlnet.ora file (Windows only)
If the target database server node is running Windows, add the following line to the sqlnet.ora file in the %ORACLE_HOME%\network\admin\[SID] directory, if it does not already exist:
SQLNET.AUTHENTICATION_SERVICES=(NTS)
Create the target initialization parameter file and CBO parameter file
The initialization parameter file (init[SID].ora) and cost-based optimizer (CBO) parameter file (ifilecbo.ora) are located in the $ORACLE_HOME/dbs directory on the source database server node. Copy both files to the Oracle 11g $ORACLE_HOME/dbs directory on the target database server node.
Refer to Database Initialization Parameters for Oracle Applications Release 12 and update both the init.ora and ifilecbo.ora files with any necessary changes. You may also need to update initialization parameters involving the db_name, control_files, and directory structures.
Ensure that the undo_tablespace parameter in the initialization parameter file of the target database instance matches with the default undo tablespace set in the aucrdb.sql script.
Ignore the initialization parameters that pertain to the native compilation of PL/SQL code. You will be instructed to add them later, if necessary.
Create a working directory
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
Create the target database instance
Copy the aucrdb.sql script, generated in Section 1, from the source administration server node to the working directory in the target database server node. Then update the script on the target database server node with any necessary changes to the directory structures for the log file(s), data file(s), or tablespaces, reflecting the layout of the target database server node. You may enlarge the file sizes to ensure that tablespaces do not run out. If the target database server node is running Windows, update the directory structure from UNIX/Linux format to Windows format or vice versa.
Make sure that the environment of your session on the target database server node is set up properly for the target database instance, especially the ORACLE_HOME, ORACLE_SID, and ORA_NLS10 environment settings. (ORACLE_SID must be set to the same value as the db_name parameter in the init[SID].ora file.) Then, use the following commands to run aucrdb.sql and create the target database instance:
$ sqlplus /nolog
SQL> connect / as sysdba;
SQL> spool aucrdb.log;
For UNIX or Linux:
SQL> startup nomount;
SQL> @aucrdb.sql
SQL> exit;
For Windows:
SQL> startup nomount pfile=%ORACLE_HOME%\dbs\init%ORACLE_SID%.ora
SQL> @aucrdb.sql
SQL> exit;
If PL/SQL of the source database was natively compiled, see the "Compiling PL/SQL Code for Native Execution" section of Chapter 11 of Oracle Database PL/SQL User's Guide and Reference 11g Release 1 (11.1) or Chapter 12 of Oracle Database PL/SQL Language Reference 11g Release 2 (11.2) for instructions on how to natively compile PL/SQL in the target database. Add the parameters that pertain to the native compilation where specified. Do not use the natively compiled code generated by the source database. Oracle does not support switching the PL/SQL compilation mode from interpreted to native (and vice-versa) for an export/import. Exporting/importing using native mode takes significantly more time than interpreted mode.
When the target database instance has been created, restart the database instance.
Note: You may modify the sizes of the database files in aucrdb.sql to ensure enough tablespace. Querying the dba_free_space and dba_data_files tables in the source database can give you an idea of how much tablespace is required. However, this is no guarantee as the space requirements may change depending on the extent sizes used by each object. Not having enough tablespace will cause failures as well as a major performance degradation in the import run.
Copy database preparation scripts to target Oracle home
The export/import patch that you applied to the source administration server node in Section 1 contain four scripts that are needed on the target database server node. Copy the following files from the $AU_TOP/patch/115/sql directory of the source administration server node to the working directory in the target database server node: audb1110.sql/audb1120.sql, ausy1110.sql/ausy1120.sql, aujv1110.sql/aujv1120.sql, and aumsc1110.sql/aumsc1120.sql (UNIX or Linux) or audb1110_nt.sql/audb1120_nt.sql, ausy1110_nt.sql/ausy1120_nt.sql, aujv1110_nt.sql/aujv1120_nt.sql, and aumsc1110_nt.sql/aumsc1120_nt.sql (Windows).
As you run each of the next four steps, note the following:
The remarks section at the beginning of each script contains additional information.
Each script creates a log file in the current directory.
Set up the SYS schema
The audb1110.sql, audb1120.sql, audb1110_nt.sql, or audb1120_nt.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 audb1110.sql/audb1120.sql (UNIX/Linux) or audb1110_nt.sql/audb1120_nt.sql (Windows).
Here is an example on UNIX or Linux for 11.2.0:
$ sqlplus "/ as sysdba" @audb1120.sql
Set up the SYSTEM schema
The ausy1110.sql, ausy1120.sql, ausy1110_nt.sql, or ausy1120_nt.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 ausy1110.sql/ausy1120.sql (UNIX/Linux) or ausy1110_nt.sql/ausy1120_nt.sql (Windows).
Here is an example on UNIX or Linux for 11.2.0:
$ sqlplus system/[system password] @ausy1120.sql
Install Java Virtual Machine
The aujv1110.sql, aujv1120.sql, aujv1110_nt.sql, or aujv1120_nt.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 aujv1110.sql/aujv1120.sql (UNIX/Linux) or aujv1110_nt.sql/aujv1120_nt.sql (Windows).
Here is an example on UNIX or Linux for 11.2.0:
$ sqlplus system/[system password] @aujv1120.sql
Attention: This script can be run only once in a given database instance, because the scripts that it calls are not rerunnable.
Install other required components
The aumsc1110.sql, aumsc1120.sql, aumsc1110_nt.sql, or aumsc1120_nt.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 run aumsc1110.sql/aumsc1120.sql (UNIX/Linux) or aumsc1110_nt.sql/aumsc1120_nt.sql (Windows). You must pass the following arguments to the script, in the order specified:
Argument Value
remove context? FALSE
SYSAUX tablespace SYSAUX
temporary tablespace TEMP
Here is an example on UNIX or Linux for 11.2.0:
$ sqlplus system/[system password] \
@aumsc1120.sql FALSE SYSAUX TEMP
Attention: All of the components are created in the SYSAUX tablespace regardless of where it was installed in the source database.
Set CTXSYS parameter (conditional)
If your target database is 11g Release 2 (11.2.0), use SQL*Plus to connect to the database as SYSDBA and run the following command:
$ sqlplus "/ as sysdba"
SQL> exec ctxsys.ctx_adm.set_parameter('file_access_role', 'public');
Disable automatic gathering of statistics
Copy $APPL_TOP/admin/adstats.sql from the administration server node to the target database server node. 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> alter system enable restricted session;
SQL> @adstats.sql
$ sqlplus "/ as sysdba"
SQL> alter system disable restricted session;
SQL> exit;
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.
--------------------------------------------------------------------------------
Section 3: Export the source Release 12 database instance
This section describes how to ensure that you have the required patches, create your export file, and capture important information that is required to import your database.
Create the export parameter file
A template for the export parameter file has been included as part of the the export/import patch. Copy $AU_TOP/patch/115/import/auexpdp.dat from the source administration server node to the working directory in the source database server node. Use a text editor to modify the file to reflect the source environment and other customized parameters.
The customizable parameters are:
Parameter Description Template Value
directory directory where the export dump files will be created dmpdir
dumpfile export dump file name(s) aexp%U.dmp
filesize export dump file size 1GB
log log file name expdpapps.log
interMedia, OLAP, and Data Mining schemas are not exported. The aumsc1110.sql script creates these schemas in the target database. Ensure that the schema names in the exclude parameters reflect those in your database.
If your source database is Oracle Database 11g (11.x), add the following line to the parameter file:
QUERY=applsys.wf_item_attribute_values:"where item_type!='WFERROR' and
name != 'EVENT_MESSAGE'"
Create a directory in the system schema that corresponds to the directory specified in the template. Here is an example of how to create a directory named dmpdir:
$ sqlplus system/[system password]
SQL> create directory dmpdir as '/u01/expimp';
Do not change the other parameters.
The export process uses as many of the listed file names as necessary to hold the exported data. You must ensure that the number of dump files specified, as well as the size of each dump file, is sufficient to contain all the data in your source database instance.
Shut down Applications server processes
Shut down all Applications server processes except the database and the Net8 listener for the database. Users cannot use the Applications until the import is completed.
Grant privilege to source system schema
Grant the exempt access policy privilege to system by using SQL*Plus to connect to the database as SYSDBA and run the following command:
SQL> grant EXEMPT ACCESS POLICY to system;
Export the Applications database instance
Start an export session on the source database server node using the customized export parameter file.
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]
Typically, the export runs for several hours.
Export tables with long columns (conditional)
If the source database is Oracle Database 10g Release 2 (10.2.0), start an export session on the source database server node using the customized aulongexp.dat file generated in Section 1. Use the following command:
$ exp parfile=aulongexp.dat
Export tables with XML type columns (conditional)
If the source database is Oracle Database 10g Release 2 (10.2.0), copy $AU_TOP/patch/115/import/auxmlexp.dat from the source administration server to the working directory in the source database server node. Start an export session on the source database server node using the following command:
$ exp parfile=auxmlexp.dat
Revoke privilege from source 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;
--------------------------------------------------------------------------------
Section 4: Import the Release 12 database instance
This section describes how to use the import utility to load the Oracle Applications data into the target database.
Create the import parameter files
Copy auimpdp.dat, aufullimp.dat, and auimpusr.dat from the $AU_TOP/patch/115/import directory in the source administration server node to the working directory in the target database server node. Make sure that the directory, dumpfile, and logfile parameters in auimpdp.dat and auimpusr.dat are set properly.
Create a directory in the system schema with the name set to the directory specified in the template and the path set to where the export dump files will reside. Here is an example of how to create a directory named dmpdir:
$ sqlplus system/[system password]
SQL> create directory dmpdir as '/u01/expimp';
Save the changed file.
Copy the export dump files
Copy the export dump files from the source database server node to the working directory in the target database server node.
Import the users into the target database (conditional)
If the source database is Oracle Database 10g Release 2 (10.2.0), start an import session on the target database server node using the customized import parameter file. Use the following command:
$ impdp system/[system password] parfile=auimpusr.dat
Import tables with long columns into the target database (conditional)
If the source database is Oracle Database 10g Release 2 (10.2.0), modify the aufullimp.dat file with the following:
Set userid to "sys/[sys password] as sysdba".
Set file to the dump file containing the long tables (longexp by default).
Set the log file appropriately.
Leave the ignore parameter commented out.
Import the tables using the following command:
$ imp parfile=aufullimp.dat
Attention: You will get failures for the triggers as the dependent tables have not yet been imported.
Import the Applications database instance
If your source database is Oracle Database 11g (11.x), remove or comment out all the exclude parameters in the auimpdp.dat parameter file. If your source database is Oracle Database 10g Release 2 (10.2.0), leave the parameter file as is. Start an import session on the target database server node using the auimpdp.dat parameter file.
If the target database is 11.1.0, use the following command:
$ impdp system/[system password] parfile=auimpdp.dat
If the target database is 11.2.0, use the following command:
$ impdp "'/ as sysdba'" parfile=[export parameter file name]
Typically, import runs for several hours.
Import triggers into the target database (conditional)
If the source database is Oracle Database 10g Release 2 (10.2.0), modify the aufullimp.dat file with the following:
Set userid to "sys/[sys password] as sysdba".
Set file to the dump file containing the long tables (longexp by default).
Change the log file name.
Uncomment the ignore parameter.
Add a line with the parameter "rows=n".
Start an import session on the target database server node using the customized import parameter file. Use the following command:
$ imp parfile=aufullimp.dat
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;
--------------------------------------------------------------------------------
Section 5: Update the imported Release 12 database instance
This section describes how to recreate the database objects and relationships that are not handled by the export and import utilities.
Reset Advanced Queues
Copy the auque2.sql script that was generated in Section 1 from the working directory in the source database server node to the working directory in the target database server node. Then, on the target database server node, as the owner of the Oracle 11g file system and database instance, use SQL*Plus to connect to the target database as SYSDBA and run the auque2.sql script to enable the Advanced Queue settings that were lost during the export/import process. The script creates a log file in the current directory.
$ sqlplus /nolog
SQL> connect / as sysdba;
SQL> @auque2.sql
Start the new database listener (conditional)
If the Oracle Net listener for the database instance in the new Oracle home has not been started, you must start it now. Since AutoConfig has not yet been implemented, start the listener with the lsnrctl executable (UNIX/Linux) or Services (Windows). See the Oracle Database Net Services Administrator's Guide, 11g Release 1 (11.1) or Oracle Database Net Services Administrator's Guide, 11g Release 2 (11.2) for more information.
Attention: Set the TNS_ADMIN environment variable to the directory where you created your listener.ora and tnsnames.ora files.
Run adgrants.sql
Copy $APPL_TOP/admin/adgrants.sql (adgrants_nt.sql for Windows) from the administration server node to the working directory in the database server node. Use SQL*Plus to connect to the database as SYSDBA and run the script using the following command:
$ sqlplus "/ as sysdba" @adgrants.sql (or adgrants_nt.sql) \
[APPS schema name]
Note: Verify the usage of adgrants.sql in the adgrants.sql script. Older versions of adgrants.sql require the APPLSYS schema name parameter to be passed instead of APPS.
Grant create procedure privilege on CTXSYS
Copy $AD_TOP/patch/115/sql/adctxprv.sql from the administration server node to the database server node. Use SQL*Plus to connect to the database as APPS and run the script using the following command:
$ sqlplus apps/[APPS password] @adctxprv.sql \
[SYSTEM password] CTXSYS
Apply patch 6494466 (conditional)
If the target database is Windows and the source is not, apply patch 6494466 on the target database tier. Create the appsutil directory if needed.
Implement and run AutoConfig
Implement and run AutoConfig in the new Oracle home on the database server node. If the database listener of the new Oracle home is defined differently than the old Oracle home, you must also run AutoConfig on each application tier server node to update the system with the new listener.
See Using AutoConfig to Manage System Configurations in Oracle E-Business Suite Release 12 on My Oracle Support, especially section 3.2, for instructions on how to implement and run AutoConfig.
Shut down all processes, including the database and the listener, and restart them to load the new environment settings.
Gather statistics for SYS schema
Use SQL*Plus to connect to the database as SYSDBA and use the following commands to restart the database in restricted mode, run adstats.sql, and restart the database in normal mode:
$ sqlplus "/ as sysdba"
SQL> alter system enable restricted session;
SQL> @adstats.sql
$ sqlplus "/ as sysdba"
SQL> alter system disable restricted session;
SQL> exit;
Attention: Make sure that you have at least 1.5 GB of free default temporary tablespace.
Re-create custom database links (conditional)
If the Oracle Net listener in the 11g 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.
Create ConText and AZ objects
Certain ConText objects and the AZ objects dependent on the tables with XML type columns are not preserved by the import process. The consolidated export/import utility patch that you applied to the source administration server node in Section 1 contains a perl script, dpost_imp.pl, that you can run to generate an AutoPatch driver file. You use this driver file to call the scripts that create these objects. Run the following command:
$ perl $AU_TOP/patch/115/bin/dpost_imp.pl [driver file]\
[source database version]
Set [source database version] to 10 if the source database is 10.2 or to 11 if the source database is 11g.
Once the driver file has been generated, use AutoPatch to apply it on the target administration server node.
Import tables with XML type columns into the target database (conditional)
If the source database is Oracle Database 10g Release 2, modify the aufullimp.dat file with the following:
Set userid to "az/[az password]"
Set file to the dump file containing the tables with XML types (xmlexp by default).
Change the log file name.
Comment out the ignore parameter.
Start an import session on the target database server node using the customized import parameter file. Use the following command:
$ imp parfile=aufullimp.dat
Once the import is complete, you can delete the export dump files, as well as the export and import parameter files, from the source and target database server nodes.
Populate CTXSYS.DR$SQE table
To populate the CTXSYS.DR$SQE table, use SQL*Plus on the database server node to connect to the Applications database instance as APPS and run the following command:
$ sqlplus apps/[apps password]
SQL> exec icx_cat_sqe_pvt.sync_sqes_for_all_zones;
Compile invalid objects
On the target database server node, as the owner of the Oracle 11g file system and database instance, use SQL*Plus to connect to the target database as SYS and run the $ORACLE_HOME/rdbms/admin/utlrp.sql script to compile invalid objects.
$ sqlplus "/ as sysdba" @$ORACLE_HOME/rdbms/admin/utlrp.sql
Maintain Applications database objects
Run AD Administration on the target administration server node. From the Maintain Applications Database Objects menu, perform the following tasks:
Compile flexfield data in AOL tables
Recreate grants and synonyms for APPS schema
Start Applications server processes
Start all the server processes on the target Applications system. You can allow users to access the system at this time.
Create DQM indexes
Create DQM indexes by following these steps:
Log on to Oracle Applications with the "Trading Community Manager" responsibility
Click Control > Request > Run
Select "Single Request" option
Enter "DQM Staging Program" name
Enter the following parameters:
Number of Parallel Staging Workers: 4
Staging Command: CREATE_INDEXES
Continue Previous Execution: NO
Index Creation: SERIAL
Click "Submit"
--------------------------------------------------------------------------------
Change Record
The following sections were changed in this document.
Date Summary of Changes
13-Oct-2008 Initial release
11-Sep-2009 Added Database Vault information
Added revoke exempt access policy for target system
24-Dec-2009 Incorporated EBS 12.1 and RDBMS 11.2.
Modified adgrants.sql to run with APPS parameter
Modified OracleMetaLink to My Oracle Support
Modified prerequisite instructions to include Interoperability notes
Modified AutoConfig step numbers as step numbers in AutoConfig has changed
Modified export/import patch to 7120092
Note 741818.1 by Oracle Applications Development
Copyright 2008 Oracle USA
Last modified: Thursday, Dec 24, 2009
Related
--------------------------------------------------------------------------------
Products
--------------------------------------------------------------------------------
Oracle E-Business Suite > Applications Technology > Technology Components > Oracle Applications Utilities
Back to top
Rate this document Article Rating
Rate this document
Excellent
Good
Poor
Did this document help you?
Yes
No
Just browsing
How easy was it to find this document?
Very easy
Somewhat easy
Not easy
Comments
Provide feedback for this article. Please use 'Contact Us' for other feedback.
Important Note: this feedback is anonymously visible to other customers until processed by Oracle Support.
Cancel
--------------------------------------------------------------------------------
Modified 19-SEP-2010 Type WHITE PAPER Status PUBLISHED
Export/Import Process for Oracle E-Business Suite Release 12
Database Instances Using Oracle Database 11g Release 1 or 11g Release 2
December 2009
--------------------------------------------------------------------------------
This document describes the process of re-creating an existing Applications Release 12.x database instance using the datapump utilities. The most current version of these notes is Note 741818.1 on My Oracle Support. There is a change log at the end of this document.
The datapump utilities allow you to move existing data in Oracle format to and from Oracle databases. For example, datapump export files can archive database data, or move data among different Oracle databases that run on the same or different operating systems. This document assumes that you are already familiar with datapump.
There are special considerations when exporting or importing an Applications Release 12 database instance. This process consists of five discrete steps. Each step is covered in a separate section in this document.
Section 1: Prepare the source system
Tasks that must be performed to prepare the source system for the database export
Section 2: Prepare a target Release 12 database instance
Tasks for creating an empty database instance in preparation for import
Section 3: Export the source Release 12 database instance
Tasks that must be performed to produce a valid export of an Applications Release 12 database instance
Section 4: Import the Release 12 database instance
Tasks for running the import utility
Section 5: Update the imported Release 12 database instance
Tasks that must be performed to restore the imported Applications Release 12 database instance to a fully functional state
The source (export from) ORACLE_HOME directories must be Oracle Database 10g Release 2 (10.2.0) or Oracle Database 11g (11.x). The target (import to) ORACLE_HOME directories must be Oracle Database 11g (11.x). You may not downgrade using export/import.
The export/import process may use either or both the datapump utilities (expdp/impdp) and the traditional export/import (exp/imp). For more information, read Oracle Database Utilities 11g Release 1 (11.1) or Oracle Database Utilities 11g Release 2 (11.2).
Attention: This document uses UNIX/Linux syntax when describing directory structures. However, it applies to Windows servers as well. Where there is a significant difference in tasks for Windows, specific instructions are given.
Some of the tasks in this document affect the APPL_TOP of one or more application server tiers. Those tasks require that the Applications file system environment be enabled by running the APPSORA.env file (for UNIX or Linux) or the envshell.cmd file (for Windows) prior to performing the tasks. Other tasks affect the Applications database instance. Those tasks require that the Oracle 11g environment be enabled by running the [ORACLE_SID].env/cmd file under the Oracle 11g Oracle home on the database server node prior to performing the tasks. In addition, you may have more than one Oracle home installed on the database server node, so it is important that you run the correct [ORACLE_SID].env/cmd file before performing tasks that affect the database instance. Read the instructions carefully to determine which environment should be enabled for each step.
Attention: This document assumes that the source and target application server tiers are the same. Creating new application server tiers for the target environment has to be done either before starting or after completing all the steps in this document. Then, update and run AutoConfig for the source database and application server tiers to enable the source environment.
Attention: If you are using Oracle Database Vault, refer to Note 822048.1 before performing any step in this document.
--------------------------------------------------------------------------------
Section 1: Prepare the source system
This section describes how to ensure that you have the required patches, create your export file, and capture important information that is required to import your database.
Apply prerequisite patches
Ensure that you have applied steps 1 and 2 in Section 1 of the Oracle E-Business Suite Release 12 with Oracle Database 11g Release 1 (11.1.0) Interoperability Notes if you are importing an E-Business Suite Release 12.0 into 11.1.0, Oracle E-Business Suite Release 12.1 with Oracle Database 11g Release 1 (11.1.0) Interoperability Notes if you are importing an E-Business Suite Release 12.1 into 11.1.0, or Oracle E-Business Suite Release 12 with Oracle Database 11g Release 2 (11.2.0) Interoperability Notes if you are importing an E-Business Suite Release 12.x into 11.2.
When performing the AutoConfig prerequisite instructions, ensure that steps 3.1 and 3.2.1 of the Using AutoConfig to Manage System Configurations in Oracle E-Business Suite Release 12 document are completed. The other steps in 3.2 are not necessary as they will be done at the target side.
Apply the Applications consolidated export/import utility patch
Apply patch 7120092 to the source administration server node. This patch provides several SQL scripts that facilitate exporting and importing an Applications database instance.
Create a working directory
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
Generate target database instance creation script aucrdb.sql
The target database instance must be created with the same tablespace structure as the source database instance. The export/import patch provides the auclondb.sql script which generates the aucrdb.sql script, which you use to create the target database instance with the appropriate tablespace and file structure. The script converts all tablespaces except for SYSTEM to locally managed tablespaces with auto segment space management, if they are not already so.
On the source administration server node, use SQL*Plus to connect to the database as SYSTEM and run the $AU_TOP/patch/115/sql/auclondb.sql script. It creates aucrdb.sql in the current directory.
$ sqlplus system/[system password] \
@$AU_TOP/patch/115/sql/auclondb.sql 11
Record Advanced Queue settings
Advanced Queue settings are not propagated in the target database instance during the export/import process. Therefore, you must record them beforehand and enable them in the target database instance afterwards. The export/import patch contains auque1.sql, which generates a script called auque2.sql. You can use auque2.sql to enable the settings in the target database instance.
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. Then, on the source database server node, as the owner of the source database server file system and database instance, use SQL*Plus to connect to the source database as sysdba and run the auque1.sql script. It generates auque2.sql.
$ sqlplus /nolog
SQL> connect / as sysdba;
SQL> @auque1.sql
Create parameter file for tables with long columns (conditional)
If the source database is Oracle Database 10g Release 2, tables with long columns may not propagate properly in datapump. Therefore, they have to be migrated separately using the traditional export/import utilities.
Copy the aulong.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. Then, on the source database server node, as the owner of the source database server file system and database instance, use SQL*Plus to connect to the source database as sysdba and run the aulong.sql script. It generates aulongexp.dat.
$ sqlplus /nolog
SQL> connect system/[system password];
SQL> @aulong.sql
Remove rebuild index parameter in spatial indexes
Ensure that you do not have the rebuild index parameter in the spatial indexes. To see if you have any rebuild index parameters, on the source database server node, as the owner of the source database server file system and database instance, use SQL*Plus to connect to the source database as sysdba and run the following command:
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.
Deregister the current database server (conditional)
If you plan to change the database port, host, SID, or database name parameter on the database server, deregister the current database server node by running 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
contextfile=$CONTEXT_FILE -removeserver
--------------------------------------------------------------------------------
Section 2: Prepare a target Release 12 database instance
This section describes how to create the empty target database and populate it with all of the required system objects prior to running import.
The Oracle home of the target database instance can be the same Oracle home that the source database instance uses, or it can be different (on another machine running a different operating system, for example), as long as it uses Oracle Database 11g Release 1 Enterprise Edition.
Create target Oracle 11g Oracle home (conditional)
If you want the target Oracle 11g Oracle home to be separate from the source Oracle home, you must create it now. Perform the steps in the "Database Installation" subsection of Section 1 of the Oracle E-Business Suite Release 12 with Oracle Database 11g Release 1 (11.1.0) Interoperability Notes, Oracle E-Business Suite Release 12.1 with Oracle Database 11g Release 1 (11.1.0) Interoperability Notes, or Oracle E-Business Suite Release 12 with Oracle Database 11g Release 2 (11.2.0) Interoperability Notes.
Modify sqlnet.ora file (Windows only)
If the target database server node is running Windows, add the following line to the sqlnet.ora file in the %ORACLE_HOME%\network\admin\[SID] directory, if it does not already exist:
SQLNET.AUTHENTICATION_SERVICES=(NTS)
Create the target initialization parameter file and CBO parameter file
The initialization parameter file (init[SID].ora) and cost-based optimizer (CBO) parameter file (ifilecbo.ora) are located in the $ORACLE_HOME/dbs directory on the source database server node. Copy both files to the Oracle 11g $ORACLE_HOME/dbs directory on the target database server node.
Refer to Database Initialization Parameters for Oracle Applications Release 12 and update both the init.ora and ifilecbo.ora files with any necessary changes. You may also need to update initialization parameters involving the db_name, control_files, and directory structures.
Ensure that the undo_tablespace parameter in the initialization parameter file of the target database instance matches with the default undo tablespace set in the aucrdb.sql script.
Ignore the initialization parameters that pertain to the native compilation of PL/SQL code. You will be instructed to add them later, if necessary.
Create a working directory
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
Create the target database instance
Copy the aucrdb.sql script, generated in Section 1, from the source administration server node to the working directory in the target database server node. Then update the script on the target database server node with any necessary changes to the directory structures for the log file(s), data file(s), or tablespaces, reflecting the layout of the target database server node. You may enlarge the file sizes to ensure that tablespaces do not run out. If the target database server node is running Windows, update the directory structure from UNIX/Linux format to Windows format or vice versa.
Make sure that the environment of your session on the target database server node is set up properly for the target database instance, especially the ORACLE_HOME, ORACLE_SID, and ORA_NLS10 environment settings. (ORACLE_SID must be set to the same value as the db_name parameter in the init[SID].ora file.) Then, use the following commands to run aucrdb.sql and create the target database instance:
$ sqlplus /nolog
SQL> connect / as sysdba;
SQL> spool aucrdb.log;
For UNIX or Linux:
SQL> startup nomount;
SQL> @aucrdb.sql
SQL> exit;
For Windows:
SQL> startup nomount pfile=%ORACLE_HOME%\dbs\init%ORACLE_SID%.ora
SQL> @aucrdb.sql
SQL> exit;
If PL/SQL of the source database was natively compiled, see the "Compiling PL/SQL Code for Native Execution" section of Chapter 11 of Oracle Database PL/SQL User's Guide and Reference 11g Release 1 (11.1) or Chapter 12 of Oracle Database PL/SQL Language Reference 11g Release 2 (11.2) for instructions on how to natively compile PL/SQL in the target database. Add the parameters that pertain to the native compilation where specified. Do not use the natively compiled code generated by the source database. Oracle does not support switching the PL/SQL compilation mode from interpreted to native (and vice-versa) for an export/import. Exporting/importing using native mode takes significantly more time than interpreted mode.
When the target database instance has been created, restart the database instance.
Note: You may modify the sizes of the database files in aucrdb.sql to ensure enough tablespace. Querying the dba_free_space and dba_data_files tables in the source database can give you an idea of how much tablespace is required. However, this is no guarantee as the space requirements may change depending on the extent sizes used by each object. Not having enough tablespace will cause failures as well as a major performance degradation in the import run.
Copy database preparation scripts to target Oracle home
The export/import patch that you applied to the source administration server node in Section 1 contain four scripts that are needed on the target database server node. Copy the following files from the $AU_TOP/patch/115/sql directory of the source administration server node to the working directory in the target database server node: audb1110.sql/audb1120.sql, ausy1110.sql/ausy1120.sql, aujv1110.sql/aujv1120.sql, and aumsc1110.sql/aumsc1120.sql (UNIX or Linux) or audb1110_nt.sql/audb1120_nt.sql, ausy1110_nt.sql/ausy1120_nt.sql, aujv1110_nt.sql/aujv1120_nt.sql, and aumsc1110_nt.sql/aumsc1120_nt.sql (Windows).
As you run each of the next four steps, note the following:
The remarks section at the beginning of each script contains additional information.
Each script creates a log file in the current directory.
Set up the SYS schema
The audb1110.sql, audb1120.sql, audb1110_nt.sql, or audb1120_nt.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 audb1110.sql/audb1120.sql (UNIX/Linux) or audb1110_nt.sql/audb1120_nt.sql (Windows).
Here is an example on UNIX or Linux for 11.2.0:
$ sqlplus "/ as sysdba" @audb1120.sql
Set up the SYSTEM schema
The ausy1110.sql, ausy1120.sql, ausy1110_nt.sql, or ausy1120_nt.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 ausy1110.sql/ausy1120.sql (UNIX/Linux) or ausy1110_nt.sql/ausy1120_nt.sql (Windows).
Here is an example on UNIX or Linux for 11.2.0:
$ sqlplus system/[system password] @ausy1120.sql
Install Java Virtual Machine
The aujv1110.sql, aujv1120.sql, aujv1110_nt.sql, or aujv1120_nt.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 aujv1110.sql/aujv1120.sql (UNIX/Linux) or aujv1110_nt.sql/aujv1120_nt.sql (Windows).
Here is an example on UNIX or Linux for 11.2.0:
$ sqlplus system/[system password] @aujv1120.sql
Attention: This script can be run only once in a given database instance, because the scripts that it calls are not rerunnable.
Install other required components
The aumsc1110.sql, aumsc1120.sql, aumsc1110_nt.sql, or aumsc1120_nt.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 run aumsc1110.sql/aumsc1120.sql (UNIX/Linux) or aumsc1110_nt.sql/aumsc1120_nt.sql (Windows). You must pass the following arguments to the script, in the order specified:
Argument Value
remove context? FALSE
SYSAUX tablespace SYSAUX
temporary tablespace TEMP
Here is an example on UNIX or Linux for 11.2.0:
$ sqlplus system/[system password] \
@aumsc1120.sql FALSE SYSAUX TEMP
Attention: All of the components are created in the SYSAUX tablespace regardless of where it was installed in the source database.
Set CTXSYS parameter (conditional)
If your target database is 11g Release 2 (11.2.0), use SQL*Plus to connect to the database as SYSDBA and run the following command:
$ sqlplus "/ as sysdba"
SQL> exec ctxsys.ctx_adm.set_parameter('file_access_role', 'public');
Disable automatic gathering of statistics
Copy $APPL_TOP/admin/adstats.sql from the administration server node to the target database server node. 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> alter system enable restricted session;
SQL> @adstats.sql
$ sqlplus "/ as sysdba"
SQL> alter system disable restricted session;
SQL> exit;
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.
--------------------------------------------------------------------------------
Section 3: Export the source Release 12 database instance
This section describes how to ensure that you have the required patches, create your export file, and capture important information that is required to import your database.
Create the export parameter file
A template for the export parameter file has been included as part of the the export/import patch. Copy $AU_TOP/patch/115/import/auexpdp.dat from the source administration server node to the working directory in the source database server node. Use a text editor to modify the file to reflect the source environment and other customized parameters.
The customizable parameters are:
Parameter Description Template Value
directory directory where the export dump files will be created dmpdir
dumpfile export dump file name(s) aexp%U.dmp
filesize export dump file size 1GB
log log file name expdpapps.log
interMedia, OLAP, and Data Mining schemas are not exported. The aumsc1110.sql script creates these schemas in the target database. Ensure that the schema names in the exclude parameters reflect those in your database.
If your source database is Oracle Database 11g (11.x), add the following line to the parameter file:
QUERY=applsys.wf_item_attribute_values:"where item_type!='WFERROR' and
name != 'EVENT_MESSAGE'"
Create a directory in the system schema that corresponds to the directory specified in the template. Here is an example of how to create a directory named dmpdir:
$ sqlplus system/[system password]
SQL> create directory dmpdir as '/u01/expimp';
Do not change the other parameters.
The export process uses as many of the listed file names as necessary to hold the exported data. You must ensure that the number of dump files specified, as well as the size of each dump file, is sufficient to contain all the data in your source database instance.
Shut down Applications server processes
Shut down all Applications server processes except the database and the Net8 listener for the database. Users cannot use the Applications until the import is completed.
Grant privilege to source system schema
Grant the exempt access policy privilege to system by using SQL*Plus to connect to the database as SYSDBA and run the following command:
SQL> grant EXEMPT ACCESS POLICY to system;
Export the Applications database instance
Start an export session on the source database server node using the customized export parameter file.
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]
Typically, the export runs for several hours.
Export tables with long columns (conditional)
If the source database is Oracle Database 10g Release 2 (10.2.0), start an export session on the source database server node using the customized aulongexp.dat file generated in Section 1. Use the following command:
$ exp parfile=aulongexp.dat
Export tables with XML type columns (conditional)
If the source database is Oracle Database 10g Release 2 (10.2.0), copy $AU_TOP/patch/115/import/auxmlexp.dat from the source administration server to the working directory in the source database server node. Start an export session on the source database server node using the following command:
$ exp parfile=auxmlexp.dat
Revoke privilege from source 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;
--------------------------------------------------------------------------------
Section 4: Import the Release 12 database instance
This section describes how to use the import utility to load the Oracle Applications data into the target database.
Create the import parameter files
Copy auimpdp.dat, aufullimp.dat, and auimpusr.dat from the $AU_TOP/patch/115/import directory in the source administration server node to the working directory in the target database server node. Make sure that the directory, dumpfile, and logfile parameters in auimpdp.dat and auimpusr.dat are set properly.
Create a directory in the system schema with the name set to the directory specified in the template and the path set to where the export dump files will reside. Here is an example of how to create a directory named dmpdir:
$ sqlplus system/[system password]
SQL> create directory dmpdir as '/u01/expimp';
Save the changed file.
Copy the export dump files
Copy the export dump files from the source database server node to the working directory in the target database server node.
Import the users into the target database (conditional)
If the source database is Oracle Database 10g Release 2 (10.2.0), start an import session on the target database server node using the customized import parameter file. Use the following command:
$ impdp system/[system password] parfile=auimpusr.dat
Import tables with long columns into the target database (conditional)
If the source database is Oracle Database 10g Release 2 (10.2.0), modify the aufullimp.dat file with the following:
Set userid to "sys/[sys password] as sysdba".
Set file to the dump file containing the long tables (longexp by default).
Set the log file appropriately.
Leave the ignore parameter commented out.
Import the tables using the following command:
$ imp parfile=aufullimp.dat
Attention: You will get failures for the triggers as the dependent tables have not yet been imported.
Import the Applications database instance
If your source database is Oracle Database 11g (11.x), remove or comment out all the exclude parameters in the auimpdp.dat parameter file. If your source database is Oracle Database 10g Release 2 (10.2.0), leave the parameter file as is. Start an import session on the target database server node using the auimpdp.dat parameter file.
If the target database is 11.1.0, use the following command:
$ impdp system/[system password] parfile=auimpdp.dat
If the target database is 11.2.0, use the following command:
$ impdp "'/ as sysdba'" parfile=[export parameter file name]
Typically, import runs for several hours.
Import triggers into the target database (conditional)
If the source database is Oracle Database 10g Release 2 (10.2.0), modify the aufullimp.dat file with the following:
Set userid to "sys/[sys password] as sysdba".
Set file to the dump file containing the long tables (longexp by default).
Change the log file name.
Uncomment the ignore parameter.
Add a line with the parameter "rows=n".
Start an import session on the target database server node using the customized import parameter file. Use the following command:
$ imp parfile=aufullimp.dat
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;
--------------------------------------------------------------------------------
Section 5: Update the imported Release 12 database instance
This section describes how to recreate the database objects and relationships that are not handled by the export and import utilities.
Reset Advanced Queues
Copy the auque2.sql script that was generated in Section 1 from the working directory in the source database server node to the working directory in the target database server node. Then, on the target database server node, as the owner of the Oracle 11g file system and database instance, use SQL*Plus to connect to the target database as SYSDBA and run the auque2.sql script to enable the Advanced Queue settings that were lost during the export/import process. The script creates a log file in the current directory.
$ sqlplus /nolog
SQL> connect / as sysdba;
SQL> @auque2.sql
Start the new database listener (conditional)
If the Oracle Net listener for the database instance in the new Oracle home has not been started, you must start it now. Since AutoConfig has not yet been implemented, start the listener with the lsnrctl executable (UNIX/Linux) or Services (Windows). See the Oracle Database Net Services Administrator's Guide, 11g Release 1 (11.1) or Oracle Database Net Services Administrator's Guide, 11g Release 2 (11.2) for more information.
Attention: Set the TNS_ADMIN environment variable to the directory where you created your listener.ora and tnsnames.ora files.
Run adgrants.sql
Copy $APPL_TOP/admin/adgrants.sql (adgrants_nt.sql for Windows) from the administration server node to the working directory in the database server node. Use SQL*Plus to connect to the database as SYSDBA and run the script using the following command:
$ sqlplus "/ as sysdba" @adgrants.sql (or adgrants_nt.sql) \
[APPS schema name]
Note: Verify the usage of adgrants.sql in the adgrants.sql script. Older versions of adgrants.sql require the APPLSYS schema name parameter to be passed instead of APPS.
Grant create procedure privilege on CTXSYS
Copy $AD_TOP/patch/115/sql/adctxprv.sql from the administration server node to the database server node. Use SQL*Plus to connect to the database as APPS and run the script using the following command:
$ sqlplus apps/[APPS password] @adctxprv.sql \
[SYSTEM password] CTXSYS
Apply patch 6494466 (conditional)
If the target database is Windows and the source is not, apply patch 6494466 on the target database tier. Create the appsutil directory if needed.
Implement and run AutoConfig
Implement and run AutoConfig in the new Oracle home on the database server node. If the database listener of the new Oracle home is defined differently than the old Oracle home, you must also run AutoConfig on each application tier server node to update the system with the new listener.
See Using AutoConfig to Manage System Configurations in Oracle E-Business Suite Release 12 on My Oracle Support, especially section 3.2, for instructions on how to implement and run AutoConfig.
Shut down all processes, including the database and the listener, and restart them to load the new environment settings.
Gather statistics for SYS schema
Use SQL*Plus to connect to the database as SYSDBA and use the following commands to restart the database in restricted mode, run adstats.sql, and restart the database in normal mode:
$ sqlplus "/ as sysdba"
SQL> alter system enable restricted session;
SQL> @adstats.sql
$ sqlplus "/ as sysdba"
SQL> alter system disable restricted session;
SQL> exit;
Attention: Make sure that you have at least 1.5 GB of free default temporary tablespace.
Re-create custom database links (conditional)
If the Oracle Net listener in the 11g 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.
Create ConText and AZ objects
Certain ConText objects and the AZ objects dependent on the tables with XML type columns are not preserved by the import process. The consolidated export/import utility patch that you applied to the source administration server node in Section 1 contains a perl script, dpost_imp.pl, that you can run to generate an AutoPatch driver file. You use this driver file to call the scripts that create these objects. Run the following command:
$ perl $AU_TOP/patch/115/bin/dpost_imp.pl [driver file]\
[source database version]
Set [source database version] to 10 if the source database is 10.2 or to 11 if the source database is 11g.
Once the driver file has been generated, use AutoPatch to apply it on the target administration server node.
Import tables with XML type columns into the target database (conditional)
If the source database is Oracle Database 10g Release 2, modify the aufullimp.dat file with the following:
Set userid to "az/[az password]"
Set file to the dump file containing the tables with XML types (xmlexp by default).
Change the log file name.
Comment out the ignore parameter.
Start an import session on the target database server node using the customized import parameter file. Use the following command:
$ imp parfile=aufullimp.dat
Once the import is complete, you can delete the export dump files, as well as the export and import parameter files, from the source and target database server nodes.
Populate CTXSYS.DR$SQE table
To populate the CTXSYS.DR$SQE table, use SQL*Plus on the database server node to connect to the Applications database instance as APPS and run the following command:
$ sqlplus apps/[apps password]
SQL> exec icx_cat_sqe_pvt.sync_sqes_for_all_zones;
Compile invalid objects
On the target database server node, as the owner of the Oracle 11g file system and database instance, use SQL*Plus to connect to the target database as SYS and run the $ORACLE_HOME/rdbms/admin/utlrp.sql script to compile invalid objects.
$ sqlplus "/ as sysdba" @$ORACLE_HOME/rdbms/admin/utlrp.sql
Maintain Applications database objects
Run AD Administration on the target administration server node. From the Maintain Applications Database Objects menu, perform the following tasks:
Compile flexfield data in AOL tables
Recreate grants and synonyms for APPS schema
Start Applications server processes
Start all the server processes on the target Applications system. You can allow users to access the system at this time.
Create DQM indexes
Create DQM indexes by following these steps:
Log on to Oracle Applications with the "Trading Community Manager" responsibility
Click Control > Request > Run
Select "Single Request" option
Enter "DQM Staging Program" name
Enter the following parameters:
Number of Parallel Staging Workers: 4
Staging Command: CREATE_INDEXES
Continue Previous Execution: NO
Index Creation: SERIAL
Click "Submit"
--------------------------------------------------------------------------------
Change Record
The following sections were changed in this document.
Date Summary of Changes
13-Oct-2008 Initial release
11-Sep-2009 Added Database Vault information
Added revoke exempt access policy for target system
24-Dec-2009 Incorporated EBS 12.1 and RDBMS 11.2.
Modified adgrants.sql to run with APPS parameter
Modified OracleMetaLink to My Oracle Support
Modified prerequisite instructions to include Interoperability notes
Modified AutoConfig step numbers as step numbers in AutoConfig has changed
Modified export/import patch to 7120092
Note 741818.1 by Oracle Applications Development
Copyright 2008 Oracle USA
Last modified: Thursday, Dec 24, 2009
Related
--------------------------------------------------------------------------------
Products
--------------------------------------------------------------------------------
Oracle E-Business Suite > Applications Technology > Technology Components > Oracle Applications Utilities
Back to top
Rate this document Article Rating
Rate this document
Excellent
Good
Poor
Did this document help you?
Yes
No
Just browsing
How easy was it to find this document?
Very easy
Somewhat easy
Not easy
Comments
Provide feedback for this article. Please use 'Contact Us' for other feedback.
Important Note: this feedback is anonymously visible to other customers until processed by Oracle Support.
Cancel
Export/Import Process for Oracle E-Business Suite Release 12 using 10gR2
Export/Import Process for Oracle E-Business Suite Release 12 using 10gR2 [ID 454616.1]
--------------------------------------------------------------------------------
Modified 02-JUL-2010 Type WHITE PAPER Status PUBLISHED
Export/Import Process for Oracle E-Business Suite Release 12
Database Instances Using Oracle Database 10g Release 2
July 2010
--------------------------------------------------------------------------------
This document describes the process of re-creating an existing Applications Release 12 database instance using the datapump utilities. The most current version of these notes is document 454616.1 on My Oracle Support. There is a change log at the end of this document.
The datapump utilities allow you to move existing data in Oracle format to and from Oracle databases. For example, datapump export files can archive database data, or move data among different Oracle databases that run on the same or different operating systems. This document assumes that you are already familiar with datapump.
There are special considerations when exporting or importing an Applications Release 12 database instance. This process consists of five discrete steps. Each step is covered in a separate section in this document.
Section 1: Prepare the source system
Tasks that must be performed to prepare the source system for the database export
Section 2: Prepare a target Release 12 database instance
Tasks for creating an empty database instance in preparation for import
Section 3: Export the source Release 12 database instance
Tasks that must be performed to produce a valid export of an Applications Release 12 database instance
Section 4: Import the Release 12 database instance
Tasks for running the import utility
Section 5: Update the imported Release 12 database instance
Tasks that must be performed to restore the imported Applications Release 12 database instance to a fully functional state
The source (export from) and target (import to) ORACLE_HOME directories must be Oracle Database 10g Release 2 (10.2.0).
The export/import process requires the use of both the datapump utilities (expdp/impdp) and the traditional export/import (exp/imp). For more information, read Oracle Database Utilities 10g Release 2 (10.2).
Attention: This document uses UNIX/Linux syntax when describing directory structures. However, it applies to Windows servers as well. Where there is a significant difference in tasks for Windows, specific instructions are given.
Some of the tasks in this document affect the APPL_TOP of one or more application server tiers. Those tasks require that the Applications file system environment be enabled by running the APPSORA.env file (for UNIX or Linux) or the envshell.cmd file (for Windows) prior to performing the tasks. Other tasks affect the Applications database instance. Those tasks require that the Oracle 10g environment be enabled by running the [ORACLE_SID].env/cmd file under the Oracle 10g Oracle home on the database server node prior to performing the tasks. In addition, you may have more than one Oracle home installed on the database server node, so it is important that you run the correct [ORACLE_SID].env/cmd file before performing tasks that affect the database instance. Read the instructions carefully to determine which environment should be enabled for each step.
Attention: This document assumes that the source and target application server tiers are the same. Creating new application server tiers for the target environment has to be done either before starting or after completing all the steps in this document. Then, update and run AutoConfig for the source database and application server tiers to enable the source environment.
Attention: If you are using Oracle Database Vault, refer to Note 822048.1 before performing any step in this document.
--------------------------------------------------------------------------------
Section 1: Prepare the source system
This section describes how to ensure that you have the required patches, create your export file, and capture important information that is required to import your database.
Apply latest AutoConfig patches
Perform steps 3.1 and 3.2.1 of the Using AutoConfig to Manage System Configurations in Oracle Applications Release 12 document. The other steps in 3.2 are not necessary as they will be done at the target side.
Apply the Applications consolidated export/import utility patch
Apply patch 7120092 to the source administration server node. This patch provides several SQL scripts that facilitate exporting and importing an Applications database instance, export and import parameter files, and a perl script, which creates an AD patch driver.
Apply latest Applications database preparation scripts patch (conditional)
If you are using Oracle E-Business Suite Release 12.0, apply patch 6342289 to every application tier server node in the source system.
Create a working directory
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
Generate target database instance creation script aucrdb.sql
The target database instance must be created with the same tablespace and file structure as the source database instance. The export/import patch provides the auclondb.sql script which generates the aucrdb.sql script, which you use to create the target database instance with the appropriate tablespace and file structure. The script converts all tablespaces except for SYSTEM to locally managed tablespaces with auto segment space management, if they are not already so.
On the source administration server node, use SQL*Plus to connect to the database as SYSTEM and run the $AU_TOP/patch/115/sql/auclondb.sql script. It creates aucrdb.sql in the current directory.
$ sqlplus system/[system password] \
@$AU_TOP/patch/115/sql/auclondb.sql 10
Record Advanced Queue settings
Advanced Queue settings are not propagated in the target database instance during the export/import process. Therefore, you must record them beforehand and enable them in the target database instance afterwards. The export/import patch contains auque1.sql, which generates a script called auque2.sql. You can use auque2.sql to enable the settings in the target database instance.
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. Then, on the source database server node, as the owner of the source database server file system and database instance, use SQL*Plus to connect to the source database as sysdba and run the auque1.sql script. It generates auque2.sql.
$ sqlplus /nolog
SQL> connect / as sysdba;
SQL> @auque1.sql
Create parameter file for tables with long columns (conditional)
The fix to this issue is part of 10.2.0.5. If you are on 10.2.0.4 or prior versions of 10g Release 2, tables with long columns may not propagate properly in datapump. Therefore, they have to be migrated separately using the traditional export/import utilities.
Copy the aulong.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. Then, on the source database server node, as the owner of the source database server file system and database instance, use SQL*Plus to connect to the source database as sysdba and run the aulong.sql script. It generates aulongexp.dat.
$ sqlplus /nolog
SQL> connect system/[system password];
SQL> @aulong.sql
Remove rebuild index parameter in spatial indexes
Ensure that you do not have the rebuild index parameter in the spatial indexes. To see if you have any rebuild index parameters, on the source database server node, as the owner of the source database server file system and database instance, use SQL*Plus to connect to the source database as sysdba and run the following command:
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.
Deregister the current database server (conditional)
If you plan to change the database port, host, SID, or database name parameter on the database server, deregister the current database server node by running 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
contextfile=$CONTEXT_FILE -removeserver
--------------------------------------------------------------------------------
Section 2: Prepare a target Release 12 database instance
This section describes how to create the empty target database and populate it with all of the required system objects prior to running import.
The Oracle home of the target database instance can be the same Oracle home that the source database instance uses, or it can be different (on another machine running a different operating system, for example), as long as it uses Oracle Database 10g Release 2 Enterprise Edition.
Create target Oracle 10g Oracle home (conditional)
If you want the target Oracle 10g Oracle home to be separate from the source Oracle home, you must create it now. Decide whether you want to install the 10.2.0 Oracle home manually, or use the Rapid Install to create it for you.
If you choose to use Rapid Install, you must use Rapid Install Release 12.0.0. As the owner of the Oracle RDBMS file system, start the Rapid Install wizard by typing:
$ rapidwiz -techstack
Choose the "10gR2 RDBMS" option in the techstack components window and provide the details for the new Oracle home. Make sure that the SID environment setting is set to the same value as your existing database instance.
If you choose to manually install the 10.2.0 Oracle home, log in to the database server node as the owner of the Oracle RDBMS file system and database instance and perform the following steps:
Ensure that environment settings, such as ORACLE_HOME, are set for the new Oracle home you are about to create.
Perform all the steps in Chapter 3 of the Oracle Database Installation Guide 10g Release 2 (10.2), for your platform.
In the subsequent windows, click on the Product Languages button and select any languages other than American English that are used by your Applications database instance, choose the Enterprise Edition installation type, and select the options not to upgrade an existing database and to install the database software only.
Perform tasks in section 3.5, "Installing Oracle Database 10g Products" in the Oracle Database Companion CD Installation Guide for your platform. Do not perform the tasks in the "Preparing Oracle Workflow Server for the Oracle Workflow Middle Tier Installation" section.
In the Installation Types window, click on the Product Languages button to select any languages other than American English that are used by your Applications database instance.
Make sure that the following environment variables are set whenever you enable the 10g Oracle home:
ORACLE_HOME points to the new 10.2.0 Oracle home.
PATH includes $ORACLE_HOME/bin and the directory where the new perl executable is located (usually $ORACLE_HOME/perl/bin).
LD_LIBRARY_PATH includes $ORACLE_HOME/lib.
PERL5LIB points to the directories where the new perl libraries are located (usually $ORACLE_HOME/perl/lib/[perl version] and $ORACLE_HOME/perl/lib/site_perl/[perl version])
Run the $ORACLE_HOME/nls/data/old/cr9idata.pl script to create the $ORACLE_HOME/nls/data/9idata directory. After creating the directory, make sure that the ORA_NLS10 environment variable is set to the full path of the 9idata directory whenever you enable the 10g Oracle home.
Attention: Check to make sure the $ORACLE_HOME/nls/data/9idata directory is created and is non-empty.
Attention (for Windows users): Keep track of the database home name used. For Rapidwiz installed Oracle homes, the home name is [SID]_db102_RDBMS. For manually installed Oracle homes, the home name is what you input when creating the Oracle home.
Upgrade to the latest 10.2.0 patch set (conditional)
If you are not on the latest patch set, perform the following steps from the Oracle E-Business Suite Release 12 with Oracle Database 10g Release 2 (10.2.0) Interoperability Notes on My Oracle Support:
Perform 10.2.0.x Patch Set installation tasks
Apply additional 10.2.0.x RDBMS patches
Do not perform any post-installation patch README steps.
Modify sqlnet.ora file (Windows only)
If the target database server node is running Windows, add the following line to the sqlnet.ora file in the %ORACLE_HOME%\network\admin\[SID] directory, if it does not already exist:
SQLNET.AUTHENTICATION_SERVICES=(NTS)
Create the target initialization parameter file and CBO parameter file
The initialization parameter file (init[SID].ora) and cost-based optimizer (CBO) parameter file (ifilecbo.ora) are located in the $ORACLE_HOME/dbs directory on the source database server node. Copy both files to the Oracle 10g $ORACLE_HOME/dbs directory on the target database server node.
Refer to Database Initialization Parameters for Oracle Applications Release 12 and update both the init.ora and ifilecbo.ora files with any necessary changes. You may also need to update initialization parameters involving the db_name, control_files, and directory structures.
Ensure that the undo_tablespace parameter in the initialization parameter file of the target database instance matches with the default undo tablespace set in the aucrdb.sql script.
Ignore the initialization parameters that pertain to the native compilation of PL/SQL code. You will be instructed to add them later, if necessary.
Create a working directory
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
Create the target database instance
Copy the aucrdb.sql script, generated in Section 1, from the source administration server node to the working directory in the target database server node. Then update the script on the target database server node with any necessary changes to the directory structures for the log file(s), data file(s), or tablespaces, reflecting the layout of the target database server node. If the target database server node is running Windows, update the directory structure from UNIX/Linux format to Windows format or vice versa.
Attention: Using the source tablespace information does not guarantee that the target tablespaces will be enough. It is highly recommended that you go through the source dba_free_space table to see which of the tablespaces are running out and modify the aucrdb.sql script to ensure ample tablespace size on the target database.
Make sure that the environment of your session on the target database server node is set up properly for the target database instance, especially the ORACLE_HOME, ORACLE_SID, and ORA_NLS10 environment settings. (ORACLE_SID must be set to the same value as the db_name parameter in the init[SID].ora file.) Then, use the following commands to run aucrdb.sql and create the target database instance:
$ sqlplus /nolog
SQL> connect / as sysdba;
SQL> spool aucrdb.log;
For UNIX or Linux:
SQL> startup nomount;
SQL> @aucrdb.sql
SQL> exit;
For Windows:
SQL> startup nomount pfile=%ORACLE_HOME%\dbs\init%ORACLE_SID%.ora
SQL> @aucrdb.sql
SQL> exit;
If PL/SQL of the source database was natively compiled, see the "Compiling PL/SQL Code for Native Execution" section of Chapter 11 of Oracle Database PL/SQL User's Guide and Reference 10g Release 2 (10.2) for instructions on how to natively compile PL/SQL in the target database. Add the parameters that pertain to the native compilation where specified. Do not use the natively compiled code generated by the source database. Oracle does not support switching the PL/SQL compilation mode from interpreted to native (and vice-versa) for an export/import. Exporting/importing using native mode takes significantly more time than interpreted mode.
When the target database instance has been created, restart the database instance.
Copy database preparation scripts to target Oracle home
The database preparation scripts that you applied to the source administration server node in Section 1 contain four scripts that are needed on the target database server node. Copy the following files from the $APPL_TOP/admin directory of the source administration server node to the working directory in the target database server node: addb1020.sql, adsy1020.sql, adjv1020.sql, and admsc1020.sql (UNIX or Linux) or addb1020_nt.sql, adsy1020_nt.sql, adjv1020_nt.sql, and admsc1020_nt.sql (Windows).
As you run each of the next four steps, note the following:
The remarks section at the beginning of each script contains additional information.
Each script creates a log file in the current directory.
Set up the SYS schema
The addb1020.sql or addb1020_nt.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 addb1020.sql (UNIX/Linux) or addb1020_nt.sql (Windows).
Here is an example on UNIX or Linux:
$ sqlplus "/ as sysdba" @addb1020.sql
Set up the SYSTEM schema
The adsy1020.sql or adsy1020_nt.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 adsy1020.sql (UNIX/Linux) or adsy1020_nt.sql (Windows).
Here is an example on UNIX or Linux:
$ sqlplus system/[system password] @adsy1020.sql
Install Java Virtual Machine
The adjv1020.sql or adjv1020_nt.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 adjv1020.sql (UNIX/Linux) or adjv1020_nt.sql (Windows).
Here is an example on UNIX or Linux:
$ sqlplus system/[system password] @adjv1020.sql
Attention: This script can be run only once in a given database instance, because the scripts that it calls are not rerunnable.
Install other required components
The admsc1020.sql or admsc1020_nt.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 run admsc1020.sql (UNIX/Linux) or admsc1020_nt.sql (Windows). You must pass the following arguments to the script, in the order specified:
Argument Value
remove context? FALSE
SYSAUX tablespace SYSAUX
temporary tablespace TEMP
Here is an example on UNIX or Linux:
$ sqlplus system/[system password] \
@admsc1020.sql FALSE SYSAUX TEMP
Attention: All of the components are created in the SYSAUX tablespace regardless of where it was installed in the source database.
Disable automatic gathering of statistics
Copy $APPL_TOP/admin/adstats.sql from the administration server node to the target database server node. 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;
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.
--------------------------------------------------------------------------------
Section 3: Export the source Release 12 database instance
This section describes how to ensure that you have the required patches, create your export file, and capture important information that is required to import your database.
Create the export parameter file
A template for the export parameter file has been included as part of the the export/import patch. Copy $AU_TOP/patch/115/import/auexpdp.dat from the source administration server node to the working directory in the source database server node. Use a text editor to modify the file to reflect the source environment and other customized parameters.
The customizable parameters are:
Parameter Description Template Value
directory directory where the export dump files will be created dmpdir
dumpfile export dump file name(s) aexp%U.dmp
filesize export dump file size 1GB
log log file name expdpapps.log
interMedia, OLAP, and Data Mining schemas are not exported. The admsc1020.sql script creates these schemas in the target database. Ensure that the schema names in the exclude parameters reflect those in your database.
Create a directory in the SYS schema that corresponds to the directory specified in the template. Here is an example of how to create a directory named dmpdir:
$ sqlplus "/ as sysdba"
SQL> create directory dmpdir as '/u01/expimp';
Do not change the other parameters.
The export process uses as many of the listed file names as necessary to hold the exported data. You must ensure that the number of dump files specified, as well as the size of each dump file, is sufficient to contain all the data in your source database instance.
Shut down Applications server processes
Shut down all Applications server processes except the database and the Net8 listener for the database. Users cannot use the Applications until the import is completed.
Grant privilege to source system schema
Grant the exempt access policy privilege to system by using SQL*Plus to connect to the database as SYSDBA and run the following command:
SQL> grant EXEMPT ACCESS POLICY to system;
Export the Applications database instance
Start an export session on the source database server node using the customized export parameter file. Use the following command:
$ expdp "'/ as sysdba'" parfile=[export parameter file name]
Typically, the export runs for several hours.
Attention: See document 339938.1 on My Oracle Support if you encounter the failure:
EXP-00056: ORACLE error 932 encountered
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
EXP-00000: Export terminated unsuccessfully
Export tables with long columns (conditional)
If you created aulongexp.dat by running aulong.sql in Section 1, start an export session on the source database server node using the following command:
$ exp parfile=aulongexp.dat
Export tables with XML type columns
Copy $AU_TOP/patch/115/import/auxmlexp.dat from the source administration server to the working directory in the source database server node. Start an export session on the source database server node using the following command:
$ exp parfile=auxmlexp.dat
Revoke privilege from source 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;
--------------------------------------------------------------------------------
Section 4: Import the Release 12 database instance
This section describes how to use the import utility to load the Oracle Applications data into the target database.
Create the import parameter files
Copy auimpdp.dat, aufullimp.dat, and auimpusr.dat from the $AU_TOP/patch/115/import directory in the source administration server node to the working directory in the target database server node. Make sure that the directory, dumpfile, and logfile parameters in auimpdp.dat and auimpusr.dat are set properly.
Create a directory in the system schema with the name set to the directory specified in the template and the path set to where the export dump files will reside. Here is an example of how to create a directory named dmpdir:
$ sqlplus system/[system password]
SQL> create directory dmpdir as '/u01/expimp';
Save the changed file.
Copy the export dump files
Copy the export dump files from the source database server node to the working directory in the target database server node.
Import the users into the target database (conditional)
If you exported the long columns in Section 3, start an import session on the target database server node using the customized import parameter file. Use the following command:
$ impdp system/[system password] parfile=auimpusr.dat
Import tables with long columns into the target database (conditional)
If you exported the long columns in Section 3, modify the aufullimp.dat file with the following:
Set userid to "sys/[sys password] as sysdba".
Set file to the dump file containing the long tables (longexp by default).
Set the log file appropriately.
Leave the ignore parameter commented out.
Import the tables using the following command:
$ imp parfile=aufullimp.dat
Attention: You will get failures for the triggers as the dependent tables have not yet been imported.
Import the Applications database instance
If you exported the long columns in Section 3, remove or comment out all the exclude parameters in the auimpdp.dat parameter file. Start an import session on the target database server node using the auimpdp.dat parameter file. Use the following command:
$ impdp "'/ as sysdba'" parfile=auimpdp.dat
Typically, import runs for several hours.
Import triggers into the target database (conditional)
If you exported the long columns in Section 3, modify the aufullimp.dat file with the following:
Set userid to "sys/[sys password] as sysdba".
Set file to the dump file containing the long tables (longexp by default).
Change the log file name.
Uncomment the ignore parameter.
Add a line with the parameter "rows=n".
Start an import session on the target database server node using the customized import parameter file. Use the following command:
$ imp parfile=aufullimp.dat
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;
--------------------------------------------------------------------------------
Section 5: Update the imported Release 12 database instance
This section describes how to recreate the database objects and relationships that are not handled by the export and import utilities.
Reset Advanced Queues
Copy the auque2.sql script that was generated in Section 1 from the working directory in the source database server node to the working directory in the target database server node. Then, on the target database server node, as the owner of the Oracle 10g file system and database instance, use SQL*Plus to connect to the target database as SYSDBA and run the auque2.sql script to enable the Advanced Queue settings that were lost during the export/import process. The script creates a log file in the current directory.
$ sqlplus /nolog
SQL> connect / as sysdba;
SQL> @auque2.sql
Start the new database listener (conditional)
If the Oracle Net listener for the database instance in the new Oracle home has not been started, you must start it now. Since AutoConfig has not yet been implemented, start the listener with the lsnrctl executable (UNIX/Linux) or Services (Windows). See the Oracle Database Net Services Administrator's Guide, 10g Release 2 (10.2) for more information.
Attention: Set the TNS_ADMIN environment variable to the directory where you created your listener.ora and tnsnames.ora files.
Run adgrants.sql
Copy $APPL_TOP/admin/adgrants.sql (adgrants_nt.sql for Windows) from the administration server node to the working directory in the database server node. Use SQL*Plus to connect to the database as SYSDBA and run the script using the following command:
$ sqlplus "/ as sysdba" @adgrants.sql (or adgrants_nt.sql) \
[APPS schema name]
Note: Verify the usage of adgrants.sql in the adgrants.sql script. Older versions of adgrants.sql require the APPLSYS schema name parameter to be passed instead of APPS.
Grant create procedure privilege on CTXSYS
Copy $AD_TOP/patch/115/sql/adctxprv.sql from the administration server node to the database server node. Use SQL*Plus to connect to the database as APPS and run the script using the following command:
$ sqlplus apps/[APPS password] @adctxprv.sql \
[SYSTEM password] CTXSYS
Apply patch 6494466 (conditional)
If the target database is Windows and the source is not, apply patch 6494466 on the target database tier. Create the appsutil directory if needed.
Implement and run AutoConfig
Implement and run AutoConfig in the new Oracle home on the database server node. If the database listener of the new Oracle home is defined differently than the old Oracle home, you must also run AutoConfig on each application tier server node to update the system with the new listener.
See Using AutoConfig to Manage System Configurations in Oracle Applications Release 12 on My Oracle Support, especially section 3.2, for instructions on how to implement and run AutoConfig.
Shut down all processes, including the database and the listener, and restart them to load the new environment settings.
Gather statistics for SYS schema
Use SQL*Plus to connect to the database as SYSDBA and use the following commands to restart the database in restricted mode, run adstats.sql, and restart the database in normal mode:
$ sqlplus "/ as sysdba"
SQL> shutdown normal;
SQL> startup restrict;
SQL> @adstats.sql
SQL> shutdown normal;
SQL> startup;
SQL> exit;
Attention: Make sure that you have at least 1.5 GB of free default temporary tablespace.
Re-create custom database links (conditional)
If the Oracle Net listener in the 10.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.
Create ConText and AZ objects
Certain ConText objects and the AZ objects dependent on the tables with XML type columns are not preserved by the import process. The consolidated export/import utility patch that you applied to the source administration server node in Section 1 contains a perl script, dpost_imp.pl, that you can run to generate an AutoPatch driver file. You use this driver file to call the scripts that create these objects. Run the following command:
$ perl $AU_TOP/patch/115/bin/dpost_imp.pl [driver file] 10
Once the driver file has been generated, use AutoPatch to apply it on the target administration server node.
Import tables with XML type columns into the target database
Modify the aufullimp.dat file with the following:
Set userid to "az/[az password]"
Set file to the dump file containing the tables with XML types (xmlexp by default).
Change the log file name.
Comment out the ignore and rows parameters.
Start an import session on the target database server node using the customized import parameter file. Use the following command:
$ imp parfile=aufullimp.dat
Once the import is complete, you can delete the export dump files, as well as the export and import parameter files, from the source and target database server nodes.
Populate CTXSYS.DR$SQE table
To populate the CTXSYS.DR$SQE table, use SQL*Plus on the database server node to connect to the Applications database instance as APPS and run the following command:
$ sqlplus apps/[apps password]
SQL> exec icx_cat_sqe_pvt.sync_sqes_for_all_zones;
Compile invalid objects
On the target database server node, as the owner of the Oracle 10g file system and database instance, use SQL*Plus to connect to the target database as SYS and run the $ORACLE_HOME/rdbms/admin/utlrp.sql script to compile invalid objects.
$ sqlplus "/ as sysdba" @$ORACLE_HOME/rdbms/admin/utlrp.sql
Maintain Applications database objects
Run AD Administration on the target administration server node. From the Maintain Applications Database Objects menu, perform the following tasks:
Compile flexfield data in AOL tables
Recreate grants and synonyms for APPS schema
Start Applications server processes
Start all the server processes on the target Applications system. You can allow users to access the system at this time.
Create DQM indexes
Create DQM indexes by following these steps:
Log on to Oracle Applications with the "Trading Community Manager" responsibility
Click Control > Request > Run
Select "Single Request" option
Enter "DQM Staging Program" name
Enter the following parameters:
Number of Parallel Staging Workers: 4
Staging Command: CREATE_INDEXES
Continue Previous Execution: NO
Index Creation: SERIAL
Click "Submit"
--------------------------------------------------------------------------------
Change Record
The following sections were changed in this document.
Date Summary of Changes
10-Dec-2007 Initial release
11-Jan-2008 Changed export/import patch 6258200 to 6723741
3-Jul-2008 Modified AutoConfig related instructions
Added step to populate CTXSYS.DR$SQE table
Added patch 6494466
Updated export/import patch to 6924477
Added instructions related to the exempt access policy grant.
Added aucrdb.sql attention box
25-May-2009 Added attention statement to see 339938.1 when encountering ORA-932
11-Sep-2009 Added Database Vault information
Added revoke exempt access policy for target system
16-Nov-2009 Modified OracleMetaLink to My Oracle Support
Modified AutoConfig step numbers as step numbers in AutoConfig has changed
Modified adgrants.sql to run with APPS parameter
Modified export/import patch to 7120092
Incorporating 12.1 into the document
Modified step to create 9idata directory to ensure directory exists
Replaced Interoperability note links from 454750.1 to 812362.1
2-Jul-2010 Made all steps related to export/import of long columns conditional on RDBMS version
Changed expdp and impdp to run as SYS schema
Note 454616.1 by Oracle Applications Development
Copyright 2007 Oracle USA
Last modified: Friday, July 2, 2010
Related
--------------------------------------------------------------------------------
Products
--------------------------------------------------------------------------------
Oracle E-Business Suite > Applications Technology > Technology Components > Oracle Applications Technology Stack
Errors
--------------------------------------------------------------------------------
XP-56; XP-0; EXP-0; EXP-56; ORA-932; ERROR 932
Back to top
Rate this document Article Rating
Rate this document
Excellent
Good
Poor
Did this document help you?
Yes
No
Just browsing
How easy was it to find this document?
Very easy
Somewhat easy
Not easy
Comments
Provide feedback for this article. Please use 'Contact Us' for other feedback.
Important Note: this feedback is anonymously visible to other customers until processed by Oracle Support.
Cancel
--------------------------------------------------------------------------------
Modified 02-JUL-2010 Type WHITE PAPER Status PUBLISHED
Export/Import Process for Oracle E-Business Suite Release 12
Database Instances Using Oracle Database 10g Release 2
July 2010
--------------------------------------------------------------------------------
This document describes the process of re-creating an existing Applications Release 12 database instance using the datapump utilities. The most current version of these notes is document 454616.1 on My Oracle Support. There is a change log at the end of this document.
The datapump utilities allow you to move existing data in Oracle format to and from Oracle databases. For example, datapump export files can archive database data, or move data among different Oracle databases that run on the same or different operating systems. This document assumes that you are already familiar with datapump.
There are special considerations when exporting or importing an Applications Release 12 database instance. This process consists of five discrete steps. Each step is covered in a separate section in this document.
Section 1: Prepare the source system
Tasks that must be performed to prepare the source system for the database export
Section 2: Prepare a target Release 12 database instance
Tasks for creating an empty database instance in preparation for import
Section 3: Export the source Release 12 database instance
Tasks that must be performed to produce a valid export of an Applications Release 12 database instance
Section 4: Import the Release 12 database instance
Tasks for running the import utility
Section 5: Update the imported Release 12 database instance
Tasks that must be performed to restore the imported Applications Release 12 database instance to a fully functional state
The source (export from) and target (import to) ORACLE_HOME directories must be Oracle Database 10g Release 2 (10.2.0).
The export/import process requires the use of both the datapump utilities (expdp/impdp) and the traditional export/import (exp/imp). For more information, read Oracle Database Utilities 10g Release 2 (10.2).
Attention: This document uses UNIX/Linux syntax when describing directory structures. However, it applies to Windows servers as well. Where there is a significant difference in tasks for Windows, specific instructions are given.
Some of the tasks in this document affect the APPL_TOP of one or more application server tiers. Those tasks require that the Applications file system environment be enabled by running the APPSORA.env file (for UNIX or Linux) or the envshell.cmd file (for Windows) prior to performing the tasks. Other tasks affect the Applications database instance. Those tasks require that the Oracle 10g environment be enabled by running the [ORACLE_SID].env/cmd file under the Oracle 10g Oracle home on the database server node prior to performing the tasks. In addition, you may have more than one Oracle home installed on the database server node, so it is important that you run the correct [ORACLE_SID].env/cmd file before performing tasks that affect the database instance. Read the instructions carefully to determine which environment should be enabled for each step.
Attention: This document assumes that the source and target application server tiers are the same. Creating new application server tiers for the target environment has to be done either before starting or after completing all the steps in this document. Then, update and run AutoConfig for the source database and application server tiers to enable the source environment.
Attention: If you are using Oracle Database Vault, refer to Note 822048.1 before performing any step in this document.
--------------------------------------------------------------------------------
Section 1: Prepare the source system
This section describes how to ensure that you have the required patches, create your export file, and capture important information that is required to import your database.
Apply latest AutoConfig patches
Perform steps 3.1 and 3.2.1 of the Using AutoConfig to Manage System Configurations in Oracle Applications Release 12 document. The other steps in 3.2 are not necessary as they will be done at the target side.
Apply the Applications consolidated export/import utility patch
Apply patch 7120092 to the source administration server node. This patch provides several SQL scripts that facilitate exporting and importing an Applications database instance, export and import parameter files, and a perl script, which creates an AD patch driver.
Apply latest Applications database preparation scripts patch (conditional)
If you are using Oracle E-Business Suite Release 12.0, apply patch 6342289 to every application tier server node in the source system.
Create a working directory
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
Generate target database instance creation script aucrdb.sql
The target database instance must be created with the same tablespace and file structure as the source database instance. The export/import patch provides the auclondb.sql script which generates the aucrdb.sql script, which you use to create the target database instance with the appropriate tablespace and file structure. The script converts all tablespaces except for SYSTEM to locally managed tablespaces with auto segment space management, if they are not already so.
On the source administration server node, use SQL*Plus to connect to the database as SYSTEM and run the $AU_TOP/patch/115/sql/auclondb.sql script. It creates aucrdb.sql in the current directory.
$ sqlplus system/[system password] \
@$AU_TOP/patch/115/sql/auclondb.sql 10
Record Advanced Queue settings
Advanced Queue settings are not propagated in the target database instance during the export/import process. Therefore, you must record them beforehand and enable them in the target database instance afterwards. The export/import patch contains auque1.sql, which generates a script called auque2.sql. You can use auque2.sql to enable the settings in the target database instance.
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. Then, on the source database server node, as the owner of the source database server file system and database instance, use SQL*Plus to connect to the source database as sysdba and run the auque1.sql script. It generates auque2.sql.
$ sqlplus /nolog
SQL> connect / as sysdba;
SQL> @auque1.sql
Create parameter file for tables with long columns (conditional)
The fix to this issue is part of 10.2.0.5. If you are on 10.2.0.4 or prior versions of 10g Release 2, tables with long columns may not propagate properly in datapump. Therefore, they have to be migrated separately using the traditional export/import utilities.
Copy the aulong.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. Then, on the source database server node, as the owner of the source database server file system and database instance, use SQL*Plus to connect to the source database as sysdba and run the aulong.sql script. It generates aulongexp.dat.
$ sqlplus /nolog
SQL> connect system/[system password];
SQL> @aulong.sql
Remove rebuild index parameter in spatial indexes
Ensure that you do not have the rebuild index parameter in the spatial indexes. To see if you have any rebuild index parameters, on the source database server node, as the owner of the source database server file system and database instance, use SQL*Plus to connect to the source database as sysdba and run the following command:
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.
Deregister the current database server (conditional)
If you plan to change the database port, host, SID, or database name parameter on the database server, deregister the current database server node by running 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
contextfile=$CONTEXT_FILE -removeserver
--------------------------------------------------------------------------------
Section 2: Prepare a target Release 12 database instance
This section describes how to create the empty target database and populate it with all of the required system objects prior to running import.
The Oracle home of the target database instance can be the same Oracle home that the source database instance uses, or it can be different (on another machine running a different operating system, for example), as long as it uses Oracle Database 10g Release 2 Enterprise Edition.
Create target Oracle 10g Oracle home (conditional)
If you want the target Oracle 10g Oracle home to be separate from the source Oracle home, you must create it now. Decide whether you want to install the 10.2.0 Oracle home manually, or use the Rapid Install to create it for you.
If you choose to use Rapid Install, you must use Rapid Install Release 12.0.0. As the owner of the Oracle RDBMS file system, start the Rapid Install wizard by typing:
$ rapidwiz -techstack
Choose the "10gR2 RDBMS" option in the techstack components window and provide the details for the new Oracle home. Make sure that the SID environment setting is set to the same value as your existing database instance.
If you choose to manually install the 10.2.0 Oracle home, log in to the database server node as the owner of the Oracle RDBMS file system and database instance and perform the following steps:
Ensure that environment settings, such as ORACLE_HOME, are set for the new Oracle home you are about to create.
Perform all the steps in Chapter 3 of the Oracle Database Installation Guide 10g Release 2 (10.2), for your platform.
In the subsequent windows, click on the Product Languages button and select any languages other than American English that are used by your Applications database instance, choose the Enterprise Edition installation type, and select the options not to upgrade an existing database and to install the database software only.
Perform tasks in section 3.5, "Installing Oracle Database 10g Products" in the Oracle Database Companion CD Installation Guide for your platform. Do not perform the tasks in the "Preparing Oracle Workflow Server for the Oracle Workflow Middle Tier Installation" section.
In the Installation Types window, click on the Product Languages button to select any languages other than American English that are used by your Applications database instance.
Make sure that the following environment variables are set whenever you enable the 10g Oracle home:
ORACLE_HOME points to the new 10.2.0 Oracle home.
PATH includes $ORACLE_HOME/bin and the directory where the new perl executable is located (usually $ORACLE_HOME/perl/bin).
LD_LIBRARY_PATH includes $ORACLE_HOME/lib.
PERL5LIB points to the directories where the new perl libraries are located (usually $ORACLE_HOME/perl/lib/[perl version] and $ORACLE_HOME/perl/lib/site_perl/[perl version])
Run the $ORACLE_HOME/nls/data/old/cr9idata.pl script to create the $ORACLE_HOME/nls/data/9idata directory. After creating the directory, make sure that the ORA_NLS10 environment variable is set to the full path of the 9idata directory whenever you enable the 10g Oracle home.
Attention: Check to make sure the $ORACLE_HOME/nls/data/9idata directory is created and is non-empty.
Attention (for Windows users): Keep track of the database home name used. For Rapidwiz installed Oracle homes, the home name is [SID]_db102_RDBMS. For manually installed Oracle homes, the home name is what you input when creating the Oracle home.
Upgrade to the latest 10.2.0 patch set (conditional)
If you are not on the latest patch set, perform the following steps from the Oracle E-Business Suite Release 12 with Oracle Database 10g Release 2 (10.2.0) Interoperability Notes on My Oracle Support:
Perform 10.2.0.x Patch Set installation tasks
Apply additional 10.2.0.x RDBMS patches
Do not perform any post-installation patch README steps.
Modify sqlnet.ora file (Windows only)
If the target database server node is running Windows, add the following line to the sqlnet.ora file in the %ORACLE_HOME%\network\admin\[SID] directory, if it does not already exist:
SQLNET.AUTHENTICATION_SERVICES=(NTS)
Create the target initialization parameter file and CBO parameter file
The initialization parameter file (init[SID].ora) and cost-based optimizer (CBO) parameter file (ifilecbo.ora) are located in the $ORACLE_HOME/dbs directory on the source database server node. Copy both files to the Oracle 10g $ORACLE_HOME/dbs directory on the target database server node.
Refer to Database Initialization Parameters for Oracle Applications Release 12 and update both the init.ora and ifilecbo.ora files with any necessary changes. You may also need to update initialization parameters involving the db_name, control_files, and directory structures.
Ensure that the undo_tablespace parameter in the initialization parameter file of the target database instance matches with the default undo tablespace set in the aucrdb.sql script.
Ignore the initialization parameters that pertain to the native compilation of PL/SQL code. You will be instructed to add them later, if necessary.
Create a working directory
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
Create the target database instance
Copy the aucrdb.sql script, generated in Section 1, from the source administration server node to the working directory in the target database server node. Then update the script on the target database server node with any necessary changes to the directory structures for the log file(s), data file(s), or tablespaces, reflecting the layout of the target database server node. If the target database server node is running Windows, update the directory structure from UNIX/Linux format to Windows format or vice versa.
Attention: Using the source tablespace information does not guarantee that the target tablespaces will be enough. It is highly recommended that you go through the source dba_free_space table to see which of the tablespaces are running out and modify the aucrdb.sql script to ensure ample tablespace size on the target database.
Make sure that the environment of your session on the target database server node is set up properly for the target database instance, especially the ORACLE_HOME, ORACLE_SID, and ORA_NLS10 environment settings. (ORACLE_SID must be set to the same value as the db_name parameter in the init[SID].ora file.) Then, use the following commands to run aucrdb.sql and create the target database instance:
$ sqlplus /nolog
SQL> connect / as sysdba;
SQL> spool aucrdb.log;
For UNIX or Linux:
SQL> startup nomount;
SQL> @aucrdb.sql
SQL> exit;
For Windows:
SQL> startup nomount pfile=%ORACLE_HOME%\dbs\init%ORACLE_SID%.ora
SQL> @aucrdb.sql
SQL> exit;
If PL/SQL of the source database was natively compiled, see the "Compiling PL/SQL Code for Native Execution" section of Chapter 11 of Oracle Database PL/SQL User's Guide and Reference 10g Release 2 (10.2) for instructions on how to natively compile PL/SQL in the target database. Add the parameters that pertain to the native compilation where specified. Do not use the natively compiled code generated by the source database. Oracle does not support switching the PL/SQL compilation mode from interpreted to native (and vice-versa) for an export/import. Exporting/importing using native mode takes significantly more time than interpreted mode.
When the target database instance has been created, restart the database instance.
Copy database preparation scripts to target Oracle home
The database preparation scripts that you applied to the source administration server node in Section 1 contain four scripts that are needed on the target database server node. Copy the following files from the $APPL_TOP/admin directory of the source administration server node to the working directory in the target database server node: addb1020.sql, adsy1020.sql, adjv1020.sql, and admsc1020.sql (UNIX or Linux) or addb1020_nt.sql, adsy1020_nt.sql, adjv1020_nt.sql, and admsc1020_nt.sql (Windows).
As you run each of the next four steps, note the following:
The remarks section at the beginning of each script contains additional information.
Each script creates a log file in the current directory.
Set up the SYS schema
The addb1020.sql or addb1020_nt.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 addb1020.sql (UNIX/Linux) or addb1020_nt.sql (Windows).
Here is an example on UNIX or Linux:
$ sqlplus "/ as sysdba" @addb1020.sql
Set up the SYSTEM schema
The adsy1020.sql or adsy1020_nt.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 adsy1020.sql (UNIX/Linux) or adsy1020_nt.sql (Windows).
Here is an example on UNIX or Linux:
$ sqlplus system/[system password] @adsy1020.sql
Install Java Virtual Machine
The adjv1020.sql or adjv1020_nt.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 adjv1020.sql (UNIX/Linux) or adjv1020_nt.sql (Windows).
Here is an example on UNIX or Linux:
$ sqlplus system/[system password] @adjv1020.sql
Attention: This script can be run only once in a given database instance, because the scripts that it calls are not rerunnable.
Install other required components
The admsc1020.sql or admsc1020_nt.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 run admsc1020.sql (UNIX/Linux) or admsc1020_nt.sql (Windows). You must pass the following arguments to the script, in the order specified:
Argument Value
remove context? FALSE
SYSAUX tablespace SYSAUX
temporary tablespace TEMP
Here is an example on UNIX or Linux:
$ sqlplus system/[system password] \
@admsc1020.sql FALSE SYSAUX TEMP
Attention: All of the components are created in the SYSAUX tablespace regardless of where it was installed in the source database.
Disable automatic gathering of statistics
Copy $APPL_TOP/admin/adstats.sql from the administration server node to the target database server node. 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;
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.
--------------------------------------------------------------------------------
Section 3: Export the source Release 12 database instance
This section describes how to ensure that you have the required patches, create your export file, and capture important information that is required to import your database.
Create the export parameter file
A template for the export parameter file has been included as part of the the export/import patch. Copy $AU_TOP/patch/115/import/auexpdp.dat from the source administration server node to the working directory in the source database server node. Use a text editor to modify the file to reflect the source environment and other customized parameters.
The customizable parameters are:
Parameter Description Template Value
directory directory where the export dump files will be created dmpdir
dumpfile export dump file name(s) aexp%U.dmp
filesize export dump file size 1GB
log log file name expdpapps.log
interMedia, OLAP, and Data Mining schemas are not exported. The admsc1020.sql script creates these schemas in the target database. Ensure that the schema names in the exclude parameters reflect those in your database.
Create a directory in the SYS schema that corresponds to the directory specified in the template. Here is an example of how to create a directory named dmpdir:
$ sqlplus "/ as sysdba"
SQL> create directory dmpdir as '/u01/expimp';
Do not change the other parameters.
The export process uses as many of the listed file names as necessary to hold the exported data. You must ensure that the number of dump files specified, as well as the size of each dump file, is sufficient to contain all the data in your source database instance.
Shut down Applications server processes
Shut down all Applications server processes except the database and the Net8 listener for the database. Users cannot use the Applications until the import is completed.
Grant privilege to source system schema
Grant the exempt access policy privilege to system by using SQL*Plus to connect to the database as SYSDBA and run the following command:
SQL> grant EXEMPT ACCESS POLICY to system;
Export the Applications database instance
Start an export session on the source database server node using the customized export parameter file. Use the following command:
$ expdp "'/ as sysdba'" parfile=[export parameter file name]
Typically, the export runs for several hours.
Attention: See document 339938.1 on My Oracle Support if you encounter the failure:
EXP-00056: ORACLE error 932 encountered
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
EXP-00000: Export terminated unsuccessfully
Export tables with long columns (conditional)
If you created aulongexp.dat by running aulong.sql in Section 1, start an export session on the source database server node using the following command:
$ exp parfile=aulongexp.dat
Export tables with XML type columns
Copy $AU_TOP/patch/115/import/auxmlexp.dat from the source administration server to the working directory in the source database server node. Start an export session on the source database server node using the following command:
$ exp parfile=auxmlexp.dat
Revoke privilege from source 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;
--------------------------------------------------------------------------------
Section 4: Import the Release 12 database instance
This section describes how to use the import utility to load the Oracle Applications data into the target database.
Create the import parameter files
Copy auimpdp.dat, aufullimp.dat, and auimpusr.dat from the $AU_TOP/patch/115/import directory in the source administration server node to the working directory in the target database server node. Make sure that the directory, dumpfile, and logfile parameters in auimpdp.dat and auimpusr.dat are set properly.
Create a directory in the system schema with the name set to the directory specified in the template and the path set to where the export dump files will reside. Here is an example of how to create a directory named dmpdir:
$ sqlplus system/[system password]
SQL> create directory dmpdir as '/u01/expimp';
Save the changed file.
Copy the export dump files
Copy the export dump files from the source database server node to the working directory in the target database server node.
Import the users into the target database (conditional)
If you exported the long columns in Section 3, start an import session on the target database server node using the customized import parameter file. Use the following command:
$ impdp system/[system password] parfile=auimpusr.dat
Import tables with long columns into the target database (conditional)
If you exported the long columns in Section 3, modify the aufullimp.dat file with the following:
Set userid to "sys/[sys password] as sysdba".
Set file to the dump file containing the long tables (longexp by default).
Set the log file appropriately.
Leave the ignore parameter commented out.
Import the tables using the following command:
$ imp parfile=aufullimp.dat
Attention: You will get failures for the triggers as the dependent tables have not yet been imported.
Import the Applications database instance
If you exported the long columns in Section 3, remove or comment out all the exclude parameters in the auimpdp.dat parameter file. Start an import session on the target database server node using the auimpdp.dat parameter file. Use the following command:
$ impdp "'/ as sysdba'" parfile=auimpdp.dat
Typically, import runs for several hours.
Import triggers into the target database (conditional)
If you exported the long columns in Section 3, modify the aufullimp.dat file with the following:
Set userid to "sys/[sys password] as sysdba".
Set file to the dump file containing the long tables (longexp by default).
Change the log file name.
Uncomment the ignore parameter.
Add a line with the parameter "rows=n".
Start an import session on the target database server node using the customized import parameter file. Use the following command:
$ imp parfile=aufullimp.dat
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;
--------------------------------------------------------------------------------
Section 5: Update the imported Release 12 database instance
This section describes how to recreate the database objects and relationships that are not handled by the export and import utilities.
Reset Advanced Queues
Copy the auque2.sql script that was generated in Section 1 from the working directory in the source database server node to the working directory in the target database server node. Then, on the target database server node, as the owner of the Oracle 10g file system and database instance, use SQL*Plus to connect to the target database as SYSDBA and run the auque2.sql script to enable the Advanced Queue settings that were lost during the export/import process. The script creates a log file in the current directory.
$ sqlplus /nolog
SQL> connect / as sysdba;
SQL> @auque2.sql
Start the new database listener (conditional)
If the Oracle Net listener for the database instance in the new Oracle home has not been started, you must start it now. Since AutoConfig has not yet been implemented, start the listener with the lsnrctl executable (UNIX/Linux) or Services (Windows). See the Oracle Database Net Services Administrator's Guide, 10g Release 2 (10.2) for more information.
Attention: Set the TNS_ADMIN environment variable to the directory where you created your listener.ora and tnsnames.ora files.
Run adgrants.sql
Copy $APPL_TOP/admin/adgrants.sql (adgrants_nt.sql for Windows) from the administration server node to the working directory in the database server node. Use SQL*Plus to connect to the database as SYSDBA and run the script using the following command:
$ sqlplus "/ as sysdba" @adgrants.sql (or adgrants_nt.sql) \
[APPS schema name]
Note: Verify the usage of adgrants.sql in the adgrants.sql script. Older versions of adgrants.sql require the APPLSYS schema name parameter to be passed instead of APPS.
Grant create procedure privilege on CTXSYS
Copy $AD_TOP/patch/115/sql/adctxprv.sql from the administration server node to the database server node. Use SQL*Plus to connect to the database as APPS and run the script using the following command:
$ sqlplus apps/[APPS password] @adctxprv.sql \
[SYSTEM password] CTXSYS
Apply patch 6494466 (conditional)
If the target database is Windows and the source is not, apply patch 6494466 on the target database tier. Create the appsutil directory if needed.
Implement and run AutoConfig
Implement and run AutoConfig in the new Oracle home on the database server node. If the database listener of the new Oracle home is defined differently than the old Oracle home, you must also run AutoConfig on each application tier server node to update the system with the new listener.
See Using AutoConfig to Manage System Configurations in Oracle Applications Release 12 on My Oracle Support, especially section 3.2, for instructions on how to implement and run AutoConfig.
Shut down all processes, including the database and the listener, and restart them to load the new environment settings.
Gather statistics for SYS schema
Use SQL*Plus to connect to the database as SYSDBA and use the following commands to restart the database in restricted mode, run adstats.sql, and restart the database in normal mode:
$ sqlplus "/ as sysdba"
SQL> shutdown normal;
SQL> startup restrict;
SQL> @adstats.sql
SQL> shutdown normal;
SQL> startup;
SQL> exit;
Attention: Make sure that you have at least 1.5 GB of free default temporary tablespace.
Re-create custom database links (conditional)
If the Oracle Net listener in the 10.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.
Create ConText and AZ objects
Certain ConText objects and the AZ objects dependent on the tables with XML type columns are not preserved by the import process. The consolidated export/import utility patch that you applied to the source administration server node in Section 1 contains a perl script, dpost_imp.pl, that you can run to generate an AutoPatch driver file. You use this driver file to call the scripts that create these objects. Run the following command:
$ perl $AU_TOP/patch/115/bin/dpost_imp.pl [driver file] 10
Once the driver file has been generated, use AutoPatch to apply it on the target administration server node.
Import tables with XML type columns into the target database
Modify the aufullimp.dat file with the following:
Set userid to "az/[az password]"
Set file to the dump file containing the tables with XML types (xmlexp by default).
Change the log file name.
Comment out the ignore and rows parameters.
Start an import session on the target database server node using the customized import parameter file. Use the following command:
$ imp parfile=aufullimp.dat
Once the import is complete, you can delete the export dump files, as well as the export and import parameter files, from the source and target database server nodes.
Populate CTXSYS.DR$SQE table
To populate the CTXSYS.DR$SQE table, use SQL*Plus on the database server node to connect to the Applications database instance as APPS and run the following command:
$ sqlplus apps/[apps password]
SQL> exec icx_cat_sqe_pvt.sync_sqes_for_all_zones;
Compile invalid objects
On the target database server node, as the owner of the Oracle 10g file system and database instance, use SQL*Plus to connect to the target database as SYS and run the $ORACLE_HOME/rdbms/admin/utlrp.sql script to compile invalid objects.
$ sqlplus "/ as sysdba" @$ORACLE_HOME/rdbms/admin/utlrp.sql
Maintain Applications database objects
Run AD Administration on the target administration server node. From the Maintain Applications Database Objects menu, perform the following tasks:
Compile flexfield data in AOL tables
Recreate grants and synonyms for APPS schema
Start Applications server processes
Start all the server processes on the target Applications system. You can allow users to access the system at this time.
Create DQM indexes
Create DQM indexes by following these steps:
Log on to Oracle Applications with the "Trading Community Manager" responsibility
Click Control > Request > Run
Select "Single Request" option
Enter "DQM Staging Program" name
Enter the following parameters:
Number of Parallel Staging Workers: 4
Staging Command: CREATE_INDEXES
Continue Previous Execution: NO
Index Creation: SERIAL
Click "Submit"
--------------------------------------------------------------------------------
Change Record
The following sections were changed in this document.
Date Summary of Changes
10-Dec-2007 Initial release
11-Jan-2008 Changed export/import patch 6258200 to 6723741
3-Jul-2008 Modified AutoConfig related instructions
Added step to populate CTXSYS.DR$SQE table
Added patch 6494466
Updated export/import patch to 6924477
Added instructions related to the exempt access policy grant.
Added aucrdb.sql attention box
25-May-2009 Added attention statement to see 339938.1 when encountering ORA-932
11-Sep-2009 Added Database Vault information
Added revoke exempt access policy for target system
16-Nov-2009 Modified OracleMetaLink to My Oracle Support
Modified AutoConfig step numbers as step numbers in AutoConfig has changed
Modified adgrants.sql to run with APPS parameter
Modified export/import patch to 7120092
Incorporating 12.1 into the document
Modified step to create 9idata directory to ensure directory exists
Replaced Interoperability note links from 454750.1 to 812362.1
2-Jul-2010 Made all steps related to export/import of long columns conditional on RDBMS version
Changed expdp and impdp to run as SYS schema
Note 454616.1 by Oracle Applications Development
Copyright 2007 Oracle USA
Last modified: Friday, July 2, 2010
Related
--------------------------------------------------------------------------------
Products
--------------------------------------------------------------------------------
Oracle E-Business Suite > Applications Technology > Technology Components > Oracle Applications Technology Stack
Errors
--------------------------------------------------------------------------------
XP-56; XP-0; EXP-0; EXP-56; ORA-932; ERROR 932
Back to top
Rate this document Article Rating
Rate this document
Excellent
Good
Poor
Did this document help you?
Yes
No
Just browsing
How easy was it to find this document?
Very easy
Somewhat easy
Not easy
Comments
Provide feedback for this article. Please use 'Contact Us' for other feedback.
Important Note: this feedback is anonymously visible to other customers until processed by Oracle Support.
Cancel
Subscribe to:
Posts (Atom)