Search This Blog

Monday, January 9, 2017

Step by step Database upgrade 11.2.0.3 to 11.2.0.4 for EBS R12.2

Below steps were experimented to my environment and i have upgraded database to 11204 successfully. Few areas are environment specific and kindly double check those steps with your environment before you execute.

Source : 11.2.0.3
Target : 11.2.0.4
Application : R12.2.4
OS : Oracle solaris 5.10

Interoperability Notes E-Business Suite Release 12.2 with Database 11g Release 2 (Doc ID 1623879.1)
Complete Checklist for Manual Upgrade to Oracle Database 11gR2 (11.2) (Doc ID 837570.1)

Recommendations for Source database 

1) Ensure that all database components/objects provided by Oracle are VALID  in the source database  prior to starting the upgrade.
2) Ensure that you do not have duplicate objects in the SYS and SYSTEM schema.
The following objects are permissible duplicate objects:
 OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
AQ$_SCHEDULES TABLE
AQ$_SCHEDULES_PRIMARY INDEX
DBMS_REPCAT_AUTH PACKAGE
DBMS_REPCAT_AUTH PACKAGE BODY
Note: All these checks are done when you execute step 3 (dbupgdiag.sql)
3) Disable the custom triggers that would fire before/after DDL and enable them after the upgrade is complete.

Requirements and recommendations for target database 
  • Check the certification of Oracle 11gR2 with your Platform/Operating system before downloading and installing Oracle 11gR2. Please check the certification information on My Oracle Support.
  • Download and Install Oracle 11g Release 2 in a new Oracle Home and make sure there are no  relinking errors. (Detailed steps provided in the upcoming section)
  • Install the latest available Patchset from Metalink (if available).
  • Install the latest opatch available for your platform and database version  (if available).
  • Either take a Cold or Hot backup of your source database (advisable to have cold backup).
  • For an awareness of performance-related issues in 11.2.0.2 . Please refer  Note 1320966.1 "Things to Consider Before Upgrade to 11.2.0.2 in Relation to Database Performance"








Database Installation:
Prepare to create the 11.2.0 Oracle home
The 11.2.0 Oracle home must be installed on the database server node in a different directory than the current Oracle home.
Read Chapters 1 and 2 of the Oracle Database Installation Guide 11g Release 2 (11.2) for your platform. Also read Chapter 1 and the "System Considerations and Requirements" section of Chapter 3 of the Oracle Database Upgrade Guide 11g Release 2 (11.2). Make sure you thoroughly understand the installation and upgrade processes. Perform any step that is relevant for your environment.
Attention: For HP Itanium platforms using the NFS file system, choose a disk to install the 11g Oracle home separate from the database files. The mount options of the disk containing the database files must include forcedirectio. The 11g Oracle home (or any other binaries) must not include the forcedirectio mount option.
Install the 11.2.0.4 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 Chapter 4, "Installing Oracle Database" of the Oracle Database Installation Guide 11g Release 2 (11.2) for your platform.
In the Installation Types window, use the Product Languages button to select any languages other than American English that are used by your Applications database instance. Choose the Enterprise Edition installation type. In the subsequent windows, select the options not to upgrade an existing database and to install the database software only.
Note:
1.        When installing 11.2.0.4 on Oracle Linux 7 or Red Hat Linux 7, ignore the warning regarding missing package 'pdksh-5.2.14' during pre-requisite checks.
2.        When installing 11.2.0.4 on Oracle Linux 7 or Red Hat Linux 7 and encountering the installer error in invoking target 'agent nmhs' of makefile with the log file showing an undefined reference to symbol 'B_DestroyKeyObject', users should review and implement the workaround noted in the document Installation of Oracle 11.2.0.4 Database Software on OL7 fails with "undefined reference to symbol 'B_DestroyKeyObject'" error".
 Install Oracle Database 11g Products from the 11g 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.
In the Installation Types window, use the Product Languages button to select any languages other than American English that are used by your Applications database instance.
After the installation, make sure that:
c.        The ORACLE_BASE environment variable must be set accordingly.
d.        The ORACLE_HOME environment variable points to the new 11.2.0 Oracle home.
e.        The PATH environment variable includes $ORACLE_HOME/bin and the directory where the new perl executable is located (usually $ORACLE_HOME/perl/bin).
f.         The LD_LIBRARY_PATH environment variable includes $ORACLE_HOME/lib.
g.        The PERL5LIB environment variable points to the directories where the new perl libraries are located ($ORACLE_HOME/perl/lib/[perl version] and $ORACLE_HOME/perl/lib/site_perl/[perl version] for UNIX/Linux, $ORACLE_HOME/perl/lib and $ORACLE_HOME/perl/site/lib for Windows).
As you read this document, bear the following important points in mind:
  • Oracle strongly recommends that you always use the latest version of the opatch utility, available from My Oracle Support via Patch 6880880.
  • More information on patching the technology Oracle Homes is available in My Oracle Support Knowledge Document 1355068.1, Oracle E-Business Suite 12.2 Patching Technology Components Guide.

Section 2: EBS Technology Codelevel Checker (ETCC)

