Export/import process for 12.0 or 12.1 using 11gR1 or 11gR2

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

Document 741818.1

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 APPS[ORACLE SID]_[HOST].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: 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.

Section 1: Prepare the source system

1. 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.0 or 12.1 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.

2. Apply the Applications consolidated export/import utility patch

Apply Patch 16541956

3. Create a working directory

D:\Oracle\expimp

4. Generate target database instance creation script aucrdb.sql

cd D:\Oracle\clone\apps\apps_st\appl\au\12.0.0\patch\115\sql

> sqlplus system/manager

@auclondb.sql 11

5. Record Advanced Queue settings

> sqlplus /nolog

SQL> connect / as sysdba;

SQL> @auque1.sql

6. Create parameter file for tables with long columns (conditional)

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

7.Remove rebuild index parameter in spatial indexes

connect system/manager and run

select * from dba_indexes where index_type=’DOMAIN’ and
upper(parameters) like ‘%REBUILD%’;

if rterns follwong four then

Connnect MST/mst on toad and run

alter index MST_MD_ADM_BNDS_N1 rebuild parameters (‘sdo_indx_dims=2 sdo_rtr_pctfree=10 tablespace=APPS_TS_TX_IDX’)

alter index MST_MD_HYDROS_N1 rebuild parameters (‘sdo_indx_dims=2 sdo_rtr_pctfree=10 tablespace=APPS_TS_TX_IDX’)

Connect HR/hr on toad and run

alter index PER_ADDRESSES_SPT rebuild parameters (‘sdo_indx_dims=2 sdo_rtr_pctfree=10 tablespace=APPS_TS_TX_IDX’)

alter index HR_LOCATIONS_SPT rebuild parameters (‘sdo_indx_dims=2 sdo_rtr_pctfree=10 tablespace=APPS_TS_TX_IDX’)

8. Synchronize Text indexes

sqlplus ‘/ as sysdba’
SQL> select pnd_index_owner,pnd_index_name,count(*)
from ctxsys.ctx_pending
group by pnd_index_owner,pnd_index_name;

exec ctx_ddl.sync_index (‘IBC.IBC_ATTRIBUTE_BUNDLES_CTX’);

exec ctx_ddl.sync_index (‘JTF.JTF_AMV_ITEMS_URL_CTX’);

exec ctx_ddl.sync_index (‘JTF.JTF_AMV_ITEMS_DESC_CTX’);

exec ctx_ddl.sync_index (‘JTF.JTF_AMV_ITEMS_NAME_CTX’);

exec ctx_ddl.sync_index (‘JTF.JTF_AMV_ITEMS_TEXT_CTX’);

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 Enterprise Edition.

1. 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.

Installation of Linux

Pre Req Doc 761566.1

Database Installation:

Doc ID 802875.1

Install the base 11.1.0 software

cd /home/oracle/patches/Database11.1/database/

./runInstaller

Select Oralce Database 11g
Click Next
Oracle Base Location: /opt/oracle
Oracle Home Location: /opt/oracle/db/tech_st/11.1.0
Unchek create database
Click Next
/home/oracle/oraInventory
oinstall
Click Next
click next
click install

Run both script using Root User in new terminal copy past
Click Ok
Click Exit
Click Yes

Install Oracle Database 11g Products from the 11g Examples CD (mandatory)

cd /oracle/home

qamar_11g.env should be created with proper values (verify twice crictical)

vi qamar.enb

export ORACLE_HOME=/opt/oracle/db/tech_st/11.1.0
export ORACLE_SID=PROD

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

.qamar.env

linux.x64_11gR1_examples

cd examples

./runInstaller

Click Next
Select Oracle Home and Verify Path then click next
Click Next
Click Install
Click Exit
Click Yes

Perform 11.1.0.7 patch set pre-installation tasks

On the database server node, as the owner of the Oracle 11g file system and database instance,

unzip and extract the 11.1.0.7 patch set file for your platform. Read the patch set notes (usually README.html).

Make sure you thoroughly understand the upgrade and patch set installation process before you begin.

Check My Oracle Support or contact Oracle Support Services to determine any known issues with the patch set and

its interoperability with Oracle E-Business Suite.

Perform the tasks in the “Preinstallation Tasks” section of the patch set notes (if they apply to your system).

nothing to do in our case

Perform 11.1.0.7 patch set installation tasks

Patch 6890831

On the database server node, as the owner of the Oracle RDBMS file system and database instance,

perform the tasks in the “Installing the Oracle Database 11g Patch Set Interactively” section of the patch set notes.

Make sure that you use the runInstaller (UNIX/Linux)

unzip p6890831

cd Disk1

./runInstaller

click next

Verify patch and selct oracle home click next

Click Next

Click Install

Run both script using Root User in new terminal copy past

Click Ok

Click Exit

Click Yes

