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

Hi!

You could export/import your db structure with exp rows=n before you ever take down your production system.
During downtime you can find changed objects using last_ddl_time column in dba_objects. Of course if you want to find which objects have been dropped in source db since last export you have to do a join on source and target's dba_objects..

100GB db isn't that big, that you couldn't use exp-imp for doing the migration. If you still don't have the storage for expfile, you could export to a pipe, rsh to a pipe on different server and import from there. Test it first.

Since you already have created the structure, you don't have to specify any tablespaces or other parameters during your scripts, you just issue insert statements that select over dblink. I've used this script:

set linesize 300

set pagesize 0

set feedback off

set trimspool on

spool /tmp/apps_insert.sql

prompt alter session enable parallel dml;;

select 'insert /*+ APPEND PARALLEL(' || table_name || ',4) NOLOGGING */ into '

   || owner || '.' || table_name

   || ' select * from ' || owner || '.' || table_name || '@dblink;'

from dba_tables

where owner in ('AP', 'APPLSYS', 'APPS', 'AR', 'GL', 'JE')

order by owner;

spool off

exit

So I'd do the task following way:

1. exp file=structure.dmp rows=n from source
2. imp file=structure.dmp rows=n indexes=n constraints=n to target
3. transfer data (either dblink, exp in direct mode, or both) when exporting in direct mode, set recordlength to 64kb, when exp/imp in conventional mode, set buffer high)
4. get index definitions from structure.dmp using indexfile
5. modify indexfile to include nologging and parallel attributes
6. run indexfile
7. imp file=structure.dmp rows=n indexes=n constraints=y to enable constraints

Cheers,
Tanel.

"Rick Denoire" <100.17706_at_germanynet.de> wrote in message news:36r2cvgs1jgbghupl0blkle8mg8u3ro841_at_4ax.com...
> 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
>
Received on Wed May 14 2003 - 09:42:39 CDT