Oracle 資料移轉

標籤：oracle 資料移轉

oracle 資料庫遷移

########################################################################################

########################################################################################

在源庫中匯出資料（備份）

exp OMS_lvnain01/OMS_lvnain01 owner=OMS_lvnain01 file=/home/oracle/gaotest/OMS_lvnain01.dmp

exp OMS_lvnain02/OMS_lvnain02 owner=OMS_lvnain02 file=/home/oracle/gaotest/OMS_lvnain02.dmp

exp OMS_lvnain03/OMS_lvnain03 owner=OMS_lvnain03 file=/home/oracle/gaotest/OMS_lvnain03.dmp

exp PORTAL_lvnain01/PORTAL_lvnain01 owner=PORTAL_lvnain01 file=/home/oracle/gaotest/PORTAL_lvnain01.dmp

exp PORTAL_lvnain02/PORTAL_lvnain02 owner=PORTAL_lvnain02 file=/home/oracle/gaotest/PORTAL_lvnain02.dmp

exp PORTAL_lvnain03/PORTAL_lvnain03 owner=PORTAL_lvnain03 file=/home/oracle/gaotest/PORTAL_lvnain03.dmp

########################################################################################

########################################################################################

把匯出的備份資料傳送到遷移的新伺服器上

cd /home/oracle/gaotest/

scp -P 6022 ./*  [email protected]:/data/oracle/gaotest/oms  

cd cd /home/oracle/gaotest/

scp -P 6022 ./*  [email protected]:/data/oracle/gaotest/portal 

########################################################################################

########################################################################################

在新的伺服器上建立資料庫資料表空間，使用者的等

1.OMS_lvnain01（資料檔案大小2G）

create tablespace OMS_lvnain01 logging  datafile ‘/data/oracle/app/oracle/OMS_lvnain01.dbf‘ size 2000m autoextend on next 2000m maxsize unlimited;                                          ##這個表結構的OMS_lvnain01.dbf被我刪除，但是記憶體不變

create user c##OMS_lvnain01 identified by OMS_lvnain01 default tablespace OMS_lvnain01;

grant connect,resource,dba to c##OMS_lvnain01;

==========

#drop tablespace OMS_lvnain01;

2. OMS_lvnain02（資料檔案大小5G）

create tablespace OMS_lvnain02 logging  datafile ‘/data/oracle/app/oradata/OMS_lvnain02.dbf‘ size 5000m autoextend on next 5000m maxsize unlimited;

create user c##OMS_lvnain02 identified by OMS_lvnain02 default tablespace OMS_lvnain02;

grant connect,resource,dba to c##OMS_lvnain02;

==========

3.OMS_lvnain03（資料檔案大小6G）

create tablespace OMS_lvnain03 logging  datafile ‘/data/oracle/app/oradata/OMS_lvnain03.dbf‘ size 6000m autoextend on next 6000m maxsize unlimited;

create user c##OMS_lvnain03 identified by OMS_lvnain03 default tablespace OMS_lvnain03;

grant connect,resource,dba to c##OMS_lvnain03;

#drop tablespace c##OMS_lvnain03;

==========

4.PORTAL_lvnain01（資料檔案大小4G）

create tablespace PORTAL_lvnain01 logging  datafile ‘/data/oracle/app/oradata/PORTAL_lvnain01.dbf‘ size 4000m autoextend on next 4000m maxsize unlimited;

create user PORTAL_lvnain01 identified by PORTAL_lvnain01 default tablespace PORTAL_lvnain01;

grant connect,resource,dba to PORTAL_lvnain01;

==========

5.PORTAL_lvnain02（資料檔案大小2G）

create tablespace PORTAL_lvnain02 logging  datafile ‘/data/oracle/app/oradata/PORTAL_lvnain02.dbf‘ size 5000m autoextend on next 5000m maxsize unlimited;

create user c##PORTAL_lvnain02 identified by PORTAL_lvnain02 default tablespace PORTAL_lvnain02;

grant connect,resource,dba to c##PORTAL_lvnain02;

==========

6.PORTAL_lvnain03（資料檔案大小2G）

create tablespace PORTAL_lvnain03 logging  datafile ‘/data/oracle/app/oradata/PORTAL_lvnain03.dbf‘ size 5000m autoextend on next 5000m maxsize unlimited;

create user c##PORTAL_lvnain03 identified by PORTAL_lvnain03 default tablespace PORTAL_lvnain03;

grant connect,resource,dba to c##PORTAL_lvnain03;

########################################################################################

########################################################################################

########################################################################################

########################################################################################

往新伺服器中匯入資料

imp c##OMS_lvnain01/OMS_lvnain01 fromuser=c##OMS_lvnain01 touser=c##OMS_lvnain01 buffer=150009081 commit=y file=/data/oracle/gaotest/oms/OMS_lvnain01.dmp ignore=y

imp c##OMS_lvnain02/OMS_lvnain02 fromuser=c##OMS_lvnain02 touser=c##OMS_lvnain02 buffer=150009081 commit=y file=/data/oracle/gaotest/oms/OMS_lvnain02.dmp ignore=y

imp c##OMS_lvnain03/OMS_lvnain03 fromuser=c##OMS_lvnain03 touser=c##OMS_lvnain03 buffer=150009081 commit=y file=/data/oracle/gaotest/oms/OMS_lvnain03.dmp ignore=y

==============================

imp c##PORTAL_lvnain01/PORTAL_lvnain01 fromuser=c##PORTAL_lvnain01 touser=c##PORTAL_lvnain01 buffer=150009081 commit=y file=/data/oracle/gaotest/portal/PORTAL_lvnain01.dmp ignore=y

imp c##PORTAL_lvnain02/PORTAL_lvnain02 fromuser=c##PORTAL_lvnain02 touser=c##PORTAL_lvnain02  buffer=150009081 commit=y file=/data/oracle/gaotest/portal/PORTAL_lvnain02.dbf ignore=y

imp c##PORTAL_lvnain03/PORTAL_lvnain03 fromuser=c##PORTAL_lvnain03 touser=c##PORTAL_lvnain03  buffer=150009081 commit=y file=/data/oracle/gaotest/portal/PORTAL_lvnain03.dmp  ignore=y

本文出自 “奮鬥吧” 部落格，請務必保留此出處http://lvnian.blog.51cto.com/7155281/1705276

Oracle 資料移轉