ETCC can be downloaded via Patch 17537119 from My Oracle Support. ETCC maps missing bugfixes to the default corresponding patches and displays them in a patch recommendation summary. If your Oracle E-Business Suite 12.2 installation has additional patches installed as well as the recommended patches, you may need to install a merge patch from Oracle Support. As well as installation instructions and basic commands, the README for this patch includes a number of usage scenarios and examples so should be carefully reviewed before running ETCC.
Note: Always use the latest version of ETCC, as new bugfixes will not be checked by older versions of the utility. Also, patch recommendation summaries are only provided for the latest and latest but one quarterly bundles supported by Oracle E-Business Suite Release 12.2. Refer to Section 3: Database Patches and Bug Numbers for the Oracle database releases and bundle documentation.
The two scripts that make up ETCC are:
  • checkDBpatch.sh (checkDBpatch.cmd on Microsoft Windows). This is the Database EBS Technology Codelevel Checker (DB-ETCC), which determines if all the needed bugfixes exist in the specified database ORACLE_HOME. When using Oracle Real Application Clusters (Oracle RAC) ensure ETCC is run on every database ORACLE_HOME.
  • checkMTpatch.sh (checkMTpatch.cmd on Microsoft Windows). This is the Middle Tier EBS Technology Codelevel Checker (MT-ETCC), which determines if all the needed bugfixes exist in the middle tier file system.

Section 3.1: Database 11.2.0.4 Patches and Bug Numbers

Table 1.1 - Patches and bug numbers for Database 11.2.0.4.0 (UNIX platforms)
'First included in startCD' column is not relevant for 11.2.0.4 Database. Apply all the patches listed for your platform.
Acronyms used: N/A = Not applicable.

Footnote 3 - After applying the patch, you must run the following Oracle E-Business Suite Release 12.2 specific script and then restart the database (which is not documented in the patch readme):
$ perl $ORACLE_HOME/nls/data/old/cr9idata.pl
Footnote 4 - If a conflict is reported with any of these previously documented fixes: Patch 21114125, Patch 21472186 or Patch 22855980 roll back the conflicting patch before applying Patch 22876444.
23477849
Patch installation :
20523280
1.      Maintain a location for storing the contents of the patch ZIP file. In the rest of the document, this location (absolute path) is referred to as <PATCH_TOP_DIR>.

2.      Extract the contents of the patch ZIP file to the location you created in Step (1). To do so, run the following command:
        $ unzip -d <PATCH_TOP_DIR> p20523280_112040_SOLARIS64.zip

3.      Navigate to the <PATCH_TOP_DIR>/20523280 directory:
        $ cd <PATCH_TOP_DIR>/20523280

4.      Install the patch by running the following command:

        Note:
        When OPatch starts, it validates the patch and ensures that there are no conflicts with the software already installed in the ORACLE_HOME of the Oracle Database. OPatch categorizes conflicts into the following types:
        -       Conflicts with a patch already applied to the ORACLE_HOME - In this case, stop the patch installation and contact Oracle Support Services.
        -       Conflicts with a patch already applied to the ORACLE_HOME that is a subset of the patch you are trying to apply  - In this case, continue with the patch installation because the new patch contains all the fixes from the existing patch in the ORACLE_HOME. The subset patch will automatically be rolled back prior to the installation of the new patch.

4.        Start the services from the Oracle home.
Pre upgrade steps :
To download and use the latest Pre-Upgrade Information Tool see the following:
How to Download and Run Oracle's Database Pre-Upgrade Utility Note 884522.1
or 
Run the Pre-Upgrade Information Tool for Collecting Pre-Upgrade Information
Step1 
  • Log into the system as the owner of the Oracle Database 11g Release 2 (11.2) Oracle Home directory.
  • Copy the Pre-Upgrade Information Tool (utlu112i.sql) from the Oracle Database 11g Release 2 (11.2) ORACLE_HOME/rdbms/admin directory to a directory outside of the Oracle Home, such as the temporary directory on your system. 
$ORACLE_HOME/rdbms/admin/utlu112i.sql
Failure to run the pre-upgrade tool (utlu112i.sql) will result in the following error while running the catupgrd.sql script :
SQL> SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
2 FROM registry$database
3 WHERE tz_version != (SELECT version from v$timezone_file);
SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
*
ERROR at line 1:
ORA-01722: invalid number

It is  required to restore the database back to previous version in order to run the preupgrade tool (utlu112i.sql ) .
Step 2
  • Change to the directory where utlu112i.sql had been copied in the previous step.
  • Start SQL*Plus and connect to the database instance as a user with SYSDBA privileges. Then run and spool the utlu112i.sql file. Please note that the database should be started using the source Oracle Home .
$ sqlplus '/ as sysdba'
SQL> spool upgrade_info.log
SQL> @utlu112i.sql
SQL> spool off
SQL>
Database
This section displays global database information about the current database, such as the database name, release number and compatibility level. A warning is displayed if you must adjust the COMPATIBLE initialization parameter before the database is upgraded.

Logfiles
This section displays a list of redo log files in the current database whose size is less than 4 MB. For each log file, the file name, group number and recommended size is displayed.
In a manual upgrade using SQL scripts and utilities, new files of at least 4 MB (preferably 10 MB) must be created in the current database, and any redo log files less than 4 MB must be dropped before the database is upgraded. These tasks are performed automatically by the Database Upgrade Assistant.

Tablespaces
This section displays a list of tablespaces in the current database. For each tablespace, the tablespace name and minimum required size is displayed. In addition, a message is displayed if the tablespace is adequate for the upgrade.In a manual upgrade using SQL scripts and utilities, space must be added to tablespaces that do not have enough free space in the current database. These tablespace adjustments must be made before the database is upgraded. This task is performed automatically by the Database Upgrade Assistant.

Update Parameters
This section displays a list of initialization parameters in the parameter file of the current database that must be adjusted before the database is upgraded. The adjustments must be made to the parameter file after it is copied to the new Oracle Database 11g release.

Deprecated Parameters
This section displays a list of initialization parameters in the parameter file of the current database that are deprecated in the new Oracle Database 11g release.Obsolete Parameters This section displays a list of initialization parameters in the parameter file of the current database that are obsolete in the new Oracle Database 11g release2 (11.2). Obsolete initialization parameters must be removed from the parameter file before the database is upgraded.

