ÖzgürBlog: 2012

How to upgrade 11.2.0.2 ORACLE RAC Database to 11.2.0.3 manually

System detail:

2 server with Red hat Enterprise Linux 5.5
Oracle RAC database + ASM is installed 11.2.0.2 with no PSU applied. (11.2.0.2.0)
GoldenGate is used with this system to another 11.2.0.3 RAC database

I upgrade the 2 node Oracle RAC system from 11.2.0.2 to 11.2.0.3 in 4 main steps:

1. Apply patch 11.2.0.2.6 PSU to both GI (Grid Infrastructure) and database.
2. Upgrade GI to 11.2.0.3 and apply 11.2.0.3.2 PSU with software only mode.
3. Upgrade database to 11.2.0.3.
4. Apply 11.2.0.3.2 PSU to db only.

1. Apply patch 11.2.0.2.6 PSU to both GI (Grid Infrastructure) and database.

Actually applying PSU to 11.2.0.2 is not a prerequisite for 11.2.0.3 upgrade. However there are many bugs related to upgrade process and I want to play it safe.

The patch 13696242 includes both GI and database PSU.

If you use GoldenGate in this system, first of all, you have to disable ddl_trigger and stop the GoldenGate services

List all services:

[oraem@mrctst1 gg]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE mrctst1
ONLINE ONLINE mrctst2
ora.FRALOG.dg
ONLINE ONLINE mrctst1
ONLINE ONLINE mrctst2
ora.LISTENER.lsnr
ONLINE ONLINE mrctst1
ONLINE ONLINE mrctst2
ora.VOTING.dg
ONLINE ONLINE mrctst1
ONLINE ONLINE mrctst2
ora.asm
ONLINE ONLINE mrctst1 Started
ONLINE ONLINE mrctst2
ora.gsd
OFFLINE OFFLINE mrctst1
OFFLINE OFFLINE mrctst2
ora.net1.network
ONLINE ONLINE mrctst1
ONLINE ONLINE mrctst2
ora.ons
ONLINE ONLINE mrctst1
ONLINE ONLINE mrctst2
ora.registry.acfs
ONLINE ONLINE mrctst1
ONLINE ONLINE mrctst2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ggateapp
1    ONLINE ONLINE     mrctst1
mvggatevip
1 ONLINE ONLINE   mrctst1
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE mrctst2
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE mrctst1
ora.LISTENER_SCAN3.lsnr
1 ONLINE ONLINE mrctst1
ora.cvu
1 ONLINE ONLINE mrctst1
ora.mrctst.db
1 ONLINE ONLINE mrctst1 Open
2 ONLINE ONLINE mrctst2 Open
ora.mrctst1.vip
1 ONLINE ONLINE mrctst1
ora.mrctst2.vip
1 ONLINE ONLINE mrctst2
ora.oc4j
1 ONLINE ONLINE mrctst1
ora.scan1.vip
1 ONLINE ONLINE mrctst2
ora.scan2.vip
1 ONLINE ONLINE mrctst1
ora.scan3.vip
1 ONLINE ONLINE mrctst1

Stop GoldenGate services

crsctl stop res ggateapp

crsctl stop res mvggatevip

Disable ddl trigger:

This is must for applying patches to oracle database if you are using GoldenGate:

$ sqlplus / as sysdba

sql> @ddl_disable

Stop Grid agent or dbconsole:

This is also necessary for applying patches to Oracle database. It may cause fail to apply patch.

$ emctl stop agent or dbconsole

Unzip latest opatch to both GI and Db homes:

Opatch version must be 11.2.0.1.5 or later.

Unzip PSU zip file:

Unzip p13696242_112020_Linux-x86-64.zip on both nodes.

Validation of oracle Inventory:

./opatch lsinventory -detail -oh $GI_HOME
./opatch lsinventory -detail -oh $DB_HOME

Check for any conflict:

opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./13696242

opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./13696224

Create an OCM respose file:

Patch asks for this response file at the beginning in order to continue patching process. It is a stupid, nonsense step but you have to create this file:

