Out-of-place Upgrading single instance DB from to

OS: AIX 7.1

# install on new oracle home
# as user oracle on both hosts

mkdir -p /oraapp/

# upgrade an existing database
# Cancel Netca

# modify .profile

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

# compile invalid objects, purge recyclebin and gather dictionary stats


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;

# verify there are no hidden parameters in pfile

create pfile='/tmp/pfile.ora' from spfile; 

# run dbua in new $ORACLE_HOME

# Make sure Recompile is ON
# Do Not Move Database Files as Part of Upgrade
# Do NOT specify Fast Recovery Area
# Do NOT Configure EM
# Press Finish

# patch 23054359 – DB PSU for single instance
# load enviroment variables from new home

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

. .profile
cd /install/23274134/23054359
opatch version
opatch prereq CheckConflictAgainstOHWithDetail -ph ./
lsnrctl stop
sqlplus / as sysdba
shutdown immediate;
opatch apply
cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> @catbundle.sql psu apply
set lines 200 pages 200
select action_time, version, id from dba_registry_history;

# apply 21354456 one-off patch for single instance
# as user oracle

unzip p21354456_112040_AIX64-5L.zip
lsnrctl stop
sqlplus / as sysdba
shutdown immediate;
cd /install/21354456
opatch apply

opatch lsinventory # check one-off patch is applied
cp /oraapp/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora /oraapp/
cp /oraapp/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora /oraapp/
lsnrctl start
sqlplus / as sysdba

# 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 '/';

# run Post-Upgrade Status Tool from new oracle home


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


# compile invalid objects


# modify parameters to new oracle home

alter system set core_dump_dest='/oraapp/' scope=spfile;
alter system set core_dump_dest='/oraapp/' scope=spfile;
alter system set audit_file_dest='/oraapp/' scope=spfile;
alter system set diagnostic_dest='/oraapp/' scope=spfile;

# bounce db instance

mkdir -p /oraapp/
shutdown immediate;

# 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 rmbbdb -o /oraapp/

# reference
Upgrading to a higher release throws ORA-01031: insufficient privileges (DOC ID 1565816.1)
id: 0711-773/0711-783 WARNING while applying patches on IBM AIX (Doc ID 2106821.1)


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