数据库迁移之数据泵实验

environment

source host：
os system:server2008x64 
oracle version:10.0.2.0.5 32bit

destination host:
os system:server2008x64
oracle version:10.0.2.0.5 64bit

对数据库中的表进行拆分导出导入，减少停机时间，首先将表比较大而不变化的数据先导出，导入，最好在导出变化的数据

source host:
C:\Users\Administrator>sqlplus /@orcl as sysdba

SQL> create tablespace loneboo datafile 'D:\oracle\product\10.2.0\oradata\orcl\loneboo01.dbf' size 200m;

表空间已创建。

SQL> create user loneboo identified by loneboo default tablespace loneboo temporary tablespace temp;

用户已创建。

SQL> grant connect,dba to loneboo;

SQL> exit

tables: A partition table
B partition blob table
C 

create table A
(
  ID         NVARCHAR2(10) not null,
  CREATEDATE DATE,
  NAME       NVARCHAR2(20)
)
partition by range (CREATEDATE)
(
  partition R_CREATE1501 values less than (TO_DATE(' 2015-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace LONEBOO
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition R_CREATE1502 values less than (TO_DATE(' 2015-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace LONEBOO
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition R_CREATE1503 values less than (TO_DATE(' 2015-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace LONEBOO
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition R_CREATE1504 values less than (TO_DATE(' 2015-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace LONEBOO
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition R_CREATE1505 values less than (TO_DATE(' 2015-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace LONEBOO
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition R_CREATE1506 values less than (TO_DATE(' 2015-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace LONEBOO
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    )
)
;
-- Create/Recreate primary, unique and foreign key constraints 
alter table A
  add constraint PK_A_ID primary key (ID)
  using index 
  tablespace LONEBOO
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );






-- Create table
create table B
(
  ID         NVARCHAR2(10) not null,
  CREATEDATE DATE,
  NAME       NVARCHAR2(20),
  DATA     BLOB
  
)
partition by range (CREATEDATE)
(
  partition R_CREATE1501 values less than (TO_DATE(' 2015-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace LONEBOO
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition R_CREATE1502 values less than (TO_DATE(' 2015-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace LONEBOO
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition R_CREATE1503 values less than (TO_DATE(' 2015-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace LONEBOO
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition R_CREATE1504 values less than (TO_DATE(' 2015-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace LONEBOO
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition R_CREATE1505 values less than (TO_DATE(' 2015-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace LONEBOO
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition R_CREATE1506 values less than (TO_DATE(' 2015-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace LONEBOO
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    )
)
;
-- Create/Recreate primary, unique and foreign key constraints 
alter table B
  add constraint PK_B_ID primary key (ID)
  using index 
  tablespace LONEBOO
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );




-- Create table
create table C
(
  ID   nvarchar2(20),
  NAME nvarchar2(50),
  AGE  number
)
;
-- Create/Recreate primary, unique and foreign key constraints 
alter table C
  add constraint pk_c_id primary key (ID);

INSERT INTO a VALUES ('1',to_date('2015-01-01','yyyy-mm-dd'),'hello');
INSERT INTO a VALUES ('2',to_date('2015-02-01','yyyy-mm-dd'),'world');
INSERT INTO a VALUES ('3',to_date('2015-03-01','yyyy-mm-dd'),'join');
INSERT INTO a VALUES ('4',to_date('2015-04-01','yyyy-mm-dd'),'syn');
INSERT INTO a VALUES ('5',to_date('2015-05-01','yyyy-mm-dd'),'koko');

INSERT INTO B VALUES ('1',to_date('2015-01-01','yyyy-mm-dd'),'hello',NULL);
INSERT INTO B VALUES ('2',to_date('2015-02-01','yyyy-mm-dd'),'world',NULL);
INSERT INTO B VALUES ('3',to_date('2015-03-01','yyyy-mm-dd'),'join',NULL);
INSERT INTO B VALUES ('4',to_date('2015-04-01','yyyy-mm-dd'),'syn',NULL);
INSERT INTO B VALUES ('5',to_date('2015-05-01','yyyy-mm-dd'),'koko',NULL);

通过sql development将图片加入到data字段中

INSERT INTO C VALUES ('1','hello',20);
INSERT INTO C VALUES ('2','world',30);
INSERT INTO C VALUES ('3','join',40);

创建目录

C:\Users\Administrator>sqlplus loneboo/loneboo@orcl


SQL*Plus: Release 10.2.0.5.0 - Production on 星期一 10月 12 11:59:56 2015


Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create directory dirloneboo as 'D:\dirloneboo';

1.导出元数据
expdp loneboo/loneboo@orcl schemas=loneboo directory='dirloneboo' exclude=statistics  content=metadata_only dumpfile=loneboometadata.dmp logfile=loneboometadata.log

2.导出分区表部分数据
expdp loneboo/loneboo@orcl directory='dirloneboo' exclude=statistics content=data_only parallel=10 tables=A,B query=\"where createdate <to_date('2015-03-01','yyyy-mm-dd')\" dumpfile=loneboombigtable%U.dmp logfile=loneboombigtable.log

3.导出小表全部数据

expdp loneboo/loneboo@orcl directory='dirloneboo' exclude=statistics content=data_only parallel=10  tables=C dumpfile=loneboosmalltable%U.dmp logfile=loneboosmalltable.log

4.导出分区表剩余数据

expdp loneboo/loneboo@orcl directory='dirloneboo' exclude=statistics content=data_only parallel=10 tables=A,B query=\"where createdate >= to_date('2015-03-01','yyyy-mm-dd')\" dumpfile=lonebootableother%U.dmp logfile=lonebootableother.log

destination host 步骤：

C:\Users\Administrator>sqlplus /@orcl as sysdba

SQL> create tablespace loneboo datafile 'D:\oracle\product\10.2.0\oradata\orcl\loneboo01.dbf' size 200m;

表空间已创建。

SQL> create user loneboo identified by loneboo default tablespace loneboo temporary tablespace temp;

用户已创建。

SQL> grant connect,dba to loneboo;

SQL> exit

C:\Users\Administrator>sqlplus loneboo/loneboo@orcl

SQL> create directory dirloneboo as 'D:\dirloneboo';

目录已创建。

拷贝导出的文件到destination host 的 D:\dirloneboo

1.导入元数据
impdp loneboo/loneboo@orcl schemas=loneboo directory='dirloneboo' dumpfile=loneboometadata.dmp logfile=imploneboometadata.log

2.导入大表的部分数据
impdp loneboo/loneboo@orcl directory='dirloneboo'  content=data_only parallel=10  dumpfile=loneboombigtable%U.dmp logfile=imploneboodata.log

3.导入小表的全部数据
impdp loneboo/loneboo@orcl directory='dirloneboo' content=data_only parallel=10 dumpfile=loneboosmalltable%U.dmp logfile=imploneboosmalltable.log

4.导入大表的剩余数据
impdp loneboo/loneboo@orcl directory='dirloneboo' content=data_only parallel=10 dumpfile=lonebootableother%U.dmp logfile=imploneboopardata.log

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28282660/viewspace-1814505/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28282660/viewspace-1814505/