Oracle database Migration HP-Unix to Linux for EBS

References :

557738.1

881505.1

Tier	Source	Target
Application	11.5.10.2	12.2.4
Database	10.2.0.4	11.2.0.4
Platform	HP-Unix itanium 11 (64bit)	RHEL 6 (64bit)

High level steps :

1) Prepare the source system

2) Prepare the target oracle home

3) Export the source database instance

4) Import the database instance

5)Update the imported database instance 

Section 1: Prepare the source system

 ** Interoperability Notes Oracle EBS 11i with Oracle Database 11gR2 (11.2.0) (Doc ID 881505.1)

Apply the patches below in application tier :

Patch 3460000

Patch 8815204

Patch 5644137

Patch 5903765

Patch 7456837

Patch 9835302 (Latest autoconfig patch)  

 Apply GL patch 6815663 (conditional)

Update application tier context file with new database listener port number (conditional)

Database Installation:

 - Install the 11.2.0.4 software

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

Create nls/data/9idata directory

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

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 11g Oracle home.

Apply additional 11.2.0.4 RDBMS patches

For UNIX/Linux platforms, apply RDBMS patches

Patch 17468141

Patch 17501296

Database Upgrade:

Shut down Applications server processes and database listener

Drop SYS.ENABLED$INDEXES (conditional)

SQL> drop table sys.enabled$indexes;

Prepare to upgrade

Read Chapter 2 of Oracle Database Upgrade Guide 11g Release 2 (11.2). Take note of the section pertaining to the Database Upgrade Assistant (DBUA).

Upgrade the database instance

 - Use the following sections in Note 216205.1, Database Initialization Parameters (init.ora settings) in Oracle Applications

SQL> connect / as sysdba;

SQL> select comp_id from dba_registry where comp_id in ('AMD','EXF');

If the query does not return EXF, then you do not have Oracle Expression Filter installed. It is required by OLAP. To install Expression Filter, use SQL*Plus to connect to the database as SYSDBA and run the following commands:

SQL> connect / as sysdba;

SQL> @$ORACLE_HOME/rdbms/admin/catexf.sql

If the query does not return AMD, then you do not have OLAP installed. To install OLAP, use SQL*Plus to connect to the database as SYSDBA and run the following command:

SQL> connect / as sysdba;

SQL> @$ORACLE_HOME/olap/admin/olap.sql SYSAUX TEMP

Apply the Applications consolidated export/import utility patch

Apply Patch 16541956 to the source administration server node.

$ mkdir /u01/expimp

Generate target database instance creation script aucrdb.sql

$ cd [working directory]

$ sqlplus system/[system password] \

    @$AU_TOP/patch/115/sql/auclondb.sql 11

Record Advanced Queue settings

$ sqlplus /nolog

SQL> connect / as sysdba;

SQL> @auque1.sql

Remove rebuild index parameter in spatial indexes

SQL> select * from dba_indexes where index_type='DOMAIN' and

  upper(parameters) like '%REBUILD%';

To remove the rebuild index parameter, use SQL*Plus to connect to the source database as the owner of the index and run the following command:

SQL> alter index [index name] rebuild parameters [parameters]

where [parameters] is the original parameter set without the rebuild_index parameter.

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;

To synchronize the indexes, run the following command:

SQL> exec ctx_ddl.sync_index('[index owner].[index name]');

Section 2: Prepare a target Applications Release 11i database instance

Create target Oracle 11g Oracle home (conditional)

Create a working directory

Create a working directory named expimp in the target system that will contain all generated files and scripts required to

complete this section. As an example,

$ mkdir /u01/expimp

Create the target database instance

Copy the aucrdb.sql script, generated in Section 1, from the source administration server node to the working directory in the target database server node. Update as required,

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;

Copy database preparation scripts to target Oracle home 

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

Set up the SYS schema

  

$ sqlplus "/ as sysdba" @/u01/expimp/audb1120.sql

Set up the SYSTEM schema

$ sqlplus system/[system password] @/u01/expimp/ausy1120.sql

Install Java Virtual Machine

$ sqlplus system/[system password] @/u01/expimp/aujv1120.sql

$ sqlplus system/[system password] \

    @/u01/expimp/aumsc1120.sql FALSE SYSAUX TEMP

SQL> select * from dba_registry;

Disable automatic gathering of statistics

 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;

- The target database instance is now prepared for an import of the Applications data. 

- You should perform a backup before starting the import.

Export the source Applications Release 11i database instance

 Here is an example of how to create a directory named dmpdir:

$ sqlplus system/[system password]

SQL> create directory dmpdir as '/u01/expimp';

- Shut down Applications server processes

- 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 

SQL> grant EXEMPT ACCESS POLICY to system;

Export the Applications database instance

Start an export session on the source database server node using the customized export parameter file.

Script : 

nohup expdp system/test123 directory=dmpdir dumpfile=exp_full_upgrade%U.dmp filesize=5G full=y logfile=exp_full_upgrade_02_10_16.log parallel=4 metrics=y JOB_NAME=EXP_TEST_FULL &

SQL> revoke EXEMPT ACCESS POLICY from system;

Import the Applications Release 11i database instance

Create the import parameter file