Step3

Check for the integrity of the source database.

Check for the integrity of the source database prior to starting the upgrade by downloading and running the  dbupgdiag.sql script from the My Oracle Support article below:
Note 556610.1  Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)

If the dbupgdiag.sql script reports any invalid objects, run $ORACLE_HOME/rdbms/admin/utlrp.sql (multiple times) to validate the invalid objects in the database until there is no change in the number of invalid objects.
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> @utlrp.sql

After validating the invalid objects, re-run dbupgdiag.sql in the database once again and make sure that everything is fine.
It is advisable to do a Health Check using "hcheck.sql" script .Please refer following article to download the script (Note 136697.1)
Step 4

Deprecated CONNECT Role

After upgrading to Oracle Database 11g Release 2 (11.2) from Oracle Database9i Release 2 (9.2) or Oracle Database 10g Release 1 (10.1), the CONNECT role has only the CREATE SESSION privilege; the other privileges granted to the CONNECT role in earlier releases are revoked during the upgrade. To identify which users and roles in your database are granted the CONNECT role, use the following query:
SELECT grantee FROM dba_role_privs
WHERE granted_role = 'CONNECT' and
grantee NOT IN (
'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
'WMSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');

If users or roles require privileges other than CREATE SESSION, then grant the specific required privileges prior to upgrade.
The upgrade scripts adjust the privileges for the Oracle-supplied users.

In Oracle 9.2.x and 10.1.x CONNECT role includes the following privileges:
SELECT GRANTEE,PRIVILEGE
FROM DBA_SYS_PRIVS
WHERE GRANTEE ='CONNECT'

GRANTEE PRIVILEGE
------- ----------------------
CONNECT CREATE VIEW
CONNECT CREATE TABLE
CONNECT ALTER SESSION
CONNECT CREATE CLUSTER
CONNECT CREATE SESSION
CONNECT CREATE SYNONYM
CONNECT CREATE SEQUENCE
CONNECT CREATE DATABASE LINK

From Oracle 10.2, 'CONNECT' role only includes 'CREATE SESSION' privilege.
Step 5

Create script for DBLINK (in case the database has to be downgraded again).

During the upgrade to Oracle Database 11g Release 2 (11.2) from Oracle Database 9i Release 2 (9.2) or Oracle Database 10g Release 1 (10.1), any passwords in database links are encrypted. To downgrade to the original release, all of the database links with encrypted passwords must be dropped prior to the downgrade. Consequently, the database links do not exist in the downgraded database. If you anticipate a requirement to be able to downgrade to your original release, then save the information about affected database links from the SYS.LINK$ table, so that you can re-create the database links after the downgrade.
SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)
||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING
'''||L.HOST||''''
||chr(10)||';' TEXT
FROM SYS.LINK$ L, SYS.USER$ U
WHERE L.OWNER# = U.USER#;
Step 6

Check for TIMESTAMP WITH TIMEZONE Datatype

The RDBMS DST patching has been greatly improved in 11gR2.
Unlike upgrading for older versions (upgrading 10.2.0.4 to 11.1.0.7 for example) there is no need anymore to 
apply "dst patches" on the old version *before* the upgrade.

If you upgrade from an older RDBMS version to 11gR2 the DST version in 11gR2 after the upgrade will be 
simply the same as the DST version that was used in the older RDBMS version.

There are however a few situations where some extra steps are needed, 
so please do check below notes before upgrading to 11gR2., depending on to what 11gR2 version you upgrade to

Applying the RDBMS DSTv17 patch 12949905 on the server side in 11.2.

After a DST patch is installed in an 11.2 $ORACLE_HOME there are steps who need to be done to change a existing database to use this newer DST version.
Simply applying the RDBMS DST patch and restarting the database will NOT enable the new applied RDBMS DST version patch (like it did in pre-11.2 versions). 

For 11.2.0.4:
* Request (if needed) and download RDBMS DSTv17 Patch 12949905 for your platform.
* Apply the RDBMS DSTv17 
Patch 12949905  using Opatch, there is no need to shut down or stop the database or other processes.
Patches location - /a02/patches
Step 8

Optimizer Statistics

When upgrading to Oracle Database 11g Release 2 (11.2), optimizer statistics are collected for dictionary tables that lack statistics. This statistics collection can be time consuming for databases with a large number of dictionary tables, but statistics gathering only occurs for those tables that lack statistics or are significantly changed during the upgrade.

To determine the schemas which lack statistics, either review the output of the utlu112i.sql script or download and run the script from the below article:
Note 560336.1 Script to Check Schemas with Stale Statistics

To decrease the amount of downtime incurred when collecting statistics, you can collect statistics prior to performing the actual database upgrade. As of Oracle Database 10g Release 1 (10.1), Oracle recommends that you use the DBMS_STATS.GATHER_DICTIONARY_STATS procedure to gather these statistics. For example, you can enter the following:
$ sqlplus "/as sysdba"

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
Step 9

Disable Oracle Database Vault (Not applicable to my env)

When upgrading from Oracle Database release 10.2, if you have enabled the Oracle Database Vault option in your current Oracle Home, then you must disable Oracle Database Vault in the target Oracle Home where the new release 11.2 software is installed before upgrading the database, and enable it again when the upgrade is finished. If Database Vault is enabled, then DBUA will return an error asking you to disable Database Vault prior to upgrade.

You must do this before upgrading the database. Enable Oracle Database Vault again once the upgrade is completed.

Please refer to the following Documentation/Articles for complete information to Disable/Enable Oracle Database Vault.

Note 453903.1  - Enabling and Disabling Oracle Database Vault in UNIX
Step 10

