Re: Idea for database migration using parallelism at the table level

From: koert54 <nospam_at_spam.com>
Date: Thu, 18 Mar 2004 10:45:04 GMT
Message-ID: <Q4f6c.10918688$Id.1831959@news.easynews.com>

I think you make it too complex - deal with the data & indexes first - do everything else last
I would :

- precreate users/tables
- parallel import data (just the data)
- break up indexfile -> parallel create indexes
- full import (no data, no index, all the rest - to enable constrants,

grants, views, etc)

maybe http://pepi.sourceforge.net is something for you ?

"Vincent G. Poore" <vincepoore_at_excite.com> wrote in message news:9f69adb7.0403171515.2e631ebf_at_posting.google.com...
> The Oracle 9i Database Utilities document contains a section
> entitled How to Use Export and Import to Partition a Database
> Migration. It describes a method to break up the migration into
> parallel imports of schemas. Unfortunately for me, that won't
> work because most of my databases don't have equally sized
> schemas. There's usually one schema that's bigger than all the
> others combined by far. What I need is a method to break up the
> migration into parallel imports of tables. I've thought this out
> and come up with the following strategy. I would greatly
> appreciate it if readers would review it and see if there are any
> holes in this strategy.
>
> Note: Where exp is piped into imp, the necessary details to
> achieve this via named pipes are omitted, but hopefully you
> understand the intention.
>
> Phase 1 - Migrate meta-data
> =================================================================
> exp FULL=y ROWS=n INDEXES=n CONSTRAINTS=n TRIGGERS=n|imp FULL=y \
> ROWS=n
> exp FILE=postActions.dmp FULL=y ROWS=n INDEXES=y CONSTRAINTS=y \
> TRIGGERS=y
>
> Each of the following phases will use a script to generate the table
> list and then spawn N processes to process the list in parallel.
>
> Phase 2 - Tables that are fkey parents only (data, indexes,
> contraints)
> =================================================================
> exp TABLES=(schemaname.tablename:partitionname) ROWS=y \
> INDEXES=y CONSTRAINTS=y|imp FROMUSER=schemaname \
> TABLES=(tablename) IGNORE=y
>
> Phase 3 - Tables that are fkey parents and children (just the
> data, to avoid constraint conflicts)
> =================================================================
> exp TABLES=(schemaname.tablename:partitionname) ROWS=y \
> INDEXES=n CONSTRAINTS=n|imp FROMUSER=schemaname \
> TABLES=(tablename) IGNORE=y
>
> Phase 4 - Tables that are fkey parents and children (just the
> indexes, so constraints in next phase work)
> =================================================================
> imp FILE=postActions.dmp FROMUSER=schemaname TABLES=(tablename) \
> ROWS=n INDEXES=y CONSTRAINTS=n IGNORE=Y
>
> Phase 5 - Tables that are fkey parents and children (just the
> constraints)
> =================================================================
> imp FILE=postActions.dmp FROMUSER=schemaname TABLES=(tablename) \
> ROWS=n INDEXES=n CONSTRAINTS=y IGNORE=Y
>
> Phase 6 - Tables that are not fkey parents (data, indexes,
> constraints)
> =================================================================
> exp TABLES=(schemaname.tablename:partitionname) ROWS=y \
> INDEXES=y CONSTRAINTS=y|imp FROMUSER=schemaname \
> TABLES=(tablename) IGNORE=y
>
> Phase 7 - Non-Table Triggers (anything else missed)
> =================================================================
> imp FILE=postActions.dmp FULL=y ROWS=n INDEXES=y CONSTRAINTS=y \
> TRIGGERS=y IGNORE=y
>
> Appendix A - SQL to determine table order
> =================================================================
> SELECT
> CASE
> WHEN p.parent_of > 0 THEN
> CASE
> WHEN c.child_of > 0 THEN
> 2
> ELSE
> 1
> END
> ELSE
> 3
> END table_order
> ,x.total_bytes / 1024 extents_kb
> ,t.owner
> ,t.table_name
> ,t.partition_name
> ,p.parent_of
> ,c.child_of
> FROM
> (
> SELECT
> t.owner
> ,t.table_name
> ,NVL(p.partition_name,' ') partition_name
> FROM
> sys.dba_tables t
> ,sys.dba_tab_partitions p
> WHERE
> NOT t.owner = 'SYS'
> AND t.owner = p.table_owner(+)
> AND t.table_name = p.table_name(+)
> ) t
> ,(
> SELECT
> owner
> ,segment_name
> ,NVL(partition_name,' ') partition_name
> ,SUM(bytes) total_bytes
> FROM
> sys.dba_extents
> WHERE
> NOT owner = 'SYS'
> GROUP BY
> owner
> ,segment_name
> ,partition_name
> ) x
> ,(
> SELECT
> p.owner
> ,p.table_name
> ,COUNT(*) parent_of
> FROM
> sys.dba_constraints p
> ,sys.dba_constraints c
> WHERE
> NOT p.owner = 'SYS'
> AND p.constraint_name = c.r_constraint_name
> AND c.constraint_type = 'R'
> GROUP BY
> p.owner
> ,p.table_name
> ) p
> ,(
> SELECT
> c.owner
> ,c.table_name
> ,COUNT(*) child_of
> FROM
> sys.dba_constraints c
> WHERE
> NOT c.owner = 'SYS'
> AND c.constraint_type = 'R'
> GROUP BY
> c.owner
> ,c.table_name
> ) c
> WHERE
> t.owner = x.owner
> AND t.table_name = x.segment_name
> AND t.partition_name = x.partition_name
> AND t.owner = p.owner(+)
> AND t.table_name = p.table_name(+)
> AND t.owner = c.owner(+)
> AND t.table_name = c.table_name(+)
> ORDER BY
> 1
> ,2 DESC
> ,3
> ,4
> ;
Received on Thu Mar 18 2004 - 04:45:04 CST