How to upgrade R12.1 Instance database to 12c

It consists of these many steps

1. Prerequisite steps for 12C database Upgrade
2. Database Software installation
3. Preupgrade steps
4. Database Upgrade
5. Post Upgrade steps

Prerequisite steps for 12c database Upgrade

(1) Oracle E-Business Suite minimum version required is 12.0.6 or  12.1.3

(2) These are some of the prerequisite which needs to be present
If you are on E-Business Suite Release 12.0,
apply below patches
Patch 6728000 – Oracle E-Business Suite 12.0.6
Patch 8796558
Patch 16289505 – 12c Release 1 interoperability patch for Release 12.0.6
If you are on E-Business Suite Release 12.1,
apply below patches
Patch 8796558
Patch 9239090 – Oracle E-Business Suite 12.1.3
Patch 12923944
Patch 13473483
Patch 16289505 – 12c Release 1 interoperability patch for Release 12.1
Patch 18843706 – apply only if you have applied a Recommended Patch Collection (RPC) patch
Patch 19558309

Apply these patches with adpatch with maintenance mode on

(3) Apply patch 6400501
If you are on a UNIX/Linux platform, apply the 10.1.0.5 version of Patch 6400501 to the iAS 10.1.2 Applications tier Oracle home.

Database Installation:

(1) Prepare to create the 12.1.0 Oracle home

The 12.1.0 Oracle home must be installed on the database server node in a different directory than the current Oracle home.

(2) Install the 12.1.0 software

Log in to the database server node as the owner of the Oracle RDBMS file system and database instance. Ensure that environment settings, such as ORACLE_HOME, are set for the new Oracle home you are about to create, and not for any existing Oracle homes on the database server node. Perform all the steps in the “Installing Oracle Database” chapter of the Oracle Database Installation Guide 12c Release 1 (12.1) for your platform.

In the install windows, select the options to install the database software only. Select any languages other than American English that are used by your Applications database instance. Choose the Enterprise Edition installation type.

(3) Install Oracle Database 12c Products from the 12c Examples CD (mandatory)

On the database server node, as the owner of the Oracle RDBMS file system and database instance, perform the tasks in the “Install Oracle Database Examples” section in the Oracle Database Examples Installation Guide.

Select any languages other than American English that are used by your Applications database instance.

(4)Create nls/data/9idata directory

On the database server node, as the owner of the Oracle RDBMS file system and database instance, run the following command to create the $ORACLE_HOME/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 12c Oracle home.

(5) Apply additional 12.1.0.2 RDBMS patches

Apply the following patches for environments without the In-Memory option enabled:

For all UNIX/Linux platforms:
Patch 6880880- the latest OPatch version for 12.1.0.
Patch 19382851
Patch 19393542
Patch 19627012
Patch 19649152
Patch 20204035

Apply the following patches for environments with the In-Memory option enabled:

For all UNIX/Linux platforms:
Patch 6880880- the latest OPatch version for 12.1.0.
Patch 20698050- 12.1.0.2.7
Patch 19475484- Take the 12.1.0.2.7 version of the patch.

Related: All information on E-Business Suite

Database PreUpgrade

(1) Shut down Applications server processes and database listener

Attention: Make sure that you do not have the LOCAL_LISTENER initialization parameter set to ensure that the database does not inadvertently point to a non-existent listener during upgrade.

(2) Drop SYS.ENABLED$INDEXES (conditional)

If the SYS.ENABLED$INDEXES table exists, use SQL*Plus to connect to the database as SYSDBA and running the following command to drop it:

SQL> drop table sys.enabled$indexes;

(3) Disable Database Vault (conditional)

Disable Oracle Database Vault as follows:
UNIX: Run the following commands:

$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk dv_off ioracle

(4) Remove the MGDSYS schema (conditional)

If you are upgrading from an RDBMS version prior to 12c, on the old database server node, use SQL*Plus to connect to the database as SYSDBA and run the $ORACLE_HOME/md/admin/catnomgdidcode.sql script. This drops the MGDSYS schema.

$ sqlplus "/ as sysdba" @?/md/admin/catnomgdidcode.sql

(5) Ensure that the oratab file contains an entry for the database to be upgraded.

