From: Ananda Kumar
Date: September 19 2005 7:14am
Subject: Re: database link
List-Archive: http://lists.mysql.com/mysql/189250
Message-Id: <829b199c050919001431efd778@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative; 
	boundary="----=_Part_13698_3419232.1127114080607"

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

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

 On 9/16/05, Mark Leith <mark@stripped> wrote:=20
>=20
> 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=20
> 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 (truncat=
e=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 file=
=20
> put
> the following options:
>=20
> HS_FDS_CONNECT_INFO =3D MySQL5 #ODBC DSN /* Replace MySQL5 with your DSN =
*/
> HS_FDS_TRACE_LEVEL =3D OFF
>=20
> Alter your listener.ora file (ORACLE_HOME/network/admin) to add the
> 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
> 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
> 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
> ----------------------------------------- --------
> ----------------------------
> 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)
>=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;
> +--------------+
> | 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.
> Version: 7.0.344 / Virus Database: 267.11.0/103 - Release Date: 15/09/200=
5
>=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
>

------=_Part_13698_3419232.1127114080607--