Up-gradation of Oracle Database from 10gR2 to 11gR2 in Oracle EBS R12.

Overview

This document highly design for the Up-gradation of database from 10.2.0.4 to 11gR2 in Oracle EBS R12 with all information required for up-grade the database.

 Requirements & recommendations:

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 the duplicate objects in the SYS and SYSTEM schema.

        

To Find Duplicate Objects:

column object_name format a30

select object_name, object_type from dba_objects 

where object_name||object_type in 

(select object_name||object_type from dba_objects 

where owner = 'SYS') and owner = 'SYSTEM'; 

              

To Drop duplicate objects:

set pause off 

set heading off 

set pagesize 0 

set feedback off 

set verify off 

spool dropsys.sql 

select 'DROP ' || object_type || ' SYSTEM.' || object_name ||';' 

from dba_objects where object_name||object_type in 

(select object_name||object_type from dba_objects where owner = 'SYS')and owner = 'SYSTEM'; 

spool off 

exit 

3. Check the certification of the Oracle 11g R2 with your Platform /Operating system before downloading and installing the Oracle 11g R2 .Please check the certification information  on the My Oracle Support.

4. Download and Install Oracle 11g Release 2 in a new Oracle Home and make sure there are no relinking errors. 

5. Install the latest available Patchset from Metalink. (If available). 

6. Install the latest opatch available for your platform and database version  ( If available).

7. Install the latest available Critical Patch Update. (If available).

8. Either take a Cold or Hot backup of your source database (advisable to have cold backup). 

9. If you have XDB installed then  please install the PSE for 10368698 to the 11.2.0.2 Home before doing the upgrade . If there is not an existing One off for your platform please open an SR to request the one off patch .This defect can cause certain databases that are XDB enabled to take too much time to upgrade as opposed to hours.

 Pre-Upgrade Steps:

1. Documents Referred : 

Interoperability Notes Oracle EBS 11i with Oracle Database 11gR2 (11.2.0) [881505.1] complete Checklist for Manual Upgrades to 11gR2 [ID 837570.1].

2. Download the 11gR2 software along with the examples CD:

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-linx8664soft-100572.html

3. Minimum Requirement from EBS for 11gR2 DB:

EBS Version  11.5.10 CU2                                            :   Meets Requirement

11gR2 Interop patch for 11.5.10 Patch 8815204            :  Need to apply

Patch 5644137                                                               :  Need to apply

ATG RUP 6 (5903765)                                                  :  Already at ATG RUP 7 (6241631)

4. Install the 11gR2 oracle software only.

               > Backup the old Inventory 

               > Create new Oracle_Home for 11gR2

               > export ORACLE_SID=OEBUAT (in VNC)

               > export ORACLE_HOME=<path of the 11gR2 home>

               > Check if display has been set (xclock)

               > ./runInstaller

5. Apply additional RDBMS patch on 11gR2 (11.2.0.3):

9858539, 12942119, 12960302, 12985184, 13001379, 13004894, 13258936, 13366268

6. Copy the Pre-Upgrade Information Tool for Collecting Pre-Upgrade Information to a temporary location.

• Log in to 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)  and utltzuv2.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.

cp $ORACLE_HOME/rdbms/admin/utlu112i.sql   /tmp

7. Go to the directory where utlu112i.sql  had been copied in the previous step.

8. 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 (old version of database).

$ sqlplus '/ as sysdba' 

SQL> spool upgrade_info.log              

SQL> @/tmp/utlu112i.sql

SQL> spool off

9. Reviews the above spool generated above and takes corrective actions accordingly.

 Preparing Database for Upgrade:

1. Check for the integrity of the source database prior to starting the upgrade by downloading and running dbupgdiag.sql script from note 556610.1

cd <location of the script> 

$ sqlplus / as sysdba

sql> alter session set nls_language='American';

sql> @dbupgdiag.sql

sql> exit

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

