Advertisements
Skip to content
September 20, 2012 / Shivananda Rao P

Applying PSU Patch in a dataguard (Physical Standby) environment

Here is a brief explanation on how to apply PSU (Patch Set Update) in a dataguard environment
In this demo, I am applying PSU 11.2.0.2.4 on the Primary and standby databases.

Primary database Server : dev
Standby database Server : uat

Primary database : srprim
Standby database : srps

Primary Server:


[oracle@dev ~]$ sqlplus sys/oracle@srprim as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Sep 18 10:43:50 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, DataMining and Real Application Testing options

SQL> select status,instance_name,database_role from v$database,v$instance;

STATUS INSTANCE_NAME DATABASE_ROLE
------ ------------- ---------------
OPEN   srprim        PRIMARY

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
10

Standby Server:

[oracle@uat ~]$ sqlplus sys/oracle@srps as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Sep 18 10:46:35 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, DataMining and Real Application Testing options

SQL> select status,instance_name,database_role from v$database,v$instance;

STATUS   INSTANCE_NAME  DATABASE_ROLE
-------- -------------- ----------------------
MOUNTED  srps           PHYSICAL STANDBY

SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
10

Step 1:

Disable the log shipping from primary database to the standby database by setting the log_archive_dest_state_2 to “defer” on the primary database. Here log_archive_dest_state_2 is deferred because parameter log_archive_dest_2 is set on my primary database to point to the Standby Database.

SQL> alter system set log_archive_dest_state_2=defer;

System altered.

Step 2:

On the standby database cancel the Managed Recovery Process.

SQL> alter database recover managed standby database cancel;

Database altered.

Step 3:

PSU (Patch Set Update)/CPU (Critical Patch Update)/ Patch Set patches  always needs to be applied first on the standby database and then on the primary database. In order to apply it on the standby database, shutdown the standby database and also the listener running on the standby server.

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

[oracle@uat ~]$ lsnrctl stop

[oracle@uat ~]$ ps -ef | grep tns
oracle 6958 5107 0 10:52 pts/1 00:00:00 grep tns
[oracle@uat ~]$
[oracle@uat ~]$ ps -ef | grep pmon
oracle 4788 1 0 09:56 ? 00:00:00 asm_pmon_+ASM
oracle 6960 5107 0 10:52 pts/1 00:00:00 grep pmon

Step 4:

Now apply the PSU on the standby database.

[oracle@uat ~]$ export PATH=$PATH:/u01/app/oracle/product/11.2.0.2/db_1/OPatch
[oracle@uat ~]$ opatch version
OPatch Version: 11.2.0.3.0

OPatch succeeded.

[oracle@uat ~]$ <span style="color: #ff6600;">opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /opt/12827726</span>
Oracle Interim Patch Installer version 11.2.0.3.0
Copyright (c) 2012, Oracle Corporation. All rights reserved.

PREREQ session

Oracle Home : /u01/app/oracle/product/11.2.0.2/db_1
Central Inventory : /u01/home/oraInventory
from : /u01/app/oracle/product/11.2.0.2/db_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.2/db_1/cfgtoollogs/opatch/opatch2012-09-18_11-11-40AM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

[oracle@uat ~]$ opatch apply /opt/12827726/
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.2/db_1
Central Inventory : /u01/home/oraInventory
from : /u01/app/oracle/product/11.2.0.2/db_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.2/db_1/cfgtoollogs/opatch/12827726_Sep_18_2012_11_12_36/apply2012-09-18_11-12-35AM_1.log

Applying interim patch '12827726' to OH '/u01/app/oracle/product/11.2.0.2/db_1'
Verifying environment and performing prerequisite checks...
All checks passed.
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

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/11.2.0.2/db_1')

Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...

Patching component oracle.rdbms.rsf, 11.2.0.2.0...

Patching component oracle.rdbms, 11.2.0.2.0...

Patching component oracle.sysman.console.db, 11.2.0.2.0...

Patching component oracle.sysman.oms.core, 10.2.0.4.3...

Patching component oracle.ldap.rsf, 11.2.0.2.0...

Patching component oracle.rdbms.dv, 11.2.0.2.0...

Patching component oracle.rdbms.dbscripts, 11.2.0.2.0...

Patching component oracle.sysman.plugin.db.main.repository, 11.2.0.2.0...

Patching component oracle.rdbms.rman, 11.2.0.2.0...

Patching component oracle.sdo.locator, 11.2.0.2.0...

Verifying the update...
Patch 12827726 successfully applied
Log file location: /u01/app/oracle/product/11.2.0.2/db_1/cfgtoollogs/opatch/12827726_Sep_18_2012_11_12_36/apply2012-09-18_11-12-35AM_1.log

OPatch succeeded.

Step 5:

Once the patch has been applied on the standby database, start the listener and the standby database.


