Export/Import Process for Oracle E-Business Suite Release 12

This blog describes the process of re-creating an existing Applications Release 12 database instance using the export and import utilities.

The export and import utilities allow you to move existing data in Oracle format to and from Oracle databases. For example, 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 export and import.

There are special considerations when exporting or importing an Applications Release 12 database instance.

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

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

---

Prepare the source system (Present Development / Production)

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.

1. Apply latest AutoConfig patches
2. Apply the Applications consolidated export/import utility patch

   Apply patch 6924477 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.

3. Apply latest Applications database preparation scripts patch

   Apply patch 6342289 to every application tier server node in the source system.

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

5. 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/ \
      @$AU_TOP/patch/115/sql/auclondb.sql 10
   

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

7. Create parameter file for tables with long columns

   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/;
   SQL> @aulong.sql
   

8. 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  rebuild parameters 
   

   where is the original parameter set without the rebuild_index parameter.

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

---

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.

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

   1. Ensure that environment settings, such as ORACLE_HOME, are set for the new Oracle home you are about to create.

   2. Perform all the steps in Chapter 3 of the Oracle Database Installation Guide 10g Release 2 (10.2), for your platform.

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

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

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

   6. 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/ and $ORACLE_HOME/perl/lib/site_perl/)

   7. 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 (for Windows users): Keep track of the database home name used. For Rapidwiz installed Oracle homes, the home name is _db102_RDBMS. For manually installed Oracle homes, the home name is what you input when creating the Oracle home.

2. Upgrade to the latest 10.2.0 patch set (conditional)

   1. Perform 10.2.0.x Patch Set installation tasks

   2. Apply additional 10.2.0.x RDBMS patches

   Do not perform any post-installation patch README steps.

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

4. Create the target initialization parameter file and CBO parameter file

   The initialization parameter file (init.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.

   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.

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

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

   When the target database instance has been created, restart the database instance.

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

   1. The remarks section at the beginning of each script contains additional information.

   2. Each script creates a log file in the current directory.

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

9. 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/ @adsy1020.sql
   

10. 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/ @adjv1020.sql
    

    Attention: This script can be run only once in a given database instance, because the scripts that it calls are not rerunnable.

11. 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/ \
        @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.

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

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

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

   For Windows platforms, add the following line to the parameter file:

   exclude=table_statistics
   

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

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 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 system/ parfile=
   

   Typically, the export runs for several hours.

5. Export tables with long columns

   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
   

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

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

---

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.

1. 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/ 
   SQL> create directory dmpdir as '/u01/expimp';
   

   Save the changed file.

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

3. Import the users into the target database

   Start an import session on the target database server node using the customized import parameter file. Use the following command:

   $ impdp system/ parfile=auimpusr.dat
   

4. Import tables with long columns into the target database

   Modify the aufullimp.dat file with the following:

   1. Set userid to "sys/ as sysdba".

   2. Set file to the dump file containing the long tables (longexp by default).

   3. Set the log file appropriately.

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

5. Import the Applications database instance

   Start an import session on the target database server node using the auimpdp.dat parameter file. Use the following command:

   $ impdp system/ parfile=auimpdp.dat
   

   Typically, import runs for several hours.

6. Import triggers into the target database

   Modify the aufullimp.dat file with the following:

   1. Set userid to "sys/ as sysdba".

   2. Set file to the dump file containing the long tables (longexp by default).

   3. Change the log file name.

   4. Uncomment the ignore parameter.

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

---

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

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

   Attention: Set the TNS_ADMIN environment variable to the directory where you created your listener.ora and tnsnames.ora files.

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 (or adgrants_nt.sql) \
      
   

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/ @adctxprv.sql \
       CTXSYS
   

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

   Shut down all processes, including the database and the listener, and restart them to load the new environment settings.

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

8. 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/
   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/
   SQL> drop database link ;
   SQL> create database link  connect to
        identified by  using
   
       '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=)
       (PORT=))(CONNECT_DATA=(SID=<ORACLE_SID>)))';
   

   where , , , , , and reflect the new Oracle Net listener for the database instance.

9. 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  10
   

   Once the driver file has been generated, use AutoPatch to apply it on the target administration server node.

10. Import tables with XML type columns into the target database

    Modify the aufullimp.dat file with the following:

    1. Set userid to "az/"

    2. Set file to the dump file containing the tables with XML types (xmlexp by default).

    3. Change the log file name.

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

11. 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/
    SQL> exec icx_cat_sqe_pvt.sync_sqes_for_all_zones;
    

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

13. Maintain Applications database objects

    Run AD Administration on the target administration server node. From the Maintain Applications Database Objects menu, perform the following tasks:

    1. Compile flexfield data in AOL tables

    2. Recreate grants and synonyms for APPS schema

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

15. Create DQM indexes

    Create DQM indexes by following these steps:

    1. Log on to Oracle Applications with the "Trading Community Manager" responsibility

    2. Click Control > Request > Run

    3. Select "Single Request" option

    4. Enter "DQM Staging Program" name

    5. Enter the following parameters:

       • Number of Parallel Staging Workers: 4

       • Staging Command: CREATE_INDEXES

       • Continue Previous Execution: NO

       • Index Creation: SERIAL

    6. Click "Submit"

16. Gather Applications statistics (conditional)

    If you encountered failures importing statistics or if your platform is Windows, gather the statistics by following these steps:

    1. Ensure that there are no concurrent programs running.

    2. Log on to Oracle Applications with the "System Administrator" responsibility.

    3. Click Navigation List > Request > Run.

    4. Enter appropriate parameters. Specify "ALL" in the schema name to gather statistics for all database objects. You may choose individual schemas to make the process run faster.

    5. Click "Submit".