SQL>select count(*) from dba_objects where status='INVALID';

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

4. Identify which users and roles in your database are granted the CONNECT role

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');

5. Create Script for DBLINK (In case the database has to be downgraded again)

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

6. Check for TIMESTAMP WITH TIMEZONE Datatype. The time zone files that are supplied with Oracle Database 11g Release 2 (11.2) have been updated to version 11  to reflect changes in transition rules for some time-zone regions.

           Check Hazel/PATTY upgrade (11.2.0.2) document and apply patches (7695070,   8524113) 

Case 1:

If  the source database is using a timezone file older than version 11 then the utlu112i.sql (Pre-Upgrade Information Tool) will generate the following warning

Example :

WARNING: -->Database is using a timezone file older than version 11.            

.... After the release migration, it is suggested that DBMS_DST package         

.... be used to upgrade the 11.1.0.6.0 database timezone version                

.... to the latest version which comes with the new release.

Case 2:

If the source database  is using a timezone file greater than version 11 then utlu112i.sql (Pre-Upgrade Information Tool) will generate the following warning:

Example :

WARNING: --> Database is using a timezone file greater than version 11.

.... BEFORE upgrading the database, patch the 11gR2

.... $ORACLE_HOME/oracore/zoneinfo/ with a timezone data file of the

.... same version as the one used in the 11.1.0.6.0 release database.

7. Check that National Characterset (NLS_NCHAR_CHARACTERSET) is UTF8 and AL16UTF16.

SQL> select value from NLS_DATABASE_PARAMETERS where parameter = 'NLS_NCHAR_CHARACTERSET';

8. When upgrading to Oracle Database 11g Release 2 (11.2), optimizer statistics are collected for dictionary tables that lack statistics. To determine the schemas which lacks statistics, either review the output of the utlu112i.sql script or download and run the script from Note 560336.1 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 script from the metalink note: 

$ sqlplus / as sysdba

SQL> @check_schema_stale_stats.sql

For database above 10.1

$ sqlplus "/as sysdba"

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

9. Backing up Enterprise Manager Database Control Data:

10. Configuring Network ACL's:

http://oracleflash.com/33/Oracle-11g-Release-2-Pre-Upgrade-tool-utlu112i-sql.html

11. This optional check is introduced to spot any logical corruption in underlying object and its dependent:

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

12. Execute the analyze.sql created above:

$ sqlplus "/ as sysdba"

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

SQL> @analyze.sql

13. Ensure that all snapshot refreshes are successfully completed, and that replication is stopped:

SELECT DISTINCT(TRUNC(last_refresh)) FROM dba_snapshot_refresh_times;

14. Ensure that no files need media recovery:

SELECT * FROM v$recover_file;

15. Ensure that no files are  in backup mode:

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

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;

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%';

18. Disable all batch and cron jobs:

19. Ensure the users sys and system have 'system' as their default tablespace:

SQL> SELECT username, default_tablespaceFROM 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;

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$';

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

22. Note down the location of datafiles, redo logs, 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;

23. Stop the listener for the database:

24. Shutdown the database

25. Take cold backup of the database

26. Make a backup of the init<SID>.ora file.

27. Comment out obsoleted parameters( Appendix A ) and change all deprecated parameters( Appendix B ).

28. If you are upgrading from 10.1.0.x or 10.2.0.x then you can leave the COMPATIBLE parameter set to it's current value until the upgrade has been completed successfully:

29. Put the database in noarchivelog mode  during upgrade.

30. Make sure the following environment variables point to the Oracle 11g Release directories:

- ORACLE_BASE

- ORACLE_HOME

- PATH

$ export ORACLE_HOME=<location of Oracle 11.2>

$ export PATH=$ORACLE_HOME/bin:$PATH

$ export ORACLE_BASE=<Oracle_Base set during installation>