[oracle@uat ~]$ lsnrctl start

[oracle@uat ~]$ sqlplus sys/oracle@srps as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Sep 18 11:40:02 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 684785664 bytes
Fixed Size 2229640 bytes
Variable Size 197134968 bytes
Database Buffers 482344960 bytes
Redo Buffers 3076096 bytes
Database mounted.

Note: Do not run any patching scripts on the standby database (Example: catbundle.sql). We are done with the patching on the standby database. Now lets move to the primary database.

Step 6:

Shutdown the Primary database and stop the listener running on the primary database server.

<pre>
[oracle@dev ~]$ sqlplus sys/oracle@srprim as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Sep 18 11:48:26 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, DataMining and Real Application Testing options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, DataMining and Real Application Testing options

[oracle@dev ~]$ lsnrctl stop

[oracle@dev ~]$ ps -ef | grep pmon
oracle 4618 1 0 09:53 ? 00:00:00 asm_pmon_+ASM
oracle 10233 4998 0 11:50 pts/1 00:00:00 grep pmon
[oracle@dev ~]$
[oracle@dev ~]$ ps -ef | grep tns
oracle 10237 4998 0 11:50 pts/1 00:00:00 grep tns

Step 7:

Now apply the PSU patch on the Primary database.

[oracle@dev ~]$ export PATH=$PATH:/u01/app/oracle/product/11.2.0.2/db1/OPatch
[oracle@dev ~]$ opatch version
OPatch Version: 11.2.0.3.0

OPatch succeeded.

[oracle@dev ~]$ opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /opt/12827726/
Oracle Interim Patch Installer version 11.2.0.3.0
Copyright (c) 2012, Oracle Corporation. All rights reserved.

PREREQ session

Oracle Home : /u01/app/oracle/product/11.2.0.2/db1
Central Inventory : /u01/home/oraInventory
from : /u01/app/oracle/product/11.2.0.2/db1/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.2/db1/cfgtoollogs/opatch/opatch2012-09-18_11-56-11AM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

[oracle@dev ~]$ export PATH=$PATH:/u01/app/oracle/product/11.2.0.2/db1/OPatch
[oracle@dev ~]$ opatch apply /opt/12827726/
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.2/db1
Central Inventory : /u01/home/oraInventory
from : /u01/app/oracle/product/11.2.0.2/db1/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.2/db1/cfgtoollogs/opatch/12827726_Sep_18_2012_12_17_40/apply2012-09-18_12-17-39PM_1.log

Applying interim patch '12827726' to OH '/u01/app/oracle/product/11.2.0.2/db1'
Verifying environment and performing prerequisite checks...
All checks passed.
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

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/11.2.0.2/db1')

Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...

Patching component oracle.rdbms.rsf, 11.2.0.2.0...

Patching component oracle.rdbms, 11.2.0.2.0...

Patching component oracle.sysman.console.db, 11.2.0.2.0...

Patching component oracle.sysman.oms.core, 10.2.0.4.3...

Patching component oracle.ldap.rsf, 11.2.0.2.0...

Patching component oracle.rdbms.dv, 11.2.0.2.0...

Patching component oracle.rdbms.dbscripts, 11.2.0.2.0...

Patching component oracle.sysman.plugin.db.main.repository, 11.2.0.2.0...

Patching component oracle.rdbms.rman, 11.2.0.2.0...

Patching component oracle.sdo.locator, 11.2.0.2.0...

Verifying the update...
Patch 12827726 successfully applied
Log file location: /u01/app/oracle/product/11.2.0.2/db1/cfgtoollogs/opatch/12827726_Sep_18_2012_12_17_40/apply2012-09-18_12-17-39PM_1.log

OPatch succeeded.

Step 8:

Start the listener on the primary database server and also start the Primary database.


[oracle@dev ~]$ lsnrctl start

[oracle@dev ~]$ sqlplus sys/oracle@srprim as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Sep 18 12:28:35 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 684785664 bytes
Fixed Size 2229640 bytes
Variable Size 222300792 bytes
Database Buffers 457179136 bytes
Redo Buffers 3076096 bytes
Database mounted.
Database opened.
SQL>

Step 9:

Now enable log shipping on the primary database by setting the log_archive_dest_state_2 to “enable”. As I said earlier, parameter log_archive_dest_2 on my primary database is set to point to the standby database.

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

Step 10:

Start the Managed Recovery Process (MRP) on the standby database.

[oracle@uat ~]$ sqlplus sys/oracle@srps as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Sep 18 12:33:03 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, DataMining and Real Application Testing options

SQL> alter database recover managed standby database disconnect;

Database altered.

SQL> select process,status,sequence# from v$managed_standby;

