Oracle PSU patching - Oracle 11gR2 GRID environment - 3 node RAC

Overview:

1.Download psu patch and interim patch
2.Move to server
3.Check for conflict patch
4.Bring down the instance on each node  to roll back the conflict patch
5.Rollback the conflict patch
6.Apply the PSU patch on each node
7.On last node load the SQL (catbundle.sql)
8.Apply the rollbacked patch/interim patch.
--------------------------------------------------------------------------------------
Pre-implementation tasks

1.We need root privileges to install PSU patch, bcos it needs to be installed from Grid_Home
2./u01 should have minimum 6.5G free space available to apply PSU patch.
3.Take count & list of invalid objects
4.Take output of dba_registry and dba_registry_history
5.Ensure datbase backup is completed successfully
6.Take backup of Oracle home, Grid home
cd /u01/app/oracle/product
find . -print|cpio -pdmv /u02/PSU_OCT2013/
cd /u01/app/11.2.0
find . -print|cpio -pdmv /u02/PSU_OCT2013/

7.check status of all crs services on all nodes
$GRID_HOME/bin/crsctl check cluster -all

8. Set the environment variables.
export GRID_HOME=/u01/app/11.2.0/grid
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export PATH=$PATH:/u01/app/oracle/product/11.2.0/db_1/OPatch/
export PATH=$PATH:$GRID_HOME/OPatch
export PATH=$PATH:$ORACLE_HOME/OPatch

9.check status of cluster resources for all nodes
crsctl stat res -t

10.Check OPatch utility version 11.2.0.3.4 or later to apply this patch.
$GRID_HOME/OPatch/opatch version
$ORACLE_HOME/OPatch/opatch version

####OPatch utility upgrade

(1) Take a backup of $ORACLE_HOME/OPatch into a dedicated backup location.
Ignoring this as Oracle home backup is taken

(2) Remove the contents of $ORACLE_HOME/OPatch directory (Please do not
remove $ORACLE_HOME/OPatch directory itself)

(3) unzip the OPatch downloaded zip into $ORACLE_HOME directory.
 unzip -d /u01/app/oracle/product/11.2.0/db_1/OPatch/ p6880880_112000_Linux-x86-64.zip

To check the version of the opatch utility installed in the above step,
Go to the $ORACLE_HOME/OPatch directory and run "opatch version".
$GRID_HOME/OPatch/opatch version
$ORACLE_HOME/OPatch/opatch version

11.Check if emctl process is running
if it is running, stop the process before applying the PSU patch.
emctl stop dbconsole

12.Validation of Oracle Inventory
$ORACLE_HOME/OPatch/opatch lsinventory -detail -oh $ORACLE_HOME
$GRID_HOME/OPatch/opatch lsinventory -detail -oh  $GRID_HOME
Save the output so you have the status prior to the patch apply

13.Download and Unzip the Patch
The directory should have read permission for the ORA_INSTALL group
cd /u02/PSU_OCT2013/PATCH/
Unzip the patch as grid home owner.
$ unzip -d p17272731_112030_.zip

14. One-off Patch Conflict Detection and Resolution.
Go to unzipped files directory
then
Go to each patch directoy
and
Execute the below with Oracle home and Grid home to check for conflicts.
For ex:
cd /u02/PSU_OCT2013/PATCH/
cd 17272731
opatch prereq CheckConflictAgainstOHWithDetail -ph ./ -invPtrLoc $ORACLE_HOME/oraInst.loc
/u01/app/11.2.0/grid/OPatch/opatch  prereq CheckConflictAgainstOHWithDetail -ph ./ -invPtrLoc  /u01/app/11.2.0/grid/oraInst.loc

Patch Implementation steps:

1. In case rollback of a patch is required, perform the below
rollback steps:
Bring down the instance on each node and run the rollback command.
If the patch to be rollbacked is from GRID_HOME run the command from GRID_HOME, if database run from ORACLE_HOME
In our case it is Oracle Home
opatch rollback -id 13775960 -local -oh /u01/app/oracle/product/11.2.0/db_1 -invPtrLoc /u01/app/oracle/product/11.2.0/db_1/oraInst.loc

2. Create a ocm response file  ($ORACLE_HOME/OPatch/ocm/bin/emocmrsp)
It has been made mandatory
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
$ORACLE_HOME/OPatch/ocm/bin/emocmrsp  -no_banner -output /u02/PSU_OCT2013/unconfig.rsp
* creates the response in location specified by the parameter "-output"
* running without "-output /file.rsp"  creats the file in current directory with default name

