How to stop data pump jobs EXPDP

Export full DB backup :--

expdp \'/ as sysdba\' full=Y directory=TEST_DIR dumpfile=DB11G.dmp logfile=Full_DB_bkp.log compression=all 

Export: Release 11.2.0.2.0 - Production on Fri Dec 12 20:24:31 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_FULL_01":  "/******** AS SYSDBA" full=Y directory=TEST_DIR dumpfile=DB11G.dmp logfile=Full_DB_bkp.log compression=all 
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 27.60 GB


Stop Export Backup from DB level:--

SQL> set lines 1000 pages 1000
SQL> SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;

OWNER_NAME                     JOB_NAME                       OPERATION                      JOB_MODE                       STATE
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
SYS                            SYS_EXPORT_FULL_01             EXPORT                         FULL                           EXECUTING


oracle@Test-DB$expdp \'/ as sysdba\' attach=SYS_EXPORT_FULL_01

Export: Release 11.2.0.2.0 - Production on Fri Dec 12 21:08:53 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

Job: SYS_EXPORT_FULL_01
  Owner: SYS
  Operation: EXPORT
  Creator Privs: TRUE
  GUID: 0A076075C651060DE054002128D658F4
  Start Time: Friday, 12 December, 2014 20:46:30
  Mode: FULL
  Instance: TestPRD
  Max Parallelism: 1
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        "/******** AS SYSDBA" full=Y directory=CRAMERDATA_EXP dumpfile=Full_DB_bkp.dmp logfile=Full_DB_bkp.log
  State: EXECUTING
  Bytes Processed: 22,522,186,936
  Percent Done: 98
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: /orabkp/exp_backup/Full_DB_bkp.dmp
    bytes written: 22,569,902,080

Worker 1 Status:
  Process Name: DW00
  State: EXECUTING
  Object Schema: Test
  Object Name: CR_FR_RO
  Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
  Completed Objects: 313
  Total Objects: 3,100
  Worker Parallelism: 1

Export> STOP_JOB
Are you sure you wish to stop this job ([yes]/no): yes

oracle@Test-DB$

Export Check Status :--

SQL> set lines 1000 pages 1000
SQL> SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;

OWNER_NAME                     JOB_NAME                       OPERATION                      JOB_MODE                       STATE
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
SYS                            SYS_EXPORT_FULL_01             EXPORT                         FULL                           NOT RUNNING

SQL> exit

Enable or Disable Hibernate in Windows

To Enable Hibernate

NOTE: This step will restore the hiberfil.sys file, and the Allow hybrid sleep and Hibernate after Power Options under Sleep.
You Need Administrator access for run below command 

In the command prompt,Run below, and press Enter. & check START butten

powercfg -h on 

To Disable Hibernate

NOTE: This step will disable hibernation, delete the hiberfil.sys file, and remove the Allow hybrid sleep and Hibernate after Power Options under Sleep..

In the command prompt, Run below, and press Enter. & check START butten

powercfg -h off

PRCN-2065 : Port(s) 1521 are not available on the nodes given

Error:--

INFO: Problem in configuration: PRCN-2061 : Failed to add listener ora.LISTENER.lsnr
INFO: PRCN-2065 : Port(s) 1521 are not available on the nodes given
INFO: PRCN-2067 : Port 1521 is not available across node(s) "hww-poc1-VIP,hww-poc2-VIP"
INFO: Oracle Net Listener Startup:
INFO:     Listener does not exists.
INFO: Check the trace file for details: /home/grid/app/grid/cfgtoollogs/netca/trace_Ora11g_gridinfrahome1-1410287PM2700.log
INFO: Oracle Net Services configuration failed.  The exit code is 1



I had a similar issue to this. It turned out the tns listener had already started on the VIP address on the both nodes.

[root@test-poc1 ~]# ps -ef | grep tns
root        165      2  0 Oct27 ?        00:00:00 [netns]
grid      80554      1  0 19:23 ?        00:00:00 /home/11.2.0/grid/bin/tnslsnr LISTENER_SCAN1 -inherit
root      83352  62292  0 19:33 pts/2    00:00:00 grep tns

[root@test-poc2 ~]# ps -ef | grep tns
root        165      2  0 Oct27 ?        00:00:00 [netns]
grid      80554      1  0 19:23 ?        00:00:00 /home/11.2.0/grid/bin/tnslsnr LISTENER_SCA2 -inherit
root      83352  62292  0 19:33 pts/2    00:00:00 grep tns
 
Check below command :--
if 1521 port assign to other ip like below show in bold. 

[root@test-poc1 ~]# netstat -nltp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name
tcp        0      0 0.0.0.0:000                 0.0.0.0:*                   LISTEN      9774/rpcbind
tcp        0      0 00.100.10.00:1521           0.0.0.0:*                   LISTEN      80554/tnslsnr
tcp        0      0 0.0.0.0:5522                0.0.0.0:*                   LISTEN      33613/sshd
Kill the SCAN listner & stop/disable listner process in both node using below command :-

[grid@test-poc1 ~]$ /home/11.2.0/grid/bin/srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node hww-poc1
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node hww-poc2

[grid@test-poc1 ~]$ /home/11.2.0/grid/bin/srvctl stop scan_listener

[grid@test-poc1 ~]$ /home/11.2.0/grid/bin/srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is not running
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is not running

[grid@test-poc1 ~]$ /home/11.2.0/grid/bin/srvctl disable scan_listener

[grid@test-poc1 ~]$  /home/11.2.0/grid/bin/srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is disabled
SCAN listener LISTENER_SCAN1 is not running
SCAN Listener LISTENER_SCAN2 is disabled
SCAN listener LISTENER_SCAN2 is not running

then re-try the Oracle Net Configuration Assistant step allowed me to continue.

clscfg.bin: error while loading shared libraries: libcap.so.1: cannot open shared object file: No such file or directory

Error:--

Failed to create keys in the OLR, rc = 127, Message:
  /home/11.2.0/grid/bin/clscfg.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 at /home/11.2.0/grid/crs/install/crsconfig_lib.pm line 7660.
/home/11.2.0/grid/perl/bin/perl -I/home/11.2.0/grid/perl/lib -I/home/11.2.0/grid/crs/install /home/11.2.0/grid/crs/install/rootcrs.pl execution failed
 

Issue :--

[root@test-poc1 ~]# /home/11.2.0/grid/root.sh
Performing root user operation for Oracle 11g

The following environment variables are set as:
    ORACLE_OWNER= grid
    ORACLE_HOME=  /home/11.2.0/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: /home/11.2.0/grid/crs/install/crsconfig_params
User ignored Prerequisites during installation
Installing Trace File Analyzer
Failed to create keys in the OLR, rc = 127, Message:
  /home/11.2.0/grid/bin/clscfg.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 at /home/11.2.0/grid/crs/install/crsconfig_lib.pm line 7660.
/home/11.2.0/grid/perl/bin/perl -I/home/11.2.0/grid/perl/lib -I/home/11.2.0/grid/crs/install /home/11.2.0/grid/crs/install/rootcrs.pl execution failed
[root@test-poc1 ~]#

Solution :--
check below command

# yum list | grep libcap
compat-libcap1.i686 1.10-1 @ol6_ga_base
libcap.i686 2.16-5.2.el6 @ol6_ga_base
libcap.x86_64 2.16-5.2.el6 @anaconda-OracleLinuxServer-201102031546.x86_64/6.0
libcap-devel.i686 2.16-5.2.el6 @ol6_ga_base
libcap-devel.x86_64 2.16-5.2.el6 @ol6_ga_base
libcap-ng.x86_64 0.6.4-3.el6_0.1 @ol6_u1_base
compat-libcap1.x86_64 1.10-1 ol6_ga_base
libcap-ng.i686 0.6.4-3.el6_0.1 ol6_u1_base
libcap-ng-devel.i686 0.6.4-3.el6_0.1 ol6_u1_base
libcap-ng-devel.x86_64 0.6.4-3.el6_0.1 ol6_u1_base
libcap-ng-python.x86_64 0.6.4-3.el6_0.1 ol6_u1_base
libcap-ng-utils.x86_64 0.6.4-3.el6_0.1 ol6_u1_base


Install below 2 pkg & run root.sh again
 
Installed...
# yum install libcap.x86_64
Setting up Install Process
Package libcap-2.16-5.2.el6.x86_64 already installed and latest version
Nothing to do
libcap2 package installed, so install libcab1.
 
# yum install compat-libcap1.x86_64
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package compat-libcap1.x86_64 0:1.10-1 will be installed
--> Finished Dependency Resolution

ld.so.1: sqlplus: fatal: libsqlplus.so: open failed: No such file or directory

Issue :--

oracle@test$sqlplus
ld.so.1: sqlplus: fatal: libsqlplus.so: open failed: No such file or directory
Killed
oracle@test$


solution :--

We could see from above result that some shared library dependencies for sqlplus is not found.
Following settings are applicable for Solaris.

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/dt/lib:/usr/openwin/lib:/lib:/usr/lib:/usr/lib/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:
export LD_LIBRARY_PATH
 
After setting correct value for LD_LIBRARY_PATH, issue got resolved and not getting any error.


oracle@test$sqlplus

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Oct 29 13:52:25 2014

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Enter user-name: / as sysdba

Database shortcut command's in PostgreSQL

General:-

  \c[onnect] [DBNAME|- [USER]]
                 connect to new database (currently "pizzastore")
  \cd [DIR]      change the current working directory
  \copyright     show PostgreSQL usage and distribution terms
  \encoding [ENCODING]
                 show or set client encoding
  \h [NAME]      help on syntax of SQL commands, * for all commands
  \q             quit psql
  \set [NAME [VALUE]]
                 set internal variable, or list all if no parameters
  \timing        toggle timing of commands (currently off)
  \unset NAME    unset (delete) internal variable
  \! [COMMAND]   execute command in shell or start interactive shell

Query Buffer:-

  \e [FILE]      edit the query buffer (or file) with external editor
  \g [FILE]      send query buffer to server (and results to file or |pipe)
  \p             show the contents of the query buffer
  \r             reset (clear) the query buffer
  \s [FILE]      display history or save it to file
  \w FILE        write query buffer to file
Input/Output
  \echo [STRING] write string to standard output
 
  \echo `date`
Thu Jun 26 21:40:57 IST 2014
 
  \i FILE        execute commands from file
  \o [FILE]      send all query results to file or |pipe
  \qecho [STRING]
                 write string to query output stream (see \o)

Informational:-

  \d [NAME]      describe table, index, sequence, or view
  \d{t|i|s|v|S} [PATTERN] (add "+" for more detail)
                 list tables/indexes/sequences/views/system tables
  \da [PATTERN]  list aggregate functions
  \db [PATTERN]  list tablespaces (add "+" for more detail)
  \dc [PATTERN]  list conversions
  \dC            list casts
  \dd [PATTERN]  show comment for object

Shows the descriptions of objects matching the pattern, or of all visible objects if no argument is given. But in either case, only objects that have a description are listed. By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects. "Object" covers aggregates, functions, operators, types, relations (tables, views, indexes, sequences), large objects, rules, and triggers. For example:
  \dd version
                       Object descriptions
     Schema   |  Name   |  Object  |        Description
  ------------+---------+----------+---------------------------
 pg_catalog | version | function | PostgreSQL version string
 
  \dD [PATTERN]  list domains
  \df [PATTERN]  list functions (add "+" for more detail)
  \dg [PATTERN]  list groups
  \dn [PATTERN]  list schemas (add "+" for more detail)
  \do [NAME]     list operators
  \dl            list large objects, same as \lo_list
  \dp [PATTERN]  list table, view, and sequence access privileges
  \dT [PATTERN]  list data types (add "+" for more detail)
  \du [PATTERN]  list users
  \l             list all databases (add "+" for more detail)
  \z [PATTERN]   list table, view, and sequence access privileges (same as \dp)

Formatting:-

  \a             toggle between unaligned and aligned output mode
  \C [STRING]    set table title, or unset if none
  \f [STRING]    show or set field separator for unaligned query output
  \H             toggle HTML output mode (currently off)
  \pset NAME [VALUE]
                 set table output option
                 (NAME := {format|border|expanded|fieldsep|footer|null|
                 recordsep|tuples_only|title|tableattr|pager})
  \t             show only rows (currently off)
  \T [STRING]    set HTML <table> tag attributes, or unset if none
  \x             toggle expanded output (currently off)

Copy, Large Object:-
  \copy ...      perform SQL COPY with data stream to the client host
  \lo_export LOBOID FILE
  \lo_import FILE [COMMENT]
  \lo_list
  \lo_unlink LOBOID    large object operations

ORA-00001: unique constraint (PERFSTAT.STATS$MUTEX_SLEEP_PK) violated

ORA-12012: error on auto execute of job 21
ORA-00001: unique constraint (PERFSTAT.STATS$MUTEX_SLEEP_PK) violated
ORA-06512: at "PERFSTAT.STATSPACK", line 5264
ORA-06512: at "PERFSTAT.STATSPACK", line 104
ORA-06512: at line 1

In this case unique constraint is violated because the job try to take a snapshot of same number which is already been taken or sometime it receives two  same id number  to process the snapshot which again violates the unique constraint.

NOTE: This is an oracle known bug.

View Metalink Note 382993.1 to get the low-down on this one, but the workaround is an easy implementation-

###### workaround or solution#########
Disable the constraint which is avoiding the job to create a snapshot

1)ALTER TABLE perfstat.stats$mutex_sleep disable CONSTRAINT STATS$MUTEX_SLEEP_PK;

Then create an index on this:-

2)CREATE INDEX perfstat.STATS$MUTEX_SLEEP_PK ON STATS$MUTEX_SLEEP(SNAP_ID,DBID,INSTANCE_NUMBER,MUTEX_TYPE,LOCATION);
after executing these two steps take an on demand snapshot just to make sure if the problem has been sorted out. If the the on demand snapshot completes successfully then it means the problem is sorted out
NOTE: constantly monitor alert log file while doing all these steps.

3) Exec statspack.snap;
 

DROP DATABASE in Oracle

Since 10g, It is possible to drop a database in Oracle 10g with the new SQL statement drop database.
The command gets rid of datafiles online redo log files, controlfiles and spfile.

 

ORA-16014: log 1 sequence# not archived, no available destinations

Error :--

ORA-16038: log 1 sequence# 93878 cannot be archived

ORA-19504: failed to create file ""

ORA-00312: online log 1 thread 1: '+DG_RED01/testprd/onlinelog/group_1.259.810739125'

ORA-00312: online log 1 thread 1: '+DG_RED02/testprd/onlinelog/group_1.259.810739127'

Fri Mar 14 10:55:10 2014

ARCH: Archival stopped, error occurred. Will continue retrying

ORACLE Instance TESTPRD - Archival Error

ORA-16014: log 1 sequence# 93878 not archived, no available destinations

ORA-00312: online log 1 thread 1: '+DG_RED01/testprd/onlinelog/group_1.259.810739125'

ORA-00312: online log 1 thread 1: '+DG_RED02/testprd/onlinelog/group_1.259.810739127'

Fri Mar 14 11:00:10 2014

Solution :--

Remove you old archive logs first. Reduce your mount point usage to below 90%, then check

Clean Archive from RMAN :-

1> check current sequence number:-

SQL> select max(sequence#),thread# from v$backup_redolog group by thread#;

MAX(SEQUENCE#)    THREAD#

-------------- ----------

         93875          1

2>Connect with RMAN
oracle@testprd$rman TARGET /
Recovery Manager: Release 11.2.0.2.0 - Production on Fri Mar 14 11:06:53 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database:TESTPRD

3> Check backup & Delete archive sequence (less then 100 from current sequence no). 

RMAN> delete archivelog until sequence 93675;

Clean Archive from System Disk :-

1>check archive location

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /disk1/archive
Oldest online log sequence     93885
Next log sequence to archive   93890
Current log sequence           93890

2> go to archive path
cd  /disk1/archive

3> Check backup & delete archive files

rm -rf archive_files.arc

ORA-09817: Write to audit file failed. ORA-09945: Unable to initialize the audit trail file

SQL> startup

 ---it show this error
 ORA-09817: Write to audit file failed.
 Linux-x86_64 Error: 28: No space left on device
 ORA-09945: Unable to initialize the audit trail file

Why :=
 ============
 its because of full the Mount point where Oracle is installed .
 your audit_trail_dest or audit trail destination is full for generation of audit file.
 you can do :-
 ==============

 1) check the space of mount point. like below.....
 $ df -k

 2) move the audit_file_dest parameter to another mount point
 using below command:
 alter system set audit_file_dest='new mount point /u02 /u03'
 scope=spfile;
 then restart the database but you need to make sure that there enough space on server before restart.

 or

 3) delete some unnecessary trace file or something from that fulled mount point.
 then try to start the oracle database.
 it is advisable, backup the files before they are delete since they might be useful in future.

fatal: librt.so.1: version `SUNW_1.2' not found

Answer:

You will need to set your environment variable, LD_NOVERSION=yes. For example, in bash:

/usr/local/> export LD_NOVERSION=yes

In csh:

/usr/local/> setenv LD_NOVERSION yes

The is is caused by an eventual incompatibility between the differing Java versions on the machine as well as the packages and patches that were applied to the machine. For now,
please use set the LD_NOVERSION environment variable to "yes".
 

SAN vs NAS - What Is the Difference?

Question: SAN vs NAS - What Is the Difference?
 
Answer:
 A NAS is a single storage device that operate on data files, while a SAN is a local network of multiple devices that operate on disk blocks.

 SAN vs NAS Technology:
 A SAN commonly utilizes Fibre Channel interconnects.
 A NAS typically makes Ethernet and TCP/IP connections.

Connect Oracle to MySQL Server through ODBC database link

I will setup 11gR2 Oracle gateway for ODBC to connect between a version 11.2.0.3 Oracle database and a version 5.5 MySQL database on different machines.. The Oracle database is on a 64-bit SunOS , while MySQL database is on a 64-bit SunOS  server. I will provide some tips and tricks along the way.
Unless specified, all the steps are performed on the Oracle gateway server.

Scope:- MySQL Connectors - Version 3.51 to 5.1 [Release 3.51 to 5.1]
Oracle Server - Enterprise Edition - Version 11.1.0.6 to 11.2.0.3 [Release 11.1 to 11.2]
Information in this document applies to any platform.
ODBC, UnixODBC, DG4ODBC, MySQL, ODBCINI, Database Link


Oracle database server uses DG4ODBC as the data gateway for connecting to non-Oracle RDBMS. This requires the proper setup for all components involved in transferring data from Oracle to MySQL and back. These components interact with each other in the following way:
+-----------------------------<ODBC Client Host>---------------------------+
| |
| [ORACLE] <---> [DG4ODBC] <---> [ODBC Driver Manager] <---> [ODBC Driver] |
| |
+--------------------------------------------------------------------------+
                     /|\
                       |
                 NETWORK
                        |
                      \|/
       +--<MySQL Server Host>--+
            |                       |
            | [MySQL Server] |
            |                       |
       +-----------------------+
In this article we will configure each component and diagnose problems that are most likely to be encountered during the setup process. NOTE: There are few articles in the internet that skip [ODBC Driver Manager] and connect directly [DG4ODBC] <- - -> [ODBC Driver]. It might work for some
drivers such as DataDirect or specific configurations, but beware that MySQL [ODBC Driver] (versions 3.51.x and 5.1.x) is not supposed to be loaded directly. The recommended driver manager is UnixODBC v.2.2.14 or newer. Configuring ODBC connections in 32-bit OS might be slightly easier than in 64-bit OS. The latter can execute 32 and 64-bit code and more attention must be paid to the components versions. In other words, when configuring ODBC you cannot mix 32-bit and 64-bit components within the Client Host. This is so because 32-bit binaries code can only load 32-bit binaries and 64-bit binaries can only load 64-bit binaries. [MySQL Server] is always independent because all communication with the driver is done through the network protocol. You have the choice to place [MySQL Server] on <ODBC Client Host> or on another physical host in the network <MySQL Server Host>. In any case, the bit depth of [MySQL Server]

Step 1:-
We shall start from determining if [Oracle] and [DG4ODBC] are 32/64-bits:             
$ file $ORACLE_HOME/bin/dg4odbc
/home/dbs/app/Ora/product/11.2.0/dbhome_1/bin/dg4odbc: ELF 64-bit LSB
executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), for GNU/Linux 2.6.9, not stripped

The above command output says that we must use 64-bit [ODBC Driver Manager] and 64-bit [ODBC Driver]

Step 2
Getting and installing [ODBC Driver Manager] UnixODBC 2.2.14.
If your OS provides UnixODBC 2.2.14 or newer update package you have to install it. Otherwise, go to UnixODBC Downloads at sourceforge:

http://sourceforge.net/projects/unixodbc/files/unixODBC/

Choose the version (2.2.14 is recommended).
+---------------------------------------------------------------------------+
| NOTE: RPM packages are not offered, so the package has to be installed |
| from tar.gz. This is good for few reasons: |
|                                                           |
| - No root access is required to do so        |
| - You can keep few different versions of UnixODBC at once |
+---------------------------------------------------------------------------+
If binary package for your OS is not available in the list, go to UnixODBC web site to find out how to build it from sources:
http://www.unixodbc.org/download.html
In this example we will get unixODBC-2.2.14-linux-x86-64.tar.gz:
http://sourceforge.net/projects/unixodbc/files/unixODBC/2.2.14/unixODBC-2.2.14-linux-x86-64.tar.gz/download
Download the package into the home directory (/home/dbs/).
Create a new directory for UnixODBC:
   $ mkdir ~/app/unixodbc-2.2.14
   $ cd ~/app/unixodbc-2.2.14

Unpack the package into the newly created directory:
   $ gunzip -c ~/unixODBC-2.2.14-linux-x86-64.tar.gz | tar xvf -
Latest UnixODBC packages put lib and bin directories in /usr/local inside the
package, so we need to move them to the upper level for convenience
   $ mv ~/app/usr/local/* .
   $ rm -r usr
Check the directory contents:
$ ls -l
drwxrwxr-x 2 dbs dbs 4096 Nov 20 2008 bin
drwxrwxr-x 2 dbs dbs 4096 Nov 20 2008 include
drwxrwxr-x 2 dbs dbs 4096 Nov 20 2008 lib
later we will have to set LD_LIBRARY_PATH to point to /home/dbs/app/unixodbc-2.2.14/lib

Step 3
Getting and installing [ODBC Driver].
Similar to UnixODBC, it is possible to have several different versions of MySQL Connector/ODBC driver. Installing the driver from tar.gz package does not require root privileges and allows installing the driver at custom locations, so we will do so.
More details about installing MySQL Connector/ODBC can be found here:
http://dev.mysql.com/doc/refman/5.5/en/connector-odbc-installation-binary-unix.html

Download the tar.gz package from the following page: 

http://www.mysql.com/downloads/connector/odbc/5.1.htmlUnpack 

the driver into ~/app director

$ cd ~/app$ gunzip -c mysql-connector-odbc-5.1.8-linux-glibc2.3-x86-64bit.tar.gz | tar xvf -

This command creates mysql-connector-odbc-5.1.8-linux-glibc2.3-x86-64bit directory and extracts all needed files in it. Create a symbolic link with a shorter name:

$ ln -s mysql-connector-odbc-5.1.8-linux-glibc2.3-x86-64bit myodbc-5.1.8

Step 4

Configuring ODBC data source for MySQL Connector/ODBC driver is described here:

http://dev.mysql.com/doc/refman/5.5/en/connector-odbc-configuration-dsn-unix.html

So, we will create odbc.ini file in ~/etc:

[myodbc5]

   Driver = /home/dbs/app/myodbc-5.1.8/lib/libmyodbc5.so

   Description = Connector/ODBC 5.1 Driver DSN

   SERVER = 10.0.0.1

   PORT = 3306

   USER = mysql_user

   PASSWORD = *****

   DATABASE = test

   OPTION = 0

   TRACE = OFF

Exp:--

more /export/home/mysql-connector/odbc.ini

[myodbc5]

driver=/export/home/mysql-connector/mysql-connector-odbc-5.2.5-solaris10-sparc-64bit/lib/libmyodbc5w.so

server=10.0.0.0

port=3306

user=abcd

password=adbc123

database=test

option=0

trace=OFF

Step 5 (Optional)
Verifying the ODBC connection using isql command line.
$ export ODBCINI=/home/dbs/etc/odbc.ini
$ export LD_LIBRARY_PATH=/home/dbs/app/unixodbc-2.2.14/lib:$LD_LIBRARY_PATH
$ cd ~/app/unixodbc-2.2.14/bin/
$ ./isql myodbc5 -v
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
The above output will display if connection has been established successfully. Next, trying to send a simple query to list tables in the database:
SQL> show tables;
+-----------------------------------------------------------------+
| Tables_in_test |
+-----------------------------------------------------------------+
| tab1 |
| tab2 |
+-----------------------------------------------------------------+
SQLRowCount returns 2
2 rows fetched

That was the good scenario when everything went smoothly. However, you might get the following errors:
Error 1:
[IM002][unixODBC][Driver Manager]Data source name not found,
no default driver specified
ISQL]ERROR: Could not SQLConnect
error usually comes if ODBCINI variable is not pointing to the correct
odbc.ini file. To fix this error try:
$ cat $ODBCINI
The command should display the contents of odbc.ini file with all settings we have configured on Step 4. If the file is there and the same error comes again, check the data source name. The parameter name for isql must be exactly the same as the section name in odbc.ini file.

Error 2:
./isql: error while loading shared libraries: libodbc.so.1: cannot open shared object file: No such file or directory This error means that LD_LIBRARY_PATH is set wrong and the linker cannot find the main UnixODBC [Driver Manager] library libodbc.so. The solution is to export the directory containing libodbc.so ito LD_LIBRARY_PATH env variable
as shown at the beginning of Step 5.
The following command must not show failing dependencies:
$ ldd isql
linux-vdso.so.1 => (0x00007fffe4ffc000)
libodbc.so.1 => /home/dbs/app/unixodbc-2.2.14/lib/libodbc.so.1
(0x00002ae5263e8000)
libdl.so.2 => /lib64/libdl.so.2 (0x00000036b1c00000)
libreadline.so.5 => /usr/lib64/libreadline.so.5 (0x00000036b1000000)
libncurses.so.5 => /usr/lib64/libncurses.so.5 (0x00000036c4400000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00000036b2000000)
libc.so.6 => /lib64/libc.so.6 (0x00000036b1400000)
/lib64/ld-linux-x86-64.so.2 (0x00000036b0c00000)

Error 3:
[S1000][unixODBC][MySQL][ODBC 5.1 Driver]Access denied for user 'dbs'@'%' (using password: YES)
[ISQL]ERROR: Could not SQLConnect
In some cases this error message is not obvious. The user name and password might be correct and mysql command line might work perfectly with the user name and password specified in odbc.ini file. Check the database name in odbc.ini file (DATABASE parameter). As explained in the note on Step 4, the database names are case sensitive in MySQL and the connection might be rejected because the database does not exist or user has no privileges to access a database with this name.
Checking the connectivity from mysql command line is a good idea too.
+---------------------------------------------------------------------------+
| NOTE: mysql command line does not use ODBC, so it might work even if ODBC |
| fails. The idea of this check is to make sure we provided the correct                |
| connection credentials to ODBC driver.                                                         |
+---------------------------------------------------------------------------+
mysql command line should use EXACTLY the same user name, host, password, port and be executed on the same host with [Oracle] and [DG4ODBC]:
$ mysql "user=mysql_user "password=****** --host=10.0.0.1 --port=3306
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.52-community-log MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+------------------------+
| Database |
+------------------------+
| information_schema |
| entitytest |
| mysql |
| test |
| test2 |
| test_db |
+------------------------+
6 rows in set (0.07 sec)

Step 6:
Configuring tnsnames.ora.
Add the following lines to $ORACLE_HOME/network/admin/tnsnames.ora
myodbc5 =
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP) (HOST=localhost) (PORT=1521)
)
(CONNECT_DATA=
(SID=myodbc5))
(HS=OK)
)

Exp:-- 
myodbc5=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test)(PORT=1521))(CONNECT_DATA=(SID=myodbc5))(HS=OK))

Step 7:
Configuring listener.ora
Open $ORACLE_HOME/network/admin/listener.ora in a text editor, find
SID_LIST_LISTENER definition and add a new entry for myodbc5:
SID_LIST_LISTENER=
(SID_LIST=
# Another entry in the list
# (SID_DESC=
# (SID_NAME=sampleDSN)
# (ORACLE_HOME=/home/dbs/app/Ora/product/11.2.0/dbhome_1)
# (PROGRAM=dg4odbc)
# (ENVS=LD_LIBRARY_PATH=/home/dbs/app/unixodbc-2.2.14)
# )
(SID_DESC=
(SID_NAME=myodbc5)
(ORACLE_HOME=/home/dbs/app/Ora/product/11.2.0/dbhome_1)
(PROGRAM=dg4odbc)
(ENVS=LD_LIBRARY_PATH=/home/dbs/app/unixodbc-2.2.14/lib:/home/dbs/app/Ora/product/11.2.0/dbhome_1/lib)
)
)
We strongly recommend to add the LD_LIBRARY_PATH to the listener.ora file to avoid any conflicts with already existing ODBC driver managers.
The LD_LIBRARY_PATH must contain the fully qualified path to the $ORACLE_HOME/lib and also the library paths of the ODBC driver manager and the ODBC driver itself.

Step 8:

Configuring gateway init<sid>.ora file.

This file does not exist and you have to create it.

In listener.ora, tnsnames.ora we use the name myodbc5 just for convenience,

but it can be literally anything. The init file (initmyodbc5.ora) is a

different story because HS_FDS_CONNECT_INFO is the DSN name in odbc.ini.

$ vi $ORACLE_HOME/hs/admin/initmyodbc5.ora

HS_FDS_CONNECT_INFO=myodbc5 # Data source name in odbc.ini

# HS_FDS_TRACE_LEVEL=user

HS_FDS_SHAREABLE_NAME=/home/dbs/app/unixodbc-2.2.14/lib/libodbc.so

HS_FDS_SUPPORT_STATISTICS=FALSE

HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15

#

# ODBC env variables

set ODBCINI=/home/dbs/etc/odbc.ini

+---------------------------------------------------------------------------+

NOTE: HS_FDS_SHAREABLE_NAME must point to the [ODBC Driver Manager] library. It is an error to put there the [ODBC Driver] library. The [ODBC Driver] DSN is referenced in HS_FDS_CONNECT_INFO=myodbc5.

+---------------------------------------------------------------------------+

+---------------------------------------------------------------------------+

| NOTE: UnixODBC [ODBC Driver Manager] might not support any character set |

| used in Oracle, so the connection string will be corrupted. |

| To avoid the connection string corruption it is recommended to set |

| HS_LANGUAGE parameter. I the present case AMERICAN_AMERICA.WE8ISO8859P15 |

| worked ok |

+---------------------------------------------------------------------------+

Step 9

Applying the settings in the configuration files.

 The listeners must be restarted in order to pick up the changes we just made into tnsnames.ora, listener.ora and initmyodbc5.ora:

$ lsnrctl stop

$ lsnrctl start

+---------------------------------------------------------------------------+

NOTE: The start status has to contain information about the service:

Services Summary...

Service "myodbc5" has 1 instance(s).

Instance "myodbc5", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

 

EXP:- lsnrctl start listener_test

Step 10 (Optional)
Checking the service status:

$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 06-MAY-2011 19:42:10
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 06-MAY-2011 19:41:39
Uptime 0 days 0 hr. 0 min. 30 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/dbs/app/Ora/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /home/dbs/app/Ora/diag/tnslsnr/dbs-pc/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))
Services Summary...
Service "myodbc5" has 1 instance(s).
Instance "myodbc5", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
The command prints the status of "myodbc5" service. UNKNOWN is not a problem
in this case because we have not tried using the service yet.
Pinging:
$ tnsping myodbc5
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on
06-MAY-2011 19:42:22
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=localhost)
(PORT=1521)) (CONNECT_DATA= (SID=myodbc5)) (HS=OK))
OK (10 msec)
If the above commands report errors check thsnames.ora and listener.ora and set them as shown on Step 6-7.

Step 11:
Creating the database link and getting the data.

Start sqlplus and type the following command (user and password are the same as in odbc.ini):
SQL> create public database link myodbc5 connect to "mysql_user" identified by
"********" using 'myodbc5';
Database link created.
SQL> select * from "tab1"@myodbc5;
id
----------
txt1
1
some text
2
some more text
DONE!

Exp:-- 
create public database link myodbc5 connect to "mysql_user" identified by "********" using 'myodbc5';

+---------------------------------------------------------------------------+
NOTE: When selecting data from MySQL linked table it is recommended to enclose the table name into double quotes as "tab1" unless MySQL Server is set to ANSI_QUOTES |
+---------------------------------------------------------------------------+

Note: because MySQL is case sensitive, make sure to use double quotes on username and password. Otherwise, you may get following error:

ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[unixODBC][MySQL][ODBC 5.1 Driver]Access denied for user
'USER1'@'XXXX' (using password: YES) {HY000,NativeErr = 1045}
ORA-02063: preceding 2 lines from MYSQLMYDB