$GI_HOME/OPatch/ocm/bin/emocmrsp

answer the stupid questions and it creates a file named: ocm.rsp

Apply patch on every node with root user:

# export PATH=$PATH:$GI_HOME/OPatch

# opatch auto PATCH_DIRECTORY

enter the path of stupid ocm.rsp file, when it finishes, apply on second node.

Check whith "opatch lsinventory" command to see applied psu.

Sample "opatch lsinventory" output after applying PSU

[oracle@ractst1 OPatch]$ ./opatch lsinventory

Oracle Interim Patch Installer version 11.2.0.3.0

Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1

Central Inventory : /u01/app/oraInventory

from : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc

OPatch version : 11.2.0.3.0

OUI version : 11.2.0.2.0

Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2012-04-25_13-46-30PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2012-04-25_13-46-30PM.txt

--------------------------------------------------------------------------------

Installed Top-level Products (1):

Oracle Database 11g 11.2.0.2.0

There are 1 products installed in this Oracle Home.

Interim patches (2) :

Patch 13696224 : applied on Wed Apr 25 13:34:48 EEST 2012

Unique Patch ID: 14617280

Patch description: "Database Patch Set Update : 11.2.0.2.6 (13696224)"

Created on 3 Apr 2012, 01:07:52 hrs PST8PDT

Bugs fixed:

13632725, 10031806, 13769501, 13769502, 13769503, 13769504, 9744252

13769505, 9956713, 13769506, 13769507, 9972680, 13769508, 13769509

11853815, 10635701, 9591812, 10127360, 11723722, 9443361, 12846268

12846269, 9707965, 10245086, 9401552, 10039731, 11689702, 13769510

12366627, 10077191, 9829397, 11785938, 10258337, 10264680, 10094823

10209232, 10284570, 8672862, 9672816, 12830339, 9881076, 10621169

10048701, 12569482, 9078442, 11057263, 10322959, 12780098, 12976376

12340939, 11788856, 8223165, 10264696, 10142909, 11800959, 13476583

10052956, 10285022, 10329146, 10332589, 9895207, 9869401, 12828071

9285259, 10229719, 11724984, 10411618, 11670161, 9724970, 10113990

10312847, 11893621, 10200390, 10084145, 10367188, 10285394, 10190642

12586486, 12586487, 10129643, 12586488, 12917230, 12586489, 11866952

10232083, 9715581, 10302581, 11690639, 12423475, 11889177, 10126094

10396041, 10269503, 9970255, 9436324, 12400751, 12589039, 11785390

12586490, 12586491, 12586492, 9795214, 12586493, 10142788, 12586494

12586495, 9905049, 12586496, 11674898, 10419984, 6892311, 11815753

10358019, 12431716, 9906422, 10422126, 13343244, 11937253, 9965655

11890804, 11651810, 9382956, 11067567, 11716621, 10126822, 9869287

9375300, 10155605, 10356782, 10326338, 10165083, 10051315, 13696224

10218814, 13554409, 11076894, 10278773, 11707302, 10230571, 12419321

9966609, 12633340, 12546006, 10137324, 11894889, 10061015, 9572787

10284838, 10073683, 12639234, 9578670, 9748749, 10022980, 10237773

10089333, 12419331, 11674485, 12685431, 10187168, 10648873, 10158965

11061775, 12635537, 9746210, 10204358, 10356513, 10378005, 10170431

12639177, 10222719, 10384285, 10035737, 12345717, 9873405, 11069199

12670165, 10159846, 13257247, 10205230, 10052141, 11818335, 12371955

12655433, 10040921, 11827088, 10219576, 12408350, 13343424, 11707699

12370722, 11695333, 11841309, 11924400, 12737666, 12797765, 10281887

10278372, 10013177, 13503598, 12543639, 10157249, 12531263, 9735237

10317487, 10219583, 9727147, 10310299, 10636231, 11065646, 10055063

10368698, 10079168, 11695416, 10233732, 10314582, 9953542, 10080579

