Oracle 12c Flex Cluster Installation in VirtualBox

Installing a flex cluster requires additional configuration, because GNS (Grid Naming Service) is mandatory for this new type of Oracle cluster that was introduced in Oracle Grid Infrastructure 12c. For this specific setup I have used VirtualBox with 5 virtual machines: 4-node cluster and a DHCP/DNS server VM. I don’t want to go into the details of the regular parts of the installation, this post is mainly about the GNS configuration (DHCP and DNS) and the new parts in the installation.

Read more of this post

Oracle Database 12c New Features – Data Redaction

Introduction

Starting with Oracle Database version 12.1.0.1 (and 11.2.0.4!) the Advanced Security option contains a new feature: Data Redaction. Data Redaction provides in-built support for hiding sensitive data on-the-fly in query results on certain conditions.

Read more of this post

Enterprise Manager Cloud Control 12.1.0.4 upgrade and downgrade

This post is about upgrading Enterprise Manager Cloud Control from version 12.1.0.3 to 12.1.0.4 with a fallback plan. As of now, there is no supported method for downgrading Enterprise Manager Cloud Control, so actually this is carried out by making the required backups, and reverting to the older state from them.

Preparations

One of the requirements for upgrading is to copy the emkey to the repository database:

$ export OMS_HOME=/oracle/base/product/mw12103/oms
$ $OMS_HOME/bin/emctl config emkey -copy_to_repos_from_file -repos_host emdb.sequoia.vbox -repos_port 1521 -repos_sid emrep -repos_user sysman -emkey_file $OMS_HOME/sysman/config/emkey.ora

Next export the OMS configuration, which should be part of the regular backups also. The file created by this operation can be used to recreate the OMS from a software-only installation in case the binaries are lost.

$ $OMS_HOME/bin/emctl exportconfig oms

Read more of this post

INDEX SKIP SCAN not considered

This post was inspired by the below question:

http://dba.stackexchange.com/questions/64880/why-does-the-cbo-not-chose-a-lower-cost-skip-scan-without-stats

Short version: INDEX SKIP SCAN is not considered by the optimizer, when optimizer statistics are missing.
Read more of this post

DBMS_METADATA.GET_DDL database link password missing

Another annoying “feature” that was introduced in 11.2.0.4.

11.2.0.3:

SQL> create database link dblink connect to user1 identified by password1 using 'ORCL';

Database link created.

SQL> select dbms_metadata.get_ddl('DB_LINK', 'DBLINK', user) as ddl from dual;

DDL
----------------------------------------------------------------------------------------------------

  CREATE DATABASE LINK "DBLINK"
   CONNECT TO "USER1" IDENTIFIED BY VALUES '063E28DC19A9C9D6724B8F76A9980676231666CA76CF563B1B91ABCC
DE3CA1352BF3BA1D50EF14843F59ED6289B6709A0360C3B4DBA099A09A88C4F40E317362DBF0171A43E87AF4F5E2E03BED6F
F7CB1DB100DDC531257D0CF9B11EB2E1773E53A82812F39BDEA33AA3BC837C367FFA5A3CBBD4B48B9B33CAC55F3306CA4A2B
'
   USING 'ORCL'

Now, do the same on 11.2.0.4:

SQL> create database link dblink connect to user1 identified by password1 using 'ORCL';

Database link created.

SQL> select dbms_metadata.get_ddl('DB_LINK', 'DBLINK', user) as ddl from dual;

DDL
----------------------------------------------------------------------------------------------------

  CREATE DATABASE LINK "DBLINK"
   CONNECT TO "USER1" IDENTIFIED BY VALUES ':1'
   USING 'ORCL'

IDENTIFIED BY VALUES ‘:1′, not what I wanted. Let’s check what happens at a Data Pump export/import:

$ expdp test/test directory=data_pump_dir include=DB_LINK
$ impdp test/test directory=data_pump_dir dumpfile=expdat.dmp sqlfile=dblink.sql
$ cat dblink.sql
-- CONNECT TEST
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/DB_LINK
CREATE DATABASE LINK "DBLINK"
   CONNECT TO "USER1" IDENTIFIED BY VALUES ':1'
   USING 'ORCL';

Even in the SQL file. Now try to really import this time:

SQL> drop database link dblink;

$ impdp test/test directory=data_pump_dir dumpfile=expdat.dmp

Import: Release 11.2.0.4.0 - Production on Sat Apr 12 15:18:29 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_FULL_01":  test/******** directory=data_pump_dir dumpfile=expdat.dmp
Processing object type SCHEMA_EXPORT/DB_LINK
Job "TEST"."SYS_IMPORT_FULL_01" successfully completed at Sat Apr 12 15:18:30 2014 elapsed 0 00:00:00

No errors.

$ sqlplus test/test
...
SQL> select dbms_metadata.get_ddl('DB_LINK', 'DBLINK', user) as ddl from dual;

DDL
----------------------------------------------------------------------------------------------------

  CREATE DATABASE LINK "DBLINK"
   CONNECT TO "USER1" IDENTIFIED BY VALUES ':1'
   USING 'ORCL'

As per Oracle Support, this is not a bug, but a feature.

Bug 18461318 : DBMS_METADATA.GET_DDL FOR DBLINK ALWAYS RETURNS IDENTIFIED BY VALUES ':1'
Status: 44 - Not Feasible to fix, to Filer

Conclusion: use Data Pump as above.

Cloud Control 12c – java.lang.NoClassDefFoundError from all emctl commands

Here is the situation, Cloud Control 12.1.0.3 crashed because of below errors:

java.lang.OutOfMemoryError : unable to create new native Thread

This is a configuration issue, Cloud Control (Java) was configured to use too much memory, and it ate up all the memory, but this is not the topic of this post.

After this crash, the OMS could not be started, because all emctl commands returned the below error:

[oracle@cc ~]$ /oracle/base/product/middleware/oms/bin/emctl start oms
Oracle Enterprise Manager Cloud Control 12c Release 3
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/log4j/Logger
        at oracle.sysman.emctl.wls.OMSController.(OMSController.java:101)
Caused by: java.lang.ClassNotFoundException: org.apache.log4j.Logger
        at java.net.URLClassLoader$1.run(URLClassLoader.java:202)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.net.URLClassLoader.findClass(URLClassLoader.java:190)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:306)
        at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:247)
        ... 1 more
Could not find the main class: oracle.sysman.emctl.wls.OMSController.  Program will exit.

[oracle@cc ~]$ /oracle/base/product/middleware/oms/bin/emctl status oms
Oracle Enterprise Manager Cloud Control 12c Release 3
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/log4j/Logger
        at oracle.sysman.emctl.wls.OMSController.(OMSController.java:101)
Caused by: java.lang.ClassNotFoundException: org.apache.log4j.Logger
        at java.net.URLClassLoader$1.run(URLClassLoader.java:202)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.net.URLClassLoader.findClass(URLClassLoader.java:190)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:306)
        at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:247)
        ... 1 more
Could not find the main class: oracle.sysman.emctl.wls.OMSController.  Program will exit.

There is a note about a similar issue with the exact same error message on MOS:

Executing Any EMCTL Command on Windows 2008 / Vista Returns “Permission denied” Error (Doc ID 1164293.1)

The problem is, that this is about Windows, and permission issues, which is definitely not the case here. Another similar error:

EM 12c: All emctl commands from OMS Home Returns “java.lang.NoClassDefFoundError: HTTPClient/http/Handler HTTPClient.http.Handler Exception Error” (Doc ID 1482238.1)

Long story short: the needed .jar file is missing or corrupt – according to the above note.
Checking what .jar files are accessed:

$ strace -f /oracle/base/product/middleware/oms/bin/emctl start oms 2> emctl.str
$ grep "\.jar" emctl.str | awk '{ print $3}' | sort | uniq

I have experimented with this on a working system, and found that the .jar file needed for the above is

$MW_HOME/modules/com.bea.core.apache.log4j_1.2.13.jar

.jar files are simple zip files, checking the file:

$ unzip -t /oracle/base/product/middleware/modules/com.bea.core.apache.log4j_1.2.13.jar
No errors detected in compressed data of /oracle/base/product/middleware/modules/com.bea.core.apache.log4j_1.2.13.jar

So let’s look into it:

$ unzip /oracle/base/product/middleware/modules/com.bea.core.apache.log4j_1.2.13.jar -d /tmp/
$ ll /tmp/org/apache/log4j/Logger.class
-rw-r--r--. 1 oracle oinstall 2567 Dec  4  2005 /tmp/org/apache/log4j/Logger.class

The .jar file is definitely intact, and the Logger class also exists, the problem must be somewhere else. Here is an excerpt of the output of the earlier strace command:

open("/oracle/base/product/middleware/wlserver_10.3/server/lib/wlinformix.jar",
open("/oracle/base/product/middleware/wlserver_10.3/server/lib/wlsqlserver.jar",
open("/oracle/base/product/middleware/wlserver_10.3/server/lib/wlsybase.jar",
open("/oracle/base/product/middleware/wlserver_10.3/server/lib/wlw-langx.jar",
open("/oracle/base/product/middleware/wlserver_10.3/server/lib/xmlx.jar",
read(4,
stat("/modules/com.bea.core.apache.log4j_1.2.13.jar",
stat("/modules/oracle.http_client_11.1.1.jar",
stat("/modules/oracle.osdt_11.1.1/osdt_cert.jar",
stat("/modules/oracle.osdt_11.1.1/osdt_core.jar",
stat("/modules/oracle.pki_11.1.1/oraclepki.jar",
stat("/modules/oracle.xdk_11.1.0/xmlparserv2.jar",
stat("/oracle/base/product/middleware/jdk16/jdk/jre/lib/charsets.jar",
stat("/oracle/base/product/middleware/jdk16/jdk/jre/lib/ext/dnsns.jar",
stat("/oracle/base/product/middleware/jdk16/jdk/jre/lib/ext/localedata.jar",
stat("/oracle/base/product/middleware/jdk16/jdk/jre/lib/ext/sunjce_provider.jar",
stat("/oracle/base/product/middleware/jdk16/jdk/jre/lib/ext/sunpkcs11.jar",

Something is wrong here, ‘emctl’ looking for .jar files in /modules, no wonder it can not find them. Actually, ‘emctl’ is a just a shell script, and a lot of related supporting functionality is written in Perl, so there is nothing that prevents us looking into them, or even editing them. For example, the code for ‘emctl start oms’, or ‘emctl status oms’ is in the file $OMS_HOME/bin/EMomsCmds.pm. This is what runs when issuing ‘emctl status oms':

#
# statusOMS - returns status of OMS
#
sub statusOMS()
{
    local (*args) = @_;
    shift(@args);
    shift(@args);

    my $class_path = "$ENV{WEBLOGIC_CLASSPATH}".
        "$cpSep$ORACLE_HOME/sysman/jlib/emagentSDK.jar".
        "$cpSep$ORACLE_HOME/sysman/jlib/emCORE.jar".
        "$cpSep$COMMON_ORACLE_HOME/modules/oracle.osdt_11.1.1/osdt_cert.jar".
        "$cpSep$COMMON_ORACLE_HOME/modules/oracle.osdt_11.1.1/osdt_core.jar".
        "$cpSep$COMMON_ORACLE_HOME/modules/oracle.http_client_11.1.1.jar".
        "$cpSep$MW_HOME/modules/com.bea.core.apache.log4j_1.2.13.jar";

    my $cmd = "$JAVA_HOME/bin/java -cp $class_path ".
        &EMomsCmds::getCommonJavaOptions() .
        "-DINSTANCE_HOME=$INSTANCE_HOME ".
        "-DORACLE_HOME=$ORACLE_HOME ".
        "-DMW_HOME=$MW_HOME ".
        "-DCLASSPATH=$class_path ".
        "oracle.sysman.emctl.wls.OMSController status @args";

    $rc = 0xffff & system($cmd);
    $rc >>= 8;
    exit($rc);
}

Some of the .jar files look very familiar here. It’s like the $COMMON_ORACLE_HOME and $MW_HOME variables get no value when this script is executed. Let’s confirm this, and edit the above file:

    my $class_path = "$ENV{WEBLOGIC_CLASSPATH}".
        "$cpSep$ORACLE_HOME/sysman/jlib/emagentSDK.jar".
        "$cpSep$ORACLE_HOME/sysman/jlib/emCORE.jar".
        "$cpSep$COMMON_ORACLE_HOME/modules/oracle.osdt_11.1.1/osdt_cert.jar".
        "$cpSep$COMMON_ORACLE_HOME/modules/oracle.osdt_11.1.1/osdt_core.jar".
        "$cpSep$COMMON_ORACLE_HOME/modules/oracle.http_client_11.1.1.jar".
        "$cpSep$MW_HOME/modules/com.bea.core.apache.log4j_1.2.13.jar";

    print "ORACLE_HOME: $ORACLE_HOME\n";
    print "COMMON_ORACLE_HOME: $COMMON_ORACLE_HOME\n";
    print "MW_HOME: $MW_HOME\n";

Run ‘emctl status oms’ again:

$ /oracle/base/product/middleware/oms/bin/emctl status oms
Oracle Enterprise Manager Cloud Control 12c Release 3
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
ORACLE_HOME: /oracle/base/product/middleware/oms
COMMON_ORACLE_HOME:
MW_HOME:
Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/log4j/Logger
        at oracle.sysman.emctl.wls.OMSController.(OMSController.java:101)
Caused by: java.lang.ClassNotFoundException: org.apache.log4j.Logger
        at java.net.URLClassLoader$1.run(URLClassLoader.java:202)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.net.URLClassLoader.findClass(URLClassLoader.java:190)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:306)
        at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:247)
        ... 1 more
Could not find the main class: oracle.sysman.emctl.wls.OMSController.  Program will exit.

Just as expected. Now to find where do these values come from:

$ cd /oracle/base/product/middleware/oms/bin
$ grep COMMON_ORACLE_HOME * | grep -v jar | sort | uniq
AgentReposResync.pm:my $commHome = $emgcProps{"COMMON_ORACLE_HOME"};
CompEMcentral.pm:    my $commHome = $emgcProps{"COMMON_ORACLE_HOME"};
CompEMcore.pm:  my $commHome = $emgcProps{"COMMON_ORACLE_HOME"};
EMconnectorCmds.pm:my $commHome = $emgcProps{"COMMON_ORACLE_HOME"};
EMomsCmds.pm:    my $commHome = $emgcProps{"COMMON_ORACLE_HOME"};
EMomsCmds.pm:    print "COMMON_ORACLE_HOME: $COMMON_ORACLE_HOME\n";
EMPlugin.pm:    $COMMON_ORACLE_HOME = $emgcProps{"COMMON_ORACLE_HOME"};
EnableDisableHostping.pm:   my $commHome = $emgcProps{"COMMON_ORACLE_HOME"};
HAConfigCmds.pm:my $commHome = $emgcProps{"COMMON_ORACLE_HOME"};
MdsCmds.pm:my $commHome = $emgcProps{"COMMON_ORACLE_HOME"};
OmsPropCmds.pm:my $commHome = $emgcProps{"COMMON_ORACLE_HOME"};
OmsProperty.pm:my $commHome = $emgcProps{"COMMON_ORACLE_HOME"};
PAFTool.pm:    my $COMMON_ORACLE_HOME = $emgcProps{"COMMON_ORACLE_HOME"};
PARTool.pm:    my $COMMON_ORACLE_HOME = $emgcProps{"COMMON_ORACLE_HOME"};
RegisterTType.pm:  my $commHome = $emgcProps{"COMMON_ORACLE_HOME"};
SecureOMSCmds.pm:    my $commHome = $emgcProps{"COMMON_ORACLE_HOME"};

It is related to emgcProps. Investigate further:

$ grep emgcProps EMomsCmds.pm | sort | uniq
    my $commHome = $emgcProps{"COMMON_ORACLE_HOME"};
    my %emgcProps = &EMomsCmds::getEmgcProps();
    my %emgcProps = getEmgcProps();
    my $INSTANCE_HOME = $emgcProps{"EM_INSTANCE_HOME"};
    my $mwHome  = $emgcProps{"MW_HOME"};
    my $wlsHome = $emgcProps{"WLS_HOME"};

The definition of getEmgcProps() is also in the EMomsCmds.pm. It starts as:

sub getEmgcProps()
{
    #load emgc.properties
    my $EMGC_FILE_LOC = "";
    my $OMS_NAME = $ENV{'OMS_NAME'};
    my $OMS_NAME_SWLIB = $ENV{'OMS_NAME'};

    #get file loc from $OH/sysman/config/emInstanceMapping.properties
    my $INSTANCE_MAP_LOC = "$ORACLE_HOME/sysman/config/emInstanceMapping.properties";
    if (!(-e $INSTANCE_MAP_LOC))
    {
        print "No OMS is found to be associated with the current Oracle Home\n";
        exit(1);
    }
    ...

It references to properties file: emInstanceMapping.properties and emgc.properties (in comment, and later in code). The first one seemed to be fine:

$ cat /oracle/base/product/middleware/oms/sysman/config/emInstanceMapping.properties
#Fri Apr 04 01:35:45 CEST 2014
EMGC_OMS1=/oracle/base/product/gc_inst/em/EMGC_OMS1/emgc.properties

Back to emgc.properties, and this was the problem:

$ ll /oracle/base/product/gc_inst/em/EMGC_OMS1/emgc.properties
-rw-r-----. 1 oracle oinstall 0 Apr 04 01:36 /oracle/base/product/gc_inst/em/EMGC_OMS1/emgc.properties

The emgc.properties file was empty, with size 0. This file is regenerated every time the OMS starts, but ‘emctl’ commands use this file to read variables (HOMEs, PORTs and such), so with an empty file, ‘emctl start oms’ will not work.
Fortunately, there was a backup of this file, after restoring that, OMS started without problems. However, if there is no backup of this file, the values still can be found in the older logs, e.g.:

$ grep -E "HOME|PORT" /oracle/base/product/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/logs/EMGC_ADMINSERVER.out00015 | awk '{ print $9 }'
EMHOME=/oracle/base/product/middleware/oms>
JAVA_HOME=/oracle/base/product/middleware/jdk16/jdk>
EM_CONSOLE_HTTPS_PORT=7803>
COHERENCE_HOME=>
OPMN_LOCAL_PORT=0>
CURRENT_HOME=/oracle/base/product/middleware/oracle_common>
MSPORT=7203>
EM_OC4J_HOME=>
EM_INSTANCE_HOME=/oracle/base/product/gc_inst/em/EMGC_OMS1>
COMMON_ORACLE_HOME=/oracle/base/product/middleware/oracle_common>
ORACLE_HOME=/oracle/base/product/middleware/oracle_common>
BEA_HOME=/oracle/base/product/middleware>
PERL_HOME=/oracle/base/product/middleware/oms/perl>
EM_REPOS_CONNECTDESCRIPTOR=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.30)(PORT=1521)))(CONNECT_DATA=(SID=EMREP)))>
AS_HTTPS_PORT=7103>
MS_HTTPS_PORT=7302>
JRE_HOME=/oracle/base/product/middleware/oms/jdk/jre>
COMMON_COMPONENTS_HOME=/oracle/base/product/middleware/oracle_common>
AS_PORT=0>
EM_UPLOAD_HTTP_PORT=4890>
EM_DOMAIN_HOME=/oracle/base/product/gc_inst/user_projects/domains/GCDomain>
EM_WEBTIER_INSTHOME=/oracle/base/product/gc_inst/WebTierIH1>
DELEGATE_ORACLE_HOME=/oracle/base/product/middleware/oracle_common>
WLST_HOME=/oracle/base/product/middleware/oracle_common/common/wlst:>
WLST_PROPERTIES=-Dweblogic.wlstHome='/oracle/base/product/middleware/oracle_common/common/wlst:'
OHS_PROXY_PORT=0>
WEBTIER_ORACLE_HOME=/oracle/base/product/middleware/oms/../Oracle_WT>
EM_NODEMGR_PORT=7404>
EM_CONSOLE_HTTP_PORT=7790>
EM_UPLOAD_HTTPS_PORT=4904>
EM_NODEMGR_HOME=/oracle/base/product/gc_inst/NodeManager/emnodemanager>
DERBY_HOME=/oracle/base/product/middleware/wlserver_10.3/common/derby>
DERBY_SYSTEM_HOME=/oracle/base/product/middleware/wlserver_10.3/common/derby/demo/databases>
MW_HOME=/oracle/base/product/middleware>
ANT_HOME=/oracle/base/product/middleware/modules/org.apache.ant_1.7.1>
WL_HOME=/oracle/base/product/middleware/wlserver_10.3>
CRS_HOME=>
OPMN_REMOTE_PORT=0>
HOME=/home/oracle>
WLS_HOME=/oracle/base/product/middleware/wlserver_10.3>

ORA-02072

Have a look at the error description:

$ oerr ora 2072
02072, 00000, "distributed database network protocol mismatch"
// *Cause: This should never happen between different PRODUCTION releases of
// ORACLE, but may happen between alpha and beta releases, for example.
// *Action: Upgrade the older release.
//

Well, it actually happened on PRODUCTION releases of ORACLE. A test database on AIX platform was upgraded to 11.2.0.4, and since then, querying specific user views from this database through database links resulted in the above error. Querying tables, dual, or dictionary views worked fine. It is true, that the views to be queried had some “interesting” structure behind them, something like this (replaced specific names with meaningless data):

create user u1 identified by Oracle123;
create user u2 identified by Oracle123;
create user u3 identified by Oracle123;
create user u4 identified by Oracle123;
alter user u1 quota unlimited on users;
grant create session to u1, u2, u3, u4;
grant create table to u1;
grant create synonym to u2, u3;
grant create view to u3, u4;
grant create database link to u3;
create table u1.t1 as ...;
create synonym u2.t1 for u1.t1;
grant select on u1.t1 to u2;
conn u3/Oracle123
create database link orcl.us.oracle.com connect to u2 identified by Oracle123 using 'orcl.company.domain';
create synonym u3.t1 for u1.t1@orcl.us.oracle.com;
create view u3.v1 (column1, column2, ...) as select column1, column2, ... from t1;
grant select on u3.v1 to u4;
conn u4/Oracle123
create view u4.v1 (column1, column2, ...) as select column1, column2, ... from u3.v1;

Different users, some synonyms and views, and even a loopback database link involved. This took place in a 11.2.0.4 database, and the developers tried to select from u4.v1 in a 11.2.0.3 database through a database link.

SQL> create database link olink conntect to u4 identified by Oracle123 using 'orcl.company.domain';

SQL> select * from dual@olink;

D
-
X

1 row selected.

SQL> select * from v1@olink;
select * from v1@olink
 *

ERROR at line 1:
ORA-02072: distributed database network protocol mismatch

Actually there is a yet unpublished note on My Oracle Support, about a more or less similar problem:

ORA-2072 Then ORA-7445 WHEN PERFORMING DML VIA DBLINK WITH QUERY OVER DBLINK (Doc ID 1635749.1)

That issue is fixed in release 11.2.0.4, and there is an one-off patch for 11.2.0.3, patch number 5702977, and that patch also fixes this error. However, this patch has to be installed on both the local and remote sides, which induces a chain reaction of patching.

In our case, the alternative solution was to simplify the structure behind the view in the remote database, which also solved the problem.

 

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: