Category Archives: Oracle Metalink

How to find latest oracle database patchset

It is sometimes a bit of a hassle, to have the latest patch name or number on hand, when you need them. Ok, you may search on My Oracle Support and save it as custom search. But it may happen that the search is inaccurate and the required patch is not found. A much easier way is to use the Oracle Metalink Notes, which have been available for a while. These MOS Notes are updated regularly with the latest patch information. My favorite is definitely the Quick Reference to Patch Numbers for Database PSU, SPU(CPU), Bundle Patches and Patchsets [1454618.1]. But there are more interesting MOS notes.

Which Patch’s are available?

MOS notes about patches, patch sets, PSU, SRU and bundle patches :

  • Introduction to Oracle Recommended Patches [756388.1]
    This MOS notes is the main entry to the Oracle recommended patches. It includes further links to Oracle Database, Oracle Enterprise Manager, Oracle Fusion Middleware and other products.
  • Oracle Recommended Patches — Oracle Database [756671.1]
    This notes includes the links for the latest recommended patches of Oracle Databases on Unix and Linux since Oracle 10.2.0.3
  • Oracle Database, Networking and Grid Agent Patches for Microsoft Platforms [161549.1]
    As the name says, this note contains further links for recommended patches of Oracle Databases on Microsoft Windows
  • Quick Reference to Patch Numbers for Database PSU, SPU(CPU), Bundle Patches and Patchsets [1454618.1]
    This MOS note is some kind of a master note for any PSU, CPU, Bundle Patches and Patchset. Here you’ll find any patch number without struggling yourself first through all the Oracle recommendations :-)
  • Release Schedule of Current Database Releases [742060.1]
    On this MOS Note you do not really find any patch numbers or names but you’ll find the release schedules of upcoming patch set. Ok you do not see an exact date. But at least the quarter of the year.

Which Patch has been installed?

The easies way to list the installed patches in the current ORACLE_HOME is to use the patch utility.

List of installed patches:

$ORACLE_HOME/OPatch/opatch lsinventory

Grep on the patch description:

$ORACLE_HOME/OPatch/opatch lsinventory|grep "Patch description"
Patch description:  "Database Patch Set Update : 11.2.0.3.7 (16619892)"

A more verbose list on the installed patches:

$ORACLE_HOME/OPatch/opatch lsinventory -details

Which Patch has been applied?

The table REGISTRY$HISTORY does contain information on applied patches respectively PSU, SRU or CPU. SinceSince I use this query regularly during the tests of the Critical Patch Update, I have it packed in a handy script ( cpui.sql).

SET linesize 200 pagesize 200
col action_time FOR a28
col version FOR a10
col comments FOR a35
col action FOR a25
col namespace FOR a12
SELECT * FROM registry$history;

Fast lane to ORA-00600 service requests

There are several way’s to find information on internal oracle errors like ORA-00600 and ORA-07445. Looking up google is one of them, but not in any case the best one. If you are using Oracle Metalink you may just search for the ORA-00600 and the error code of first argument or use the ORA-600/ORA-7445/ORA-700 Error Look-up Tool [153788.1], which is available since several years. But sometimes you do not get the expected results and you have to open an Oracle service request to get more information, bug fix, workaround, justification that you really haft this issue, etc. It is then a bit cumbersome to add all kind of information and upload files to create the SR. Since a while Oracle provides simple troubleshooting tools in My Oracle Support. Rather than searching for the error you just have to upload the IPS package created with Oracle adrci. Based on the information in the IPS package, Oracle suggests possible causes / solutions. Optional one has the possibility to open an SR based directly on the IPS package. The troubleshooting tool can directly be accessed ORA-00600 or will be opened if you add ORA-00600 in the error message field when creating an SR. At the end your problem and you SR is probably not solved faster, but at least it is a bit easier to open an SR. :-)

Similar troubleshooting tools are also available for ORA-07445, ORA-04030 and ORA-04031

Build the Package

Start ADRCI and set the proper ADR home

oracle@urania:~/[TDB01] adrci

ADRCI: Release 11.2.0.3.0 - Production on Mon Jun 3 10:54:39 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

ADR base = "/u00/app/oracle"
adrci> show home
ADR Homes:
diag/rdbms/tdb01/TDB01
diag/tnslsnr/urania/listener
adrci> set home diag/rdbms/tdb01/TDB01

List the last 5 incidents

adrci> show incident -last 5