3.Patch Installation
Important note:
In Oracle README , it is mentioned to patch GRID_HOME it needs to run under root user.
opatch auto command will only work under root user.
Under root user no other options for opatch will work.

opatch auto -ocmrf /home/oracle/ocm.rsp
For ex:
opatch auto /u02/PSU_OCT2013/PATCH -ocmrf /u02/PSU_OCT2013/unconfig.rsp

Generally the above command should work, if all the pre-requiste steps have been perfomed properly
like stopping emctl stop dbconsole & space availabe in /u01 filesystem 6.5G
If the above fails check the logfiles based on the errors take action.
Logfile location
/u01/app/11.2.0/grid/cfgtoollogs/opatch
/u01/app/11.2.0/grid/cfgtoollogs
Below are commands used to test and try to apply the patch when auto option failed.
opatch napply /u02/PSU_OCT2013/PATCH/17076717 -local -ocmrf /u02/PSU_OCT2013/unconfig.rsp -oh /u01/app/11.2.0/grid -invPtrLoc /u01/app/11.2.0/grid/oraInst.loc
Below option was tried, we got the error stating few oracle binaries were still active and were accessed.
Later we found out that emctl process was running. We brought it down and continued with normal installation as mentioned in step 1 with auto option.
opatch napply ./ -oh /u01/app/oracle/product/11.2.0/db_1 -invPtrLoc /u01/app/oracle/product/11.2.0/db_1/oraInst.loc -local '

4. If the above opatch auto install completes successfully, then follow the same steps on all other nodes.

5. In the final node run the below to load the SQL (catbundle.sql)
>>>>>>>Loading Modified SQL Files into the Database
The following steps load modified SQL files into the database.
For an Oracle RAC environment, perform these steps on only one node.
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> @catbundle.sql psu apply
SQL> QUIT
The catbundle.sql execution is reflected in the dba_registry_history view by a row associated with bundle series PSU.

Check the following log files in $ORACLE_BASE/cfgtoollogs/catbundle for any errors:
catbundle_PSU__APPLY_.log
catbundle_PSU__GENERATE_.log
where TIMESTAMP is of the form YYYYMMMDD_HH_MM_SS.
>>>>>>>>>>Upgrade Oracle Recovery Manager Catalog
If you are using the Oracle Recovery Manager,
the catalog needs to be upgraded. Enter the following command to upgrade it:
$ rman catalog username/password@alias
RMAN> UPGRADE CATALOG;
Example:

6. If the conflict patch was removed, then as per Oracle's advise we need to download and install the latest one-off patch
Before applying the patch, bring down the instance using srvctl
srvctl stop instance -d DBRAC -i DBRAC1

Go to the interim patch location
for ex: cd /u02/patch/13775960
Run opatch from oracle home
as oracle user

opatch apply -local
then start the instance
srvctl start instance -d DBRAC -i DBRAC1

--------------------------------------------------------------------------
Few commands to keep it handy when patching Grid environment.
###check status of all services in 11gr2 rac
1.check the status of CRS on specific node:
crsctl check crs
2. check the status of services on all nodes
srvctl status nodeapps
3.check the status of complete clusterware stack on all nodes
crsctl status resource -t
4.check the status of clusterware servers
crsctl status server -f
-----5.to check the status of specific Instance across cluster nodes
srvctl status instance -d DBRAC -i DBRAC2
6.to check the status of all instances across the cluster nodes
srvctl status instance -d  DBRAC -i DBRAC1,DBRAC2,DBRAC3

7.check the status of database across cluster nodes
srvctl status database -d DBRAC
==============================================
##shutdown/startup commands
1.to stop crs on specific node
Run this command as root user only.
./crsctl stop has
2.to start crs on specific node:
./crsctl start has
3.to stop specific Instance across cluster nodes
srvctl stop instance -d DBRAC -i DBRAC1
4.to start specific Instance across cluster nodes
srvctl start instance -d DBRAC -i DBRAC3
========================================

Determining the Job ID for Each Refresh Job at a Materialized View Site

Determining the Job ID for Each Refresh Job at a Materialized View Site

You can use the query in this section to list the following information about the refresh jobs at a materialized view site:

• The job identification number of each refresh job. Each job created by the DBMS_JOB package is assigned a unique identification number.
• The privilege schema, which is the schema whose default privileges apply to the job.
• The schema that owns each refresh job. Typically, the materialized view administrator owns a refresh job. A common username for the materialized view administrator is mviewadmin.
• The name of the refresh group that the job refreshes.
• The status of the refresh job, either normal or broken.