** Not applicable to my env

Backing up Enterprise Manager Database Control Data. This can be skipped if EM Database Control Console is not being used or not configured.

After upgrading to Oracle Database 11g release 2 (11.2), if you want to downgrade Oracle Enterprise Manager Database Control you must save your Database Control files and data before upgrading your database. The emdwgrd utility can be used to keep a copy of your database control files and data before upgrading your database. The emdwgrd utility resides in the ORACLE_HOME/bin directory in the Oracle Database 11g release 2 (11.2) home.

1. Set ORACLE_HOME to your old Oracle Home
2. Set ORACLE_SID to the SID of the database being upgraded.
3. Set PATH, LD_LIBRARY_PATH and SHLIB_PATH to point to the Oracle home from which the database is being upgraded.
     Example : export SHLIB_PATH=$ORACLE_HOME/lib:$SHLIB_PATH
                    export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
4. Change directory to Oracle Database 11g release 2 (11.2) home.
5. Run the emdwgrd command.
   a. Run the following command for single instance database:
$ emdwgrd -save -sid old_SID -path save_directory

Step 12

This optional check is introduced to spot any logical corruption in underlying objects and their dependencies. 
This proactive check is introduced to avoid any failure in database upgrade at a later stage due to such corruption. If there is corruption the upgrade will most likely fail.

To check for corruption in the dictionary, use the following commands in SQL*Plus (connected as sys):
Set verify off
Set space 0
Set line 120
Set heading off
Set feedback off
Set pages 1000
Spool analyze.sql

SELECT 'Analyze cluster "'||cluster_name||'" validate structure cascade ;'
FROM dba_clusters
WHERE owner='SYS'
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade ;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='NO'
AND (iot_type='IOT' OR iot_type is NULL)
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade into invalid_rows;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='YES';

spool off

This creates a script called analyze.sql.
Now execute the following steps:
$ sqlplus "/ as sysdba"
SQL> @$ORACLE_HOME/rdbms/admin/utlvalid.sql
SQL> @analyze.sql

This script (analyze.sql) should not return any errors.

Note:
1. ORA-30657 might occur if there is any external table validated, which can be safely ignored as per Note 209355.1 ORA-30657: Using ANALYZE TABLE for an External Table
2. Errors shown below when executing analyze.sql can be ignored:
SP2-0734: unknown command beginning "SQL> SELEC..." - rest of line ignored.
SP2-0042: unknown command "SQL>" - rest of line ignored.
SP2-0734: unknown command beginning "SQL> spool..." - rest of line ignored.
3. "ORA-00054: resource busy and acquire with NOWAIT specified" may be returned when analyzing AWR tables (WRH$_...)
    to workaround this error AWR can be temporarily disabled :
3.a) get current value for snapshot interval :
       select snap_interval,retention from dba_hist_wr_control;
3.b) set this interval to zero to temporarily disable AWR :
    exec dbms_workload_repository.modify_snapshot_settings(interval=>0);

3.c) Analyze the WRH$ tables 

3.d) Revert back to initial value : 
   
  exec dbms_workload_repository.modify_snapshot_settings(interval=><value in mn of snap_interval returned at 3.a>);
Step 13
Before upgrading Oracle Database, you must wait until all materialized views have completed refreshing and check that replication is stopped. 
Run the following query to determine if there are any materialized view refreshes still in progress:
SQL> select distinct(trunc(last_refresh)) from dba_snapshot_refresh_times;
SQL> select s.obj#,o.obj#,s.containerobj#,lastrefreshdate,pflags,xpflags,o.name,o.owner#, bitand(s.mflags, 8) from obj$ o, sum$ s
where o.obj# = s.obj# and o.type# = 42 AND bitand(s.mflags, 8) = 8;
If the second query returns any row, then use Note 1442457.1 : During 11g Upgrade, Mview refresh warning
Step 14

Ensure that no files need media recovery and that no files are in backup mode.
SELECT * FROM v$recover_file;
SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
This should return no rows. 
Step 16

Resolve outstanding distributed transactions prior to the upgrade.
SQL> select * from dba_2pc_pending; 

If this returns rows you should do the following: 
SQL> SELECT local_tran_id
     FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');
SQL> COMMIT;
Step 17

To check if a standby database exists, issue the following query:
SELECT SUBSTR(value,INSTR(value,'=',INSTR(UPPER(value),'SERVICE'))+1)
FROM v$parameter
WHERE name LIKE 'log_archive_dest%' AND UPPER(value) LIKE 'SERVICE%';

If this query returns a row, then sync the standby database with the primary database.
1. Make sure all the logs are transported to the standby server after a final log switch
in the primary.
2. Start the recovery of the standby database with the NODELAY option.
Step 18 

Disable all batch and cron jobs. 
About jobs initiated with Oracle the packages DBMS_JOB, DBMS_SCHEDULER can be used , regarding cron jobs (external jobs controlled at the OS level), this is a task for your Unix administrator


See also : 

Note 404238.1 : How to Disable an Entry from DBMS_SCHEDULER 
Note 1335741.1 : How To Stop A Running Job Using DBMS_JOB 
Note 67695.1 : PROCEDURE DBMS_JOB.BROKEN Specification 
Step 19

Ensure the users SYS and SYSTEM have 'SYSTEM' as their default tablespace.
You must have sufficient space in the tablespace or be set to extents unlimited.
SQL> SELECT username, default_tablespace
     FROM dba_users
     WHERE username in ('SYS','SYSTEM'); 

If DEFAULT_TABLESPACE is anything other than SYSTEM tablespace, modify the default tablespace to SYSTEM by using the below command.
SQL> ALTER user SYS default tablespace SYSTEM;
SQL> ALTER user SYSTEM default tablespace SYSTEM;

