Interim patches

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.1.0.6
Oracle Universal Installer - Version: 10.2.0.1 to 11.1.0.6
Information in this document applies to any platform.

Purpose

This note explains about the frequently asked questions related to Interim (One-off patches)

Questions and Answers

What is an interim patch ?

• Interim patches are bug fixes that are made available to customers in response to specific bugs.
• They require a particular base release or patchset to be installed before they can be applied.
• They generally address specific bugs for a particular customer.
• These patches are not versioned and are generally made available in a future patchset as well as the next product release

How to install/Rollback an interim patch ?

Interim patches can be installed/Rollbacked using the patching tool called opatch.Opatch can be found under the Oracle_Home/Opatch (by default)
Note 224346.1 Opatch - Where Can I Find the Latest Version of Opatch?

How can you list all the installed patches in your oracle home?

Interim patches can be listed using the patching tool called opatch.Opatch can be found under the Oracle_Home/Opatch (by default).  You can use the command

% opatch lsinventory -all

What is the Structure of an interim patch ?

• Interim patches generally come in a zipped format.
• You need to unzip them before you apply a patch.
  The following figure illustrates the structure of the interim patch.
  

What are the contents of an interim patch ?

The interim patches have the following contents :

• Patch Metadata: This contains information on the patch ID, the bugs fixed, the files affected, and the actions to be performed.
• Payload: This contains the files that will be modified by OPatch.
• Custom Scripts: Pre-processing and post-processing scripts that needs to be run before and after patching.

How to download a patch?

• To obtain a patches from MetaLink:
• Select the Patches and Updates tab after logging in to Oracle MetaLink.
• Click Simple Search.
• In the Search By field, select Patch Number from the list.
• Enter the patch number. The patch number may be different for different product releases and platforms
• Select the platform.
• Click Go.
• Read any applicable notes before downloading, then click the Download button.

Note: Please review the Readme file for instructions on how to install the patch.

What is the difference between an interim patch and a Patchset ?

Patchset are a small collection of files that are copied over an existing installation. They are associated to particular versions of Oracle Products. Patch set , when applied to the correct version of an installed product, results in an upgraded version of the product.

Interim patches are bug fixes that are made available to customers in response to specific bugs. They require a particular base release or patchset to be installed before they can be applied. They generally address specific bugs for a particular customer.These patches are not versioned and are generally made available in a future patchset

Is CPU (Critical Patch Update) an Interim patch ?

Yes, CPU is an Interim patch 

• A Critical Patch Update (CPU) is a collection of patches for multiple security vulnerabilities. 
• Oracle provides CPUs for all product offerings on a quarterly schedule. 
• CPU is cumulative patch.

For more details please refer to the Note 360470.1( Security Alerts and Critical Patch Updates-Frequently Asked Questions )

Flash Recovery Area Full

FLASH RECOVERY AREA FULL
 
Database startup fails:
ORA-16038: log one sequence 3144 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312 online log 1 thread 1 <path to redo log file>

Cause
Using the default Oracle-Suggested strategy as implemented by EM in Oracle10gR1
Retention policy changed from the default redundancy of 1 to recovery window of  15 days
When the retention policy is changed to recovery window or to a redundancy > 1, RMAN cannot satisfy the retention policy if there is only one copy of database and it is recovered to the latest point in time.  In order to satisfy the
retention policy, it keeps all the incrementals and archivelogs since database creation
Solution
1) Increase the parameter db_recovery_file_dest_size
     SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=4g SCOPE=BOTH;
2) Stop using the db_recovery_file_dest by unsetting the parameter. 
    (This assumes you never really wanted to use this option)
3. Backup and delete archivelogs
    RMAN> backup archivelog all delete input;
It is the dba's responsibility to ensure that the FRA size is sufficient to account for their retention policy.

Autoextend on script

spool autoext.sql
 
select
   'alter database datafile '||
   file_name||
   ' '||
   ' autoextend on;'
from
   dba_data_files;
 
@autoext

User Sessions

 Finding and Killing user session in Oracle

SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45

SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND';

   INST_ID        SID    SERIAL#   SPID       USERNAME   PROGRAM
   ----------   ---------- ----------  ---------- --------------- ---------------------------------------------
         1             30           15            3859        TEST         sqlplus@oel5-11gr2.localdomain (TNS V1-V3)
         1             23           287          3834        SYS           sqlplus@oel5-11gr2.localdomain (TNS V1-V3)
         1             40          387           4663                          oracle@oel5-11gr2.localdomain (J000)
         1             38          125           4665                          oracle@oel5-11gr2.localdomain (J001)



SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Fragmentation

Script to Find out Fragmentation

 
select substr(de.owner,1,8) "Owner",
substr(de.segment_type,1,8) "Seg Type",
substr(de.segment_name,1,20) "Table Name (Segment)",
substr(de.tablespace_name,1,20) "Tablespace Name",
count(*) "Frag NEED"
from dba_extents de, v$datafile df
where de.owner <> 'SYS'
and de.file_id = df.file#
and de.segment_type in ('TABLE','INDEX')
group by de.owner, de.segment_name, de.segment_type, de.tablespace_name,
df.name
having count(*) > 40
order by count(*) desc;