Source Assumptions:
Character sets used in Oracle databases US7ASCII
Target Assumptions:
Character sets used in Oracle databases AL32UTF8
This document assumes that the source SID (PDB SID for Oracle Database 19c) and the target PDB SID are the same.
12.1 Prerequisites
1. The target (import to) ORACLE_HOME must be an Oracle 19c Enterprise Edition multitenant environment. The imported database will be stored as a pluggable database (PDB).
2. The source (export from) ORACLE_HOME must be Oracle 11g Release 2 (11.2), 12c Release 1 (12.1.0), or 19c Enterprise Edition.
3. The target database will have a single PDB inside a container database (CDB). Do not attach a second PDB to the CDB.
4. If the source database is 11g, Oracle Label Security (OLS) policies cannot be migrated through export/import. Do not proceed with this process if you have OLS policies that need to be migrated from source to target environment.
12.2 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.
1. Apply prerequisite patches
Ensure that you have performed step 2 of Section 2.1 of the My Oracle Support Knowledge Document 2552181.1, Interoperability Notes: Oracle E-Business Suite Release 12.2 with Oracle Database 19c.
2. Apply the Oracle E-Business Suite consolidated export/import utility patch
Apply Patch 31405457 to the administration server node. This patch provides several SQL scripts that facilitate exporting and importing an Oracle E-Business Suite database instance.
3. Complete patching cycle and remove adop created editions
As the owner of the administration server, run any of the following commands not previously run. This will clean up the editions created by previous adop patching cycles. These commands also complete any open patching cycle.
On the current run file system:
$ adop phase=prepare
$ adop phase=actualize_all
$ adop phase=finalize finalize_mode=full
$ adop phase=cutover
On the new run file system:
$ adop phase=cleanup cleanup_mode=full
Note: These maintenance operations will take much longer than a typical online patching cycle, and should only be performed when there is no immediate need to start a new online patching cycle.
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 PDB instance must be created with the same tablespace structure as the source PDB instance. The export/import patch provides the auclondb.sql script which generates the aucrdb.sql script, which you use to create the target PDB 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.
Copy the $AU_TOP/patch/115/sql/auclondb.sql script from the patch APPL_TOP on the administration server node to the working directory in the source database server node.
For multitenant environments, run the following command:
$ export ORACLE_PDB_SID=<PDB SID>
Then, use SQL*Plus to connect to the database as SYSTEM and run the auclondb.sql script. It creates aucrdb.sql in the current directory.
$ sqlplus system/<system password> @auclondb.sql 19
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. Patch 31405457 contains auque1.sql, which generates the script 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 in the patch APPL_TOP on the administration server node to the working directory in the source database server node.
For multitenant environments, run the following command:
$ export ORACLE_PDB_SID=<PDB SID>
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.
$ sqlplus "/ as sysdba"
SQL> @auque1.sql
7. Remove rebuild index parameter in spatial indexes
Ensure that you do not have the rebuild index parameter in the spatial indexes.
For multitenant environments, run the following command:
$ export ORACLE_PDB_SID=<PDB SID>
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:
$ sqlplus "/ as sysdba"
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 the optional parameters entry is usually the original parameter setting without the rebuild_index parameter.
8. Synchronize Text indexes
Unsynchronized Oracle Text indexes slow down the export process. Ensure that the indexes are synchronized before running the export.
For multitenant environments, run the following command:
$ export ORACLE_PDB_SID=<PDB SID>
Then, use SQL*Plus to connect to the source database as SYSDBA and run the following command to find all indexes pending synchronization:
$ sqlplus "/ as sysdba"
SQL> select pnd_index_owner,pnd_index_name,count(*) from ctxsys.ctx_pending group by pnd_index_owner,pnd_index_name;
To synchronize the indexes, run the following command:
SQL> exec ctx_ddl.sync_index('<index owner>.<index name>');
9. Determine TCA DQM status
If you are using the Oracle Trading Community Architecture (TCA) Data Quality Management (DQM) functionality, the DQM indexes will need to be recreated in the target environment.
To determine if you are using the TCA DQM functionality, use SQL*Plus to connect to the database as apps, and run the following query:
$ sqlplus apps/<apps password>
SQL> select hz_dqm_search_util.is_dqm_available from dual;
If the query returns 'T', then the TCA DQM functionality is enabled.
12.3 Prepare the Target System
This section describes how to create the empty target database and populate it with all of the required system objects prior to running import.
1. Create target Oracle 19c Oracle home
Note: user AL32UTF8 character sets on the new database.
Perform Section 2.2 of the My Oracle Support Knowledge Document 2552181.1, Interoperability Notes: Oracle E-Business Suite Release 12.2 with Oracle Database 19c. Every time the target environment is used, ensure that the ORACLE_BASE, ORACLE_HOME, PATH, LD_LIBRARY_PATH, and PERL5LIB are set according to Document 2552181.1 and that ORACLE_SID is set to the CDB SID.
Do not shut down the CDB after completing all the steps.
Note: Ensure the appropriate patches listed in My Oracle Support Knowledge Document 1594274.1, Oracle E-Business Suite Release 12.2: Consolidated List of Patches and Technology Bug Fixes, are applied to both the source and target environments. Re-run adgrants.sql in the source environment as the patches may revoke grants necessary to E-Business Suite.
In addition, apply Patch 28318139 to the target environment.
2. 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)
3. Modify the target initialization parameter file
If the source database is multitenant, refer to My Oracle Support Knowledge Document 396009.1, Database Initialization Parameters for Oracle E-Business Suite Release 12, and modify the newly created init<CDB SID>.ora with any necessary changes. You may also need to update initialization parameters involving the 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 of the database created by DBCA. Ignore the initialization parameters that pertain to the native compilation of PL/SQL code. You will be instructed to add them later, if necessary.
Use SQL*Plus to connect to the CDB as SYSDBA and run the following commands to update the CDB initialization parameter LOCAL_LISTENER.
SQL> alter system set LOCAL_LISTENER="<hostname>:<port number>" scope=both;
SQL> shutdown;
SQL> startup;
If the source database is not multitenant, perform the following commands on the source database tier to create the $ORACLE_HOME/dbs/<source SID>_initparam.sql and $ORACLE_HOME/dbs/<source SID>_datatop.txt files.
$ cd $ORACLE_HOME/appsutil
$ . ./txkSetCfgCDB.env dboraclehome=<full path of ORACLE_HOME>
$ export ORACLE_SID=<source SID>
$ cd $ORACLE_HOME/appsutil/bin
$ perl txkOnPremPrePDBCreationTasks.pl -dboraclehome=<ORACLE_HOME> \
-outdir=<ORACLE_HOME>/appsutil/log -appsuser=<apps user> \
-dbsid=<ORACLE_SID> -skipdbshutdown=yes
When the txkOnPremPrePDBCreationTasks.pl script is run on an 11.2.0.4 database, ignore any DBMS_PDB.DESCRIBE related errors.
Copy both <source SID>_initparam.sql and <source SID>_datatop.txt files to the target $ORACLE_HOME/dbs directory and then use SQL*Plus to connect to the CDB as SYSDBA and run the following commands to update the CDB initialization parameters
SQL> shutdown;
SQL> startup nomount;
SQL> @$ORACLE_HOME/dbs/<source SID>_initparam.sql
SQL> alter system set LOCAL_LISTENER="<hostname>:<port number>" scope=both;
SQL> shutdown;
SQL> startup;
4. 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
5. Create the target database instance
Use SQL*Plus to connect to the CDB as SYSDBA and run the following commands to create the PDB.
$ sqlplus "/ as sysdba"
SQL> create pluggable database "<PDB SID>" admin user <PDB admin user> identified by <password> file_name_convert=('<CDB file location>/<CDB SID>/pdbseed','<PDB file location>');
SQL> alter pluggable database "<PDB SID>" open;
SQL> alter pluggable database "<PDB SID>" save state;
Copy the aucrdb.sql script, generated in Section 3, to the working directory in the target database server node. Then update the scripts on the target database server node with any appropriate changes to the directory structures for the log file(s), data file(s), or tablespaces, reflecting the layout of the target PDB database. You may resize the file sizes and rename the file names. 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. You may add commands that modify database or tablespace options such as force logging.
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, ORACLE_PDB_SID, and ORA_NLS10 environment settings. Then, use the following commands to run aucrdb.sql and create the target PDB tablespaces.
Note: Edit the aucrdb.sql generated in section 3, the Character set and National Character must much the ones used while creating the CBD SID above.
CHARACTER SET AL32UTF8 from US7ASCII
NATIONAL CHARACTER SET UTF8
$ export ORACLE_PDB_SID=<PDB SID>
$ sqlplus "/ as sysdba" @aucrdb.sql
If PL/SQL of the source database was natively compiled, see the "Compiling PL/SQL Code for Native Execution" section of Chapter 12 of Oracle Database PL/SQL Language Reference 19c 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: 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.
6. Copy database preparation scripts to target Oracle home
The export/import patch that you applied to the administration server node in Section 3 contain two scripts that are needed on the target database server node. Copy the following files from the $AU_TOP/patch/115/sql directory in the patch APPL_TOP on the administration server node to the working directory in the target database server node: audb19c.sql and ausy19c.sql (UNIX or Linux) or audb19c_nt.sql and ausy19c_nt.sql (Windows).
As you run each of the next two steps, note the following:
o The remarks section at the beginning of each script contains additional information.
o Each script creates a log file in the current directory.
o Set the ORACLE_PDB_SID environment variable to the PDB SID when running the two scripts.
7. Set up the SYS schema
The audb19c.sql or audb19c_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 PDB as SYSDBA and run audb19c.sql (UNIX/Linux) or audb19c_nt.sql (Windows).
Here is an example on UNIX or Linux:
$ export ORACLE_PDB_SID=<PDB SID>
$ sqlplus "/ as sysdba" @audb19c.sql
8. Set up the SYSTEM schema
The ausy19c.sql or ausy19c_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 PDB as SYSTEM and run ausy19c.sql (UNIX/Linux) or ausy19c_nt.sql (Windows).
Here is an example on UNIX or Linux:
$ export ORACLE_PDB_SID=<PDB SID>
$ sqlplus system/<system password> @ausy19c.sql
9. Install custom Oracle Database components
If you have other custom Oracle Database components loaded in the source database such as Label Security, install them in both the target CDB and PDB. To determine the Oracle Database 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;
10. Run additional Oracle Database scripts
Use SQL*Plus to connect to the PDB as SYSDBA and run the following scripts:
$ export ORACLE_PDB_SID=<PDB SID>
$ sqlplus "/ as sysdba"
SQL> @?/rdbms/admin/catmgd.sql
SQL> @?/rdbms/admin/dbmsxdbschmig.sql
SQL> @?/rdbms/admin/prvtxdbschmig.plb
11. Grant datastore access
Use SQL*Plus to connect to the PDB as SYSDBA and run the following command:
$ export ORACLE_PDB_SID=<PDB SID>
$ sqlplus "/ as sysdba"
SQL> grant text datastore access to public;
12. Disable automatic gathering of statistics
Copy $APPL_TOP/admin/adstats.sql from the patch APPL_TOP on the administration server node to the target database server node. Use SQL*Plus to connect to the PDB as SYSDBA and use the following commands to restart the database in restricted mode and run adstats.sql:
$ export ORACLE_PDB_SID=<PDB SID>
$ sqlplus "/ as sysdba"
SQL> alter system enable restricted session;
SQL> @adstats.sql
$ sqlplus "/ as sysdba"
SQL> alter system disable restricted session;
SQL> exit;
13. Back up the target database instance
The target database instance is now prepared for an import of the Oracle E-Business Suite data. You should perform a backup before starting the import.
1.4 Export Source Database Objects
This section describes the process of exporting data from the source Oracle E-Business Suite database.
1. Create the export parameter file
A template for the export parameter file has been included as part of the export/import patch. Copy $AU_TOP/patch/115/import/auexpdp.dat from the patch APPL_TOP on the 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
parallel number of parallel workers 4
log log file name expdpapps.log
Add the following parameter to the file:
EXCLUDE=STATISTICS
Oracle Database Vault schemas do not migrate through expdp and impdp. If you are using Oracle Database Vault, the import logs will contain many errors related to the schemas. These can be ignored as Oracle Database Vault schemas will be re-created in the target environment, if you choose to continue using Oracle Database Vault. Also, you may exclude the schemas to reduce the number of errors in the import log file. The following line is an example of what to add to the auexpdp.dat file to exclude the schemas.
EXCLUDE=SCHEMA:"IN('C##DBV_OWNER','C##DBV_ACCTMGR')"
For multitenant environments, run the following command:
$ export ORACLE_PDB_SID=<PDB SID>
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.
2. Store the UTL_FILE_DIR parameter values (Conditional)
If the source database is 11g or 12c, perform Section 3.1.1, Migrating UTL_FILE_DIR Settings when Upgrading to Oracle Database 19c, in My Oracle Support Knowledge Document 2525754.1, Using UTL_FILE_DIR or Database Directories for PL/SQL File I/O in Oracle E-Business Suite Releases 12.1 and 12.2, to set the UTL_FILE_DIR paramter values.
Note: Create the directories listed in the <ORACLE_SID>_utlfiledir.txt file in the target database server node. As the directories may not exist in the source database server node, run setUtlFileDir with the -skipdirvalidation=Yes parameter.
3. Gather statistics into a table
From the administration server node, use SQL*Plus to connect to the database and run the following commands to gather statistics of all schemas.
SQL> connect apps/<apps password>
SQL> exec fnd_stats.gather_schema_statistics(schemaname=>'ALL',estimate_percent=>dbms_stats.auto_sample_size,options=>'GATHER AUTO');
Then, as the apps user, run the script 7.10.2.1 of My Oracle Support Knowledge Document 1581549.1, Best Practices for Minimizing Oracle E-Business Suite Release 12.2.n Upgrade Downtime. This stores the gathered statistics into a table.
4. Shut down the application tier server processes
On each application tier server node, shut down all server processes or services except the database and the Net Services listener for the database. The applications will be unavailable to users until the import is completed.
5. Grant privilege to source SYSTEM schema
For multitenant environments, run the following command:
$ export ORACLE_PDB_SID=<PDB SID>
Then, to grant the exempt access policy privilege to SYSTEM, use SQL*Plus to connect to the PDB as SYSDBA and run the following command:
$ sqlplus "/ as sysdba"
SQL> grant EXEMPT ACCESS POLICY to system;
6. Authorize SYSTEM to export database (Conditional)
For multitenant environments, run the following command:
$ export ORACLE_PDB_SID=<PDB SID>
If you are using Database Vault, use SQL*Plus to connect to the source PDB as a user with DV_OWNER role and perform the following commands:
$ sqlplus "/ as sysdba"
SQL> exec dvsys.dbms_macadm.authorize_datapump_user('SYSTEM');
7. Remove the MGDSYS schema (Conditional)
If the source database is 11g, use SQL*Plus to connect to the database as SYSDBA and run the $ORACLE_HOME/md/admin/catnomgdidcode.sql script to remove the MGDSYS schema.
$ sqlplus "/ as sysdba" @?/md/admin/catnomgdidcode.sql
Note: If the source 11g database will still be used, then the MGDSYS schema is still required. Run the $ORACLE_HOME/md/admin/catmgdidcode.sql script on the source database after the export has completed to re-create the MGDSYS schema.
8. 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 ORA-04030: Out of Process Memory error. 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 Knowledge Document 352306.1, Steps for Migrating OLAP Across Databases/Platforms, to export OLAP analytical workspaces on the source machine. Copy the export files to the target machine.
9. Export the Applications database instance
For multitenant environments, run the following command:
$ export ORACLE_PDB_SID=<PDB SID>
Start an export session on the source PDB using the customized export parameter file. Use the following command.
$ expdp system/<system password> parfile=<export parameter file name>
10. Revoke privilege from source SYSTEM schema (Optional)
For multitenant environments, run the following command:
$ export ORACLE_PDB_SID=<PDB SID>
If the source database is still to be used, revoke the exempt access policy privilege from SYSTEM by using SQL*Plus to connect to the PDB as SYSDBA and run the following command.
$ sqlplus "/ as sysdba"
SQL> revoke EXEMPT ACCESS POLICY from system;
11. Import OLAP analytical workspaces (Optional)
If the source database is still to be used and you exported OLAP analytical workspaces in a previous step, perform the detailed step 7 as documented in My Oracle Support Knowledge Document 352306.1, Steps for Migrating OLAP Across Databases/Platforms, to import the OLAP analytical workspaces that were previously exported.
1.5 Import Target Database Objects
This section describes how to use the import utility to load the Oracle E-Business Suite data into the target database.
1. Create the import parameter files
Copy auimpdp.dat from the $AU_TOP/patch/115/import directory in the run APPLTOP on the administration server node to the working directory in the target database server node. Make sure that the directory, dumpfile, parallel, and logfile parameters in auimpdp.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:
$ export ORACLE_PDB_SID=<PDB SID>
$ sqlplus system/<system password>
SQL> create directory dmpdir as '/u01/expimp';
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. Create Oracle wallet (Conditional)
If you have Transparent Data Encryption (TDE) enabled in the source database or if you choose to use TDE, perform the following steps to create a software keystore for the target CDB and to set the TDE master encryption key:
a. Set the keystore location and type
Create the <WALLET_ROOT>/tde directory which is where the keystore will be stored. Next, modify the following CDB initialization parameters and then restart the database to set the parameters:
WALLET_ROOT='<WALLET_ROOT directory given above (without "tde")>'
TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE"
b. Create a software keystore
Use SQL*Plus to connect to the database as a user who has the ADMINISTER KEY MANAGEMENT or SYSKM privilege. Perform the following steps to create the software keystore under <WALLET_ROOT>/tde.
$ unset ORACLE_PDB_SID
$ sqlplus sec_admin as syskm
SQL> administer key management create keystore identified by "<keystore password>";
SQL> administer key management create local auto_login keystore from keystore
'<WALLET_ROOT>/tde' identified by "<keystore password>";
c. Open the keystore and set the TDE Master Encryption Key
Perform the following steps to open the keystore and set the TDE Master Encryption Key in the software keystore.
SQL> administer key management set keystore open force keystore identified by "<keystore password>";
SQL> administer key management set key force keystore identified by "<keystore password>" with backup;
4. Import the Applications database instance
Ensure that both PDB and CDB are up. Start an import session on the target database server node using the auimpdp.dat parameter file. Use the following command (encryption_password entry is conditional on an Oracle wallet created in the previous step):
$ export ORACLE_PDB_SID=<PDB SID>
$ impdp system/<system password> parfile=auimpdp.dat encryption_password=<keystore password>
5. Import OLAP analytical workspaces (Conditional)
If you exported OLAP analytical workspaces, perform the detailed step 7 as documented in My Oracle Support Knowledge Document 352306.1, Steps for Migrating OLAP Across Databases/Platforms, to import the OLAP analytical workspaces that were previously exported from the source machine.
6. 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:
$ export ORACLE_PDB_SID=<PDB SID>
$ sqlplus "/ as sysdba"
SQL> revoke EXEMPT ACCESS POLICY from system;
1.6 Update the Target Database Instance
This section describes how to recreate the database objects and relationships that are not handled by the export and import utilities.
After running the txkPostPDBCreationTasks.pl, additional environment files are created so that connecting to the database is more convenient. Load the proper environment variables and connect to the database by performing the following steps:
• For the CDB database, source the $ORACLE_HOME/<CDB SID>_<HOST>.env/cmd file. Then, run sqlplus <user>/<password> or connect as SYSDBA.
• For the PDB database on UNIX/Linux platforms, to connect as SYSDBA, source the $ORACLE_HOME/<CDB SID>_<HOST>.env file. Set the ORACLE_PDB_SID environment variable to <PDB SID>. Then, connect as SYSDBA.
• For the PDB database on Windows platforms, to connect as SYSDBA, source the $ORACLE_HOME/<PDB SID>_<HOST>.env file. Then, connect as SYSDBA.
• For the PDB database, to connect to other users, source the $ORACLE_HOME/<PDB SID>_<HOST>.env file. Then, run sqlplus <user>/<password>@<PDB SID>.
1. Reset Advanced Queues
Copy the auque2.sql script that was generated in Section 3 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 19c 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.
$ export ORACLE_PDB_SID=<PDB SID>
$ sqlplus "/ as sysdba" @auque2.sql
2. Start the database listeners (Conditional)
If you are running Oracle E-Business Suite TXK Delta 13 or later, on the target database server node, use the following commands to create and run the database listeners.
$ cd $ORACLE_HOME/appsutil
$ unset ORACLE_PDB_SID
$ . ./txkSetCfgCDB.env dboraclehome=<full path of ORACLE_HOME>
$ cd bin
$ perl txkGenPDBTnsAdmin.pl -dboraclehome=<ORACLE_HOME> \
-cdbname=<CDB name for RAC> -cdbsid=<CDB SID> -pdbsid=<PDB SID> \
-outdir=<ORACLE_HOME>/appsutil/log -israc=<Yes|No if RAC or not>\
-dbport=<port number>
$ export ORACLE_PDB_SID=<PDB SID>
$ export TNS_ADMIN=$ORACLE_HOME/network/admin/<PDB SID>_<hostname>
$ lsnrctl start <CDB SID>
3. Grant database privileges
Perform the steps in Document 2758993.1, Managing Database Privileges in Oracle E-Business Suite Release 12.2 (Running adgrants.sql), to grant database privileges to the Oracle E-Business Suite schemas. Do not run any non-existent environment files.
4. Run post PDB script
Use the following commands to run the txkPostPDBCreationTasks.pl script. This updates the PDB configuration.
$ cd $ORACLE_HOME/appsutil
$ unset ORACLE_PDB_SID
$ unset TWO_TASK
$ . ./txkSetCfgCDB.env dboraclehome=<full path of ORACLE_HOME>
$ cd bin
$ perl txkPostPDBCreationTasks.pl -dboraclehome=<ORACLE_HOME> \
-outdir=<ORACLE_HOME>/appsutil/log -cdbsid=<CDB SID> -pdbsid=<PDB SID> \
-appsuser=apps -dbport=<port number> -servicetype=onpremise
5. Create target statistics
Use SQL*Plus to connect to the target PDB as SYSTEM and use the following commands to gather system and fixed object statistics, and upgrade the FND_STATTAB statistics table:
$ export ORACLE_PDB_SID=<PDB SID>
$ sqlplus system/<system password>;
SQL> exec dbms_stats.gather_dictionary_stats;
SQL> exec dbms_stats.gather_fixed_objects_stats;
SQL> exec dbms_stats.upgrade_stat_table('APPLSYS','FND_STATTAB');
Then, as the apps user, run the script 7.10.2.2 of My Oracle Support Knowledge Document 1581549.1, Best Practices for Minimizing Oracle E-Business Suite Release 12.2.n Upgrade Downtime. This creates the target statistics from the statistics table.
6. Reload UTL_FILE_DIR parameter values (Conditional)
If the source environment is multitenant, as the owner of the target database server, perform the "To modify or delete a directory path in the supplemental UTL_FILE_DIR parameter" subsection of Section 3.1.2 in My Oracle Support Knowledge Document 2525754.1, Using UTL_FILE_DIR or Database Directories for PL/SQL File I/O in Oracle E-Business Suite Releases 12.1 and 12.2, to change the directory paths from the source database server to the target database server.
7. Implement and run AutoConfig
On each application tier server node of both patch and run APPL_TOP, modify the TNS entries and the context files to point to the new database. Then, run AutoConfig on each application tier server node.
To modify the TNS entries, use a text editor and edit the $TNS_ADMIN/tnsnames.ora files to specify the CDB instance name. The following is an example of the new TNS entry.
<TWO_TASK> =
(DESCRIPTION =
(ADDRESS = (PROTOCOL=tcp)(HOST=<host>.<domain>)(PORT=<port number>))
(CONNECT_DATA = (SERVICE_NAME=ebs_<PDB SID>)(INSTANCE_NAME=<CDB SID>))
)
Update the following values in the context file of every application tier server node.
Variable Name Value
s_dbhost New database hostname
s_dbdomain New database domain name
s_dbGlnam CDB SID
s_dbport New database listener port
s_applptmp Directory (not /usr/tmp) defined in UTL_FILE_DIR
To identify the allowable directories for s_applptmp use, connect to the Oracle E-Business Suite database instance as the apps user and run the following query:
SQL> select value from v$parameter where name='utl_file_dir';
Run AutoConfig on both patch and run APPL_TOPs using the following command.
$ $INST_TOP/admin/scripts/adautocfg.sh
Note: AutoConfig on the run APPL_TOP will fail because jtfictx.sql requires AD_CTX_DDL, which is created in a later step. AutoConfig on the run APPL_TOP will be run again after AD_CTX_DDL has been created.
When running AutoConfig on the patch APPL_TOP, ignore all errors.
Run the correct Oracle E-Business Suite file system environment file again, in a new window to ensure the correct environment variables are loaded. (Existing windows logged into the application tier may have the old environment variables loaded.) Shut down and restart all Oracle E-Business Suite application tier processes to load the new environment settings.
8. Re-create custom database links (Conditional)
If the Oracle Net listener in the 19c Oracle home is defined differently than the one used by the old Oracle home, you must recreate any custom self-referential database links that exist in the Oracle E-Business Suite database instance. To check for the existence of database links, use SQL*Plus on the database server node to connect to the database instance as apps and run the following query:
$ sqlplus apps/<apps password>@<PDB SID>
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>@<PDB SID>
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=(SERVICE_NAME=ebs_<PDB SID>) (SID=<CDB SID>)))';
where <custom database link>, <user>, <password>, <hostname>, <port number>, <CDB SID>, and <PDB SID> reflect the new Oracle Net listener for the database instance.
9. Run ETCC on target database
The adop utility requires that the EBS Technology Codelevel Checker (ETCC) utility is run on the database. Perform Section 2 of My Oracle Support Knowledge Document 1594274.1, Oracle E-Business Suite Release 12.2: Consolidated List of Patches and Technology Bug Fixes, to run the ETCC utility.
10. Create Oracle Text objects
Certain Oracle Text objects are not preserved by the import process. The consolidated export/import utility patch that you applied to the administration server node in Section 3 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 commands.
$ cd $AU_TOP/patch/115/bin
$ mkdir 6924477
$ perl dpost_imp.pl u6924477.drv <source database version>
$ mv u6924477.drv 6924477
$ adop phase=apply hotpatch=yes patchtop=$AU_TOP/patch/115/bin patches=6924477 options=forceapply
Set <source database version> to 11 if the source database is 11g, 12 if the source database is 12c, or 19 if the source database is 19c.
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 Oracle E-Business Suite database instance as apps and run the following command:
$ sqlplus apps/<apps password>@<PDB SID>
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 19c 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.
$ export ORACLE_PDB_SID=<PDB SID>
$ sqlplus "/ as sysdba" @$ORACLE_HOME/rdbms/admin/utlrp.sql
13. Run AutoConfig on the run APPL_TOP
On the run APPL_TOP, run AutoConfig by using the following command:
$ $INST_TOP/admin/scripts/adautocfg.sh
14. Install Oracle Database Vault (Optional)
If you choose to use Oracle Database Vault, perform the steps in My Oracle Support Knowledge Document 2727580.1, Integrating Oracle E-Business Suite Release 12.2 with Oracle Database Vault using EBS_SYSTEM. You may skip steps that have already been done in the source environment that involve the applications tier. However, you must perform the steps such as registering Oracle Database Vault and integrating Oracle Database Vault with the Oracle E-Business Suite that involve the target database.
15. Apply post-upgrade WMS patches (Conditional)
If the source database is 11g, apply Patch 19007053. Since the WLS admin server has not yet been brought up, apply the patch in downtime mode on the run APPL_TOP by running the following command:
$ adop phase=apply patches=19007053 apply_mode=downtime
16. Reconfigure external node integrations (Conditional)
If you have implemented any integrations that connect an external node to Oracle E-Business Suite, then you should reconfigure those integrations with the updated DBC file. For example, if you have implemented Oracle Enterprise Command Center Framework in your Oracle E-Business Suite system, repeat the configuration steps in the "Configuring the JNDI to Connect to Oracle E-Business Suite" subsection of Section 4.3.2.1 in My Oracle Support Knowledge Document 2495053.1, Installing Oracle Enterprise Command Center Framework, Release 12.2.
17. Start application tier server processes
Start all the application tier server processes. You can allow users to access the system at this time.
18. Create TCA DQM indexes (Conditional)
If the source environment TCA DQM functionality was enabled, create the DQM indexes by following these steps:
a. Log on to the Oracle E-Business Suite 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".
Reference:
Interoperability Notes: Oracle E-Business Suite Release 12.2 with Oracle Database 19c
(Doc ID 2552181.1)
Export/Import Process for Oracle E-Business Suite Release 12.2 Database Instances Using Oracle Database 19c (Doc ID 2554156.1)
No comments:
Post a Comment