Step 20 

Ensure that if the aud$ table exists that it is in the SYS schema and in the SYSTEM tablespace.
SQL> SELECT owner,tablespace_name
     FROM dba_tables
     WHERE table_name='AUD$';

If the AUD$ table is not in SYSTEM tablespace and not owned by the SYS user then before doing the upgrade put it back to the SYSTEM tablespace and it should be owned by SYS .

Note: If the AUD$ table exists and is in use, upgrade performance can be effected depending on the number of records in the table. 

Step 21

Check whether database has any externally authenticated SSL users.
SQL> SELECT name FROM sys.user$
     WHERE ext_username IS NOT NULL
     AND password = 'GLOBAL';
If any SSL users are found then Step 33 has to be followed after the upgrade.

Step 22


Note down the location of datafiles, redo logs and control files. Also take a backup of all configuration files like listener.ora, tnsnames.ora, etc. from $ORACLE_HOME.
SQL> SELECT name FROM v$controlfile;
SQL> SELECT file_name FROM dba_data_files;
SQL> SELECT group#, member FROM v$logfile;.
Step 23
If the you have upgraded the Grid Infrastructure then this step is not needed as it was done as part of the GI install / upgrade

a) Stop the listener for the database.
$ lsnrctl stop
Previous versions of the listener are not supported for use with an Oracle Database 11g Release 2 (11.2) database. However, it is possible to use the new version of the listener with previous versions of Oracle Databases.
If you are upgrading from 9i or upgrading manually without using DBUA, run Oracle Net Configuration Assistant before upgrading the Oracle RAC database.

This is a two-step option.
 You must first run Oracle Net Configuration Assistant from the old Oracle Home to remove the old listener.
- Invoke the Netca
- Choose the configuration you want to do  ==> Choose Listener Configuration
- Select what you want to do ==> Delete
- Select the listener you want to delete .

 Then you must run Oracle Net Configuration Assistant again from the new Oracle Database 11g Release 2 (11.2) Home to create a new listener.

- Invoke the Netca
- Choose the configuration you want to do ==> Choose Listener Configuration
- Select what you want to do ==> Add
- Provide the detail that is required to configure the listener.

You must remove the old listener before creating a new one. If you attempt to create a new listener from the new Oracle Home first, and use the same name and port as the old listener, then Oracle Net Configuration Assistant returns an error.

Note: This is your only option if you want to upgrade your Oracle RAC database manually.

b) Stop other executable such as dbconsole, isqlplus, etc.
$ emctl stop dbconsole
$ isqlplusctl stop

Step 24 

Shutdown the database.
$ sqlplus "/as sysdba"
SQL> shutdown immediate;

Back up the Database

1. Perform Cold Backup (Recommended)
(or)
2. Take a backup using RMAN
Connect to RMAN:

rman "target / nocatalog"

RUN
{
ALLOCATE CHANNEL chan_name TYPE DISK;
BACKUP DATABASE FORMAT '<db_backup_directory>%U' TAG before_upgrade;
BACKUP CURRENT CONTROLFILE TO '<controlfile_backup_directory>';
}

--> backup_directory >> Location of the Database backup.
--> controlfile_backup_directory >> Location of the Controlfile backup.
Step 25
 - copy the initialization file from source Oracle Home to <target 11GR2 home>/dbs (<target 11GR2 home>\database on Windows)
- then process in target 11GR2 directory (<target 11GR2 home>/dbs for unix and <target 11GR2 home>\database for Windows) to the needed modiciations : 

Comment out obsoleted parameters (Appendix A) and change all deprecated parameters (Appendix A).
It is  also recommended to  remove all hidden parameters set manually prior to upgrading.

* The DIAGNOSTIC_DEST initialization parameter replaces the USER_DUMP_DEST, BACKGROUND_DUMP_DEST.

According to Bug 8937877, CORE_DUMP_DEST is not deprecated.

Refer to the below article for understanding directory structure in 11g and DIAGNOSTIC_DEST.
Step 27

If your operating system is UNIX then complete this step, else skip to the next step.

1. Make sure the following environment variables point to the Oracle 11g Release 2 (11.2) directories:

- ORACLE_BASE
- ORACLE_HOME
- PATH, LD_LIBRARY_PATH , SHLIB_PATH  and LIBPATH ( for AIX )
For example:

$ export ORACLE_HOME=<location of Oracle 11.2>
$ export PATH=$ORACLE_HOME/bin:$PATH
$ export ORACLE_BASE=<Oracle_Base set during installation>
$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
$ export SHLIB_PATH=$ORACLE_HOME/lib:$SHLIB_PATH
$ export LIBPATH=$ORACLE_HOME/lib:$LIBPATH

Note : If ORACLE_BASE is not known, after setting PATH to 11gR2 Oracle Home, execute 'orabase', which will point the location of the base.
Note : Unset/Remove  the ORA_TZFILE environment variable if it is set in your environment .

Database Upgrade:
9.        Ensure Applications patching cycle is complete
If the patching cycle has not been completed, as the owner of the source administration server, run the following command to finish any in-progress adop session:
$ adop phase=cutover,cleanup
10.     Shut down Applications server processes and database listener
On each application tier server node, shut down all server processes or services. On the database tier server node, shut down the Oracle Net or Net8 database listener in the old Oracle home.
Note: The Applications will be unavailable to users until all remaining tasks in this section are completed.

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

# Multiple entries with the same $ORACLE_SID are not allowed.
DEV:/d02/DEV/oratest/11204:N


Upgrade the database instance


If you are upgrading database from 10g, verify following information

SQL> select DBMS_STATS.GET_PARAM('METHOD_OPT') from dual; 

