Database upgrade from 11.2.0.1 to 11.2.0.3
Referenced documents:
Interoperability
Notes Oracle EBS 11i with Oracle Database 11gR2 (11.2.0) [ID 881505.1]
Complete
checklist for out-of-place manual upgrade from previous 11.2.0.N version to the
latest 11.2.0.N patchset. [ID 1276368.1]
Install software
cd /nfs/db_11.2.0.3_patches/database
start the installer in vnc session
./runInstaller
Uncheck
I
wish to receive security updates via My Oracle Support.
Click Next
Click on Yes
Select
Skip
software updates
Click Next
Select
Install
database software only
Click next
Change
Oracle Base: /d02/oracle/<sid>db (eg:/d02/oracle/<sid>db)
Software Location: /d02/oracle/<sid>db/11.2.0.3 (eg:/d02/oracle/svrdb/11.2.0.3)
Select
Ignore
all
Click on Install
cd /d02/oracle/<sid>db/11.2.0.3
[root@svrtstebsdl1 11.2.0.3]# ./root.sh
Performing root user operation
for Oracle 11g
The following environment
variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /d02/oracle/<sid>db/11.2.0.3
Enter the full pathname of the
local bin directory: [/usr/local/bin]:
The contents of
"dbhome" have not changed. No need to overwrite.
The file "oraenv"
already exists in /usr/local/bin.
Overwrite it? (y/n)
[n]:y
The file "coraenv"
already exists in /usr/local/bin.
Overwrite it? (y/n)
[n]: y
Copying coraenv to /usr/local/bin ...
Entries will be added to the
/etc/oratab file as needed by
Database Configuration Assistant
when a database is created
Finished running generic part of
root script.
Now product-specific root
actions will be performed.
Finished product-specific root
actions.
Install software from
examples CD
cd /nfs/db_11.2.0.3_patches/examples
./runInstaller
Select
Skip
software updates
Select
Software
location : /d02/oracle/<sid>db/11.2.0.3 (eg:/d02/oracle/svrdb/11.2.0.3)
Verify the software
installation
Cd /d02/oracle/<sid>db/11.2.0.3/OPatch
opatch lsinventory -OH /d02/oracle/<sid>db/11.2.0.3
output:
---------
Invoking OPatch 11.2.0.1.7
Oracle Interim Patch Installer
version 11.2.0.1.7
Copyright (c) 2011, Oracle
Corporation. All rights reserved.
Oracle Home : /d02/oracle/<sid>db/11.2.0.3
Central Inventory :
/home/oracle/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.2.0.1.7
OUI version : 11.2.0.1.0
Log file location : /d02/oracle/<sid>db/11.2.0.3/cfgtoollogs/opatch/opatch2012-02-16_16-12-37PM.log
Lsinventory Output file location
: /d02/oracle/<sid>db/11.2.0.3/cfgtoollogs/opatch/lsinv/lsinventory2012-02-16_16-12-37PM.txt
--------------------------------------------
Installed Top-level Products
(2):
Oracle Database 11g
11.2.0.3.0
Oracle Database 11g Examples 11.2.0.3.0
There are 2 products installed
in this Oracle Home.
There are no Interim patches
installed in this Oracle Home.
-------------------------------------------
OPatch succeeded.
Post Install steps
After the installation, make sure
that:
- The ORACLE_HOME environment variable points to the new
11.2.0 Oracle home.
- The PATH environment variable includes $ORACLE_HOME/bin
and the directory where the new perl executable is located (usually
$ORACLE_HOME/perl/bin).
- The LD_LIBRARY_PATH environment variable includes
$ORACLE_HOME/lib.
- 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])
export ORACLE_HOME=/d02/oracle/<sid>db/11.2.0.3
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH
export PERL5LIB=$ORACLE_HOME/perl/lib/5.10.0:$ORACLE_HOME/perl/lib/site_perl/5.10.0
Create nls/data/9idata directory
On the database server node, as the owner of the Oracle RDBMS file system and
database instance, run the $ORACLE_HOME/nls/data/old/cr9idata.pl script to
create the $ORACLE_HOME/nls/data/9idata directory.
cd $ORACLE_HOME/nls/data/old
perl cr9idata.pl
output:
Creating directory /d02/oracle/<sid>db/11.2.0.3/nls/data/9idata
...
Copying files to /d02/oracle/<sid>db/11.2.0.3/nls/data/9idata...
Copy finished.
Please reset environment variable ORA_NLS10 to /d02/oracle/<sid>db/11.2.0.3/nls/data/9idata!
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.
export ORA_NLS10=/d02/oracle/<sid>db/11.2.0.3/nls/data/9idata
Apply patches
Apply rdbms patch 12320006 to update timezone files to
version 16
export ORACLE_HOME=/d02/oracle/<sid>db/11.2.0.3
cd /nfs/db_11.2.0.3_patches/12320006
$ORACLE_HOME/OPatch/opatch lsinventory
$ORACLE_HOME/OPatch/opatch apply
export ORACLE_HOME=/d02/oracle/<sid>db/11.2.0.3
cd /nfs/db_11.2.0.3_patches/
9858539
$ORACLE_HOME/OPatch/opatch lsinventory
$ORACLE_HOME/OPatch/opatch apply
export ORACLE_HOME=/d02/oracle/<sid>db/11.2.0.3
$ORACLE_HOME/OPatch/opatch lsinventory
$ORACLE_HOME/OPatch/opatch apply
export ORACLE_HOME=/d02/oracle/<sid>db/11.2.0.3
$ORACLE_HOME/OPatch/opatch lsinventory
$ORACLE_HOME/OPatch/opatch apply
export ORACLE_HOME=/d02/oracle/<sid>db/11.2.0.3
$ORACLE_HOME/OPatch/opatch lsinventory
$ORACLE_HOME/OPatch/opatch apply
$ORACLE_HOME/OPatch/opatch lsinventory
Pre-Upgrade steps
Run Pre-Upgrade check script
cd /d02/oracle/<sid>db/11.2.0.3/rdbms/admin
sqlplus / as sysdba
SQL> spool /nfs/db_11.2.0.3_patches/logs/<SID>/before_upgrade_utlu112i.log
SQL> @utlu112i.sql
SQL> spool off
Review the log file before_upgrade_utlu112i.log
Complete the following steps as per the before_upgrade_utlu112i.log file,
Get DBA_REGISTRY information
sqlplus '/ as sysdba'
SQL> select COMP_ID,COMP_NAME,VERSION,STATUS,MODIFIED from dba_registry;
Update RDBMS time zone
Check the current version of the 11.2.0.1 Oracle RDBMS
time zone definitions
sqlplus / as sysdba
SQL> SELECT version FROM
v$timezone_file;
Output:
VERSION
----------
16
Apply rdbms patch 12320006 to update timezone files to
version 16 as above
Emtry the recycle bin
SQL>
spool purge_recyclebin.log
SQL> PURGE DBA_RECYCLEBIN;
Output:
DBA Recyclebin
purged.
SQL> spool off
Gather dictionary statistics execute
the following command
while connected as SYSDBA:
SQL>
spool gather_dictionary_stats.log
SQL> EXECUTE
dbms_stats.gather_dictionary_stats;
Output:
PL/SQL
procedure successfully completed. ( took 1 hour 30 minutes)
SQL> spool off
Oracle recommends removing all hidden parameters prior to
upgrading.
Remove hidden
parameters
To view existing hidden parameters execute the following
command
while connected AS SYSDBA:
SQL> spool /nfs/db_11.2.0.3_patches/logs/<SID>/hidden_parameters.log
SQL> SELECT
name,description from SYS.V$PARAMETER WHERE name
LIKE '\_%'
ESCAPE '\' ;
SQL> spool off
SQL>
Create pfile from spfile and commend out all the lines
starting with _
Shutdown the database
Create spfile from pfile
Startup the database
Run the sql above and make sure it returns no rows.
Review events
Oracle recommends reviewing any defined events prior to
upgrading.
To view existing non-default events execute the following
commands
while connected AS SYSDBA:
Events:
SQL> spool
events.log
SQL> SELECT
(translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2
WHERE UPPER(name) ='EVENT' AND isdefault='FALSE' ;
Output:
no
rows selected
SQL> spool
off
Trace Events:
SQL> spool
trace_events.log
SQL> SELECT (translate(value,chr(13)||chr(10),' '))
from sys.v$parameter2
WHERE
UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE' ;
Output:
no
rows selected
SQL> spool
off
Lock data mining user DMSYS
Sqlplus / as sysdba
SQL> ALTER USER DMSYS ACCOUNT LOCK;
Run dbupgdiag
cd /nfs/db_11.2.0.3_patches
sqlplus / as sysdba
SQL> @dbupgdiag.sql
Enter location for Spooled output:
Enter value for 1: /nfs/db_11.2.0.3_patches/logs/<SID>
Please review the generated log file (eg:db_upg_diag_SVRTEST_16_Feb_2012_0742.log)
Set environment
variables to 11.2.0.3 oracle home
export ORACLE_BASE=/d02/oracle/<sid>db
export ORACLE_HOME=/d02/oracle/<sid>db/11.2.0.3
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH
export PERL5LIB=$ORACLE_HOME/perl/lib/5.10.0:$ORACLE_HOME/perl/lib/site_perl/5.10.0
export
ORA_NLS10=/d02/oracle/<sid>db/11.2.0.3/nls/data/9idata
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/X11R6/lib:/usr/openwin/lib:$ORACLE_HOME/ctx/lib
Start database upgrade
assistant
Enable autoextend to SYSAUX
tablespace
sqlplus / as sysdba
SQL> Alter database datafile '/u03/oradata/<SID>/sysaux01.dbf'
AUTOEXTEND ON NEXT 100M MAXSIZE 32000M;
Click on Ignore
Post-Upgrade steps
Run Post-Upgrade check script
cd /d02/oracle/<sid>db/11.2.0.3/rdbms/admin
sqlplus / as sysdba
SQL> spool /nfs/db_11.2.0.3_patches/logs/<SID>/after_upgrade_utlu112i.log
SQL> @utlu112i.sql
SQL> spool off
Run dbupgdiag
cd /nfs/db_11.2.0.3_patches
sqlplus / as sysdba
SQL> @dbupgdiag.sql
Enter location for Spooled output:
Enter value for 1: /nfs/db_11.2.0.3_patches/logs/<SID>
Please review the generated log file (eg:db_upg_diag_SVRTEST_16_Feb_2012_0742.log)
Copy network configuration files (listener.ora,
sqlnet.ora, tnsnames.ora, etc) from previous home
rsync -azv /d02/oracle/<sid>db/11.2.0/network/admin/$CONTEXT_NAME
/d02/oracle/<sid>db/11.2.0.3/network/admin/
edit all the files
and change all the 11.2.0 entries to 11.2.0.3
export TNS_ADMIN=/d02/oracle/<sid>db/11.2.0.3/network/admin/$CONTEXT_NAME
Start the listener
lsnrctl start <SID>
Run adgrants.sql
Cd $ORACLE_HOME
mkdir appsutil
cd appsutil/
mkdir admin
cd admin
Copy $APPL_TOP/admin/adgrants.sql from the administration server node (eg:svrtestebsal1 )to the database server node.
scp svrtestebsal1:/d01/oracle/<sid>appl/admin/adgrants.sql
.
Use SQL*Plus to connect to the database as SYSDBA and run
the script using the following command:
sqlplus / as
sysdba
SQL> spool adgrants.log
SQL>
@adgrants.sql APPLSYS
SQL> spool off
Grant create procedure privilege on CTXSYS
Copy $AD_TOP/patch/115/sql/adctxprv.sql from the administration server node to
the database server node.
scp svrtestebsal1:/d01/oracle/<sid>appl/ad/11.5.0/patch/115/sql/adctxprv.sql
.
Use SQL*Plus to connect to the database as APPS and run
the script using the following command:
sqlplus apps/apps
SQL> spool adctxprv.log
SQL> @adctxprv.sql system CTXSYS
SQL> spool off
Set CTXSYS parameter
Use SQL*Plus to connect to the database as SYSDBA and run the following
command:
cd /nfs/db_11.2.0.3_patches/logs/<SID>/
sqlplus / as
sysdba
SQL> spool ctx.log
SQL>
exec ctxsys.ctx_adm.set_parameter('file_access_role', 'public');
Output:
PL/SQL procedure successfully completed.
SQL> spool off
Deregister the current database
server
Use SQL*Plus to connect to the database as APPS and run
the following command:
sqlplus apps/apps
SQL> exec fnd_conc_clone.setup_clean;
Make sure the following two sqls return no rows
SQL> select node_name "Node Name", node_mode
"Mode", support_cp "C",support_web "W",
support_admin "A", support_forms "F" from FND_NODES;
SQL> select *
from FND_OAM_CONTEXT_FILES;
Implement and run AutoConfig
rsync -azv /d02/oracle/<sid>db/11.2.0/appsutil /d02/oracle/<sid>db/11.2.0.3/
edit context file /d02/oracle/<sid>db/11.2.0.3/appsutil
/ $CONTEXT_NAME.xml and change all the 11.2.0 entries to 11.2.0.3
and modify the following also,
<PERL5LIB oa_var="s_perl5lib"
osd="unix">/d02/oracle/<sid>db/11.2.0.3/perl/lib/5.10.0:/d02/oracle/<sid>db/11.2.0.3/perl/lib/site_perl/5.10.0
:/d02/oracle/<sid>db/11.2.0.3/appsutil/perl </PERL5LIB>
<ADPERLPRG oa_var="s_adperlprg"
osd="unix">/d02/oracle/<sid>db/11.2.0.3/perl/bin/perl</ADPERLPRG>
cd $ORACLE_HOME/dbs
mv init<SID>.ora init<SID>.ora_after_ugrade
cd $ORACLE_HOME/appsutil/bin
./adconfig.sh
Enter the full path to the Context file: /d02/oracle/<sid>db/11.2.0.3/appsutil/$CONTEXT_NAME.xml
Enter the APPS user password: <apps password>
Modify the
database parameters to add back the hidden parameters
cd $ORACLE_HOME/dbs
Edit init<SID>.ora file and to the following,
·
Comment out the entry for _index_join_enabled as
below
##_index_join_enabled =
FALSE # Required 11i setting
·
Make sure it has the following hidden parameters
_b_tree_bitmap_plans =
FALSE # Required 11i setting
_fast_full_scan_enabled = FALSE
_like_with_bind_as_equality = TRUE
_optimizer_autostats_job=false
# Turning off auto statistics
_sort_elimination_cost_ratio=5
_system_trig_enabled = true
_trace_files_public = TRUE
make sure ifile (SVRPROD_svrprodebsdl1_ifile.ora) has the following entries(can copy from
previous $ORACLE_HOME/dbs),
compatible = 11.2.0
undo_tablespace=APPS_UNDOTS3
sga_target=10G
sga_max_size=10G
fast_start_parallel_rollback=FALSE
log_checkpoint_interval=999999999
filesystemio_options=SETALL
open_cursors=4000
undo_retention=10800
shared_pool_size=4g
shared_pool_reserved_size=80M
pga_aggregate_target=10G
log_archive_dest_1="LOCATION=/archivelog/SVRPROD"
log_archive_format='SVRPROD_%t_%s_%r.arc'
NOTE : the
values of sga_target and sga_max_size should be half the size of the db node
memory
mv spfile<SID>.ora spfile<SID>.ora_before_hidden_params
Shutdown the database
Sqlplus / as sysdba
SQL> shutdown immediate
SQL> create spfile from pfile;
SQL> startup
Make sure the database is using correct spfile,
SQL> show parameter spfile
NAME TYPE VALUE
----------- ----------
--------------------
spfile sring /d02/oracle/<sid>db/11.2.0.3/dbs/spfile<SID>.ora
Gather statistics for SYS schema
Copy $APPL_TOP/admin/adstats.sql
from the administration server node to the database server node.
cd $ORACLE_HOME/appsutil/admin
scp svrtestebsal1:/d01/oracle/<sid>appl/admin/adstats.sql
.
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 restart the database in restricted mode, run
adstats.sql, and restart the database in normal mode:
sqlplus
/ as sysdba
SQL>
shutdown immediate;
SQL>
startup;
SQL>
shutdown normal;
SQL>
startup restrict;
SQL>
spool adstats.log
SQL> select systimestamp
from dual;
SQL>
@adstats.sql (30 minutes)
SQL> select systimestamp
from dual;
SQL>
spool off
SQL>
shutdown normal;
SQL>
startup;
SQL>
exit;
Run autoconfig on
apps node
cd $COMMON_TOP/admin/scripts
adautocfg.sh
Re-create grants and synonyms
Oracle Database 11g Release 2 (11.2) contains new functionality for grants and
synonyms compared to previous database releases. As a result, 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 "Re-create grants and synonyms for APPS
schema(2)" task from the Maintain Applications Database Entities menu(4).
SQL> select count(1) from dba_objects where
status='INVALID';
COUNT(1)
----------
96
SQL> select owner,object_type,count(*) from
dba_objects where status='INVALID' group by owner,object_type;
OWNER OBJECT_TYPE COUNT(*)
--------------- -------------------
----------
APPS PACKAGE BODY 2
APPS JAVA CLASS 40
TWE_ORA_ADAPTER JAVA CLASS 6
APPS_RO SYNONYM 20
AM_AGENT FUNCTION 9
APPS SYNONYM 10
APPS VIEW 9
Run utlrp
Cd $ORACLE_HOME/rdbms/admin
Sqlplus / as sysdba
SQL>@utlrp
SQL> select count(1) from dba_objects where
status='INVALID';
COUNT(1)
----------
17
SQL> select owner,object_type,count(*) from
dba_objects where status='INVALID' group by owner,object_type;
OWNER OBJECT_TYPE COUNT(*)
----------- -------------------
----------
APPS PACKAGE BODY 1
APPS JAVA CLASS 9
TWE_ORA_ADAPTER JAVA CLASS 6
AM_AGENT FUNCTION 1
Upgrade statistics
table FND_STATTAB
Sqlplus / as sysdba
EXECUTE
DBMS_STATS.UPGRADE_STAT_TABLE('APPLSYS','FND_STATTAB');
Start all the
processes on application node
Cd $COMMON_TOP/admin/scripts/$CONTEXT_NAME
adstrtal.sh apps/apps
+++++++++++++++++++++++++++++
Document References
When upgrading from 10.1.0.x , 10.2.0.x or 11.1.0.x to 11.2.0.3: (1358166.1)
* For 10.2.0.2 , 10.2.0.3 , 10.2.0.4 or 10.2.0.5 there is no need to apply any patchset before upgrading to 11.2.0.3
Upgrade to 11.2.0.3.No need to apply DST patches on the 10.2.0.2 , 10.2.0.3 , 10.2.0.4 or 10.2.0.5 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 10.2.0.2 , 10.2.0.3 , 10.2.0.4 or 10.2.0.5.
* (recommended) update the 11.2.0.3 database(s) to DSTv14 (standard DST version of 11.2.0.3) by following Note 977512.1 Updating the RDBMS DST version in 11gR2 (11.2.0.1 and up) using DBMS_DST from step 3a) using "14" as (<the new DST version number>) in that note. When going to DSTv14 there is no need to apply any DST patch to the 11.2.0.3 home.
1. 11.2.0.3 Patch Set - Availability and Known Issues [ID 1348336.1]
2. ALERT: Oracle 11g Release 2 (11.2) Support Status and Alerts [ID 880782.1]
3. http://docs.oracle.com/cd/E11882_01/server.112/e23633.pdf -- Upgrade guide
4. http://docs.oracle.com/cd/E11882_01/install.112/e24326.pdf - Quick Installation Guide
5. Oracle Recommended Patches -- Oracle Database [ID 756671.1]
6. Quick Reference to Patchset Patch Numbers [ID 753736.1]
Complete Checklist for Manual Upgrades to 11gR1 [ID 429825.1]
+++++++++++++++++++++++++++++++
upgrade an Oracle Database 10.2.0.X to 11.2.0.3
+++++++++++++++++++++++++++++++
After running catupgrd.sql to upgrade an Oracle Database 10.2.0.X to 11.2.0.3 when running the Post Upgrade Scripts - utlu112s.sql & catuppst.sql Scripts give Errors.ID 1543850.1
Using the below SQL Query would list all the Triggers in the Database -
SQL> set linesize 77
SQL> column triggering_event format a30
SQL> column trigger_name format a20
SQL>
SQL> select trigger_name,triggering_event,trigger_type,status from dba_triggers;
Solution
1) As mentioned in Note 837570.1 Complete Checklist for Manual Upgrades to 11gR2
Disable the custom triggers that would fire before/after DDL and enable them after the upgrade is complete.
2) In this case - NAMECHECK_BEFORE_DDL_DB_TRG is in "ENABLED" State and has to be disabled.
SQL>
alter trigger NAMECHECK_BEFORE_DDL_DB_TRG disable;
Once the trigger is disabled,The Post Upgrade Steps can then be resumed(starting with catuppst.sql)
3) Once all the Post Upgrade Scripts are completed successfully,the trigger can be enabled again.
SQL>
alter trigger NAMECHECK_BEFORE_DDL_DB_TRG enable;
+++++++++++++
Is It Required To Run catuppst.sql When Upgrading From 9i To 11gR1? [ID 782867.1]
+++++++++++++
