Export/Import Process for Oracle E-Business Suite Release 12.0 or 12.1
Database Instances Using Oracle Database 11g Release 1 or 11g Release 2
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. |
| 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.We migrated database 11.1.0.7 from windows 2003 32 bit to Linux 5.10 64 bit.
|
| Attention: You will get failures for the triggers as the dependent tables have not yet been imported. |
5.Import the Applications database instance
If your source database is Oracle Database 11g (11.x), leave the exclude parameters commented out
in the auimpdp.dat parameter file.
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/manager parfile=auimpdp.dat
If your source database is Oracle Database 11g (11.x), leave the exclude parameters commented out
in the auimpdp.dat parameter file.
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/manager parfile=auimpdp.dat
6. We Skip in our case
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
7.Import OLAP analytical workspaces (conditional)
If you exported OLAP analytical workspaces, perform the detailed step 7 as documented in My Oracle Support Note 352306.1
to import the OLAP analytical workspaces that were previously exported from the source machine.
cd /opt/expimp
sqlplus /nolog
SQL> conn / as sysdba
exec dbms_aw.execute('aw create APPS.ODPCODE');
exec dbms_aw.execute('import all from eif file ''dmpdir/EXP_APPS_ODPCODE.EIF'' data dfns');
exec dbms_aw.execute('update');
commit;
exec dbms_aw.execute('aw detach APPS.ODPCODE');
exec dbms_aw.execute('aw create APPS.XWDEVKIT');
exec dbms_aw.execute('import all from eif file ''dmpdir/EXP_APPS_XWDEVKIT.EIF'' data dfns');
exec dbms_aw.execute('update');
commit;
exec dbms_aw.execute('aw detach APPS.XWDEVKIT');
exec dbms_aw.execute('aw create FPA.FPAPJP');
exec dbms_aw.execute('import all from eif file ''dmpdir/EXP_FPA_FPAPJP.EIF'' data dfns');
exec dbms_aw.execute('update');
commit;
exec dbms_aw.execute('aw detach FPA.FPAPJP');
if error of olap
HOW TO CREATE OLAP
cd /opt/oracle/db/tech_st/11.1.0/olap/admin/
sqlplus /nolog
SQL> conn / as sysdba
SQL> spool add_olap.log
SQL> @olap.sql SYSAUX TEMP;
SQL> spool off8.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.
1.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
2.Start the new database listener (conditional)
set env
netca --to configure listner
Next
Next
Name : RPOD
Next
Next
Next
Next
Next
Finsh
PROD =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = qamar.com)(PORT = 1521))
)
)
SID_LIST_PROD =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME= /opt/oracle/db/tech_st/11.1.0)
(SID_NAME = PROD)
)
)
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.
cd /home.oracle
vi qamar.env
export ORACLE_HOME=/opt/oracle/db/tech_st/11.1.0
export ORACLE_SID=PROD
export TNS_ADMIN=/opt/oracle/db/tech_st/11.1.0/network/admin
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch/:$ORACLE_HOME/perl/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PERL5LIB=$ORACLE_HOME/perl/lib/5.8.3:$ORACLE_HOME/perl/lib/site_perl/5.8.3
export ORA_NLS10=$ORACLE_HOME/nls/data/9idata
3.
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 APPS
4. 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 @adctxprv.sql manager CTXSYS
5. In our case we Skip
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.
6.Deregister the current database server (conditional)
sqlplus apps/apps
exec fnd_conc_clone.setup_clean;
7 Implement and run AutoConfig
. On the Application Tier (as the APPLMGR user):
. Log in to the APPL_TOP environment and source the environment file.
. Create appsutil.zip file: "perl %AD_TOP%\bin\admkappsutil.pl".
(This will create appsutil.zip in $APPL_TOP/admin/out/appsutil.zip)
. Copy or FTP the appsutil.zip file to the RDBMS $ORACLE_HOME.
. On the Database Tier (as the ORACLE user):
$ cd $ORACLE_HOME
$ unzip -o appsutil.zip
. Generate the Database Context File:
Context File Creation on UNIX
$ cd $ORACLE_HOME
$ . <SID>_<HOST>.env
$ cd $ORACLE_HOME/appsutil/bin
$ perl adbldxml.pl tier=db appsuser=<APPSuser> appspasswd=<APPSpwd>
perl adbldxml.pl appsuser=apps appspass=apps jtop=/opt/oracle/db/tech_st/11.1.0/jdk/
su -
give rights to Oracle User
chown -R oracle:oinstall /opt
chmod -R 777/opt
sh adconfig.sh contextfile=/opt/oracle/db/tech_st/11.1.0/appsutil/PROD_qamar.xml appspass=apps
exec fnd_cond_clone.setup_clean;
sh <RDBMS_ORACLE_HOME>/appsutil/scripts/<CONTEXT_NAME>/adautocfg.sh
on apps tier
enter database in hosts
run autoconfig on apps node
On Windows:
%INST_TOP%\admin\scripts\adautocfg.cmd
8.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;
9.Re-create custom database links (conditional)
select db_link from dba_db_links;
drop database link APPS_TO_APPS.com
create database link APPS_TO_APPS.TE.LAN connect to
apps identified by apps using
'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=qamar.com)
(PORT=1521))(CONNECT_DATA=(SID=PROD)))';
EDW_APPS_TO_WH.com
drop database link EDW_APPS_TO_WH.com
create database link EDW_APPS_TO_WH.TE.LAN connect to
apps identified by apps using
'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=qamar.com)
(PORT=1521))(CONNECT_DATA=(SID=PROD)))';
10. Create ConText and AZ objects
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.
$ perl $AU_TOP/patch/115/bin/dpost_imp.pl qamar.drv 11
11. In our case we Skip (for 10gR2 Database)
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.
12.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
SQL> exec icx_cat_sqe_pvt.sync_sqes_for_all_zones;
13.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
14.Re-create XLA packages (conditional)
If you dropped the XLA packages in the source environment,
copy $XLA_TOP/patch/115/sql/xla6128278.sql
from the administration server node to the target working directory, use SQL*Plus to connect to the database as APPS, and run the following script to re-create the XLA packages:
$ sqlplus apps/apps
SQL> @xla6128278.sql xla6128278
In our case these are 46 verify
select count(*)
from dba_objects db, xla_subledgers xl
where db.object_type='PACKAGE BODY' and db.object_name like 'XLA%AAD%PKG'
and substr(db.object_name,1,9) = 'XLA_'||
LPAD(SUBSTR(TO_CHAR(ABS(xl.application_id)), 1, 5), 5, '0')
and db.object_name NOT IN ('XLA_AAD_HDR_ACCT_ATTRS_F_PKG','XLA_AMB_AAD_PKG')
order by 1;
IMPORTANT: TAKE BACKUP make tar of /opt
15. Maintain Applications database objects
Maintain Applications database objects
Run AD Administration on the target administration server node. From the Maintain Applications Database Objects menu, perform the following tasks:
a.Compile flexfield data in AOL tables
b.Recreate grants and synonyms for APPS schema
16. 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.
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.
$ imp parfile=aufullimp.dat
17. Create DQM indexes
Create DQM indexes by following these steps:
a.Log on to Oracle Applications with the "Trading Community Manager" responsibility
b.Click Control > Request > Run
c.Select "Single Request" option
d.Enter "DQM Staging Program" name
e.Enter the following parameters:
Number of Parallel Staging Workers: 4
Staging Command: CREATE_INDEXES
Continue Previous Execution: NO
Index Creation: SERIAL
f.Click "Submit"
Take back up: db_migration.tar.gz of /opt
References
NOTE:352306.1 - Migrating OLAP From 32 Bits to 64 Bits
NOTE:741818.1 - Export/import process for 12.0 or 12.1 using 11gR1 or 11gR2
NOTE:822048.1 - How To Export / Import Objects In Database Vault Environment
References
NOTE:352306.1 - Migrating OLAP From 32 Bits to 64 Bits
NOTE:741818.1 - Export/import process for 12.0 or 12.1 using 11gR1 or 11gR2
NOTE:822048.1 - How To Export / Import Objects In Database Vault Environment
Please Subscribe my blog Qamar Zahoor, YouTube Channel YouTube, Join the Facebook group
Facebook Group and do follow on Twitter Twitter to get knowledge of Oracle EBS, Database, Ecommerce, Amazon, Ebay and Digital Marketing. Keep learning.
No comments:
Post a Comment
Note: only a member of this blog may post a comment.