DBMS_STATS.GET_PARAM('METHOD_OPT')
-------------------------------------------------------------
FOR COLUMNS ID SIZE 1 

If it reports "FOR COLUMNS ID SIZE 1", it might create issues during upgrade.  Hence as a workaround execute 

SQL>exec DBMS_STATS.SET_PARAM('METHOD_OPT','FOR ALL COLUMNS SIZE AUTO'); 

Refer: Unpublished BUG 22454765 - CARRYING METHOD_OPT = "FOR COLUMNS ID SIZE 1" FROM 10G WILL BREAK UPGRADE 
Upgrading Database to 11gR2

Step 28


9.        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:
a.        Common database initialization parameters
b.        Release-specific database initialization parameters for 11gR2
c.        Additional Database Initialization Parameters For Oracle E-Business Suite Release 12.2
d.        Database initialization parameter sizing
Attention: If you encounter the error:
ORA-04030: out of process memory when trying to allocate 822904 bytes (pga heap, kco buffer)
ORA-07445: exception encountered: core dump [dbgtfdFileWrite()+48]
then set the _pga_max_size initialization parameter to a larger value as follows:
_pga_max_size=104857600
Restart your database.
If native compilation of PL/SQL code is disabled in your database instance, ignore the initialization parameters that pertain to the native compilation of PL/SQL code. Ensure that the temporary tablespace is properly defined. Follow the instructions in the Temporary Tablespace Setup section.
Note: There are many ways to generate the parameter file (for example, copying and updating an old init.ora parameter file). You are not confined to using the parameter files generated by the database upgrade assistant.

At the operating system prompt, change to the $ORACLE_HOME/rdbms/admin directory of 11gR2 Oracle Home.
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> startup UPGRADE

Note: If you are upgrading from 9.2 and the SYSAUX table already exists then drop the existing SYSAUX tablespace. The SYSAUX tablespace needs to be created immediately after the database is started in upgrade mode using 11g binaries (with Compatibility set to atleast 10.1 and just prior to the running of the catupgrd.sql scripts.

Create the SYSAUX tablespace only if you are upgrading from Oracle Database9i Release 2 (9.2) with the following mandatory attributes:

ONLINE
PERMANENT
READ WRITE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO

The Pre-Upgrade Information Tool provides an estimate of the minimum required size for the SYSAUX tablespace in the SYSAUX Tablespace section. Refer to the output generated by the utlu112i.sql script in Step 1. The following SQL statement would create a 500 MB SYSAUX tablespace for the database:
SQL> CREATE TABLESPACE SYSAUX
     DATAFILE '<location>/sysaux01.dbf'
     SIZE 500M REUSE
     EXTENT MANAGEMENT LOCAL
     SEGMENT SPACE MANAGEMENT AUTO
     ONLINE;

Set the system to spool results to a log file for later verification after the upgrade is completed and start the upgrade script.
SQL> set echo on
SQL> SPOOL upgrade.log
SQL> @catupgrd.sql
SQL> spool off

These measures are an important final step to ensure the integrity and consistency of the newly upgraded Oracle Database software. Also, if you encountered a message listing obsolete initialization parameters when you started the database for upgrade, then remove the obsolete initialization parameters from the parameter file before restarting. If necessary, convert the SPFILE to a PFILE so you can edit the file to delete parameters.

Run the Post-Upgrade Status Tool $ORACLE_HOME/rdbms/admin/utlu112s.sql which provides a summary of the upgrade at the end of the spool log. It displays the status of the database components in the upgraded database and the time required to complete each component upgrade. Any errors that occur during the upgrade are listed with each component and must be addressed.
$ sqlplus "/as sysdba"
SQL> STARTUP
SQL> @utlu112s.sql

Run catuppst.sql, located in the $ORACLE_HOME/rdbms/admin directory, to perform upgrade actions that do not require the database to be in UPGRADE mode.
SQL> @catuppst.sql

This script can be run concurrently with utlrp.sql. Run utlrp.sql to recompile any remaining stored PL/SQL and Java code in another session.
SQL> @utlrp.sql

Check for the integrity of the upgraded database by running dbupgdiag.sql script from the below article:
Note 556610.1  Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)

If the dbupgdiag.sql script reports any invalid objects, run $ORACLE_HOME/rdbms/admin/utlrp.sql (multiple times) to validate the invalid objects in the database, until there is no change in the number of invalid objects.

After validating the invalid objects, re-run dbupgdiag.sql in the upgraded database once again and make sure that everything is fine.

Disregard warnings related to Network ACLs. AutoConfig manages all the E-Business Suite Network ACLs.
When upgrading all statistics tables, note that Oracle E-Business Suite has only one statistics table, APPLSYS.FND_STATTAB, that needs to be upgraded.
If you plan to change the PL/SQL compilation mode, disable the compilation of objects.
10.     Revoke ORA$BASE grant
If the ORA$BASE edition is not the default edition, a grant has to be revoked.
To see the default edition, use SQL*Plus to connect to the database as SYSDBA and run the following command:
SQL> select * from database_properties where property_name='DEFAULT_EDITION';
If ORA$BASE is not returned by the query, use SQL*Plus to connect to the database as SYSDBA and run the following command:
SQL> revoke use on edition ora$base from public;
11.     Natively compile PL/SQL code (optional)
You can choose to run Oracle E-Business Suite 12 PL/SQL database objects in natively compiled mode with Oracle Database 11g. See the "Compiling PL/SQL Program Units for Native Execution" section of Chapter 12 of Oracle Database PL/SQL Language Reference 11g Release 2 (11.2).
Post Upgrade Steps

Verify the listener.ora file.

