List:General Discussion« Previous MessageNext Message »
From:Ananda Kumar Date:September 20 2005 3:18pm
Subject:Re: database link
View as plain text  
Hi Mark,
First set up an ODBC data source for your MySQL database, using MyODBC.
 How do i do this.
 regards
anandkl
 On 9/19/05, Ananda Kumar <anandkl@stripped> wrote: 
> 
> Hi Mark,
> Thanks for the email. Yes i also read about this.
> But the problem with this approach is since our oracle db is a production 
> database which runs 24*78 and during night time it runs lot of other jobs i 
> cannot make the reporting job run from this oracle database. 
>  regards
> anandkl
> 
>  On 9/16/05, Mark Leith <mark@stripped> wrote:  
> > 
> > You may want to think about doing this the opposite way around also, and
> > look at pushing the data from Oracle in to MySQL. Oracle has something 
> > called "heterogeneous services", which allows you to define ODBC 
> > datasources
> > as valid entries in the tnsnames.ora file. Then you could simply create 
> > a
> > job in Oracle that executes a procedure to do the entire process 
> > (truncate / 
> > load), no external scripting necessary..
> > 
> > Here's a quick example of what to do:
> > 
> > First set up an ODBC data source for your MySQL database, using MyODBC.
> > 
> > Create a file in ORACLE_HOME/hs/admin called initMySQL.ora. In this file 
> > put
> > the following options:
> > 
> > HS_FDS_CONNECT_INFO = MySQL5 #ODBC DSN /* Replace MySQL5 with your DSN 
> > */
> > HS_FDS_TRACE_LEVEL = OFF
> > 
> > Alter your listener.ora file (ORACLE_HOME/network/admin) to add the 
> > following:
> > 
> > (SID_DESC =
> > (PROGRAM = hsodbc)
> > (ORACLE_HOME = oracle/product/92) /* Your ORACLE_HOME */
> > (SID_NAME = MySQL5) /* Your DSN */
> > )
> > 
> > Add the following to your tnsnames.ora file:
> > 
> > MYSQL5 =
> > (DESCRIPTION=
> > (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
> > (CONNECT_DATA=(SID=MYSQL5))
> > (HS=OK)
> > )
> > 
> > Reload your Oracle listener (lsnrctl reload), and then connect to the 
> > Oracle 
> > database. To set the database link up:
> > 
> > CREATE DATABASE LINK mysql5 CONNECT TO "user" identified by "password" 
> > using
> > 'mysql5';
> > 
> > User and password should be a valid user within MySQL, that can connect 
> > from 
> > the Oracle host. You should be set to go from there. Here's a quick 
> > example
> > of this working, to a MySQL 5 database using the new "sakila" sample
> > database that Mike Hillyer recently released
> > ( http://www.openwin.org/mike/download/sakila.zip):
> > 
> > SQL> select count(*) from film@mysql5;
> > 
> > COUNT(*)
> > ----------
> > 1000
> > 
> > SQL> desc film@mysql5;
> > Name Null? Type 
> > ----------------------------------------- --------
> > ----------------------------
> > film_id NUMBER(10)
> > category_id NOT NULL NUMBER(10)
> > title NOT NULL VARCHAR2(27) 
> > description LONG
> > rental_duration NOT NULL NUMBER(3)
> > length NUMBER(10)
> > rating CHAR(5) 
> > 
> > SQL> insert into film@mysql5 values (1000000,1,'test','test',1,1,'PG');
> > 
> > 1 row created.
> > 
> > ---change prompts---
> > 
> > mysql> use sakila
> > Database changed
> > mysql> select max(film_id) from film; 
> > +--------------+
> > | max(film_id) |
> > +--------------+
> > | 1000000 |
> > +--------------+
> > 1 row in set (0.01 sec)
> > 
> > HTH
> > 
> > Mark Leith
> > 
> > --
> > No virus found in this outgoing message.
> > Checked by AVG Anti-Virus. 
> > Version: 7.0.344 / Virus Database: 267.11.0/103 - Release Date: 
> > 15/09/2005
> > 
> > 
> > 
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/mysql?unsub=1
> > 
> > 
>

Thread
database linkAnanda Kumar15 Sep
  • Re: database linkSGreen15 Sep
RE: database linkMikhail Berman15 Sep
Re: database linkSGreen15 Sep
  • RE: database linkMark Leith16 Sep
    • Re: database linkAnanda Kumar19 Sep
    • Re: database linkAnanda Kumar19 Sep
      • Re: database linkAnanda Kumar20 Sep
Re: database linkSGreen15 Sep