数据库迁移案例

1.      Install 10.2.0.1 db and install path p6810189

2.      Create db and configure listener

3.      Create user simplo、smt and tablespace

Create user smt identified by smt default tablespace SMT_TB;

Grant dba to smt_tb;

4.      Create user smphr and tables

Create user smphr identified by smphr;

Grant dba to smphr;

create table SMPHR.ORGNIZATION_OWNER(

  ORG_ID   INTEGER,

  USERNAME VARCHAR2(30),

  PASSWORD VARCHAR2(30),

  PRODLINE VARCHAR2(30));

Insert into SMPHR.ORGNIZATION_OWNER values(51,’SMT’,’smt’,’SMT’);

Commit;

5.      Move smp table from system to users

select 'alter table smp.'||table_name||' move to USERS' from dba_tables where owner='SMP' and tablespace_name='SYSTEM';

6.      Rebuild index

select 'alter index smp.'||index_name||' rebuild tablespace USERS online' from dba_indexes where owner='SMP' and tablespace_name='SYSTEM';

7.      Export schemas smp from 31(fab2)

Drop tablespace temp including contens and datafile;

Create directory dump_arch as ‘/ora102/archive’;

$ Expdp smp/ezsfcs directory=dumpdir dumpfile=dump_arch:smp1.dmp,dumpdir:smp2.dmp,dumpdir:smp3.dmp

Logfile=dumpdir:dump.log Schemas=smp filesize=85G parallel=4

8.      Copy dumpfile

ftp>

binary

prompt

9.      Import schemas from  smp to smt

SQL>Create directory dumpdir as ‘/ora105/oradata/’;

SQL>Grant read,write on directory dumpdir to smt;

$impdp smt/smt directory=dumpdir dumpfile=smp1.dmp,smp2.dmp,smp3.dmp

Logfile=impdp_smt.log schemas=smp remap_schema=smp:smt

Remap_tablespace=USERS:SMT_TB parallel=4

10.  Check impdp log

Create error  job and compile invalid objects

11.  Archive log enable

Shutdown immediate;

Startup mount;

Alter database archivelog;

12.  FAB2 create temp tablespace

SQL>Create temporary tablespace temp tempfile ‘/ora102/oradata/STCSMES/temp01.dbf’ size 5120M  AUTOEXTEND  ON NEXT 100M MAXSIZE UNLIMITED;

SQL>Alter database default temporary tablespace temp;

SQL>Alter tablespace temp add datafile ‘/ora102/oradata/STCSMES/temp02.dbf’ size 5120 AUTOEXTEND  ON NEXT 100M MAXSIZE UNLIMITED;

SQL>Alter tablespace temp add datafile ‘/ora102/oradata/STCSMES/temp03.dbf’ size 5120AUTOEXTEND  ON NEXT 100M MAXSIZE UNLIMITED;

SQL>Alter tablespace temp add datafile ‘/ora102/oradata/STCSMES/temp04.dbf’ size 5120 AUTOEXTEND  ON NEXT 100M MAXSIZE UNLIMITED;

创建6个

13.  Drop sequence

select ' drop sequence smt.'||sequence_name||';' from dba_sequences where sequence_owner='SMT' AND sequence_name like 'A2009%';

select ' drop sequence smt.'||sequence_name||';' from dba_sequences where sequence_owner='SMT' AND sequence_name like 'A2010%';

select ' drop sequence smt.'||sequence_name||';' from dba_sequences where sequence_owner='SMT' AND sequence_name like 'F51_%_2009%';

select ' drop sequence smt.'||sequence_name||';' from dba_sequences where sequence_owner='SMT' AND sequence_name like 'F51_%_2010%';

14.总结

2011/10/1 8:20am进行export dump数据248G 在9:08am结束export

2011/10/1 9:10amftp传输文件 10:00am结束

2011/10/1 10:00am开始import数据，到10/2 1:48am结束improt动作

总共消耗时间15h48min

导入之后的数据 由之前的657G变为487G