Out-of-place Upgrading GI and DB from 11.2.0.3 to 11.2.0.4.160719

OS: AIX 7.1

# prerequisites

1 make sure GUI is available for installation
2 make sure /oraapp has at least 50G free space
3 media list

p13390677_112040_AIX64-5L_[1-4]of7.zip # AIX 11.2.0.4
p23274134_112040_AIX64-5L.zip # 11.2.0.4.160719 GI + DB PSU
(or 23615403 combo patch)
p21354456_112040_AIX64-5L.zip # 21354456 one-off patch
p6880880_112000_AIX64-5L.zip # opatch 11.2.0.3.10

# as root on both hosts

mkdir -p /oraapp/grid/11.2.0.4/gridhome
chown grid:oinstall /oraapp/grid/11.2.0.4/gridhome
chown grid:oinstall /oraapp/grid/11.2.0.4

# ssh equivalence requires /bin/bash

ln -s /usr/bin/ksh /bin/bash
ln -s /usr/bin/ssh-keygen /usr/local/bin/ssh-keygen

# make new oracle home directory as user oracle on both hosts

mkdir -p /oraapp/11.2.0.4/oracle/product/dbhome_1

# modify memory_target for ASM instance

sqlplus / as sysasm
show parameter memory_target # if memory_target is less than 1536m, we need to increase it first
alter system set memory_max_target=4096m scope=spfile;
alter system set memory_target=1536m scope=spfile;

# restart GI to validate the above change as user root on both hosts

/oraapp/grid/gridhome/bin/crsctl stop crs
/oraapp/grid/gridhome/bin/crsctl start crs

# upgrade Grid Infrastructure

# as user grid

unset ORACLE_BASE
unset ORACLE_HOME
unset ORACLE_SID
unset ORACLE_UNQNAME

olsnodes # check nodes' names
dev1m
dev2m

# make sure grid user is not locked on both hosts
# check grid part in /etc/security/lastlog
# unzip p13390677_112040_AIX64-5L_[3-4]of7.zip as user grid

cd /install/11.2.0.4/grid
./runcluvfy.sh stage -pre crsinst -upgrade -n dev1m,dev2m -rolling -src_crshome /oraapp/grid/gridhome \
-dest_crshome /oraapp/grid/11.2.0.4/gridhome -dest_version 11.2.0.4.0 -fixup -fixupdir /tmp -verbose
# resolve any error that may occur

# install grid infrastructure 11.2.0.4
# as user grid on host1
# update node name to lower case

/oraapp/grid/gridhome/oui/bin/runInstaller -updateNodelist ORACLE_HOME=/oraapp/grid/gridhome "CLUSTER_NODES={dev1m,dev2m}" CRS=true

# initialize runinstaller

export DISPLAY=[client_host_IP]:0.0
cd /install/11.2.0.4/grid
./runInstaller

# skip software updates
# Upgrade Oracle Grid Infrastructure or Oracle Automatic Storage Management
# add Simplified Chinese and Traditional Chinese
# check the original gridhome path , setup and test ssh equivalence
# asmadmin, asmdba, asmoper
# /oraapp/grid/11.2.0.4/gridhome
# all prechecks passed and Install

# execute on 1st node first as user root, then the 2nd node

cd /oraapp/grid/11.2.0.4
/oraapp/grid/11.2.0.4/gridhome/rootupgrade.sh

# modify ORACLE_HOME in profile to new home for user grid on both hosts

. .profile
sqlplus / as sysasm
select * from v$version; # make sure output is 11.2.0.4

# Upgrade Database

# unzip p13390677_112040_AIX64-5L_[1-2]of7.zip as user oracle

# as user oracle on 1st node

export DISPLAY=[client_host_IP]:0.0
cd /install/11.2.0.4/database
./runInstaller

# Upgrade an existing database
# Oracle Real Application Cluster database installation, setup and test ssh equivalence
# input ORACLE_HOME and ORACLE_BASE
# ignore SCAN warning and press install

# run root.sh

# execute the following steps when DBUA pops up
# modify .profile

cp .profile .profile.`date +%Y%m%d`
vi .profile # modify ORACLE_BASE and ORACLE_HOME

# compile invalid objects first

@?/rdbms/admin/utlrp

set lines 200 pages 200
col object_name for a30
select owner, object_name, object_type
from dba_objects
where status='INVALID'
order by owner, object_name;

purge dba_recyclebin;
exec DBMS_STATS.GATHER_DICTIONARY_STATS();