Create nls/data/9idata directory

On the database server node, as the owner of the Oracle RDBMS file system and database instance,

run the $ORACLE_HOME/nls/data/old/cr9idata.pl

script to create the $ORACLE_HOME/nls/data/9idata directory.

perl $ORACLE_HOME/nls/data/old/cr9idata.pl

cd /oracle/home

vi qamar.enb

export ORACLE_HOME=/opt/oracle/db/tech_st/11.1.0
export ORACLE_SID=PROD

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

Root User

chown oracle:oinstall /etc/oraInst.loc

chmod 777 /etc/oraInst.loc

Apply Patches (opatch apply)

Patch 7211965

opatch apply

Patch 7330434

opatch apply

Patch 7486407

opatch apply

Patch 7627743

opatch apply

Patch 7639602

opatch apply

Patch 8199107

opatch apply

Patch 8639653

opatch apply

Patch 8940108

opatch apply

Patch 9066130

opatch apply

Patch 9554727 ——Warning ignore

opatch apply

Patch 9743057 ——-Warning ignore

opatch apply

Patch 17792484

opatch apply

Patch 7684818

opatch napply /home/oracle/patches/7684818 -id 4247037,6263237,6530141,6815733,7243270,7277741,7327166,7684818 -skip_duplicate -invPtrLoc /opt/oracle/db/tech_st/11.1.0/oraInst.loc

APPLY PATCH 6880880

(1) Please take a backup of ORACLE_HOME/OPatch into a dedicated backup

location.

(2) Please make sure no directory ORACLE_HOME/OPatch exist.

(3) Please unzip the OPatch downloaded zip into ORACLE_HOME directory.

To check the version of the opatch utility installed in the above step,

go to the OPatch directory and run “opatch version”.

$ cp p6880880_111000_Linux-x86-64.zip $ORACLE_HOME/

$ cd $ORACLE_HOME

cp -rdf OPatch/ OPatch_Bkp

unzip p6880880_111000_Linux-x86-64.zip

Apply 17465583

optach apply

Patch 7111245 – 7684818 also includes 7111245 but this is a later version of the patch. Disregard errors related to 7111245 being installed when applying 7684818.

optach apply

Patch 7319922

optach apply

3. Create the target initialization parameter file and CBO parameter fileThe 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.

on window %oracle_home%\database\init[SID].ora

modify the initPROD.ora file

4. Create a working direcotry

# mkdir expimp

# chown -R oracle:oinstall expimp/

# chmod -R 777 expimp/

#create dirctories /opt/oracle/db/apps_st/data/

5.Create the target database instanceCopy 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;

6.
Copy database preparation scripts to target Oracle homeThe 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.

7.Set up the SYS schemasqlplus “/ as sysdba” @audb1110.sql

8.Set up the SYSTEM schemasqlplus system/manager @ausy1110.sql

9.Install Java Virtual Machinesqlplus system/manager @aujv1110.sql

10.Install other required componentssqlplus system/manager @aumsc1110.sql FALSE SYSAUX TEMP

11 skip in our case

Install custom RDBMS components (conditional)

If you have other custom RDBMS components loaded in the source database such as Label Security, install them in the target database. To determine the RDBMS components that are loaded in the source and target databases, use SQL*Plus to connect to the databases as SYSDBA and run the following command:

SQL> select * from dba_registry;

12 skip in our case

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

13.
Disable automatic gathering of statisticsCopy $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;grant ANALYZE ANY to system;

conn system/manager

@adstats.sql

if error for fix

exec dbms_stats.gather_schema_stats(‘SYS’,gather_fixed=>TRUE) ;

$ sqlplus “/ as sysdba”

SQL> alter system disable restricted session;

SQL> exit;

14.Back up the target database instanceThe target database instance is now prepared for an import of the Applications data.

You should perform a backup before starting the import.

Down db and make tar file of …/oracle

Section 3: Export the source Release 12 database instance

1. Create the export parameter file

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.

filesize=10485760000

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/manager

SQL> create directory dmpdir as ‘D:\Oracle\expimp’

Do not change the other parameters.

2. 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.

3. 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;

4. Export OLAP analytical workspaces (optional)

The export/import of OLAP analytical workspaces may take up a lot of resources. It may cause memory issues such as bug 10331951. Customers who use OLAP may export/import OLAP through the DBMS_AW package directly as an alternative.

Perform the detailed steps 1-3 as documented in My Oracle Support Note 352306.1

Detailed steps:

To obtain the list of AWs that need to be exported execute this command and then proceed to export the AWs that are NOT owned by 0:

col owner format a15

col aw_name format a15

select OWNER, AW_NAME ,PAGESPACES from dba_aws

where owner != ‘SYS’ order by 1,2;

exec dbms_aw.execute(‘aw attach APPS.ODPCODE rw’);