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

Install Oracle Grid Infrastructure 12.1.0.1++ on Fedora 17 – 20


In my previous post i’vh described how to set up the Oracle 12c Database Software to get working on Fedora 17 up to 20. Today i’m going to to show how to setup a RAC Cluster (12.1.0.1) on Fedora 17++ (I’vh testet on Fedora 20). My enviroment: 2x VirtualBox, Fedora 20 x86_64, with 4 CPUs, 4092 MB RAM, 50GB local, 10GB shared disks, 2x NIC Paravirtualized

1. Prerequirements

First start follow the Oracle Database 12c Quick Installation Guide to setup the base for the Grid Infrastructure (see here)
During the setup of the inital stuff you should mention something like that:

Loaded plugins: langpacks, refresh-packagekit
No package libcap1 available.
Error: Nothing to do

This problem doesn’t matter, because there is a newer working version:

[root@fed01 ~]# yum list libcap
Loaded plugins: langpacks, refresh-packagekit
Installed Packages
libcap.x86_64                                             2.22-7.fc20                                             @anaconda
Available Packages
libcap.i686                                               2.22-7.fc20                                             fedora

To avoid an exception like this:

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /opt/oracle/12.1.0.1/grid/crs/install/crsconfig_params
/opt/oracle/12.1.0.1/grid/bin/crsctl query crs releaseversion ... failed rc=127 with message:
 /opt/oracle/12.1.0.1/grid/bin/crsctl.bin: error while loading shared libraries: libcap.so.1: cannot open shared object file: No such file or directory 

Failed to create keys in the OLR, rc = 127, Message:
  /opt/oracle/12.1.0.1/grid/bin/clscfg.bin: error while loading shared libraries: libcap.so.1: cannot open shared object file: No such file or directory 

2014/02/28 00:13:19 CLSRSC-188: Failed to create keys in Oracle Local Registry

Died at /opt/oracle/12.1.0.1/grid/crs/install/crsutils.pm line 6479.
The command '/opt/oracle/12.1.0.1/grid/perl/bin/perl -I/opt/oracle/12.1.0.1/grid/perl/lib -I/opt/oracle/12.1.0.1/grid/crs/install /opt/oracle/12.1.0.1/grid/crs/install/rootcrs.pl ' execution failed

… you have to relink the existing library …

[root@fed01 install]# cd /usr/lib64/
[root@fed01 lib64]# ln -s /usr/lib64/libcap.so.2 /usr/lib64/libcap.so.1

Futher i’vh disabled the firewall to avoid network problems during my tests

systemctl disable firewalld.service

2. Unpack and install the software

There will be nothing special. During linking process you will get the following error:

fedora20_12c_rac

To workaround this errors please follow my previous post. (see here)

You should be able to complete the installation up to the root.sh successfully.

3. root.sh

If you are going to execute the root.sh right now, you will get the following problem:

[root@fed01 lib64]# /opt/oracle/12.1.0.1/grid/root.sh 
Performing root user operation for Oracle 12c 

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /opt/oracle/12.1.0.1/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:  
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /opt/oracle/12.1.0.1/grid/crs/install/crsconfig_params
OLR initialization - successful
  root wallet
  root wallet cert
  root cert export
  peer wallet
  profile reader wallet
  pa wallet
  peer wallet keys
  pa wallet keys
  peer cert request
  pa cert request
  peer cert
  pa cert
  peer root cert TP
  profile reader root cert TP
  pa root cert TP
  peer pa cert TP
  pa peer cert TP
  profile reader pa cert TP
  profile reader peer cert TP
  peer user cert
  pa user cert
2014/02/28 00:16:34 CLSRSC-330: Adding Clusterware entries to file '/etc/inittab'

2014/02/28 00:18:40 CLSRSC-214: Failed to start 'ohasd'

Failed to start the Clusterware. Last 20 lines of the alert log follow: 
2014-02-28 00:13:19.635: 
[client(8796)]CRS-2101:The OLR was formatted using version 4.
2014-02-28 00:15:11.647: 
[client(9189)]CRS-2101:The OLR was formatted using version 4.

CRS-4133: Oracle High Availability Services has been stopped.
2014/02/28 00:31:53 CLSRSC-117: Failed to start Oracle Clusterware stack

Died at /opt/oracle/12.1.0.1/grid/crs/install/crsutils.pm line 8813.
The command '/opt/oracle/12.1.0.1/grid/perl/bin/perl -I/opt/oracle/12.1.0.1/grid/perl/lib -I/opt/oracle/12.1.0.1/grid/crs/install /opt/oracle/12.1.0.1/grid/crs/install/rootcrs.pl ' execution failed