# verify there are no hidden parameters in pfile
create pfile='/tmp/pfile.ora' from spfile;

# return to DBUA

# Make sure Recompile is ON
# Do NOT specify Fast Recovery Area
# Do NOT Configure EM
# Press Finish


ORA-01031 may be encountered:
ORA-04045: errors during recompilation/revalidation of
DB_MONITOR.TRI_AUDIT_DDL_LHR
ORA-01031: insufficient privileges
ORA-06512: at “SYS.DBMS_UTILITY”, line 1337
ORA-06512: at line 2

we need to execute the following command:
grant administer database trigger to db_monitor;

IMPORTANT: If you terminate the upgrade, but do not restore the database, then you cannot restart DBUA.
Instead, you must continue with a manual (command line) upgrade.

SQL> SHUTDOWN IMMEDIATE;

check the following:
1 $ORACLE_SID
2 oratab points to new oracle home
3 ORACLE_HOME, PATH points to new oracle home
4 Any scripts that clients use to set the ORACLE_HOME value must point to the new Oracle home

# as user grid on both hosts

/oraapp/grid/11.2.0.4/gridhome/bin/setasmgidwrap o=/oraapp/11.2.0.4/oracle/product/dbhome_1/bin/oracle

# as user oracle on both hosts

cp /oraapp/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora /oraapp/11.2.0.4/oracle/product/dbhome_1/network/admin/

# as user oracle on 1st node

. .profile.20160725 # load 11.2.0.3 environment variables
sqlplus / as sysdba
startup nomount;
create pfile='/tmp/pfile.ora' from spfile;
vi /tmp/pfile.ora, modify cluster_database to false
shutdown abort;
startup upgrade pfile='/tmp/pfile.ora';

# run the Pre-Upgrade Information Tool

spool /tmp/upgrade_info.log
@/oraapp/11.2.0.4/oracle/product/dbhome_1/rdbms/admin/utlu112i.sql

SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2
WHERE UPPER(name) ='EVENT' AND isdefault='FALSE';
-- no rows selected

SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2
WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'
-- no rows selected

spool off
SQL> shutdown immediate;

# Start the database using new oracle home

. .profile # load 11.2.0.4 environment variables

sqlplus / as sysdba
SQL> STARTUP UPGRADE pfile='/tmp/pfile.ora';

spool /tmp/upgrade.log
@/oraapp/11.2.0.4/oracle/product/dbhome_1/rdbms/admin/catupgrd.sql
spool off

# no rows return

grep -i error /tmp/upgrade.log | grep -vE "No errors|DOC>|SQL>|--|return|^ *[1-9]|^[1-9]|rem" | more
sqlplus / as sysdba
SQL> STARTUP pfile='/tmp/pfile.ora';

# run Post-Upgrade Status Tool from new oracle home

@?/rdbms/admin/utlu112s.sql

# run catuppst.sql to perform upgrade actions that do not require the database to be in UPGRADE mode

@?/rdbms/admin/catuppst.sql

# compile invalid objects

@?/rdbms/admin/utlrp
cp /oraapp/oracle/product/11.2.0/dbhome_1/dbs/initrmbtdb1.ora /oraapp/11.2.0.4/oracle/product/dbhome_1/dbs/

SQL> startup # startup using spfile

# switch off adaptive log file sync
alter system set "_use_adaptive_log_file_sync"=FALSE;

set lines 200 pages 200
col parameter for a50
col Description for a30
col "session value" for a20
col "instance value" for a20
SELECT a.ksppinm "parameter",
a.ksppdesc "Description",
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
AND a.ksppinm LIKE '/_%' escape '/'
AND a.ksppinm LIKE '/_use_adaptive_log_%' escape '/';

# modify parameters to new oracle home

alter system set core_dump_dest='/oraapp/11.2.0.4/oracle/diag/rdbms/rmbbdb/rmbbdb/cdump' scope=spfile;
alter system set core_dump_dest='/oraapp/11.2.0.4/oracle/diag/rdbms/rmbbdb/rmbbdb/trace' scope=spfile;
alter system set audit_file_dest='/oraapp/11.2.0.4/oracle/diag/rdbms/rmbbdb/rmbbdb/adump' scope=spfile;
alter system set diagnostic_dest='/oraapp/11.2.0.4/oracle' scope=spfile;

shutdown immediate;

# create directory as user oracle on both hosts

mkdir -p /oraapp/11.2.0.4/oracle/diag/rdbms/rmbbdb/rmbbdb/adump

# if you are upgrading a cluster database from releases 10.2, 11.1, or 11.2, then upgrade the database configuration in Oracle Clusterware using the following command:

srvctl upgrade database -d rmbtdb -o /oraapp/11.2.0.4/oracle/product/dbhome_1
srvctl start database -d rmbtdb

vi /etc/oratab, modify to new oracle home

# bounce grid infrastructure

/oraapp/grid/11.2.0.4/gridhome/bin/crsctl stop crs
# kill all processes owned by oracle or grid
/oraapp/grid/11.2.0.4/gridhome/bin/crsctl start crs

# apply 23274134 – GI and DB 11.2.0.4.160719 PSU
# as grid user(execute on both hosts):

unzip p6880880_112000_AIX64-5L.zip
cp -pr OPatch $ORACLE_HOME
opatch version # make sure version is >= 11.2.0.3.6

$ORACLE_HOME/OPatch/ocm/bin/emocmrsp -no_banner -output /tmp/ocm.rsp
--Input Y to confirm that we want to remain uninformed of security issues
# patch GI as user root, on 1st node , then the 2nd node
# before executing opatch auto, we need to periodically clean slib as user root.
# otherwise we may encounter several copy failed errors:
# Copy failed from '/install/11.2.0.4.160719_GIPSU/23274134/23054319/files/lib/libocrutl11.so' to '/oraapp/grid/11.2.0.4/gridhome/lib/libocrutl11.so'...
# lsof and fuser would return no rows. It is still unclear which process loaded those files into system cache.
while true
do
/usr/sbin/slibclean
sleep 1
done
/oraapp/grid/11.2.0.4/gridhome/OPatch/opatch auto /install/11.2.0.4.160719_GIPSU/23274134 -och /oraapp/grid/11.2.0.4/gridhome -ocmrf /tmp/ocm.rsp

# when cluster has stopped, use the following command to verify no open lib files in grid_home/lib

genld -l | grep 11.2.0.4/gridhome/lib # should return no rows
genkld | grep 11.2.0.4/gridhome/lib # should return no rows

# as user grid on both hosts
opatch lsinventory

# patch DB as user root, on 1st node, then the 2nd node

/oraapp/11.2.0.4/oracle/product/dbhome_1/OPatch/opatch auto /install/11.2.0.4.160719_GIPSU/23274134 -oh /oraapp/11.2.0.4/oracle/product/dbhome_1 -ocmrf /tmp/ocm.rsp

# as user oracle on both hosts

opatch lsinventory

# For each database instance running on the Oracle home being patched, connect to the database using SQL*Plus.
# Connect as SYSDBA and run the catbundle.sql script as follows:

cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catbundle.sql psu apply
set lines 200 pages 200
select action_time, version, id from dba_registry_history;

# apply 21354456 one-off patch
# as user oracle on both hosts

unzip p21354456_112040_AIX64-5L.zip

# as user root on 1st node, then 2nd node

/oraapp/grid/11.2.0.4/gridhome/bin/crsctl stop crs

# as user oracle

cd /install/21354456
opatch apply

opatch lsinventory # check one-off patch is applied

# as user root on 1st node, then 2nd node

/oraapp/grid/11.2.0.4/gridhome/bin/crsctl start crs

# reboot host to make sure automatic startup of GI and DB is fine
# as uesr root on both hosts

shutdown -Fr

# Reference:
https://gemsofprogramming.wordpress.com/2014/12/02/upgrade-oracle-rac-grid-database-from-11-2-0-3-0-to-11-2-0-4-0-or-lets-run-the-gauntlet
11.2.0.4 runInstaller: [INS-06006] Passwordless SSH connectivity not set up between the following nodes(s) (DOC ID 1597212.1)
Upgrading to a higher release throws ORA-01031: insufficient privileges (DOC ID 1565816.1)
http://docs.oracle.com/cd/E11882_01/server.112/e23633/upgrade.htm#UPGRD12408
https://oraganism.wordpress.com/2010/02/23/setasmgidwrap-an-illegitimate-google-whack/
Using AIX commands genld, genkld and sibclean to avoid library file locking errors (libjox) (Doc ID 739963.1)
Complete Checklist for out-of-Place Manual Upgrade from Previous 11.2.0.N Version to the Latest 11.2.0.N Patchset (Doc ID 1276368.1)
Things to Consider Before Upgrading to 11.2.0.3/11.2.0.4 Grid Infrastructure/ASM (Doc ID 1363369.1)
Database Will Not Mount: ORA-15025, ORA-27041, ‘Permission denied’, ORA-15081 (Doc ID 1378747.1)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s