Re: Transferring a DB to a new platform WITHOUT export/import

On Wed, 14 May 2003 00:30:07 +0200, Rick Denoire wrote:

> Going from Oracle 8.1.7/Solaris/Sun E3500 to
> Intel-Industry-Standard-Hardware/Linux, I will by no means use
> export/import: It takes too long for a 100 GB DB. Downtime should be
> minimal.
>
> So my intention is to transfer data through the network via a DB link.
> Of course, it is simple to say "create table xxx as select ..." but
> you need to know which tables are there in the first place. And
> indexes, triggers, packages, users, roles, profiles etc.
>
> And since this is a very volatile DB (its structure changes very
> quickly), I must be able to determine the structure of the source DB
> immediately prior to transfer time. This is an approximate plan to
> transfer the DB:
>
> a) Shutdown and start the source DB in readonly mode
> b) reproduce its structure on the target platform
> c) fire data transfer based on the objects found (no assumptions made)
> d) make some kind of plausibility test about success or failure
> e) shutdown the source DB
> f) set the passwords of users (if not transferred)
> f) reconfigure Oracle Names to serve the new DB using the old
> connection string
>
> and that should it be. This assumes that at destination a DB exists
> with at least adequate tablespaces. The main point here is that
> everything created in the target DB should be based on what is found
> in the source DB, so some script would select objects from all_tables,
> all_indexes, etc.
>
> I would pleasantly avoid rediscovering all getchas involved in this
> process. If there is some script out there to do this, I would very
> much appreciate a pointer, or a hint about white papers,
> recommendations, etc. I don't expect a ready solution (I will have to
> exercise this in a test environment anyway), but I would like to *save
> time* avoiding getting into any trouble. I am doing this for the 1st
> time this way (this time the target host is a different *platform* as
> well as a different Oracle version).
>
> Thanks a lot
> Rick Denoire

If you need just the database structure, the best and the fastest thing around is DataBee. Their (free) DBATool is a phenomenal thing that can extract data structure from an export file created with "ROWS=NO" argument. For the Linux fans like me, it runs flawlessly under wine. I was able to get all the DDL for a 1.1 TB database with 1500+ tables within 30 minutes. And no, I do not work for them. Go and wisit http://www.databee.com. You will not regret it.

-- 
Mladen Gogala
Software is like sex, it is better when it is free.
Linus Torvalds