11699057, 12620422, 10427260, 11666137, 10110863, 10363186, 10417716

10019218, 10388660, 12748240, 9539440, 10373381, 10239480, 10158493

11842991, 10399808, 10417216, 11695285, 11800170, 10157402, 9651350

10299224, 10151017, 11724916, 9564886, 9847634, 10018789, 10248523

11694127, 10630870, 9770451, 10425676, 9683047, 10180307, 9835264

10132870, 10094201, 10193846, 11664046, 10324294, 9414040, 9819805

11830776, 11830777, 11830778, 11683713, 10200404, 10102506, 12827726

11733179, 10229886, 10040531, 10082277, 9788588, 12326246, 12397410

10622001, 13468884, 13386082, 10040035, 12539000, 11867127, 9842573

9771278, 10013431, 10228151, 10324526, 12417369, 10238786, 10217802

10332111, 10227288, 10623249, 9943960, 10021022, 9824435, 11664719

12950644, 9735282, 11800854, 10097711, 11858315, 6523037, 10053725, 8685446

Patch 13696242 : applied on Wed Apr 25 13:31:18 EEST 2012

Unique Patch ID: 14617280

Patch description: "Grid Infrastructure Patch Set Update : 11.2.0.2.6 (13696242)"

Created on 4 Apr 2012, 05:37:11 hrs PST8PDT

Bugs fixed:

13696242, 10157506, 10178670, 10425672, 12311357, 12419353, 12827731

13343447, 13653086, 9959110, 10374874, 10272615, 10314123, 10014392

10089120, 10057296, 11692271, 9864003, 11775080, 13635562, 9916145

12695202, 12593451, 10044622, 11705438, 10365625, 12767231, 9935308

12399977, 11882456, 12421404, 12862012, 11744343, 12340700, 10056713

10637741, 9939306, 9795321, 9902536, 10007185, 10376847, 10038791

11741224, 12823479, 11655840, 10048487, 10322157, 11768599, 10260251

10052721, 10028235, 10027079, 12412303, 10357258, 11664015, 11871469

10045436, 10231906, 12722095, 10622973, 11724464, 9891341, 10072474

10036834, 10029900, 9974223, 10161846, 10016083, 12597463, 9918485

11781515, 10040647, 13332363, 12651595, 12345151, 11925641, 10069541

10029119, 12709476, 10233159, 12332919, 9812956, 10036193, 10015210

12424479, 12340501, 10621175, 9686152, 12556028, 12605690, 11877079

8906163, 10111010, 12366642, 11777648, 10115514, 10104377, 10121931

10057680, 10280665, 10078086, 9944948, 10146768, 10052529, 10011084

10012319, 10073075, 10233811, 12720728, 10299006, 12426375, 10248739

10236074, 10128191, 11071429, 10019726, 9975837, 10253630, 12615394

10420872, 9949676, 11936945, 11820787, 10637483, 13036424, 13396479

10379703, 12677816, 10157622, 11698552, 10385838, 10053985, 12718811

10229297, 10425674, 9812970, 11828633, 12538907, 12359585, 11899801

10083789, 12728585, 9876201, 10073372, 9963327, 10428946, 11077756

10375649, 12398492, 9336825, 11682409, 10062301, 12767563, 12546712

10018215, 10105195, 10419987, 13355549, 10071992, 11807012, 10634513

9926027, 10103954, 12743755, 12406573, 10028343, 12425730, 11768055

11866171, 10065216, 9907089, 9897335, 10190153, 11744313, 10175855

12600343, 12422324, 10284828, 10028637, 10361177, 12800501, 9979706

12950415, 10324594, 10015460, 11818524, 9971646, 11782423, 11654726

9978765, 10398810, 11904778, 10397652, 13768473, 9915329, 10107380

10110969, 10305361, 10331452, 10083009, 10631693, 10008467, 10048027

10040109, 9944978, 10370797, 10033106, 12700217, 11840629, 10042143

9978195, 10284693, 12639016, 10638381, 11839004, 9679401, 11663339

