Time for action – applying a patch on logical standby

  1. Disable log shipping in a standby database and stop SQL Apply in it. First we need to stop SQL Apply in the standby database and disable log shipping from the primary database as follows:
    SQL> select db_unique_name,database_role from v$database;
DB_UNIQUE_NAME  DATABASE_ROLE
--------------- ----------------
turkey_un       PRIMARY
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2='DEFER';
System altered.
SQL>
  2. Stop SQL Apply in the logical standby database as follows:
    SQL> select db_unique_name,database_role from v$database;
DB_UNIQUE_NAME  DATABASE_ROLE
--------------- ----------------
INDIA_UN        LOGICAL STANDBY
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
Database altered.
SQL>
  3. Stop the database services of the primary and standby and perform a backup of ORACLE_HOME.
  4. After applying a patch, more objects can become invalid. Hence. gather all the invalid objects and keep a count of them so that they can be recompiled after the activity as follows:
    SQL> select owner,object_name,object_type,status from dba_objects  where status <> 'VALID' and OWNER !='PUBLIC' and OBJECT_TYPE!='SYNONYM';
  5. Ensure a valid and latest Cold/RMAN backup prior to applying the patch, and also ensure that all the applications are stopped completely. You can check for active sessions from v$session.
    SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 
[oracle@oracle-primary ~]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 15-DEC-2012 22:52:16
Copyright (c) 1991, 2009, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
[oracle@oracle-primary ~]$
  6. Make sure that no Oracle-related services are running and perform a backup of ORACLE_HOME and of Oracle's inventory using the tar command as follows:
    [oracle@oracle-primary backup]$ tar -zcpvf  /home/oracle/backup/11.2.0_Home_Inventory_Backup_$(date +%Y%m%d).tar.gz /u01/home/oracle/product/11.2.0/db_1 /u01/app/oraInventory
/u01/home/oracle/product/11.2.0/db_1/
/u01/home/oracle/product/11.2.0/db_1/uix/
..............
/u01/app/oraInventory/ContentsXML/inventory.xml
/u01/app/oraInventory/ContentsXML/comps.xml
/u01/app/oraInventory/ContentsXML/libs.xml
[oracle@oracle-primary backup]$

    Tip

    You can use the tar ball in case there are any libraries that are corrupted and you are unable to access the Oracle home after applying the patch.

  7. Apply a patch on both primary and standby.
  8. We apply patch 9711859, which is a fix for Patch 9711859: ORA-600 [KTSPTRN_FIX-EXTMAP] DURING EXTENT ALLOCATION on both primary and standby databases. We have already performed the prerequisite check to apply the patch and ensured that you have exported OPatch to the environment path to use the OPatch utility as follows:
    [oracle@oracle-primary 9711859]$ export PATH=/u01/home/oracle/product/11.2.0/db_1/OPatch:$PATH
[oracle@oracle-primary 9711859]$ opatch apply
Oracle Interim Patch Installer version 11.2.0.3.0
...............
Applying interim patch '9711859' to OH '/u01/home/oracle/product/11.2.0/db_1'
Verifying environment and performing prerequisite checks...
All checks passed.
.............
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Patching component oracle.rdbms, 11.2.0.1.0...
Verifying the update...
Patch 9711859 successfully applied
Log file location: /u01/home/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/9711859_Dec_15_2012_23_46_16/apply2012-12-15_23-46-16PM_1.log
OPatch succeeded.
[oracle@oracle-primary 9711859]$
  9. Once you initiate patching on the database server, the patch will prompt you to enter the support identifier's e-mail address for sending frequent updates on latest patches. The patch will then ask you to give a confirmation. Now verify that the patch has been applied and you are able to view it from the inventory as follows:
    [oracle@oracle-primary ~]$ opatch lspatches -bugs
9711859;;9711859
[oracle@oracle-primary ~]$

    or

    [oracle@oracle-primary ~]$ opatch lsinventory|grep 9711859
Patch  9711859      : applied on Sat Dec 15 23:47:18 IST 2012
     9711859
[oracle@oracle-primary ~]$
  10. You must perform the previous steps in both primary and standby databases.
  11. Start the primary database, the logical standby databases, and listeners, and enable apply services. Enable log shipping from the primary database as follows:
    SQL> startup
ORACLE instance started.
Total System Global Area 2238099456 bytes
Fixed Size                  2215304 bytes
Variable Size            1040188024 bytes
Database Buffers         1191182336 bytes
Redo Buffers                4513792 bytes
Database mounted.
Database opened.
SQL> alter system set log_archive_dest_state_2='enable';
System altered.
SQL>
  12. Start the database, listener, and SQL Apply from the logical standby database as follows:
    SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
Database altered.
SQL>
    Sun Dec 16 00:02:27 2012
RFS LogMiner: Registered logfile [/u01/home/oracle/product/11.2.0/db_1/dbs/arch1_920_788992101.dbf] to LogMiner session id [2]
LOGMINER: Alternate logfile found, transition to mining logfile for session 2 thread 1 sequence 920, /u01/home/oracle/product/11.2.0/db_1/dbs/arch1_920_788992101.dbf
LOGMINER: End   mining logfile for session 2 thread 1 sequence 920, /u01/home/oracle/product/11.2.0/db_1/dbs/arch1_920_788992101.dbf

    Tip

    If you are using logical standby with RAC, you have to perform the same steps on each of the nodes, restart the database, and then start SQL Apply.

  13. Verify the logical standby SQL Apply from the standby database. Use the following query to ensure that the redo transport service is working properly in the V$DATAGUARD_STATS view:
    SQL> SELECT NAME, VALUE, TIME_COMPUTED FROM V$DATAGUARD_STATS WHERE NAME='transport lag';
NAME                 VALUE                TIME_COMPUTED
-------------------- ------------------ ------------------------
transport lag        +00 00:01:00         12/16/2012 00:08:37
  14. You can also monitor the status of the redo transport service that has been transferred from the primary and the sequences that are being archived on the logical standby, using the following query:
    SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      WRITING               1          5      30720       2048
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CLOSING               1        919      28672       1776
ARCH      CLOSING               1        920          1          1
RFS       IDLE                  0          0          0          0
RFS       WRITING               1        921     149579       2048
RFS       RECEIVING             0          0          0          0

We have successfully applied a bug fix in the logical database environment and double-checked if log shipping is active after the patching, as shown previously.

Tip

To know the applied patches on ORACLE_HOME, use the following commands. It shows the patches applied with the date and time as follows:

$opatch lsinventory –all

$opatch lsinventory -detail

What just happened?

We have seen how to apply an interim/bug fix (9711859) step by step in a Data Guard environment containing a logical standby database.

How to apply a PSU patch on physical standby database using broker?

The CPU or PSU patches are a collection of security fixes. They are released every quarter, that is, four times a year. The CPU patches contain overall security fixes of each quarter and the PSU patches, and are cumulative. Once you have applied PSU, you can further apply only PSU for future quarters until the database is upgraded to the new base version. In this example, we will see how to apply the PSU patch on the physical standby database managed by the broker.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.145.109.8