Oracle Database: Select BLOB via DB Link


Today i’vh tried to select a simple blob over da database link. I know there are some restrictions using database links but in my business case i’vh to do this. So my goal is to transparenty select blobs over a database link from a remote database.

First I havn’t found any suiteable workaround. Here my references from support.oracle.com:

  • SELECT with a LOB and DBLink Returns an ORA-22992: Cannot Use LOB Locators Selected from Remote tables (Doc ID 1234893.1)
    • “The error is expected because the use of DBLinks and LOBs via the SELECT from PL/SQL is not supported.”
  • Ora-22992 workaround (Doc ID 436707.1)
    • Getting ORA-1406 with lobs greater than 32KB – 1
  • ORA-1406: Fetched Column Value was Truncated When Selecting Remote Column into Local BLOB Variable (Doc ID 459557.1)
    • “This means that we are not able to retrieve BLOBs columns greater than 32KB – 1 in size through a database link.”

Summarized we are not able to select a blob nativ over a database link if this blob is greater than 32KB-1.

The interesting thing is, that you are able to use DBMS_LOB operators on local and remote site. My favourite is the DBMS_LOB.SUBSTR function. The name is a little bit missleading because you can retrieve BLOB and CLOB. Here is my approach to select blob via a database link transparently to application:

VERSION 1 (chunk method):

create or replace function GETBLOBVIADBLINK
( dblnk in varchar2
  ,tbl  in varchar2
  ,col  in varchar2
  ,rwid in urowid)
return blob
is
  retval blob;
  tmpraw raw(2000);  
  tmplen number;
  tmpchk number;
  chksize number;
begin
  --preset vars
  chksize:=2000;
  dbms_lob.createtemporary (retval,true);
  execute immediate 'select dbms_lob.getlength@'||dblnk||' ('||col||') from '||tbl||'@'||dblnk||' where rowid=:rwid' into tmplen using rwid;
  
  -- precalc  
  tmpchk:=floor(tmplen/chksize);

  -- applicate frist chunks  
  for i in 0 .. tmpchk-1
  loop  
    execute immediate 'select dbms_lob.substr@'||dblnk||'('||col||','||chksize||','||((i*chksize)+1)||') from '||tbl||'@'||dblnk||' where rowid=:rwid' into tmpraw using rwid;
    dbms_lob.append(retval,tmpraw);
  end loop;
  
  -- applicate last entry
  if (tmplen-(tmpchk*chksize)) > 0 then
    execute immediate 'select dbms_lob.substr@'||dblnk||'('||col||','||(tmplen-(tmpchk*chksize))||','||((tmpchk*chksize)+1)||') from '||tbl||'@'||dblnk||' where rowid=:rwid' into tmpraw using rwid;
    dbms_lob.append(retval,tmpraw);
  end if;
  return retval;
end;
/

The explanation of the function is simple:

  1. Create a temp lob at local site
  2. The limitation of DBMS_LOB.SUBSTR is a RAW(2000) as maximum chunk size
  3. Copy chunk (2000 bytes max.) by chunk over the database link and append it to our local temporary blob. So we generate a local copy
  4. return local blob to upper caller

 

Now create a view with the new defintion:

CREATE OR REPLACE FORCE VIEW TESTVW1 (ID, MYLOB) AS 
SELECT id
       ,getblobviadblink('ARCHIV','MYLOBTABLE','MYLOB',rowid) MYLOB
FROM  MYLOB@archiv;

It’s done. I’m able to select a blob via dblink even it is greater than 32KB, now!

VERSION 2 (temporary table method):

create global temporary table tmplob (tmplob blob) ON COMMIT PRESERVE ROWS;
create or replace function getblobviadblink2
( dblnk in varchar2
  ,tbl  in varchar2
  ,col  in varchar2
  ,rwid in urowid)
return blob
is
  PRAGMA AUTONOMOUS_TRANSACTION;
  retval blob;
begin

  execute immediate 'insert /*+ NOLOGGING */ into tmplob select '||col||' from '||tbl||'@'||dblnk||' where rowid=:rwid' using rwid;
  select tmplob into retval from tmplob;
  delete /*+ NOLOGGING */ from tmplob;
  commit;
  return retval;
end;
/

Both methods are suitable for selecting on  it, but the VERSION2 Method is significant faster on network.

These are just two of many other ways. I would appreciate it if you share your implementation and experience with lob selection.

Oracle Database Applicance Release 2.10.0.0.0


