A special way of migration

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 ..._at_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

--
http://www.freelists.org/webpage/oracle-l