10075643, 10057719, 11834289, 9458055, 10205290, 10124517, 12680491

13334158, 11069614, 10165314, 9593552, 10168006

Rac system comprising of multiple nodes

Local node = ractst1

Remote node = ractst2

--------------------------------------------------------------------------------

OPatch succeeded.

Post patch:

$ cd $ORACLE_HOME/rdbms/admin

$ sqlplus / as sysdba

SQL> @catbundle.sql psu apply

OK finished, now we can upgrade GI...

2. Upgrade GI to 11.2.0.3 and apply 11.2.0.3.2 PSU with software only mode.

All the upgades on GI and DB homes should be done with out-of-place upgrade. This means you have to create the new GI and DB home directories for upgrade.
Normally you don't have to apply 11.2.0.3.2 PSU while upgrading GI home. However if you give an eye to note 1367631.1 Top 5 CRS/Grid Infrastructure Install issues , at Issue #2, it states that: Frequently GI installs fail due to known issues for which fixes already exist. It is therefore a good practice to check for and potentially install the latest GI PSU - embedded in the initial GI installation. I will explain how it will installed while upgrading GI home.

Validate for Oracle Cluster upgrade

./runcluvfy.sh stage -pre crsinst -upgrade -n mrc1,mrc2 -rolling -src_crshome $GI_HOME -dest_crshome $NEW_GI_HOME -dest_version 11.2.0.3.0 -fixup -fixupdir /home/oramrc/fixup -verbose

Unset environments

$ unset ORA_CRS_HOME
$ unset ORACLE_HOME
$ unset ORA_NLS10
$ unset TNS_ADMIN
$ unset ORACLE_BASE
$ unset ORACLE_SID
$ unset CRS_HOME

Create new GI_HOME directories

$ mkdir NEW_GI_HOME

Start runInstaller with oracle user:

When it asks to run rootupgrade.sh install 11.2.0.3.2 PSU with software only mode: ( Patch 13696251 )

** unzip opatch to new GI home
** set new GI_HOME in the environment

** Install the PSU with software only mode on each node:

$ <GI_HOME>/OPatch/opatch napply -oh <GI_HOME> -local <UNZIPPED_PATCH_LOCATION>/13696251

$ <GI_HOME>/OPatch/opatch apply -oh <GI_HOME> -local <UNZIPPED_PATCH_LOCATION>/13696216

After installing 11.2.0.3.2 PSU run rootupgrade.sh for each node as runInstaller wants.

# /u01/app/11.2.0/grid11203/rootupgrade.sh

Click continue on runInstaller and finish it.

Check for GI version

$ crsctl query crs activeversion

And GI upgarde is finished with PSU installed (11.2.0.3.2), continue to next section db upgarde...

3. Upgrade database to 11.2.0.3 manually .

** It is not recommended to use dbua while upgrading a business critical database. So I upgrade the database manually

create new DB_HOME directories:

$ mkdir NEW_DB_HOME

Launch runInstaller and install database as "software only":

Use pre-upgrade information tool:

This is not an optional section for manual db upgrade, it is a must.

Download latest version of this script and run: Note 884522.1

SQL> SPOOL upgrade_info.log

SQL> @$11g_ORACLE_HOME/rdbms/admin/utlu112i.sql

SQL> SPOOL OFF

sample run:

Miscellaneous Warnings
**********************************************************************
WARNING: --> The "cluster_database" parameter is currently "TRUE"
.... and must be set to "FALSE" prior to running a manual upgrade.
WARNING: --> Database contains INVALID objects prior to upgrade.
.... The list of invalid SYS/SYSTEM objects was written to
.... registry$sys_inv_objs.
.... The list of non-SYS/SYSTEM objects was written to
.... registry$nonsys_inv_objs.
.... Use utluiobj.sql after the upgrade to identify any new invalid
.... objects due to the upgrade.
.... USER XXXX has 2 INVALID objects.
.... USER XX has 2 INVALID objects.
.... USER XXX has 26 INVALID objects.
WARNING: --> Database contains schemas with objects dependent on DBMS_LDAP package.
.... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
.... USER APEX_030200 has dependent objects.
.
**********************************************************************
Recommendations
**********************************************************************
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:

EXECUTE dbms_stats.gather_dictionary_stats;

**********************************************************************
Oracle recommends reviewing any defined events prior to upgrading.

To view existing non-default events execute the following commands
while connected AS SYSDBA:
Events:
SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2
WHERE UPPER(name) ='EVENT' AND isdefault='FALSE'

Trace Events:
SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2
WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'

Changes will need to be made in the init.ora or spfile.

Analyze every item and fix the warnings.

Apply the following scripts:

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;

no rows selected

SQL> PURGE dba_recyclebin;

DBA Recyclebin purged.

Prepare the new home:

create pfile from spfile and remove cluster parameters:

SQL> create pfile='/u01/app/oracle/product/11.2.0/pfilerac_orj.ora' from spfile;

set cluster database parameter to false and change the paths to new DB_HOME directories.

Copy parameter files from old_db_home to new_db_home

Copy password file and tnsnames.ora and other needed parameter files to new ORACLE_HOME on both nodes.

stop databases runing from old home:

srvctl stop database -d ractest

Check the environment ORACLE_HOME for pointing to new DB_HOME.

startup the database with upgrade mode from new home:

SQL> startup upgrade pfile='/u01/app/oracle/product/11.2.0/pfilerac.ora';

run catalog upgrade script: (Before start, check the archive log location for enough free space)

SQL> SPOOL upgrade2.log

SQL> @?/rdbms/admin/catupgrd.sql
.....
SQL> SPOOL OFF

It took 40 minutes to upgrade my 600 Gb database.

After upgrade create spfile with original pfile;

Run post-upgrade status tool:

SQL> @utlu112s.sql

Run post-Upgrade Scripts

SQL> catuppst.sql

Recompile Invalid Packages

SQL> @utlrp.sql

Update /etc/oratab

Update oratab with new database home

upgrade the database configuration in Oracle Clusterware

srvctl upgrade database -d rac -o /u01/app/oracle/product/11.2.0/dbhome_11203

This finishes upgrading database, now move on to last step to appy 11.2.0.3.2 PSU to db_home

4. Apply 11.2.0.3.2 PSU to db only.

Unzip latest opatch to new db_homes again and again and again !!!!!

Create ocm.rsp file

$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/ocm/bin/emocmrsp

Check for validation and conflict detection

./opatch lsinventory -detail -oh /u01/app/oracle/product/11.2.0/db_11203

opatch prereq CheckConflictAgainstOHWithDetail -ph ./13696216 -oh /u01/app/oracle/product/11.2.0/db_11203

opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./13696251/custom/server/13696251 -oh /u01/app/oracle/product/11.2.0/db_11203

start opatch on each node(with root user )

opatch auto PATCH_DIRACTORY -oh /u01/app/oracle/product/11.2.0/dbhome_11203 -ocmrf /u01/app/oracle/product/11.2.0/dbhome_11203/OPatch/ocm/bin/ocm.rsp

Post patch

cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catbundle.sql psu apply
SQL> QUIT

catalog upgrade

$ rman catalog rman/rman@CATALOG_DB
RMAN> upgrade catalog;

recovery catalog owner is RMAN
enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> upgrade catalog

recovery catalog upgraded to version 11.02.00.02
DBMS_RCVMAN package upgraded to version 11.02.00.02
DBMS_RCVCAT package upgraded to version 11.02.00.02

If you use GoldenGate, enable ddl_trigger and start services

$ sqlplus / as sysdba

SQL> @ddl_enable

$ crsctl start res ggateapp

$ crsctl start res mvggatevip

Finished !! Both GI and DB versions are upgraded to 11.2.0.3.2.

ÖzgürBlog

Sayfalar

Friday, August 24, 2012

Oracle Discoverer report usage stats

Tuesday, June 26, 2012

Upgrade 11.2.0.2 RAC Database to 11.2.0.3 manually