For the upgraded instance(s) Verify the ORACLE_HOME parameter to point to the new ORACLE_HOME. Start the listener.
lsnrctl start


Environment Variables

1. Make sure the following environment variables point to the Oracle 11g Release 2 (11.2) directories:

- ORACLE_BASE
- ORACLE_HOME
- PATH, LD_LIBRARY_PATH, SHLIB_PATH and LIBPATH ( for AIX )

Also check that your oratab file and any client scripts that set the value of ORACLE_HOME point to the Oracle Database 11g Release 2 (11.2) Home.

Note : If you are upgrading a cluster database, then perform these checks on all nodes in which this cluster database has instances configured.

2. Modify /etc/oratab entry to use automatic startup.
SID:ORACLE_HOME:Y

For Instance,
orcl:/opt/oracle/product/11.2/db_1:Y

After the upgrade

note : this simply repeats the same actions as given in the "DST" notes referenced in step 6 of this note.

Check the current version of the Oracle time zone definitions in the upgraded database:
SQL> conn / as sysdba
Connected.
SQL>SELECT version FROM v$timezone_file;

VERSION
----------
4

Upgrade Statistics Tables Created by the DBMS_STATS Package.

If you created statistics tables using the DBMS_STATS.CREATE_STAT_TABLE procedure, then upgrade these tables by executing the following procedure:
EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('SYS','dictstattab');

In the example, 'SYS' is the owner of the statistics table and 'dictstattab' is the name of the statistics table. Execute this procedure for each statistics table.

Enable Database Vault

Refer to the following documents for enabling Oracle Database Vault:
Note 453903.1 - Enabling and Disabling Oracle Database Vault in UNIX

SQL> create spfile from pfile;

Change passwords for Oracle-Supplied Accounts.


You can view the status of all accounts by issuing the following SQL statement:
SQL> SELECT username, account_status FROM dba_users ORDER BY username;

To lock and expire passwords, issue the following SQL statement:
SQL> ALTER USER username PASSWORD EXPIRE ACCOUNT LOCK;

TDE (Transparent Data Encryption)
If your are using Transparent Data Encryption then you will have to rekey the master key as follows:
SQL> alter system set encryption key identified by "<wallet password>";

Gather Fixed Object Statistics
Please create stats on fixed objects two weeks after the upgrade using the below command
SQL>EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
It would to good to gather the statistic during non-peak hours

Patch Post installation steps :

After you install the patch, reload the packages into the Oracle Database.
change directory to $ORACLE_HOME/rdbms/admin and then
connect as SYSDBA and run the following in given order :

Note : Below steps must be executed in the order they appear in.

SQL> @?/rdbms/admin/catbundle.sql EBS apply
SQL> @?/md/admin/catmgdidcode.sql

23477849
The following steps load modified SQL files into the database. For a RAC environment, perform these steps on only one node.
        $ sqlplus /nolog
        SQL> CONNECT / AS SYSDBA
        SQL> @?/sqlpatch/23477849/postinstall.sql

22098122

1.      For each database instance running on the Oracle home being patched, connect to the database using SQL*Plus. Connect as SYSDBA and run the below script as follows:

        $ sqlplus /nolog
        SQL> CONNECT / AS SYSDBA
        SQL> @?/sqlpatch/22098122/postinstall.sql
18966843
1.      For each database instance running on the Oracle home being patched, connect to the database using SQL*Plus. Connect as SYSDBA and run the below script as follows:

        $ sqlplus /nolog
        SQL> CONNECT / AS SYSDBA
        SQL> @?/sqlpatch/18966843/postinstall.sql
22731026
1.      For each database instance running on the Oracle home being patched, connect to the database using SQL*Plus. Connect as SYSDBA and run the below script as follows:

        $ sqlplus /nolog
        SQL> CONNECT / AS SYSDBA
        SQL> @?/sqlpatch/22731026/postinstall.sql

DST patch post installation instructions :
Conn / as sysdba
-- this gives the current RDBMS DST version
SELECT version FROM v$timezone_file;

-- check also
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

-- the output gives
-- PROPERTY_NAME VALUE
-- ------------------------------ ------------------------------
-- DST_PRIMARY_TT_VERSION <current DST version> <<<<------ this should match version FROM v$timezone_file if not make sure the database is open when selecting from v$timezone_file;
-- DST_SECONDARY_TT_VERSION 0 <<<<------ this should be "0" if not then see point 3a) in note 977512.1 (for 11gR2) or note 1509653.1 (for 12c)
-- DST_UPGRADE_STATE NONE <<<<------ this should be "NONE" if not then see point 3a) in note 977512.1 (for 11gR2) or note 1509653.1 (for 12c)


Execute the below as sysdba :
Scripts can be found in /a02/patches
Conn / as sysdba
spool countstatsTSTZ.log
@countstatsTSTZ.sql
spool off

Conn / as sysdba
exec dbms_scheduler.purge_log;

Conn / as sysdba
-- check current nr of rows in HISTHEAD / HISTGRM
select count(*) from SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;
select count(*) from SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;
-- check the current retention of stats
-- the default value is 31
select systimestamp - dbms_stats.get_stats_history_availability from dual;
-- now disable stats retention
exec dbms_stats.alter_stats_history_retention(0);
-- remove all stats 
exec DBMS_STATS.PURGE_STATS(systimestamp);
-- check result of purge
select count(*) from SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;
select count(*) from SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;
-- AFTER the DST update you can set the retention back to the original value
exec dbms_stats.alter_stats_history_retention(31);

run upg_tzv_check.sql using SQL*PLUS from the database home

Note that upg_tzv_check.sql takes no arguments, it will detect the highest installed DST patch automatically and needs no downtime, this can be executed on a live production database but it WILL purge the dba_recyclebin.

