数据库迁移步骤

数据库迁移：

采用导入导出的方式（以HBZSPT为例）

1.、在目标机器上创建一个同名的数据库实例。

2、创建相应的表空间和用户

--创建用户GSYY

CREATE TABLESPACE "TBS_GSYY_DATA"

    LOGGING

    DATAFILE 'D:/ORACLE/ORADATA/HBZSPT/TBS_GSYY_DATA.ora' SIZE

    2000M AUTOEXTEND

    ON NEXT  50M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL

    SEGMENT SPACE MANAGEMENT  AUTO;

CREATE TABLESPACE "TBS_GSYY_IDX"

    LOGGING

    DATAFILE 'D:/ORACLE/ORADATA/HBZSPT/TBS_GSYY_IDX.ora' SIZE

    500M AUTOEXTEND

    ON NEXT  10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL

    SEGMENT SPACE MANAGEMENT  AUTO ;

CREATE

    TEMPORARY TABLESPACE "TBS_GSYY_TMP" TEMPFILE

    'D:/ORACLE/ORADATA/HBZSPT/TBS_GSYY_TMP.ora' SIZE 200M EXTENT

    MANAGEMENT LOCAL UNIFORM SIZE 1M;

   

CREATE USER "GSYY"  PROFILE "DEFAULT"

    IDENTIFIED BY "gsyy" DEFAULT TABLESPACE "TBS_GSYY_DATA"

    TEMPORARY TABLESPACE "TBS_GSYY_TMP"

    ACCOUNT UNLOCK;

GRANT "CONNECT" TO "GSYY";

GRANT "DBA" TO "GSYY";

GRANT "RESOURCE" TO "GSYY";

 

--创建用户JCPT

CREATE TABLESPACE "TBS_JCPT_DATA"

    LOGGING

    DATAFILE 'D:/ORACLE/ORADATA/HBZSPT/TBS_JCPT_DATA.ora' SIZE

    500M AUTOEXTEND

    ON NEXT  20M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL

    SEGMENT SPACE MANAGEMENT  AUTO ;

CREATE TABLESPACE "TBS_JCPT_IDX"

    LOGGING

    DATAFILE 'D:/ORACLE/ORADATA/HBZSPT/TBS_JCPT_IDX.ora' SIZE

    200M AUTOEXTEND

    ON NEXT  5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT

    SPACE MANAGEMENT  AUTO ;

CREATE

    TEMPORARY TABLESPACE "TBS_JCPT_TMP" TEMPFILE

    'D:/ORACLE/ORADATA/HBZSPT/TBS_JCPT_TMP.ora' SIZE 200M EXTENT

    MANAGEMENT LOCAL UNIFORM SIZE 1M;

 

CREATE USER "JCPT"  PROFILE "DEFAULT"

    IDENTIFIED BY "jcpt" DEFAULT TABLESPACE "TBS_JCPT_DATA"

    TEMPORARY TABLESPACE "TBS_JCPT_TMP"

    ACCOUNT UNLOCK;

GRANT "CONNECT" TO "JCPT";

GRANT "DBA" TO "JCPT";

GRANT "RESOURCE" TO "JCPT";

 

--创建用户WWJCPT

CREATE USER "WWJCPT"  PROFILE "DEFAULT"

    IDENTIFIED BY "wwjcpt" DEFAULT TABLESPACE "TBS_JCPT_DATA"

    TEMPORARY TABLESPACE "TBS_JCPT_TMP"

    ACCOUNT UNLOCK;

GRANT "CONNECT" TO "WWJCPT";

GRANT "DBA" TO "WWJCPT";

GRANT "RESOURCE" TO "WWJCPT";

 

--创建用户PERM   

CREATE TABLESPACE "PERM_MAIN"

    LOGGING

    DATAFILE 'D:/ORACLE/ORADATA/HBZSPT/PERM_MAIN.ora' SIZE 200M

    AUTOEXTEND

    ON NEXT  10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL

    SEGMENT SPACE MANAGEMENT  AUTO ;

CREATE TABLESPACE "PERM_IDX"

    LOGGING

    DATAFILE 'D:/ORACLE/ORADATA/HBZSPT/PERM_IDX.ora' SIZE 200M

    AUTOEXTEND

    ON NEXT  5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT

    SPACE MANAGEMENT  AUTO ;

CREATE

    TEMPORARY TABLESPACE "PERM_TMP" TEMPFILE

    'D:/ORACLE/ORADATA/HBZSPT/PERM_TMP.ora' SIZE 50M EXTENT

    MANAGEMENT LOCAL UNIFORM SIZE 1M;

 

CREATE USER "PERM"  PROFILE "DEFAULT"

    IDENTIFIED BY "perm" DEFAULT TABLESPACE "PERM_MAIN"

    TEMPORARY TABLESPACE "PERM_TMP"

    ACCOUNT UNLOCK;

GRANT "CONNECT" TO "PERM";

GRANT "DBA" TO "PERM";

3、从原机器上导出数据

导出gsyy,jcpt,wwjcpt，perm三个用户的数据

exp gsyy/gsyy@hbzspt file="d:/hbzspt.dmp" log=d:/log_hbzspt.log  owner=gsyy,jcpt,wwjcpt,perm;

4、将数据导入到目标机器

导入数据

对于数据量很大的表必须在导入命令中加buffer参数，否则会导入失败。

GSYY是拥有DBA权限的用户，若不指定owner，则会导出整个HBZSPT实例。

imp gsyy/gsyy@hbzspt file="d:/hbzspt.dmp" buffer=100000 log=d:/log_hbzspt.log full=y;