The following query displays this information:

COLUMN JOB HEADING 'Job ID' FORMAT 999999
COLUMN PRIV_USER HEADING 'Privilege|Schema' FORMAT A10
COLUMN RNAME HEADING 'Refresh|Group|Name' FORMAT A10
COLUMN ROWNER HEADING 'Refresh|Group|Owner' FORMAT A10
COLUMN BROKEN HEADING 'Broken?' FORMAT A7

SELECT J.JOB, 
       J.PRIV_USER, 
       R.ROWNER, 
       R.RNAME, 
       J.BROKEN
    FROM DBA_REFRESH R, DBA_JOBS J 
    WHERE R.JOB = J.JOB
    ORDER BY 1;

Your output looks similar to the following:

                   Refresh    Refresh
        Privilege  Group      Group
 Job ID Schema     Owner      Name       Broken?
------- ---------- ---------- ---------- -------
     21 MVIEWADMIN MVIEWADMIN HR_REFG    N

The N in the Broken? column means that the job is not broken. Therefore, the job will run at the next start time. A Y in this column means that the job is broken.

Listing Information About the Refresh Groups at a Materialized View Site

Listing Information About the Refresh Groups at a Materialized View Site

Each refresh group at a materialized view site is associated with a refresh job that refreshes the materialized views in the refresh group at a set interval. You can query the DBA_REFRESH data dictionary view to list the following information about the refresh jobs at a materialized view site:

• The name of the refresh group.
• The owner of the refresh group.
• Whether the refresh job is broken.
• The next date and time when the refresh job will run.
• The current interval setting for the refresh job. The interval setting specifies the amount of time between the start of a job and the next start of the same job.

The following query displays this information:

COLUMN RNAME HEADING 'Refresh|Group|Name' FORMAT A10
COLUMN ROWNER HEADING 'Refresh|Group|Owner' FORMAT A10
COLUMN BROKEN HEADING 'Broken?' FORMAT A7
COLUMN next_refresh HEADING 'Next Refresh'
COLUMN INTERVAL HEADING 'Interval' FORMAT A20

SELECT RNAME, 
               ROWNER, 
       BROKEN, 
       TO_CHAR(NEXT_DATE, 'DD-MON-YYYY HH:MI:SS AM') next_refresh, 
       INTERVAL 
    FROM DBA_REFRESH 
    ORDER BY 1;

Your output looks similar to the following:

Refresh    Refresh
Group      Group
Name       Owner      Broken? Next Refresh            Interval
---------- ---------- ------- ----------------------- --------------------
HR_REFG    MVIEWADMIN N       24-OCT-2003 07:18:44 AM SYSDATE + 1/24

The N in the Broken? column means that the job is not broken. Therefore, the refresh job will run at the next start time. A Y in this column means that the job is broken.

Listing the Properties of Materialized Views

Listing the Properties of Materialized Views

You can use the query in this section to list the following information about the materialized views at the current replication site:

• The name of each materialized view
• The owner of each materialized view
• The refresh method used by each materialized view: COMPLETE, FORCE, FAST, or NEVER
• Whether a materialized view is updatable
• The last date on which each materialized view was refreshed

Run the following query to list this information:

COLUMN MVIEW_NAME HEADING 'Materialized|View Name' FORMAT A15
COLUMN OWNER HEADING 'Owner' FORMAT A10
COLUMN REFRESH_METHOD HEADING 'Refresh|Method' FORMAT A10
COLUMN UPDATABLE HEADING 'Updatable?' FORMAT A10
COLUMN LAST_REFRESH_DATE HEADING 'Last|Refresh|Date'
COLUMN LAST_REFRESH_TYPE HEADING 'Last|Refresh|Type' FORMAT A15

SELECT MVIEW_NAME, 
       OWNER,
       REFRESH_METHOD, 
       UPDATABLE, 
       LAST_REFRESH_DATE, 
       LAST_REFRESH_TYPE
    FROM DBA_MVIEWS; 

Your output looks similar to the following:

                                                 Last      Last
