Idea for database migration using parallelism at the table level

From: Vincent G. Poore <vincepoore_at_excite.com>
Date: 17 Mar 2004 15:15:34 -0800
Message-ID: <9f69adb7.0403171515.2e631ebf@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 Wed Mar 17 2004 - 17:15:34 CST