From: Ananda Kumar
Date: September 20 2005 3:18pm
Subject: Re: database link
List-Archive: http://lists.mysql.com/mysql/189317
Message-Id: <829b199c05092008185c0cb0ee@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative; 
	boundary="----=_Part_3989_29466498.1127229486616"

------=_Part_3989_29466498.1127229486616
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

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:=20
>=20
> 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=
=20
> database which runs 24*78 and during night time it runs lot of other jobs=
 i=20
> cannot make the reporting job run from this oracle database.=20
>  regards
> anandkl
>=20
>  On 9/16/05, Mark Leith <mark@stripped> wrote: =20
> >=20
> > You may want to think about doing this the opposite way around also, an=
d
> > look at pushing the data from Oracle in to MySQL. Oracle has something=
=20
> > called "heterogeneous services", which allows you to define ODBC=20
> > datasources
> > as valid entries in the tnsnames.ora file. Then you could simply create=
=20
> > a
> > job in Oracle that executes a procedure to do the entire process=20
> > (truncate /=20
> > load), no external scripting necessary..
> >=20
> > Here's a quick example of what to do:
> >=20
> > First set up an ODBC data source for your MySQL database, using MyODBC.
> >=20
> > Create a file in ORACLE_HOME/hs/admin called initMySQL.ora. In this fil=
e=20
> > put
> > the following options:
> >=20
> > HS_FDS_CONNECT_INFO =3D MySQL5 #ODBC DSN /* Replace MySQL5 with your DS=
N=20
> > */
> > HS_FDS_TRACE_LEVEL =3D OFF
> >=20
> > Alter your listener.ora file (ORACLE_HOME/network/admin) to add the=20
> > following:
> >=20
> > (SID_DESC =3D
> > (PROGRAM =3D hsodbc)
> > (ORACLE_HOME =3D oracle/product/92) /* Your ORACLE_HOME */
> > (SID_NAME =3D MySQL5) /* Your DSN */
> > )
> >=20
> > Add the following to your tnsnames.ora file:
> >=20
> > MYSQL5 =3D
> > (DESCRIPTION=3D
> > (ADDRESS=3D(PROTOCOL=3Dtcp)(HOST=3Dlocalhost)(PORT=3D1521))
> > (CONNECT_DATA=3D(SID=3DMYSQL5))
> > (HS=3DOK)
> > )
> >=20
> > Reload your Oracle listener (lsnrctl reload), and then connect to the=
=20
> > Oracle=20
> > database. To set the database link up:
> >=20
> > CREATE DATABASE LINK mysql5 CONNECT TO "user" identified by "password"=
=20
> > using
> > 'mysql5';
> >=20
> > User and password should be a valid user within MySQL, that can connect=
=20
> > from=20
> > the Oracle host. You should be set to go from there. Here's a quick=20
> > 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):
> >=20
> > SQL> select count(*) from film@mysql5;
> >=20
> > COUNT(*)
> > ----------
> > 1000
> >=20
> > SQL> desc film@mysql5;
> > Name Null? Type=20
> > ----------------------------------------- --------
> > ----------------------------
> > film_id NUMBER(10)
> > category_id NOT NULL NUMBER(10)
> > title NOT NULL VARCHAR2(27)=20
> > description LONG
> > rental_duration NOT NULL NUMBER(3)
> > length NUMBER(10)
> > rating CHAR(5)=20
> >=20
> > SQL> insert into film@mysql5 values (1000000,1,'test','test',1,1,'PG');
> >=20
> > 1 row created.
> >=20
> > ---change prompts---
> >=20
> > mysql> use sakila
> > Database changed
> > mysql> select max(film_id) from film;=20
> > +--------------+
> > | max(film_id) |
> > +--------------+
> > | 1000000 |
> > +--------------+
> > 1 row in set (0.01 sec)
> >=20
> > HTH
> >=20
> > Mark Leith
> >=20
> > --
> > No virus found in this outgoing message.
> > Checked by AVG Anti-Virus.=20
> > Version: 7.0.344 / Virus Database: 267.11.0/103 - Release Date:=20
> > 15/09/2005
> >=20
> >=20
> >=20
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/mysql?unsub=3Danandkl@stripped
> >=20
> >=20
>

------=_Part_3989_29466498.1127229486616--