Sunday, June 16, 2013

Database upgrade from 11.2.0.1 to 11.2.0.3 :

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

Apply rdbms patch 9858539
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

Apply rdbms patch 12942119
export ORACLE_HOME=/d02/oracle/<sid>db/11.2.0.3
cd /nfs/db_11.2.0.3_patches/12942119
$ORACLE_HOME/OPatch/opatch lsinventory
$ORACLE_HOME/OPatch/opatch apply

Apply rdbms patch 12960302
export ORACLE_HOME=/d02/oracle/<sid>db/11.2.0.3
cd /nfs/db_11.2.0.3_patches/12960302
$ORACLE_HOME/OPatch/opatch lsinventory
$ORACLE_HOME/OPatch/opatch apply

Apply rdbms patch 13004894
export ORACLE_HOME=/d02/oracle/<sid>db/11.2.0.3
cd /nfs/db_11.2.0.3_patches/13004894
$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]
+++++++++++++

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)