PROCESS STATUS        SEQUENCE#
------- ------------- -----------
ARCH    CONNECTED     0
ARCH    CONNECTED     0
ARCH    CONNECTED     0
ARCH    CONNECTED     0
RFS     IDLE          0
RFS     IDLE          13
RFS     IDLE          0
RFS     IDLE          0
MRP0    WAIT_FOR_LOG  13

9 rows selected.

Step 11:

On the primary database, run the patching scripts like “catbundle.sql” in this case.
The script run generates archives and these archives would be shipped and applied to the standby database. So, there is no requriement to run the patching scripts on the standby database.


[oracle@dev ~]$ sqlplus sys/oracle@srprim as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Sep 18 12:31:52 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, DataMining and Real Application Testing options

SQL> @?/rdbms/admin/catbundle.sql psu apply

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
14

Step 12:

Check if the PSU applied shows up in the primary database by querying the registry$history or dba_registry_history view.


SQL> select * from registry$history order by action_time desc;

ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS BUNDLE_SER
------------------------------ ---------- ---------- --------------- ---------- ------------------------- ----------
18-SEP-12 12.32.44.728562 PM APPLY SERVER  11.2.0.2 4 PSU 11.2.0.2.4 PSU

Step 13:

Make sure that the latest archive applied on the standby database is the latest archive generated on the primary database. You can see below that the latest archive sequence applied on the standby database is sequence 14 and the latest sequence generated on the primary database too is 14. Now, check if the PSU applied shows up in the standby database by querying the registry$history or dba_registry_history view.


[oracle@uat ~]$ sqlplus sys/oracle@srps as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Sep 18 12:44:25 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, DataMining and Real Application Testing options

SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
14

SQL> select * from registry$history order by action_time desc;

ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS BUNDLE_SER
----------------------------------- ---------- ---------- ---------- ---------- ------------------------- ----------
18-SEP-12 12.32.44.728562 PM APPLY SERVER 11.2.0.2 4 PSU 11.2.0.2.4 PSU

We can see that the PSU is applied successfully on both Primary and standby databases.

Here we go !!

 

COPYRIGHT

© Shivananda Rao P, 2012, 2013, 2014. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.

DISCLAIMER

The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.

Advertisements

14 Comments

Leave a Comment
  1. Syed / Oct 3 2012 11:16 am

    this is a very quick reference!! easy to understand ,,

  2. gouthamk10 / Oct 5 2012 10:51 am

    Hi
    good doc.nice step by step process.
    thanks for the doc.

  3. pavan / Nov 25 2012 11:39 am

    yes its verys understandable way thanks

  4. Shivananda Rao P / Nov 25 2012 4:26 pm

    Thank you !

  5. Jamsher / Apr 24 2013 5:46 am

    Thanks for writing post in such a simple language….

  6. Jamsher / Apr 24 2013 5:58 am

    Hi Shiva,
    Can you please post an article on how we can recover standby database after incomplete recovery of primary..
    — Thanks & Regards

  7. Shivananda Rao P / Apr 24 2013 2:31 pm

    Hello Jamsher,

    Once you do an incomplete recovery of the primary database and open it with the resetlogs, the archive sequence would start from 1 and the standby would be out of sync. You’ll have to rebuild the standby database in this case.

    Regards,
    Shivananda

  8. Jamsher / Apr 25 2013 5:18 am

    Hi Shiva,

    I just read the below post which says that
    http://oracleinaction.blogspot.in/2012/12/DGFBRESETLOGS.html
    If primary database is opened with resetlogs option following an incomplete recovery, redo apply service might halt on standby database when it encounters open resetlogs command in redo. But if physical standby database’s SCN is far enough behind the primary database’s SCN, then redo apply service can interpret open resetlogs command without stopping.
    Hence , after primary has been opened with resetlogs option, if we flashback standby database to an SCN which is earlier than reset logs SCN# of primary, we won’t have to recreate the standby database.

    Regards
    Jamsher

  9. Shivananda Rao P / Apr 25 2013 5:34 am

    Yes, if flashback is enabled, then you can use this feature. Please refer my document on failover under the dataguard section.

    Regards,
    Shivananda.

  10. Leah / Aug 7 2013 4:33 am

    Saved my life during the production upgrade… thank you so much.

  11. srinivasa reddy bijjam / Mar 18 2014 11:56 am

    excellent .. mind blowing …. superb …… UN-believable ….
    this article makes me good understand of applying patches on data-guard environment
    nice procedure you are following in every article and is understandable to everybody…..

    thank u verymuch

  12. Shivananda Rao P / Mar 21 2014 5:18 am

    Thanks Srinivasa.

  13. Munavver / May 9 2014 2:54 am

    Hi Shiva,
    Thanks for your guidence.Very good and easy doc.

    Good going

  14. Robert / Aug 6 2014 1:56 pm

    Very nice, succinct summary. Very helpful!
    Thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: