Transferring a DB to a new platform WITHOUT export/import

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:

1. Shutdown and start the source DB in readonly mode
2. reproduce its structure on the target platform
3. fire data transfer based on the objects found (no assumptions made)
4. make some kind of plausibility test about success or failure
5. shutdown the source DB
6. set the passwords of users (if not transferred)
7. 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 Received on Tue May 13 2003 - 17:30:07 CDT