Re: A special way of migration
- From: scott.hutchinson@xxxxxxxxxxxxxxxxxxxxx
- To: jost@xxxxxxxxxxxx
- Date: Tue, 27 Sep 2005 17:13:19 +0100
Joerg,
I think you're on the right track. I would never never use export/import for
large volumes of data. Keep it for maintaining small volume of reference data.
We regularly have to move 1TB+ between various databases and I do this using
CREATE TABLE .. AS SELECT. You can parallelise the SELECT, and the CREATE, but
you can not parallelise the DB Link transfer, so this is your slowest point by
far. (It can be parallelised by dividing each table into ROWID ranges, but
this gets a bit complicated...). We copy about 1TB from remote servers and
index it in 5 hours.
You can create the table NOLOGGING, and then build the indexes (in parallel +
nologging) AFTER the data has been copied. All things that Export prevents you
from doing.
btw - if you're Oracle 10 there is the data pump...
Good luck,
Scott Hutchinson
Interact Analysis Ltd.
Quoting jost@xxxxxxxxxxxx:
>
>
>
>
>
> Hi list,
>
> i have an unusal question i think, but i am curios about your meaning about
> this.
>
> I am often in the situation to get a production database from an old
> db-server
> to a new one. In some cases i am also instructed to lift up the Oracle
> Version. Naturally i use export/import to get all the data of our
> application
> into the new database.
>
> Sometimes the databases are quite big (about 20 to 200 Gig), so the Import
> and
> some other stuff lasts long. The consequence is, of course, to do it at
> weekends or after buisness hours.
>
> So far so good. There is no problem so far, but i was thinking about
> another
> solution for this task.(maybe i am just lazy? ;-)
>
> Here is my idea.
>
> Write a package to get the application data via a database link into the
> new
> database. The easiest way is "create table ... as select * from
> ...@db_link"
>
> This is very fast, as my first tests have shown.
>
> After that, i get all Indexes via some dynamic SQL, the same with all sorts
> of
> constraints. At last i get all defaults for the table columns also via
> dynamic SQL
>
> The advantage for me is, i can start one procedure, enjoy my weekend and
> just
> have to look for problems after everything is done. It seems to be safe for
> the old production database, so, in worst case, we just have to cancel the
> installation of the new db-server, but the production database is still
> available.
>
> What did i miss? Where are the pitfalls?
>
> I started to write the package. It is working inside a test - scenario with
> a
> small database.
>
> Some things are still missing. Views are not implemented now, also Triggers
> are not ready yet. Packages of the application are no problem, because they
> are part of the installation of the application so i can install it after
> the
> process is done. if they are compiling without errors, it is also a good
> sign
> for the whole "migration" of the data because of the dependencies.
> Sequences are also not totally ready now.
>
> So, what is your opinion?
>
> Thx in advance
>
> Joerg
>
> --
> //www.freelists.org/webpage/oracle-l
>
::This message sent using the free Web Mail service from http://TheName.co.uk
--
//www.freelists.org/webpage/oracle-l
Other related posts:
