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>
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>
ORACLE_HOME
.SQL> select owner,object_name,object_type,status from dba_objects where status <> 'VALID' and OWNER !='PUBLIC' and OBJECT_TYPE!='SYNONYM';
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 ~]$
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]$
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]$
[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 ~]$
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>
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
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
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.
We have seen how to apply an interim/bug fix (9711859
) step by step in a Data Guard environment containing a logical standby database.
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.