Conn / as sysdba
spool upg_tzv_check.log
@upg_tzv_check.sql
spool off

A succesfull run will show at the end:
 INFO: A newer RDBMS DST version than the one currently used is found. 
 INFO: Note that NO DST update was yet done. 
 INFO: Now run upg_tzv_apply.sql to do the actual RDBMS DST update. 
 INFO: Note that the upg_tzv_apply.sql script will
 INFO: restart the database 2 times WITHOUT any confirmation or prompt.

Conn / as sysdba
spool upg_tzv_apply.log
@upg_tzv_apply.sql
spool off
A succesfull run will show at the end:
 INFO: The RDBMS DST update is successfully finished.
 INFO: Make sure to exit this sqlplus session.
 INFO: Do not use it for timezone related selects.
Note : 1585343.1
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 11g Oracle home.

 

18.     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). See the Oracle Database Net Services Administrator's Guide, 11g Release 2 (11.2) for more information.
Attention: Set the TNS_ADMIN environment variable to the directory where you created your listener.ora and tnsnames.ora files.
19.     Run adgrants.sql
Copy $APPL_TOP/admin/adgrants.sql (adgrants_nt.sql for Windows) 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 (or adgrants_nt.sql) 
    [APPS schema name]
20.     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
21.     Compile invalid objects
Use SQL*Plus to connect to the database as SYSDBA and run the $ORACLE_HOME/rdbms/admin/utlrp.sql script to compile invalid objects.
$ sqlplus "/ as sysdba" @$ORACLE_HOME/rdbms/admin/utlrp.sql
22.     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');
23.     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]
24.     Deregister the current database server (conditional)
If you plan to change the database port, host, or database name parameter on the database server, you must also update AutoConfig on the database tier and deregister the current database server node.
Use SQL*Plus to connect to the database as APPS and run the following command:
$ sqlplus apps/[APPS password]
SQL> exec fnd_conc_clone.setup_clean;
25.     Implement and run AutoConfig

Enabling AutoConfig on a New Oracle Home

In Release 12.2, AutoConfig is enabled by default on the application tier. However, it
might not be enabled on the database tier in the following scenarios:

• The database tier was not created by Rapid Install.
• Cross-platform migration has been performed on the database tier.
• The database has been upgraded to Oracle Database 11g.
• The database tier has been upgraded as part of an Oracle E-Business Suite upgrade
from Release 11i to 12.2.
To enable AutoConfig on the database tier, perform the following steps in the order
listed:

1. Copy AutoConfig to the RDBMS ORACLE_HOME
Update the RDBMS ORACLE_HOME file system by following the steps in Applying
the Latest AutoConfig Updates above.

2. Install Java Runtime Environment (JRE) on the Database tier
** copy entire appsutil directory from source to target oracle home
The JRE resides in the <ORACLE_HOME>/appsutil/jre directory on the database
tier. Ensure that the JRE version on the database tier is at least at:
Solaris: Java SE Runtime Environment (build 1.6.0_21-b06)
Linux x86-64: Java SE Runtime Environment (build 1.6.0_17-b04)
You can obtain the JRE from the official download location
[http://www.oracle.com/technetwork/java/javase/downloads/index.html].
Warning: Do not download the Java SE Development Kit (JDK).
Instead, download the JRE that supports 64-bit JVM. For additional
information on installation, see Using Latest Java 6.0 Update With
Oracle E-Business Suite Release 12 (Doc ID 455492.1), disregarding
the note about downloading 32-bit JRE.

3. Generate the Database Context File
Technical Configuration 3-29
Execute the following command to create your database context file:
$ perl <RDBMS_ORACLE_HOME>/appsutil/bin/adbldxml.pl
Important: If you run the adbldxml.pl utility for an instance that is
part of an Oracle RAC environment, all the Oracle RAC instances
must be running so that adbldxml.pl can connect to them and
gather information about the configuration.

4. Run AutoConfig on the Database tier
Run AutoConfig on the database tier by executing one of the following commands:
On UNIX:
$ <RDBMS_ORACLE_HOME>/appsutil/bin/adconfig.sh \
contextfile=<context_file>

Attention: For Windows customers, after creating the XML context file, ensure that the variable s_dlsnname has the proper listener service name.

Attention: When running AutoConfig on the Patch APPL_TOP, ignore all errors.

Attention: If the database tier node is different from the Applications tier node, after running AutoConfig on the database tier, modify tcp.invited_nodes of the generated [TNS_ADMIN]/sqlnet.ora to include the Applications tier so that the Applications tier can connect to the database.
26.     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 at least 1.5 GB of free default temporary tablespace.
27.     Create Demantra privileges (conditional)
If you are using Demantra, perform the steps in document 730883.1 on My Oracle Support.
28.     Re-create custom database links (conditional)
If the Oracle Net listener in the 11.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/[apps password]
SQL> select db_link from all_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.
29.     Restart Applications server processes
Restart all the Application tier server processes that you shut down previously. Remember that the Oracle Net listener for the database instance, as well as the database instance itself, need to be started in the 11.2 Oracle home. Users may return to the system.
30.     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:
o    Request Name = Workflow Directory Services User/Role Validation
o    Batch Size = 10000
o    Fix dangling users = Yes
o    Add missing user/role assignments = Yes
o    Update WHO columns in WF tables = No
Click "OK" and "Submit".


No comments:

Post a Comment

You cannot complete this task because one of the following events caused a loss of page data: A system failure has occurred "Front end is locked after 12.2 upgrade"

  If you have upgraded to Oracle E-Business Suite 12.2.10 Release Update Pack from Oracle E-Business Suite 12.2.6 Release Update Pack (or ea...