Wednesday, June 5, 2013

How to install cloud controll 12c agent in silent mode

Installing Oracle Management Agent in Silent Mode


I will use agentDeploy scipt for installing agent. (Installing Using agentDeploy Script).
First we need the management agent software. 

If you are using online mode in OMS( Oracle Management Server) ( you can easily download agent from internet, if you are using offline mode (in this mode you did not enter oracle support id or your oms is not open to internet) you need some additional steps to download management agent software.After downloading management agent software, you need to upload this zip to client which we install agent. Then install in silent mode.


Lets start with emcli command which query OMS repository for agent software . 
Then download example agent software for windwos 64 bit platform and apply this software to OMS reposiyory for future installations. 
Finally install management agent to client machine in silent mode.

  


  • Download agent  software from OMS repository: Download the Management Agent Software onto the OMS Host Using EMCLI



Login successful
[oracle@grid12c bin]$ ./emcli login -username=sysman
Enter password :

Login successful
[oracle@grid12c bin]$ ./emcli get_supported_platforms
Getting list of platforms ...
Check the logs at /u01/app/oracle/Middleware/gc_inst/em/EMGC_OMS1/sysman/emcli/setup/.emcli/agent.log
About to access self-update code path to retrieve the platforms list..
Getting Platforms list  ...
-----------------------------------------------
Version = 12.1.0.2.0
 Platform = Linux x86-64
-----------------------------------------------
Version = 12.1.0.2.0
 Platform = HP-UX Itanium
-----------------------------------------------
Version = 12.1.0.2.0
 Platform = Microsoft Windows (32-bit)
-----------------------------------------------
Platforms list displayed successfully.

Note: If you don't have the software for required platform, you need additional steps for downloading from internet and appliying to OMS. Here we have 3 platforms which are downloaded previously, I will add windows 64 bit software to illustrate.



  • Download agent software from internet: (Acquiring Management Agent Software in Offline Mode)


If you run grid12c server on offline mode, you have to perform additional steps before download the agent install software:




  1. From the Setup menu, select Extensibility, then select Self Update.



2. On the Self Update home page, click Check Updates. A message is displayed that contains the URL to be accessed to download a catalog of all updates.



3. From an Internet-enabled computer, download the catalog file using the aforementioned URL.
Note:
                           Do not unzip the file.



4. Copy the downloaded file to OMS host machine.

5. Navigate to the home/bin directory on OMS and log in to EMCLI, run the command as the install user, for example:

Login successful
[oracle@grid12c bin]$ ./emcli login -username=sysman
Enter password :

Login successful
[oracle@grid12c bin]$ ./emcli sync
Synchronized successfully
[oracle@grid12c bin]$ ./emcli import_update_catalog -omslocal  -file=/home/oracle/p9348486_112000_Generic.zip
Processing catalog for Provisioning Bundle
Processing catalog for Agent Software
Processing catalog for Management Connector
Processing catalog for Plug-in
Processing update: Plug-in - Enterprise Manager for Oracle Virtual Desktop Infrastructure
Processing update: Plug-in - Enterprise Manager for Oracle Virtual Desktop Infrastructure
Processing update: Plug-in - Enterprise Manager for Sun Ray Software
Processing update: Plug-in - Management Plug-in for the Sun ZFS Storage Appliance Family
Processing update: Plug-in - Enterprise Manager for Oracle Tuxedo
Processing update: Plug-in -
                Enterprise Manager for Pillar Axiom

Processing catalog for Middleware Profiles and Gold Images
Processing catalog for Informational
Processing update: Informational - Oracle Audit Vault Plug-in (12.1.0.3) to be Withdrawn
Processing update: Informational - Announcing New and Updated Cloud Management Capabilites for Database, Infrastructure, and Testing
Processing update: Informational - Enterprise Manager Cloud Control 12c Release 2 Plug-in Update 1 (12.1.0.2) now available on OTN
Processing catalog for Extensibility Development Kit
Processing update: Extensibility Development Kit - 'Extensibility Development Kit (EDK) is a standalone tool designed to help development of Enterprise Manager extensions (For example: Plug-ins)'

Operation completed successfully. Self Update catalog has been uploaded to Enterprise Manager. Please use the Self Update Home to view and manage updates.

6. Return to the Self Update home page and do a refresh to see a count of new updates in the Available Updates column. Here you can compare plugin row with section 2 picture. Here it has 28 available updates, at section 2  it was 22. This means repository is refreshed !



7. For Type, select Agent Software, then select Open from the Actions menu. The Agent Software Updates page appears to show the Management Agent software available for different platforms

8. Select an update from the list of available updates. All entries other than the one that matches the platform of the OMS host should show their status asAvailable.

9. Click Download. A message displays with a URL and instructions.



10.  After downloading zip file and uploading to oms server run command:

[oracle@grid12c bin]$ ./emcli import_update -omslocal -file=/home/oracle/p14570373_112000_Generic.zip
Processing update: Agent Software - Agent Software (12.1.0.2.0) for Microsoft Windows x64 (64-bit)
Operation completed successfully. Update has been uploaded to Enterprise Manager. Please use the Self Update Home to manage this update.


11. On grid control self update page, click refresh. And press apply button .



12. After job finishes you can see that agent software is applied



Now we can run the emcli commd again and see that windows 64 bit software is available on OMS repository.

[oracle@grid12c bin]$ ./emcli get_supported_platforms
Getting list of platforms ...
Check the logs at /u01/app/oracle/Middleware/gc_inst/em/EMGC_OMS1/sysman/emcli/setup/.emcli/agent.log
About to access self-update code path to retrieve the platforms list..
Getting Platforms list  ...
-----------------------------------------------
Version = 12.1.0.2.0
 Platform = Linux x86-64
-----------------------------------------------
Version = 12.1.0.2.0
 Platform = HP-UX Itanium
-----------------------------------------------
Version = 12.1.0.2.0
 Platform = Microsoft Windows (32-bit)
-----------------------------------------------
Version = 12.1.0.2.0
 Platform = Microsoft Windows x64 (64-bit)
-----------------------------------------------
Platforms list displayed successfully.

Now we can download agent software from OMS repository to any directory on host, for example /tmp.

[oracle@grid12c bin]$ ./emcli get_agentimage -destination=/tmp -platform="Linux x86-64" -version=12.1.0.2.0
Platform:Linux x86-64
Destination:/tmp
 === Partition Detail ===
Space free : 31 GB
Space required : 1 GB
Check the logs at /u01/app/oracle/Middleware/gc_inst/em/EMGC_OMS1/sysman/emcli/setup/.emcli/get_agentimage_2013-06-05_11-30-42-AM.log
Setting property ORACLE_HOME to:/u01/app/oracle/Middleware/oms
calling pulloneoffs with arguments:/u01/app/oracle/Middleware/oms/u01/app/oracle/Middleware/oms/sysman/agent/12.1.0.2.0_AgentCore_226.zip12.1.0.2.0linux_x64
Check this logs for more information: /u01/app/oracle/Middleware/oms/sysman/prov/agentpush/logs
[oracle@grid12c bin]$ ls -l /tmp/*.zip
-rw-r----- 1 oracle oinstall 240091482 Jun  5 11:30 /tmp/12.1.0.2.0_AgentCore_226.zip




  • Installing management agent software to clients: (Install the Management Agent Using the agentDeploy Script)



1. Unzip the file on a stage directory on client that agent will be installed. 

 $ unzip 12.1.0.2.0_AgentCore_226.zip

2. Fill the agent.rsp file as following:

$ cat agent.rsp 
####################################################################
## copyright (c) 1999, 2012 Oracle. All rights reserved.          ##
##                                                                ##
## Specify values for the variables listed below to customize     ##
## your installation.                                             ##
##                                                                ##
## Each variable is associated with a comment. The comment        ##
## identifies the variable type.                                  ##
##                                                                ##
## Please specify the values in the following format:             ##
##                                                                ##
##         Type         Example                                   ##
##         String       "Sample Value"                            ##
##         Boolean      True or False                             ##
##         Number       1000                                      ##
##         StringList   {"String value 1","String Value 2"}       ##
##                                                                ##
## The values that are given as <Value Required> need to be       ##
## specified for a silent installation to be successful.          ##
##                                                                ##
##                                                                ##
## This response file is generated by Oracle Software             ##
## Packager.                                                      ##
###################################################################

RESPONSEFILE_VERSION=2.2.1.0.0


#-------------------------------------------------------------------------------
#OMS_HOST:<String> OMS host info required to connect to OMS
#EM_UPLOAD_PORT:<String> OMS port info required to connect to OMS
#AGENT_REGISTRATION_PASSWORD:<String> Agent Registration Password needed to 
#     establish a secure connection to the OMS.
#AGENT_INSTANCE_HOME:<String> Agent instance home is the location of agent state directory.
#AGENT_PORT:<String> Agent port on which the agent process should be started.
#b_startAgent:<boolean> Agent will not be started after configuration if the value specified is false.
#ORACLE_HOSTNAME:<String> Fully qualified domain name of host where is the agent is deployed.
#s_agentHomeName:<String>Customized Oracle home name for the agent home. Example: s_agentHomeName="agent12gR1"
#-------------------------------------------------------------------------------
OMS_HOST=grid12c.xxx.com
EM_UPLOAD_PORT=4904
AGENT_REGISTRATION_PASSWORD=xxxxxxxx
#AGENT_INSTANCE_HOME=<Value Unspecified>
#AGENT_PORT=3872
b_startAgent=true
ORACLE_HOSTNAME=xxxx.xxx.com
#s_agentHomeName=<Value Unspecified>
#-------------------------------------------------------------------------------
#s_agentServiceName:<String> Sets the agent Service Name and this variable can be
#      used to overrite the agent service name calculated by the install. This is
#      required for only Windows.
#      Example: 
#      s_agentServiceName = "Oracleagent12gAgent"      ;  default value
#      s_agentServiceName = "GridAgent"                 ;  User specified value 
#-------------------------------------------------------------------------------
#s_agentServiceName=""

####################################################################################
#Please Don't change the values of these variables
####################################################################################
#-------------------------------------------------------------------------------
#EM_INSTALL_TYPE:<STRING> install type
#-------------------------------------------------------------------------------
EM_INSTALL_TYPE="AGENT"


3. Execute the following command:

./agentDeploy.sh AGENT_BASE_DIR=/u01/app/agent12c RESPONSE_FILE=/u01/app/agent12c_install/agent.rsp


where agent_base_dir is installation directory of management agent.


4. Run root.sh scipt







Friday, August 24, 2012

Oracle Discoverer report usage stats


How to find the Oracle Discoverer reports usage stats:



/* Query a specific user's report run history  */
  SELECT A.QS_CREATED_DATE,

         B.user_name,
         QS_DOC_NAME,
         QS_DOC_DETAILS
    FROM appseul_tr.EUL4_qpp_stats a, fnd_user b
   WHERE     QS_CREATED_DATE > TO_DATE ('07 10 28 05 2012', 'hh mi DD MM YYYY')
         AND SUBSTR (a.QS_CREATED_BY, 2, LENGTH (a.QS_CREATED_BY)) = B.USER_ID
         AND user_name = '&USER_NAME'
ORDER BY a.QS_CREATED_DATE DESC



/*  How many times a discovere report is run and how much does it take   */
  SELECT a.qs_doc_owner Owner,
         a.qs_doc_name Workbook,
         a.qs_doc_details Worksheet,
       trunc(AVG (a.QS_ACT_ELAP_TIME) ) duration,
         COUNT (*) run_count, trunc(avg(QS_ACT_CPU_TIME)) CPUTime
    FROM APPSEUL.eul4_qpp_stats a, APPSEUL.eul4_documents b
   WHERE a.qs_created_date between '01-JUN-2012' and '30-JUN-2012'            
                                          AND a.qs_doc_name = b.doc_name
GROUP BY a.qs_doc_owner, a.qs_doc_name, a.qs_doc_details
ORDER BY duration DESC;

Tuesday, June 26, 2012

Upgrade 11.2.0.2 RAC Database to 11.2.0.3 manually

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
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.


  • 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.