Materialized               Refresh               Refresh   Refresh
View Name       Owner      Method     Updatable? Date      Type
--------------- ---------- ---------- ---------- --------- ---------------
COUNTRIES_MV1   HR         FAST       Y          21-OCT-03 FAST
DEPARTMENTS_MV1 HR         FAST       Y          21-OCT-03 FAST
EMPLOYEES_MV1   HR         FAST       Y          21-OCT-03 FAST
JOBS_MV1        HR         FAST       Y          21-OCT-03 FAST
JOB_HISTORY_MV1 HR         FAST       Y          21-OCT-03 FAST
LOCATIONS_MV1   HR         FAST       Y          21-OCT-03 FAST
REGIONS_MV1     HR         FAST       Y          21-OCT-03 FAST

Listing Master Information For Materialized Views

Listing Information About Materialized Views

This section contains queries that you can run to display information about the materialized views at a replication site.

Listing Master Information For Materialized Views

The following query shows the master for each materialized view at a replication site and whether the materialized view can be fast refreshed:

COLUMN MVIEW_NAME HEADING 'Materialized|View Name' FORMAT A15
COLUMN OWNER HEADING 'Owner' FORMAT A10
COLUMN MASTER_LINK HEADING 'Master Link' FORMAT A30
COLUMN Fast_Refresh HEADING 'Fast|Refreshable?' FORMAT A16

SELECT MVIEW_NAME, 
       OWNER, 
       MASTER_LINK,  
       DECODE(FAST_REFRESHABLE, 
              'NO', 'NO',
              'DML', 'YES',    
              'DIRLOAD', 'DIRECT LOAD ONLY',
              'DIRLOAD_DML', 'YES',
              'DIRLOAD_LIMITEDDML', 'LIMITED') Fast_Refresh
    FROM DBA_MVIEWS;

Your output looks similar to the following:

Materialized                                              Fast
View Name       Owner      Master Link                    Refreshable?
--------------- ---------- ------------------------------ ----------------
COUNTRIES_MV1   HR         @ORC1.WORLD                    YES
DEPARTMENTS_MV1 HR         @ORC1.WORLD                    YES
EMPLOYEES_MV1   HR         @ORC1.WORLD                    YES
JOBS_MV1        HR         @ORC1.WORLD                    YES
JOB_HISTORY_MV1 HR         @ORC1.WORLD                    YES
LOCATIONS_MV1   HR         @ORC1.WORLD                    YES
REGIONS_MV1     HR         @ORC1.WORLD                    YES

Listing General Information About Materialized View Groups

Listing General Information About Materialized View Groups

You can use the query in this section to list the following general information about the materialized view groups at the current materialized view site:

• The name of each materialized view group
• The master of each materialized view group
• The method of propagation to a materialized view group's master, either asynchronous or synchronous
• The comment associated with each materialized view group

Run the following query to list this information:

COLUMN GNAME HEADING 'Group Name' FORMAT A10
COLUMN DBLINK HEADING 'Master' FORMAT A25
COLUMN Propagation HEADING 'Propagation|Method' FORMAT A12
COLUMN SCHEMA_COMMENT HEADING 'Comment' FORMAT A30

SELECT S.GNAME, 
       S.DBLINK, 
       DECODE(S.PROP_UPDATES, 
              0, 'ASYNCHRONOUS', 
              1, 'SYNCHRONOUS') Propagation,
       G.SCHEMA_COMMENT 
    FROM DBA_REPSITES S, DBA_REPGROUP G
    WHERE S.GNAME = G.GNAME
    AND S.SNAPMASTER = 'Y';

Your output looks similar to the following:

                                     Propagation
Group Name Master                    Method       Comment
---------- ------------------------- ------------ ------------------------------
HR_REPG    ORC1.WORLD                ASYNCHRONOUS

Determining Which Materialized Views Are Currently Refreshing

Determining Which Materialized Views Are Currently Refreshing

The following query shows the materialized views that are currently refreshing:

COLUMN SID HEADING 'Session|Identifier' FORMAT 9999
COLUMN SERIAL# HEADING 'Serial|Number' FORMAT 999999
COLUMN CURRMVOWNER HEADING 'Owner' FORMAT A15
COLUMN CURRMVNAME HEADING 'Materialized|View' FORMAT A25

SELECT * FROM V$MVREFRESH;

Your output looks similar to the following:

   Session  Serial                 Materialized
Identifier  Number Owner           View
---------- ------- --------------- -------------------------
        19     233 HR              COUNTRIES_MV
         5     647 HR              EMPLOYEES_MV

Note:

The V$MVREFRESH dynamic performance view does not contain information about updatable materialized views when the materialized views' deferred transactions are being pushed to its master.