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 : OPDB1
Standby
database Server : OPDB2
Primary
database : DCPROC
Standby
database : DRPROC
Primary Server: DCPROC
[oracle@dev ~]$
sqlplus sys/oracle@DCPROC 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 DCPROC
PRIMARY
SQL> select
max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
10
Standby Server: DRPROC
[oracle@uat ~]$
sqlplus sys/oracle@DRPROC 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 DRPROC
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 ~]$ 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/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@DRPROC 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.
[oracle@dev ~]$
sqlplus sys/oracle@DCPROC 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@DCPROC 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@DRPROC 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@DCPROC 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@DRPROC 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.
No comments:
Post a Comment