ADR Home = /u00/app/oracle/diag/rdbms/tdb01/TDB01:
*************************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
----------- --------------------------------------- ----------------------------------------
2995358 ORA 700 [dbghmo_read_msgobj_mt_bygrpid] 2013-06-01 23:32:03.985000 +02:00
2932162 ORA 600 [3619] 2013-06-01 21:05:10.287000 +02:00
2911681 ORA 600 [3619] 2013-06-01 20:27:38.752000 +02:00
2891200 ORA 600 [3619] 2013-06-01 20:25:49.517000 +02:00
2851271 ORA 700 [dbghmo_read_msgobj_mt_bygrpid] 2013-06-01 18:23:27.147000 +02:00
First 5 rows fetched (*** more available ***)

For an Oracle SR we need a physical package of the incident. This can be generated with just a simple command.

adrci> IPS PACK INCIDENT 2932162 IN /tmp
Generated package 6 in file /tmp/ORA600361_20130603113930_COM_1.zip, mode complete

References

More information on ADR, Incidents and ORA-00600 troubleshooting can be found in the following MOS notes:

ADR

  • 11g Understanding Automatic Diagnostic Repository [ID 422893.1]
  • Database 11g: Quick Steps to Package and Send Critical Error Diagnostic Information to Support [ID 443529.1]
  • ADR Different Methods to Create IPS Package [ID 411.1]
  • 11g How to Unpack a Package in to ADR [ID 745960.1]
  • How to use the ADRCI interface to generate a SQL test case for an incident [ID 1330207.1]
  • Retention Policy for ADR [ID 564269.1]
  • 11g How To Find Out Trace Files Relevant To A Critical Error [ID 443536.1]

ORA-600/7445 Internal Errors

Oracle Database Firewall Patch

Earlier this year, oracle released there first version of Oracle Database Firewall. Since a couple of week’s now a bunch of patches are available. The latest seems to be available since today. As usual the patch can be obtained through the Patch Search on My Oracle Support.

Installation

The patches are delivered as a ZIP archive with misc RPM. These RPM’s have to be copied onto the Database Firewall or the Management Server. As soon as this is done the can be installed with rpm as root. After a reboot the update is complete. By the way, to be able to copy the patch’s and access the system via ssh / command line, terminal access has to be enabled in the system configuration (DB Firewall Web Console).

Installing the latest patch on the Database Firewall would be done according the following procedure :

scp dbfw-multi-5.0-134.i686.rpm support@192.168.1.200:/tmp/

ssh support@192.168.1.200

su -
cd /tmp

rpm --freshen --repackage dbfw-multi-5.0-134.i686.rpm

/bin/ls /var/spool/repackage -t | /usr/bin/head -n1 \
 > /usr/local/dbfw/updates/lastrpm

reboot

Don’t forget to review the readme.txt before installing any patch. It’s a text file very short and readable within seconds ;-)

List of available Patches

Below you find a list of available patch’s for Oracle Database Firewall. The amount of patches is still manageable, so that the entire list fits into this post ;-) There is one more (11794289) but this one is supersede by bundled patch 1.

Patch Name Description Release Platform Updated Size
11872181 BUNDLED PATCH 1 5.0 Linux x86 Mar 16, 2011 61.1 MB
11886996 HOT FIX FOR BNY RESTORING CONFIGURATION 5.0 Linux x86 Mar 18, 2011 10.9 MB
11934644 BNY Audit Reports 5.0 Generic Platform Mar 30, 2011 11.1 MB
11873566 LOG SEARCH RESULTS: LONG (>4000 BYTE) STATEMENTS CAUSING ERROR IN SYSTEM LOG 5.0 Linux x86 Apr 7, 2011 11.1 MB
12317493 DBFW 5.0 – BUNDLED PATCH 2 5.0 Linux x86 Apr 12, 2011 64.8 MB
12391703 Alerting and Reporting Hot Fix 5.0 Linux x86 May 10, 2011 50.3 MB

Reference and Links

Script to download Oracle Patch

Downloading Oracle software, patch or patch-set via web browser is handy if you need the software on your client PC or if you just download small patch’s. As soon as you want, however, download a greater volume of patches or a large patch set, it gets cumbersome. After downloading the patch must be copied to the target system. All steps could be quite time consuming depending on your network throughput.

WGET Option

Since a while it is possible to select a WGET Option in the download dialog rather than downloading each file individual (red box in the picture below).

MOS download dialog

In a new dialog box you then my download or copy the wget download script for the selected patch’s.

MOS download wget

Before starting the download via script the MOS credential have to be modified eg. SSO_USERNAME=youraccount
SSO_PASSWORD=yourpassword

But…

So far so good, but currently it is now working. According to Oracle Support this is a known issue and there is the Bug 12372706: WGET SCRIPTS FROM MOS FAIL IN PRODUCTION.

To workaround each file has to be downloaded manually with wget.

wget --http-user=username --http-password=password --no-check-certificate \
--output-document=filename "paste the above copied address here in quotes"

The URL’s can be copied from the download dialog above. If more than just one patch have to be downloaded wget can be put in a for loop which get’s the URL’s from a text file.

Download URL’s

Text File with Patch URL’s

#Linux x86-64
https://updates.oracle.com/Orion/Services/download/p10098816_112020_Linux-x86-64_1of7.zip?aru=13149219&patch_file=p10098816_112020_Linux-x86-64_1of7.zip
https://updates.oracle.com/Orion/Services/download/p10098816_112020_Linux-x86-64_2of7.zip?aru=13149219&patch_file=p10098816_112020_Linux-x86-64_2of7.zip
https://updates.oracle.com/Orion/Services/download/p10098816_112020_Linux-x86-64_3of7.zip?aru=13149219&patch_file=p10098816_112020_Linux-x86-64_3of7.zip
https://updates.oracle.com/Orion/Services/download/p10098816_112020_Linux-x86-64_4of7.zip?aru=13149219&patch_file=p10098816_112020_Linux-x86-64_4of7.zip
https://updates.oracle.com/Orion/Services/download/p10098816_112020_Linux-x86-64_5of7.zip?aru=13149219&patch_file=p10098816_112020_Linux-x86-64_5of7.zip
https://updates.oracle.com/Orion/Services/download/p10098816_112020_Linux-x86-64_6of7.zip?aru=13149219&patch_file=p10098816_112020_Linux-x86-64_6of7.zip
https://updates.oracle.com/Orion/Services/download/p10098816_112020_Linux-x86-64_7of7.zip?aru=13149219&patch_file=p10098816_112020_Linux-x86-64_7of7.zip

Download more patch

For loop to download the patch’s:

for i in $(cat download_url.txt|grep -v ^#)
do
OUTPUT_FILE=$(echo "$i"|cut -d= -f3)
echo "download $OUTPUT_FILE from '$i'" >> $LOGFILE 2>&1
wget --http-user=MOS_USER --http-password=MOS_PASSWORD --no-check-certificate \
-O $OUTPUT_FILE "$i" >> wget_logfile.log 2>&1
done

MOS Download Script

I’ve put everything in a small script. To download the patch a text with the download URL’s have to be specified

mos_download_url.sh -h
INFO : Usage, mos_download_url.sh  [-hv]
INFO :        -h             Usage (this message)
INFO :        -u <user>      MOS user account
INFO :        -p <password>  MOS password
INFO :        -f <file>      Text file with download url
INFO :                       Logfile : mos_download_url.sh-04-22-11-1422.log
</file></password></user>

Run the script with nohup on a stage server to download a few patchs.

nohup mos_download_url.sh  -u me@domain.com -p secret -f download_url.txt &

The mos_download_url.sh script can be downloaded in the script section of OraDBA or direct ( mos_download_url.sh).

Metalink Notes, some favorites

If time permits, I try to have a look once a day at the Knowledge Articles listed on my My Oracle Support (MOS) Dashboard. I’ve configured my Dashboard to show recently updated knowledge articles and alerts. Before Metalink was migrated to the flash based My Oracle Support, it was possible to configure a regularly e-mail newsletter with similar information but with MOS this somehow does not work anymore for me. Nevertheless going through the notes and alerts is a simple way to learn everyday something new about Oracle. Especially reading the alerts about bug you will find from time to time interessting internal stuff. Tanel Poder wrote about this topic in his blog quite a while ago.

During my daily MOS review I realized, that there are since a while more and more notes around with titles like Master Note for… or All about…. These notes provides often a rough overview and a good starting point to dig a bit deeper into a particular topic. For my first technical post I thought it would be a good idea to providing a list with some of these notes. Due to the fact that my blog audience is not yet that big, the list is at least a nice personel reference to some notes :-) The list is not concluding, I only pick a few which a read or plan to read. There are much more…

Not all of the notes have the same quality but nevertheless they are a good staring point into a certain topic. For a bether overview I’ve put them in a few different categories. Some of the notes would fit into more than just one category. For now I just post this list as a reference. I will probably look a bit deeper into some of the notes in a later post. By the way, you probably have to log into MOS before you click on one of theese links.

Security

Database Security and Security Feature related Notes.

  • All About Security: User, Privilege, Role, SYSDBA, O/S Authentication, Audit, Encryption, OLS, Database Vault, Audit Vault [207959.1]
  • Master Note For Transparent Data Encryption [1228046.1]
  • Quick and dirty TDE Setup and FAQ [1251597.1]
  • Managing TDE wallets in a RAC environment [567287.1]
  • Master Note For Oracle Database Vault [1195205.1]
  • Master Note For Oracle Audit Vault [1199033.1]