Oracle’s Grid Infrastructure starts with the intitialization script:

[root@fed01 lib64]# cat /etc/inittab
# inittab is no longer used when using systemd.
#
# ADDING CONFIGURATION HERE WILL HAVE NO EFFECT ON YOUR SYSTEM.
#
# Ctrl-Alt-Delete is handled by /etc/systemd/system/ctrl-alt-del.target
#
# systemd uses 'targets' instead of runlevels. By default, there are two main targets:
#
# multi-user.target: analogous to runlevel 3
# graphical.target: analogous to runlevel 5
#
# To set a default target, run:
#
# ln -sf /lib/systemd/system/.target /etc/systemd/system/default.target
#
h1:35:respawn:/etc/init.d/init.ohasd run >/dev/null 2>&1

Fedora doesn’t use the old style and switched to systemd, therefore you have to setup the daemon with the new enviroment. Here my implementation

[root@fed01 lib64]# cat /etc/systemd/system/ohasd.service 
[Unit]
Description=ohasd daemon

[Service]
ExecStart=/etc/init.d/init.ohasd run >/dev/null 2>&1

Reload the dameon unit to inform systemd about my changes

systemctl daemon-reload

Now enable and start the daemon

systemctl enable ohasd.service
systemctl start ohasd.service

Check how it’s work?

[root@fed01 ~]# ps -ef | grep oha                                                                                                                     
root     13069     1  0 00:56 ?        00:00:00 /bin/sh /etc/init.d/init.ohasd run >/dev/null 2>&1

Now re execute the root.sh script and it should finalize successfully:

...
CRS-6016: Resource auto-start has completed for server fed01
CRS-6024: Completed start of Oracle Cluster Ready Services-managed resources
CRS-4123: Oracle High Availability Services has been started.

Preparing packages...
cvuqdisk-1.0.9-1.x86_64
2014/02/28 01:13:27 CLSRSC-325: Configure Oracle Grid Infrastructure for a Cluster ... succeeded

Here the cluster (first node) output:

[root@fed01 system]# crsctl stat res -t 
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr
               ONLINE  ONLINE       fed01                    STABLE
ora.DATA.dg
               ONLINE  ONLINE       fed01                    STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       fed01                    STABLE
ora.net1.network
               ONLINE  ONLINE       fed01                    STABLE
ora.ons
               ONLINE  ONLINE       fed01                    STABLE
ora.proxy_advm
               ONLINE  OFFLINE      fed01                    STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       fed01                    STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       fed01                    STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       fed01                    STABLE
ora.MGMTLSNR
      1        OFFLINE OFFLINE                               STABLE
ora.asm
      1        ONLINE  ONLINE       fed01                    STABLE
      2        OFFLINE OFFLINE                               STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.cvu
      1        ONLINE  ONLINE       fed01                    STABLE
ora.fed01.vip
      1        ONLINE  ONLINE       fed01                    STABLE
ora.oc4j
      1        ONLINE  ONLINE       fed01                    STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       fed01                    STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       fed01                    STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       fed01                    STABLE
--------------------------------------------------------------------------------

4. Redo on all other nodes and complete setup

To complete your cluster setup redo all step above.

For completion the output from the second node (root.sh):

[root@fed02 lib64]# /opt/oracle/12.1.0.1/grid/root.sh 
Performing root user operation for Oracle 12c 

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /opt/oracle/12.1.0.1/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]: 
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /opt/oracle/12.1.0.1/grid/crs/install/crsconfig_params

CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'fed02'                                                                                                                                                                                                                                
CRS-2673: Attempting to stop 'ora.crsd' on 'fed02'                                                                                                                                                                                                                                                                           
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'fed02'                                                                                                                                                                                                                                           
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'fed02'                                                                                                                                                                                                                                                                        
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on 'fed02'                                                                                                                                                                                                                                                            
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN3.lsnr' on 'fed02'                                                                                                                                                                                                                                                            
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN2.lsnr' on 'fed02'                                                                                                                                                                                                                                                            
CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on 'fed02' succeeded                                                                                                                                                                                                                                                             
CRS-2673: Attempting to stop 'ora.scan1.vip' on 'fed02'                                                                                                                                                                                                                                                                      
CRS-2677: Stop of 'ora.LISTENER_SCAN3.lsnr' on 'fed02' succeeded                                                                                                                                                                                                                                                             
CRS-2673: Attempting to stop 'ora.scan3.vip' on 'fed02'                                                                                                                                                                                                                                                                      
CRS-2677: Stop of 'ora.LISTENER_SCAN2.lsnr' on 'fed02' succeeded                                                                                                                                                                                                                                                             
CRS-2673: Attempting to stop 'ora.scan2.vip' on 'fed02'                                                                                                                                                                                                                                                                      
CRS-2677: Stop of 'ora.scan1.vip' on 'fed02' succeeded
CRS-2672: Attempting to start 'ora.scan1.vip' on 'fed01'
CRS-2677: Stop of 'ora.scan3.vip' on 'fed02' succeeded
CRS-2672: Attempting to start 'ora.scan3.vip' on 'fed01'
CRS-2677: Stop of 'ora.scan2.vip' on 'fed02' succeeded
CRS-2672: Attempting to start 'ora.scan2.vip' on 'fed01'
CRS-2676: Start of 'ora.scan1.vip' on 'fed01' succeeded
CRS-2672: Attempting to start 'ora.LISTENER_SCAN1.lsnr' on 'fed01'
CRS-2676: Start of 'ora.scan3.vip' on 'fed01' succeeded
CRS-2672: Attempting to start 'ora.LISTENER_SCAN3.lsnr' on 'fed01'
CRS-2676: Start of 'ora.scan2.vip' on 'fed01' succeeded
CRS-2672: Attempting to start 'ora.LISTENER_SCAN2.lsnr' on 'fed01'
CRS-2676: Start of 'ora.LISTENER_SCAN1.lsnr' on 'fed01' succeeded
CRS-2676: Start of 'ora.LISTENER_SCAN3.lsnr' on 'fed01' succeeded
CRS-2676: Start of 'ora.LISTENER_SCAN2.lsnr' on 'fed01' succeeded
CRS-2677: Stop of 'ora.DATA.dg' on 'fed02' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'fed02'
CRS-2677: Stop of 'ora.asm' on 'fed02' succeeded
CRS-2673: Attempting to stop 'ora.ASMNET1LSNR_ASM.lsnr' on 'fed02'
CRS-2677: Stop of 'ora.ASMNET1LSNR_ASM.lsnr' on 'fed02' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'fed02'
CRS-2677: Stop of 'ora.ons' on 'fed02' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'fed02'
CRS-2677: Stop of 'ora.net1.network' on 'fed02' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'fed02' has completed
CRS-2677: Stop of 'ora.crsd' on 'fed02' succeeded
CRS-2673: Attempting to stop 'ora.evmd' on 'fed02'
CRS-2673: Attempting to stop 'ora.storage' on 'fed02'
CRS-2673: Attempting to stop 'ora.crf' on 'fed02'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'fed02'
CRS-2673: Attempting to stop 'ora.gpnpd' on 'fed02'
CRS-2677: Stop of 'ora.storage' on 'fed02' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'fed02'
CRS-2677: Stop of 'ora.crf' on 'fed02' succeeded
CRS-2677: Stop of 'ora.mdnsd' on 'fed02' succeeded
CRS-2677: Stop of 'ora.gpnpd' on 'fed02' succeeded
CRS-2677: Stop of 'ora.evmd' on 'fed02' succeeded
CRS-2677: Stop of 'ora.asm' on 'fed02' succeeded
CRS-2673: Attempting to stop 'ora.ctssd' on 'fed02'
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'fed02'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'fed02' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'fed02' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'fed02'
CRS-2677: Stop of 'ora.cssd' on 'fed02' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'fed02'
CRS-2677: Stop of 'ora.gipcd' on 'fed02' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'fed02' has completed
CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Starting Oracle High Availability Services-managed resources
CRS-2672: Attempting to start 'ora.mdnsd' on 'fed02'
CRS-2672: Attempting to start 'ora.evmd' on 'fed02'
CRS-2676: Start of 'ora.evmd' on 'fed02' succeeded
CRS-2676: Start of 'ora.mdnsd' on 'fed02' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'fed02'
CRS-2676: Start of 'ora.gpnpd' on 'fed02' succeeded
CRS-2672: Attempting to start 'ora.gipcd' on 'fed02'
CRS-2676: Start of 'ora.gipcd' on 'fed02' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'fed02'
CRS-2676: Start of 'ora.cssdmonitor' on 'fed02' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'fed02'
CRS-2672: Attempting to start 'ora.diskmon' on 'fed02'
CRS-2676: Start of 'ora.diskmon' on 'fed02' succeeded
CRS-2789: Cannot stop resource 'ora.diskmon' as it is not running on server 'fed02'
CRS-2676: Start of 'ora.cssd' on 'fed02' succeeded
CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'fed02'
CRS-2672: Attempting to start 'ora.ctssd' on 'fed02'
CRS-2676: Start of 'ora.ctssd' on 'fed02' succeeded
CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'fed02' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'fed02'
CRS-2676: Start of 'ora.asm' on 'fed02' succeeded
CRS-2672: Attempting to start 'ora.storage' on 'fed02'
CRS-2676: Start of 'ora.storage' on 'fed02' succeeded
CRS-2672: Attempting to start 'ora.crf' on 'fed02'
CRS-2676: Start of 'ora.crf' on 'fed02' succeeded
CRS-2672: Attempting to start 'ora.crsd' on 'fed02'
CRS-2676: Start of 'ora.crsd' on 'fed02' succeeded
CRS-6017: Processing resource auto-start for servers: fed02
CRS-2672: Attempting to start 'ora.ASMNET1LSNR_ASM.lsnr' on 'fed02'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on 'fed01'
CRS-2672: Attempting to start 'ora.ons' on 'fed02'
CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on 'fed01' succeeded
CRS-2673: Attempting to stop 'ora.scan1.vip' on 'fed01'
CRS-2677: Stop of 'ora.scan1.vip' on 'fed01' succeeded
CRS-2672: Attempting to start 'ora.scan1.vip' on 'fed02'
CRS-2676: Start of 'ora.scan1.vip' on 'fed02' succeeded
CRS-2672: Attempting to start 'ora.LISTENER_SCAN1.lsnr' on 'fed02'
CRS-2676: Start of 'ora.ASMNET1LSNR_ASM.lsnr' on 'fed02' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'fed02'
CRS-2676: Start of 'ora.ons' on 'fed02' succeeded
CRS-2676: Start of 'ora.LISTENER_SCAN1.lsnr' on 'fed02' succeeded
CRS-2676: Start of 'ora.asm' on 'fed02' succeeded
CRS-2672: Attempting to start 'ora.proxy_advm' on 'fed01'
CRS-2679: Attempting to clean 'ora.proxy_advm' on 'fed02'
CRS-5017: The resource action "ora.proxy_advm start" encountered the following error: 
ORA-03113: end-of-file on communication channel
Process ID: 0
Session ID: 0 Serial number: 0
. For details refer to "(:CLSN00107:)" in "/opt/oracle/12.1.0.1/grid/log/fed01/agent/crsd/oraagent_oracle/oraagent_oracle.log".
CRS-2681: Clean of 'ora.proxy_advm' on 'fed02' succeeded
CRS-2672: Attempting to start 'ora.proxy_advm' on 'fed02'
CRS-2674: Start of 'ora.proxy_advm' on 'fed01' failed
CRS-2679: Attempting to clean 'ora.proxy_advm' on 'fed01'
CRS-5017: The resource action "ora.proxy_advm start" encountered the following error: 
ORA-03113: end-of-file on communication channel
Process ID: 0
Session ID: 0 Serial number: 0
. For details refer to "(:CLSN00107:)" in "/opt/oracle/12.1.0.1/grid/log/fed02/agent/crsd/oraagent_oracle/oraagent_oracle.log".
CRS-2674: Start of 'ora.proxy_advm' on 'fed02' failed
CRS-2679: Attempting to clean 'ora.proxy_advm' on 'fed02'
CRS-2681: Clean of 'ora.proxy_advm' on 'fed01' succeeded
CRS-2681: Clean of 'ora.proxy_advm' on 'fed02' succeeded
CRS-2672: Attempting to start 'ora.proxy_advm' on 'fed02'
CRS-2672: Attempting to start 'ora.DATA.dg' on 'fed02'
CRS-2672: Attempting to start 'ora.proxy_advm' on 'fed01'
CRS-2676: Start of 'ora.DATA.dg' on 'fed02' succeeded
CRS-5017: The resource action "ora.proxy_advm start" encountered the following error: 
ORA-03113: end-of-file on communication channel
Process ID: 0
Session ID: 0 Serial number: 0
. For details refer to "(:CLSN00107:)" in "/opt/oracle/12.1.0.1/grid/log/fed02/agent/crsd/oraagent_oracle/oraagent_oracle.log".
CRS-5017: The resource action "ora.proxy_advm start" encountered the following error: 
ORA-03113: end-of-file on communication channel
Process ID: 0
Session ID: 0 Serial number: 0
. For details refer to "(:CLSN00107:)" in "/opt/oracle/12.1.0.1/grid/log/fed01/agent/crsd/oraagent_oracle/oraagent_oracle.log".
CRS-2674: Start of 'ora.proxy_advm' on 'fed02' failed
CRS-2679: Attempting to clean 'ora.proxy_advm' on 'fed02'
CRS-2674: Start of 'ora.proxy_advm' on 'fed01' failed
CRS-2679: Attempting to clean 'ora.proxy_advm' on 'fed01'
CRS-2681: Clean of 'ora.proxy_advm' on 'fed02' succeeded
CRS-2672: Attempting to start 'ora.proxy_advm' on 'fed02'
===== Summary of resource auto-start failures follows =====
CRS-2807: Resource 'ora.proxy_advm' failed to start automatically.
CRS-6016: Resource auto-start has completed for server fed02
CRS-6024: Completed start of Oracle Cluster Ready Services-managed resources
CRS-4123: Oracle High Availability Services has been started.
2014/02/28 01:53:02 CLSRSC-343: Successfully started Oracle clusterware stack

Preparing packages...
cvuqdisk-1.0.9-1.x86_64
2014/02/28 01:53:18 CLSRSC-325: Configure Oracle Grid Infrastructure for a Cluster ... succeeded

The final state:

--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr
               ONLINE  ONLINE       fed01                    STABLE
               ONLINE  ONLINE       fed02                    STABLE
ora.DATA.dg
               ONLINE  ONLINE       fed01                    STABLE
               ONLINE  ONLINE       fed02                    STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       fed01                    STABLE
               ONLINE  ONLINE       fed02                    STABLE
ora.net1.network
               ONLINE  ONLINE       fed01                    STABLE
               ONLINE  ONLINE       fed02                    STABLE
ora.ons
               ONLINE  ONLINE       fed01                    STABLE
               ONLINE  ONLINE       fed02                    STABLE
ora.proxy_advm
               ONLINE  OFFLINE      fed01                    STABLE
               ONLINE  OFFLINE      fed02                    STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       fed02                    STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       fed01                    STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       fed01                    STABLE
ora.MGMTLSNR
      1        ONLINE  ONLINE                                STABLE
ora.asm
      1        ONLINE  ONLINE       fed01                    STABLE
      2        ONLINE  ONLINE       fed02                    STABLE
ora.cvu
      1        ONLINE  ONLINE       fed01                    STABLE
ora.fed01.vip
      1        ONLINE  ONLINE       fed01                    STABLE
ora.fed02.vip
      1        ONLINE  ONLINE       fed02                    STABLE
ora.oc4j
      1        ONLINE  ONLINE       fed01                    STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       fed02                    STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       fed01                    STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       fed01                    STABLE
--------------------------------------------------------------------------------

In my next post i’m trying to enable asmlib and acfs support. I hope i’ll get it to work.

DONE & Have Fun!

Oracle Database Appliance X4-2: Copper vs. Fiber


With the Database Applicance Release X4-2 hardware you can choose between 10GbE SFP+ and 10GBase-T (Dual Port) for interconnect or public network.

At initial deployment you are able to choose the configuration you want to use:

copper_vs_fiber

Delivery

If a new x4-2 has been ordered, it will be delivered with 2x SFP+ and 2x Ethernet Cable for both interconnect options

ethtool for fiber card

Settings for eth0
        Supported ports: [ FIBRE ]
        Supported link modes:   10000baseT/Full 
        Supports auto-negotiation: No
        Advertised link modes:  10000baseT/Full 
        Advertised pause frame use: No
        Advertised auto-negotiation: No
        Speed: 10000Mb/s
        Duplex: Full
        Port: Direct Attach Copper
        PHYAD: 0
        Transceiver: external
        Auto-negotiation: off
        Supports Wake-on: d
        Wake-on: d
        Current message level: 0x00000007 (7)
        Link detected: yes

Settings for eth1
        Supported ports: [ FIBRE ]
        Supported link modes:   10000baseT/Full 
        Supports auto-negotiation: No
        Advertised link modes:  10000baseT/Full 
        Advertised pause frame use: No
        Advertised auto-negotiation: No
        Speed: 10000Mb/s
        Duplex: Full
        Port: Direct Attach Copper
        PHYAD: 0
        Transceiver: external
        Auto-negotiation: off
        Supports Wake-on: d
        Wake-on: d
        Current message level: 0x00000007 (7)
        Link detected: yes

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