Upgrade from 11.2.0.1 to 11.2.0.3 (Part I Software Installation)
In this part I'll discuss OUT-PLACE installation steps of Oracle 11.2.0.3 (standalone DB) on a Linux server already have an Oracle 11.2.0.1 setup with databases up and running.
Database upgrade steps (Standalone DB) will be covered in Part II
In case you're interested in RAC database upgrade from 11.2.0.1 to 11.2.0.3 I've explained a full implementation (cookbook) of upgrading a 11.2.0.1 to 11.2.0.3 RAC database on new servers (out-place upgrade) in this link:
http://dba-tips.blogspot.ae/2013/09/upgrade-rac-11201-to-11203-part-i.html
My current environment specs before the upgrade:
Oracle Enterprise Linux 5.8 X86_64
Oracle 11.2.0.1
3 Up and running 11.2.0.1 databases.
11.2.0.1 ORACLE_HOME = /u01/oracle/ora11gr2/11.2.0.1
My environment specs after the upgrade:
Oracle Enterprise Linux 5.8 X86_64
Oracle 11.2.0.3
3 Up and running 11.2.0.3 databases.
11.2.0.3 ORACLE_HOME = /u02/oracle/ora11g/11.2.0.3
Note: In this demonstration I'll do an Out-Place upgrade, this means I'll install the new software on a new ORACLE_HOME path which Oracle recommends.
In-Place upgrade means, to install the new software on the same location of the original old ORACLE_HOME, the thing requires to detach the old ORACLE_HOME first before installing the new software in-place of it, the thing will increases the downtime window.
Out-PLACE upgrade is much safer, easier, with minimal downtime if you compare it with the In-PLACE upgrade, the only disadvantage of Out-Place upgrade that it needs more space than the In-Place upgrade.
Environment Preparation:
#####################
Requirements for Linux :
===================
For RHEL 5 x86_64:
Minimum Red Hat/Oracle Enterprise Linux version is EL 5 Update 5
Minimum Unbreakable Enterprise Kernel => 2.6.32 or later
Currently I've Oracle Linux 5 Update 8 with Unbreakable kernel 2.6.32-300
Note: Starting with Oracle 11gR2, SELinux is supported.
If enabling Automatic Memory Management:
-----------------------------------------------------
/dev/shm must be greater than the sum of MEMORY_MAX_TARGET for all instance on the server.
================================
All of these packages are already installed on my server as I already have 11.2.0.1 setup.
Required packages for 11.2.0.2 and above (on OEL 5 x86_64):
--------------------------------------------------------------------------
rpm -qa | grep binutils-2.17.50.0.6
rpm -qa | grep compat-libstdc++-33-3.2.3
rpm -qa | grep elfutils-libelf-0.1
rpm -qa | grep elfutils-libelf-devel-0.1
rpm -qa | grep gcc-4.1.2
rpm -qa | grep gcc-c++-4.1.2
rpm -qa | grep glibc-2.5
rpm -qa | grep glibc-common-2.5
rpm -qa | grep glibc-devel-2.5
rpm -qa | grep glibc-headers-2.5
rpm -qa | grep ksh-2
rpm -qa | grep libaio-0.3.106
rpm -qa | grep libaio-devel-0.3.106
rpm -qa | grep libgcc-4.1.2
rpm -qa | grep libstdc++-4.1.2
rpm -qa | grep libstdc++-devel-4.1.2
rpm -qa | grep make-3.81
rpm -qa | grep sysstat-7.0.2
rpm -qa | grep unixODBC-2.2.11 #=> (32-bit) or later
rpm -qa | grep unixODBC-devel-2.2.11 #=> (64-bit) or later
rpm -qa | grep unixODBC-2.2.11 #=> (64-bit) or later
Create Oracle user:
===============
Note: Oracle user, DBA and OINSTALL groups are automatically created during Oracle Enterprise Linux installation, you can skip this step. also as we already have 11.2.0.1 Oracle installation on this server this gurantee that Oracle user with it's group is already there.
# groupadd -g 502 dba
# groupadd -g 503 oinstall
# useradd -u 505 -g oinstall -G dba -s /bin/bash -d /home/oracle oracle
# passwd oracle
# mkdir -p /home/oracle
# chown oracle:dba /home/oracle
# chmod 750 /home/oracle
Create a new ORACLE_HOME path:
============================
# mkdir -p /u02/oracle/ora11g/11.2.0.3
# chown -R oracle:dba /u01/oracle/ora11g
# chmod 750 /u02/oracle/ora11g/11.2.0.3
Create a new .bash_profile holds 11.2.0.3 Environment variables:
==================================================
As I already have an 11.2.0.1 installation on the server and I don't want to create a new oracle user to be the owner of the new 11.2.0.3 installation, I have to create a new .bash_profile file, holds the environment variables of the new ORACLE_HOME to not mix with the already running 11.2.0.1 setup.
This new .bash_profile which will name it .bash_profile11203 will holds the same Env variables from the original .bash_profile except replacing $ORACLE_BASE & ORACLE_HOME variables with the ones pointing to 11.2.0.3 installation.
Each time I deal with 11.2.0.3 installation or databases running from 11.2.0.3 Oracle Home, I've to call that .bash_profile11203 .
# cd /home/oracle
# cp .bash_profile .bash_profile11203
Modify the new environment profile:
# vi .bash_profile11203
=>Run these two vi commands:
ESC :
%s/\/u01\/oracle\/ora11gr2\/11\.2\.0\.1/\/u02\/oracle\/ora11g\/11\.2\.0\.3/g
%s/\/u01\/oracle/\/u02\/oracle/g
=> You've to customize the above command to replace old ORACLE_HOME path with the new ORACLE_HOME path.
Now I've a good new profile contains variables pointing to the new ORACLE_HOME installation.
Configure SYSTEM parameters:
=========================
All parameters should be same or greater on the OS:
--------------------------------------------------
# /sbin/sysctl -a | grep sem #=> semaphore parameters (250 32000 100 142).
# /sbin/sysctl -a | grep shm #=> shmmax, shmall, shmmni (536870912, 2097152, 4096).
# /sbin/sysctl -a | grep file-max #=> (6815744).
# /sbin/sysctl -a | grep ip_local_port_range #=> Minimum: 9000, Maximum: 65500
# /sbin/sysctl -a | grep rmem_default #=> (262144).
# /sbin/sysctl -a | grep rmem_max #=> (4194304).
# /sbin/sysctl -a | grep wmem_default #=> (262144).
# /sbin/sysctl -a | grep wmem_max #=> (1048576).
# /sbin/sysctl -a | grep aio-max-nr #=> (Maximum: 1048576) limits concurrent requests to avoid
I/O Failures.
If you need to change any of these parameters, login as root user, modify file /etc/sysctl.conf then execute this command:
# sysctl -p
vi /etc/security/limits.conf --Already exist with bigger values so keep it as it except the last parameter.
---------------------------------
oracle soft nofile 131072
oracle hard nofile 131072
oracle soft nproc 131072
oracle hard nproc 131072
oracle soft core unlimited
oracle hard core unlimited
oracle soft memlock 50000000
oracle hard memlock 50000000
# Adjust MAX stack size for 11.2.0.3 => Original was 8192:
oracle soft stack 10240
After updating limits.conf user should logoff & logon to let new adjustments take effect.
Ensure mounting /usr in READ-WRITE mode:
====================================
# mount -o remount,rw /usr
>For security reasons some System Administrators prefer to mount /usr
in READ ONLY mode, during Oracle installation /usr must be in RW mode.
=================
# cp /etc/oraInst.loc /etc/oraInst.loc.11.2.0.1
# cp /etc/oratab.11.2.0.1
# cp /usr/local/bin/oraenv /usr/local/bin/oraenv.11.2.0.1
# cp /usr/local/bin/dbhome /usr/local/bin/dbhome.11.2.0.1
# cp /usr/local/bin/coraenv /usr/local/bin/coraenv.11.2.0.1
Create&Modify Oracle Inventory location: (By ROOT user)
===============================
# mkdir -p /u02/oracle/oraInventory
# vi /etc/oraInst.loc
inventory_loc=/u02/oracle/oraInventory
inst_group=oinstall
Run the new 11.2.0.3 PROFILE:
=========================
Set the new Env variables that point to the new ORACLE_HOME by running file .bash_profile1123:
# cd /home/oracle
# . .bash_profile1123
ORACLE 11.2.0.3 INSTALLATION:
#############################
Download the 11.2.0.3 installation files from Oracle support web site -it's not available on oracle.com site-
Go to Patches&Updates tab,
search for patch# 10404530 then select the right one for your platform (mine is Linux x86_64).
Download the first two files only (p10404530_112030_Linux-x86-64_1of7 , p10404530_112030_Linux-x86-64_2of7)
for Oracle software installation only, if you'll install Grid for RAC
or ASM (which is not in our scope) you need to download the first three
files.
The rest of files are for (Client, Gateways, examples, deinstall) which are not in our scope.
You can select which mode you will use to perform the installation whether GUI or SILENT mode, I'll discuss both of them in the coming lines.
Note: As we are doing an out-place upgrade, we will not touch
the old Oracle Home, that means you don't need to shutdown the already
running databases or listeners which runs from 11.2.0.1 Oracle Home.
this explains how Out-Place upgrade minimize the downtime window.
Installation using GUI mode:
#####################
Login to your server using VNC or directly to the Console (physical access) with Oracle user.
Sometimes Sys admins lock direct login to Oracle user, if so login with root user then issue this command:
# xhost +
Then switch to Oracle user
# su - oracle
To know how to use configure VNC on your server check this link:
http://dba-tips.blogspot.ae/2012/05/easy-way-to-configure-vnc-server-on.html
Go to the DVD location:
----------------------------
# cd /export/11.2.0.3/database
# ./runInstaller
Page1 (Configure Security Updates):
Remove the check from"I wish to receive security updates via My Oracle Support"click Next..click YES
Page2 (Download Software Updates):
Check "Skip software updates" ...click Next (My server doesn't have access to the internet).
Page3 (Installation Option):
Check "Install database software only" ...click Next
Page4 (Grid Installation Options):
Check "Single instance database installation" ...click Next
Page5 (Product Languages):
Leave it to the default ...click Next
Page6 (Database Edition):
Enterprise Edition ...click select options..(check the needed options)..click OK..click Next
Page7 (Installation Location):
ORACLE_BASE: /u02/oracle , Software Location: /u02/oracle/ora11g/11.2.0.3 (software location is ORACLE_HOME).
Page8 (Operating System Groups):
Database Administrator (OSDBA) Group ...select "dba"
Database Operator (OSOPER) Group (Optional) ..leave it blank >>I never had a need to connect to the database as SYSOPER.
Page9 (Summary):
Click Install
At the end of installation:
from another session by root user execute script $ORACLE_HOME/root.sh
Page10 (Finish):
Once executed root.sh script from another session, go back to the OUI window and click Exit.
Note: if you want to create a response file, from runInstaller GUI do this:
# cd /u02/11.2.0.3/database
# ./runInstaller
Choose Options you need to install ,at the "Summary" screen click "Save responsefile" button.
Installation using SILENT mode:
========================
# cd /export/11.2.0.3/database
# ./runInstaller -silent -ignoreSysPrereqs -ignorePrereq -ignoreInternalDriverError -showProgress -noconfig
-responseFile /u02/11.2.0.3/database/response/db_install.rsp
ORACLE_BASE=/u02/oracle
ORACLE_HOME=/u02/oracle/ora11g/11.2.0.3
INVENTORY_LOCATION=/u02/oracle/oraInventory
ORACLE_HOME_NAME=OraDbHome11203
oracle.install.option=INSTALL_DB_SWONLY
oracle.install.db.InstallEdition=EE
UNIX_GROUP_NAME=oinstall
oracle.install.db.DBA_GROUP=dba
DECLINE_SECURITY_UPDATES=true
oracle.install.db.optionalComponents=oracle.rdbms.lbac:11.2.0.3.0,oracle.rdbms.dm:11.2.0.3.0,oracle.rdbms.dv:11.2.0.3.0,oracle.rdbms.rat:11.2.0.3.0
Note:
----
oracle.install.db.optionalComponents=
oracle.oraolap:11.2.0.3.0 - Oracle OLAP
oracle.rdbms.dm:11.2.0.3.0 - Oracle Data Mining RDBMS Files
oracle.rdbms.dv:11.2.0.3.0 - Oracle Database Vault option
oracle.rdbms.lbac:11.2.0.3.0 - Oracle Label Security
oracle.rdbms.partitioning:11.2.0.3.0 - Oracle Partitioning
oracle.rdbms.rat:11.2.0.3.0 - Oracle Real Application Testing
That means the options I'm installing are: Oracle Label Security, Data Mining, Database Vault, Real Application Testing.
-showProgress :Showing the progress of the installation on the screen.
-noconfig: Supress running configuration assistants during installation as it will be "Software Only"
>>Progress will be printed on the screen also you can check the log:
# tail -f /u02/oracle/oraInventory/logs/installActions<$date_$time>.log
Starting Oracle Universal Installer...
Checking Temp space: must be greater than 120 MB. Actual 2872 MB Passed
Checking swap space: must be greater than 150 MB. Actual 12768 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2013-01-02_12-13-51PM. Please wait ...
[oracle@dev1 database]#
[oracle@dev1 database]#You can find the log of this install session at:
/u02/oracle/oraInventory/logs/installActions2013-01-02_12-13-51PM.log
Prepare in progress.
.................................................. 9% Done.
Prepare successful.
Copy files in progress.
.................................................. 14% Done.
.................................................. 20% Done.
.................................................. 26% Done.
.................................................. 31% Done.
.................................................. 36% Done.
.................................................. 44% Done.
.................................................. 49% Done.
.................................................. 55% Done.
.................................................. 63% Done.
.................................................. 68% Done.
.................................................. 73% Done.
.................................................. 78% Done.
.................................................. 83% Done.
..............................
Copy files successful.
Link binaries in progress.
..........
Link binaries successful.
Setup files in progress.
.................................................. 88% Done.
.................................................. 94% Done.
Setup files successful.
The installation of Oracle Database 11g was successful.
Please check '/u02/oracle/oraInventory/logs/silentInstall2013-01-02_12-13-51PM.log' for more details.
Execute Root Scripts in progress.
As a root user, execute the following script(s):
1. /u02/oracle/ora11g/11.2.0.3/root.sh
.................................................. 100% Done.
Execute Root Scripts successful.
Successfully Setup Software.
At the End of the installation: (By root user run root.sh script):
# /u02/oracle/ora11g/11.2.0.3/root.sh
Installation is done.
Backup oraInventory directory:
-------------------------------------
# tar cvf /u02/oracle/oraInventory.tar /u02/oracle/oraInventory
Backup root.sh:
------------------
# cp /u02/oracle/ora11g/11.2.0.3/root.sh /u02/oracle/ora11g/11.2.0.3/root.sh_after_installation
Backup ORACLE_HOME: (By root user)
-------------------------------
# tar cvpf /u02/oracle/ora11g/11.2.0.3_After_DB_install.tar /u02/oracle/ora11g/11.2.0.3
Backup the following files:
-------------------------------
# cp /usr/local/bin/oraenv /usr/local/bin/oraenv.11.2.0.3
# cp /usr/local/bin/dbhome /usr/local/bin/dbhome.11.2.0.3
# cp /usr/local/bin/coraenv /usr/local/bin/coraenv.11.2.0.3
PSU Patch Apply:
##############
Apply the latest PSU patch on the 11.2.0.3 ORACLE_HOME before upgrading
the database, PSU patch post steps which runs on the database like
catbunddel.sql are not required (unless the PATCH README mention post
steps to be applied after DB Creation|Upgrade), because every PSU patch
is unique. As per .
Download and Install latest OPatch utility: Patch# 6880880 (For 11.2.0.3 on Linux X64_68)
Latest OPatch utility was 11.2.0.3.3
# cd $ORACLE_HOME
# tar cvf OPatch.org.tar OPatch
# cd OPatch
# rm -rf *
# cd $ORACLE_HOME
# unzip p6880880_112000_Linux-x86-64.zip
Download & Apply latest PSU patch 11.2.0.3.4 : Metalink patch# 14275605
# unzip p14275605_112030_Linux-x86-64.zip
# cd 14275605
# opatch apply
Now you're done with PSU patch apply.
Part II : Database Upgrade steps from 11.2.0.1 to 11.2.0.3 (Standalone DB)
In this part I'll discuss database upgrade steps from 11.2.0.1 to 11.2.0.3, I already covered 11.2.0.3 database software installation steps on a server already running 11.2.0.1 database using out-place method in Part I : environment preparation & 11.2.0.3 Software Installation.
In case you're interested in RAC database upgrade from 11.2.0.1 to 11.2.0.3 I've explained a full implementation (cook book) of upgrading a 11.2.0.1 to 11.2.0.3 RAC database on a new servers (out-place upgrade) in this link:
http://dba-tips.blogspot.ae/2013/09/upgrade-rac-11201-to-11203-part-i.html
Important Notes:
Metalink [ID 730365.1] Includes all patchset downloads + How to upgrade from any Oracle DB version to another one.
Metalink [ID 1276368.1] Out-of-place manual upgrade from previous 11.2.0.N version to the latest 11.2.0.N patchset.
Tips Before Starting The Upgrade Process:
##################################
Backup & Truncate Audit Table SYS.AUD$:
-------------------------------------------------
Truncating SYS.AUD$ table will speed up the upgrade process:
--Backing up + compressing audit data in SYS.AUD_BKP table:
SQL> create table SYS.AUD_BKP COMPRESS as select * from SYS.AUD$;
SQL> Truncate table SYS.AUD$;
Purge DBA_RECYCLEBIN:
---------------------------
Purging DBA_RECYCLEBIN will speed up the upgrade process:
SQL> PURGE DBA_RECYCLEBIN;
Save a backup of all DB Parameters (Visible & hidden):
------------------------------------------------------------------
This will help you in troubleshooting issues coming after the upgrade (bugs, performance, or whatever the problem is).
The following statement will make it easy for you:
SQL> Spool All_parameters.txt
set linesize 170
col Parameter for a50
col "Session Value" for a40
col "Instance Value" for a40
SELECT a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE a.indx = b.indx AND a.indx = c.indx order by 1;
Spool off
I also strongly recommend to reset the hidden parameter to its default values before starting the upgrade, you don't want to get a weird problems during the upgrade and you don't know from where they came up.
#####################
PRE-UPGRADE STEPS:
#####################
Step 1: Software Installation
#####
Covered in Part I.
Install 11.2.0.3 RDBMS Software into a new ORACLE_HOME.
=>You only need to install examples cd if your DB is using Oracle Text Themes or you intend to install Multimedia demos.
Apply the latest PSU patch on the 11.2.0.3 ORACLE_HOME before upgrading the database.
PSU patch post steps which runs on the database like executing script $ORACLE_HOME/rdbms/admin/catbundle.sql are not required in my case as per 11.2.0.3.4 PSU read-me.
Note that every PSU patch is unique, not all PSU patches exempt running catcpu.sql after the database upgrade, so you have to read the readme file first.
Pre-upgrade information:
=================
Download latest version of utlu112i_5.sql script and execute it: Note ID 884522.1
Note: This script already exist under the new Oracle Home $ORACLE_HOME/rdbms/admin, but usually the one in Metalink is updated with the most recent upgrade checks.
SQL> SPOOL upgrade_info.log
SQL> @new_ORACLE_HOME/rdbms/admin/utlu112i.sql
SQL> SPOOL OFF
Step 2: Dictionary Check
#####
Verify the validity of data dictionary objects by running dbupgdiag.sql script (Download it from NOTE ID 556610.1)
If the dbupgdiag.sql script reports any invalid objects, run utlrp (multiple times) to validate the invalid objects in the database, until there is no change in the number of invalid objects:
SQL> @/home/oracle/dbupgdiag.sql
SQL> @?/rdbms/admin/utlrp.sql
Gather Dictionary Statistics:
---------------------------------
Helps in speeding up upgrade process.
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
Step 3: TIMEZONE Version
#####
In case you have TIMESTAMP WITH TIME ZONE data type in your DB, you need to Upgrade the Time Zone version to version 14.
The possibilities are:
>If current version < 14 ,You need to upgrade to version 14 after you finish the upgrade to 11.2.0.3.
>If current version = 14 ,No need to upgrade, Skip the whole Step.
>If current version > 14 ,You must upgrade your Time Zone version before upgrading to 11.2.0.3 or your data stored in TIMESTAMP WITH TIME ZONE datatype can become corrupted during the upgrade.
Check your current Time Zone version:
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
4
In my case the version is older, so I can do this step later after finalizing the upgrade.
STEP 11 covers this part.
Check National Characterset is UTF8 or AL16UTF16:
=======================================
SQL> select value from NLS_DATABASE_PARAMETERS where parameter = 'NLS_NCHAR_CHARACTERSET';
Step 4: Disable Cluster option
####
Set the parameter cluster_database to FALSE for RAC database.
Step 5: Configure log locations
#####
Change SPFILE parameters to point to the new ORACLE HOME:
-------------------------------------------------------------------------------
# mkdir -p /u02/oracle/ora11g/11.2.0.3/diagnostics/orcl
SQL> ALTER SYSTEM SET diagnostic_dest = '/u02/oracle/ora11g/11.2.0.3/diagnostics/orcl';
SQL> ALTER SYSTEM SET audit_file_dest = '/u02/oracle/ora11g/11.2.0.3/rdbms/audit' SCOPE=SPFILE;
Step 6: New Environment Variables
#####
Make sure the following Linux environment variables are pointing to the new 11.2.0.3 ORACLE_HOME:
(ORACLE_BASE, ORACLE_HOME, PATH, NLS_10 and LIBRARY_PATH).
Step 7: Modify | Move configuration files
#####
>Make sure that entries inside /etc/oratab file are pointing to the new 11.2.0.3 ORACLE_HOME, hash the original entries pointing to 11.2.0.1 ORACLE_HOME:
e.g.
#pefms:/u01/oracle/ora11gr2/11.2.0.1:Y
pefms:/u02/oracle/ora11g/11.2.0.3:Y
> Copy SPFILE & Password File to the new ORACLE_HOME:
---------------------------------------------------------------------------
# cd /u01/oracle/ora11gr2/11.2.0.1/dbs
# cp spfile* orapw* /u02/oracle/ora11g/11.2.0.3/dbs/
> Copy network configuration files to the new 11.2.0.3 $TNS_ADMIN directory:
-----------------------------------------------------------------------------------------------
# cd /u01/oracle/ora11gr2/11.2.0.1/network/admin
# cp tnsnames.ora listener.ora sqlnet.ora /u02/oracle/ora11g/11.2.0.3/network/admin/
> Copy DB Control EM directories to the new 11.2.0.3 ORACLE_HOME:
---------------------------------------------------------------------------------------
# $ORACLE_HOME/
# $ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_
# $ORACLE_HOME/owb/bin/admin
> Copy SQLPLUS settings file:
-----------------------------------
# cp /u01/oracle/ora11gr2/11.2.0.1/sqlplus/admin/glogin.sql /u02/oracle/ora11g/11.2.0.3/sqlplus/admin/
Step 8: Disable Vault | Adjust parameters for JVM
#####
>Disable Database Vault if enabled.
>IF JVM installed, java_pool_size and shared_pool_size must be set to at least 250MB prior to the upgrade.
Latest checks:
==========
SQL> SELECT * FROM v$recover_file; no rows selected
SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE'; no rows selected
SQL> SELECT * FROM dba_2pc_pending; --outstanding distributed transactions no rows selected
SQL> SELECT name FROM sys.user$ WHERE ext_username IS NOT NULL AND password = 'GLOBAL';
no rows selected
Disable all batch and cron jobs:
---------------------------------
Disable all crontab scripts:
# crontab /root/crontab_root
Step 9: Set the Database in the Noarchivelog mode.
#####
>Stop the listener: lsnrctl stop
>Stop DBCONSOLE: emctl stop dbconsole
Putting the database in the noarchivelog shrink the upgrade time. .
$ sqlplus "/as sysdba"
SQL> shutdown immediate;
SQL> startup mount
SQL> alter database noarchivelog;
SQL> archive log stop;
SQL> shutdown immediate;
YOU MUST TAKE A COLD BACKUP AT THIS STAGE FOR FALLBACK PLAN.
#################
UPGRADE STEPS:
#################
Note: The database should be opened from the old ORACLE_HOME before running DBUA.
Note: If Oracle clusterware installed, it needs to be UP and running before starting DBUA.
There are many ways Oracle provide to upgrade your database, I'll discuss here three of them (DBUA, Silent, Manual) and it's up to you to select the suitable way for your implementation.
DBUA Way: (GUI)
========
Ensure that dbua is running from the new ORACLE_HOME:
# which dbua
Run the DBUA by Oracle User:
# dbua
=>First screen: introduction .
=>2nd screen: Select the database name.
=>3rd screen: Check the following:
-Recompile invalid objects at the end of upgrade.
-Turn off Archiving for the duration of upgrade.
-Upgrade Timezone version and TIMESTAMP WITH TIMEZONE data.
=>4th screen: keep the default.
=>5th screen: keep the default.
=>6th screen: keep the default.
=>7th screen: Put password for DBSNMP,SYSMAN.
=>8th screen: Select the DB listener.
=>9th screen: Finish.
Notes: Continue to Post upgrade steps but skip these two steps (Upgrade the Timezone version & Configure EM) as they are automatically configured by DBCA.
Silent Way:
========
In case you want to perform the upgrade using DBUA but you cannot forward X11 packets due to firewall rule or other reason, you can use the silent mode which is Faster and doesn't requires X11 packet forwarding:
By Oracle user run this command:
# dbua -silent
-sid orcl
-autoextendFiles
-upgradeTimezone
-recompile_invalid_objects true
-degree_of_parallelism 4
-emConfiguration LOCAL
-dbsnmpPassword <password>
-sysmanPassword <password>
Outputs will be like the following:
Log files for the upgrade operation are located at: /u02/oracle/cfgtoollogs/dbua/bkpefms/upgrade1
Performing Pre Upgrade
1% complete
7% complete
Upgrading Oracle Server
....
Upgrading JServer JAVA Virtual Machine
22% complete
....
85% complete
Upgrading Timezone
....
92% complete
....
Generating Summary
100% complete
Check the log file "/u02/oracle/cfgtoollogs/dbua/logs/silent2.log" for upgrade details.
The Manual Way: (The Way I Prefer)
=============
Run 1123 profile which points to the new 11.2.0.3 ORACLE_HOME locations:
# cd /home/oracle
# . .bash_profile11203
If you didn't create this file yet you can copy the .bash_profile, renaming the new file to .bash_profile11203 ,edit the file by replacing the old ORACLE_HOME path with the new ORACLE_HOME path which is /u02/oracle/ora11g/11.2.0.3 in my setup, then replace the old ORACLE_BASE with the new ORACLE_BASE which is /u02/oracle in my setup.
Step 10: Execute the upgrade script
######
# export ORACLE_SID=orcl
# sqlplus / as sysdba
SQL> set echo on
SQL> SPOOL upgrade.log
SQL> startup upgrade
SQL> @?/rdbms/admin/catupgrd.sql
SQL> spool off
SQL> Shutdown immediate
Note: If you encounter a message listing obsolete initialization parameters during startup upgrade,remove the obsolete parameters from the PFILE.
Note: You can re-run the catupgrd.sql script as many times as necessary. IF you experience errors during the upgrade fix it first then:
1)Shu immediate
2)Startup Upgrade
3)@?/rdbms/admin/catupgrd.sql
> Check the spool file for errors.
> Restart the database in normal mode.
When upgrade script is done, run the following scrips:
SQL> Startup
SQL> @?/rdbms/admin/utlu112s.sql
SQL> @?/rdbms/admin/catuppst.sql
SQL> @?/rdbms/admin/utlrp.sql
SQL> @?/rdbms/admin/utluiobj.sql --Checks invalid objects after the upgrade.
Run dbupgdiag.sql script (See note: 556610.1) and verify that all the components in dba_registry are valid and there are no invalid objects in dba_objects.
SQL>@/home/oracle/dbupgdiag.sql
Modify listener.ora:
==============
In the listener.ora file, modify the ORACLE_HOME path to the new 11.2.0.3 ORACLE_HOME:
Ex:
vi /u02/oracle/ora11g/11.2.0.3/network/admin/listener.ora
LISTENER_orcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora-dev1)(PORT = 1521))
)
)
SID_LIST_LISTENER_orcl =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u02/oracle/ora11g/11.2.0.3)
(SID_NAME = orcl)
)
)
##################
Post Upgrade Steps:
##################
Step 11: Upgrade the TimeZone version
######
Preparation Stage:
=============
SQL> SHU IMMEDIATE
SQL> STARTUP UPGRADE
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION 4
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SQL> alter session set "_with_subquery"=materialize;
SQL> alter session set "_simple_view_merging"=TRUE;
SQL> exec DBMS_DST.BEGIN_PREPARE(14)
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION 4
DST_SECONDARY_TT_VERSION 14
DST_UPGRADE_STATE PREPARE
SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
SQL> TRUNCATE TABLE sys.dst$affected_tables;
SQL> TRUNCATE TABLE sys.dst$error_table;
SQL> set serveroutput on
SQL> BEGIN
DBMS_DST.FIND_AFFECTED_TABLES
(affected_tables => 'sys.dst$affected_tables',
log_errors => TRUE,
log_errors_table => 'sys.dst$error_table');
END;
/
SQL> SELECT * FROM sys.dst$affected_tables;
SQL> SELECT * FROM sys.dst$error_table;
SQL> EXEC DBMS_DST.END_PREPARE;
Upgrade Stage:
===========
SQL> purge dba_recyclebin;
SQL> alter session set "_with_subquery"=materialize;
SQL> alter session set "_simple_view_merging"=TRUE;
SQL> EXEC DBMS_DST.BEGIN_UPGRADE(14);
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION 14
DST_SECONDARY_TT_VERSION 4
DST_UPGRADE_STATE UPGRADE
SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';
no rows selected | I got 14 rows selected.
SQL> shutdown immediate
SQL> startup
SQL> alter session set "_with_subquery"=materialize;
SQL> alter session set "_simple_view_merging"=TRUE;
SQL> set serveroutput on
VAR numfail number
BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
parallel => TRUE,
log_errors => TRUE,
log_errors_table => 'SYS.DST$ERROR_TABLE',
log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
error_on_overlap_time => FALSE,
error_on_nonexisting_time => FALSE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/
.
.
.
Failures:0
SQL> VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/
An upgrade window has been successfully ended.
Failures:0
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION 14
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SQL> SELECT * FROM v$timezone_file;
FILENAME VERSION
-------------------- ----------
timezlrg_14.dat 14
SQL> select TZ_VERSION from registry$database;
TZ_VERSION
----------
4
SQL> update registry$database set TZ_VERSION = (select version FROM v$timezone_file);
SQL> commit;
SQL> select TZ_VERSION from registry$database;
TZ_VERSION
----------
14
SQL> SELECT value$ FROM sys.props$ WHERE NAME = 'DST_PRIMARY_TT_VERSION';
VALUE$
--------
14
STEP 12: Set CLUSTER_DATABASE=TRUE
######
If your DB is a RAC one, set CLUSTER_DATABASE=TRUE
I've explained a full implementation (cook book) of upgrading a 11.2.0.1 to 11.2.0.3 RAC database on a new servers (out-place upgrade) in this link:
http://dba-tips.blogspot.ae/2013/09/upgrade-rac-11201-to-11203-part-i.html
STEP 13: Upgrade the Recovery Catalog
#######
If you're using the Recovery Catalog to backup your database you have to upgrade tha catalog DB:
A) Connect to the catalog DB through RMAN:
RMAN> CONNECT CATALOG username/password@catalog_DB
B) Execute this command two times:
RMAN> UPGRADE CATALOG;
STEP 14: Upgrade Statistics Tables
#######
Statistics tables are tables store the statistics for DB or schema or tables to be restored later, or to be imported on another database usually for testing purposes.
If you created statistics tables before using the DBMS_STATS.CREATE_STAT_TABLE, then upgrade each statistics table by running:
e.g.
SQL> EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('scott', 'stat_table');
STEP 15: Enable Database Vault
######
Enable Oracle Database Vault and Revoke the DV_PATCH_ADMIN Role Note 453903.1
STEP 16: Compatible version + Enable Archiving & Flashback mode
#######
Set the compatibility version to the current on, and enable archivelog and flashback modes:
SQL> alter system set compatible='11.2.0.3' scope=spfile;
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database flashback on;
SQL> alter database open;
Re-point the directories to the new ORACLE_HOME:
-----------------------------------------------
As the ORACLE_HOME path became different, The directories that point to old ORACLE_HOME should point to the new ORACLE_HOME:
Example:
SQL> col DIRECTORY_PATH for a80
SQL> SELECT * FROM dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
--------------- ------------------------------ ------------------------------------------
SYS QUEST_SOO_UDUMP_DIR /u01/oracle/ora11g/11.2.0.1/diagnostics/orcl/diag/rdbms/orcl/orcl/trace/
SQL> create or replace directory QUEST_SOO_UDUMP_DIR as '/u02/oracle/ora11g/11.2.0.3/diagnostics/orcl/diag/rdbms/orcl/orcl/trace/';
STEP 17: Enable Cron & DB Jobs:
######
Enable Crontab Jobs.
Enable DB jobs:
SQL> EXEC dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','FALSE');
STEP 18: Configure the Enterprise Manager
######
WARNING:
##########
During recreating or dropping the repository, this requires the database to be in quiesce mode. This means that, temporarily, no operations are possible on the database and new users cannot login.
If this mistakenly done during business hours, do the following:
1. Kill the emca command
2. SQL > alter system unquiesce;
Configure EM Using DBCA:
======================
Flush any previous failed attempt to configure EM:
---------------------------------------------------------
From sqlplus by sysdba: alter user dbsnmp identified by "xxx";
drop user sysman cascade;
drop public synonym SETEMVIEWUSERCONTEXT;
drop role MGMT_USER;
drop PUBLIC SYNONYM MGMT_TARGET_BLACKOUTS;
drop user MGMT_VIEW;
drop type sys.MGMT_MNTR_USER_STATS_ARRAY;
drop type sys.HA_HOST_CREDS_ARR;
Then run this command from Linux shell by Oracle user:
# emca -deconfig dbcontrol db -repos drop
Configure EM:
--------------
By Oracle User:
# emctl stop dbconsole
# dbca
--> Configure Database Options
--> ... select: register this database with selected listeners only --> .. --> Put complicated password for SYSMAN and DBSNMP like abcde.$1234 --> keep selecting the defaults.
How To start multiple EM Agents On same server: ORACLE_UNQNAME
-----------------------------------------------------------
Note: We have more than one DB on DEV server, each DB will have a unique EM port assigned to it, before starting or stopping EM agent for each DB you have to export the environment variable ORACLE_UNQNAME first:
e.g. starting EM agent for orcl3:
# export ORACLE_UNQNAME=orcl3
# emctl start dbconsole
>>>>>>>>>>>>>>>>>>>>
THE UPGRADE IS DONE
>>>>>>>>>>>>>>>>>>>>
The following are Optional STEPS: Good To Do
############################
STEP 20: Rebuild Unusable Indexes & Gather Statistics
#######
Rebuild Unusable indexes:
===================
SQL> select 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD ONLINE;'from dba_indexes where status ='UNUSABLE';
Gather FIXED OBJECTS stats: (Do it during peak hours not within the downtime)
======================
Fixed objects are the x$ tables (been loaded in SGA during startup) on which V$ views are built (V$SQL etc.).
Fixed Object statistics are not being gathered automatically nor within gather DB stats procedure.
If the statistics are not gathered on fixed objects, the Optimizer will use predefined default values for the statistics. These defaults may lead to inaccurate execution plans.
Statistics on fixed objects are not being gathered automatically nor within gathering DB stats.
Gather the fixed objects stats:
---------------------------------
SQL> exec dbms_stats.gather_fixed_objects_stats;
Gather DICTIONARY stats:
=====================
Dictionary stats are gathered on dictionary tables owned by SYS and resides in the system tablespace.
SQL> Exec DBMS_STATS.GATHER_DICTIONARY_STATS ();
Gather database statistics:
==================
SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,degree => 8);
In case you need to gather DB Stats + Histograms on all skewed columns:
SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT=>100,METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY',cascade => TRUE,degree => 8);
ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE => Let Oracle estimate skewed values always gives excellent results.
Removed "METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY'" => As histograms is not recommended to gathered on all columns.
Removed "cascade => TRUE" => To let Oracle determine whether index statistics to be collected or not.
For more information about gathering statistics on the database I strongly recommend you to read this post:
http://dba-tips.blogspot.ae/2012/11/all-about-statistics-in-oracle.html
Step 21: Check Oracle Recommended Patches:
######
Note ID 756671.1 includes the recommended patches for releases starting from 10.2.0.3.
Note ID 742060.1 represents the most accurate information Oracle can provide for coming releases.
The difference between PSU patch and CPU (SPU) patch : [ID 854428.1]
=========================================
-PSU patch is the fifth digit in the release like 11.2.0.1.1 it's being published each 4 months in the same moths of releasing CPU patch (Jan,Apr,Jun,Oct).
-PSU patch includes (CPU patches + common Bug fixes which affect a large number of customers).
-Once you start to apply PSU patch you can't apply CPU patch again (this is what Oracle recommends but it possible to apply CPU patch after apply PSU patches).
-PSU patches are cumulative same like CPU once you applied the latest patch the old ones are included in that patch.
-When downloading PSU patch there is (PSU & GI PSU), GI PSU is for Grid Infrastructure (this apply the patch for GI and database) the other one PSU (only apply the patch for the database).
-Starting from October 2012 Oracle re-named CPU Critical Patch Update to SPU Security Patch Update, both are same, it's just a renaming .
*You can check the latest applied patches on the database by running this query:
SQL> select * from DBA_REGISTRY_HISTORY;
//////////////////////////////////////////////*
Removing OLD ORACLE_HOME: (Optional, you can do it later)
=========================
After you feel confident with the new Oracle installation and you will never downgrade to the previous release, remove the ORACLE_HOME:
Detach old ORACLE_HOME:
# $OLD_HOME/oui/bin/runInstaller -detachHome -silent -local
Confirm old ORACLE_HOME is removed from central inventory:
# $OLD_HOME/OPatch/opatch lsinventory -all
Remove files in old ORACLE_HOME manually:
# rm -rf $OLD_HOME
*////////////////////////////////////////////
In this part I'll discuss database upgrade steps from 11.2.0.1 to 11.2.0.3, I already covered 11.2.0.3 database software installation steps on a server already running 11.2.0.1 database using out-place method in Part I : environment preparation & 11.2.0.3 Software Installation.
In case you're interested in RAC database upgrade from 11.2.0.1 to 11.2.0.3 I've explained a full implementation (cook book) of upgrading a 11.2.0.1 to 11.2.0.3 RAC database on a new servers (out-place upgrade) in this link:
http://dba-tips.blogspot.ae/2013/09/upgrade-rac-11201-to-11203-part-i.html
Important Notes:
Metalink [ID 730365.1] Includes all patchset downloads + How to upgrade from any Oracle DB version to another one.
Metalink [ID 1276368.1] Out-of-place manual upgrade from previous 11.2.0.N version to the latest 11.2.0.N patchset.
Tips Before Starting The Upgrade Process:
##################################
Backup & Truncate Audit Table SYS.AUD$:
-------------------------------------------------
Truncating SYS.AUD$ table will speed up the upgrade process:
--Backing up + compressing audit data in SYS.AUD_BKP table:
SQL> create table SYS.AUD_BKP COMPRESS as select * from SYS.AUD$;
SQL> Truncate table SYS.AUD$;
Purge DBA_RECYCLEBIN:
---------------------------
Purging DBA_RECYCLEBIN will speed up the upgrade process:
SQL> PURGE DBA_RECYCLEBIN;
Save a backup of all DB Parameters (Visible & hidden):
------------------------------------------------------------------
This will help you in troubleshooting issues coming after the upgrade (bugs, performance, or whatever the problem is).
The following statement will make it easy for you:
SQL> Spool All_parameters.txt
set linesize 170
col Parameter for a50
col "Session Value" for a40
col "Instance Value" for a40
SELECT a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE a.indx = b.indx AND a.indx = c.indx order by 1;
Spool off
I also strongly recommend to reset the hidden parameter to its default values before starting the upgrade, you don't want to get a weird problems during the upgrade and you don't know from where they came up.
#####################
PRE-UPGRADE STEPS:
#####################
Step 1: Software Installation
#####
Covered in Part I.
Install 11.2.0.3 RDBMS Software into a new ORACLE_HOME.
=>You only need to install examples cd if your DB is using Oracle Text Themes or you intend to install Multimedia demos.
Apply the latest PSU patch on the 11.2.0.3 ORACLE_HOME before upgrading the database.
PSU patch post steps which runs on the database like executing script $ORACLE_HOME/rdbms/admin/catbundle.sql are not required in my case as per 11.2.0.3.4 PSU read-me.
Note that every PSU patch is unique, not all PSU patches exempt running catcpu.sql after the database upgrade, so you have to read the readme file first.
Pre-upgrade information:
=================
Download latest version of utlu112i_5.sql script and execute it: Note ID 884522.1
Note: This script already exist under the new Oracle Home $ORACLE_HOME/rdbms/admin, but usually the one in Metalink is updated with the most recent upgrade checks.
SQL> SPOOL upgrade_info.log
SQL> @new_ORACLE_HOME/rdbms/admin/utlu112i.sql
SQL> SPOOL OFF
Step 2: Dictionary Check
#####
Verify the validity of data dictionary objects by running dbupgdiag.sql script (Download it from NOTE ID 556610.1)
If the dbupgdiag.sql script reports any invalid objects, run utlrp (multiple times) to validate the invalid objects in the database, until there is no change in the number of invalid objects:
SQL> @/home/oracle/dbupgdiag.sql
SQL> @?/rdbms/admin/utlrp.sql
Gather Dictionary Statistics:
---------------------------------
Helps in speeding up upgrade process.
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
Step 3: TIMEZONE Version
#####
In case you have TIMESTAMP WITH TIME ZONE data type in your DB, you need to Upgrade the Time Zone version to version 14.
The possibilities are:
>If current version < 14 ,You need to upgrade to version 14 after you finish the upgrade to 11.2.0.3.
>If current version = 14 ,No need to upgrade, Skip the whole Step.
>If current version > 14 ,You must upgrade your Time Zone version before upgrading to 11.2.0.3 or your data stored in TIMESTAMP WITH TIME ZONE datatype can become corrupted during the upgrade.
Check your current Time Zone version:
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
4
In my case the version is older, so I can do this step later after finalizing the upgrade.
STEP 11 covers this part.
Check National Characterset is UTF8 or AL16UTF16:
=======================================
SQL> select value from NLS_DATABASE_PARAMETERS where parameter = 'NLS_NCHAR_CHARACTERSET';
Step 4: Disable Cluster option
####
Set the parameter cluster_database to FALSE for RAC database.
Step 5: Configure log locations
#####
Change SPFILE parameters to point to the new ORACLE HOME:
-------------------------------------------------------------------------------
# mkdir -p /u02/oracle/ora11g/11.2.0.3/diagnostics/orcl
SQL> ALTER SYSTEM SET diagnostic_dest = '/u02/oracle/ora11g/11.2.0.3/diagnostics/orcl';
SQL> ALTER SYSTEM SET audit_file_dest = '/u02/oracle/ora11g/11.2.0.3/rdbms/audit' SCOPE=SPFILE;
Step 6: New Environment Variables
#####
Make sure the following Linux environment variables are pointing to the new 11.2.0.3 ORACLE_HOME:
(ORACLE_BASE, ORACLE_HOME, PATH, NLS_10 and LIBRARY_PATH).
Step 7: Modify | Move configuration files
#####
>Make sure that entries inside /etc/oratab file are pointing to the new 11.2.0.3 ORACLE_HOME, hash the original entries pointing to 11.2.0.1 ORACLE_HOME:
e.g.
#pefms:/u01/oracle/ora11gr2/11.2.0.1:Y
pefms:/u02/oracle/ora11g/11.2.0.3:Y
> Copy SPFILE & Password File to the new ORACLE_HOME:
---------------------------------------------------------------------------
# cd /u01/oracle/ora11gr2/11.2.0.1/dbs
# cp spfile* orapw* /u02/oracle/ora11g/11.2.0.3/dbs/
> Copy network configuration files to the new 11.2.0.3 $TNS_ADMIN directory:
-----------------------------------------------------------------------------------------------
# cd /u01/oracle/ora11gr2/11.2.0.1/network/admin
# cp tnsnames.ora listener.ora sqlnet.ora /u02/oracle/ora11g/11.2.0.3/network/admin/
> Copy DB Control EM directories to the new 11.2.0.3 ORACLE_HOME:
---------------------------------------------------------------------------------------
# $ORACLE_HOME/
# $ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_
# $ORACLE_HOME/owb/bin/admin
> Copy SQLPLUS settings file:
-----------------------------------
# cp /u01/oracle/ora11gr2/11.2.0.1/sqlplus/admin/glogin.sql /u02/oracle/ora11g/11.2.0.3/sqlplus/admin/
Step 8: Disable Vault | Adjust parameters for JVM
#####
>Disable Database Vault if enabled.
>IF JVM installed, java_pool_size and shared_pool_size must be set to at least 250MB prior to the upgrade.
Latest checks:
==========
SQL> SELECT * FROM v$recover_file; no rows selected
SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE'; no rows selected
SQL> SELECT * FROM dba_2pc_pending; --outstanding distributed transactions no rows selected
SQL> SELECT name FROM sys.user$ WHERE ext_username IS NOT NULL AND password = 'GLOBAL';
no rows selected
Disable all batch and cron jobs:
---------------------------------
Disable all crontab scripts:
# crontab /root/crontab_root
# crontab /dev/null
# crontab -l
# crontab -l > /home/oracle/oracle_crontab
# crontab /dev/null
# crontab -l
# crontab -l
# crontab -l > /home/oracle/oracle_crontab
# crontab /dev/null
# crontab -l
Disable DB jobs:
SQL> EXEC dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','TRUE');SQL> alter system set job_queue_processes=0 scope=both;Step 9: Set the Database in the Noarchivelog mode.
#####
>Stop the listener: lsnrctl stop
>Stop DBCONSOLE: emctl stop dbconsole
Putting the database in the noarchivelog shrink the upgrade time. .
$ sqlplus "/as sysdba"
SQL> shutdown immediate;
SQL> startup mount
SQL> alter database noarchivelog;
SQL> archive log stop;
SQL> shutdown immediate;
YOU MUST TAKE A COLD BACKUP AT THIS STAGE FOR FALLBACK PLAN.
#################
UPGRADE STEPS:
#################
Note: The database should be opened from the old ORACLE_HOME before running DBUA.
Note: If Oracle clusterware installed, it needs to be UP and running before starting DBUA.
There are many ways Oracle provide to upgrade your database, I'll discuss here three of them (DBUA, Silent, Manual) and it's up to you to select the suitable way for your implementation.
DBUA Way: (GUI)
========
Ensure that dbua is running from the new ORACLE_HOME:
# which dbua
Run the DBUA by Oracle User:
# dbua
=>First screen: introduction .
=>2nd screen: Select the database name.
=>3rd screen: Check the following:
-Recompile invalid objects at the end of upgrade.
-Turn off Archiving for the duration of upgrade.
-Upgrade Timezone version and TIMESTAMP WITH TIMEZONE data.
=>4th screen: keep the default.
=>5th screen: keep the default.
=>6th screen: keep the default.
=>7th screen: Put password for DBSNMP,SYSMAN.
=>8th screen: Select the DB listener.
=>9th screen: Finish.
Notes: Continue to Post upgrade steps but skip these two steps (Upgrade the Timezone version & Configure EM) as they are automatically configured by DBCA.
Silent Way:
========
In case you want to perform the upgrade using DBUA but you cannot forward X11 packets due to firewall rule or other reason, you can use the silent mode which is Faster and doesn't requires X11 packet forwarding:
By Oracle user run this command:
# dbua -silent
-sid orcl
-autoextendFiles
-upgradeTimezone
-recompile_invalid_objects true
-degree_of_parallelism 4
-emConfiguration LOCAL
-dbsnmpPassword <password>
-sysmanPassword <password>
Outputs will be like the following:
Log files for the upgrade operation are located at: /u02/oracle/cfgtoollogs/dbua/bkpefms/upgrade1
Performing Pre Upgrade
1% complete
7% complete
Upgrading Oracle Server
....
Upgrading JServer JAVA Virtual Machine
22% complete
....
85% complete
Upgrading Timezone
....
92% complete
....
Generating Summary
100% complete
Check the log file "/u02/oracle/cfgtoollogs/dbua/logs/silent2.log" for upgrade details.
Notes:
Continue to Post upgrade steps but skip these two steps (Upgrade the
Timezone version & Configure EM) as they are already configured by
DBCA.
=============
Run 1123 profile which points to the new 11.2.0.3 ORACLE_HOME locations:
# cd /home/oracle
# . .bash_profile11203
If you didn't create this file yet you can copy the .bash_profile, renaming the new file to .bash_profile11203 ,edit the file by replacing the old ORACLE_HOME path with the new ORACLE_HOME path which is /u02/oracle/ora11g/11.2.0.3 in my setup, then replace the old ORACLE_BASE with the new ORACLE_BASE which is /u02/oracle in my setup.
Step 10: Execute the upgrade script
######
# export ORACLE_SID=orcl
# sqlplus / as sysdba
SQL> set echo on
SQL> SPOOL upgrade.log
SQL> startup upgrade
SQL> @?/rdbms/admin/catupgrd.sql
SQL> spool off
SQL> Shutdown immediate
Note: If you encounter a message listing obsolete initialization parameters during startup upgrade,remove the obsolete parameters from the PFILE.
Note: You can re-run the catupgrd.sql script as many times as necessary. IF you experience errors during the upgrade fix it first then:
1)Shu immediate
2)Startup Upgrade
3)@?/rdbms/admin/catupgrd.sql
> Check the spool file for errors.
> Restart the database in normal mode.
When upgrade script is done, run the following scrips:
SQL> Startup
SQL> @?/rdbms/admin/utlu112s.sql
SQL> @?/rdbms/admin/catuppst.sql
SQL> @?/rdbms/admin/utlrp.sql
SQL> @?/rdbms/admin/utluiobj.sql --Checks invalid objects after the upgrade.
Run dbupgdiag.sql script (See note: 556610.1) and verify that all the components in dba_registry are valid and there are no invalid objects in dba_objects.
SQL>@/home/oracle/dbupgdiag.sql
Modify listener.ora:
==============
In the listener.ora file, modify the ORACLE_HOME path to the new 11.2.0.3 ORACLE_HOME:
Ex:
vi /u02/oracle/ora11g/11.2.0.3/network/admin/listener.ora
LISTENER_orcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora-dev1)(PORT = 1521))
)
)
SID_LIST_LISTENER_orcl =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u02/oracle/ora11g/11.2.0.3)
(SID_NAME = orcl)
)
)
##################
Post Upgrade Steps:
##################
Step 11: Upgrade the TimeZone version
######
Preparation Stage:
=============
SQL> SHU IMMEDIATE
SQL> STARTUP UPGRADE
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION 4
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SQL> alter session set "_with_subquery"=materialize;
SQL> alter session set "_simple_view_merging"=TRUE;
SQL> exec DBMS_DST.BEGIN_PREPARE(14)
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION 4
DST_SECONDARY_TT_VERSION 14
DST_UPGRADE_STATE PREPARE
SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
SQL> TRUNCATE TABLE sys.dst$affected_tables;
SQL> TRUNCATE TABLE sys.dst$error_table;
SQL> set serveroutput on
SQL> BEGIN
DBMS_DST.FIND_AFFECTED_TABLES
(affected_tables => 'sys.dst$affected_tables',
log_errors => TRUE,
log_errors_table => 'sys.dst$error_table');
END;
/
SQL> SELECT * FROM sys.dst$affected_tables;
SQL> SELECT * FROM sys.dst$error_table;
SQL> EXEC DBMS_DST.END_PREPARE;
Upgrade Stage:
===========
SQL> purge dba_recyclebin;
SQL> alter session set "_with_subquery"=materialize;
SQL> alter session set "_simple_view_merging"=TRUE;
SQL> EXEC DBMS_DST.BEGIN_UPGRADE(14);
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION 14
DST_SECONDARY_TT_VERSION 4
DST_UPGRADE_STATE UPGRADE
SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';
no rows selected | I got 14 rows selected.
SQL> shutdown immediate
SQL> startup
SQL> alter session set "_with_subquery"=materialize;
SQL> alter session set "_simple_view_merging"=TRUE;
SQL> set serveroutput on
VAR numfail number
BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
parallel => TRUE,
log_errors => TRUE,
log_errors_table => 'SYS.DST$ERROR_TABLE',
log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
error_on_overlap_time => FALSE,
error_on_nonexisting_time => FALSE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/
.
.
.
Failures:0
SQL> VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/
An upgrade window has been successfully ended.
Failures:0
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION 14
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SQL> SELECT * FROM v$timezone_file;
FILENAME VERSION
-------------------- ----------
timezlrg_14.dat 14
SQL> select TZ_VERSION from registry$database;
TZ_VERSION
----------
4
SQL> update registry$database set TZ_VERSION = (select version FROM v$timezone_file);
SQL> commit;
SQL> select TZ_VERSION from registry$database;
TZ_VERSION
----------
14
SQL> SELECT value$ FROM sys.props$ WHERE NAME = 'DST_PRIMARY_TT_VERSION';
VALUE$
--------
14
STEP 12: Set CLUSTER_DATABASE=TRUE
######
If your DB is a RAC one, set CLUSTER_DATABASE=TRUE
I've explained a full implementation (cook book) of upgrading a 11.2.0.1 to 11.2.0.3 RAC database on a new servers (out-place upgrade) in this link:
http://dba-tips.blogspot.ae/2013/09/upgrade-rac-11201-to-11203-part-i.html
STEP 13: Upgrade the Recovery Catalog
#######
If you're using the Recovery Catalog to backup your database you have to upgrade tha catalog DB:
A) Connect to the catalog DB through RMAN:
RMAN> CONNECT CATALOG username/password@catalog_DB
B) Execute this command two times:
RMAN> UPGRADE CATALOG;
STEP 14: Upgrade Statistics Tables
#######
Statistics tables are tables store the statistics for DB or schema or tables to be restored later, or to be imported on another database usually for testing purposes.
If you created statistics tables before using the DBMS_STATS.CREATE_STAT_TABLE, then upgrade each statistics table by running:
e.g.
SQL> EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('scott', 'stat_table');
STEP 15: Enable Database Vault
######
Enable Oracle Database Vault and Revoke the DV_PATCH_ADMIN Role Note 453903.1
STEP 16: Compatible version + Enable Archiving & Flashback mode
#######
Set the compatibility version to the current on, and enable archivelog and flashback modes:
SQL> alter system set compatible='11.2.0.3' scope=spfile;
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database flashback on;
SQL> alter database open;
Re-point the directories to the new ORACLE_HOME:
-----------------------------------------------
As the ORACLE_HOME path became different, The directories that point to old ORACLE_HOME should point to the new ORACLE_HOME:
Example:
SQL> col DIRECTORY_PATH for a80
SQL> SELECT * FROM dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
--------------- ------------------------------ ------------------------------------------
SYS QUEST_SOO_UDUMP_DIR /u01/oracle/ora11g/11.2.0.1/diagnostics/orcl/diag/rdbms/orcl/orcl/trace/
SQL> create or replace directory QUEST_SOO_UDUMP_DIR as '/u02/oracle/ora11g/11.2.0.3/diagnostics/orcl/diag/rdbms/orcl/orcl/trace/';
######
Enable Crontab Jobs.
Enable DB jobs:
SQL> EXEC dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','FALSE');
STEP 18: Configure the Enterprise Manager
######
WARNING:
##########
During recreating or dropping the repository, this requires the database to be in quiesce mode. This means that, temporarily, no operations are possible on the database and new users cannot login.
If this mistakenly done during business hours, do the following:
1. Kill the emca command
2. SQL > alter system unquiesce;
Configure EM Using DBCA:
======================
Flush any previous failed attempt to configure EM:
---------------------------------------------------------
From sqlplus by sysdba: alter user dbsnmp identified by "xxx";
drop user sysman cascade;
drop public synonym SETEMVIEWUSERCONTEXT;
drop role MGMT_USER;
drop PUBLIC SYNONYM MGMT_TARGET_BLACKOUTS;
drop user MGMT_VIEW;
drop type sys.MGMT_MNTR_USER_STATS_ARRAY;
drop type sys.HA_HOST_CREDS_ARR;
Then run this command from Linux shell by Oracle user:
# emca -deconfig dbcontrol db -repos drop
Configure EM:
--------------
By Oracle User:
# emctl stop dbconsole
# dbca
--> Configure Database Options
--> ... select: register this database with selected listeners only --> .. --> Put complicated password for SYSMAN and DBSNMP like abcde.$1234 --> keep selecting the defaults.
-----------------------------------------------------------
Note: We have more than one DB on DEV server, each DB will have a unique EM port assigned to it, before starting or stopping EM agent for each DB you have to export the environment variable ORACLE_UNQNAME first:
e.g. starting EM agent for orcl3:
# export ORACLE_UNQNAME=orcl3
# emctl start dbconsole
>>>>>>>>>>>>>>>>>>>>
THE UPGRADE IS DONE
>>>>>>>>>>>>>>>>>>>>
The following are Optional STEPS: Good To Do
############################
STEP 20: Rebuild Unusable Indexes & Gather Statistics
#######
Rebuild Unusable indexes:
===================
SQL> select 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD ONLINE;'from dba_indexes where status ='UNUSABLE';
Gather FIXED OBJECTS stats: (Do it during peak hours not within the downtime)
======================
Fixed objects are the x$ tables (been loaded in SGA during startup) on which V$ views are built (V$SQL etc.).
Fixed Object statistics are not being gathered automatically nor within gather DB stats procedure.
If the statistics are not gathered on fixed objects, the Optimizer will use predefined default values for the statistics. These defaults may lead to inaccurate execution plans.
Statistics on fixed objects are not being gathered automatically nor within gathering DB stats.
Note:
-It's recommended to Gather the fixed object stats during peak hours (system is busy)
or after the peak hours but the sessions are still connected (even if
they idle), to gurantee that the fixed object tables been populated and
the statistics well represent the DB activity. also note that
performance degradation may be experienced while the statistics are
gathering.
-Having no statistics is better than having a non representative statistics.
---------------------------------
SQL> exec dbms_stats.gather_fixed_objects_stats;
Gather DICTIONARY stats:
=====================
Dictionary stats are gathered on dictionary tables owned by SYS and resides in the system tablespace.
SQL> Exec DBMS_STATS.GATHER_DICTIONARY_STATS ();
Gather database statistics:
==================
SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,degree => 8);
In case you need to gather DB Stats + Histograms on all skewed columns:
SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT=>100,METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY',cascade => TRUE,degree => 8);
ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE => Let Oracle estimate skewed values always gives excellent results.
Removed "METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY'" => As histograms is not recommended to gathered on all columns.
Removed "cascade => TRUE" => To let Oracle determine whether index statistics to be collected or not.
For more information about gathering statistics on the database I strongly recommend you to read this post:
http://dba-tips.blogspot.ae/2012/11/all-about-statistics-in-oracle.html
Step 21: Check Oracle Recommended Patches:
######
Note ID 756671.1 includes the recommended patches for releases starting from 10.2.0.3.
Note ID 742060.1 represents the most accurate information Oracle can provide for coming releases.
The difference between PSU patch and CPU (SPU) patch : [ID 854428.1]
=========================================
-PSU patch is the fifth digit in the release like 11.2.0.1.1 it's being published each 4 months in the same moths of releasing CPU patch (Jan,Apr,Jun,Oct).
-PSU patch includes (CPU patches + common Bug fixes which affect a large number of customers).
-Once you start to apply PSU patch you can't apply CPU patch again (this is what Oracle recommends but it possible to apply CPU patch after apply PSU patches).
-PSU patches are cumulative same like CPU once you applied the latest patch the old ones are included in that patch.
-When downloading PSU patch there is (PSU & GI PSU), GI PSU is for Grid Infrastructure (this apply the patch for GI and database) the other one PSU (only apply the patch for the database).
-Starting from October 2012 Oracle re-named CPU Critical Patch Update to SPU Security Patch Update, both are same, it's just a renaming .
*You can check the latest applied patches on the database by running this query:
SQL> select * from DBA_REGISTRY_HISTORY;
//////////////////////////////////////////////*
Removing OLD ORACLE_HOME: (Optional, you can do it later)
=========================
After you feel confident with the new Oracle installation and you will never downgrade to the previous release, remove the ORACLE_HOME:
Detach old ORACLE_HOME:
# $OLD_HOME/oui/bin/runInstaller -detachHome -silent -local
Confirm old ORACLE_HOME is removed from central inventory:
# $OLD_HOME/OPatch/opatch lsinventory -all
Remove files in old ORACLE_HOME manually:
# rm -rf $OLD_HOME
*////////////////////////////////////////////