Backup & Recovery, Availability and related Features

Master notes related to backup & recovery and availability.

Database Feature, Options and Components

A few master notes on database components and features.

  • Master Note for Oracle Data Mining [1087643.1]
  • Master Note for Oracle JVM [1098505.1]
  • Master Note for Oracle Multimedia/interMedia [1078222.1]
  • Master Note for Oracle Spatial and Oracle Locator Installation [220481.1]
  • Master Note for Oracle XML Developer’s Kit [1133826.1]
  • Master Note for the Oracle OLAP Option [1107593.1]
  • Master Note for OLTP Compression [1223705.1]
  • Master Note for Oracle XML DB Protocols: FTP HTTP HTTPS WebDAV, APEX and Native Database Web Services [1083991.1]
  • Master Note for Oracle Database Machine and Exadata Storage Server [1187674.1]
  • Master Note for Automatic Storage Management (ASM) [1187723.1]
  • Master Note for Transportable Tablespaces (TTS) — Common Questions and Issues [1166564.1]

Troubleshooting and Performance Tuning

A few master notes related to database troubleshooting and performance tuning.

  • Introduction to 600/7445 Internal Error Analysis [390293.1]
  • How to deal with ORA-600 Internal Errors [146581.1]
  • Master Note for Diagnosing ORA-600 [1092832.1]
  • Master Note for Diagnosing ORA-7445 [1092855.1]
  • Master Note: How to diagnose Database Performance – FAQ [402983.1]
  • Master Note: Query Tuning Overview [199083.1]
  • FAQ: Query Tuning Frequently Asked Questions [398838.1]
  • Diagnostics for Query Tuning Problems [68735.1]
  • Master Note for Diagnosing ORA-4030 [1088267.1]
  • Master Note for Diagnosing ORA-4031 [1088239.1]
  • ORA-4031 Common Analysis/Diagnostic Scripts [Video][430473.1]
  • 11g Understanding Automatic Diagnostic Repository. [422893.1]
  • 11g Diagnosability: Frequently Asked Questions [453125.1]
  • Master Note for Troubleshooting Oracle Managed Distributed Transactions [100664.1]

Oracle Enterprise Manager Grid Control

Master notes for Grid Control, DB Console and related Tools.

  • Master Note for 10g Enterprise Manager Grid Control Security Framework [1092513.1]
  • Master Note for 10g Grid Control Enterprise Manager Communication and Upload issues [1086343.1]
  • Master Note for 10g Grid Control OMS Process Control (Start, Stop and Status) & Configuration [1081865.1]
  • Master Note for 10g Grid Control OMS Performance Issues [1161003.1]
  • Master Note for 10g Grid Control Agent Process Control (Start, Stop & Status) & Configuration [1082009.1]
  • Master Note for Diagnostic Tools for 10g Enterprise Manager Grid Control Components [1098262.1]
  • Master Note for Target Maintenance Through 10g Enterprise Manager Grid Control [1202453.1]
  • Master Note for 10g Grid Console Browser / User-interface Issues [1190323.1]
  • Master Note for 10g Enterprise Manager Grid Control Agent Performance & Core Dump issues [1087997.1]
  • Master Note for Grid Control 11.1.0.1.0 Installation and Upgrade [1067438.1]
  • EMDIAG Master Index [421053.1]

Miscellaneous

A lot of other notes which did not fit in one of my 3-4 sections.

  • Master Note For Oracle Database Upgrades and Migrations [1152016.1]
  • Master Note For Oracle Database Downgrade [1151427.1]
  • Master Note For Oracle Database Server Installation [1156586.1]
  • Master Note of Linux OS Requirements for Database Server [851598.1]
  • Master Note for Inventory [1192649.1]
  • Master Note on Oracle Universal Installer (OUI) for installing Oracle Database Software [1157464.1]
  • Master Note For Cloning Oracle Database Server ORACLE_HOME’s Using the Oracle Universal Installer (OUI) [1154613.1]
  • Master Note For OPatch [293369.1]
  • Master Note For Oracle Database Client Installation [1157463.1]
  • Master Note For The Oracle ODBC Driver [741033.1]
  • Master Note For The Oracle Provider for OLE DB (OLE DB) [1132946.1]
  • Master Note Index for Oracle Application Server (Fusion Middleware) [1074916.1]

One more thing…

If you do not like the flash version of MOS there is also a HTML version around. The functionality is a bit limited, but you still can access all important information. Very handy if you access MOS from a device without flash (e.g. iPad). The HTML version is available through the link http://supporthtml.oracle.com. More information to the HTML version can be found in the article Master Note for My Oracle Support – TUGBU Tools and Tips (1151721.1] or My Oracle FAQ [747242.5]