UPGRADE 11.1.0.7 DATABASE TO 11.2.0.3 DATABASE WITH R12.1.3
11.2.0.3 is the latest release (at the time of writing this post) for the Oracle database. The intent of this guide is to upgrade the current database version from 11.1.0.7 to 11.2.0.3.
The installation procedure for the new Oracle Database 11g Release 2 (11.2) installs the Oracle software into a new Oracle home. This is referred to as an out-of-place upgrade and is different from patch set releases for earlier releases of Oracle Database, where the patch set was always installed in place. Oracle strongly recommends that you follow the steps in this procedure to ensure minimal downtime for the upgrade process and integrity of the new binaries and software libraries.
Also, Manual upgrade method has been used instead of DBUA for better control over the complete upgrade process.
Linux x86-64 is used as platform in the below guide
SOFTWARE SOURCES
You can obtain the 11.2.0.3 software from patch 10404530 on My Oracle Support
Files 1, 2 and 3 are sufficient to cover 11.2.0.3 database (including grid infra)
Part 1: INSTALL ORACLE 11.2.0.3 SOFTWARE
1.1) Check oratab file
Ensure that the oratab file contains an entry for the database to be upgraded
1.2) Check for installed languages
SQL> select LANGUAGE_CODE,LANGUAGE_ID,NLS_LANGUAGE,INSTALLED_FLAG from fnd_languages where INSTALLED_FLAG in (‘I’,’B’);
LANG LANGUAGE_ID NLS_LANGUAGE
—- ———– —————————— -
US 0 AMERICAN B
CS 30 CZECH I
D 4 GERMAN I
1.3) Install 11g database software
cd <software location>
./runInstaller
Below are the details of OUI screen and actions.
Screen 1: Configure Security updates
Leave blank and press next
Screen 2: Download Software updates
Check ‘Skip software updates’ and press next
Screen 3: Select Installation option
Check ‘Install database software only’ and press next
Screen 4: Grid Installation options
Check ‘Single Instance database installation’ and press next
Screen 5: Select Product Languages
Choose English & all other languages required and press next
NOTE : You may select only ‘English language’ even if on EBS side you have many languages installed. The languages that we select during database install/upgrade through OUI are not related to what languages we install in EBS. If you select languages other than the English language, the Oracle installer will bring over extra *.msb files for the languages selected and the user/developer will be able to see “core” ora- messages in languages other than English too.
Screen 6: Select database edition
Check ‘EnterpriseEdition’
Screen 7: Specify Installation location
Input values of ‘Oracle base’ and press next
Screen 8: Privileged operating system groups
Take dba (or whatever group you created ) as OSDBA Group and press next
Screen 9: Perform prerequisite checks
Correct if anything is required to (by fix & check again) and press next
Screen 10: Summary
Check and press Install and press next
Screen 11a: Install
Installation will proceed.
Screen 11b: run root.sh scripts as instructed
Screen 12: Finish
This completes the database 11g installation.
Part 2: PRE-UPGRADE STEPS
2.1) Take Invalid Count
SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where STATUS=’INVALID';
2.2) Apply mandatory patches
Category a) For customers on E-Business Suite Release 12.1, apply:
Check in ad_bugs and apply whichever patch is not available. All the below patches need to be applied by adpatch
1) 9062910 (11g Release 2 interoperability patch for Release 12.1 )
2) 8919489 (12.1 TXK Delta 3 patch)
3) 9868229
4) 10163753
Category b) ) On 11.2.0.3 home, apply additional 11.2.0.3 RDBMS patches :
Apply the following patches (OPATCH) for all UNIX/Linux platforms:
NOTE: Do not run any of the post install instructions as those will be done after the upgrade.
1) 4247037
There is a Special post patch Instructions (mentioned below), execute ONLY after database upgrade part is done
Post install steps:
cd $ORACLE_HOME/md/admin
connect to the database using sqlplus / as sysdba
SQL>@catmgdidcode
2) 9858539
There is a post patch install Instructions (step 3, mentioned below), execute ONLY after database upgrade is done
Post install steps:
Run following files as sysdba
cd $ORACLE_HOME/rdbms/admin
@catnomtt.sql
@catnomta.sql
@dbmsmeta.sql
@dbmsmeti.sql
@dbmsmetu.sql
@dbmsmetb.sql
@dbmsmetd.sql
@dbmsmet2.sql
@catmeta.sql
@prvtmeta.plb
@prvtmeti.plb
@prvtmetu.plb
@prvtmetb.plb
@prvtmetd.plb
@prvtmet2.plb
@catmet2.sql– recompile dbms_metadata_int to enable the diffing code
alter package dbms_metadata_int compile plsql_ccflags = ‘ku$xml_enabled:true';
— recompile dbms_metadata_util to enable the xmlschema load code
alter package dbms_metadata_util compile plsql_ccflags = ‘ku$xml_enabled:true';
3) 12942119
4) 12960302
5) 12985184
6) 13001379
7) 13004894
8) 13258936
9) 13366268
2.3) Run utlu112i.sql on existing 11.1.0.7 oracle database
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(/tmp) on your system.
Connect via existing 11.1.0.7 database and run utlu112i.sql
Below issue were found and were corrected
ISSUE 1
———-
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
–> plsql_native_library_dir 11.2 OBSOLETE
–> plsql_native_library_subdir_ 11.2 OBSOLETE
SOLUTION 1
—————-
commented out below values in init file
#plsql_native_library_dir = /u01/oracle/<instance_name>/db/tech_st/11.1.0/plsql/nativelib
#plsql_native_library_subdir_count = 149
ISSUE 2
———-
WARNING: –> Database is using a timezone file older than version 14.
…. After the release migration, it is recommended that DBMS_DST package
…. be used to upgrade the 11.1.0.7.0 database timezone version
…. to the latest version which comes with the new release
SOLUTION 2
————–
-ignore
Upgrade to 11.2.0.3. No need to apply DST patches on the 11.1.0.6 or 11.1.0.7 side first. You can skip any DST related upgrade instructions.
The 11.2.0.3 RDBMS DST version after the upgrade to 11.2.0.3 will be the same DST version as used in 11.1.0.6 or 11.1.0.7.
ISSUE 3
———–
WARNING: –> Your recycle bin contains 99 object(s).
…. It is REQUIRED that the recycle bin is empty prior to upgrading
…. your database. The command:
PURGE DBA_RECYCLEBIN
SOLUTION 3
—————
<run PURGE DBA_RECYCLEBIN>
ISSUE 4
———-
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
SOLUTION 4
—————-
<run EXECUTE dbms_stats.gather_dictionary_stats;>
ISSUE 5
———-
Oracle recommends removing all hidden parameters prior to upgrading.
To view existing hidden parameters execute the following command
while connected AS SYSDBA:
SELECT name,description from SYS.V$PARAMETER WHERE name
LIKE ‘\_%’ ESCAPE ‘\’
Changes will need to be made in the init.ora or spfile.
SOLUTION 5
————–
Comment out all hidden variables in init file
2.4) Shut down Applications server processes and database listener
shut down all the application services running on Apps tier and also the database listener ( keep the database running)
2.5) Run dbupgdiag.sql on existing 11.1.0.7 oracle database
Run dbupgdiag.sql to find any duplicate objects in sys or system schema
Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) [ID 556610.1]
It will ask for input directory to create log file
See the instructions/warnings/errors in log file produced and correct if anything
2.6) Purge DBA_RECYCLEBIN on existing 11.1.0.7 oracle database
SQL> PURGE DBA_RECYCLEBIN;
Note: Already done as corrective action in Step 2.3
2.7) Check Hidden Parameters on existing 11.1.0.7 oracle database
SELECT name,description from SYS.V$PARAMETER WHERE name LIKE ‘\_%’ ESCAPE ‘\';
Oracle recommends removing all hidden parameters prior to upgrading.
Note: Already done as corrective action in Step 2.3
2.8) Set the new environment for 11.2.0.3 oracle home
After the software installation of Oracle 11.2.0.3 you must set the environment variables to the new home.
export ORACLE_BASE=/u01/oracle/<instance_name>/
export ORACLE_HOME=/u01/oracle/<instance_name>/db/tech_st/11.2.0
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PERL5LIB=$ORACLE_HOME/perl/lib/5.10.0:$ORACLE_HOME/perl/lib/site_perl/5.10.0
export ORA_NLS10=/u01/oracle/<instance_name>/db/tech_st/11.2.0/nls/data/9idata
Make sure that The PERL5LIB environment variable points to the directories where the new perl libraries are located (usually $ORACLE_HOME/perl/lib/[perl version] and $ORACLE_HOME/perl/lib/site_perl/[perl version])
2.9) Create nls/data/9idata directory on new 11.2.0.3 home
Run the $ORACLE_HOME/nls/data/old/cr9idata.pl script to create the $ORACLE_HOME/nls/data/9idata directory.
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.
$ perl cr9idata.pl
$ export ORA_NLS10=/u01/oracle/<instance_name>/db/tech_st/11.2.0/nls/data/9idata
2.10) Check for TIMESTAMP WITH TIMEZONE Datatype on existing 11.1.0.7 oracle database
The RDBMS DST patching has been greatly improved in 11gR2.
SQL> select TZ_VERSION from registry$database;
TZ_VERSION
———-
10
For Upgrade to 11.2.0.3, there is no need to apply DST patches on the 11.1.0.6 or 11.1.0.7 side first. You can skip any DST related upgrade instructions.
The 11.2.0.3 RDBMS DST version after the upgrade to 11.2.0.3 will be the same DST version as used in 11.1.0.6 or 11.1.0.7.
2.11) Check the National Characterset on existing 11.1.0.7 oracle database
Check that the National Characterset (NLS_NCHAR_CHARACTERSET) is UTF8 or AL16UTF16.
select value from NLS_DATABASE_PARAMETERS where parameter = ‘NLS_NCHAR_CHARACTERSET';
If this is UTF8 or AL16UTF16 then no action is needed.
SQL> select value from NLS_DATABASE_PARAMETERS where parameter = ‘NLS_NCHAR_CHARACTERSET';
VALUE
———————-
AL16UTF16
2.12) Run Optimizer Statistics on existing 11.1.0.7 oracle database
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.
$ sqlplus “/as sysdba”
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
2.13) 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;
PL/SQL procedure successfully completed.
2.14) Disable Database Vault (conditional)
If you have Database Vault installed, perform steps 1 to 6 of Part 2 of document 1091083.1 on My Oracle Support to disable Database Vault.
Check>>
SQL> SELECT * FROM V$OPTION WHERE PARAMETER = ‘Oracle Database Vault';
PARAMETER VALUE
—————————————————————-
Oracle Database Vault FALSE
If the Value is ‘FALSE’ so no action is required.
2.15) Back up Enterprise Manager Database Control Data (conditional)
You may need to backup the EM data if you have installed.
2.16) Check for any logical corruption
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.
SQL> @analyze.sql
Analyze table “SYS_TZUV2_AFFECTED_REGIONS” validate structure cascade
*
ERROR at line 1:
ORA-30657: operation not supported on external organized table
2.17) Ensure that all snapshot refreshes are successfully completed
Ensure that all snapshot refreshes are successfully completed, and that replication is stopped.
SELECT DISTINCT(TRUNC(last_refresh))FROM dba_snapshot_refresh_times;
2.18) Ensure that no files need media recovery and that no files are in backup mode
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.
2.19) Resolve outstanding distributed transactions
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;
Note: no records were returned
2.20) To check if a standby database exists, issue the following query
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.
2.21) Disable all batch and cron jobs
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
2.22) Check SYS and SYSTEM tablespace
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;
2.23) Check AUD$ table
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.
For 10.2 and later source versions there is now a pre-process script available.
Please review and refer the following note for complete information :
NOTE:1329590.1 How to Pre-Process SYS.AUD$ Records Pre-Upgrade From 10.1 or later to 11gR1 or later
2.24) Check for externally authenticated SSL users
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 few Step has to be followed after the upgrade ( refer to the documents mentioned at the end of document under ‘References’ section.
2.25) Take Backup of Configuration files
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;
2.26) Check if the database listener is stopped
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.
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.
2.27) Stop other executable such as dbconsole, isqlplus, etc.
Stop other executable such as dbconsole, isqlplus, etc.
$ emctl stop dbconsole
$ isqlplusctl stop
2.28) Shutdown the 11.1.0.7 database and take cold backup
Shutdown the database and take cold backup (more reliable)
$ sqlplus “/as sysdba”
SQL> shutdown immediate;
Back up the Database
2.29) Make a backup of the init<SID>.ora file
Make a backup of the init<SID>.ora file.
Comment out obsoleted parameters if present.
DRS_START
GC_FILES_TO_LOCKS
MAX_COMMIT_PROPAGATION_DELAY
PLSQL_NATIVE_LIBRARY_DIR
PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT
SQL_VERSION
2.30) Clean Up Duplicate Objects Owned by SYS and SYSTEM schema
Refer > Note 1030426.6 How to Clean Up Duplicate Objects Owned by SYS and SYSTEM schema
Since most data dictionary objects should be owned by SYS (see exceptions below) you will want to drop the objects that are owned by SYSTEM in order to clear up this situation.
List all objects that have been created in both the SYS and SYSTEM schema:
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';
This will give a list of duplicate objects and you will simply issue the appropriate DROP command to get rid of the object that is owned by the SYSTEM user.
EXCEPTION TO THE RULE (Important!)
Please check the above mentioned note id and do not drop few objects mentioned in it which are related to replication or else it will cause replication to fail.
Part 3: UPGRADE DATABASE TO 11.2.0.3
3.1) Copy over the modified init file
Once the parameter file is modified as per your requirement (already done above), copy the file from old 11.1.0.7 $ORACLE_HOME/dbs to the new 11.2.0.3 $ORACLE_HOME/dbs.
3.2) Check Environment Variables
Make sure the following environment variables point to the Oracle 11g Release 2 (11.2) directories:
export ORACLE_BASE=/u01/oracle/<instance_name>/
export ORACLE_HOME=/u01/oracle/<instance_name>/db/tech_st/11.2.0
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PERL5LIB=$ORACLE_HOME/perl/lib/5.10.0:$ORACLE_HOME/perl/lib/site_perl/5.10.0
export ORA_NLS10=/u01/oracle/<instance_name>/db/tech_st/11.2.0/nls/data/9idata
3.3) Update the oratab entry
Update the oratab entry to set the new ORACLE_HOME pointing to <db_name> and disable automatic startup
/etc/oratab entries
#<instance_name>:/u01/oracle/<instance_name>/db/tech_st/11.1.0:N
<instance_name>:/u01/oracle/<instance_name>/db/tech_st/11.2.0:N
Note: After /etc/oratab is updated to have SID and Oracle Home (11.2), you can execute oraenv (/usr/local/bin/oraenv) and set the environment. The input has to be the SID which is entered in /etc/oratab against the 11gR2 home.
3.4) Upgrading Database to 11gR2
At the operating system prompt, change to the $ORACLE_HOME/rdbms/admin directory of 11gR2 Oracle Home.
$ export ORACLE_SID=<instance_name>
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus “/ as sysdba”
SQL> startup UPGRADE
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.
3.5) Check the upgraded database
SQL> select banner from v$version;
BANNER
———————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
PL/SQL Release 11.2.0.3.0 – Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 – Production
NLSRTL Version 11.2.0.3.0 – Production
3.6) Run utlu112s.sql script
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
3.7) Run catuppst.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
3.8) Run dbupgdiag.sql
Check for the integrity of the upgraded database by running dbupgdiag.sql script
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 (next step)
After validating the invalid objects, re-run dbupgdiag.sql in the upgraded database once again and make sure that everything is fine.
3.9) Run utlrp.sql
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.
Note: This step will take some considerable time on first run
Keep monitoring the invalid count by
select count(*) from dba_objects where status =’INVALID';
3.10) Check components in dba_registry for status ‘VALID’
set lines 150 pages 500
column COMP_NAME format a45
column version format a15
column status format a12
SQL> select comp_name,version,status from dba_registry;
COMP_NAME VERSION STATUS
——————————————— ————— ————
OLAP Catalog 11.2.0.3.0 VALID
Oracle Data Mining 11.2.0.3.0 VALID
Oracle XML Database 11.2.0.3.0 VALID
Oracle Text 11.2.0.3.0 VALID
Spatial 11.2.0.3.0 VALID
Oracle Multimedia 11.2.0.3.0 VALID
Oracle Database Catalog Views 11.2.0.3.0 VALID
Oracle Database Packages and Types 11.2.0.3.0 VALID
JServer JAVA Virtual Machine 11.2.0.3.0 VALID
Oracle Database Java Packages 11.2.0.3.0 VALID
Oracle XDK 11.2.0.3.0 VALID
Oracle Real Application Clusters 11.2.0.3.0 INVALID
OLAP Analytic Workspace 11.2.0.3.0 VALID
Oracle OLAP API 11.2.0.3.0 VALID
14 rows selected.
PART 4: POST-UPGRADE STEPS
4.1) Perform patch post-install instructions
Run all the patch post install instructions except for those of 13001379 and 13366268. There is no need to run the post install instructions of 13001379 and 13366268 as they are run as part of the database upgrade.
4.2) Start the new database listener (conditional)
To start the new Oracle 11.2.0 Listener. First copy the old Oracle 11.1.0 $TNS_ADMIN directory to the new Oracle 11.2.0 $TNS_ADMIN. Be careful to change inside the files all the old references to the 11.1.0 directories to the new 11.2.0 directories.
lsnrctl start
4.3) Set the environment variables
copy the $ORACLE_11.1.0_HOME/SID_host.env to $ORACLE_11.2_HOME/SID_host.env, make the necessary changes to point the new 11.2.0 home and source the environment ( correct the value in .bash_profile).
4.4) 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 (or adgrants_nt.sql)
[APPS schema name]
4.5) 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
4.6) 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.7) 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]
4.8) 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.
See “Using AutoConfig to Manage System Configurations in Oracle E-Business Suite Release 12 [ID 387859.1]”
on My Oracle Support for instructions on how to implement and run AutoConfig.
Section 3.2 explains how to generate a context file on the database tier.
Step 1: Create appsutil.zip
On the application tier (as the APPLMGR user):
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
Step 2: Copy appsutil.zip to DB node and uncompress
On the database tier (as the ORACLE user):
Copy or FTP the appsutil.zip file to the <RDBMS ORACLE_HOME>
cd <RDBMS ORACLE_HOME>
unzip -o appsutil.zip
Step 3: Generate the Database Context File
Execute the following command to create your Database Context File:
perl <RDBMS_ORACLE_HOME>/appsutil/bin/adbldxml.pl
Step 4: Run AutoConfig on the Database tier
Run AutoConfig on the Database tier by executing the below command:
On Unix:
<RDBMS_ORACLE_HOME>/appsutil/bin/adconfig.sh contextfile=<context_file>
Shut down all processes, including the database and the listener, and restart them to load the new environment settings.
4.9) Apply post-upgrade ECX patch
If you are on E-Business Suite Release 12.0, apply ECX patch 9922442.
If you are on E-Business Suite Release 12.1, apply patch 9151516.
4.10) 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;
4.11) Re-create custom database links (conditional)
Create as required
4.12) Re-create grants and synonyms
You must re-create the grants and synonyms in the APPS schema. On the administration server node, as the owner of the Applications file system, run AD Administration and select the “4. Maintain Applications Database Entities menu” and then > “Recreate grants and synonyms for APPS schema” task.
4.13) Compile Invalid Objects
SQL>@utlrp.sql
SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where STATUS=’INVALID';
4.14) Restart Applications server processes
Start the application process and do a basic check of all application services from EBS side.
4.15) 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
p_BatchSize = 10000
p_Check_Dangling = Yes
Add missing user/role assignments = Yes
Update WHO columns in WF tables = No
REFERENCES
Interoperability Notes EBS R12 with Database 11gR2 [ID 1058763.1]
Oracle Database Upgrade Guide 11g Release 2 (11.2)
Complete Checklist for Manual Upgrades to 11gR2 [ID 837570.1]
Using AutoConfig to Manage System Configurations in Oracle E-Business Suite Release 12 [ID 387859.1]
Database Initialization Parameters for Oracle E-Business Suite Release 12 [ID 396009.1]
Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) [ID 556610.1]
11.2.0.3 is the latest release (at the time of writing this post) for the Oracle database. The intent of this guide is to upgrade the current database version from 11.1.0.7 to 11.2.0.3.
The installation procedure for the new Oracle Database 11g Release 2 (11.2) installs the Oracle software into a new Oracle home. This is referred to as an out-of-place upgrade and is different from patch set releases for earlier releases of Oracle Database, where the patch set was always installed in place. Oracle strongly recommends that you follow the steps in this procedure to ensure minimal downtime for the upgrade process and integrity of the new binaries and software libraries.
Also, Manual upgrade method has been used instead of DBUA for better control over the complete upgrade process.
Linux x86-64 is used as platform in the below guide
SOFTWARE SOURCES
You can obtain the 11.2.0.3 software from patch 10404530 on My Oracle Support
Files 1, 2 and 3 are sufficient to cover 11.2.0.3 database (including grid infra)
Part 1: INSTALL ORACLE 11.2.0.3 SOFTWARE
1.1) Check oratab file
Ensure that the oratab file contains an entry for the database to be upgraded
1.2) Check for installed languages
SQL> select LANGUAGE_CODE,LANGUAGE_ID,NLS_LANGUAGE,INSTALLED_FLAG from fnd_languages where INSTALLED_FLAG in (‘I’,’B’);
LANG LANGUAGE_ID NLS_LANGUAGE
—- ———– —————————— -
US 0 AMERICAN B
CS 30 CZECH I
D 4 GERMAN I
1.3) Install 11g database software
cd <software location>
./runInstaller
Below are the details of OUI screen and actions.
Screen 1: Configure Security updates
Leave blank and press next
Screen 2: Download Software updates
Check ‘Skip software updates’ and press next
Screen 3: Select Installation option
Check ‘Install database software only’ and press next
Screen 4: Grid Installation options
Check ‘Single Instance database installation’ and press next
Screen 5: Select Product Languages
Choose English & all other languages required and press next
NOTE : You may select only ‘English language’ even if on EBS side you have many languages installed. The languages that we select during database install/upgrade through OUI are not related to what languages we install in EBS. If you select languages other than the English language, the Oracle installer will bring over extra *.msb files for the languages selected and the user/developer will be able to see “core” ora- messages in languages other than English too.
Screen 6: Select database edition
Check ‘EnterpriseEdition’
Screen 7: Specify Installation location
Input values of ‘Oracle base’ and press next
Screen 8: Privileged operating system groups
Take dba (or whatever group you created ) as OSDBA Group and press next
Screen 9: Perform prerequisite checks
Correct if anything is required to (by fix & check again) and press next
Screen 10: Summary
Check and press Install and press next
Screen 11a: Install
Installation will proceed.
Screen 11b: run root.sh scripts as instructed
Screen 12: Finish
This completes the database 11g installation.
Part 2: PRE-UPGRADE STEPS
2.1) Take Invalid Count
SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where STATUS=’INVALID';
2.2) Apply mandatory patches
Category a) For customers on E-Business Suite Release 12.1, apply:
Check in ad_bugs and apply whichever patch is not available. All the below patches need to be applied by adpatch
1) 9062910 (11g Release 2 interoperability patch for Release 12.1 )
2) 8919489 (12.1 TXK Delta 3 patch)
3) 9868229
4) 10163753
Category b) ) On 11.2.0.3 home, apply additional 11.2.0.3 RDBMS patches :
Apply the following patches (OPATCH) for all UNIX/Linux platforms:
NOTE: Do not run any of the post install instructions as those will be done after the upgrade.
1) 4247037
There is a Special post patch Instructions (mentioned below), execute ONLY after database upgrade part is done
Post install steps:
cd $ORACLE_HOME/md/admin
connect to the database using sqlplus / as sysdba
SQL>@catmgdidcode
2) 9858539
There is a post patch install Instructions (step 3, mentioned below), execute ONLY after database upgrade is done
Post install steps:
Run following files as sysdba
cd $ORACLE_HOME/rdbms/admin
@catnomtt.sql
@catnomta.sql
@dbmsmeta.sql
@dbmsmeti.sql
@dbmsmetu.sql
@dbmsmetb.sql
@dbmsmetd.sql
@dbmsmet2.sql
@catmeta.sql
@prvtmeta.plb
@prvtmeti.plb
@prvtmetu.plb
@prvtmetb.plb
@prvtmetd.plb
@prvtmet2.plb
@catmet2.sql– recompile dbms_metadata_int to enable the diffing code
alter package dbms_metadata_int compile plsql_ccflags = ‘ku$xml_enabled:true';
— recompile dbms_metadata_util to enable the xmlschema load code
alter package dbms_metadata_util compile plsql_ccflags = ‘ku$xml_enabled:true';
3) 12942119
4) 12960302
5) 12985184
6) 13001379
7) 13004894
8) 13258936
9) 13366268
2.3) Run utlu112i.sql on existing 11.1.0.7 oracle database
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(/tmp) on your system.
Connect via existing 11.1.0.7 database and run utlu112i.sql
Below issue were found and were corrected
ISSUE 1
———-
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
–> plsql_native_library_dir 11.2 OBSOLETE
–> plsql_native_library_subdir_ 11.2 OBSOLETE
SOLUTION 1
—————-
commented out below values in init file
#plsql_native_library_dir = /u01/oracle/<instance_name>/db/tech_st/11.1.0/plsql/nativelib
#plsql_native_library_subdir_count = 149
ISSUE 2
———-
WARNING: –> Database is using a timezone file older than version 14.
…. After the release migration, it is recommended that DBMS_DST package
…. be used to upgrade the 11.1.0.7.0 database timezone version
…. to the latest version which comes with the new release
SOLUTION 2
————–
-ignore
Upgrade to 11.2.0.3. No need to apply DST patches on the 11.1.0.6 or 11.1.0.7 side first. You can skip any DST related upgrade instructions.
The 11.2.0.3 RDBMS DST version after the upgrade to 11.2.0.3 will be the same DST version as used in 11.1.0.6 or 11.1.0.7.
ISSUE 3
———–
WARNING: –> Your recycle bin contains 99 object(s).
…. It is REQUIRED that the recycle bin is empty prior to upgrading
…. your database. The command:
PURGE DBA_RECYCLEBIN
SOLUTION 3
—————
<run PURGE DBA_RECYCLEBIN>
ISSUE 4
———-
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
SOLUTION 4
—————-
<run EXECUTE dbms_stats.gather_dictionary_stats;>
ISSUE 5
———-
Oracle recommends removing all hidden parameters prior to upgrading.
To view existing hidden parameters execute the following command
while connected AS SYSDBA:
SELECT name,description from SYS.V$PARAMETER WHERE name
LIKE ‘\_%’ ESCAPE ‘\’
Changes will need to be made in the init.ora or spfile.
SOLUTION 5
————–
Comment out all hidden variables in init file
2.4) Shut down Applications server processes and database listener
shut down all the application services running on Apps tier and also the database listener ( keep the database running)
2.5) Run dbupgdiag.sql on existing 11.1.0.7 oracle database
Run dbupgdiag.sql to find any duplicate objects in sys or system schema
Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) [ID 556610.1]
It will ask for input directory to create log file
See the instructions/warnings/errors in log file produced and correct if anything
2.6) Purge DBA_RECYCLEBIN on existing 11.1.0.7 oracle database
SQL> PURGE DBA_RECYCLEBIN;
Note: Already done as corrective action in Step 2.3
2.7) Check Hidden Parameters on existing 11.1.0.7 oracle database
SELECT name,description from SYS.V$PARAMETER WHERE name LIKE ‘\_%’ ESCAPE ‘\';
Oracle recommends removing all hidden parameters prior to upgrading.
Note: Already done as corrective action in Step 2.3
2.8) Set the new environment for 11.2.0.3 oracle home
After the software installation of Oracle 11.2.0.3 you must set the environment variables to the new home.
export ORACLE_BASE=/u01/oracle/<instance_name>/
export ORACLE_HOME=/u01/oracle/<instance_name>/db/tech_st/11.2.0
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PERL5LIB=$ORACLE_HOME/perl/lib/5.10.0:$ORACLE_HOME/perl/lib/site_perl/5.10.0
export ORA_NLS10=/u01/oracle/<instance_name>/db/tech_st/11.2.0/nls/data/9idata
Make sure that The PERL5LIB environment variable points to the directories where the new perl libraries are located (usually $ORACLE_HOME/perl/lib/[perl version] and $ORACLE_HOME/perl/lib/site_perl/[perl version])
2.9) Create nls/data/9idata directory on new 11.2.0.3 home
Run the $ORACLE_HOME/nls/data/old/cr9idata.pl script to create the $ORACLE_HOME/nls/data/9idata directory.
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.
$ perl cr9idata.pl
$ export ORA_NLS10=/u01/oracle/<instance_name>/db/tech_st/11.2.0/nls/data/9idata
2.10) Check for TIMESTAMP WITH TIMEZONE Datatype on existing 11.1.0.7 oracle database
The RDBMS DST patching has been greatly improved in 11gR2.
SQL> select TZ_VERSION from registry$database;
TZ_VERSION
———-
10
For Upgrade to 11.2.0.3, there is no need to apply DST patches on the 11.1.0.6 or 11.1.0.7 side first. You can skip any DST related upgrade instructions.
The 11.2.0.3 RDBMS DST version after the upgrade to 11.2.0.3 will be the same DST version as used in 11.1.0.6 or 11.1.0.7.
2.11) Check the National Characterset on existing 11.1.0.7 oracle database
Check that the National Characterset (NLS_NCHAR_CHARACTERSET) is UTF8 or AL16UTF16.
select value from NLS_DATABASE_PARAMETERS where parameter = ‘NLS_NCHAR_CHARACTERSET';
If this is UTF8 or AL16UTF16 then no action is needed.
SQL> select value from NLS_DATABASE_PARAMETERS where parameter = ‘NLS_NCHAR_CHARACTERSET';
VALUE
———————-
AL16UTF16
2.12) Run Optimizer Statistics on existing 11.1.0.7 oracle database
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.
$ sqlplus “/as sysdba”
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
2.13) 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;
PL/SQL procedure successfully completed.
2.14) Disable Database Vault (conditional)
If you have Database Vault installed, perform steps 1 to 6 of Part 2 of document 1091083.1 on My Oracle Support to disable Database Vault.
Check>>
SQL> SELECT * FROM V$OPTION WHERE PARAMETER = ‘Oracle Database Vault';
PARAMETER VALUE
—————————————————————-
Oracle Database Vault FALSE
If the Value is ‘FALSE’ so no action is required.
2.15) Back up Enterprise Manager Database Control Data (conditional)
You may need to backup the EM data if you have installed.
2.16) Check for any logical corruption
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.
SQL> @analyze.sql
Analyze table “SYS_TZUV2_AFFECTED_REGIONS” validate structure cascade
*
ERROR at line 1:
ORA-30657: operation not supported on external organized table
2.17) Ensure that all snapshot refreshes are successfully completed
Ensure that all snapshot refreshes are successfully completed, and that replication is stopped.
SELECT DISTINCT(TRUNC(last_refresh))FROM dba_snapshot_refresh_times;
2.18) Ensure that no files need media recovery and that no files are in backup mode
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.
2.19) Resolve outstanding distributed transactions
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;
Note: no records were returned
2.20) To check if a standby database exists, issue the following query
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.
2.21) Disable all batch and cron jobs
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
2.22) Check SYS and SYSTEM tablespace
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;
2.23) Check AUD$ table
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.
For 10.2 and later source versions there is now a pre-process script available.
Please review and refer the following note for complete information :
NOTE:1329590.1 How to Pre-Process SYS.AUD$ Records Pre-Upgrade From 10.1 or later to 11gR1 or later
2.24) Check for externally authenticated SSL users
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 few Step has to be followed after the upgrade ( refer to the documents mentioned at the end of document under ‘References’ section.
2.25) Take Backup of Configuration files
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;
2.26) Check if the database listener is stopped
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.
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.
2.27) Stop other executable such as dbconsole, isqlplus, etc.
Stop other executable such as dbconsole, isqlplus, etc.
$ emctl stop dbconsole
$ isqlplusctl stop
2.28) Shutdown the 11.1.0.7 database and take cold backup
Shutdown the database and take cold backup (more reliable)
$ sqlplus “/as sysdba”
SQL> shutdown immediate;
Back up the Database
2.29) Make a backup of the init<SID>.ora file
Make a backup of the init<SID>.ora file.
Comment out obsoleted parameters if present.
DRS_START
GC_FILES_TO_LOCKS
MAX_COMMIT_PROPAGATION_DELAY
PLSQL_NATIVE_LIBRARY_DIR
PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT
SQL_VERSION
2.30) Clean Up Duplicate Objects Owned by SYS and SYSTEM schema
Refer > Note 1030426.6 How to Clean Up Duplicate Objects Owned by SYS and SYSTEM schema
Since most data dictionary objects should be owned by SYS (see exceptions below) you will want to drop the objects that are owned by SYSTEM in order to clear up this situation.
List all objects that have been created in both the SYS and SYSTEM schema:
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';
This will give a list of duplicate objects and you will simply issue the appropriate DROP command to get rid of the object that is owned by the SYSTEM user.
EXCEPTION TO THE RULE (Important!)
Please check the above mentioned note id and do not drop few objects mentioned in it which are related to replication or else it will cause replication to fail.
Part 3: UPGRADE DATABASE TO 11.2.0.3
3.1) Copy over the modified init file
Once the parameter file is modified as per your requirement (already done above), copy the file from old 11.1.0.7 $ORACLE_HOME/dbs to the new 11.2.0.3 $ORACLE_HOME/dbs.
3.2) Check Environment Variables
Make sure the following environment variables point to the Oracle 11g Release 2 (11.2) directories:
export ORACLE_BASE=/u01/oracle/<instance_name>/
export ORACLE_HOME=/u01/oracle/<instance_name>/db/tech_st/11.2.0
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PERL5LIB=$ORACLE_HOME/perl/lib/5.10.0:$ORACLE_HOME/perl/lib/site_perl/5.10.0
export ORA_NLS10=/u01/oracle/<instance_name>/db/tech_st/11.2.0/nls/data/9idata
3.3) Update the oratab entry
Update the oratab entry to set the new ORACLE_HOME pointing to <db_name> and disable automatic startup
/etc/oratab entries
#<instance_name>:/u01/oracle/<instance_name>/db/tech_st/11.1.0:N
<instance_name>:/u01/oracle/<instance_name>/db/tech_st/11.2.0:N
Note: After /etc/oratab is updated to have SID and Oracle Home (11.2), you can execute oraenv (/usr/local/bin/oraenv) and set the environment. The input has to be the SID which is entered in /etc/oratab against the 11gR2 home.
3.4) Upgrading Database to 11gR2
At the operating system prompt, change to the $ORACLE_HOME/rdbms/admin directory of 11gR2 Oracle Home.
$ export ORACLE_SID=<instance_name>
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus “/ as sysdba”
SQL> startup UPGRADE
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.
3.5) Check the upgraded database
SQL> select banner from v$version;
BANNER
———————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
PL/SQL Release 11.2.0.3.0 – Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 – Production
NLSRTL Version 11.2.0.3.0 – Production
3.6) Run utlu112s.sql script
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
3.7) Run catuppst.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
3.8) Run dbupgdiag.sql
Check for the integrity of the upgraded database by running dbupgdiag.sql script
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 (next step)
After validating the invalid objects, re-run dbupgdiag.sql in the upgraded database once again and make sure that everything is fine.
3.9) Run utlrp.sql
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.
Note: This step will take some considerable time on first run
Keep monitoring the invalid count by
select count(*) from dba_objects where status =’INVALID';
3.10) Check components in dba_registry for status ‘VALID’
set lines 150 pages 500
column COMP_NAME format a45
column version format a15
column status format a12
SQL> select comp_name,version,status from dba_registry;
COMP_NAME VERSION STATUS
——————————————— ————— ————
OLAP Catalog 11.2.0.3.0 VALID
Oracle Data Mining 11.2.0.3.0 VALID
Oracle XML Database 11.2.0.3.0 VALID
Oracle Text 11.2.0.3.0 VALID
Spatial 11.2.0.3.0 VALID
Oracle Multimedia 11.2.0.3.0 VALID
Oracle Database Catalog Views 11.2.0.3.0 VALID
Oracle Database Packages and Types 11.2.0.3.0 VALID
JServer JAVA Virtual Machine 11.2.0.3.0 VALID
Oracle Database Java Packages 11.2.0.3.0 VALID
Oracle XDK 11.2.0.3.0 VALID
Oracle Real Application Clusters 11.2.0.3.0 INVALID
OLAP Analytic Workspace 11.2.0.3.0 VALID
Oracle OLAP API 11.2.0.3.0 VALID
14 rows selected.
PART 4: POST-UPGRADE STEPS
4.1) Perform patch post-install instructions
Run all the patch post install instructions except for those of 13001379 and 13366268. There is no need to run the post install instructions of 13001379 and 13366268 as they are run as part of the database upgrade.
4.2) Start the new database listener (conditional)
To start the new Oracle 11.2.0 Listener. First copy the old Oracle 11.1.0 $TNS_ADMIN directory to the new Oracle 11.2.0 $TNS_ADMIN. Be careful to change inside the files all the old references to the 11.1.0 directories to the new 11.2.0 directories.
lsnrctl start
4.3) Set the environment variables
copy the $ORACLE_11.1.0_HOME/SID_host.env to $ORACLE_11.2_HOME/SID_host.env, make the necessary changes to point the new 11.2.0 home and source the environment ( correct the value in .bash_profile).
4.4) 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 (or adgrants_nt.sql)
[APPS schema name]
4.5) 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
4.6) 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.7) 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]
4.8) 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.
See “Using AutoConfig to Manage System Configurations in Oracle E-Business Suite Release 12 [ID 387859.1]”
on My Oracle Support for instructions on how to implement and run AutoConfig.
Section 3.2 explains how to generate a context file on the database tier.
Step 1: Create appsutil.zip
On the application tier (as the APPLMGR user):
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
Step 2: Copy appsutil.zip to DB node and uncompress
On the database tier (as the ORACLE user):
Copy or FTP the appsutil.zip file to the <RDBMS ORACLE_HOME>
cd <RDBMS ORACLE_HOME>
unzip -o appsutil.zip
Step 3: Generate the Database Context File
Execute the following command to create your Database Context File:
perl <RDBMS_ORACLE_HOME>/appsutil/bin/adbldxml.pl
Step 4: Run AutoConfig on the Database tier
Run AutoConfig on the Database tier by executing the below command:
On Unix:
<RDBMS_ORACLE_HOME>/appsutil/bin/adconfig.sh contextfile=<context_file>
Shut down all processes, including the database and the listener, and restart them to load the new environment settings.
4.9) Apply post-upgrade ECX patch
If you are on E-Business Suite Release 12.0, apply ECX patch 9922442.
If you are on E-Business Suite Release 12.1, apply patch 9151516.
4.10) 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;
4.11) Re-create custom database links (conditional)
Create as required
4.12) Re-create grants and synonyms
You must re-create the grants and synonyms in the APPS schema. On the administration server node, as the owner of the Applications file system, run AD Administration and select the “4. Maintain Applications Database Entities menu” and then > “Recreate grants and synonyms for APPS schema” task.
4.13) Compile Invalid Objects
SQL>@utlrp.sql
SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where STATUS=’INVALID';
4.14) Restart Applications server processes
Start the application process and do a basic check of all application services from EBS side.
4.15) 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
p_BatchSize = 10000
p_Check_Dangling = Yes
Add missing user/role assignments = Yes
Update WHO columns in WF tables = No
REFERENCES
Interoperability Notes EBS R12 with Database 11gR2 [ID 1058763.1]
Oracle Database Upgrade Guide 11g Release 2 (11.2)
Complete Checklist for Manual Upgrades to 11gR2 [ID 837570.1]
Using AutoConfig to Manage System Configurations in Oracle E-Business Suite Release 12 [ID 387859.1]
Database Initialization Parameters for Oracle E-Business Suite Release 12 [ID 396009.1]
Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) [ID 556610.1]
No comments:
Post a Comment