On 1. May 2014 Oracle released the 2.10.0.0.0 patch for Oracle Database Appliance.

Major changes

This release doesn’t contain many new features, but some internal important internal bug fixes, so i’vh renames this section to “Major changes”

  • Oracle PSU 11.2.0.3.10 and 11.2.0.4.2 for GI and Database
  • Out of Place for Grid Infrastructure update supportes the upgrade from 11.2.0.3.x to 11.2.0.4.2
  • ODAchk has been renamed to ORAchk
  • Bug 17896838, has been fixed on 2.10.0.0.0. See here

NOTICE: ODA Note 888888.1 has been updated right now

Description:

  • ODA Bundle Patch
    Contains patches to the following components: OS, Component Firmware and Oracle Appliance Manager Modules, Grid Infrastructure, and RDBMS.
  • End-User GI/RDBMS Clone files
    Contains the latest Grid Infrastructure and RDBMS components, for deployment on an Oracle Database Appliance in the “shipped from factory” state, or an Oracle Database Appliance that has been re-imaged using the OS ISO Image.
  • OS ISO Image
    Used to restore the OS to the “shipped from factory” state. Use only when necessary.
  • Bare Metal
    Refers to the non-virtualized ODA configuration.

IMPORTANT:

  • There are some problems with the Shared Repository. Please read “Known Issues 2.10.0.0.0” section in MOS ID 888888.1 carefully!
  • X4-2 core Key generation not yet working on MOS. This is going to be fixed in Apr-2014. Until then, please open an SR requesting keys for X4-2. (Virtualized Deployment with core binding is working. This doesn’t need a core key)

Patch ID’s:

  • Patch 18284007     Oracle Database Appliance Patch Bundle 2.10
  • Patch 16186163     Oracle Database Appliance 2.10 Virtualized OS ISO Image (Dom0)
  • Patch 16186172     Oracle Database Appliance Template download for Virtualized Platform (ODA_BASE)
  • Patch 12999313     Oracle Database Appliance Bare Metal OS ISO Image
  • Patch 12978712     Oracle Database Appliance End User Bundle
  • Patch 17770873     Oracle Database Appliance 11.2.0.4.2 End-User (RDBMS clone)
  • Patch 14777276     Oracle Database Appliance 11.2.0.3.10 End-User (RDBMS clone)
  • Patch 14349293     Oracle Database Appliance 11.2.0.2.12 End-User (RDBMS clone, not changed since 2.8.0.0.0)

Version Changes:

Component Name X4-2 Hardware X3-2 Hardware V1 Hardware
Controller 11.05.03.00 11.05.03.00 11.05.03.00
Expander 0018 0018 342
SSD_SHARED 9440 9440 E12B
HDD_LOCAL A606 A450 SF04,SA03
HDD_SHARED A600 A600 0B25,A700
ILOM 3.1.2.30.a r83840 3.1.2.10.d r 83372 3.0.16.22.d r83408
BIOS 25010601 17050100 12010311
IPMI 1.8.10.5 1.8.10.5 1.8.10.5
HMP 2.2.6.5 2.2.6.5 2.2.6.5
OAK 2.10.0.0.0 2.10.0.0.0 2.10.0.0.0
ORACLE LINUX 5.9 5.9 5.9
Kernel 2.6.39-400.126.1.el5uek 2.6.39-400.126.1.el5uek 2.6.39-400.126.1.el5uek
OVS 3.2.3 3.2.3 3.2.3
Dom0 Kernel 2.6.39-400.126.1.el5uek 2.6.39-400.126.1.el5uek 2.6.39-400.126.1.el5uek
GI_HOME 11.2.0.4.2(18031740) 11.2.0.4.2(18031740) 11.2.0.4.2(18031740)
DB_HOME 11.2.0.4.2(18031668) 11.2.0.4.2(18031668) 11.2.0.4.2(18031668)
ASR 4.7 4.7  4.7

How to update see here

Oracle Database Applicance Release 2.9.0.0.0


On 20. February 2014 Oracle released the 2.9.0.0.0 patch for Oracle Database Appliance.

New features

  • Import of http based templates on ODA_BASE
  • Send Key support to the VM via xenstore.
  • Oracle Database Patch Set Update (PSU) 11.2.0.4.1 and 11.2.0.3.9.
  • Software enhancements:
    • New template available for Very Very Small databases
    • Support for SNMP Version 3 which can be used, optionally, instead of SNMP Version 2
    • Hardware Monitoring Tool enhanced to collect information about storage enclosures
    • Revised prompts and tools for managing the selection of Fiber or Copper public network
    • New Oracle Appliance Manager option to update the first-boot installation configuration of a virtual machine
  • Oracle Appliance Manager updates include
    • The oakcli show command has a new option, enclosure, to monitor storage enclosures
    • The oakcli modify vm command has a new parameter, -s, to define and send a message to a virtual machine

