Kafka Connector For Oracle

I have just released Kafka Oracle source connector , in order to import changed data from Oracle database to Kafka. Main logic is based on Oracle Logminer solution and all details and instructions can be found at following URL

https://github.com/erdemcer/kafka-connect-oracle

SQL Profile ignores hints

Sometimes , hints can be very powerful to let optimizer choose proper execution plans. This lets application execute with good performance. But sometimes given hints to sqls can be harmful choosing wrong execution plans.At this situations , development can take care of this sqls and by removing hints problems can be solved. But if development process can not be done immediately . DBA can solve this issues by telling optimizer to ignore hints.

alter session set "_optimizer_ignore_hints"=true 

command ignores all hints during session. This can solve your problem but also can be trouble.At sql level ignoring hints can be done by import_sql_profile procedure in dbms_sqltune package.

For sample below sql is used . Firstly given hint for sql tell optimizer not to use index. But for some reason hint losed its validity and index should be used for query.

SQL> explain plan for select /*+no_index(s) */ count(1) from xdba_dcyear s where cust_name like 'ER%';

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3491557762
--------------------------------------------------------------------------------
| Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| T
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                    |     1 |   252 |     2   (0)| 0
|   1 |  SORT AGGREGATE    |                    |     1 |   252 |            |
|*  2 |   TABLE ACCESS FULL| XDBA_DCYEAR |    20 |  5040 |     2   (0)| 0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("CUST_NAME" LIKE 'ER%')
Note
-----
   - dynamic sampling used for this statement (level=2)
18 rows selected
Executed in 0.076 seconds

Now dbms_sqltune.import_sql_profile procudure is used with attribute "IGNORE_OPTIM_EMBEDDED_HINTS".

begin
 dbms_sqltune.import_sql_profile(
 name => 'SQLPROF1',
 category => 'DEFAULT',
 sql_text => 'select /*+no_index(s) */ count(1) from xdba_dim_cust_year s where cust_name like ''ER%''',

 profile => sqlprof_attr('IGNORE_OPTIM_EMBEDDED_HINTS'));
end;
/


SQL> explain plan for select /*+no_index(s) */ count(1) from xdba_dcyear s where cust_name like 'ER%';

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2253536563
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   252 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |      |     1 |   252 |            |          |
|*  2 |   INDEX RANGE SCAN| X1   |    20 |  5040 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CUST_NAME" LIKE 'ER%')
       filter("CUST_NAME" LIKE 'ER%')
Note
-----
   - dynamic sampling used for this statement (level=2)
   - SQL profile "SQLPROF1" used for this statement
20 rows selected
Executed in 0.126 seconds

As a result no_index hint is ignored and index is being used according to explain plan. Also usage of "SQLPROF1" profile can be observe from explain plan notes

In order to remove sql profile following procedure is used.

begin
 dbms_sqltune.import.drop_sql_profile('SQLPROF1');
end;
/

Note : dbms_sqltune package is under Oracle Tuning Pack license.This must be considered.

ORA-01378 The logical block size error

For some reason client asked me to open production database in another environment until specific time.After successfull restore and recover process , clear logfile gave the below error ,

ORA-01378: The logical block size (4096) of file +RECO is not compatible with the disk sector size (media sector size is 512 and host sector size is 512)

Production system 's redo log files have 4K sector size and clearing log file on 512 byte is not possible.

Recreating controlfiles without redo logs statements which are 4K size has solved our issue.I have added new redo logfiles to system before recreating controlfile which area group 11,12,13

create controlfile reuse database "testdb" noresetlogs force logging archivelog

  maxlogfiles 

  max...

  ...

logfile2

   group 2('+RECO/TESTDB/log2a','+RECO/TESTDB/log2b') size 100M blocksize 4096,

   group 11('+RECO/TESTDB/log11a','+RECO/TESTDB/log11b') size 100M blocksize 512,

   group 12('+RECO/TESTDB/log12a','+RECO/TESTDB/log12b') size 100M blocksize 512

   group 13('+RECO/TESTDB/log13a','+RECO/TESTDB/log13b') size 100M blocksize 512

datafile

...

...

New Controlfile script : 

create controlfile reuse database "testdb" noresetlogs force logging archivelog

  maxlogfiles 

  max...

  ...

logfile2

   group 11('+RECO/TESTDB/log11a','+RECO/TESTDB/log11b') size 100M blocksize 512,

   group 12('+RECO/TESTDB/log12a','+RECO/TESTDB/log12b') size 100M blocksize 512

   group 13('+RECO/TESTDB/log13a','+RECO/TESTDB/log13b') size 100M blocksize 512

datafile

...

...

Installation of Oracle Foreign Data Wrapper for PostgreSQL

If you are working on both Oracle and PostgreSQL databases , sometimes you need to access each database from other. My project contains accessing Oracle database from PostgreSQL database and this took me to search how to do it and found foreign data wrappers.Foreign data wrapper based on accessing to data that is not in PostgreSQL database.

Following link contains all FDW information for PostgreSQL .

https://wiki.postgresql.org/wiki/Foreign_data_wrappers

In order to install Oracle FDW , below Oracle Instant Client rpm files and oracle_fdw extension are needed.

Oracle FDW - https://github.com/laurenz/oracle_fdw

Oracle Instant Client - http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html

Install oracle-instantclient-basic, oracle-instantclient-sqlplus, oracle-instantclient-devel packages for your version.

After installation below envinroments are set.

export PATH=$PATH:/usr/pgsql-9.3/bin
export ORACLE_HOME=/usr/lib/oracle/11.2/client64
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME/bin:$LD_LIBRARY_PATH

Extract downloaded oracle_fdw.master.zip file and change into directory.Software installation is done with following commands

$make

$make install

Now oracle_fdw shared library has been installed in the PostgreSQL library directory and oracle_fdw.control and the SQL files are in the PostgreSQL extension directory.

Following step is done to install extension in a database with superuser.

$psql -d {Your database name}

$CREATE EXTENSION oracle_fdw;

ERROR:  could not load library "/usr/pgsql-9.3/lib/oracle_fdw.so": libclntsh.so.11.1: cannot open shared object file: No such file or directory

  

Above error can be seen while creating extension because of library missing which is actually exists.In order to solve this error, Oracle library path is added to system library list.

$echo /usr/lib/oracle/11.2/client64/lib> /etc/ld.so.conf.d/oracle.conf

$ldconfig

Executing same command for install extension completed successfully after library arrangement.

$CREATE EXTENSION oracle_fdw;

CREATE EXTENSION

enq tx - row lock contention tx mode 4

After transition of new project to live system, we have seen excessive "enq tx - row lock contention" wait event on 2 node RAC system.From top activity as seen below ,

Node 1 :

Node 2 :

 

these waits cause users not work properly. When examine the sql which causes lock wait was simple insert statement and from P1 field of v$session who waits for this event indicates that lock type was  shared mode 4 TX wait. Common causes for mode 4 are 

a.Unique index

b.Foreign Key

c.Bitmap index

  

v$session , for sessions which wait for this event gives object which cause wait.

select row_wait_obj#,row_wait_file#,row_wait_block#,row_wait_row#

  from gv$session

where event='enq: TX - row lock contention' and state='WAITING'

Output of sql gave me object which was bitmap index and after dropping index  problem has gone away.According to explanation for this type of operation , each entry in bitmap index can cover multiple rows   in table.If two sessions would like to update rows covered by the same index key entry , second session waits for the first session's transaction to be performed by commit or rollback by waiting for the TX lock in mode 4.

libXp.so.6: cannot open shared object file: No such file or directory

While installing Oracle 10gr2 on linux x86_64 i have faced this error when Oracle Universal Installer is invoked.

Exception java.lang.UnsatisfiedLinkError: /tmp/OraInstall2012-04-16_09-50-07AM/jre/1.4.2/lib/i386/libawt.so: libXp.so.6: cannot open shared object file: No such file or directory occurred..
java.lang.UnsatisfiedLinkError: /tmp/OraInstall2012-04-16_09-50-07AM/jre/1.4.2/lib/i386/libawt.so: libXp.so.6: cannot open shared object file: No such file or directory
        at java.lang.ClassLoader$NativeLibrary.load(Native Method)
        at java.lang.ClassLoader.loadLibrary0(Unknown Source)
        at java.lang.ClassLoader.loadLibrary(Unknown Source)
        at java.lang.Runtime.loadLibrary0(Unknown Source)
        at java.lang.System.loadLibrary(Unknown Source)
        at sun.security.action.LoadLibraryAction.run(Unknown Source)
        at java.security.AccessController.doPrivileged(Native Method)
        at sun.awt.NativeLibLoader.loadLibraries(Unknown Source)
        at sun.awt.DebugHelper.<clinit>(Unknown Source)
        at java.awt.Component.<clinit>(Unknown Source)
        at oracle.sysman.oii.oiif.oiifm.OiifmGraphicInterfaceManager.<init>(OiifmGraphicInterfaceManager.java:222)
        at oracle.sysman.oii.oiic.OiicSessionInterfaceManager.createInterfaceManager(OiicSessionInterfaceManager.java:193)
        at oracle.sysman.oii.oiic.OiicSessionInterfaceManager.getInterfaceManager(OiicSessionInterfaceManager.java:202)
        at oracle.sysman.oii.oiic.OiicInstaller.getInterfaceManager(OiicInstaller.java:436)
        at oracle.sysman.oii.oiic.OiicInstaller.runInstaller(OiicInstaller.java:926)
        at oracle.sysman.oii.oiic.OiicInstaller.main(OiicInstaller.java:866)
Exception in thread "main" java.lang.NoClassDefFoundError
        at oracle.sysman.oii.oiif.oiifm.OiifmGraphicInterfaceManager.<init>(OiifmGraphicInterfaceManager.java:222)
        at oracle.sysman.oii.oiic.OiicSessionInterfaceManager.createInterfaceManager(OiicSessionInterfaceManager.java:193)
        at oracle.sysman.oii.oiic.OiicSessionInterfaceManager.getInterfaceManager(OiicSessionInterfaceManager.java:202)
        at oracle.sysman.oii.oiif.oiifm.OiifmAlert.<clinit>(OiifmAlert.java:151)
        at oracle.sysman.oii.oiic.OiicInstaller.runInstaller(OiicInstaller.java:984)
        at oracle.sysman.oii.oiic.OiicInstaller.main(OiicInstaller.java:866)

it is caused from missing package.After installing XFree86-libs Universal installer started successfully.

XFree86-libs-3.3.5-1.5.x.i386.rpm

ERROR OGG-01161 Bad column index () specified for table ., max columns = 62 .

Because of mismatch structure between source and target tables, this error can be taken.In my case i am not synchronizing all columns for specific table named TEST1 . First 62 columns of a table are in  synchronization.There was already changes in the source table and these changes were not propagated to target as well.

After abnormal crash of source database ,Golden Gate immediately gave ERROR OGG-01161 error.

2014-03-02 18:19:50  ERROR   OGG-01161  Oracle GoldenGate Delivery for Oracle, REP1.prm:  Bad column index (62) specified for table TEST.TABLE1, max columns = 62.

2014-03-02 18:19:50  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, REP1.prm:  PROCESS ABENDING.

For solution , I have recreated definiton file for specific table and copied def file content to target def file.I have not created absent columns at target side.Updating definition file with new definition of related table solved my issue.

Source : 

$GG_HOME=/gg

1. Created new definition parameter file for TEST.TABLE1

   ## TEST1.prm file

   DEFSFILE /gg/dirdef/TEST1.def

   USERID gg01, password gg01

   TABLE TEST.TABLE1;

2. Using the defgen utility ,new definition file is created

   /gg/defgen paramfile /gg/dirprm/TEST1.prm

   

   This command will create TEST1.def under /gg/dirdef and will contain following information for table1

   

   #######################################################################
   *+- Defgen version 2.0, Encoding UTF-8

*

*  Definitions created/modified  2014-03-02 19:05

*

*  Field descriptions for each column entry:

*

*     1    Name

*     2    Data Type

*     3    External Length

*     4    Fetch Offset

*     5    Scale

*     6    Level

*     7    Null

*     8    Bump if Odd

*     9    Internal Length

*    10    Binary Length

*    11    Table Length

*    12    Most Significant DT

*    13    Least Significant DT

*    14    High Precision

*    15    Low Precision

*    16    Elementary Item

*    17    Occurs

*    18    Key Column

*    19    Sub Data Type

*

Database type: ORACLE

Character set ID: ISO-8859-9

National character set ID: UTF-16

Locale: neutral

Case sensitivity: 14 14 14 14 14 14 14 14 14 14 14 14 11 14 14 14

Definition for table TEST.TABLE1

Record length: 204

Syskey: 0

Columns: 4

C1   64     20        0   0  0 1 0     20     20      0 0 0 0 0 1    0 1 0

C2   64     50       26  0  0 1 0     50     50     50 0 0 0 0 1    0 1 2

C3     0      1       82  0  0 1 0      1      1      0 0 0 0 0 1    0 1 0

C4   64     50       86  0  0 1 0     50     50     50 0 0 0 0 1    0 1 2

End of definition

   #######################################################################

Target :

3. Stopped replicat and manager at target

4. Due to more than 1 table exist in GoldenGate replication , only TEST1 table part of target definition file was replaced with above content between "Definition for table" and "End of definition" snippet.

5. Started manager and replicat.

Oracle Discoverer 11g host name change issues

I had to clone server which contains Oracle Discoverer 11g for a test purpose.Host name change process was done by examining the Rittman following document 

http://www.rittmanmead.com/2010/12/oracle-bi-ee-11g-managing-host-name-changes/

Successfull change process provided Admin Server and Managed server to run without any problem.

But 2 problems have been seemed ,

1.Port Number In Weblogiccluster Parameter Specified In Httpd.Conf Is Not An Integer Less Than 65535

Of course hostname change is not an easy process on 11g Discoverer which has a lot of components.Although changing hostname in related all files , some others can be missed. This problem occured from same manner.

In ORACLE_INSTANCE/config/OHSComponent/ohs-comp-name/moduleconf/module_disco.conf

<Location /discoverer tag contains WeblogicCluster address maps to disco server name and related port.It must be changed to new hostname

2.Discoverer Components Application URL

From Weblogic Enterprise Manager discoverer components have Application URL mapping to old hostname.Following directory contains configuration.xml stores Discoverer settings.

DOMAIN_HOME/config/fmwconfig/servers/WLS_DISCO/applications/discoverer_discoverer_version/configuration/

Head of configuration.xml related applicaton url can bee seen to change.

Simple DNS Configuration for Oracle RAC

While installing RAC on virtualBox guests (linux), related docs declare that SCAN addresses should not be defined in the hosts file.Beacuse of round-robin resolution cannot be simulated using a local host file ,The Single Client Access Name (SCAN) should be defined in the DNS  and round-robin between one of 3 addresses , which are on the same subnet as the public and virtual IPs.

I am using virtualbox for testing purposes on various guests, and sometimes DNS server is needed.Not to struggle with complex DNS configuration , Dnsmasq  is the best solution.Following steps are used to install,configure,start Dnsmasq service.

On seperate linux guest , Dnsmasq  is installed from yum 

# yum install dnsmasq 

Start Dnsmasq  service 

# service dnsmasq  start

To start Dnsmasq  service automatically on reboot 

# chkconfig dnsmasq on

Dnsmasq  will use entries of the "/etc/hosts" to resolve.

"/etc/hosts" file contains following entries on the server running Dnsmasq  service provide acting live DNS server by resolving these entries.

192.168.0.91 testracclusterscan.localdomain testracclusterscan
192.168.0.92 testracclusterscan.localdomain testracclusterscan

192.168.0.93 testracclusterscan.localdomain testracclusterscan

192.168.0.71 node1.localdomain node1

192.168.0.72 node2.localdomain node2

Guests which will use DNS server for name resolution , need to have "/etc/resolve.conf" file configured DNS server

nameserver 192.168.0.10
search localdomain

Detail information about SCAN:
http://www.oracle.com/technetwork/database/clustering/overview/scan-129069.pdf

source : http://www.oracle-base.com/articles/linux/dnsmasq-for-simple-dns-configurations.php

enq: TS – contention dbms_stats

On 11.2.0.3 sessions were waiting when try to collect statistics with dbms_stats.
Job collecting statistics was blocked by parallel sessions of dbms_stats.

Insert /*+ append */ into sys.ora_temp_1_ds_350003 SELECT /*+  parallel(t,16) parallel_index(t,16)

According to metalink doc , this wait can be seen while using parallelism for dbms_stats.Temporary
solution is setting parallelism to 1 . For permanent solution , patch 12865902  should be applied to database.

Source : Metalink ‘enq: TS – contention’ / Hang While Gathering Statistics in Parallel [ID 1463791.1]

jarsigner: unable to recover key from keystore

I have encountered this problem while signing EBS jarfiles with new digital certificate.Codesigning certificate is used to sign jar files , in order to pass through security issues which come with new Java version 1.7.0_51. 

New cert file has private key with different password. When keystore and keyentry have different password , this issue can become. 

ERROR: JarSigner subcommand exited with status 1

JarSigner standard output:

jarsigner: unable to recover key from keystore

JarSigner error output:

Enter Passphrase for keystore: Enter key password for <Alias>

In order to pass over , keyentry password should be changed to keystore password.

keytool -keypasswd -keystore adkeystore.dat -keypass <KeyEntryPass> -new <KeyStorePass> -alias ykxcodesign

Change Domain Name of Apps Tier for EBS R12

For a certification issue i had to change domain name of apps Tier with following steps.Firstly i have a unclear point that different domains of db Tier and apps Tier is compatible ? After below steps , i have examined there will be no problem.

Related steps

1. Deregister current App server

    As the applications domain name will be changed , app server need to be deregistered.
    perl $AD_TOP/bin/adgentns.pl appspass=<APPSpwd> contextfile=<CONTEXT> -removeserver

2. Change domain name of server

    Ensure that /etc/hosts shows new domain.

3. Recreate context file with new settings

    mv $INST_TOP/appl/admin/$CONTEXTFILE $INST_TOP/appl/admin/oldcontextfile.xml

    cd $INST_TOP/appl/admin
    perl $COMMON_TOP/clone/bin/adclonectx.pl contextfile=oldcontextfile.xml

    This step will create new context file with new app domain name.

4. Execute autoconfig

    $AD_TOP/bin/adconfig.sh contextfile=$INST_TOP/appl/admin/$CONTEXTFILE appspass=<password>

5. Check fnd_nodes.DOMAIN , fnd_nodes.WEBHOST and icx_parameters.SESSION_COOKIE_DOMAIN values to       ensure that show new domain name.

6. Start all application tier services with adstrtal.sh apps/<appspassword>

ORA-08104: this index object is being online built or rebuilt.

While creating an index , related session has been killed.When trying drop index
or rebuild it following error occured.

ORA-08104: this index object <object_id> is being online built or rebuilt.

SMON should do clean up process.But there must be no transaction against the table in order to SMON be successfull.
To do this application can be stopped and wait until cleanup is done.

For 10.1 SMON need to be waited to finish process,After 10.2 DBMS_REPAIR.ONLINE_INDEX_CLEAN() can be used.

Following script does this cleanup process.

declare
  ret boolean;
begin
  ret=sys.dbms_repair.online_index_clean(&OBJECT_ID);
end;