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
$ 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
Sample "opatch lsinventory" output after applying PSU
[oracle@ractst1 OPatch]$ ./opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.0
Copyright (c) 2012, Oracle Corporation. All rights reserved.
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.
$ 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.
      
** 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 |
|
