|
In this Document
Oracle Database - Enterprise Edition -
Version 11.2.0.1 to 11.2.0.4 [Release 11.2]
Oracle Database - Enterprise Edition - Version 12.1.0.2 to 12.1.0.2 [Release
12.1]
Information in this document applies to any platform.
** Checked for relevance '25-Nov-2015' **
To detail a set of steps that can be used
to apply a PSU using opatch auto to Primary and
Standby sites that are part of a Data Guard Physical Standby implementation.
The process detailed takes into account Oracle Restart configurations where
the GRID Infrastructure is installed in the environment and needs to be
patched along with the RDBMS installation.
Process
Overview
Phase 1: Ensure
ALL prerequisites set out in the patches/PSU's README for applying the patch
are met
Phase 2: Disable log shipping between the sites
Phase 3: Apply the Patch/PSU to the Standby site
Phase 4: Apply the Patch/PSU to the Primary site
Phase 5: Post patch application processes and checks
Phase 1: Ensure ALL prerequisites
set out in the patches/PSU's README for applying the patch are met
1. Ensure that the prerequisites for applying the PSU have been met.
1.1 Make sure the opatch release
matches that required to apply the PSU. This
will be documented in the README bundled with the PSU.
[oracle@dg1 ~]$ /opt/app/oracle/product/11.2.0.3/grid/OPatch/opatch version
OPatch Version: 11.2.0.3.5
OPatch succeeded.
1.2 Validate the inventory for each of the homes being
patched. Perform this for both the GRID/Restart and RDBMS Homes.
[oracle@dg2
~]$ echo $ORACLE_HOME
/opt/app/oracle/product/11.2.0.3/grid
[oracle@dg2 ~]$ $ORACLE_HOME/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.5
Copyright (c) 2013, Oracle Corporation. All rights reserved.
Oracle Home :
/opt/app/oracle/product/11.2.0.3/dbhome_1
Central Inventory : /opt/app/oracle/oraInventory
from
: /opt/app/oracle/product/11.2.0.3/dbhome_1/oraInst.loc
OPatch version : 11.2.0.3.5
OUI version : 11.2.0.3.0
Log file location :
/opt/app/oracle/product/11.2.0.3/dbhome_1/cfgtoollogs/opatch/opatch2014-08-12_17-32-12PM_1.log
Lsinventory Output file location :
/opt/app/oracle/product/11.2.0.3/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2014-08-12_17-32-12PM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (1):
Oracle Database
11g
11.2.0.3.0
There are 1 products installed in this Oracle Home.
There are no Interim patches installed in this Oracle Home.
--------------------------------------------------------------------------------
OPatch succeeded.
The results above show that the home is the base release of 11.2.0.3.
As I am applying PSU 6 to this home it is OK to proceed.
1.3 Make sure an OCM response file exists in each site in order
for opatch auto to apply the patch. The utility emocmrsp is used to generate this file. It will be
referenced later by patch auto. Personally I create this response file
in all homes including RDBMS and GI and put it in the same location as the
utility itself so I always know where it is.
[oracle@dg1
bin]$ cd /opt/app/oracle/product/11.2.0.3/grid/OPatch/ocm/bin
[oracle@dg1 bin]$ ./emocmrsp
OCM Installation Response Generator 10.3.4.0.0 - Production
Copyright (c) 2005, 2010, Oracle and/or its affiliates. All rights
reserved.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:
You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es,
[N]o) [N]: Y
The OCM configuration response file (ocm.rsp) was
successfully created.
[oracle@dg1 bin]$ ls -l
total 16
-rwxrw----. 1 oracle oinstall
9063 Nov 27 2009 emocmrsp
-rw-r--r--. 1 oracle oinstall
623 Aug 12 18:26 ocm.rsp
1.4 Make sure the Primary and Standby sites are in sync prior to
applying the patch. Log in to the standby site and check v$dataguard_stats.
[oracle@dg2
~]$ . oraenv
ORACLE_SID = [+ASM] ? LOND
The Oracle base remains unchanged with value /opt/app/oracle
[oracle@dg2 ~]$ sqlplus / as sysdba
SQL> col name format a30
SQL> col value format a30
SQL> select name,value from v$dataguard_stats;
NAME
VALUE
------------------------------ ------------------------------
transport lag
+00 00:00:00
apply
lag
+00 00:00:00
apply finish
time
+00 00:00:00.000
estimated startup time 16
From the results above we can see the 2 sites are synchronised,
there is no transport lag and no apply lag.
Phase 2: Disable log shipping between the sites
2. In the primary disable log shipping to the standby site.
There are 2 approaches to performing this, one where a Data Guard Broker
configuration exists and the second where the environment has no broker
configuration.
2.1 Where a Data Broker configuration exists disable log shipping
from the Primary site using the following process.
http://docs.oracle.com/cd/E11882_01/server.112/e40771/dbresource.htm#DGBKR3537
Initially log transport services are enabled "Intended State:
TRANSPORT-ON"
DGMGRL>
show database verbose melb;
Database - melb
Role:
PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
MELB
..
.
Disable the log transport services using
DGMGRL> edit database MELB set state='TRANSPORT-OFF';
Succeeded.
DGMGRL> show database verbose melb;
Database - melb
Role:
PRIMARY
Intended State: TRANSPORT-OFF
Instance(s):
MELB
..
.
Note: Please
disregard the LogShipping='ON' database property as
this is overridden by setting the state to 'TRANSPORT-OFF'.
2.2. Where
no broker configuration exists disable log shipping using the log_archive_dest_state_X database parameter.
Currently the environment here is set to use log_archive_dest_2 for shipping
to the standby.
SQL>
show parameter log_archive_dest_2
NAME
TYPE VALUE
------------------------------------ -----------
------------------------------
log_archive_dest_2 string
service="lond", LGWR ASYNC NOA
FFIRM delay=0
optional compres
sion=disable max_failure=0 max
_connections=1
reopen=300 db_u
nique_name="lond" net_timeout=
30, valid_for=(all_logfiles,pr
imary_role)
SQL> show parameter log_archive_dest_state_2
NAME
TYPE
VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2 string ENABLE
To disable or defer log shipping defer the destination.
SQL>
alter system set log_archive_dest_state_2=defer;
Phase 3: Apply the Patch/PSU to the Standby site
In the scenario documented here we are going to use opatch
auto as the root use to patch both the GRID Infrastructure and the RDBMS Home
together at the same time. If you do not want to patch this way then
follow the instructions in the PSU README to apply the patch to GI and RDBMS
homes separately using opatch auto.
Readme - Patch Installation and Deinstallation For
11.2.0.3.x GI PSU (Doc ID 1494646.1)
3. As the root user use opatch apply to apply
the PSU to both the GRID Infrastructure/Restart and RDBMS Homes.
3.1 Using opatch auto and the directory
into which you have unzipped the patch and the OCM response file location
apply the patch.
Please Note: the location
you must point to will contain the bundle.xml file for the patch and it is
this directory you must point opatch at to apply
the PSU.
[root@dg2
patch]# /opt/app/oracle/product/11.2.0.3/grid/OPatch/opatch auto /home/oracle/patch -ocmrf
/opt/app/oracle/product/11.2.0.3/grid/OPatch/ocm/bin/ocm.rsp
Executing /opt/app/oracle/product/11.2.0.3/grid/perl/bin/perl /opt/app/oracle/product/11.2.0.3/grid/OPatch/crs/patch11203.pl
-patchdir /home/oracle -patchn
patch -ocmrf /opt/app/oracle/product/11.2.0.3/grid/OPatch/ocm/bin/ocm.rsp -paramfile
/opt/app/oracle/product/11.2.0.3/grid/crs/install/crsconfig_params
/opt/app/oracle/product/11.2.0.3/grid/crs/install/crsconfig_params
/opt/app/oracle/product/11.2.0.3/grid/crs/install/s_crsconfig_defs
This is the main log file:
/opt/app/oracle/product/11.2.0.3/grid/cfgtoollogs/opatchauto2014-08-12_18-35-05.log
This file will show your detected configuration and all the steps that opatch auto attempted to do on your system:
/opt/app/oracle/product/11.2.0.3/grid/cfgtoollogs/opatchauto2014-08-12_18-35-05.report.log
2014-08-12 18:35:05: Starting Oracle Restart Patch Setup
Using configuration parameter file:
/opt/app/oracle/product/11.2.0.3/grid/crs/install/crsconfig_params
patch /home/oracle/patch/16315641/custom/server/16315641 apply
successful for home /opt/app/oracle/product/11.2.0.3/dbhome_1
patch /home/oracle/patch/16056266 apply successful for home
/opt/app/oracle/product/11.2.0.3/dbhome_1
Successfully unlock /opt/app/oracle/product/11.2.0.3/grid
patch /home/oracle/patch/16315641 apply successful for home
/opt/app/oracle/product/11.2.0.3/grid
patch /home/oracle/patch/16056266 apply successful for home
/opt/app/oracle/product/11.2.0.3/grid
CRS-4123: Oracle High Availability Services has been started.
3.2 At the end of the process make sure that the GRID
Infrastructure/Restart resources are all health (ONLINE ONLINE).
[root@dg2
patch]# crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET
STATE
SERVER
STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE
dg2
ora.FRA.dg
ONLINE ONLINE
dg2
ora.LISTENER.lsnr
ONLINE ONLINE
dg2
ora.asm
ONLINE ONLINE
dg2
Started
ora.ons
OFFLINE OFFLINE dg2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
1
ONLINE ONLINE
dg2
ora.diskmon
1
OFFLINE OFFLINE
ora.evmd
1
ONLINE ONLINE
dg2
ora.lond.db
1
ONLINE ONLINE
dg2
Open,Readonly
Phase 4: Apply the Patch/PSU to the Primary site
4.1 Apply the PSU to the Primary site GRID Infrastructure and
RDBMS homes using opatch auto.
[oracle@dg1
patch]$ su -
Password:
[root@dg1 ~]# cd /home/oracle/patch
[root@dg1 patch]# ls
16056266 16315641 atp_lfp
bundle.xml README.html README.txt
[root@dg1 patch]# . oraenv
ORACLE_SID = [root] ? +ASM
The Oracle base has been set to /opt/app/oracle
[root@dg1 patch]# /opt/app/oracle/product/11.2.0.3/grid/OPatch/opatch auto /home/oracle/patch
-ocmrf /opt/app/oracle/product/11.2.0.3/grid/OPatch/ocm/bin/ocm.rsp
Executing /opt/app/oracle/product/11.2.0.3/grid/perl/bin/perl
/opt/app/oracle/product/11.2.0.3/grid/OPatch/crs/patch11203.pl -patchdir /home/oracle -patchn
patch -ocmrf /opt/app/oracle/product/11.2.0.3/grid/OPatch/ocm/bin/ocm.rsp -paramfile
/opt/app/oracle/product/11.2.0.3/grid/crs/install/crsconfig_params
/opt/app/oracle/product/11.2.0.3/grid/crs/install/crsconfig_params
/opt/app/oracle/product/11.2.0.3/grid/crs/install/s_crsconfig_defs
This is the main log file:
/opt/app/oracle/product/11.2.0.3/grid/cfgtoollogs/opatchauto2014-08-12_18-59-15.log
This file will show your detected configuration and all the steps that opatch auto attempted to do on your system:
/opt/app/oracle/product/11.2.0.3/grid/cfgtoollogs/opatchauto2014-08-12_18-59-15.report.log
2014-08-12 18:59:15: Starting Oracle Restart Patch Setup
Using configuration parameter file:
/opt/app/oracle/product/11.2.0.3/grid/crs/install/crsconfig_params
patch /home/oracle/patch/16315641/custom/server/16315641 apply
successful for home /opt/app/oracle/product/11.2.0.3/dbhome_1
patch /home/oracle/patch/16056266 apply successful for home
/opt/app/oracle/product/11.2.0.3/dbhome_1
Successfully unlock /opt/app/oracle/product/11.2.0.3/grid
patch /home/oracle/patch/16315641 apply successful for home
/opt/app/oracle/product/11.2.0.3/grid
patch /home/oracle/patch/16056266 apply successful for home
/opt/app/oracle/product/11.2.0.3/grid
CRS-4123: Oracle High Availability Services has been started.
4.2 At the end of the process make sure that the GRID
Infrastructure/Restart resources are all health (ONLINE ONLINE).
[root@dg1
patch]# crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET
STATE
SERVER
STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE
dg1
ora.FRA.dg
ONLINE ONLINE
dg1
ora.LISTENER.lsnr
ONLINE ONLINE
dg1
ora.asm
ONLINE ONLINE
dg1
Started
ora.ons
OFFLINE OFFLINE dg1
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
1
ONLINE ONLINE
dg1
ora.diskmon
1
OFFLINE OFFLINE
ora.evmd
1
ONLINE ONLINE
dg1
ora.melb.db
1
ONLINE ONLINE
dg1
Open
Phase 5: Post patch application processes and checks
5.1 Re-enable log shipping between the sites.
5.1.1 If you are using the Data Guard Broker at the Primary Site
use the broker command line utility to enable log shipping from the Primary
to the Standby site.
[oracle@dg1
patch]$ . oraenv
ORACLE_SID = [MELB] ? MELB
The Oracle base remains unchanged with value /opt/app/oracle
[oracle@dg1 patch]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect /
Connected.
DGMGRL> show configuration;
Configuration - dgdr
Protection Mode: MaxPerformance
Databases:
melb - Primary database
lond - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> show database verbose MELB
Database - melb
Role:
PRIMARY
Intended State: TRANSPORT-OFF
Instance(s):
MELB
..
.
Database Status:
SUCCESS
DGMGRL> edit database melb set
state='TRANSPORT-ON';
Succeeded.
DGMGRL> show database verbose melb;
Database - melb
Role:
PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
MELB
..
.
Database Status:
SUCCESS
5.1.2 If you are not using the Data Guard Broker to manage the
sites re-enable log shipping in the Primary site by altering the parameter log_archive_dest_state_X used to ship redo between the
sites.
Currently the environment here is set to use the log_archive_dest_2 parameter
in the Primary site for shipping to the Standby.
SQL>
show parameter log_archive_dest_2
NAME
TYPE VALUE
------------------------------------ -----------
------------------------------
log_archive_dest_2
string service="lond", LGWR ASYNC NOA
FFIRM delay=0 optional compres
sion=disable
max_failure=0 max
_connections=1 reopen=300 db_u
nique_name="lond" net_timeout=
30, valid_for=(all_logfiles,pr
imary_role)
SQL> show parameter log_archive_dest_state_2
NAME
TYPE VALUE
------------------------------------ -----------
------------------------------
log_archive_dest_state_2
string defer
To disable or defer log shipping defer the destination.
SQL> alter system set log_archive_dest_state_2=enable;
5.2 Execute the sql script to update
the dictionary information with the PSU's dictionary changes in the Primary
site database.
[oracle@dg1
patch]$ . oraenv
ORACLE_SID = [MELB] ? MELB
The Oracle base remains unchanged with value /opt/app/oracle
[oracle@dg1 patch]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 12 19:27:26 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> @?/catbundle.sql psu
apply
SP2-0310: unable to open file "/opt/app/oracle/product/11.2.0.3/dbhome_1/catbundle.sql"
SQL> @?/rdbms/admin/catbundle.sql
psu apply
PL/SQL procedure successfully completed.
..
.
SQL> PROMPT Updating registry...
Updating registry...
SQL> INSERT INTO registry$history
2 (action_time, action,
3 namespace, version, id,
4 bundle_series,
comments)
5 VALUES
6 (SYSTIMESTAMP, 'APPLY',
7 SYS_CONTEXT('REGISTRY$CTX','NAMESPACE'),
8 '11.2.0.3',
9 6,
10 'PSU',
11 'PSU 11.2.0.3.6');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SPOOL off
SQL> SET echo off
Check the following log file for errors:
/opt/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_MELB_APPLY_2014Aug12_19_29_32.log
SQL>
5.3 Check the Data Guard Broker configuration is still sound
after the upgrade.
DGMGRL>
show configuration;
Configuration - dgdr
Protection Mode: MaxPerformance
Databases:
melb - Primary database
lond - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
5.4 Check that log shipping and redo apply are
operating normally.
5.4.1 From the Primary perform a log switch and make sure the log
is shipped to the standby site.
[oracle@dg1
patch]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 12 19:37:52 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> archive log list
Database log
mode
Archive Mode
Automatic
archival
Enabled
Archive
destination
USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 32
Next log sequence to archive 34
Current log
sequence 34
SQL> alter system switch logfile
2 ;
System altered.
SQL> archive log list
Database log
mode
Archive Mode
Automatic
archival
Enabled
Archive
destination
USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 33
Next log sequence to archive 35
Current log
sequence 35
tail
-f /opt/app/oracle/diag/rdbms/melb/MELB/trace/alert_MELB.log
Tue Aug 12 19:38:33 2014
Thread 1 advanced to log sequence 35 (LGWR s | 