Major Updates

  • Database Update 11.2.0.2.12, 11.2.0.3.9 or 11.2.0.4.1 (PSU JAN-2014 included)
  • Hardware Monitoring on X4-2

NOTICE: ODA Note 888888.1 has been updated right now
Description:

  • ODA Bundle Patch
    Contains patches to the following components: OS, Component Firmware and Oracle Appliance Manager Modules, Grid Infrastructure, and RDBMS.
  • End-User GI/RDBMS Clone files
    Contains the latest Grid Infrastructure and RDBMS components, for deployment on an Oracle Database Appliance in the “shipped from factory” state, or an Oracle Database Appliance that has been re-imaged using the OS ISO Image.
  • OS ISO Image
    Used to restore the OS to the “shipped from factory” state. Use only when necessary.
  • Bare Metal
    Refers to the non-virtualized ODA configuration.

IMPORTANT:

  • There are some problems with the Shared Repository. Please read “Known Issues 2.9.0.0.0” section in MOS ID 888888.1 carefully!
  • X4-2 core Key generation not yet working on MOS. This is going to be fixed in Apr-2014. Until then, please open an SR requesting keys for X4-2. (Virtualized Deployment with core binding is working. This doesn’t need a core key)

Patch ID’s:

  • Patch 17630388     Oracle Database Appliance Patch Bundle 2.9
  • Patch 16186163     Oracle Database Appliance 2.9 Virtualized OS ISO Image
  • Patch 16186172     Oracle Database Appliance Template download for Virtualized Platform
  • Patch 12999313     Oracle Database Appliance Bare Metal OS ISO Image
  • Patch 12978712     Oracle Database Appliance End User Bundle
  • Patch 17770873     Oracle Database Appliance 11.2.0.4.1 End-User (RDBMS clone)
  • Patch 14777276     Oracle Database Appliance 11.2.0.3.9 End-User (RDBMS clone)
  • Patch 14349293     Oracle Database Appliance 11.2.0.2.12 End-User (RDBMS clone)

Version Changes:

V1 Hardware:

Depending on your hardware, the output should look similar to the output below:    
System Version  Component Name            Installed Version         Supported Version       
--------------  ---------------           ------------------        ----------------       
2.9.0.0.0                                                                                   
                Controller                11.05.03.00               Up-to-date              
                Expander                  0342                      Up-to-date              
                SSD_SHARED                E12B                      Up-to-date
                HDD_LOCAL                 SF04                      Up-to-date              
                HDD_SHARED                A700                      Up-to-date              
                ILOM                      3.0.16.22.d r83408        Up-to-date  
                BIOS                      12010311                  Up-to-date              
                IPMI                      1.8.10.5                  Up-to-date              
                HMP                       2.2.6.5                   Up-to-date              
                OAK                       2.9.0.0.0                 Up-to-date              
                OEL                       5.9                       Up-to-date
                GI_HOME                   11.2.0.4.0(17721778)      Up-to-date
                DB_HOME                   11.2.0.4.1(17478514)      Up-to-date              
                ASR                       4.6                       Up-to-date

X3-2 Hardware:

Depending on the hardware, the output should look similar to the output below:    
System Version  Component Name            Installed Version         Supported Version        
--------------  ---------------           ------------------        ----------------    
2.9.0.0.0                                                                                   
                Controller                11.05.03.00               Up-to-date              
                Expander                  0018                      Up-to-date              
                SSD_SHARED                9440                      Up-to-date
                HDD_LOCAL {               A450                      Up-to-date              
                HDD_SHARED {              A600                      Up-to-date
                ILOM                      3.1.2.10.d r83372         Up-to-date  
                BIOS                      17050100                  Up-to-date              
                IPMI                      1.8.10.5                  Up-to-date              
                HMP                       2.2.6.5                   Up-to-date              
                OAK                       2.9.0.0.0                 Up-to-date
                OVM                       3.2.3                     Up-to-date              
                OEL                       5.9                       Up-to-date
                GI_HOME                   11.2.0.4.0(17721778)      Up-to-date    
                DB_HOME                   11.2.0.4.1(17478514)      Up-to-date              
                ASR                       4.6                       Up-to-date

X4-2 Hardware:

Depending on the hardware, the output should look similar to the output below:    
System Version  Component Name            Installed Version         Supported Version       
--------------  ---------------           ------------------        ----------------    
2.9.0.0.0                                                                                   
                Controller                11.05.03.00               Up-to-date              
                Expander                  0018                      Up-to-date              
                SSD_SHARED                9440                      Up-to-date
                HDD_LOCAL {               A606                      Up-to-date              
                HDD_SHARED {              A600                      Up-to-date
                ILOM                      3.1.2.30.a r83840         Up-to-date  
                BIOS                      25010601                  Up-to-date              
                IPMI                      1.8.10.5                  Up-to-date              
                HMP                       2.2.6.5                   Up-to-date              
                OAK                       2.9.0.0.0                 Up-to-date
                OVM                       3.2.3                     Up-to-date              
                OEL                       5.9                       Up-to-date
                GI_HOME                   11.2.0.4.0(17721778)      Up-to-date    
                DB_HOME                   11.2.0.4.1(17478514)      Up-to-date
                ASR                       4.6                       Up-to-date

How to update see here

Oracle Database Appliance X4-2 is out!


On 04. December 2013 Oracle release the 3rd generation of Oracle Database Appliance over night. There is a good news: When Oracle changed v1 into X3-2 the price increased a “bit” … The price difference between x3-2 and x4-2 is zero which make me happy if I saw it the first time. No more discussions about “Why oracle increases prices in that matter …”. Further Oracle didn’t release a the option to run 12c on the Database Appliance. Maybe at the next ODA release 2.9.0.0.0?

A few days ago oracle also published the datasheet and whitepaper:

Here are the main specs:

  • 2x Server Nodes each with:
    • 2x E5-2697 v2 12-core 2.7 Ghz CPUs
    • Service processor w/ILOM 3.1
    • 256GB (16x 16GB) memory
    • 1x Dual Port 10GbE SFP+ NIC (PCIe cards)
    • 2x Dual port external SAS-2 (PCIe cards)
    • 1x Internal SAS-2 (PCIe cards)
    • 2x 2.5″ 10K 600GB SAS disks
    • 4x (onboard) 10GBase-T ports
  • One DE2-24P storage shelf (optional 2nd shelf can be ordered):
    • 4x 2.5″ 200GB SSDs
    • 20x 2.5″ 10K 900GB SAS HDDs

As you can see there are just a few technical innovations

  • Newer Server Case (4x-2) and mainboard
  • More cores. There are 2x 12 Cores each node
  • 10Gbit SFP+ Connect instead of 10GBit T-Base for Interconnect
  • Optional 10GbE SFP+ or 10GBase-T external networking connectivity

For further information visit the following links:

Oracle Enterprise Linux 6.5 is out!


There was two weeks since RedHat released it’s Enterprise Linux 6.4 (RHEL 6.5 Release Notes). Oracle have followed with Oracle Enterprise Linux 6.5 (Announcement). Some interessting new features comes with this release (OEL6.5/UEK3 Release Notes):

Linux Containers

This Features is fully supported by OEL now.

For the Oracle Database Users:

“Oracle Database is not yet supported for use with Linux Containers. The following information is intended for those who want to experiment with such a configuration.”

The following /proc parameter files may only be set on the host and not for individual containers

  • /proc/sys/fs/aio-max-nr
  • /proc/sys/net/core/rmem_default
  • /proc/sys/net/core/rmem_max
  • /proc/sys/net/core/wmem_default
  • /proc/sys/net/core/wmem_max
  • /proc/sys/net/ipv4/ip_local_port_range

Setting the parameters in the host to the Oracle recommended values sets them for all containers and allows the Oracle database to run in a container. For more information, see Configuring Kernel Parameters. (Bug ID 17217854)

Transparent Huge Pages

This release removes the Transparent Huge Pages (THP) feature. Following extensive benchmarking and testing, Oracle found that THP caused a performance degradation of between 5 and 10% for some workloads. This performance degradation was a result of a slower memory allocator code path being used even when the applications were not using THP. When the fact that huge pages are not swappable was taken into account, the positive effect that THP should provide was outweighed by its negative effects.

After installing this UEK release, you cannot enable THP (for example, by specifying kernel boot parameters). The THP settings under /sys/kernel/mm/transparent_hugepage have also been removed. A future update might contain an updated THP implementation which resolves the performance issue.

Note:This change does not affect support for applications that use explicit huge pages (for example, Oracle Database). (Bug ID 16823432)

One-gigabyte (1 GB) huge pages are not currently supported for the following configurations:

  • HVM guests
  • PV guests
  • Oracle Database

Two-megabyte (2 MB) huge pages have been tested and work with these configurations. (Bug ID 17299364, 17299871, 17271305)

Other features

There are some more interessting features. Please refer to UEK3 Overview

Oracle Database Applicance Release 2.8.0.0.0


On 19. Novermber 2013 Oracle released the 2.8.0.0.0 patch for Oracle Database Appliance.

New Features

  • Shared Repository support for Oracle Database Appliance Virtualized Platform
  • Virtual Local Area Network support for Oracle Database Appliance Virtualized Platform
  • Hardware Monitoring Tool reports data from sensors in subsystems
  • Initial deployment does not require configuration of initial Oracle Database or Oracle Enterprise Database Control

Major Updates

  • Out-of-place update Grid Infrastructure from 11.2.0.3.x to 11.2.0.4.0.
  • Virtualized platform shared repositories support
  •  Hardware Monitoring on X3-2
  • Virtualized platform VLAN support

NOTICE: ODA Note 888888.1 has been updated right now
Description:

  • ODA Bundle Patch
    Contains patches to the following components: OS, Component Firmware and Oracle Appliance Manager Modules, Grid Infrastructure, and RDBMS.
  • End-User GI/RDBMS Clone files
    Contains the latest Grid Infrastructure and RDBMS components, for deployment on an Oracle Database Appliance in the “shipped from factory” state, or an Oracle Database Appliance that has been re-imaged using the OS ISO Image.
  • OS ISO Image
    Used to restore the OS to the “shipped from factory” state. Use only when necessary.
  • Bare Metal
    Refers to the non-virtualized ODA configuration.

IMPORTANT: There are many problems with the new Features VLANs, Shared Repository and ACFS. Please read “Known Issues 2.8.0.0.0” section in MOS ID 888888.1 carefully!

Patch ID’s:

  • ORACLE DATABASE APPLIANCE PATCH BUNDLE 2.8.0.0.0 [ Patch 17630367]
  • ORACLE DATABASE APPLIANCE 2.8.0.0.0 VIRTUALIZED OS ISO IMAGE [Patch 16186163]
  • ORACLE DATABASE APPLIANCE 2.8.0.0.0 END-USER BUNDLE (GI/RDBMS Clone) [Patch 12978712]
  • ORACLE DATABASE APPLIANCE 2.8.0.0.0 BARE METAL OS ISO IMAGE [Patch 12999313]
  • ORACLE DATABASE APPLIANCE 2.8.0.0.0 (GI/RDBMS Clone) TEMPLATE DOWNLOAD FOR VIRTUALIZED PLATFORM [Patch 16186172]
  • ORACLE DATABASE APPLIANCE 2.8.0.0.0 END-USER BUNDLE (GI/RDBMS Clone, 11.2.0.4.0) [Patch 17770873]
  • ORACLE DATABASE APPLIANCE 2.8.0.0.0 END-USER BUNDLE (GI/RDBMS Clone, 11.2.0.3.8) [Patch 14777276]
  • ORACLE DATABASE APPLIANCE 2.8.0.0.0 END-USER BUNDLE (GI/RDBMS Clone, 11.2.0.2.12) [Patch 14349293]

Version Changes:

Component Name X3-2 Hardware V1 Hardware
Controller 11.05.03.00 11.05.03.00
Expander 0018 342
SSD_SHARED 9440 E12B
HDD_LOCAL A3D0 SF04,SA03
HDD_SHARED A31A 0B25,A700
ILOM 3.1.2.10.d r 83372 3.0.16.22.d r83408
BIOS 17050100 12010311
IPMI 1.8.10.5 1.8.10.5
HMP 2.2.6.4 2.2.6.4
OAK 2.8.0.0.0 2.8.0.0.0
ORACLE LINUX 5.9 5.9
Kernel 2.6.39-400.126.1.el5uek 2.6.39-400.126.1.el5uek
OVS 3.2.3 3.2.3
Dom0 Kernel 2.6.39-400.126.1.el5uek 2.6.39-400.126.1.el5uek
TFA 2.5.1.5 2.5.1.5
GI_HOME 11.2.0.4.0 11.2.0.4.0
DB_HOME 11.2.0.3.8(16902043,17076717) 11.2.0.3.8(16902043,17076717)
ASR 4.5 4.5

How to update see here