(6) Copy the file preupgrd.sql from 12c Oracle home to the temp directory and execute this on source database.It will give lot of action which we need to resolve before starting the upgrade

(7) Run the gather stats on SYS schema

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

(8) Check the DST timezone for the source database and compare with the timezone available in the 12c and take proper action

(9) Verify That Materialized View Refreshes Have Completed Before Upgrading

SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s
WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;

(10) Ensure That No Files Need Media Recovery Before Upgrading

SELECT * FROM v$recover_file;

(11) Ensure That No Files Are in Backup Mode Before Upgrading

SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

(12) Resolve Outstanding Distributed Transactions Before Upgrading

SELECT * FROM dba_2pc_pending;

If the query in the previous step returns any rows, then run the following statements:

SQL> SELECT local_tran_id FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');
SQL> COMMIT;

(13) Make sure if standby exists,it is in sync with Primary

(14) Take proper backup of the database.

(15) Purge dba_recycle_bin

(16) Run utlrp to compile all the invalid objects in the database

Upgrade the database instance

(1)Remove desupported initialization parameters and adjust deprecated initialization parameters. Certain parameters are desupported in Oracle Database 12c, while other parameters have become deprecated. Remove all desupported parameters from any parameter file that starts an Oracle Database 12c instance. Desupported parameters might cause errors in Oracle Database 12c. Also, alter any parameter whose syntax has changed in the new release

(2) If it is RAC,set cluster_database =true

(3) Start the Upgrade using dbca and select the database need to be upgraded

(4) There is new parallel upgrade utility in 12c which reduce the upgrade downtime substantially. DBCA make use of it

(5) 12 c DBCA will present you with these new options
 Upgrade Parallelism  it  enables the degree of parallelism for the upgrade process .By default DBUA runs in parallel upgrade mode.
This option reduces the time needed to perform the upgrade based on the number of CPUs available to handle the running of scripts and processes.By default upgrade parallelism is set to number of CPU’s or an alternate value can be selected from the dropdown list. By default upgrade can run with parallelism 4 even though machine is a single processor.

Gather Statisitcs Before Upgrade It is to reduce the overall time for the upgrade process, if a decision is made to gather the statistics prior to upgrading the database.

Set User Tablespace Read Only During the Upgrade  it is to set user tablespaces to “read only” during the upgrade. In case of any failure then only non-user tablespaces ( system ,sysaux and undo .. ) would need to be restored.

(6) 12c DBCA also presents some RMAN options to use

Use RMAN Backup: Select either the option for DBUA to create an RMAN backup now, or to use your existing RMAN backup.

Create RMAN Backup before Upgrade: Enter the full path for a location for the backup in the Backup Location field.

Use Existing RMAN Backup: DBUA displays the timestamp for the latest RMAN backup that exists.

Use Flashback and Guaranteed Restore Point: If a flash recovery area is currently configured then the current settings are retained. DBUA also displays a screen to permit overriding these values if needed.

Create a Guaranteed Restore Point before Upgrade: DBUA automatically create a restore point that you can revert back to in case of upgrade failure.

Use Existing Guaranteed Restore Point: If a restore point was previously created then select the existing restore point from the dropdown list.

I have my own backup and restore strategy Use this option only if a customized backup procedure was used back up the database. You should use customized restore procedures must be used to restore the customized backup.

(7) Once the upgrade is finished
Modify initialization parameters
Use the following sections in document 396009.1, Database Initialization Parameter Settings for Oracle Applications Release 12 on My Oracle Support as a guideline

After the Database Upgrade:

(1) Run adgrants.sql

Copy $APPL_TOP/admin/adgrants.sql from the administration server node to the database server node. Use SQL*Plus to connect to the database as SYSDBA and run the script using the following  command:

sqlplus "/ as sysdba" @adgrants.sql  APPS

(2) Grant create procedure privilege on CTXSYS

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

sqlplus apps/[APPS password] @adctxprv.sql  [SYSTEM password] CTXSYS

(3) Set CTXSYS parameter: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');

(4) Validate Workflow ruleset
On the administration server node, use SQL*Plus to connect to the database as APPS and run the $FND_TOP/patch/115/sql/wfaqupfix.sql script using the following command:

$ sqlplus [APPS user]/[APPS password] @wfaqupfix.sql\
[APPLSYS user] [APPS user]

(5)Gather statistics for SYS schema

Copy $APPL_TOP/admin/adstats.sql from the administration server node to the database server node. Note that adstats.sql has to be run in restricted mode. Use SQL*Plus to connect to the database as SYSDBA and use the following commands to run adstats.sql in restricted mode:

$ sqlplus "/ as sysdba"
SQL> alter system enable restricted session;
SQL> @adstats.sql
$ sqlplus "/ as sysdba"
SQL> alter system disable restricted session;
SQL> exit;

Note: Make sure that you have atleast 1.5 GB of free default temporary tablespace.

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

i) Copy AutoConfig to the RDBMS ORACLE_HOME
Update the RDBMS ORACLE_HOME file system with the AutoConfig files by performing the following steps:

On the application tier
Log in to the APPL_TOP environment (source the environment file)
Create appsutil.zip file
perl $AD_TOP/bin/admkappsutil.pl
This will create appsutil.zip in $INST_TOP/admin/out
On the database tier
Copy or FTP the appsutil.zip file to the <RDBMS ORACLE_HOME>
cd <RDBMS ORACLE_HOME>
unzip -o appsutil.zip

ii) Install JRE on database tiers

Copied the “jre” folder from 10g oracle_home/appsutil to 11g
oracle_home/appsutil.
Attention: After creating the appsutil directory and before running AutoConfig, copy orai18n.jar from $ORACLE_HOME/jlib to $ORACLE_HOME/appsutil/jre/lib/ext

iii) Generate the Database Context File

Execute the following command to create your Database Context File:

perl $ORACLE_HOME/appsutil/bin/adbldxml.pl

Attention: If you build the context file for an instance that runs on RAC, all your RAC instances have to be up and running while executing the adbldxml utility. The utility connects to all RAC instances to gather information about the configuration.

Attention: After creating the XML context file, ensure the variable s_jretop points to $ORACLE_HOME/jdk/jre and is not altered to any other value.
iv) Run autoconfig on DB Tier:

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

Related: Steps to run autoconfig on both Database and application tier

(7) Create the new MGDSYS schema (conditional)
If you upgraded from an RDBMS version prior to 12c, use SQL*Plus to connect to the database as SYSDBA and run the $ORACLE_HOME/rdbms/admin/catmgd.sql script. This creates the new MGDSYS schema.

$ sqlplus "/ as sysdba" @?/rdbms/admin/catmgd.sql

(8) Apply post-upgrade WMS patch  (conditional)
If you  upgraded from an RDBMS version prior to 12c, apply Patch 14356466 for E-Business Suite Release 12.0 or Patch 18039691 for E-Business Suite Release 12.1

(9) Create Demantra  privileges (conditional)

If you are using Demantra, perform the steps in document 730883.1 on My Oracle Support.

(10) Move the custom tns entries to the new 12 Oracle home and verify all the database link are working

(11) Re-create grants and synonyms

On the administration server node, as the owner of the Applications file system, run AD Administration and select the “Recreate grants and synonyms for APPS schema” task from the Maintain Applications Database Objects menu.

(12) Enable Database Vault (conditional)

If you need, you can re-enable the Database Vault after the upgrade is complete
Enable Oracle Database Vault as follows:
UNIX: Run the following commands. The make command enables both Oracle Database Vault (dv_on) and Oracle Label Security (lbac_on). You must enable Oracle Label Security before you can use Database Vault.
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk dv_on lbac_on ioracle

(13) Restart Applications server processes
Restart all the Application tier server processes that you shut down previously. It includes Database,database listener, Concurrent Manager and application services.

(14) Synchronize Workflow views
Log on to Oracle E-Business Suite with the “System Administrator” responsibility. Click Requests > Run > Single Request and the OK button. Enter the following parameters:
Request Name = Workflow Directory Services User/Role Validation
Batch Size = 10000
Fix dangling users = Yes
Add missing user/role assignments = Yes
Update WHO columns in WF tables = No

Click “OK” and “Submit”.

(15) If the media management software is being,change the link of media library

(16) Take a fresh backup of the database

I have tried to given as much information as required for How to upgrade R12.1 Instance database  to 12c.