31. Update the oratab entry, to set the new ORACLE_HOME pointing towards ORCL and disable automatic startup:

Sample /etc/oratab

#orcl:/opt/oracle/product/10.2/db_1:N

orcl:/opt/oracle/product/11.2/db_1:N

32. Upgrading database to 11gR2:

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

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

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

$ sqlplus "/as sysdba"

SQL>SHUT IMMEDIATE

SQL> STARTUP

SQL> @utlu112s.sql

35. 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. Run utlrp.sql  concurrently to recompile invalids in another session.

SQL> @catuppst.sql

SQL> @utlrp.sql

36. Check for the integrity of the upgraded database by running dbupgdiag.sql script from Note 556610.1

cd <location of the script> 

$ sqlplus / as sysdba

sql> alter session set nls_language='American';

sql> @dbupgdiag.sql

sql> exit

 Post Upgrade Steps:

1. Modify the listener.ora file to point to the new ORACLE_HOME and start the listener:

lsnrctl start

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

ORACLE_BASE

ORACLE_HOME

PATH

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

4. Edit init.ora:

 If you changed the CLUSTER_DATABASE parameter prior the upgrade set it back to TRUE

 Create a server parameter file with a initialization parameter file

SQL> create spfile from pfile;

5. Change Passwords for Oracle-Supplied Accounts

Oracle recommends that you lock all Oracle supplied accounts except for SYS and SYSTEM, and expire their passwords, thus requiring new passwords to be specified when the accounts are unlocked.

SQL> SELECT username, account_status FROM dba_users ORDER BY username;

SQL> ALTER USER username PASSWORD EXPIRE ACCOUNT LOCK;

6. Upgrading Oracle Text

Copy the following files from the previous Oracle home to the new Oracle home:

* Stemming user-dictionary files

* User-modified KOREAN_MORPH_LEXER dictionary files

* USER_FILTER executable

To obtain a list of the use:

$ORACLE_HOME/ctx/admin/ctxf<version>.txt

$ORACLE_HOME/ctx/admin/ctxf<version>.sql

where version is 920,101,102

7. Configure Enterprise Manager

If your database is being managed by Oracle Enterprise Manager Database Control or Oracle Enterprise Manager Grid Control, then use the following command to update the configuration:

emca -upgrade (db | asm | db_asm) [-cluster] [-silent] [parameters]

8. To get a list of all deprecated initialization parameters

SELECT name FROM v$parameter WHERE isdeprecated = 'TRUE';

9. Create a soft link for timezone_11.dat and timezlrg_11.dat

usmtntsderpdb03:<11.2.0_home> cd oracore/zoneinfo

ln -s timezlrg_11.dat timezlrg.dat

ln -s timezone_11.dat timezone.dat

10. Create the appsutil.zip by running admkappsutil.pl

11. Copy the appsutil.zip to $ORACLE_HOME and unzip it

12. Copy xml file from the old oracle_home/appsutil to new one and edit the paths in it to point to 11.2.0

13. Run autoconfig on db tier

ISSUE: UnsatisfiedLinkError exception loading native library: njni11

java.lang.UnsatisfiedLinkError: ORACLE_HOME/lib/libnjni11.so: ld.so.1: java: fatal: ORACLE_HOME/lib/libnjni11.so: wrong ELF class: ELFCLASS64 (Possible cause: architecture word width mismatch)

14. Run adgrants.sql 

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

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

15. Grant create procedure privilege on CTXSYS: 

Copy $AD_TOP/patch/115/sql/adctxprv.sql from the administration server node to the database server node.

sqlplus apps/[APPS password]

@adctxprv.sql <system pwd> CTXSYS

16. Set CTXSYS parameter 

$ sqlplus "/ as sysdba"

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

17. 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> shutdown normal;

SQL> startup restrict;

SQL> @$APPL_TOP/admin/adstats.sql

SQL> shutdown normal;

SQL> startup; 

SQL> exit;