nohup impdp system/test123 directory=dmpdir dumpfile=exp_full_upgrade%U.dmp full=y logfile=imp_full_upgrade_02_10_16.log parallel=4 metrics=y JOB_NAME=IMP_TEST_FULL &

$ sqlplus system/[system password]

SQL> create directory dmpdir as '/u01/expimp';

Copy the export dump files from the source database server node to the target database server node.

$ sqlplus "/ as sysdba"

SQL> exec ctxsys.ctx_adm.set_parameter ('file_access_role', 'public');

Import the Applications database instance

$ impdp "'/ as sysdba'" parfile=[import parameter file name]

  

Update the imported Applications Release 11i database instance

$ sqlplus /nolog

SQL> connect / as sysdba;

SQL> @/u01/expimp/auque2.sql

$ perl $AU_TOP/patch/115/driver/dpost_imp.pl [driver file]

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

Compile invalid objects

$ sqlplus "/ as sysdba" @$ORACLE_HOME/rdbms/admin/utlrp.sql

Maintain Applications database objects

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

perform the following tasks:

Compile flexfield data in AOL tables

Recreate grants and synonyms for APPS schema

Start Applications server processes

Start all the server processes on the target Applications system. You can allow users to access the system at this time.

Create DQM indexes

Create DQM indexes by following these steps:

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

Click Control > Request > Run

Select "Single Request" option

Enter "DQM Staging Program" name

Enter the following parameters:

Number of Parallel Staging Workers: 4

Staging Command: CREATE_INDEXES

Continue Previous Execution: NO

Index Creation: SERIAL

Click "Submit"

After the Database Upgrade:

Perform patch post-install instructions

Run all the patch post install instructions.

Fix Korean lexers (conditional)

If you upgraded from 10.1.0 or previous releases, use SQL*Plus to connect to the database as SYSDBA, and run drkorean.sql

using the following command:

$ sqlplus "/ as sysdba" @$ORACLE_HOME/ctx/sample/script/drkorean.sql

Start the new database listener (conditional)

Run adgrants.sql

Copy $APPL_TOP/admin/adgrants.sql (adgrants_nt.sql for Windows) from the administration server node to the database server node. 

Connect to the database as SYSDBA and run the script using the following command:

$ sqlplus "/ as sysdba" @adgrants.sql APPS

  

Grant create procedure privilege on CTXSYS

Copy $AD_TOP/patch/115/sql/adctxprv.sql from the administration server node to the database server node. Connect to the database as APPS and run the script using the following command:

$ sqlplus apps/[APPS password] @adctxprv.sql \

    [SYSTEM password] CTXSYS

Set CTXSYS parameter

$ sqlplus "/ as sysdba"

SQL> exec ctxsys.ctx_adm.set_parameter('file_access_role', 'public');

Deregister the current database server (conditional)

$ sqlplus apps/[APPS password]

SQL> exec fnd_conc_clone.setup_clean;

Implement and run AutoConfig

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

Gather statistics for SYS schema

Copy $APPL_TOP/admin/adstats.sql from the administration server node to the database server node. 

$ sqlplus "/ as sysdba"

SQL> alter system enable restricted session;

SQL> @adstats.sql

$ sqlplus "/ as sysdba"

SQL> alter system disable restricted session;

SQL> exit;

as APPS and run the following query:

$ sqlplus apps/[apps password]

SQL> select db_link from dba_db_links;

The EDW_APPS_TO_WH and APPS_TO_APPS database links, if they exist, should have been updated with the new port number by AutoConfig in the previous step.

If you have custom self-referential database links in the database instance, use the following commands to drop and re-

create them:

$ sqlplus apps/[apps password]

SQL> drop database link [custom database link];

SQL> create database link [custom database link] connect to

     [user] identified by [password] using

     '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=[hostname])

     (PORT=[port number]))(CONNECT_DATA=(SID=[ORACLE_SID])))';

where [custom database link], [user], [password], [hostname], [port number], and [ORACLE_SID] reflect the new Oracle Net

listener for the database instance.

Apply Oracle Human Resources (HRMS) patch 7721754 (conditional)

If you are using Oracle HRMS, apply Patch 7721754.

Re-create grants and synonyms

run AD Administration and select the "Recreate grants and synonyms for APPS

schema" task from the Maintain Applications Database Objects menu.

Enable Database Vault (conditional)

Restart Applications server processes (conditional)

Scripts Used :

nohup expdp system/test123 directory=EXP_DP_UPG dumpfile=exp_full_upgrade%U.dmp filesize=5G full=y logfile=exp_full_upgrade_02_10_16.log parallel=4 metrics=y JOB_NAME=EXP_TEST_FULL &

 Backup script before export :

rman target /

run

{

Allocate channel ch01 device type DISK ;

Allocate channel ch02 device type DISK ;

Allocate channel ch03 device type DISK ;

Allocate channel ch04 device type DISK ;

sql 'alter system archive log current';

backup as compressed backupset incremental level 0 database  filesperset=10 tag='TESTR12_full' format '/backup/RMAN/TESTR12_09022016_%d_%U.rmn';

sql 'alter system archive log current';

backup current controlfile  format '/backup/RMAN/TESTR12_09022016_%d_%U.ctl';

 release channel ch01;

 release channel ch02;

 release channel ch03;

 release channel ch04;

}