Upgradation Database
Version from 11.1.0.7 to 11.2.0.1 for Oracle EBS
(Worked on R12.1.3 Application & 11.1.0.7
Database)
Please refer to metalink note
Master Note
of Linux OS Requirements for Database Server [ID 851598.1]
Database
Preparation Guidelines for an E-Business Suite Release 12.1.1 Upgrade [ID
761570.1]
11gR2 DBUA
Errors - Database Vault Option Is Enabled [ID 1085051.1]
The steps
followed during the Upgradation are as follows (Step should be followed as per
the TOC is given):
1.
Pre-Upgrade
Step
1.1. Take
a cold backup of DB and Application.
1.5. Replace Old Oracle Home 11gR1 (11.1.0.7) with New Oracle Home 11gR2 (11.2.0.1) in
required files.
1.9. Disable database vault option before upgrade starts
in both 11gR1 (11.1.0.7) & 11gR2 (11.2.0.1)
1.13. Start
the old database 11gR1 (11.1.0.7)
1.14. Don’t
Start the Listener of old database 11gR1
(11.1.0.7)
2.
Upgrade
from 11gR1(11.1.0.7) to 11gR2 (11.2.0.1)
3.
Post
Upgrade Step
3.1. Set
the value of New Oracle Home 11gR2(11.2.0.1)
in adautocfg.sh and then Run autoconfig on Database Tier 11gR2 (11.2.0.1)
3.2. Shutdown
database listener of New Oracle Home 11gR2(11.2.0.1)
3.4. Start
database and database listener of New Oracle Home 11gR2(11.2.0.1)
3.5. Perform
utlrp.sql once more.
3.6. Perform
post upgrade task for patch no (9218789, 9657344) on New Oracle Home 11gR2 (11.2.0.1) Database Tier
3.11. Re-create
grants and synonyms using adadmin utility on Apps Server
3.12. Startup
all Application Services and verify database upgrade in OAM.
Database
11.7.0.1(source).
Pre-Upgrade
1.2 Perform all OS per-requisites for Installing
64-bit Oracle 11gR2 on RHEL or OEL 5.2 (or greater) 64-bit (x86_64) as per the Article Id 851598.1
I.
Hardware:
===========
1. Minimum Hardware Requirements
a.) At least 1.0 Gb (1024MB) of physical RAM. Recommended, 2gb or more.
b.) Swap disk space proportional to the system's physical memory as follows:
===========
1. Minimum Hardware Requirements
a.) At least 1.0 Gb (1024MB) of physical RAM. Recommended, 2gb or more.
b.) Swap disk space proportional to the system's physical memory as follows:
RAM
|
Swap Space
|
Between 1
GB and 2 GB
|
1.5 times
the size of RAM
|
Between 2
GB and 16 GB
|
equal
to the size of RAM
|
More
than 16 GB
|
16 GB
|
c.) 1024 Mb of disk space (and less than
2Tb of disk space) in the /tmp directory.
d.) approximately 4.4 Gb of local disk space for the database software.
e.) approximately 1.7 Gb of disk space for a preconfigured database that uses file system storage (optional)
II. Software:
============
1. Red Hat Enterprise Linux Server 5.2 (or greater), which is Kernel 2.6.18-92 or newer.
d.) approximately 4.4 Gb of local disk space for the database software.
e.) approximately 1.7 Gb of disk space for a preconfigured database that uses file system storage (optional)
II. Software:
============
1. Red Hat Enterprise Linux Server 5.2 (or greater), which is Kernel 2.6.18-92 or newer.
Note : The update level should
be Red Hat Enterprise Linux 5 Update 5 or greater if using the Oracle
Unbreakable Enterprise Kernel for Linux.
Reference : Certification Information for Oracle Database on Linux x86-64 (Doc ID 1304727.1)
Reference : Certification Information for Oracle Database on Linux x86-64 (Doc ID 1304727.1)
2. Required OS Components (per Release Notes,
and Install Guide)
Oracle Database 11g release before 11.2.0.2, both the 32-bit and 64-bit packages listed in the following
Oracle Database 11g release before 11.2.0.2, both the 32-bit and 64-bit packages listed in the following
sections are required.
binutils-2.17.50.0.6-6.el5
(x86_64)
compat-libstdc++-33-3.2.3-61 (x86_64) << both ARCH's are required. See next line.
compat-libstdc++-33-3.2.3-61 (i386) << both ARCH's are required. See previous line.
elfutils-libelf-0.125-3.el5 (x86_64)
glibc-2.5-24 (x86_64) << both ARCH's are required. See next line.
glibc-2.5-24 (i686) << both ARCH's are required. See previous line.
glibc-common-2.5-24 (x86_64)
ksh-20060214-1.7 (x86_64)
libaio-0.3.106-3.2 (x86_64) << both ARCH's are required. See next line.
libaio-0.3.106-3.2 (i386) << both ARCH's are required. See previous line.
libgcc-4.1.2-42.el5 (i386) << both ARCH's are required. See next line.
libgcc-4.1.2-42.el5 (x86_64) << both ARCH's are required. See previous line.
libstdc++-4.1.2-42.el5 (x86_64) << both ARCH's are required. See next line.
libstdc++-4.1.2-42.el5 (i386) << both ARCH's are required. See previous line.
make-3.81-3.el5 (x86_64)
unixODBC-2.2.12 (x86_64) << both ARCH's are required. See next item and Note 762206.1
unixODBC-2.2.12 (i386) << both ARCH's are required. See previous item and Note 762206.1
unixODBC-devel-2.2.12 (x86_64) << both ARCH's are required. See next item and Note 762206.1
unixODBC-devel-2.2.12 (i386) << both ARCH's are required. See previous item and Note 762206.1
compat-libstdc++-33-3.2.3-61 (x86_64) << both ARCH's are required. See next line.
compat-libstdc++-33-3.2.3-61 (i386) << both ARCH's are required. See previous line.
elfutils-libelf-0.125-3.el5 (x86_64)
glibc-2.5-24 (x86_64) << both ARCH's are required. See next line.
glibc-2.5-24 (i686) << both ARCH's are required. See previous line.
glibc-common-2.5-24 (x86_64)
ksh-20060214-1.7 (x86_64)
libaio-0.3.106-3.2 (x86_64) << both ARCH's are required. See next line.
libaio-0.3.106-3.2 (i386) << both ARCH's are required. See previous line.
libgcc-4.1.2-42.el5 (i386) << both ARCH's are required. See next line.
libgcc-4.1.2-42.el5 (x86_64) << both ARCH's are required. See previous line.
libstdc++-4.1.2-42.el5 (x86_64) << both ARCH's are required. See next line.
libstdc++-4.1.2-42.el5 (i386) << both ARCH's are required. See previous line.
make-3.81-3.el5 (x86_64)
unixODBC-2.2.12 (x86_64) << both ARCH's are required. See next item and Note 762206.1
unixODBC-2.2.12 (i386) << both ARCH's are required. See previous item and Note 762206.1
unixODBC-devel-2.2.12 (x86_64) << both ARCH's are required. See next item and Note 762206.1
unixODBC-devel-2.2.12 (i386) << both ARCH's are required. See previous item and Note 762206.1
gcc-4.1.2-42.el5
(x86_64)
gcc-c++-4.1.2-42.el5 (x86_64)
glibc-devel-2.5-24 (x86_64) << both ARCH's are required. See next item.
glibc-devel-2.5-24 (i386) << both ARCH's are required. See previous item.
glibc-headers-2.5-24 (x86_64)
kernel-headers-2.6.18-92 (x86_64)
libgomp-4.1.2-42 (x86_64)
libstdc++-devel-4.1.2-42 (x86_64)
libaio-devel-0.3.106-3.2 (x86_64) << both ARCH's are required. See next item
libaio-devel-0.3.106-3.2 (i386) << both ARCH's are required. See previous item.
sysstat-7.0.2-1 (x86_64)
gcc-c++-4.1.2-42.el5 (x86_64)
glibc-devel-2.5-24 (x86_64) << both ARCH's are required. See next item.
glibc-devel-2.5-24 (i386) << both ARCH's are required. See previous item.
glibc-headers-2.5-24 (x86_64)
kernel-headers-2.6.18-92 (x86_64)
libgomp-4.1.2-42 (x86_64)
libstdc++-devel-4.1.2-42 (x86_64)
libaio-devel-0.3.106-3.2 (x86_64) << both ARCH's are required. See next item
libaio-devel-0.3.106-3.2 (i386) << both ARCH's are required. See previous item.
sysstat-7.0.2-1 (x86_64)
elfutils-libelf-devel-0.125-3
elfutils-libelf-devel-static-0.125-3
3. Additional Required OS Components (per
the runInstaller OUI)
a.) intentionally blank
a.) intentionally blank
4. Additional Required OS Components (per this NOTE)
1.) elfutils-libelf-devel-static-0.125-3.el5.x86_64.rpm
2.) kernel-headers-2.6.18-92.el5.x86_64.rpm
3.) libgomp-4.1.2-42.el5.x86_64.rpm
1.) elfutils-libelf-devel-static-0.125-3.el5.x86_64.rpm
2.) kernel-headers-2.6.18-92.el5.x86_64.rpm
3.) libgomp-4.1.2-42.el5.x86_64.rpm
III. Environment:
================
1. Modify your kernel settings in /etc/sysctl.conf (RedHat) as follows. If the current value for any parameter is higher than the value listed in this table, do not change the value of that parameter. Range values (such as net.ipv4.ip_local_port_range) must match exactly.
================
1. Modify your kernel settings in /etc/sysctl.conf (RedHat) as follows. If the current value for any parameter is higher than the value listed in this table, do not change the value of that parameter. Range values (such as net.ipv4.ip_local_port_range) must match exactly.
kernel.shmall
= physical RAM size / pagesize For most systems, this will be the value
2097152. See Note
301830.1
for more information.
kernel.shmmax
= 1/2 of physical RAM. This would be the value 2147483648 for a system with 4Gb
of physical RAM.
kernel.shmmni
= 4096
kernel.sem = 250 32000 100 128
fs.file-max = 512 x processes (for example 6815744 for 13312 processes)
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
kernel.sem = 250 32000 100 128
fs.file-max = 512 x processes (for example 6815744 for 13312 processes)
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
2. To activate these new settings into the running kernel space, run the sysctl -p command as root.
3. Set Shell Limits for the oracle User. Assuming that the "oracle" Unix user will perform the installation, do the following:
a.) Add the following settings to /etc/security/limits.conf
*
soft nproc 2047
* hard nproc 16384
* soft nofile 1024
* hard nofile 65536
* soft stack 10240
* hard nproc 16384
* soft nofile 1024
* hard nofile 65536
* soft stack 10240
b.) Add or edit the following line in the /etc/pam.d/login file, if it does not already exist:
session required pam_limits.so
c.) Add the following lines to
/etc/profile:
if
[ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -u 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
if [ $SHELL = "/bin/ksh" ]; then
ulimit -u 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
4. The hostname command should return the fully qualified hostname as shown below:
$ hostname
hostname.domainname
hostname.domainname
5. If any Java packages are installed on the system, unset the Java environment variables, for example JAVA_HOME.
6. The oracle account that is used to install Oracle 11.2.0.1, should not have the Oracle install related variables set by default. For example setting
ORACLE_HOME, PATH, LD_LIBRARY_PATH to
include Oracle binaries in .profile, .login file and /etc/profile.d should be
completely avoided.
a.) Setting $ORACLE_BASE
(not $ORACLE_HOME) is recommended, since it eases a few prompts in the OUI
runInstaller tool.
b.) Following the successful install, it is recommended to set $ORACLE_HOME, and to set $PATH to include $ORACLE_HOME/bin at the beginning of
b.) Following the successful install, it is recommended to set $ORACLE_HOME, and to set $PATH to include $ORACLE_HOME/bin at the beginning of
the $PATH string.
7. By default, RHEL 5 x86_64 Linux is installed with SELinux as "enforcing". This is fine for the 11gR2 installation process. However, to subsequently run "sqlplus", switch SELinux to the "Permissive" mode. See NOTE 454196.1, "./sqlplus: error on libnnz11.so: cannot restore segment prot after reloc" for more details.
8. Log in as Oracle user and start the installation as follows:
7. By default, RHEL 5 x86_64 Linux is installed with SELinux as "enforcing". This is fine for the 11gR2 installation process. However, to subsequently run "sqlplus", switch SELinux to the "Permissive" mode. See NOTE 454196.1, "./sqlplus: error on libnnz11.so: cannot restore segment prot after reloc" for more details.
8. Log in as Oracle user and start the installation as follows:
$ ./runInstaller
I. Set proper display variable & begin Installation
#su – root
#echo $DISPLAY
# xhost +
Here we are setting the Oracle Base and Software Location to the EBS dbTier path forcefully, So that we couldn’t move the data path.
Run the scripts on root user to get the variable set.
I. Copy
$ORACLE_HOME(11gR1)/network/admin folder to $ORACLE_HOME(11gR2)/network/admin
[oratest@test
admin]$ cp -rvf TEST_test /d01/TEST/db/tech_st/11.2.0/network/admin
[oratest@test admin]$
[oratest@test admin]$
II. Copy Environment file from $ORACLE_HOME(11gR1) to $ORACLE_HOME(11gR2)
[oratest@test 11.1.0]$ ls -l *.env
-rwxrwxr-x 1 oratest dba 4295 Feb 15 13:35 TEST_test.env
[oratest@test 11.1.0]$ cp -rvf TEST_test.env /d01/TEST/db/tect_st/11.2.0
III. Copy
library folder from $ORACLE_HOME(11gR1) to $ORACLE_HOME(11gR2)
[oratest@test
11.1.0]$ cp -rvf lib32
/d01/TEST/db/tech_st/11.2.0/
Note: Error can occur while running the adautocfg.sh on dbTier
Context Value Management will now update the Context file
UnsatisfiedLinkError exception loading native library: njni11
java.lang.UnsatisfiedLinkError: libnjni11.so: /libnjni11.so:
wrong ELF class: ELFCLASS64 (Possible cause: architecture word width
mismatch)
[oratest@test TEST]$
find . -name 'libnjni11.so'
./db/tech_st/11.2.0/lib/libnjni11.so
./db/tech_st/11.1.0/lib/libnjni11.so
./db/tech_st/11.1.0/lib32/libnjni11.so
./db/tech_st/11.2.0/lib/libnjni11.so
./db/tech_st/11.1.0/lib/libnjni11.so
./db/tech_st/11.1.0/lib32/libnjni11.so
IV. Copy appsutil folder from $ORACLE_HOME(11gR1) to $ORACLE_HOME(11gR2)
[oratest@test
11.1.0]$ cp -rvf appsutil /d01/TEST/db/tech_st/11.2.0
[oratest@test 11.1.0]$
[oratest@test 11.1.0]$
V.
Copy dbs folder from $ORACLE_HOME(11gR1) to $ORACLE_HOME(11gR2)
[oratest@test
11.1.0]$ cp –rvf dbs /d01/TEST/db/tech_st/11.2.0
[oratest@test
dbs]$ ls
VI.
Copy admin folder from $ORACLE_HOME(11gR1) to $ORACLE_HOME(11gR2)
[oratest@test
11.1.0]$ cp –rvf admin /d01/TEST/db/tech_st/11.2.0
[oratest@test
dbs]$ ls
1.5 Replace Old Oracle Home 11gR1 (11.1.0.7) with New Oracle Home 11gR2 (11.2.0.1) in
required files.
I. Change
the required files TEST_test.env, TEST_test.xml, listener.ora, tnsnames.ora
& sqlnet.ora with
11gR1 Home - /d01/TEST/db/tech_st/11.1.0
11gR2 Home - /d01/ TEST/db/tech_st /11.2.0
11gR2 Home - /d01/ TEST/db/tech_st /11.2.0
I.
Start the database from
11gR1 ORACLE_HOME as well as listener
II. Apply
the patches (9062910, 9852070, 8919489) using adpatch. Make sure the
application is maintenance mode and application is down.
III. Perform
the post patch step.
[appltest@test
bin]$cd $AD_TOP/bin
[appltest@test
bin]$ perl admkappsutil.pl
Starting the generation of appsutil.zip
Log file located at /u01/TEST/inst/apps/TEST_test/admin/log/MakeAppsUtil_03131154.log
output located at /u01/TEST/inst/apps/TEST_test/admin/out/appsutil.zip
MakeAppsUtil completed successfully.
[appltest@test bin]$ scp /u01/TEST/inst/apps/TEST_test/admin/out/appsutil.zip oratest@test:/d01/TEST/db/tech_st/11.2.0
Starting the generation of appsutil.zip
Log file located at /u01/TEST/inst/apps/TEST_test/admin/log/MakeAppsUtil_03131154.log
output located at /u01/TEST/inst/apps/TEST_test/admin/out/appsutil.zip
MakeAppsUtil completed successfully.
[appltest@test bin]$ scp /u01/TEST/inst/apps/TEST_test/admin/out/appsutil.zip oratest@test:/d01/TEST/db/tech_st/11.2.0
[oratest@test
11.2.0]$ chmod 775 appsutil.zip
[oratest@test 11.2.0]$ unzip -o appsutil.zip
Archive: appsutil.zip
creating: appsutil/
creating: appsutil/bin/
inflating: appsutil/bin/adchkutl.sh
inflating: appsutil/bin/txkGenCtxInfRep.pl
inflating: appsutil/bin/txkrun.pl
inflating: appsutil/bin/adclone.sh
inflating: appsutil/bin/addlnctl.pl
[oratest@test 11.2.0]$ unzip -o appsutil.zip
Archive: appsutil.zip
creating: appsutil/
creating: appsutil/bin/
inflating: appsutil/bin/adchkutl.sh
inflating: appsutil/bin/txkGenCtxInfRep.pl
inflating: appsutil/bin/txkrun.pl
inflating: appsutil/bin/adclone.sh
inflating: appsutil/bin/addlnctl.pl
I.
Set New Oracle Home in
bash profile of db user and freshly login to db user.
II.
Set the Opatch path in
PATH variable of bash profile of db user.
PATH=$PATH:$HOME/bin:/d01/TEST/db/tech_st/11.2.0/OPatch/
III.
Set the Central Inventory
path to /etc/oraInst.loc
inventory_loc=/d01/TEST/db/tech_st/11.2.0/admin/oui/TEST_test/oraInventory(/d01/TEST/db/tech_st/11.2.0)
IV.
Set Central Inventory using
runlnstaller.
[oratest@test
11.2.0]$ cd $ORACLE_HOME/oui/bin
[oratest@test
11.2.0]$ ./runInstaller -silent -ignoreSysPrereqs -attachHome
ORACLE_HOME="(PATH OF RDBMS HOME 11GR2)" ORACLE_HOME_NAME=SID
Example:
[oratest@test
11.2.0]$ ./runInstaller -silent -ignoreSysPrereqs -attachHome
ORACLE_HOME="/d01/TEST/db/tech_st/11.2.0" ORACLE_HOME_NAME=TEST
V.
Before applying the
opatch make sure database and listener is down. Apply the following opatch for
11gR2 are as follows. Also follow the article id 761570.1
a. 9719541, 9535951, 9756939, 9657344, 9644960, 9500046, 9318214, 9218789,
9081430, 8993052, 8328200
1.8 Run pre-upgrade utlu112i.sql
and update changes as required on 11gR1 (11.1.0.7) Database.
I.
Copy utlu112i.sql from
the New Oracle Home 11gR2 to Old Oracle Home 11gR1
[oratest@test
admin]$ cd
/d01/TEST/db/tech_st/11.1.0/rdbms/admin
[oratest@test
admin]$ cp –rvf
/d01/TEST/db/tech_st/11.2.0/rdbms/admin/utlu112i.sql .
[oratest@test admin]$
[oratest@test admin]$
II.
Run script utlu112i.sql
on 11gR1
[oratest@test
admin]$ sqlplus / as sysdba
SQL*Plus:
Release 11.1.0.7.0 - Production on Tue Mar 13 14:18:21 2012
Sqlplus> @utlu112i.sql
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> spool db_upgrade.info
SQL> @utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 03-13-2012 14:20:39
.
**********************************************************************
Database:
**********************************************************************
--> name: TEST
--> version: 11.1.0.7.0
--> compatible: 11.1.0
--> blocksize: 8192
--> platform: Linux IA (64-bit)
--> timezone file: V10
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 11042 MB
--> CTXD tablespace is adequate for the upgrade.
.... minimum required size: 19 MB
--> ODM tablespace is adequate for the upgrade.
.... minimum required size: 10 MB
--> APPS_UNDOTS1 tablespace is adequate for the upgrade.
.... minimum required size: 1445 MB
--> APPS_TS_TX_DATA tablespace is adequate for the upgrade.
.... minimum required size: 5213 MB
--> APPS_TS_QUEUES tablespace is adequate for the upgrade.
.... minimum required size: 107 MB
--> OLAP tablespace is adequate for the upgrade.
.... minimum required size: 16 MB
WARNING: --> SYSAUX tablespace is not large enough for the upgrade.
.... currently allocated size: 472 MB
.... minimum required size: 501 MB
.... increase current size by: 29 MB
.... tablespace is NOT AUTOEXTEND ENABLED.
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
WARNING: --> "java_pool_size" needs to be increased to at least 64 MB
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
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
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> JServer JAVA Virtual Machine [upgrade] VALID
--> Oracle XDK for Java [upgrade] VALID
--> Real Application Clusters [upgrade] INVALID
--> OLAP Analytic Workspace [upgrade] VALID
--> OLAP Catalog [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
--> Spatial [upgrade] VALID
--> Data Mining [upgrade] VALID
--> Oracle OLAP API [upgrade] VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database is using a timezone file older than version 11.
.... 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.
WARNING: --> Database contains schemas with stale optimizer statistics.
.... Refer to the Upgrade Guide for instructions to update
.... schema statistics prior to upgrading the database.
.... Component Schemas with stale statistics:
.... SYS
.... OLAPSYS
.... CTXSYS
.... MDSYS
WARNING:--> recycle bin in use.
.... Your recycle bin is turned on and it contains
.... 1610 object(s). It is REQUIRED
.... that the recycle bin is empty prior to upgrading
.... your database.
.... The command: PURGE DBA_RECYCLEBIN
.... must be executed immediately prior to executing your upgrade.
PL/SQL procedure successfully completed.
Sqlplus> @utlu112i.sql
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> spool db_upgrade.info
SQL> @utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 03-13-2012 14:20:39
.
**********************************************************************
Database:
**********************************************************************
--> name: TEST
--> version: 11.1.0.7.0
--> compatible: 11.1.0
--> blocksize: 8192
--> platform: Linux IA (64-bit)
--> timezone file: V10
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 11042 MB
--> CTXD tablespace is adequate for the upgrade.
.... minimum required size: 19 MB
--> ODM tablespace is adequate for the upgrade.
.... minimum required size: 10 MB
--> APPS_UNDOTS1 tablespace is adequate for the upgrade.
.... minimum required size: 1445 MB
--> APPS_TS_TX_DATA tablespace is adequate for the upgrade.
.... minimum required size: 5213 MB
--> APPS_TS_QUEUES tablespace is adequate for the upgrade.
.... minimum required size: 107 MB
--> OLAP tablespace is adequate for the upgrade.
.... minimum required size: 16 MB
WARNING: --> SYSAUX tablespace is not large enough for the upgrade.
.... currently allocated size: 472 MB
.... minimum required size: 501 MB
.... increase current size by: 29 MB
.... tablespace is NOT AUTOEXTEND ENABLED.
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
WARNING: --> "java_pool_size" needs to be increased to at least 64 MB
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
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
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> JServer JAVA Virtual Machine [upgrade] VALID
--> Oracle XDK for Java [upgrade] VALID
--> Real Application Clusters [upgrade] INVALID
--> OLAP Analytic Workspace [upgrade] VALID
--> OLAP Catalog [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
--> Spatial [upgrade] VALID
--> Data Mining [upgrade] VALID
--> Oracle OLAP API [upgrade] VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database is using a timezone file older than version 11.
.... 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.
WARNING: --> Database contains schemas with stale optimizer statistics.
.... Refer to the Upgrade Guide for instructions to update
.... schema statistics prior to upgrading the database.
.... Component Schemas with stale statistics:
.... SYS
.... OLAPSYS
.... CTXSYS
.... MDSYS
WARNING:--> recycle bin in use.
.... Your recycle bin is turned on and it contains
.... 1610 object(s). It is REQUIRED
.... that the recycle bin is empty prior to upgrading
.... your database.
.... The command: PURGE DBA_RECYCLEBIN
.... must be executed immediately prior to executing your upgrade.
PL/SQL procedure successfully completed.
SQL>
spool off
SQL> PURGE
DBA_RECYCLEBIN(11GR1)
DBA Recyclebin purged.
DBA Recyclebin purged.
1.9 Disable database
vault option before upgrade starts in both 11gR1 (11.1.0.7) & 11gR2 (11.2.0.1)
I.
Set the environment variable on old ORACLE_HOME 11gR1 (11.1.0.7)
II.
Run the script from the $ORACLE_HOME/rdbms/lib location.
[oratest@test
admin]$ make -f ins_rdbms.mk dv_off ioracle
III.
Set the environment variable on 11gR2
(11.2.0.1)
IV.
Run the script from the $ORACLE_HOME/rdbms/lib location.
[oratest@test
admin]$ make -f ins_rdbms.mk dv_off ioracle
1.10 Create 9idata
folder in 11gR2 (11.2.0.1)
I. Set the environment variable on 11gR2
(11.2.0.1)
[oratest@test old]$
cd $ORACLE_HOME/nls/data/old
[oratest@test old]$
perl cr9idata.pl
Creating directory /d01/TEST/db/tech_st/11.2.0/nls/data/9idata ...
Copying files to /d01/TEST/db/tech_st/11.2.0/nls/data/9idata...
Copy finished.
Please reset environment variable ORA_NLS10 to /d01/TEST/db/tech_st/11.2.0/nls/data/9idata!
Creating directory /d01/TEST/db/tech_st/11.2.0/nls/data/9idata ...
Copying files to /d01/TEST/db/tech_st/11.2.0/nls/data/9idata...
Copy finished.
Please reset environment variable ORA_NLS10 to /d01/TEST/db/tech_st/11.2.0/nls/data/9idata!
1.11 Setting the TimeZone
in 11gR2 (11.2.0.1)
[oratest@test old]$ cd $ORACLE_HOME/oracore/zoneinfo
[oratest@test zoneinfo]$ mv -f
timezone_1.dat timezone.dat
[oratest@test zoneinfo]$ mv -f timezlrg_1.dat timezlrg.dat
[oratest@test zoneinfo]$ mv -f timezlrg_1.dat timezlrg.dat
note
if timeezlrg.dat and timezone.dat is not present than rename one of the files
respectively .
1.12 Setting values of oratab
file in /etc/oratab
Set oratab to old ORACLE_HOME 11gR1 (11.1.0.7)
[root@test ]$ vi /etc/oratab
<SID>:<Path of Old Oracle Home>:N
TEST:/d01/TEST/db/tech_st/11.1.0:N
1.15 Setting values of New ORACLE_HOME
11gR2 (11.2.0.1) to bash profile
[root@test ]$ su - oratest
[oratest@test old]$ vi .bash_profile
.
/d01/TEST/db/tech_st/11.2.0/TEST_test.env
Database Upgrade
2.1 Execute dbua to
perform an database upgrade
[oratest@test
]cd $ORACLE_HOME/bin
[oratest@test
bin]./dbua -initParam "_disable_fast_validate=TRUE" [ 10g and 11g]
[oratest@test
bin]./dbua [Only upgrade from 9i to
10g]
Click on Ignore button.
2.2 Set the temporary tablespace to TEMP1, when dbua is running Performing Post Upgrade
To avoid the error ( ORA-25153: Temporary Tablespace is Empty & ORA-01403: no data found )
To avoid the error ( ORA-25153: Temporary Tablespace is Empty & ORA-01403: no data found )
SQL> alter database default temporary tablespace TEMP1;
SQL> alter tablespace TEMP1 add tempfile '/b02/uatdb/db/apps_st/data/temp03.dbf' size 1024M reuse;
SQL> alter tablespace TEMP1 add tempfile '/b02/uatdb/db/apps_st/data/temp04.dbf' size 1024M reuse;
SQL> alter tablespace TEMP1 add tempfile '/b02/uatdb/db/apps_st/data/temp03.dbf' size 1024M reuse;
SQL> alter tablespace TEMP1 add tempfile '/b02/uatdb/db/apps_st/data/temp04.dbf' size 1024M reuse;
Post upgrade steps
3.2 Set value to Listener file in New Oracle
Home 11gR2 (11.2.0.1) Database Tier
[root@test ~]# su – oratest
[oratest@test]$cd $TNS_ADMIN
[oratest@test admin]$vi SID_hostname_ifile.ora [Add
entry to this file with the given pattern]
LISTENER_<SID> =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=<Hostname>)(PORT=<Portnumber>))
)
)
Example:
LISTENER_TEST =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=test)(PORT=1571))
)
)
3.9 Set the value of diagnostic_dest in
init.ora as well as on the Database Tier 11gR2 (11.2.0.1)
SQL> sqlplus ‘/as sysdba’
SQL> ALTER SYSTEM SET
diagnostic_dest='/d01/TEST/db/tech_st/11.2.0/admin/TEST_test'
3.7 Copy scripts (adgrants.sql, adctxprv.sql, wfaqupfix.sql) from 11gR1 (11.1.0.7) Application Tier to 11gR2 (11.2.0.1) Database Tier
[root@test ~]# su -
appltest
[appltest@test ~]$ cd $APPL_TOP/admin
[appltest@test admin]$ scp adgrants.sql oratest@192.168.0.228:/d01/TEST/db/tech_st/11.2.0/appsutil/scripts/TEST_test/
[appltest@test sql]$ cd $AD_TOP/patch/115/sql
[appltest@test ~]$ cd $APPL_TOP/admin
[appltest@test admin]$ scp adgrants.sql oratest@192.168.0.228:/d01/TEST/db/tech_st/11.2.0/appsutil/scripts/TEST_test/
[appltest@test sql]$ cd $AD_TOP/patch/115/sql
[appltest@test sql]$scp adctxprv.sql
oratest@192.168.0.228:/d01/TEST/db/tech_st/11.2.0/appsutil/scripts/TEST_test/
[appltest@test sql]$
[appltest@test sql]$
[appltest@test
sql]$ cd $FND_TOP/patch/115/sql
[appltest@test sql]$ scp wfaqupfix.sql oratest@192.168.0.228:/d01/TEST/db/tech_st/11.2.0/appsutil/scripts/TEST_test/
[appltest@test sql]$ scp wfaqupfix.sql oratest@192.168.0.228:/d01/TEST/db/tech_st/11.2.0/appsutil/scripts/TEST_test/
3.8 Run scripts (adgrants.sql, adctxprv.sql,
wfaqupfix.sql) on Database Tier 11gR2 (11.2.0.1)
[oratest@test db_scripts]$ sqlplus
/nolog @adgrants.sql < schema name> (schema
name=apps)
[oratest@test db_scripts]$ sqlplus
apps/<passwd> @adctxprv.sql <System Password> CTXSYS
(apps password=kdropss, system
password=manager)
SQL> exec
ctxsys.ctx_adm.set_parameter('file_access_role', 'public');
[oratest@test TEST_test]$ sqlplus apps/<passwd> @wfaqupfix.sql APPLSYS APPS
[oratest@test TEST_test]$ sqlplus apps/<passwd> @wfaqupfix.sql APPLSYS APPS
3.10 Gather statistics for SYS schema
Copy adstats.sql from $APPL_TOP/admin to database 11gR2 $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> alter system enable restricted session;
SQL> @adstats.sql
$ sqlplus "/ as sysdba"
SQL> alter system disable restricted session;
SQL> exit;
SQL> alter system enable restricted session;
SQL> @adstats.sql
$ sqlplus "/ as sysdba"
SQL> alter system disable restricted session;
SQL> exit;
